Expand direct-sync schema and reconcile historical active content

Parent epic: #38
Owner architecture decision: #226 (closed)
Historical staged-row gate: #219 (implemented but final HUMAN/on-call evidence still open)
Projection/source baseline: #253 (blocked on a repaired immutable source revision)
Downstream evidence consumer: #272

Product outcome

Add the execution-disabled persistence and migration boundary needed by the owner-approved direct-sync architecture, then make a deterministic, reversible copy of each approved source's one unambiguous historically active ContentRelease state into source-owned current rows.

This slice does not run a sync, change a public query, contact GitHub, activate direct rows, or remove staged history. Historical ContentRelease, child, active-pointer, and path-claim rows remain the public authority and remain byte-for-byte readable. The new rows are inert reconciliation candidates until later #38 slices independently prove direct-upsert behavior and explicitly cut over each public family.

PM disposition — execution contract researched, still blocked / do not dispatch

Keep needs grooming. #226 fixes the architecture, and the repository fixes the current staged schema, but an engineer-ready migration cannot yet embed two required authorities:

1. #219 is still open for the actual applied-0004 classification, exact-image populated migration, readiness, failure drill, and deployed smoke. Its accepted 0005/0006 repair must either receive final acceptance or be explicitly superseded by a reviewed #38 migration plan before this lane rebases.
2. The owner-approved exhaustive source rollout manifest does not exist. It must name, in order, every live-direct, checked/baked-only, deferred, optional, and disabled source; exact repository/branch/adapter/mount/ownership partitions; the historical source row and one accepted active release used for expansion; and the public family each source may eventually own. #253 is also not a reproducible cutover baseline today.

The schema and safety semantics derivable without those values are frozen below. PM must replace the manifest placeholders with exact reviewed bytes and recompute the current migration leaf before removing needs grooming. No engineer may guess source membership from ContentSource.enabled, the checked projection, repository names, an active pointer, or current production rows.

Normative authority

• _docs/PROCESS.md: lifecycle, independent tester/PM gates, migration drift, versioned verification, and no implementation while needs grooming remains.
• _docs/specs/open-decisions.md, decision 1 / #226: direct source-owned upsert, draft/soft-delete visibility, SyncLog, source status, no staged candidate/activation/rollback graph, and accepted mixed-version direct-sync tradeoff.
• _docs/specs/01-platform-architecture.md and _docs/architecture/app-boundaries.md: one Django deployment; content owns persistence/business invariants; content_sync may later orchestrate verified input but cannot own these models; network/provider work stays outside transactions.
• _docs/specs/03-github-content-and-people.md: source ownership, immutable provenance, bounded parsing, public filtering, routes, relations/assets, edit provenance, and safe diagnostics remain authority. Its candidate/active-release/rollback vocabulary is stale where it conflicts with #226.
• _docs/specs/07-security-privacy-operations.md: bounded freshness, last-known-safe public behavior, monitoring, and secret/PII-safe evidence.
• _docs/specs/09-migration-rollout-roadmap.md and _docs/specs/10-verification-strategy.md: expand/reconcile/cutover/contract, idempotent dry-run/apply, SQLite/PostgreSQL portability, failure/concurrency evidence, and non-destructive rollback.
• #219 owns repair/preservation of the historical contract-digest graph. #253 owns reproducible source/projection identities. #272 consumes accepted direct-sync evidence later; neither defines this issue's schema.

Additive execution-disabled schema

All new primary keys are application-generated UUIDs. All timestamps are aware UTC values. SHA-1 fields are exact lowercase 40-hex; SHA-256 fields are exact lowercase 64-hex. All stable keys use the current bounded canonical grammar. Database constraints must be portable across SQLite and PostgreSQL; clean() alone is never the integrity boundary.

Extend existing ContentSource without changing its identity/configuration

Preserve every existing column, value, constraint, revision, secret reference, active_release, and staged-service behavior. Add nullable/default-safe direct-sync bookkeeping only:

• last_sync_status: exactly unavailable, queued, running, success, partial, failed, or skipped; expand default is unavailable;
• nullable protected last_sync_log to the immutable log below;
• last_attempted_commit plus last_attempted_at as an all-null or complete pair;
• retain last_successful_commit; add nullable last_successful_at; existing nonblank historical values are not synthesized into successful direct-sync evidence;
• last_remote_head_commit plus last_remote_head_observed_at as an all-null or complete pair;
• replace no existing lock field in this slice: add nullable opaque sync_lock_owner, lowercase 64-hex sync_lock_fence, and sync_lock_acquired_at as an all-null or complete triple; retain sync_locked_at as historical compatibility until the runner/contract slice owns a reviewed transition;
• retain pending_follow_up; it starts unchanged and no migration queues work;
• direct_sync_contract_version, adapter_contract_version, parser_policy_sha256, render_policy_sha256, selection_policy_sha256, and source_status_evidence_sha256, nullable during expansion and required together only after a later accepted live-enable transition.

