Find Supabase schema records

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.

Find customer portal tables

0010_agency_os_spine.sql defines the base portal records:

Table	Purpose
client_memberships	Customer authorization grant, role, approval capability, and portal-management capability
client_updates	Published customer updates
client_requests	Current request status, assignment, next-response side, due date, resolution, and revision
deliverables	Base deliverable and pointer to its current immutable version
deliverable_reviews	Append-only comments and decisions for a released version

20260911000449_customer_portal_collaboration.sql adds durable collaboration records:

Table	Purpose
client_request_events	Immutable customer replies, internal notes, assignment, status, and resolution events
client_request_attachments	Private file metadata, event binding, byte validation, and cleanup lease state
deliverable_versions	Immutable released content snapshots and version numbers
deliverable_review_states	Current status, decision receipt, due date, and optimistic revision for each version
deliverable_release_receipts	Immutable idempotent release receipts
client_milestones	Current shared outcome, owner, due date, blocker, commitments, visibility, and revision
client_milestone_events	Immutable idempotent create and update receipts
portal_notification_outbox	Transactional event, recipient rule, approval, retry, and claim state
portal_notifications	Person-scoped in-app inbox and read state
portal_notification_preferences	Email choice, digest cadence, categories, quiet hours, time zone, and revision
portal_notification_deliveries	Email or digest provider, lease, batch, retry, suppression, failure, and delivery state
portal_access_invitations	Recipient, membership, role, Auth identity, redirect, provider, and invitation lifecycle
portal_client_visits	Previous and current visit baseline for the Overview change feed

The same migration adds can_approve_deliverables and can_manage_portal to client_memberships; request ownership, resolution, and revision fields to client_requests; and current_version_id to deliverables.

20260911024049_portal_resource_activation.sql adds:

Table	Purpose
portal_resource_publication_events	Immutable explicit document or meeting publication and withdrawal history
portal_update_publication_receipts	Immutable idempotent receipts for direct customer updates

Customer routes read these tables through authorization and publication projections. A deliverable needs both visible_to_client = true and a valid current_version_id. Released documents and sources also need an allowed project and release marker. Raw document bodies, storage paths, source transcripts, and internal request events stay out of customer payloads.

Publication workflows use list_portal_publication_inventory, get_portal_publication_counts, mutate_portal_resource_publication, and publish_portal_update. The customer resource projection uses list_portal_resources, which returns curated publication metadata and enforces active release state.

20260911024549_unify_deliverable_release_approval.sql adds release_deliverable_approval_to_portal. The service-role RPC locks the pending approval, validates the exact deliverable and linked source-draft gates, and creates the immutable version, review state, release receipt, approval feedback, and notification outbox rows as one transaction.

20260923175000_portal_policies_and_links.sql adds the Client Portal Essentials tables. Both are staff-read only under RLS (admin and lead); customers reach their tab policy and links only through the service-role portal payload and /api/portal-links, which apply project scope. Writes run as service_role from server handlers.

Table	Purpose
portal_policies	One row per client; a client without a row behaves as the defaults below
portal_links	A lead's ordered important links for a client: title, http(s) URL, optional note, optional project, position, and archived_at

portal_policies columns:

Column	Meaning
client_id, org_id	Primary key and owning organization
enabled_views	Tabs the customer sees, from this_week, overview, plan, reviews, requests, resources; at least one. Default is the five tabs without this_week
landing_view	Tab customers open first; must be in enabled_views. Default overview
auto_release_client_meetings	Automatic sharing of client-domain meetings. Default off
auto_release_enabled_at	When automatic sharing was turned on; the backfill window counts back from here
auto_release_include_recording_link	Also share the recording link. Default off; only valid while automatic sharing is on
auto_draft_weekly_update	Draft a weekly update each Friday for review. Default off
weekly_update_format	weekly_wrap. The column still admits the retired standard (and defaults to it), but the app reads standard as weekly_wrap and every save writes weekly_wrap
meeting_backfill_days	How many days of meetings before auto_release_enabled_at may be shared, 0 to 365. Default 30
updated_by, updated_at	The lead who last saved it (the acting person for automation) and the save time, used for optimistic concurrency

20260923180100_auto_release_client_meetings.sql adds the auto_release_client_meetings(p_client_id) RPC, executable by service_role only. It shares the title and summary of eligible client-domain meetings through mutate_portal_resource_publication, so each release is an ordinary, withdrawable publication attributed to updated_by. It shares the recording link only when auto_release_include_recording_link is on, never shares the transcript, and never re-releases a meeting staff already published or withdrew.

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.