Skip to main content

Database Schema

Navaid's checked-in schema is an ordered chain of 72 migrations in supabase/migrations/, from 20260407130324_initial_schema.sql through 20260627170000_security_audit_remediation_run6b.sql.

Those migrations create 31 tables in public. This page summarizes the current end state; old CREATE TABLE statements must always be read together with later ALTER, policy, grant, trigger, and function migrations.

Schema Conventions​

  • IDs are UUIDs generated with gen_random_uuid() unless tied to auth.users.
  • Timestamps are timestamptz.
  • Tenant-owned rows carry company_id.
  • A membership grants access only when status = 'active'.
  • Company roles are company_admin, company_consultant, company_manager, and company_user.
  • company_consultant is intentionally admin-equivalent.
  • AI-derived and pipeline-owned columns are protected by database triggers, not merely hidden in the UI.
  • SECURITY DEFINER functions pin search_path and verify the caller internally.
  • Service-role-only tables enable RLS with no client policy.

Complete Table Inventory​

Identity and tenancy​

TablePurposeImportant controls
usersApplication profile mapped one-to-one to auth.usersOwn profile access, company-member visibility, system-admin global read
companiesTenant configuration, logo, email domains, default budgets, document-retention flagActive membership read; admin update; budget columns guarded to system admin
company_membershipsMany-to-many user/company roles and statusActive membership helpers avoid recursive RLS
invitationsPer-email company invitation and secret tokenAdmin mutation; token preview through scoped RPC; email domain trigger
invite_codesReusable signup codes, capacity and revocationSystem-admin management only
invite_signup_reservationsShort-lived preflight reservation tying email/codeService role and Auth hook only
invite_code_consumptions(code,email) idempotency ledgerService role only
early_access_registrationsPublic pilot-interest captureWrites through Edge Function; admin read/update

Conversations and maturity​

TablePurposeImportant controls
sessionsConversation owner/company, state, snapshots, summary and multiplayer metadataOwner/participant/company-role policies; pipeline column guards
messagesOrdered user/assistant/system turns, signals and attributionUser turns pinned to auth.uid(); assistant writes are server-side
readiness_snapshotsLegacy three-state readiness historySession owner scope
maturity_snapshotsFive-level company maturity progressionCompany-member read; pipeline write
session_sharesRevocable share-token capabilitiesOnly owner/company admin can mint/manage
session_participantsMultiplayer membership, role and seen stateJoin/participant policies; leave/remove through Edge Function
tasksRecommended maturity next steps and completion stateCompany-member read; manager-plus update rules

Company knowledge and intelligence​

TablePurposeImportant controls
company_profilesShared facts, learnings, readiness and authoritative maturity stateMember read; derived columns pipeline-owned
company_profile_historyVersioned append-only knowledge/maturity auditMember read; server/admin pipeline writes
company_documentsUpload metadata and optional R2 stateMember read/own insert; uploader/admin mutation; quota/storage guards
competitor_analysesGrounded competitor discovery and analysis resultsMember read/create; creator/admin update/delete

Usage and budgets​

TablePurposeImportant controls
token_usagePer-call audit recordUser can read own rows
token_usage_dailyDaily aggregateUser can read own rows; server writes
token_usage_monthlyBudget-enforcement aggregateUser can read own rows; atomic server RPC
budget_alertsThreshold notifications/acknowledgementUser can read and acknowledge own
rate_limit_countersFixed-window server abuse countersService role/RPC only

Administration and ecosystem​

TablePurposeImportant controls
prompt_versionsVersioned v1/v2 system promptsActive prompts readable; system-admin mutation
system_configBudget, feature and theme JSON valuesPublic-key allowlist; system-admin mutation
admin_audit_logServer-stamped administrative actionsSystem-admin read; writes through audited RPC
partnersActive support-partner catalogueActive rows intentionally public; admin mutation
partner_contactsPartner contact channelsPublic read; admin mutation
partner_referralsSession-to-partner referral/click trackingSession-owner scope
feedbackUser feedback plus admin triageOwn/all-admin read; triage-field insert guard

Core Tables​

users​

users.id equals the corresponding auth.users.id. Current application fields include:

  • email and full name
  • avatar URL
  • is_system_admin
  • input/output token-budget overrides
  • persisted BCP-47 locale
  • retained experience_version compatibility state
  • created/last-active timestamps

