module-spec · supabase_json

Patient Visit Advocate module-spec M-02B

module-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

Risks

id	risk	mitigation
M02B-RISK-001	Importing source text directly could surface compliance-sensitive language later if lint/review gates are bypassed.	Default publish_status blocked and require M-02C/M-02D before public use.
M02B-RISK-002	Future full-corpus workbook may use different IDs or clusters.	Use import batches and source checksum identity rather than destructive overwrite.
M02B-RISK-003	Coverage caveat depends on correct cluster tagging.	Hard-code accepted coverage cluster list from source verification and assert row coverage during import.

tasks

id	name	output
M02B-T1	Define ingestion schema/migration	migration or schema artifact for qna_import_batches, qna_clusters, qna_items, qna_lint_findings
M02B-T2	Build dry-run parser contract	dry-run parser script/design that reports row counts, rejects, duplicates, parity, and normalized records
M02B-T3	Build controlled import path	import script/design with batch record, source checksum validation, inserts, and blocked default statuses
M02B-T4	Create ingestion verification artifact	future ingestion-verification-report

dependencies

module	status	artifact id
M-00	accepted_for_boundary
M-01	specified
M-02A	accepted	6733187b-befe-4a6d-8dba-07d40d6af804

risks

id	risk	mitigation
M02B-RISK-001	Importing source text directly could surface compliance-sensitive language later if lint/review gates are bypassed.	Default publish_status blocked and require M-02C/M-02D before public use.
M02B-RISK-002	Future full-corpus workbook may use different IDs or clusters.	Use import batches and source checksum identity rather than destructive overwrite.
M02B-RISK-003	Coverage caveat depends on correct cluster tagging.	Hard-code accepted coverage cluster list from source verification and assert row coverage during import.

Agent Handoff

Start Here

id	name	output
M02B-T1	Define ingestion schema/migration	migration or schema artifact for qna_import_batches, qna_clusters, qna_items, qna_lint_findings
M02B-T2	Build dry-run parser contract	dry-run parser script/design that reports row counts, rejects, duplicates, parity, and normalized records
M02B-T3	Build controlled import path	import script/design with batch record, source checksum validation, inserts, and blocked default statuses
M02B-T4	Create ingestion verification artifact	future ingestion-verification-report

Completion Evidence

id	criterion
M02B-AC-001	Data contracts include import batch, clusters, Q&A items, and lint findings with source provenance and status gates.
M02B-AC-002	Importer/dry-run contract preserves the accepted 250-row V1 seed and rejects unexpected source checksum/count changes.
M02B-AC-003	Imported rows default to lint pending, review pending, publish blocked.
M02B-AC-004	Coverage clusters are tagged and available for M-02D coverage caveat rendering.
M02B-AC-005	No public UI or AI output path consumes rows until M-02C lint/review and M-02D legal UX gates pass.

Goal

Define the durable ingestion and provenance model for importing the accepted 250-row Pocket Advocate Q&A seed while preserving source traceability, provider-question framing, coverage tags, lint/review state, and future re-import safety.

Risks

M02B-RISK-001: id: string, risk: string, mitigation: string
M02B-RISK-002: id: string, risk: string, mitigation: string
M02B-RISK-003: id: string, risk: string, mitigation: string

Structured Payload

Machine-readable source fields

goal

risks

id	risk	mitigation
M02B-RISK-001	Importing source text directly could surface compliance-sensitive language later if lint/review gates are bypassed.	Default publish_status blocked and require M-02C/M-02D before public use.
M02B-RISK-002	Future full-corpus workbook may use different IDs or clusters.	Use import batches and source checksum identity rather than destructive overwrite.
M02B-RISK-003	Coverage caveat depends on correct cluster tagging.	Hard-code accepted coverage cluster list from source verification and assert row coverage during import.

tasks

id	name	output
M02B-T1	Define ingestion schema/migration	migration or schema artifact for qna_import_batches, qna_clusters, qna_items, qna_lint_findings
M02B-T2	Build dry-run parser contract	dry-run parser script/design that reports row counts, rejects, duplicates, parity, and normalized records
M02B-T3	Build controlled import path	import script/design with batch record, source checksum validation, inserts, and blocked default statuses
M02B-T4	Create ingestion verification artifact	future ingestion-verification-report

module

M-02B

name

Q&A Ingestion and Provenance Model

status

accepted

status

accepted_for_schema_contract

product

Pocket Advocate / Patient Visit Advocate

non goals

Do not approve public display of Q&A content; M-02C compliance lint and M-02D legal UX remain required.
Do not rewrite or medically review individual Q&A rows in this module.
Do not build end-user Q&A screens.
Do not ingest more than the accepted 250-row V1 seed without a new source-verification report.

project id

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

dependencies

module	status	artifact id
M-00	accepted_for_boundary
M-01	specified
M-02A	accepted	6733187b-befe-4a6d-8dba-07d40d6af804

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.

