Data model and migrations

The schema is hand-written SQL in apps/api/migrations; apps/api/src/db/schema.ts mirrors it for typed Drizzle queries. Every table has row-level security enabled.

Migration	Adds
0001_init.sql	The app schema, the workbench_app role, every core table, the RLS helper functions, policies and grants.
0002_reference_and_prompts.sql	reference_versions, prompt_versions, datasets.reference_version, and prompt_version on dataset_ai and classification_cache.
0003_ai_choice_and_pii_grants.sql	datasets.use_ai (the upload's AI choice), app.own_pii_membership_ids(), and engagement_members insert and update policies that stop anyone granting themselves PII access.
0004_own_name.sql	app.set_own_name(), so people can set their own display name without a self-update policy on users.
0005_client_brands.sql	client_brands: a client's deliverable brand, readable by everyone, set by administrators, the client's creator or a lead of one of its engagements.
0006_fte_attributes_report_notes.sql	dataset_employees.fte (0–1, null = 1) and dataset_employees.attributes (client groupings, a JSON object), and engagements.report_notes (the lead's report wording). New columns inherit the tables' existing RLS.

Entities

users ──< engagement_members >── engagements >── clients
                                     │
               ┌─────────────────────┼───────────────────────┬──────────────┐
            datasets            title_overrides          audit_log      ai_usage
               │  └── reference_version ──▶ reference_versions
   ┌───────────┼──────────────┬───────────────────┬──────────────┬─────────────┐
dataset_   dataset_       dataset_titles     employee_       copilot_plans  dataset_ai
chunks     employees                         identities

classification_cache   (shared across engagements; titles only)
reference_versions     (platform: versioned reference data)
prompt_versions        (platform: versioned AI prompts)

Tables

Table	Holds	Notes
users	Davies staff: email (lower-case), name, platform role (admin, member), active, last_seen_at	Provisioned on first sign-in from an allowed domain, or added by an admin.
clients	Client organisations: name (unique, case-insensitive), sector, notes, archived_at	Readable by every signed-in user so engagements can be filed against them.
engagements	Industry pack, region, status (active, closed, purged), share_classifications, include_in_benchmarks, retention_days (0–3650, default 90), report_notes (0006: the lead's report wording), dek_wrapped (the wrapped identity key), closed_at, purged_at	Name unique per client. dek_wrapped is nulled on purge (crypto-shredding). report_notes is the engagement's own text, changed under the lead-only update policy; the API changes it only while the engagement is active, and a purge clears it.
engagement_members	role (lead, analyst, viewer) and pii_access per user per engagement	The access boundary. PII access is never implied, not even for admins, and nobody can grant it to themselves.
datasets	One uploaded staff list: status (uploading, classifying, ready, purged), settings (model assumptions), column_mapping, expected_rows, uploaded_rows, has_identities, use_ai, headcount, total_cogs, the cached summary, reference_version	The summary outlives row-level data after a purge.
dataset_chunks	Which upload chunks have arrived	Makes chunk upload idempotent.
dataset_employees	One row per person: employee ID, manager's employee ID, job title, business, division, sub-division, department, location, country, tenant, base salary, FTE (fte, 0006), up to five client groupings (attributes, 0006)	No names or emails, ever. Groupings are categories: attributesProblem in core refuses personal-data headings, emails and long values, in the browser and on every API row, and completeUpload refuses a grouping with so many values it identifies people.
dataset_titles	Each distinct title: normalised form, headcount, and its resolved classification: source (pending, override, pack, cache, ai, rules), role category and sub-category, US SOC-2018, UK SOC-2020, confidence, rationale	Classification happens here, once per title. A partial index finds pending titles.
employee_identities	Name and email, AES-256-GCM encrypted, plus an HMAC blind index of the email	Only written when a PII-authorised user stores identities.
title_overrides	A consultant's classification of a title for the engagement, with a mandatory rationale: either a SOC pair (kind = 'soc') or a taxonomy role (kind = 'taxonomy')	Wins over every automated source.
classification_cache	AI classifications keyed by normalised title and context (the industry pack id): codes, confidence, model, prompt_version, verified, hits	Shared across engagements that allow sharing. Titles only. Written only by the service connection.
copilot_plans	Saved rollout plans: name and config (waves, cohort selectors, options, existing holders as employee IDs)	Deleted on purge.
dataset_ai	Cached AI outputs per dataset and kind (exec-summary, lifecycle), with prompt_version	Cleared whenever the dataset is re-scored.
audit_log	Append-only trail: at, actor_id, action, engagement_id, target_type, target_id, meta	No update or delete policy or grant.
ai_usage	Tokens per call, feature and model	Drives the monthly budget and the admin usage view.
reference_versions	One JSON document per reference data version: version, status (draft, published), data, notes, based_on, who created and published it and when	At most one draft (a unique partial index). Published rows are immutable under RLS.
prompt_versions	Per prompt_key (classifier, exec-summary, lifecycle, ask) and version: system, template, model (null = platform default), temperature (0–2), max_output_tokens (64–32768), notes, status and authorship	At most one draft per prompt. Administrators only.
schema_migrations	Applied migration names	Created by the migrator.

No failed status

The column's check still allows a legacy failed, but nothing sets it and the types no longer include it: interrupted uploads stay uploading (delete and upload again) and interrupted classification stays classifying (Continue resumes it).

Dataset lifecycle

uploading ──complete──▶ classifying ──(no titles pending)──▶ ready ──engagement purged──▶ purged
                                                               │
                                                               └─ re-scored in place when assumptions,
                                                                  overrides, the industry pack or the reference version change

• A dataset is created with reference_version = the current published version.
• A ready dataset has every title classified and a summary; finalising writes reference_version again from the version it was scored with.
• A purged dataset keeps only its summary (and cached AI text): reports, comparisons and its benchmark contribution remain; row-level views and exports that need rows are unavailable.

Engagement lifecycle

active → closed (read-only; the retention clock starts at closed_at) → purged (automatically after retention_days, or immediately by a lead who types the engagement name). A closed engagement can be reopened until it's purged. Admins can delete an engagement outright (cascading to everything but the audit log, whose engagement_id is set null).

Versioned reference data and prompts

• Reference data. reference_versions holds the whole ReferenceData document as jsonb. Everyone reads published versions; only administrators read the draft and write. The policies only allow inserting drafts and updating or deleting rows that are still drafts, so a published row can never change. Version 1 is seeded from builtinReferenceData() by seedPlatformData. Datasets from before 0002 have a null reference_version and read as version 1. See Reference data.
• Prompts. prompt_versions has the same draft/publish lifecycle per prompt key; only administrators can read it under RLS. The AI services read the newest published version through the service connection. Version 1 of each prompt is seeded from BUILTIN_PROMPTS. See Prompts.

Row-level security

Policies call SECURITY DEFINER helper functions in the app schema that return sets of engagement IDs for the current user (from app.user_id), used as engagement_id IN (SELECT …) so they're evaluated once per statement:

Function	Returns
app.current_user_id()	The app.user_id setting as a UUID.
app.is_admin()	Whether the current user is an active admin.
app.readable_engagement_ids()	All engagements for admins; otherwise those the (active) user is a member of.
app.lead_engagement_ids()	All for admins; otherwise those the user leads. Any status.
app.writable_engagement_ids()	Active engagements the user leads or analyses (all active ones for admins).
app.pii_engagement_ids()	Non-purged engagements where the user has pii_access. Admins aren't exempt.
app.can_bootstrap_members(eid)	Whether the user created this engagement and it has no members yet (so the creator can add themselves as lead, with PII access).
app.own_pii_membership_ids()	Engagements where the user already holds pii_access, at any status. Used by the member update policy: as a STABLE function it sees the rows before the statement, so an update can keep existing access but not create it.

In summary:

Data	Read	Write
Engagement and its datasets, titles, overrides, plans, AI outputs, chunks, rows	Members (admins: all)	Leads, analysts and admins, while the engagement is active
Engagement settings (including report wording), team, close and reopen	Members	Leads and admins, at any status. Nobody may set pii_access on their own membership unless they already hold it; the creator's first row is the exception. (The API inserts and updates members separately rather than upserting, because an upsert also meets the insert policy.)
employee_identities	Members with pii_access only	Insert: pii_access members on an active engagement. Delete: writers (so a dataset can be removed). No updates.
audit_log	Admins; leads for their engagements	Insert only, as ourselves. No updates or deletes.
classification_cache	Everyone	New entries: service connection only. Verify and delete: admins.
users, clients	Everyone	Users: admins (sign-in bookkeeping runs on the service connection). Clients: anyone can add; the creator or an admin can edit; admins delete.
ai_usage	Admins	Insert only, as ourselves
reference_versions	Published: everyone. Draft: admins	Admins, drafts only
prompt_versions	Admins	Admins, drafts only

Membership only counts for active users, so deactivating someone removes their access everywhere at once. Closed engagements are read-only because writable_engagement_ids() only returns active ones.

See Authentication and authorisation for how requests assume the workbench_app role.

How migrations run

migrate(db, migrations) (src/db/migrate.ts):

1. creates schema_migrations if needed;
2. reads the applied names;
3. applies each pending .sql file in name order, each in its own transaction (BEGIN; … INSERT INTO schema_migrations …; COMMIT;).

It runs:

• on every start of the Node dev server (node.ts), then seedPlatformData;
• in every API test harness, then seedPlatformData;
• via npm run db:migrate (src/db/migrate-cli.ts) against DATABASE_URL, then seedPlatformData. This is the only way staging and production are migrated: the Worker never migrates or seeds at runtime.

The first migration creates the workbench_app role and grants it to CURRENT_USER, so the connecting (owner) role needs CREATEROLE (Neon's default owner has it).

To add one, see Add a migration.