Database Table Atlas

How to read this atlas

Field	Meaning
Main tables	The primary persisted records in that domain.
Key FK spine	The relationships that matter most when explaining page-to-page handoffs.
Why it matters in the product atlas	The practical effect on routes, screens, or outputs.

Access, tenancy, and membership backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Users and personal identity	`users`, `push_tokens`	`push_tokens.user_id -> users.id`	The mobile app, personal subscriptions, AI usage, and user-scoped API/webhook ownership all start here.
Organizations and membership	`organizations`, `organization_members`, `organization_invitations`	`organization_members.organization_id -> organizations.id`; `organization_members.user_id -> users.id`; `organization_invitations.organization_id -> organizations.id`	Explains workspace switching, org roles, scheduler eligibility, and why some desks are office-only or engineer-only.
Feature availability	`organization_role_feature_policies`, `organization_member_feature_overrides`	Both hang off `organization_id`; member overrides also point to `user_id`	These tables explain why Quotes, Work Orders, AI, and connected-system tooling can appear in one workspace and disappear in another.
Org billing and credits	`organization_seat_purchases`, `organization_ai_credit_transactions`	Both point to `organization_id`; actor or purchaser links point to `user_id`	These tables explain team-seat checkout, org AI credit top-ups, and billing-admin flows.

Customer, site, and operational asset backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
CRM accounts and relationships	`crm_accounts`, `crm_contacts`, `crm_opportunities`, `crm_tasks`, `crm_activities`, `crm_documents`, `crm_sla_templates`	`crm_accounts.organization_id -> organizations.id`; contacts/tasks/opportunities all hang from `crm_accounts`; documents can also point to `quotes`, `work_orders`, and `generated_reports`	This is the real source behind the Customers desk, account activity tabs, commercial follow-up, and SLA/service-policy snapshots.
Service contracts and routines	`service_contracts`, `routines`	Both hang from `organization_id` and `crm_account_id`; routines can point to service contract, site, system, asset, and default engineer	Explains the Routines account tab, routine health prompts, and why generated work can become either inspections or scheduler tasks.
Sites	`sites`, `site_industries`	`sites.user_id -> users.id`; `sites.organization_id -> organizations.id` nullable; `site_industries.site_id -> sites.id`	Site records are user-owned first, org-linked optionally, which is why personal-first and org workflows can both exist.
Systems	`systems`	`systems.site_id -> sites.id`	Systems are the bridge between site context and asset/test context.
Assets	`assets`	`assets.site_id -> sites.id`; `assets.system_id -> systems.id`	Asset detail, checklist generation, QR lookup, and defect linkage all depend on this layer staying clean.

Inspection, defect, and specialist testing backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Inspections	`inspections`, `inspection_items`, `inspection_visit_notes`	`inspections.site_id/system_id/engineer_id`; `inspection_items.inspection_id -> inspections.id`; `inspection_visit_notes.inspection_id -> inspections.id`	This is the central office-to-field handoff record. The inspection owns schedule state, completion state, certificates, checklist rows, and visit-note timelines.
Defects and asset condition	`defects`, `asset_inspection_status`	`defects.inspection_id -> inspections.id`; `defects.asset_id -> assets.id`; `asset_inspection_status.inspection_id/asset_id`	Defects are inspection-born records that later drive remediation, reporting, and work-order follow-up.
Flow-rate support	`flow_rate_measurements`	`flow_rate_measurements.inspection_id -> inspections.id`; `asset_id -> assets.id`	This is the persisted specialist measurement layer behind flow-rate screens and reporting.
PDS specialist work	`pds_compliance_standards`, `pds_system_configs`, `pds_test_sessions`, `pds_floors`, `pds_pressure_readings`, `pds_door_force_tests`	`pds_system_configs.system_id -> systems.id`; `pds_test_sessions.inspection_id -> inspections.id`; floors hang from sessions; readings/tests hang from floors	This is the specialist pressure-differential data model behind the wizard/config/test flows.

