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

Table	Purpose	Important controls
users	Application profile mapped one-to-one to auth.users	Own profile access, company-member visibility, system-admin global read
companies	Tenant configuration, logo, email domains, default budgets, document-retention flag	Active membership read; admin update; budget columns guarded to system admin
company_memberships	Many-to-many user/company roles and status	Active membership helpers avoid recursive RLS
invitations	Per-email company invitation and secret token	Admin mutation; token preview through scoped RPC; email domain trigger
invite_codes	Reusable signup codes, capacity and revocation	System-admin management only
invite_signup_reservations	Short-lived preflight reservation tying email/code	Service role and Auth hook only
invite_code_consumptions	(code,email) idempotency ledger	Service role only
early_access_registrations	Public pilot-interest capture	Writes through Edge Function; admin read/update

Conversations and maturity

Table	Purpose	Important controls
sessions	Conversation owner/company, state, snapshots, summary and multiplayer metadata	Owner/participant/company-role policies; pipeline column guards
messages	Ordered user/assistant/system turns, signals and attribution	User turns pinned to auth.uid(); assistant writes are server-side
readiness_snapshots	Legacy three-state readiness history	Session owner scope
maturity_snapshots	Five-level company maturity progression	Company-member read; pipeline write
session_shares	Revocable share-token capabilities	Only owner/company admin can mint/manage
session_participants	Multiplayer membership, role and seen state	Join/participant policies; leave/remove through Edge Function
tasks	Recommended maturity next steps and completion state	Company-member read; manager-plus update rules

Company knowledge and intelligence

Table	Purpose	Important controls
company_profiles	Shared facts, learnings, readiness and authoritative maturity state	Member read; derived columns pipeline-owned
company_profile_history	Versioned append-only knowledge/maturity audit	Member read; server/admin pipeline writes
company_documents	Upload metadata and optional R2 state	Member read/own insert; uploader/admin mutation; quota/storage guards
competitor_analyses	Grounded competitor discovery and analysis results	Member read/create; creator/admin update/delete

Usage and budgets

Table	Purpose	Important controls
token_usage	Per-call audit record	User can read own rows
token_usage_daily	Daily aggregate	User can read own rows; server writes
token_usage_monthly	Budget-enforcement aggregate	User can read own rows; atomic server RPC
budget_alerts	Threshold notifications/acknowledgement	User can read and acknowledge own
rate_limit_counters	Fixed-window server abuse counters	Service role/RPC only

Administration and ecosystem

Table	Purpose	Important controls
prompt_versions	Versioned v1/v2 system prompts	Active prompts readable; system-admin mutation
system_config	Budget, feature and theme JSON values	Public-key allowlist; system-admin mutation
admin_audit_log	Server-stamped administrative actions	System-admin read; writes through audited RPC
partners	Active support-partner catalogue	Active rows intentionally public; admin mutation
partner_contacts	Partner contact channels	Public read; admin mutation
partner_referrals	Session-to-partner referral/click tracking	Session-owner scope
feedback	User feedback plus admin triage	Own/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:

Key	Default intent
active_system_prompt	compatibility/current prompt selector
theme_config	runtime brand-teal accent
daily_token_budget	Stored Admin UI value; no current runtime enforcement consumer
alert_config	Stored Admin UI thresholds; no current alert sender/consumer
voice_enabled	on
multilingual_enabled	off
overview_enabled	off
multiplayer_enabled	off
aida_enabled	off
group_voice_enabled	off
mcp_enabled	off
world_cup_theme_enabled	off
company_budgets_enabled	on
lead_hunt_enabled	off

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.