handle_new_user() creates the profile after an Auth row is inserted. The frontend has a defensive getCurrentUser() fallback that attempts to insert a minimal profile if the trigger result is not visible yet.

The profile row is not the source of company scope. Scope comes from active company_memberships.

companies​

Important current fields include:

  • name, unique slug, optional logo_url
  • JSON settings
  • allowed_email_domains
  • default input/output token budgets
  • store_documents

companies_guard_budget_columns() rejects authenticated non-system-admin changes to the default budget columns, even if another policy permits updating ordinary company settings.

allowed_email_domains is normalized and enforced when an invitation is inserted or its email/company changes. A null/empty list means any domain.

company_memberships​

A user can have multiple memberships. Important fields are:

  • company_id, user_id
  • role
  • status
  • invitation/join timestamps and actor

Use get_user_company_ids(), get_user_role(company_id), and is_company_admin(company_id) rather than recursive policy subqueries.

invitations​

Invitations contain company, email, role, inviter, secret token, expiry, acceptance, and creation timestamps.

The current default expiry is 14 days. The current role constraint includes company_consultant.

The original blanket anonymous SELECT policy was removed. Logged-out preview is provided by:

get_invitation_by_token(p_token text)

The function returns at most the unaccepted, unexpired matching invitation and company. There is no list operation for an anonymous caller.

accept_invitation(token):

  • requires an authenticated caller,
  • verifies the caller's email matches the invitation,
  • checks expiry and acceptance,
  • inserts or updates the membership,
  • marks the invitation accepted,
  • behaves robustly across retries.

Session Tables​

sessions​

The session row spans several generations of the product. Current concerns include:

  • owner and company
  • optional title and focus dimension
  • active/completion lifecycle
  • last_active_at
  • whether company knowledge is used
  • legacy readiness state
  • current maturity snapshot and at-start snapshot
  • experience version
  • total token counters
  • generated summary
  • multiplayer flag and turn/claim metadata
  • summary single-winner claim timestamp

sessions_guard_pipeline_columns() prevents ordinary authenticated writes to status, completed_at, and summary. Completion belongs to the generate-summary pipeline.

claim_session_summary() prevents two completion requests from generating and charging for duplicate summaries.

messages​

Messages contain:

  • session, role, content and creation order
  • model/token metadata
  • readiness/maturity signals
  • optional attributed user_id
  • multiplayer message kind/processing state
  • source metadata such as voice/interruption

Clients may insert user turns only. messages_pin_author_user_id() rewrites the author of authenticated client inserts to auth.uid(), closing speaker impersonation. Assistant turns are inserted by the service-role pipelines.

readiness_snapshots​

This is the legacy three-state assessment history. It remains for compatibility with older sessions. New features should use the five-level maturity model.

maturity_snapshots​

Completion writes a company maturity snapshot for progression charts. Snapshot rows are historical evidence; the live authoritative value remains company_profiles.maturity_state.

session_shares​

A share row contains a high-entropy token, session, creator, expiry/revocation state, and timestamps.

Creating a row requires ownership of the underlying session or company/system administration. Merely setting created_by = auth.uid() is insufficient. Public reads flow through the shared-session function so response fields stay allowlisted.

session_participants​

Rows identify group-session participants and participant/owner roles. Helper functions include:

  • is_session_participant(session_id)
  • can_join_session(session_id)
  • session_add_participant(session_id,user_id)
  • session_invitable_members(session_id)

The direct delete policy was removed. Leave/remove calls livekit-session-control, which deletes the row and removes connected LiveKit identities from the media plane.

tasks​

Tasks carry company, dimension, target level, title/description, priority, role, status, completion notes/actor/session and timestamps.

The tasks Edge Function lists, updates, and dismisses tasks. AI generation occurs in _shared/tasks.ts as part of chat and summary pipelines, with deduplication against open tasks.

Company Knowledge Tables​

company_profiles​

One row per company stores:

  • structured facts
  • extracted learnings
  • aggregate legacy readiness
  • authoritative maturity_state
  • optimistic version
  • session/extraction metadata

Derived fields are pipeline-owned. Authenticated edits route through update-company-facts, which verifies company-admin/consultant access, writes through the service role, and appends history.

company_profiles_guard_derived_insert() and company_profiles_guard_derived_columns() prevent clients from seeding or overwriting AI-derived state through direct PostgREST.

company_profile_history​