output artifacts

status	phase id	description	artifact type
required_before_build	M-02B	Concrete table/migration plan for Q&A ingestion.	schema-or-migration-spec
required_after_import	M-02B	Evidence of dry-run/import counts and blocked statuses.	ingestion-verification-report

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	artifact id	phase id	accepted scope	status	version at creation
client-source-reference	fb647c16-c6de-412b-a46f-56746971cf25
source-verification-report	20f31336-e9fb-4059-be3a-176e11d1d720	M-02A	250 Q&A rows, 50 clusters, 8 waves
M-02A module-spec	6733187b-befe-4a6d-8dba-07d40d6af804	M-02A		accepted
plan	98a26319-8d8d-48bd-b0f9-abcf0e20de46				7

validation rules

Expected row count is exactly 250 for V1 seed.
Every source_row_id must be unique in the import batch.
Every cluster must have exactly 5 Q&A rows for the accepted V1 seed unless an explicit operator override is recorded.
Every row category must be Preparation, Your rights, Safety, or Logistics.
All answer sections must be non-empty.
Coverage clusters must carry coverage_content true.
Master sheet parity must be checked and mismatch must block import.
No public-ready status is allowed during M-02B.

acceptance criteria

id	criterion
M02B-AC-001	Data contracts include import batch, clusters, Q&A items, and lint findings with source provenance and status gates.
M02B-AC-002	Importer/dry-run contract preserves the accepted 250-row V1 seed and rejects unexpected source checksum/count changes.
M02B-AC-003	Imported rows default to lint pending, review pending, publish blocked.
M02B-AC-004	Coverage clusters are tagged and available for M-02D coverage caveat rendering.
M02B-AC-005	No public UI or AI output path consumes rows until M-02C lint/review and M-02D legal UX gates pass.

acceptance decision

decision

Accept M-02B as the Q&A ingestion/provenance planning contract for the 250-row V1 seed.

accepted at

2026-05-07T14:18:12.916Z

accepted by

atlas-codex

next required artifact

schema-or-migration-spec / phase_id M-02B

accepted seed scope

waves

8

source file

C:/Users/jstnk/Downloads/pocket_advocate_all_waves.xlsx

cluster count

50

source sha256

5d6112787e8aae61b24feadd9bafad783f0b645795b906979a0b1553d3138605

total qna rows

250

category counts

Safety

56

Logistics

58

Preparation

79

Your rights

57

questions per cluster

5

proposed data contracts

name	purpose	constraints	primary fields
qna_import_batches	Audit each workbook import and support future re-imports without losing provenance.	- source_sha256 unique for active imported batches unless superseded - imported_row_count must equal expected_row_count before imported status	- id - project_id - source_file_name - source_sha256 - source_file_size_bytes - expected_row_count - imported_row_count - sheet_inventory - status - created_by - created_at
qna_clusters	Cluster metadata for navigation, coverage caveats, and specialty grouping.	- unique(wave, name) - coverage_content true for accepted coverage clusters	- id - slug - name - wave - domain - coverage_content - display_order - source_import_batch_id - created_at - updated_at
qna_items	Canonical imported patient-advocacy Q&A records.	- unique(import_batch_id, source_row_id) - publish_status cannot become ready unless lint passed/waived and review approved - reject any import/UI mapping named What to do or ask	- id - cluster_id - import_batch_id - source_file_sha256 - source_sheet_name - source_row_id - wave - cluster_name_source - category - question - what_to_know - questions_to_ask_provider - what_to_watch_for - revised - coverage_content - lint_status
qna_lint_findings	Store M-02C compliance findings against imported rows without mutating source text.	- M-02C owns rule implementation and finding generation	- id - qna_item_id - rule_id - severity - field_name - snippet - status - reviewed_by - reviewed_at

next step after approval

Approve/execute M-02B as schema + parser/import contract, then create M-02C compliance lint module before any public content use.

coverage content clusters

Insurance Denial Appeals
Durable Medical Equipment
Prior Authorization
Prescription Coverage
Medicare Navigation
Medicaid Navigation
Medical Bills
Surprise Billing

import pipeline requirements

id	requirement
M02B-PIPE-001	Importer reads only the accepted source SHA unless operator explicitly starts a new verification/import batch.
M02B-PIPE-002	Importer extracts from wave sheets as source of truth and checks parity against Master sheet count.
M02B-PIPE-003	Importer normalizes IDs, wave, cluster, category, and answer fields without rewriting client content.
M02B-PIPE-004	Importer assigns coverage_content based on cluster list and preserves source cluster names.
M02B-PIPE-005	Imported Q&A rows default to lint_status pending, review_status pending, and publish_status blocked.
M02B-PIPE-006	Importer produces a dry-run report before any write, including inserts, updates, rejects, duplicate IDs, missing required fields, and category/cluster counts.