Status/reference checks must permit only: unavailable with no log for expanded historical sources; or a status equal to the referenced log's accepted current state once a future service supplies that transition. This issue creates no fake success, remote observation, attempt, lock, or freshness evidence. The future source manifest must pin whether the existing minutes field is retained or replaced and the exact per-source 14-minute policy required by #38; this issue must not silently rewrite the current default of 60.

New immutable ContentSyncLog

Table/model name is ContentSyncLog to avoid collision with unrelated application logs; the public contract term remains SyncLog. One row is one (source, run identity, immutable input) and has:

• UUID id; protected source; positive schema_version, direct_sync_contract_version, adapter_contract_version;
• status exactly queued, running, success, partial, failed, or skipped;
• trigger exactly webhook, reconcile, manual, follow_up, or historical_reconcile; historical_reconcile is migration evidence only and is never mapped to a successful live sync;
• nullable safe reason_code from a code-owned allowlist; no free-form error/message column;
• immutable lowercase digests for run_identity, request_identity, delivery_identity, and idempotency_identity, each nullable only where its trigger contract declares it inapplicable;
• requested_at, nullable started_at, nullable finished_at, with exact state/timestamp checks;
• nullable commit_sha and tree_sha; commit/tree are required for terminal parsed-input states and absent for failures before an immutable checkout exists;
• nullable parser, rendering, selection, and source-policy digests;
• non-negative bounded selected_count, created_count, updated_count, unchanged_count, drafted_count, and rejected_count;
• partition_count, completed_partition_count, partition_state_sha256, visible_state_sha256, draft_state_sha256, and ownership_state_sha256;
• canonical evidence_sha256 and log_sha256, produced only by the future runner/evidence service after the applicable fields are complete;
• created_at only; no mutable updated_at, raw request/body/payload, delivery ID, repository URL, branch, source path/content, HTML/Markdown/YAML, signature, secret/token, actor PII, exception, stack, SQL, provider response, or arbitrary JSON.

Unique constraints are (source, run_identity) and (source, commit_sha, direct_sync_contract_version, adapter_contract_version) when a commit is present. Terminal states are immutable at the model/service boundary. This schema migration defines choices/checks only; no manager, signal, job, admin action, or transition service may create or advance a log in this slice. Exact per-status field arithmetic and transition checks remain blocked on the final #38 runner interface and must be pinned before dispatch rather than guessed in a migration.

Source-owned current rows

Add separate tables; do not add direct identities to or repurpose release-owned historical child rows.

CurrentContentDocument copies the existing document payload contract and adds direct ownership/provenance:

• UUID id; protected source; content_kind; stable_key;
• normalized source_path_sha256; last_applied_commit; source_checksum;
• visibility exactly draft, published, or soft_deleted; is_published is not duplicated;
• nullable protected historical_release and historical_document used only by expansion provenance;
• existing source timestamps, exact public path, slug/title/summary/canonical/SEO values, raw frontmatter/body/structured data, sanitized HTML, normalized text, bounded adapter metadata, noindex/edit URL, and contract provenance fields copied without reinterpretation;
• created_at and updated_at.

Unique (source, content_kind, stable_key). A non-null exact public path has a globally unique digest claim in CurrentContentPathClaim; it is not made public by merely existing. The historical foreign keys are all-null for future live direct rows and both present for copied rows.

CurrentContentRelation contains UUID id, protected source_document, relation type, target kind/key, nullable protected resolved direct target, nullable resolved public path, label, order, timestamp seconds, required flag, nullable protected historical_relation, and timestamps. Preserve the existing resolution and required-target checks and unique (source_document, relation_type, order). A relation may resolve only within the accepted source/ownership manifest or to an explicit public path; migration must not infer identity from title, name, or email.

CurrentContentAsset contains UUID id, protected source, stable_key, normalized source_path_sha256, last_applied_commit, source checksum, visibility, stable public path, storage key, content type, size, contract provenance, nullable protected historical_release and historical_asset, and timestamps. Unique (source, stable_key), globally unique storage_key, and one global path claim. Historical asset bytes are referenced/copied according to the manifest's exact custody rule; this migration performs no network upload and never invents a new asset checksum or storage key.

