Setup / lookup
Operational
Audit / history
Has RI issues
PK primary key
FK foreign key
UK unique
VIRT virtual / generated
High-level relationship diagram — click any table card below to filter the diagram
Referential-integrity issues at a glance
| Marker | Where | Issue | Fix (briefing stage) |
|---|---|---|---|
| ⚠️ nullable + SET NULL | financial_categories.inventory_gl_account_idcogs_gl_account_idwaste_gl_account_id | The columns nulled by the production bug. ON DELETE SET NULL means hard-deleting a GL nulls these silently; nullable allows the form to overwrite with empty. | Stage 3 — backfill from audit log, then NOT NULL + RESTRICT |
| ⚠️ JSON blob | financial_categories.sales_gl_account_idsfinancial_categories.mapped_category_ids | Arrays of FK ids stored as JSON, no constraints. Source of the [16] vs "[\"16\"]" audit log inconsistency. | Stage 4 — junction tables |
| ⚠️ free text | sizes.quantity_unit, sizes.container_type | Plain string columns, no units / container_types lookups. Typos and inconsistencies aren't preventable. | Stage 5 — defer |