Automate catalog binding synchronization while preserving catalog IDs #35

Problem

Catalog bindings must stay aligned with WranglesPY operations and Registry documents when names or repository locations change. These changes must update the existing catalog entry without allocating a replacement catalog_id.

Today, Registry compilation reconciles catalog rows using executable keys and reports differences between registry_path and the actual document location. It does not synchronize those differences back to the catalog. Matching by mutable names alone cannot reliably distinguish a rename from a new entry.

Routine synchronization must be automatic. Manually updating SQL, copying paths, or approving individual metadata updates is not the intended operating process.

Related design: #32, especially central catalog identity, authoring ownership, and versioned Registry publication. Related bindings work: #34. This issue tracks the synchronization workflow; it does not itself apply database changes or authorize production cutover.

Required outcome

Provide an automated workflow that reconciles approved versions of WranglesPY and Wrangles-Docs with the existing authoritative PostgreSQL wrangles_catalog, updates changed bindings, and publishes a matching versioned Registry snapshot while preserving catalog identities.

Field meanings:

Use the existing shared catalog and existing tables. Do not introduce independent environment counters or additional database tables. Represent catalog IDs as decimal strings in JSON and generated artifacts.

Implementation scope

1. Stable identity across source changes

• Define a durable identity reference that survives a file move or public-key rename. A proposed approach is to carry the centrally allocated catalog_id in source metadata and exported manifests; determine its exact location without creating duplicate authoring of mutable facts.
• Automate the initial association of existing source entries with their verified catalog IDs. Report ambiguous or missing associations instead of guessing.
• Do not infer that an entry is new merely because its old name or path disappeared. Missing or ambiguous identity must fail validation, not allocate a replacement ID or delete a row.
• For genuinely new entries, define idempotent automatic registration through the central allocator and persistence of the returned identity reference. Retries must not create duplicate entries.

2. Automated reconciliation and updates

• Define one authoring owner for each mutable field and one process responsible for applying catalog updates. Avoid bidirectional last-write-wins synchronization.
• Consume pinned runtime and Docs revisions, validate their compatibility, and prepare changes for the existing rows identified by catalog_id.
• Apply only the fields owned by this workflow, transactionally, without changing catalog IDs, saved-model IDs, permissions, or unrelated metadata.
• Support multiple catalog entries binding to the same executable operation where the Registry contract permits it.
• Make retries idempotent and prevent concurrent or older jobs from overwriting a newer synchronized state.
• Retain entries when a source is missing; removal/deprecation must follow an explicit lifecycle policy, not file disappearance.

3. CI and publication

• Run read-only reconciliation for pull requests and automatic synchronization from the agreed trusted merge/release event. Routine successful runs must not require manual SQL or a separate approval for every binding update.
• After synchronization, export the authoritative catalog and regenerate the Registry bundle using the selected source revisions.
• Validate the complete bundle before publishing it. Preserve previously published versions and declare the compatible runtime version.
• Record source revisions, affected catalog IDs, changed fields, and results in the run output. Provide a dry-run mode for investigation and define recovery if publication fails after a database update.
• Validate real runtime compatibility for renamed public operations; documentation aliases alone must not be treated as preserving old executable recipes.

Examples

Acceptance criteria

• Existing source entries can be associated with their catalog identities without recurring manual mapping updates.
• Document moves and supported binding/key changes are detected and synchronized automatically while retaining the original catalog IDs.
• New-entry registration is idempotent and uses only the central PostgreSQL allocator.
• Missing/ambiguous identity, duplicate IDs or keys, incompatible runtime bindings, and stale updates are rejected with actionable diagnostics.
• A repeated successful run makes no further changes; concurrency and failed-publication recovery are covered.
• Generated snapshots preserve IDs exactly, including IDs above JavaScript's safe-integer range, and earlier published bundles remain unchanged.
• Existing saved-model IDs, permissions, supported legacy recipes, and unrelated catalog fields are preserved.
• Automated validation covers moves, renames, shared callable bindings, retries, conflicts, and new-entry registration.
• The CI trigger, ownership rules, bootstrap procedure, and recovery procedure are documented; routine synchronization requires no manual SQL.