CurrentContentPathClaim contains lowercase SHA-256 path_digest primary key, exact public path, protected source, exactly one protected document-or-asset owner, visibility, nullable historical active-path provenance, and created_at/updated_at. It enforces digest shape, exact path shape, XOR owner, owner/source agreement in the owning service, and global uniqueness. Draft/soft-deleted claims reserve identity for reconciliation but are excluded from every future public selector until the cutover issue defines its query.

New immutable HistoricalContentReconciliation

One UUID row per manifest source and migration contract records migration evidence without fabricating a live sync:

• protected source; positive schema_version and migration_contract_version;
• manifest_sha256; nullable protected historical active release; source revision and active-pointer identity observed at snapshot;
• status exactly pending, reconciled, or quarantined;
• allowlisted reason_code only;
• historical and current document/relation/asset/path counts and canonical state digests;
• exact source commit, public-contract digest, copied-payload digest, ownership digest, and reconciliation digest when derivable;
• started_at, nullable finished_at, and created_at; no update timestamp or free-form payload.

Unique (source, migration_contract_version). A quarantined row contains only bounded codes, counts, digests, UUIDs, and timestamps—never rejected content or exception text. Reconciliation evidence is immutable after terminal state.

Expand, backfill, reconcile, and quarantine phases

The final engineer-ready issue must pin a code-reviewed canonical manifest file and its SHA-256. The current leaf is content.0006; PM must recheck it at dispatch. Use separate portable migrations so rollback and failure attribution are explicit:

1. Expand: create the new tables/columns, indexes, enum/check constraints, and nullable compatibility links. Do not alter/drop historical constraints, active_release, release statuses, children, ActiveContentPath, services, or queries. All existing rows remain valid; every source begins direct status unavailable.
2. Preflight/backfill: for each manifest source, lock/read one transactionally consistent historical snapshot. Require the exact source UUID/stable key/config revision and exactly the manifest-selected active release; require pointer/source ownership, active status, supported public-contract digest, complete child graph, path-claim equality, valid payload/checksum/provenance, and no duplicate identity/path/storage/contract ownership. Canonically order by stable keys—not database iteration—and deterministically generate direct UUIDs (UUIDv5 under a versioned migration namespace) from source/type/stable identity.
3. Quarantine: missing source/release/pointer, zero-or-multiple active candidates, pointer/status disagreement, unsupported digest, malformed/incomplete provenance, count/checksum mismatch, relation ambiguity, duplicate/cross-source identity, public/storage collision, or unknown manifest entry creates one source-level quarantined reconciliation row and no direct rows for that source. It must not block unrelated manifest sources from being classified, but the migration exits without claiming global readiness and later cutover for that source is forbidden.
4. Copy: for a source that fully passes preflight, copy every selected document/relation/asset/path payload and provenance exactly into current tables within one source transaction. No rendering, normalization rewrite, network, storage upload, service callback, audit/job, cache, search, graph, or public mutation occurs.
5. Reconcile: independently re-read and compare canonical counts/digests for payload bytes, relationships/order, routes, assets/storage/checksums, visibility, contract provenance, and ownership. Only exact equality creates terminal reconciled. The source remains last_sync_status=unavailable, because historical equivalence is not live-sync success.
6. Idempotence: reapplying the data operation with the same manifest and snapshot creates/changes nothing and returns identical evidence. A changed manifest, source revision, active pointer, child row, path claim, digest, or existing direct row conflicts/quarantines; it is never overwritten, adopted, or silently refreshed.

No direct row may be partially retained for a quarantined source. Transaction rollback removes that source's attempted rows and retains only bounded quarantine evidence through a separately safe transaction. The exact implementation mechanism and cross-database transaction test must be frozen after the manifest is approved.

Compatibility and rollback

• Old and new application images continue to read/write the historical staged schema during this expand-only interval. New tables are not public authority and there is no dual writer.
• Existing staged services may make the inert copy stale after migration; drift invalidates its reconciliation evidence and must be detected/rebuilt in a later reviewed reconciliation, never masked. This slice does not freeze editorial work or claim cutover readiness.
• Schema rollback before any later direct write is non-destructive: remove only the additive direct tables/columns and their generated evidence. Historical rows/pointers/constraints remain intact. The reverse data operation must identify only rows created by this migration contract and fail closed on later/unknown direct rows rather than deleting them.
• There is no product rollback to an older source SHA and no restored release activation feature. Post-cutover recovery belongs to forward source history under later #38 slices.
• The old activate/rollback APIs, Studio surfaces, queries, tests, and schema are removed only by the later contract slice after every consumer and compatibility window is accepted.

