Skip to main content

Understanding the Reporting Database Schema

Azumuta Support

Beta. The reporting database is currently in beta; the schema and conventions described here may still change.

Where Your Data Lives

All of your workspace's data lives in a single schema named company_<your-workspace-id>. Each table inside it maps to a concept you already know from Azumuta.

The Main Tables

The exact set of tables depends on what you enabled. The most common ones are:

Table What it holds
workinstruction, workinstruction_version Your work instructions and their published versions.
instruction_step The individual steps within a work instruction version.
instruction_visit, instruction_total_visit Each time an operator works through a step, plus per-step totals.
recording, recording_status Execution sessions (an operator running an instruction) and their status history.
issue Continuous-improvement issues / tickets, with handy pre-computed counts.
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_item, product_order_item_spot Production orders and their items.
users, user_group, user_group_member People and groups in your workspace.

Conventions Used Throughout

Once you know these few rules, the whole schema becomes predictable:

  • Primary keys. Every table has a text primary key (for example recording_id, issue_id) that matches the record's id in Azumuta. You can use it to join related tables together.
  • Soft deletes. Rows are never silently removed. When something is deleted in Azumuta, its row gets a deleted_at timestamp instead. To work with only current data, add where deleted_at is null to your queries.
  • Timestamps. Every table carries created_at and modified_at (and deleted_at). All timestamps are stored in UTC.
  • Durations are in milliseconds. Fields such as actual_duration or rework_time are stored as whole milliseconds. Divide by 1000 for seconds.
  • 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;

This means you rarely have to guess what a column means — the schema documents itself.

Ready to query? See Example Analytics Queries.