Data model
MERGED, NOT DEPLOYED — these columns come from the forward migrations in the supplied app branch. They are not an introspection of staging. Types, nullability and defaults below retain the SQL declarations. The pack identifies the deployed release, but does not introspect table columns or applied migration checksums; those are NOT VERIFIED. E:evidence.md:13-18
Client response children use composite client/response foreign keys. Staff edits have one live held edit per response/question. Restore-point subject checks distinguish answer sets, individual answers, client copies and tenant templates. A:apps/api/drizzle/tenant/0007_responses.sql:46-56 A:apps/api/drizzle/tenant/0020_staff_edits.sql:28-36
questionnaire_responses
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:31 |
client_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:32 |
questionnaire_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:33 |
revision | integer DEFAULT 1 NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:34 |
status | text DEFAULT 'draft' NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:35 |
submitted_at | timestamp with time zone | A:apps/api/drizzle/tenant/0007_responses.sql:36 |
submitted_by | text | A:apps/api/drizzle/tenant/0007_responses.sql:37 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:38 |
updated_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:39 |
returned | boolean DEFAULT false NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:13 |
restored | boolean DEFAULT false NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:23 |
cycle | integer DEFAULT 1 NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:25 |
held_revision | integer DEFAULT 1 NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:26 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "org_client_scope" ON "questionnaire_responses" AS PERMISSIVE FOR ALL TO public USING (client_id = nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0007_responses.sql:56 |
questionnaire_answers
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
client_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:3 |
response_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:4 |
question_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:5 |
asked | jsonb NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:6 |
value | jsonb NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:7 |
answered_by | text NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:8 |
updated_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:9 |
deleted_at | timestamp with time zone | A:apps/api/drizzle/tenant/0018_restore_points.sql:22 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "org_client_scope" ON "questionnaire_answers" AS PERMISSIVE FOR ALL TO public USING (client_id = nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0007_responses.sql:54 |
questionnaire_staff_edits
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:2 |
client_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:3 |
response_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:4 |
question_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:5 |
asked | jsonb NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:6 |
value | jsonb NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:7 |
note | text NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:8 |
edited_by | text NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:9 |
base | jsonb | A:apps/api/drizzle/tenant/0020_staff_edits.sql:10 |
published_at | timestamp with time zone | A:apps/api/drizzle/tenant/0020_staff_edits.sql:11 |
discarded_at | timestamp with time zone | A:apps/api/drizzle/tenant/0020_staff_edits.sql:12 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:13 |
updated_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:14 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "org_client_scope" ON "questionnaire_staff_edits" AS PERMISSIVE FOR ALL TO public USING (client_id = nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0020_staff_edits.sql:36 |
questionnaire_response_notes
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:2 |
client_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:3 |
response_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:4 |
kind | text NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:5 |
note | text NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:6 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0008_response_notes.sql:7 |
question_ids | uuid[] | A:apps/api/drizzle/tenant/0020_staff_edits.sql:23 |
cycle | integer DEFAULT 1 NOT NULL | A:apps/api/drizzle/tenant/0020_staff_edits.sql:24 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "org_client_scope" ON "questionnaire_response_notes" AS PERMISSIVE FOR ALL TO public USING (client_id = nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0008_response_notes.sql:19 |
questionnaire_attachments
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:15 |
client_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:16 |
response_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:17 |
question_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:18 |
name | text NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:19 |
content_type | text NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:20 |
size_bytes | integer NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:21 |
uploaded_by | text NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:22 |
deleted_at | timestamp with time zone | A:apps/api/drizzle/tenant/0007_responses.sql:23 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0007_responses.sql:24 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "org_client_scope" ON "questionnaire_attachments" AS PERMISSIVE FOR ALL TO public USING (client_id = nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0007_responses.sql:55 |
restore_points
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:2 |
kind | text NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:3 |
event | text NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:4 |
client_id | uuid | A:apps/api/drizzle/tenant/0018_restore_points.sql:5 |
response_id | uuid | A:apps/api/drizzle/tenant/0018_restore_points.sql:6 |
copy_id | uuid | A:apps/api/drizzle/tenant/0018_restore_points.sql:7 |
template_id | uuid | A:apps/api/drizzle/tenant/0018_restore_points.sql:8 |
version | smallint DEFAULT 1 NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:9 |
snapshot | jsonb NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:10 |
actor_id | text NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:11 |
actor_label | text | A:apps/api/drizzle/tenant/0018_restore_points.sql:12 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0018_restore_points.sql:13 |
question_id | uuid | A:apps/api/drizzle/tenant/0020_staff_edits.sql:27 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "restore_points_read" ON "restore_points" AS PERMISSIVE FOR SELECT TO public USING (client_id is not distinct from nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0018_restore_points.sql:33 |
CREATE POLICY "restore_points_write" ON "restore_points" AS PERMISSIVE FOR INSERT TO public WITH CHECK (client_id is not distinct from nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0018_restore_points.sql:34 |
CREATE POLICY "restore_points_expire" ON "restore_points" AS PERMISSIVE FOR DELETE TO public USING (client_id is not distinct from nullif(current_setting('set.client_id', true), '')::uuid and created_at < now() - interval '1 year') | A:apps/api/drizzle/tenant/0018_restore_points.sql:35 |
DROP POLICY "restore_points_expire" ON "restore_points" CASCADE | A:apps/api/drizzle/tenant/0019_restore_delete_revoked.sql:1 |
questionnaire_reminders
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
questionnaire_id | uuid PRIMARY KEY NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:8 |
client_id | uuid NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:9 |
kind | text NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:10 |
due_at | timestamp with time zone | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:11 |
version | integer DEFAULT 1 NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:12 |
read_version | integer | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:13 |
read_by | text | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:14 |
read_at | timestamp with time zone | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:15 |
updated_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:16 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "reminder_scope" ON "questionnaire_reminders" AS PERMISSIVE FOR ALL TO public USING (nullif(current_setting('set.client_id', true), '') is null or client_id = nullif(current_setting('set.client_id', true), '')::uuid) | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:33 |
alert_reads
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
event_id | uuid PRIMARY KEY NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:2 |
read_by | text NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:3 |
read_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:4 |
RLS definitions (later migrations supersede earlier policies):
| Policy SQL | Source |
|---|---|
CREATE POLICY "alert_reads_unscoped" ON "alert_reads" AS PERMISSIVE FOR ALL TO public USING (nullif(current_setting('set.client_id', true), '') is null) WITH CHECK (nullif(current_setting('set.client_id', true), '') is null) | A:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:32 |
standards
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:38 |
title | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:39 |
category_id | uuid | A:apps/api/drizzle/public/0001_knowledge_base.sql:40 |
deleted_at | timestamp with time zone | A:apps/api/drizzle/public/0001_knowledge_base.sql:41 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:42 |
updated_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:43 |
active | boolean DEFAULT true NOT NULL | A:apps/api/drizzle/public/0003_standards_frameworks.sql:6 |
deleted_by_label | text | A:apps/api/drizzle/public/0003_standards_frameworks.sql:7 |
purged_at | timestamp with time zone | A:apps/api/drizzle/public/0003_standards_frameworks.sql:8 |
erased_at | timestamp with time zone | A:apps/api/drizzle/public/0003_standards_frameworks.sql:9 |
No RLS policy for this public table is declared in the inspected forward migrations. Access uses control-plane roles and grants; it is not a tenant/client RLS table. A:apps/api/src/db/migrations/runner.ts:293-314
standard_files
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:24 |
standard_id | uuid NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:25 |
name | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:26 |
content_type | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:27 |
size_bytes | integer NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:28 |
replaces_id | uuid | A:apps/api/drizzle/public/0001_knowledge_base.sql:29 |
uploaded_by | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:30 |
deleted_at | timestamp with time zone | A:apps/api/drizzle/public/0001_knowledge_base.sql:31 |
created_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:32 |
pending | boolean DEFAULT false NOT NULL | A:apps/api/drizzle/public/0003_standards_frameworks.sql:3 |
uploaded_by_label | text | A:apps/api/drizzle/public/0003_standards_frameworks.sql:4 |
erased_at | timestamp with time zone | A:apps/api/drizzle/public/0003_standards_frameworks.sql:5 |
No RLS policy for this public table is declared in the inspected forward migrations. Access uses control-plane roles and grants; it is not a tenant/client RLS table. A:apps/api/src/db/migrations/runner.ts:293-314
platform_audit_events
MERGED, NOT DEPLOYED.
| Column | SQL type / constraints / default | Migration |
|---|---|---|
id | uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:2 |
occurred_at | timestamp with time zone DEFAULT now() NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:3 |
actor_id | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:4 |
actor_label | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:5 |
action | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:6 |
target_type | text NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:7 |
target_id | uuid NOT NULL | A:apps/api/drizzle/public/0001_knowledge_base.sql:8 |
standard_id | uuid | A:apps/api/drizzle/public/0001_knowledge_base.sql:9 |
previous_id | uuid | A:apps/api/drizzle/public/0001_knowledge_base.sql:10 |
ip | "inet" | A:apps/api/drizzle/public/0001_knowledge_base.sql:11 |
user_agent | text | A:apps/api/drizzle/public/0001_knowledge_base.sql:12 |
detail | text | A:apps/api/drizzle/public/0003_standards_frameworks.sql:2 |
No RLS policy for this public table is declared in the inspected forward migrations. Access uses control-plane roles and grants; it is not a tenant/client RLS table. A:apps/api/src/db/migrations/runner.ts:293-314
Grants and history
MERGED, NOT DEPLOYED — response notes and restore points are SELECT/INSERT-only for the tenant runtime role. The runner explicitly revokes DELETE on restore_points; migration 0019 drops its expiry policy. The owner maintenance job owns expiry. Answer history is stored in restore_points, not a separate answer_history table. A:apps/api/src/db/migrations/runner.ts:418-457 A:apps/api/drizzle/tenant/0019_restore_delete_revoked.sql:1 A:apps/api/drizzle/tenant/0020_staff_edits.sql:33-35
Approved tables not yet in migrations
APPROVED, IN BUILD — the following are design declarations, not current database facts.
| Table / change | Proposed columns | Isolation / source |
|---|---|---|
| standard_pages | id, standard_id, file_id, page, text, created_at | Square RLS for AI/ingest; purge role has broader select/delete. Composite file/standard FK. A:.set/work/P2.22.md:235-245 |
| platform_ai_usage | period, scope, reserved_micros, questions, in_flight_until | Primary key period/scope; locked reservations. Control-plane grants, not a client RLS ledger. A:.set/work/P2.22.md:252-267,310-329 |
| ai_standard / ai_standard_files | Filtered metadata views | security_barrier views filtered by set.standard_id. A:.set/work/P2.22.md:247-250 |
| Expert extraction fields | standard_files: extract_status, extract_reason, token_count; standards: extract_requested_at; platform_audit_events: metrics | A:.set/work/P2.22.md:227-256 |
| File transition and manifest | layout on attachments/standard_files; pending, purge_claimed_at, erased_at on attachments; file_move_manifest | Owner-only move manifest; temporary layout migration. A:.set/work/P2.23.md:120-122,174-180 |
NOT VERIFIED — these tables on staging. Verify migration checksums and schema/grants in the app evidence pack before promoting status.