Skip to main content

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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:31
client_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:32
questionnaire_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:33
revisioninteger DEFAULT 1 NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:34
statustext DEFAULT 'draft' NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:35
submitted_attimestamp with time zoneA:apps/api/drizzle/tenant/0007_responses.sql:36
submitted_bytextA:apps/api/drizzle/tenant/0007_responses.sql:37
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:38
updated_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:39
returnedboolean DEFAULT false NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:13
restoredboolean DEFAULT false NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:23
cycleinteger DEFAULT 1 NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:25
held_revisioninteger DEFAULT 1 NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:26

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
client_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:3
response_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:4
question_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:5
askedjsonb NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:6
valuejsonb NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:7
answered_bytext NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:8
updated_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:9
deleted_attimestamp with time zoneA:apps/api/drizzle/tenant/0018_restore_points.sql:22

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:2
client_iduuid NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:3
response_iduuid NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:4
question_iduuid NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:5
askedjsonb NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:6
valuejsonb NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:7
notetext NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:8
edited_bytext NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:9
basejsonbA:apps/api/drizzle/tenant/0020_staff_edits.sql:10
published_attimestamp with time zoneA:apps/api/drizzle/tenant/0020_staff_edits.sql:11
discarded_attimestamp with time zoneA:apps/api/drizzle/tenant/0020_staff_edits.sql:12
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:13
updated_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:14

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:2
client_iduuid NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:3
response_iduuid NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:4
kindtext NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:5
notetext NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:6
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0008_response_notes.sql:7
question_idsuuid[]A:apps/api/drizzle/tenant/0020_staff_edits.sql:23
cycleinteger DEFAULT 1 NOT NULLA:apps/api/drizzle/tenant/0020_staff_edits.sql:24

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:15
client_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:16
response_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:17
question_iduuid NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:18
nametext NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:19
content_typetext NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:20
size_bytesinteger NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:21
uploaded_bytext NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:22
deleted_attimestamp with time zoneA:apps/api/drizzle/tenant/0007_responses.sql:23
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0007_responses.sql:24

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:2
kindtext NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:3
eventtext NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:4
client_iduuidA:apps/api/drizzle/tenant/0018_restore_points.sql:5
response_iduuidA:apps/api/drizzle/tenant/0018_restore_points.sql:6
copy_iduuidA:apps/api/drizzle/tenant/0018_restore_points.sql:7
template_iduuidA:apps/api/drizzle/tenant/0018_restore_points.sql:8
versionsmallint DEFAULT 1 NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:9
snapshotjsonb NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:10
actor_idtext NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:11
actor_labeltextA:apps/api/drizzle/tenant/0018_restore_points.sql:12
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0018_restore_points.sql:13
question_iduuidA:apps/api/drizzle/tenant/0020_staff_edits.sql:27

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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" CASCADEA:apps/api/drizzle/tenant/0019_restore_delete_revoked.sql:1

questionnaire_reminders​

MERGED, NOT DEPLOYED.

ColumnSQL type / constraints / defaultMigration
questionnaire_iduuid PRIMARY KEY NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:8
client_iduuid NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:9
kindtext NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:10
due_attimestamp with time zoneA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:11
versioninteger DEFAULT 1 NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:12
read_versionintegerA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:13
read_bytextA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:14
read_attimestamp with time zoneA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:15
updated_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:16

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
event_iduuid PRIMARY KEY NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:2
read_bytext NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:3
read_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/tenant/0014_questionnaire_flow.sql:4

RLS definitions (later migrations supersede earlier policies):

Policy SQLSource
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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:38
titletext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:39
category_iduuidA:apps/api/drizzle/public/0001_knowledge_base.sql:40
deleted_attimestamp with time zoneA:apps/api/drizzle/public/0001_knowledge_base.sql:41
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:42
updated_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:43
activeboolean DEFAULT true NOT NULLA:apps/api/drizzle/public/0003_standards_frameworks.sql:6
deleted_by_labeltextA:apps/api/drizzle/public/0003_standards_frameworks.sql:7
purged_attimestamp with time zoneA:apps/api/drizzle/public/0003_standards_frameworks.sql:8
erased_attimestamp with time zoneA: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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:24
standard_iduuid NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:25
nametext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:26
content_typetext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:27
size_bytesinteger NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:28
replaces_iduuidA:apps/api/drizzle/public/0001_knowledge_base.sql:29
uploaded_bytext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:30
deleted_attimestamp with time zoneA:apps/api/drizzle/public/0001_knowledge_base.sql:31
created_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:32
pendingboolean DEFAULT false NOT NULLA:apps/api/drizzle/public/0003_standards_frameworks.sql:3
uploaded_by_labeltextA:apps/api/drizzle/public/0003_standards_frameworks.sql:4
erased_attimestamp with time zoneA: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.

ColumnSQL type / constraints / defaultMigration
iduuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:2
occurred_attimestamp with time zone DEFAULT now() NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:3
actor_idtext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:4
actor_labeltext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:5
actiontext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:6
target_typetext NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:7
target_iduuid NOT NULLA:apps/api/drizzle/public/0001_knowledge_base.sql:8
standard_iduuidA:apps/api/drizzle/public/0001_knowledge_base.sql:9
previous_iduuidA:apps/api/drizzle/public/0001_knowledge_base.sql:10
ip"inet"A:apps/api/drizzle/public/0001_knowledge_base.sql:11
user_agenttextA:apps/api/drizzle/public/0001_knowledge_base.sql:12
detailtextA: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 / changeProposed columnsIsolation / source
standard_pagesid, standard_id, file_id, page, text, created_atSquare RLS for AI/ingest; purge role has broader select/delete. Composite file/standard FK. A:.set/work/P2.22.md:235-245
platform_ai_usageperiod, scope, reserved_micros, questions, in_flight_untilPrimary 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_filesFiltered metadata viewssecurity_barrier views filtered by set.standard_id. A:.set/work/P2.22.md:247-250
Expert extraction fieldsstandard_files: extract_status, extract_reason, token_count; standards: extract_requested_at; platform_audit_events: metricsA:.set/work/P2.22.md:227-256
File transition and manifestlayout on attachments/standard_files; pending, purge_claimed_at, erased_at on attachments; file_move_manifestOwner-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.