Understanding the Reporting Database Schema

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.

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. A full resync is needed to fill these columns for unchanged historical recordings.

Completed work and closed orders

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

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.

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.

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.

For product-order recordings, read parameters from product_order.parameters through recording.product_order_id. Other recording parameters can be in 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.

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.