Comprender el esquema de la base de datos de informes

BETA: The reporting database is currently in beta and may still evolve. It is available by opt-in only. To join the beta or ask questions, contact support@azumuta.com.

Where Your Data Lives

The managed reporting database stores your workspace’s reportable data in a schema named company_<normalized-workspace-id>. The name uses lowercase letters and replaces other characters, except digits, with underscores. A custom database can use a different schema name. Use your configured schema name in the examples below.

The Main Tables

Enabling the reporting database syncs all tables below. You cannot select individual tables.

Table	What it holds
workinstruction, workinstruction_version, workinstruction_tag	Work instructions, their versions, and their assigned tags. Versions include unapproved versions. Use is_approved and is_effective to check approval and effective-date state.
instruction_step	The individual steps within a Work Instruction version.
instruction_visit, instruction_total_visit, instruction_partial_check	Each time an operator works through a step, per-step totals, and partial check submissions.
recording, recording_status, recording_variant, recording_step	Recordings with an instruction version and their status history, selected variant information, and per-step recording data. These can include planned audits that have not started.
issue, board, issue_column	Issues, their boards, and their board columns. These include Continuous Improvement, approval, Andon, and training trajectory boards. Use issue.board_type to select a board type.
issue_task, issue_comment, issue_attachment, issue_signature, issue_checklist	The items attached to an issue.
issue_transition	The history of an issue moving between board columns.
product_order, product_order_workinstruction, product_order_workinstruction_spot	Production orders and the work instructions linked to them.
article, article_category	Product articles and their categories, not knowledge-base articles.
tree_node	The Explore tree structure that organizes modules, folders, and content.
user, user_group, user_group_member	People and groups in your workspace.
competency_training, competency_level	Each training fact behind a skill matrix cell: the level, how it was given (training, training without trainer, product order review, or set by hand), the assessed trainer, the durations, the trained version and a custom validity period. competency_level holds the label your workspace shows for each level.
qualification	Qualifications with their type.
qualification_competency_setting	The skill matrix settings of each qualification: whether a level needs an attachment or a custom validity period, and the validity and warning periods.
recording_trainee	The planned trainees of each training, with the level each one got.
workinstruction_competency_setting	The Learning & Development settings of each work instruction: the minimum level to start a product order, the levels for fast forward and for leaving out double-checks, and the validity period.
tree_node_competency_coverage	For each skill matrix row and level, how many people must reach that level. A folder without its own value takes the lowest value of its available children, as in the skill matrix.
skill_matrix_cell	Each skill matrix cell as the matrix shows it: the level and status of each person on each work instruction or qualification, and the score of each folder. Only filled when your workspace uses stored skill matrix cells.

Audit recordings in the reporting database

The recording table includes only recordings with a numeric instruction version. A recording without a version is excluded, even when its status is Finished (FINISHED).

The recording does not have to be In progress (IN_PROGRESS) or Finished to be included. An offline download can link an instruction version while the status stays Planned (IN_QUEUE). After a successful reporting database sync, that recording can appear with status IN_QUEUE.

Automatic downloads depend on the device’s selected content and date range. They can occur before an operator opens an audit. See Device Settings: Offline.

Closing an unstarted audit as N/A (NOT_APPLICABLE) does not itself link an instruction version. If the recording has no version, the reporting database continues to exclude it. Status alone does not explain whether an audit appears in the database.

Status and result are different

The status column contains the stored value, such as IN_QUEUE, not the display label Planned. See Status, result, and notes for all English labels and stored values.

The recording_status table contains status changes. It does not contain result changes.

The recording.result column stores the selected result, such as Success (SUCCESS), Failed (FAILED), N/A (NOT_APPLICABLE), or Cancelled (CANCELLED). New recordings start with PLANNED. This value can remain if no result is selected. If the source recording has no result value, the column is NULL. Use status, not result, to identify the execution state.

The is_failure column is calculated from checks, events, and rework. It can be true because of:

• A failed check or double-check.
• A torque result of NOK.
• A number outside the permitted limits.
• An Andon event, which signals a problem during execution.
• A current or previous Rework (REWORK) status.

The is_defect column uses the same checks but excludes rework alone. For example, rework without a failed check or Andon event gives is_failure = true and is_defect = false. Previous rework can keep is_failure = true after the recording finishes. See Rework for the workflow.

Neither calculation uses the selected result. To identify N/A or Success, use result, not is_failure = false. A recording can have is_failure = false without a selected result.

Recording notes

The recording.notes column contains the recording notes, including notes entered with a result. You can filter on notes = 'auto cleanup' to identify recordings with that exact note. Missing or empty notes appear as NULL.

Rows synced before result and notes were added can still have NULL in these columns. A normal sync updates changed recordings only. See Fill columns for existing data to fill these columns for unchanged historical recordings.

