Schema changes for nova-codex-curator

AI-DRAFT. Written against ../../schema/nova-codex-curator.md (reverse-engineered, accepted as-is — not authored or confirmed by the team that owns this service). This is the largest schema delta of the three seed Feature Sets in this repo, because the PRD's ops dashboard and review workflow assume state (curation status, accept/reject decisions, audit trail, legal track) that the current reverse-engineered schema does not model at all today — see "What's missing" below.

What already exists (per the reverse-engineered schema doc)

The current pipeline persists three kinds of state, none of which is a single unified database:

• Postgres tenant catalog (instances, tenants, instance_oauth) — unrelated to curation state; this is ServiceNow OAuth plumbing for the KB sync path, not regulatory content.
• OpenSearch k-NN index (codex-documents, plus per-country and per-tenant variants) — the actual embedded, searchable regulatory content, with a metadata object carrying country, state, agency, jurisdiction, cfr_title, cfr_part, publication_date, crawled_at, and similar provenance fields.
• S3-persisted config/manifest records (CountryConfig → SourceConfig → PartSpec, and per-source SyncManifest/PartState entries) — which sources exist per country, and incremental crawl/version state (content_hash, last_amendment_date, crawled_at) per part.

What's missing, relative to the PRD

None of the following exists in the current schema — every one of these is a genuinely new delta, not a refinement of an existing field:

1. Four-state per-country curation lifecycle (A–D). No field anywhere currently records "no source list / sources configured, not crawled / crawled, review pending / crawled and validated" as an explicit, queryable status. CountryConfig records that sources exist, and SyncManifest records that a part was crawled, but neither models "has this document cleared SME review" — that's new.
2. Document review decision + audit trail. Accept / Reject / Needs Review, the required comment on the latter two, and "who did what and why" per document, do not exist in OpenSearch metadata or anywhere else found. This is the biggest gap: the two "hard gate" human decisions the PRD describes (source-list curation, per-document review) currently have no corresponding schema at all — they may exist purely as an unbuilt concept, consistent with the PRD's own framing of the ops dashboard as "proposed, not yet built."
3. Legal Track (Statute vs. Regulation). No field distinguishes a USC statute (Track 1) from the CFR regulation implementing it (Track 2) on any ingested document today. metadata.cfr_title/metadata.cfr_part exist for CFR content, but there's no equivalent USC field or a discriminator field to tell the two apart at the document level.
4. Per-source health/count aggregation for the dashboard. The PRD's Country detail screen wants, per source: type, owning agency/ jurisdiction, last crawl date, document/pending counts, and health status. SourceConfig (S3 YAML) has type/agency/jurisdiction as static config; SyncManifest has last-crawl timestamps per part, but nothing aggregates counts or health status at the source level today — that would need to be computed (e.g. from OpenSearch queries + manifest scans) or newly persisted.
5. Vector-DB sync status per document. The PRD's dashboard wants "how many documents have made it into the vector DB" as a distinct count from "crawled" and "accepted" — implying acceptance and vector-sync are (or should be) two separate steps with two separate states. Today, nothing in the reverse-engineered pipeline shows an intermediate accepted-but-not-yet-synced state; bulk_index() appears to run as part of the same ingestion flow that also does chunking/embedding, with no review gate currently wired in between.

Proposed delta (draft — needs an engineer who owns this pipeline to confirm shape)

• A new document review record (new table, likely Postgres given the Postgres tenant catalog is this service's only relational store today) per ingested document, holding: document_id (matches the existing OpenSearch/S3 document_id), curation_state (A–D, or a document-level equivalent), review_decision (accepted | rejected | needs_review | pending), review_comment (required when decision is rejected or needs_review), reviewed_by (→ sys_user or an equivalent identity), reviewed_at, and legal_track (statute | regulation, defaulted from the crawl source per the two-track classification feature, confirmable/ correctable by the SME).
• A new country curation status record (or a computed view over the above, if per-document state is suf., aggregating source/state), one row per country, holding the A–D curation_state plus the aggregate counts the Home/Overview dashboard needs (total documents, pending review, accepted, rejected, validated %, documents live in the vector DB).
• A "changed since last crawl" flag, likely derived by comparing SyncManifest.content_hash against the previously accepted document's hash, surfaced onto the review record above rather than modeled as new raw storage — the hash comparison already exists in the manifest, only the "resurface for re-review" state is new.

Given how much of this is genuinely new rather than a refinement, the Codex Curator ops dashboard Feature (curator-ops-dashboard/spec.md) should not be treated as buildable purely on top of the existing pipeline without this delta landing first, or without the pipeline owner confirming a materially different existing design that this reverse-engineering pass missed.

Once the schema-first gate is signed on the above, apply this delta to ../../schema/ and flip status to applied.