Appendix A — Data dictionary

All business tables carry tenant_id (NOT NULL, FK to tenants, indexed); uniqueness and document numbering are per tenant. Migrations are forward-only SQL files in packages/shop-db/migrations.

Migration	Tables / changes
0001	`install_settings`, `users` (e-mail, argon2id hash, display name, role, status)
0002	`uom`, `warehouse`, `item` (codes upper-case, unique)
0003	`document_sequence` (gapless numbers), `stock_movement`, `stock_movement_line`, `stock_ledger_entry` (append-only), `item_stock_balance`, `item_cost` (moving average)
0004	`bom`, `bom_line`
0005	`work_order`, `work_order_material`
0006	tenant logo path
0007	`password_reset_tokens` (SHA-256 of the token, single use, expiry)
0008–0010	`tenants`, `tenant_admin_audit`; `tenant_id` on every table, backfill, constraints and per-tenant uniqueness
0011	`attachments` (photos, signatures; object storage key, URL, size)
0012	`saved_views`, `user_preferences`; `users.deleted_at`
0013–0014	`tags`, `item_tags`, `notifications`, `api_keys`, `webhooks`, `audit_logs`; dark logo
0015	`customers`, `vendors`, `sales_orders`, `sales_order_lines`, `purchase_orders`, `purchase_order_lines`, `work_centers`, `work_order_steps`
0016, 0018	`feedback` (+ repair)
0017	`permissions`, `roles`, `role_permissions`; `users.role_id`
0019	production repairs (audit log)
0020	`custom_forms`, `dynamic_records` (JSONB, GIN), `dynamic_record_links`, `workflow_functions`, `workflow_rules`, `workflow_runs`, `dashboards`
0021	`saved_reports`; `report_button` trigger (rules without a form)
0022	plan renames, 180-day trial, billing fields (`billing_currency`, `billing_cycle`, `seats`, `custom_limits`, `payment_failed_at`, `grace_period_ends_at`, `cancelled_at`, `purge_after`), `api_usage`, `webhook_usage`
0023	`custom_forms.nav_section`, `dashboards.nav_section`, `users.avatar_url`, `tenant_ui_config`

Key entities

Entity	Important columns
tenants	slug, name, plan, plan_status, trial dates, region, currency, locale, time zone, accent, brand, logos, status, billing fields
users	email (unique per tenant), display_name, role_id, status, avatar_url, deleted_at
item	code, name, item_type, base_uom_id, standard_cost, material_grade, thickness_mm, dimensions, status
stock_movement	document_no (RCV/ISS/TRF/ADJ/JIS/JRT), movement_type, status, movement_date, reference, reason, idempotency_key, purchase_order_id, work_order_id
stock_ledger_entry	item, warehouse, quantity delta, unit cost, document reference — never updated
item_stock_balance / item_cost	on hand per item & warehouse / moving-average cost per item
bom / bom_line	parent_item_id, revision, status; component_item_id, quantity_per, scrap_percent
work_order	job_no (JOB-), item_id, quantity, due_date, status, completed_quantity, labour_hours, labour_rate, operations
work_order_material	planned / issued / returned per component
sales_orders / lines	document_no (QT-/SO-), customer_id, status, dates; item, quantity, unit_price, reserved
purchase_orders / lines	document_no (PO-), vendor_id, status, dates; item, quantity, unit_cost, received_quantity
custom_forms	name, slug, fields (JSONB), settings, status, version, nav_section
dynamic_records	form_id, record_no, data (JSONB)
workflow_rules / runs	form_id (nullable), trigger, condition, actions, enabled; run status, steps, error
dashboards / saved_reports	widgets, filters, nav_section; module, chart, query, options, buttons, position
api_keys	name, prefix, hash, scopes, expires_at, last_used_at
webhooks	url, description, events, secret, active
audit_logs	actor, action, entity_type, entity_id, summary, metadata, created_at
api_usage / webhook_usage	tenant, period (YYYY-MM), calls / deliveries, warned_at
tenant_ui_config	config JSONB: home, modules, journey, blueprint
user_mfa	user_id, email_verified_at, totp_secret_enc (AES-256-GCM), totp_enabled_at, totp_last_step, pending secret, backup_code_hashes (argon2id), locked_until
mfa_challenges	user_id, method, attempts per method, failed_state_at (hold start), fallback_granted_at, e-mail code hash + expiry, sends, expires_at, consumed_at
user_sessions	user_id, session_hash, device, user_agent, ip, location, mfa_method, created_at, last_active_at, expires_at (60 days)
ai_conversations / ai_messages	user_id, title; role, content, tool_calls (JSONB), tool_call_id, prompt / completion tokens
ai_proposals	conversation_id, user_id, kind (chart / layout_change), payload (JSONB), status, expires_at, decided_at / by, applied_ref
ai_usage	tenant, period (YYYY-MM), requests, prompt_tokens, completion_tokens, warned_at

Last updated 2026-11-03