Select audits and work instructions with tags

Tags that you attach to a work instruction or an audit are in the workinstruction_tag table, one row per tag. Use them to select the right audits without matching on names or descriptions, which break when someone edits the text.

Column	What it holds
workinstruction_id	The work instruction or audit that the tag is attached to.
workinstruction_name	Its display name.
tag	One tag. Tags are free text, so agree on the spelling in your workspace.

For example, to list the audits that carry the tag calculate_quality_score:

select r.id, r.status, r.result
from "company_<your-workspace-id>".recording r
join "company_<your-workspace-id>".workinstruction_tag t
    on t.workinstruction_id = r.workinstruction_id
where r.type = 'AUDIT'
    and t.tag = 'calculate_quality_score';

The work instruction table has no column that marks audits. Select audits with recording.type = 'AUDIT'.

Completed work and closed orders

Recording status and product-order status describe different levels of work:

Field and value	Meaning
recording.status = 'FINISHED'	The recording has ended. Check result separately.
product_order.enhanced_status = 'all-complete' (All complete)	The active order meets the calculated completion conditions. These consider its work instructions, quantities, and non-archived child orders.
product_order.enhanced_status = 'finished' (Finished)	The order has been closed.

An order can remain In progress when its recordings have finished but its completed quantity does not match its required quantity. An active order with no work instructions or child orders is All complete. See Product-order status.

Force-finishing an order cancels its unfinished recordings. Those recordings receive status FINISHED and result CANCELLED. Thus, closure is not proof of successful execution. product_order.finished_at records order closure, not necessarily the time all work was completed.

product_order.activated_at records when the order was first activated. It is NULL for an order that was never activated, and for an order activated before Azumuta recorded activation times. It is not derived from execution_date, which is a planned date. Orders that were already synced before this column existed only receive a value when they change. To fill it for all existing orders, ask Azumuta support to clear your reporting data so the next sync rebuilds it.

Zone associations and assignment

product_order_workinstruction_spot relates each order item to its zones. The zone_type identifies the source:

• assigned: a zone assigned to the order item.
• accessed: a zone recorded during operator activity, unless the same zone is already assigned.
• associated: a zone from work-instruction placement, used only when the item has no assigned or accessed zones.

The table combines assigned and accessed zones. It keeps one row per item and zone, with assigned taking priority. A locked assignment is not a separate SQL zone type.

For example, an item is assigned to A, accessed at A and B, and has default placement C. The rows are assigned A and accessed B, with no C. A filter on zone_type = 'accessed' therefore misses the access at A.

Fallback placement uses the work instruction’s direct parent tree node when that node is a zone. It does not search arbitrary ancestors.

Assignments can change. The assigned_spot_id fields describe assignment, not an unchanging execution location. The association table is not an assignment history. Its rules also do not match every built-in widget. See Zone filtering for product-order widgets for widget configuration.

Join at the order-item level

To count planned work, start from product_order_workinstruction. If you need recording details, use a left join from its recording_id to recording.id. This retains items without a reporting row for a recording.

Join zones through product_order_workinstruction_spot.product_order_workinstruction_id = product_order_workinstruction.id. Do not join by workinstruction_id alone: several order items can use the same work instruction.

Multiple zones or visits can repeat an item’s quantity or a recording’s duration in a joined result. Use EXISTS for an association filter, or aggregate each source before joining. See Count order items associated with a zone.

Planned users and actual operators

recording.planned_user_id and planned_group_id describe assignment. They do not identify everyone who performed the work. instruction_visit.user_id identifies the operator for that visit.

For a training, planned_user_id is the planned trainer and recording.trainer_id is the trainer who finished the training. The trainees are in recording_trainee.

Several operators can contribute to one recording. Count distinct recording IDs for each operator, not visit rows. A shared recording then counts for each contributing operator. See Recordings for the report workflow and the operator query for this calculation.

Filter steps by check type

instruction_step.check_type holds the type of the check on a step. Use it instead of matching on step_description, which changes when someone edits the step. The column is NULL for a step without a check.

Value	Check
choice/ok-nok	OK/NOK
choice/yes-no	Yes/No
choice/single	Single choice
choice/multiple	Multiple choice
number/prompt	Number
number/slider	Slider
number/score	Score
tools/torque	Torque
text/line	Single line
text/paragraph	Paragraph
text/masked	Masked input
text/unique-id	Automatic ID
camera/bar-code	Barcode
camera/picture	Pictures
camera/signature	Handwritten signature
table	Table
special/attachment	Files
special/print	Print
special/email	Email
special/procedure	Procedure
special/identifier	Product order number
special/variants	Variants
special/timer	Timer
special/link-product	Link product order
special/scan-parts	Parts
special/electronic-signature	Electronic signature

For example, to list the steps with an OK/NOK check:

select workinstruction_name, step_description
from "company_<your-workspace-id>".instruction_step
where check_type = 'choice/ok-nok';

The values are also in the description of the column, see the built-in data dictionary.

Visits, attempts, and applicable steps

• instruction_visit has a row when an operator leaves a step. A visit that is still open may have no row yet.
• type = 'normal' and type = 'double-check' are separate visit groups. is_last_visit marks the last visit within its group, not necessarily the last visit with an answer. See Double-checks.
• A blocked or invalid check counts as an attempt but does not supply an accepted answer. instruction_total_visit.answer_attempts counts visits with an attempt after the operator leaves, not individual button presses.
• instruction_total_visit holds totals and the final recorded answer for each recording, step, and visit type. A recorded answer is not necessarily a successful answer.
• recording_step.active = false means that variants exclude the step. No visit is expected. Older recordings without stored variant selection treat all steps as active. See Variants and variant configuration.

Missing data alone does not prove failure. Check applicability, recording status, and whether the step requires an answer. See Compare applicable steps with recorded answers.

Version and parameter context

Join recording.workinstruction_version_id to workinstruction_version.id to read the version linked to the recording. Do not use the current work-instruction version to interpret historical steps. Approval is a separate property; see Revision History.

Parameters are stored as a JSON object keyed by parameter name. Read one value with parameters->>'key'. A recording or order without parameters has NULL.

To make an answer available in the reporting database, set Write value to parameters on the check and give it a stable key, for example machine_operator. See Use a parameter in a Product Check. Select on that key instead of matching on step descriptions:

select id, parameters->>'machine_operator' as machine_operator
from "company_<your-workspace-id>".recording
where parameters->>'machine_operator' = 'Jan';

A product-order recording keeps its parameters on the order, so recording.parameters is empty for it. Read product_order.parameters through recording.product_order_id. The column is a JSON object as well, so the same expression works:

select r.id, o.parameters->>'batch' as batch
from "company_<your-workspace-id>".recording r
join "company_<your-workspace-id>".product_order o
    on o.id = r.product_order_id;

Audits and other recordings that are not product-order recordings use recording.parameters. See Product-order parameters and Introduction to parameters for what a parameter is.

JSON fields can contain lists or nested objects, not only numbers or text. Check each value’s type before converting it. A SQL NULL indicates missing data, not zero or a failed answer.

Fill columns for existing data

A new column or table fills during the next sync. A row that already exists only gets a value in a new column when its source record next changes, so historical data can stay empty. This applies to columns that were added after you enabled the reporting database, such as recording.result, recording.notes, recording.parameters, recording.trainer_id, tree_node.competency_importance, workinstruction_version.is_training_required and instruction_step.check_type. The workinstruction_tag table fills itself.

To fill the history once, use Clear data in the reporting configuration. It removes all synced rows for your workspace and keeps the reporting database enabled. The next sync fills every table from scratch.

Warning: Queries and BI reports show no data until that sync is done. The button is not available while a sync is running. See Enable the reporting database.

Conventions Used Throughout

• Primary keys. Each table has a text primary key named id. This identifies a source record or a generated row. Related columns use names such as recording_id and workinstruction_id. For example, join recording.workinstruction_id to workinstruction.id.
• Deletions. When a sync detects a deleted source record, it removes the corresponding reporting rows. Current table definitions do not include deleted_at. Older databases can retain this column because schema updates do not remove old columns. The current sync does not maintain it; do not use it as a current deletion indicator. Archived records are different from deleted records. For example, issue.archived_at identifies archived issues.
• Sync timestamps. Every table has first_synced_at and last_synced_at. These record the first and most recent sync that wrote the row, not the source record’s creation or modification time.
• Source timestamps. Columns such as created_at, updated_at, and started_at depend on the table. PostgreSQL stores these timestamps as instants in UTC and displays them in the session time zone.
• Recording durations. recording.actual_duration and recording.rework_time are in seconds and can contain fractions. Do not divide them by 1000. Check the column description for the unit of other fields.
• Flexible fields use JSON. Data that varies in shape (such as parameters or an instruction answer) is stored as jsonb, which you can query with PostgreSQL’s JSON operators.
• Pre-computed values. To save you from extra joins, some tables include ready-made counts and metrics, for example issue.comment_count, issue.open_task_count, or recording.rework_time.

The Built-In Data Dictionary

Every table and every column has a human-readable description attached to it. Most SQL clients and BI tools show these automatically. For example, in psql:

\d+ "company_<your-workspace-id>".issue

You can also read them with a query:

select column_name, col_description(
    ('company_<your-workspace-id>.issue')::regclass,
    ordinal_position
) as description
from information_schema.columns
where table_schema = 'company_<your-workspace-id>'
  and table_name = 'issue'
order by ordinal_position;

Ready to query? See Example Analytics Queries.