Commercial and finance backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Quotes	`quotes`, `quote_line_items`, `quote_settings`, `quote_parts_catalog`	`quotes.inspection_id/site_id/crm_account_id`; `quote_line_items.quote_id -> quotes.id`; line items can point to `defects.id`	Quotes can start from inspection, CRM, site, or certificate context, then carry defect-derived line items forward.
Quote approvals	`quote_approval_grants`, `quote_approval_responses`, `quote_approval_events`, `quote_approval_attachments`	Grants point to `quotes` and carry token hash, invite, expiry, and status; responses and events hang from grants and quotes	Explains public quote approval links, manual decisions, response audit trail, and attachment handling without exposing raw tokens.
Work orders	`work_orders`, `work_order_items`, `work_order_assignments`	`work_orders.quote_id -> quotes.id`; `work_orders.site_id -> sites.id`; `work_order_items.work_order_id -> work_orders.id`; assignments hang off work orders and users	Work orders are not separate “draft documents”; they are live operational rows with schedule and deployment state.
Receivables and invoice records	`crm_invoices`, `crm_invoice_payments`	`crm_invoices.account_id -> crm_accounts.id`; invoices optionally point to `quotes.id` or `work_orders.id`; `crm_invoices.source_key` identifies the source; payments point to invoices	This is the finance desk’s AR model, ready-to-invoice candidate dedupe layer, and bridge from commercial/execution work into billing state.
Contractor access and submissions	`contractors`, `contractor_access_grants`, `contractor_access_events`, `contractor_submissions`	Contractors and grants are org-scoped; grants point to work orders and optional recipient users; submissions point to grants, contractors, and work orders	This model lets an external app user receive a native job pack without joining the sending organization, then submit staged evidence for admin review.

Scheduler and cross-surface handoff backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Scheduler tasking	`scheduler_tasks`, `scheduler_changes`	`scheduler_tasks.organization_id`; `assigned_to_id -> users.id`; `site_id -> sites.id`; `system_id -> systems.id`; optional `routine_id`	Explains office-side planner work that is not itself an inspection or work order yet, including routine-generated planner tasks.
Scheduler buckets	`scheduler_buckets` plus `bucket_id` on scheduler-backed work	Buckets hang from `organization_id`; inspections, work orders, and tasks can point to a bucket	Explains planning lanes for demand that is grouped before it is assigned to a named field resource.
Deployment batches	`scheduler_deployment_batches`, `scheduler_deployment_items`, `scheduler_notification_deliveries`	deployment items hang off batches and optionally point to engineers; notifications hang off batches and engineers	These tables explain how draft scheduling becomes engineer-visible deployed work.
Customer appointment notifications	`scheduler_customer_notifications`	Notifications hang from organization plus scheduler entity type/id and optional customer contact	Explains customer appointment notification history for exact-dated offered or confirmed visits.
Embedded scheduling state on live work	`inspections.deployment_state`, `work_orders.deployment_state`, booking status, engineer visibility, bucket, and routine fields	live work rows track deployer, last deployment batch, booking status, field visibility, and routine generation identity	These fields explain why work can exist but still not be field-live yet, why locked visits block movement, and why generated routine work can be traced.

Customer handover backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Customer handover packages	`customer_handover_packages`, `customer_handover_package_items`, `customer_handover_grants`, `customer_handover_events`	Packages point to organization, site, account, and creator; items point to source records by source type/id; grants point to CRM contact, customer user, or invited email	Explains Client Handover package readiness, publication, customer portfolio access, downloads, and package activity.