History captures version, facts, learnings, readiness, maturity provenance, actor, session, source, and time. Change sources include automated chat, session completion, admin edit, and MCP.

The table is append-only to ordinary clients. Direct authenticated derived-field edits by an allowed system path produce an admin_edit history record; normal service-role pipelines write their own correctly labelled rows and are not double-audited.

company_documents​

The row records:

  • company/uploader
  • filename, MIME, kind and extracted counts
  • truncation/source/session
  • storage_key and storage_status
  • measured size_bytes
  • synthesis state

R2 storage is optional. The database enforces:

  • allowed state transitions and safe key shape
  • maximum 15 MiB per object
  • maximum 500 retained documents per company
  • maximum 2 GiB retained bytes per company
  • advisory locking during quota-sensitive insert/commit

commit_document_storage() atomically validates the HEAD-measured size and commits the object key/status.

See Company Knowledge and Documents.

Token Budget Tables​

token_usage​

This is the per-operation audit trail. Every Gemini-spending path should insert a row with user/company/session context, model/surface and input/output counts.

token_usage_daily​

Daily aggregates support trend reporting and dashboards.

token_usage_monthly​

The budget gate reads the current user's monthly input/output totals. record_token_usage() updates the aggregate atomically to avoid lost increments from concurrent AI calls.

budget_alerts​

Budget threshold alerts can be acknowledged by the owning user.

rate_limit_counters​

check_rate_limit(bucket,max,window_seconds) owns fixed-window increments. Direct client execution is revoked; Edge Functions call it through the service role.

The rate limiter is a concurrency/abuse bound, not a replacement for durable monthly budgets.

Admin Tables​

prompt_versions​

Prompts are versioned by experience slot. System Admin activates one row per slot by deactivating siblings before activating/inserting the selected version. Prompt Lab test traffic still records tokens.

system_config​

Current important keys include:

KeyDefault intent
active_system_promptcompatibility/current prompt selector
theme_configruntime brand-teal accent
daily_token_budgetStored Admin UI value; no current runtime enforcement consumer
alert_configStored Admin UI thresholds; no current alert sender/consumer
voice_enabledon
multilingual_enabledoff
overview_enabledoff
multiplayer_enabledoff
aida_enabledoff
group_voice_enabledoff
mcp_enabledoff
world_cup_theme_enabledoff
company_budgets_enabledon
lead_hunt_enabledoff

The public SELECT policy is an explicit key allowlist. It includes theme_config and user-visible feature flags but not sensitive operational configuration. System admins manage rows.

admin_audit_log​

Client code calls log_admin_action(). The RPC:

  • verifies system-admin status,
  • stamps admin_user_id = auth.uid(),
  • stamps server time,
  • stores old/new context,
  • prevents the actor from being self-reported.

Direct authenticated insert is not the normal path.

Partner Tables​

partners​

Active partners are intentionally public because recommendations can render outside privileged admin flows. System admins manage catalogue rows.

partner_contacts​

Public partner contact channels linked to partners.

partner_referrals​

Records which session recommended a partner and whether the user clicked. Policies scope rows through the user's session.

Onboarding and Feedback Tables​

invite_codes​

Codes have use limits, used count, notes, creator, expiry/revocation, and timestamps. System administrators list/create/revoke them through the invite-codes Edge Function.

Code consumption occurs in the Auth hook, not through a browser action.

invite_signup_reservations​

The public preflight can reserve a valid code for an email for 15 minutes. RLS has no client policy. The Auth hook consumes/cleans reservations.

invite_code_consumptions​

The composite primary key (code,email) makes retries idempotent: the same email does not burn a second use after a partially repeated signup.

early_access_registrations​

Stores email, locale, source, actioned state/actor and timestamps. Public capture is mediated by an Edge Function with validation/rate limiting.

feedback​

Users submit and read their own feedback; system admins triage all rows. A restrictive insert policy forces new submissions to start with default triage state and no admin notes.

Row Level Security Strategy​

RLS is enabled across application tables. The main patterns are:

Own-row​

user_id = auth.uid()

Used for profiles, usage, alerts, referrals, and user-owned sessions.

Active company membership​

company_id IN (SELECT public.get_user_company_ids())

The helper runs with a pinned search path and avoids recursive policy reads.

Role-aware company access​

public.is_company_admin(company_id)

