schema-or-migration-spec · supabase_json

Patient Visit Advocate schema-or-migration-spec M-02B

schema-or-migration-spec artifact · for Patient Visit Advocate · phase M-02B · status approved

Planning Surface

Use this to decide what happens next.

Status

approved

Phase

M-02B

goal

Specify the database migration and import-safety contract for the accepted 250-row Q&A seed without making any content public-ready.

Agent Handoff

Start Here

schema-or-migration-spec artifact · for Patient Visit Advocate · phase M-02B · status approved

Completion Evidence

No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.

Goal

Specify the database migration and import-safety contract for the accepted 250-row Q&A seed without making any content public-ready.

Structured Payload

Machine-readable source fields

goal

Specify the database migration and import-safety contract for the accepted 250-row Q&A seed without making any content public-ready.

tables

name	checks	columns	indexes	purpose	unique constraints
pva_qna_import_batches	- status in ('draft','validated','imported','rejected','superseded') - expected_row_count > 0 - imported_row_count >= 0 - source_sha256 ~ '^[a-f0-9]{64}$'	- item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string	- studio_project_id - source_sha256 - status	One row per source workbook import attempt or accepted import batch.	- unique(studio_project_id, source_sha256) where status != superseded
pva_qna_clusters	- wave between 1 and 8 - display_order > 0 - length(trim(name)) > 0 - length(trim(slug)) > 0	- item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string	- studio_project_id - import_batch_id - wave - coverage_content	Navigation and caveat metadata for the accepted 50 clusters.	- unique(studio_project_id, import_batch_id, wave, name) - unique(studio_project_id, import_batch_id, slug)
pva_qna_items	- wave between 1 and 8 - category in ('Preparation','Your rights','Safety','Logistics') - lint_status in ('pending','passed','flagged','waived') - review_status in ('pending','approved','needs_revision','rejected') - publish_status in ('blocked','ready','published','archived') - trim(question) <> empty string - trim(what_to_know) <> empty string - trim(questions_to_ask_provider) <> empty string - trim(what_to_watch_for) <> empty string - publish_status ready/published requires lint passed or waived and review approved	- item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string	- studio_project_id - cluster_id - import_batch_id - wave - category - coverage_content - lint_status - review_status - publish_status	Canonical imported Q&A seed rows, always blocked until lint/review/legal gates pass.	- unique(import_batch_id, source_row_id)
pva_qna_lint_findings	- severity in ('hard','warning','info') - status in ('open','resolved','waived') - trim(rule_id) <> empty string - trim(field_name) <> empty string	- item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string - item: 0: string, 1: string, 2: string	- studio_project_id - qna_item_id - rule_id - severity - status	Compliance finding records generated by M-02C; table can exist before rules are implemented.	- unique(qna_item_id, rule_id, field_name, snippet)

product

Pocket Advocate / Patient Visit Advocate

artifact

type

schema-or-migration-spec

status

accepted

phase id

M-02B

project id

31d7f681-bed2-44e9-9a55-b9fadcbba0da

rls policy

policies

service_role all access through Supabase service key / trusted server processes
no anon insert/update/delete
no anon select in M-02B
future public select only where publish_status = published after legal UX gate

v1 recommendation

Enable RLS on all pva_qna_* tables. Service role can write. Public/anon read should be disabled until M-02D legal UX and M-02C lint/review gates pass. App read policies can be added later for publish_status published only.

schema version

1.0

target database

decision

Projects/Product DB

not target

RedKey platform DB

migration note

Run product migration with MIGRATION_DB_URL pointing at the Projects DB connection string, not REDKEY_DATABASE_URL.

scope correction

reason

Justin clarified product DB work belongs in the Projects/Product DB. RedKey remains the planning/orchestration artifact store.

corrected at

2026-05-07T18:45:10.665Z

corrected by

atlas-codex

redkey db scope

studio_projects, studio_artifacts, module specs, verification reports, orchestration/planning state

projects product db scope

Pocket Advocate product tables, Q&A corpus, import batches, Q&A items, lint/review records used by app runtime

source artifacts

id	status	artifact id	accepted scope
M-02B module-spec	accepted	949a5971-6667-4965-a425-7a17154fc42d
source-verification-report		20f31336-e9fb-4059-be3a-176e11d1d720	250 Q&A rows

namespace decision

schema

public

database

Projects/Product DB

rationale

Avoid collisions with KnowledgeVault or future shared Q&A products while keeping tables discoverable in RedKey Supabase.

redkey role

Artifact source of truth only; no pva_qna_* product tables should be applied to RedKey platform DB.

table prefix

pva_qna_

acceptance decision

decision

Accept schema-or-migration-spec/M-02B as the implementation contract for the local migration and dry-run/import path.

accepted at

2026-05-07T14:34:08.998Z

accepted by

atlas-codex

next required work

supabase migration file
dry-run parser/import script
ingestion-verification-report after dry-run/import

import safety rules

M-02B migration may create tables and import records, but every item must remain publish_status=blocked.
The importer must validate source_sha256 exactly: 5d6112787e8aae61b24feadd9bafad783f0b645795b906979a0b1553d3138605.
Dry-run report is required before import write.
If parsed row count is not 250, import must abort.
If master/wave parity fails, import must abort.
Coverage clusters must be tagged and verified at import time.
No UI route or AI-generation path should read pva_qna_items before M-02C and M-02D.
Do not run this migration with REDKEY_DATABASE_URL unless RedKey is intentionally acting as the product DB for this app; default target is Projects DB.

verification queries

count pva_qna_items by import_batch_id = 250
count distinct pva_qna_clusters = 50
count items grouped by wave matches 35/30/30/30/30/30/30/35
count items where publish_status != blocked = 0
count coverage clusters where coverage_content = true = 8
count qna_items in coverage clusters where coverage_content = false = 0

proposed migration name

051_patient_visit_advocate_qna_seed.sql

migration acceptance criteria

id	criterion
M02B-SCHEMA-AC-001	Migration creates four pva_qna_* tables with checks, indexes, provenance fields, and RLS enabled.
M02B-SCHEMA-AC-002	Q&A item table prevents ready/published status unless lint/review statuses permit it.
M02B-SCHEMA-AC-003	Import batch model records source hash, row counts, sheet inventory, and status.
M02B-SCHEMA-AC-004	No anon/public read policy exposes content during M-02B.
M02B-SCHEMA-AC-005	Verification query can prove 250 items, 50 clusters, 8 waves, and all items blocked after import.

next artifact after migration spec

dry-run-parser-spec or implementation task for parser/import script under M-02B