Acceptance criteria after blockers are resolved

• The issue embeds the owner-approved canonical source rollout manifest bytes/digest, exact current historical source/release identities, ownership partitions, enabled/disabled/baked dispositions, and current migration leaf; no runtime discovery or guessed source is allowed.
• Additive models/migrations implement the exact schema, enum, timestamp pairing, digest, UUID, count, XOR, unique, and portable integrity constraints above without changing any historical row or current public/staged behavior.
• Clean zero-to-leaf, populated 0006-to-leaf, already-applied/idempotent, forward, and safe-reverse migration tests pass on SQLite and PostgreSQL. They preserve every historical table count, PK, status, pointer, child payload byte/digest, route/path claim, source commit, public-contract digest, revision, and timestamp.
• Deterministic fixtures cover every manifest source and prove two clean runs produce byte-identical UUIDs, rows, ordering, counts, and reconciliation digests.
• Every ambiguity/collision/unsupported-provenance case quarantines only the affected source, writes no partial direct graph for it, leaks no input, and prevents any complete/readiness claim.
• Reconciled sources prove exact document/relation/asset/path counts and canonical payload, ordering, route, storage, visibility, provenance, ownership, and aggregate digests; historical import never produces a live success log/status.
• Existing staged prepare/ready/activate/rollback services and public queries pass unchanged compatibility tests against both pre-expand and expanded databases. Direct tables are unreachable from public, Studio, API, admin, jobs, commands, signals, and schedulers.
• Drift/replay tests cover changed manifest, source revision, pointer, status, child, relation order, path claim, asset, checksum, direct-row tamper, duplicate identity, and cross-source ownership; all fail closed without overwrite/adoption/deletion.
• Model/service tests prove terminal log/reconciliation immutability, safe status/reference rules, bounded allowlisted diagnostics, no sensitive fields, no database-generated ordering identity, and no reverse content → content_sync import.
• Network/socket/subprocess/provider/secret/job/audit/cache/search/graph guards prove the migration is offline, execution-disabled, and side-effect-free apart from its database rows.
• make migrations-check, make test-migrations, focused content/content_sync tests, database portability, quality/type, graph-selected full Django, and container verification pass under a versioned plan with exact base/head/graph/plan/evidence digests.
• Browser/render/screenshots are not_applicable: this issue changes no URL, template, copy, navigation, cache/public selector, Studio/API surface, or rendered content. Any such change requires PM re-grooming and the normal desktop/mobile screenshot gate.

Explicit non-goals

• No sync runner, transition service, direct upsert, stale sweep, soft-delete execution, retry/watchdog, source lock acquisition, webhook, HMAC/delivery fence, checkout, GitHub/network/provider call, scheduler, job, signal, or reconciliation polling.
• No public/direct read cutover, dual read fallback, dual writer, preview, activation, rollback action, route/search/graph/sitemap/feed/cache/invalidation rebuild, asset upload, or render change.
• No source configuration UI, Studio/admin/API/command surface, permission/audit workflow, operator override, --force, or production-data operation.
• No mutation, normalization, deletion, reparenting, digest rewrite, or reinterpretation of ContentRelease, ContentDocument, ContentRelation, ContentAsset, ActiveContentPath, source pointers, or #219 history.
• No hard deletion of source-owned content; no cross-source adoption/transfer; no identity matching by display name/email/title; no guessed source set or branch head.
• No ContentRelease resurrection as direct-sync authority and no claim of a site-wide atomic snapshot.
• No repair or blessing of #253 projection bytes and no implementation of #272 receipts.

Dependency and grooming exit

Blocking order:

1. final #219 HUMAN/on-call classification and acceptance, or an explicit reviewed supersession plan preserving all historical states;
2. owner approval of the exhaustive source rollout/ownership/historical-selection manifest, including exact canonical bytes and digest;
3. PM rebase audit against the then-current content migration leaf and current models/services;
4. PM replaces the remaining transition/arithmetic/manifest placeholders with exact model/migration interfaces and fixtures, then removes needs grooming.

#253 is required before any later projection/public-authority cutover, but this issue may expand/reconcile historical database rows once steps 1–4 are satisfied. #272 remains downstream of accepted live direct-sync evidence. #273 must not be dispatched while needs grooming remains.

Start with _docs/PROCESS.md, _docs/specs/open-decisions.md, and the migration and verification roadmap, then review #219 and #253. No implementation file is named, and dispatch is blocked until the applied-0004 evidence, repair acceptance, exhaustive source manifest, and reproducible projection baseline exist; done means a reviewed, additive, execution-disabled migration plan can be frozen without guessing source membership.