Data Model & ERD

Every instance has its own database — your data is never co-mingled with another customer’s. This page lists the tables behind the features described in this documentation: 162 tables, each with its columns, keys, indexes and relations, read directly from the product’s code.

What A Data Model Is

A database is a set of tables, and a table is a list of records of one kind: people, devices, invoices. Each table has columns (the facts kept about each record) and some columns point at a record in another table, which is how a device knows its owner or a candidate knows the job they applied for. At the center is the table of users: identity, access, devices and the org chart all point back to it.

You do not need this page to run Zonitix. It is for the person who reviews the product before you buy it, writes reports against an export, or needs to know which fields the code shows to be encrypted. How one customer’s instance is kept apart from another’s is on Data Isolation.

This is not the whole schema. 9 models that the product’s code defines are left off this page on purpose. They fall into two families: models behind a feature that is not offered in the plans this documentation covers, and models behind features whose documentation is on hold. They are not described here, relations that point at them are not drawn, and a database role that serves only such a family is left out of the per-service table below. Every other model the code defines is listed.

How Access Is Decided

A person gets permissions three ways: from the roles they hold directly (a role is a bundle of permissions), from roles attached to a group they belong to, and from any direct grant given to them individually. What they can do is the combination of all three. A persona is a provisioning template for a job function, and the manager link on each user is what draws the org chart.

The rules that turn these tables into a decision are on Roles & Permissions (RBAC).

The table of users described above is named User. The diagram shows the tables around it.

Schema Conventions

One PostgreSQL database holds every table. Of the models listed on this page, 134 are declared as Sequelize models in a module shared by the Node services, and 29 are declared as SQLAlchemy models by the Python finance service. Because the database belongs to one customer, there is no tenant_id column on any table — isolation is physical, not row-level.

• How the schema is built. A one-shot migration step runs to completion before the services that use the database start. It creates the Sequelize tables from the model definitions, adds columns, enum values, extra indexes and integrity constraints in separate idempotent steps, and sets up the per-service database roles. In production it does not alter existing tables from the models unless DB_ALTER=true is set; new columns arrive through the additive step instead.
• What the lists below show. Columns, keys, indexes and relations exactly as the model definitions declare them, and under each model what the migration step adds on top of it. Types are the ORM’s own names (STRING, JSONB, DATEONLY for Sequelize; String, Numeric(14,2) for SQLAlchemy).
• What the migration step adds. Read from the two lists the migration step applies on every deploy: 63 foreign keys with their ON DELETE rule (every one also ON UPDATE CASCADE), 5 two-column unique constraints on the join tables, 2 check constraints and 50 indexes. They appear under the model they belong to, marked “added by the migration step”. Each one is created only if it is not there yet. A foreign key or check is first added so that it applies to new and changed rows, and is validated against the rows already stored only when none of them breaks it. An index that cannot be built is logged and left out, and the text-search indexes are built only when the pg_trgm extension can be switched on, so on a given instance a listed item can be missing.
• What is still not listed. Rules the migration step creates with its own SQL rather than from those two lists: the trigger and unique index that chain the audit log, the triggers that make the audit-log tables, the finance approval record and the ledger’s financial columns append-only, the trigger that stops a report run’s stored files and fingerprints being changed once they are set, the unique index on a candidate’s email within one job posting, the unique indexes and other indexes the finance service’s own migration step creates (for example one approval per person per document, one payment entry per document and ledger account, one live invoice per vendor and invoice number, one document per identical uploaded file), value guards added together with a new column, and the indexes on tables the step creates directly.
• Naming. Table names are snake_case (job_postings). Sequelize columns are camelCase (managerId), except the join-table keys (UserId, RoleId). The finance tables mostly use snake_case columns (vendor_id); finance_documents and the createdAt / updatedAt timestamps on many of them are camelCase.
• Timestamps. Sequelize adds createdAt and updatedAt unless the model switches them off: some models keep only createdAt, and the join tables have neither. These columns are marked “added by the ORM”.
• Join tables. Many-to-many links are their own tables with a two-column key: user_roles, user_groups, role_permissions, user_permissions and role_groups.
• Soft delete. One model (Workflow) uses the ORM’s soft delete: a delete sets deletedAt and ordinary queries skip the row. No other table has a deletedAt column; where a record must outlive its use, the model carries its own state column instead (a status, or a date such as retiredAt on a device).

Application-Level Encryption

29 of the columns listed below are flagged as holding ciphertext that the application writes, in whole or in part, on top of whatever the disk or the database does. All of them use one cipher, AES-256-GCM, under the instance’s application data key (ENCRYPTION_KEY). A stored text value has the form v1:<key id>:<iv>:<auth tag>:<ciphertext>; the key id lets a value written under an earlier key still be read after rotation, as long as that key is supplied in ENCRYPTION_KEYS_RETIRED; whether a running instance is given that list today is under Keys And Rotation on Data Isolation.

Every column flagged in the model list is proven encrypted: the flag is set only where the code shows it, by an encrypting accessor or hook in the model, or by a write site in service code that the generator re-checks on every run. A column without the flag has no such proof; that is not a guarantee that no service ever encrypts a value into it.

Which Service Owns Which Tables

The services share one database but not one login. Each signs in with its own PostgreSQL role, and the migration step grants that role only the tables it works with, so a service cannot change a table it was not given. The grants are applied again each time the migration step runs.

A table can appear under more than one service: users, for example, is written by the identity, user-management, integration and workflow services. Each model in the list below names the services that may write its table and the exact privileges they hold. Read-only grants are not shown. The count in the table above is the role’s main write list: most of those tables carry SELECT, INSERT, UPDATE, DELETE and a few lack UPDATE. Narrower grants on other tables, such as insert-only access to the audit log, are listed per model and are not counted.

• The audit log is append-only for everyone but its owner. Other services hold SELECT, INSERT on audit_logs and nothing more. The logging service holds SELECT, INSERT, DELETE with no UPDATE; the delete exists so that entries past the retention period can be pruned.
• The finance tables belong to the Python service. The finance service declares its tables itself, creates them on first start and adds its own columns later, which is why its role is the only one that owns tables and may create them. The Node services do not write these tables; the Sequelize FinanceDocument model describes a subset of the finance_documents columns for reading.
• Four finance ledgers are taken back from that service. finance_audit_logs, approval_actions, budget_transfer_approvals and journal_entries are reassigned to the migration role each time the migration step runs, and the finance service is left with SELECT, INSERT, plus an update limited to the status, export and sync columns of journal_entries. It can add a ledger row and cannot delete one; on journal_entries it can change only those columns, and on the other three it cannot change a row at all.

Tables, By Domain

163 models over 162 tables, 2,167 columns in all, grouped by what they are for. (One table, finance_documents, is declared twice: in full by the finance service and in part by a Sequelize model.) Open a group, then a model, to see its columns with type, nullability, default and keys, followed by its indexes, relations and the services that may write it. The list is generated from app commit 99e5035a2 and is not edited by hand.

Reading a row. PK is the primary key, UNIQUE a single-column unique constraint, and FK → users.id a reference to another table. A multi-column unique rule appears under Indexes. “Added by the ORM” marks a column the model does not write out itself: a timestamp, or a foreign key created by a relation. A default of NOW or UUIDV4 is filled in by the ORM when the row is written: the current time, or a new random identifier.