Skip to main content

Schema Reference

This is a practical reference, not a replacement for the migrations.

clients and client_external_refs​

clients is the canonical account record. client_external_refs stores stable account-level identities such as a HubSpot company ID so connector matching does not depend on a mutable name. Lead/admin account creation writes the client, initial project, memberships, operating profile, signals, external references, idempotency record, and audit event in one database transaction.

projects​

Important fields:

  • id
  • org_id
  • client_id
  • name
  • code
  • slug
  • asana_gid
  • toggl_project_id
  • hours_logged
  • hours_30d
  • hours_synced_at
  • project_category
  • budget_hours
  • budget_amount
  • actual_revenue
  • actual_cost
  • rate_card_id

sources​

Important fields:

  • id
  • org_id
  • project_id
  • type
  • title
  • url
  • occurred_at
  • external_id
  • body
  • metadata

Fathom transcripts go in body.

approvals​

Important fields:

  • id
  • org_id
  • project_id
  • kind
  • code
  • title
  • body
  • status
  • payload
  • edited_payload
  • result
  • source_id
  • confidence
  • proposed_by
  • approved_by
  • created_at
  • resolved_at

documents​

Important fields:

  • id
  • org_id
  • project_id
  • title
  • category
  • source_system
  • source
  • storage_path
  • body
  • embedding
  • restricted
  • mime_type
  • byte_size
  • checksum_sha256
  • source_url
  • external_id
  • source_modified_at
  • indexed_status
  • indexed_at
  • index_error
  • version
  • supersedes_document_id
  • metadata

knowledge_chunks​

Chunk-level semantic retrieval for documents, transcripts, spreadsheets, and future source text.

Important fields:

  • org_id
  • project_id
  • document_id
  • source_id
  • source_type
  • chunk_index
  • text
  • metadata
  • restricted
  • embedding_model
  • embedding
  • content_hash
  • indexed_at

spreadsheet_rows​

Structured workbook extraction for XLSX/XLSM/CSV/TSV files. Spreadsheets are not flattened like documents; rows retain workbook, sheet, table/range, row, columns, formulas, and display values.

Important fields:

  • org_id
  • project_id
  • document_id
  • workbook_name
  • sheet_name
  • table_name
  • row_number
  • cell_range
  • columns
  • display_columns
  • formulas
  • normalized_text
  • metadata
  • restricted

ingest_jobs​

Operational ledger for document/source indexing attempts.

Important fields:

  • org_id
  • project_id
  • document_id
  • source_id
  • source_type
  • status
  • attempt_count
  • last_error
  • requested_by
  • started_at
  • finished_at

retrieval_events​

Query-level retrieval telemetry for search and Ask SABLE.

Important fields:

  • org_id
  • actor_person_id
  • query
  • filters
  • result_chunk_ids
  • candidate_count
  • reranked_count
  • latency_ms
  • embedding_model
  • rerank_model

rag_eval_questions and rag_eval_runs​

Lead/admin evaluation tables for recall, citation correctness, leakage, and latency checks.

Important fields:

  • question
  • expected_source_type
  • expected_document_id
  • expected_source_id
  • expected_answer_contains
  • restricted
  • recall_at_10
  • citation_correct
  • leakage_detected

audit​

Used for external action trail and webhook replay checks.

Important fields:

  • org_id
  • actor
  • action
  • detail
  • created_at

For Fathom replay, detail.webhook_id is unique where action = 'fathom_webhook'.

Agency OS Pricing Tables​

0010_agency_os_spine.sql adds:

  • project_categories
  • rate_cards
  • budget_lines
  • estimate_cases
  • scope_events

These tables turn outcome estimates from comparable-hour averages into a learning loop with project taxonomy, SOW budget lines, estimate cases, actual margin, and scope-change causes.

Client Portal Tables​

0010_agency_os_spine.sql also adds:

  • client_memberships
  • client_updates
  • client_requests
  • deliverables

Client users should read through /api/client-portal. The portal payload excludes internal decisions, promises, and unrestricted spine rows by default. Released documents and sources can appear in the portal only when they are scoped to one of the client user's allowed projects; raw document bodies, storage paths, and source transcripts stay out of the client payload.

Deliverable Authoring Tables​

0020_deliverable_authoring.sql adds:

  • deliverable_authoring_profiles
  • deliverable_canonical_targets
  • deliverable_templates
  • contract_obligations
  • deliverable_drafts
  • deliverable_evidence
  • deliverable_revision_events

Contracts/SOWs are extracted into obligations and budget lines for lead-only drafting. Drafts keep their evidence citations separate from the rendered body so reviewers can inspect source grounding before an approval publishes an update or releases a deliverable.

Authoring profiles store reusable client/project policy: required sections, standing facts, banned client-facing terms, source collection rules, QA policy, canonical export policy, and known out-of-scope items. Canonical targets identify the intended file destination and overwrite/versioning behavior for living deliverables such as a single Drive-backed SOW. Revision events record the in-document change history to preserve the old living-SOW workflow without keeping it as prompt-only behavior.