Reference An information-oriented lookup resource. Use it to find specific values and definitions.
all database schema tables supabase atlas
Database Table Atlas Map the customer-facing product atlas back to the main Supabase-backed Postgres tables so teams can see which persisted records power each workflow.
Before you start You need to understand the persisted model behind the routes, screens, and outputs. Expected outcome Teams can trace each major workflow back to the tables, foreign keys, and tenancy rules that actually carry it.
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.
Domain Main tables Key FK spine Why it matters in the product atlas Users and personal identity users, push_tokenspush_tokens.user_id -> users.idThe mobile app, personal subscriptions, AI usage, and user-scoped API/webhook ownership all start here. Organizations and membership organizations, organization_members, organization_invitationsorganization_members.organization_id -> organizations.id; organization_members.user_id -> users.id; organization_invitations.organization_id -> organizations.idExplains 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_overridesBoth 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_transactionsBoth 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.
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_templatescrm_accounts.organization_id -> organizations.id; contacts/tasks/opportunities all hang from crm_accounts; documents can also point to quotes, work_orders, and generated_reportsThis is the real source behind the Customers desk, account activity tabs, commercial follow-up, and SLA/service-policy snapshots. Sites sites, site_industriessites.user_id -> users.id; sites.organization_id -> organizations.id nullable; site_industries.site_id -> sites.idSite records are user-owned first, org-linked optionally, which is why personal-first and org workflows can both exist. Systems systemssystems.site_id -> sites.idSystems are the bridge between site context and asset/test context. Assets assetsassets.site_id -> sites.id; assets.system_id -> systems.idAsset detail, checklist generation, QR lookup, and defect linkage all depend on this layer staying clean.
Domain Main tables Key FK spine Why it matters in the product atlas Inspections inspections, inspection_items, inspection_visit_notesinspections.site_id/system_id/engineer_id; inspection_items.inspection_id -> inspections.id; inspection_visit_notes.inspection_id -> inspections.idThis 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_statusdefects.inspection_id -> inspections.id; defects.asset_id -> assets.id; asset_inspection_status.inspection_id/asset_idDefects are inspection-born records that later drive remediation, reporting, and work-order follow-up. Flow-rate support flow_rate_measurementsflow_rate_measurements.inspection_id -> inspections.id; asset_id -> assets.idThis 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_testspds_system_configs.system_id -> systems.id; pds_test_sessions.inspection_id -> inspections.id; floors hang from sessions; readings/tests hang from floorsThis is the specialist pressure-differential data model behind the wizard/config/test flows.
Domain Main tables Key FK spine Why it matters in the product atlas Quotes quotes, quote_line_items, quote_settings, quote_parts_catalogquotes.inspection_id/site_id/crm_account_id; quote_line_items.quote_id -> quotes.id; line items can point to defects.idQuotes can start from inspection, CRM, site, or certificate context, then carry defect-derived line items forward. Work orders work_orders, work_order_items, work_order_assignmentswork_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 usersWork orders are not separate “draft documents”; they are live operational rows with schedule and deployment state. Receivables and invoice records crm_invoices, crm_invoice_paymentscrm_invoices.account_id -> crm_accounts.id; invoices optionally point to quotes.id or work_orders.id; payments point to invoicesThis is the finance desk’s AR model and the bridge from commercial/execution work into billing state.
Domain Main tables Key FK spine Why it matters in the product atlas Scheduler tasking scheduler_tasks, scheduler_changesscheduler_tasks.organization_id; assigned_to_id -> users.id; site_id -> sites.id; system_id -> systems.idExplains office-side planner work that is not itself an inspection or work order yet. Deployment batches scheduler_deployment_batches, scheduler_deployment_items, scheduler_notification_deliveriesdeployment 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. Embedded deployment state on live work inspections.deployment_state, work_orders.deployment_stateboth also track deployer and last deployment batch These fields explain why work can exist but still not be field-live yet.
Domain Main tables Key FK spine Why it matters in the product atlas Generated reports generated_reportsgenerated_reports.user_id -> users.id; optionally organization_id -> organizations.id; site_id -> sites.idReport 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_dataCertificates 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_historyversions hang from templates; assignments/branding hang from organizations; render history can point to template and version Composer and downstream output quality are driven by these tables. Email delivery tracking email_delivery_eventsoptional organization_id, user_id, plus resource_type/resource_id Explains why report/certificate/notification delivery can be audited after the fact.
Domain Main tables Key FK spine Why it matters in the product atlas Connected systems integration_connections, integration_entity_maps, integration_sync_logmaps and sync logs hang from integration_connections.id These tables explain the Integrations and Connected Systems desks. Imports integration_import_runs, integration_import_rowsimport 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_keysapi_keys.user_id -> users.idAPI keys are user-scoped machine credentials, not org-global keys. Webhooks webhooks, webhook_deliverieswebhooks.user_id -> users.id; deliveries hang from webhooks.idWebhook configuration and retry history are persisted here.
Domain Main tables Key FK spine Why it matters in the product atlas Collaboration history page_history_events, page_comments, mention_notificationshistory 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_mentionscomment 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_confirmationsconversations 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_receiptsuser 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 tablessessions own messages and usage logs This is adjacent to the customer-facing docs set, but it matters when tracing AI/reporting data lineage.
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.
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_urlsinspection_items.photo_urlsdefects.photo_urlsasset_inspection_status.photo_urlsreport-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.
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.