Admin-equivalent means system admin, company admin, or company consultant. Manager-plus checks include company manager where appropriate.

Session relationship​

Policies join session ownership, active company membership, and participant state. A UUID alone never grants access.

Public capability​

Public access is deliberately narrow:

  • known invitation token through a scoped RPC
  • valid session-share token through the share function
  • active partners/contact information
  • active prompts and allowlisted public configuration
  • early-access capture through validated Edge Function
  • MCP discovery/challenge metadata

Never add USING (true) to a table containing a secret token merely because the page using it is public.

Service-role-only​

For reservations, consumption ledgers, rate counters, aggregates, and pipeline history, RLS plus revoked grants ensures browser clients have no path.

RPC and Trigger Catalogue​

The migration chain defines 50 named functions. They fall into these groups.

Authorization and membership​

is_system_admin
get_user_role
get_user_company_ids
is_company_admin
company_has_members
is_session_participant
can_join_session
session_add_participant
session_invitable_members

Onboarding and account lifecycle​

handle_new_user
hook_before_user_created
email_exists
consume_invite_code
get_invitation_by_token
accept_invitation
create_company_with_admin
delete_own_account

Administration​

admin_company_experience_breakdown
admin_set_company_experience
company_set_member_experience
admin_set_user_experience
admin_set_platform_experience
admin_company_members
admin_all_company_members
admin_delete_company
log_admin_action

Some experience-version functions remain for migration/compatibility history; v2 is the active product path.

Usage and concurrency​

record_token_usage
company_token_usage
company_daily_usage
check_rate_limit
claim_session_turn
claim_session_summary
commit_document_storage

Demo lifecycle​

delete_demo_company
sweep_demo_companies

Guard/audit/maintenance triggers​

update_updated_at
update_user_last_active
update_session_last_active
tasks_set_updated_at
feedback_set_updated_at
enforce_experience_version_change
companies_guard_budget_columns
sessions_guard_pipeline_columns
company_profiles_guard_derived_insert
company_profiles_guard_derived_columns
company_profiles_audit_direct_edit
messages_pin_author_user_id
invitations_enforce_email_domains
company_documents_guard_storage_columns
company_documents_enforce_quota

Storage Buckets​

avatars​

The public bucket stores:

  • user images at <user-id>/<safe-name>
  • company logos at companies/<company-id>/<safe-name>

Uploads validate image extension, MIME, and size in the frontend helper. Policies allow owners to manage their own avatar and company admins/consultants to manage company logos. Public object delivery remains available, while authenticated object listing is scoped to prevent whole-bucket enumeration.

Company document originals do not use Supabase Storage; optional retention uses R2.

Index Strategy​

Migrations add indexes for:

  • membership and role lookups
  • invitation token/email lookups
  • session owner/company/activity queries
  • ordered message and participant reads
  • monthly/daily company usage
  • profile and history access
  • task dimension/status queries
  • company document creation and quota scans
  • competitor and feedback listing

When adding an RPC or RLS policy, inspect its filter/join columns. A secure SECURITY DEFINER function can still become an availability problem if it forces large sequential scans.

Migration Workflow​

Create:

npx supabase migration new descriptive_name

Verify locally:

npx supabase db reset

Apply remotely only when authorized:

npx supabase db push

Rules:

  1. Migrations are append-only after shared deployment.
  2. Use idempotent DROP ... IF EXISTS when replacing policies/triggers.
  3. Pin search_path in SECURITY DEFINER functions.
  4. Revoke implicit PUBLIC execution before granting intended roles.
  5. Add restrictive policies or triggers for column-level invariants.
  6. Test hostile cross-tenant identifiers.
  7. Update TypeScript types, service select lists, tests, and this reference.

Troubleshooting​

RLS recursion​

Do not query company_memberships recursively inside its own policy. Use the existing SECURITY DEFINER helpers.

Service role works but browser fails​

This usually means a missing grant or RLS policy. Do not weaken RLS until the browser's actor, operation, company and expected policy are explicit.

Client can update ordinary company settings but budgets fail​

Expected: a column guard restricts budget columns to system administrators. Omit protected fields from tenant-admin updates.

Profile update is rejected​

Expected for derived fields. Use update-company-facts for authorized manual facts/learnings edits and the maturity pipeline for assessment state.

Migration count differs remotely​

This guide proves only the repository chain. Compare remote migration history before claiming a hosted environment is current.