Output, reporting, and branding backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Generated reports	`generated_reports`	`generated_reports.user_id -> users.id`; optionally `organization_id -> organizations.id`; `site_id -> sites.id`	Report Writer and report-review flows operate on draft and approved report rows here.
Certificates	Inspection-level certificate fields on `inspections`	`inspections.certificate_number`, `certificate_url`, `signature_data`	Certificates are primarily inspection outputs, not a separate standalone table.
PDF templates and branding	`document_templates`, `document_template_versions`, `organization_document_template_assignments`, `organization_pdf_branding`, `document_render_history`	versions hang from templates; assignments/branding hang from organizations; render history can point to template and version	Document Templates and downstream output quality are driven by these tables.
Email delivery tracking	`email_delivery_events`	optional `organization_id`, `user_id`, plus `resource_type/resource_id`	Explains why report/certificate/notification delivery can be audited after the fact.

Integrations, APIs, and automation backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Connected systems	`integration_connections`, `integration_entity_maps`, `integration_sync_log`	maps and sync logs hang from `integration_connections.id`	These tables explain the Integrations and Connected Systems desks.
Imports	`integration_import_runs`, `integration_import_rows`	import rows hang from runs; runs hang from organizations and creators	This is the staged-import model behind customer/site/system/asset/work-order bulk onboarding.
API credentials	`api_keys`	`api_keys.user_id -> users.id`	API keys are user-scoped machine credentials, not org-global keys.
Webhooks	`webhooks`, `webhook_deliveries`	`webhooks.user_id -> users.id`; deliveries hang from `webhooks.id`	Webhook configuration and retry history are persisted here.

Collaboration, AI, and support backbone

Domain	Main tables	Key FK spine	Why it matters in the product atlas
Collaboration history	`page_history_events`, `page_comments`, `mention_notifications`	history uses `resource_type/resource_id`; comments can anchor to a history event; mentions point to comment and recipient user	This is the current collaboration-history model behind Activity Feed and the notification bell.
Audit-comment legacy layer	`audit_log`, `page_history_comments`, `page_history_comment_mentions`	comment threads hang from audit-log entries and mention rows hang from comments	This is still relevant when tracing older audit-thread behavior.
AI conversations	`ai_usage_log`, `ai_conversations`, `ai_messages`, `ai_pending_confirmations`	conversations point to `user_id` and optionally `inspection_id` or `site_id`; messages hang from conversations	These tables ground AI Assistant and AI Chat in real site/inspection context rather than floating standalone threads.
External AI channels	`ai_channel_links`, `ai_channel_link_codes`, `ai_channel_event_receipts`	user and org scoped, with provider-specific link and receipt state	Explains Telegram-style linked-channel behavior.
Public and embedded chat	`public_chat_sessions`, `public_chat_messages`, `public_chat_usage_log`, plus embed tables	sessions own messages and usage logs	This is adjacent to the customer-facing docs set, but it matters when tracing AI/reporting data lineage.

Multi-tenant rules that explain product behavior

Pattern	What it means in practice
Required `organization_id` tables	CRM, org-admin, policy, and most billing/admin tables are hard tenant-scoped and usually cascade on org delete.
Nullable `organization_id` tables	Many personal-first operational tables such as `sites`, `work_orders`, scheduler tables, AI tables, reports, and collaboration tables can survive outside an org or remain after org detachment.
User-owned operational records	`sites.user_id`, `quotes.user_id`, `work_orders.user_id`, and many AI/API records explain why personal and org workflows coexist.
Role resolution lives in membership, not in the core user row	`organization_members.role`, `can_manage_schedule`, and `can_be_scheduled` are what matter for workspace behavior.

Media and sync caveats

Deucalion does not rely on one big customer-facing media table for core inspection evidence. Instead, evidence is usually embedded on the owning records:

assets.photo_urls
inspection_items.photo_urls
defects.photo_urls
asset_inspection_status.photo_urls
report-style image fields inside generated_reports

That matters for docs because the “owner screen” for evidence is usually also the “owner table” for the persisted photo references.

Local-first behavior that is not a dedicated server table

Draft Manager represents local unsynced inspection work before it hardens into server-side inspection, note, defect, or evidence records.
Pending photo uploads are a sync-state concern first; once uploaded, their references are stored on the owning row rather than in a separate evidence table.
Certificate generation is mostly inspection-backed plus template-backed, not a separate certificate-record family.