PostgreSQL Store

PostgresRegistryStore in src/rimthan_publication/store.py is the explicit database boundary for drafts, revisions, publication jobs, immutable releases, artifacts, channel pointers, and database-backed MCP loading. It uses psycopg only when the backend extra is installed and keeps filesystem authority limited to temporary release snapshots.

Migration model

apply_migrations(include_roles=False) scans src/rimthan_publication/migrations/*.sql with a strict filename pattern, creates rimthan_registry.schema_migrations, takes a PostgreSQL advisory transaction lock, checks migration SHA-256 if already applied, and inserts each migration digest after applying it. include_roles=True includes 0002_registry_roles.sql; Admin API startup uses it.

Key migrations:

Migration	Main responsibility
0001_registry_backend.sql	Base authoring and registry schemas, workflows, drafts, revisions, releases, release artifacts, channels, jobs, and audit events.
0002_registry_roles.sql	NOLOGIN group roles for rimthan_admin_api, rimthan_publisher, and rimthan_mcp.
0003_workflow_engine.sql	Adds workflow_kind, step-gate artifact kinds, workflow source shape, registry asset drafts/revisions, and validator definition drafts/revisions.
0004_published_identity_and_history.sql	Adds published_artifact_identities, immutable triggers for revision/audit/identity tables, and release-transaction-only insert triggers.
0005_release_provenance_and_content_integrity.sql	Adds digest verification triggers for release manifests and artifact bytes and adjusts workflow source identity.

Authoring schemas

The authoring schema owns mutable heads and immutable histories:

• rimthan_authoring.workflows: workflow ID, display name, route, and workflow_kind (module-graph or step-gate).
• workflow_drafts: current mutable head with revision, document, semantic digest, editor layout, updated actor/time.
• workflow_revisions: append-only snapshots of every document and layout.
• registry_asset_drafts and registry_asset_revisions: step-gate asset draft heads and immutable asset histories.
• validator_definition_drafts and validator_definition_revisions: validator draft heads and immutable histories.
• admin_audit_events: append-only actor/action records.

save_engine_workflow_draft validates a workflow-engine-draft, enforces optimistic concurrency, writes the head, appends a revision, updates asset/validator draft heads only when their semantic digest changes, appends asset/validator revision rows, and records engine_workflow_draft_saved. migrate_workflow_to_engine requires a legacy module-graph head and records workflow_migrated_to_engine.

Registry schemas

The registry schema owns immutable published bytes and mutable channel pointers:

• registry_releases: release identity, manifest bytes, manifest digest/size, source revision, publisher, mode, timestamp.
• release_artifacts: path, kind, MIME type, content bytes, digest, size for every release artifact.
• release_workflow_sources: binds release to workflow revision/source shape.
• published_artifact_identities: ledger of first-published artifact path identities per registry.
• registry_channels: named active release pointers with generation counters.
• publication_jobs: idempotent job state.

0004 and 0005 make immutability enforceable in the database, not just Python. Release content rows can be inserted only in the same transaction as their new release. Revision and audit rows reject update/delete. Release bytes must match stored SHA-256 and size. Every release_artifacts insert must match the existing or newly inserted published artifact identity ledger.

PostgresRegistryStore._insert_release repeats those checks before the database triggers fire. It parses the manifest bytes and verifies release columns agree with manifest fields, rejects an existing (registry_id, release) with a different manifest digest, checks every artifact path is safe and every stored byte sequence matches its SHA-256 and size, inserts or compares published_artifact_identities, writes release_artifacts, then writes release_workflow_sources. The base migration adds release_workflow_sources_immutable to reject update/delete after insertion, and migration 0004 adds release_workflow_source_insert_with_release so source rows can be inserted only in the same transaction as their new release. For aggregate step-gate publication, _insert_release first copies all workflow source rows from the source registry release except the workflow being newly published, preserving their source_kind, route module fields, and workflow_version; it then inserts the new workflow source row. This is how a new step-gate release can carry forward other workflows without weakening their historical source shape.

Roles and trust boundary

The deployment contract in docs/operations/workflow-studio-staging.md requires distinct principals:

• Admin API currently uses a migration-capable database owner in staging, because it applies migrations and writes authoring/publication state.
• MCP must use a dedicated login granted only the rimthan_mcp role. It should read immutable release catalogue state and channel pointers, not authoring drafts and not channel writes.

The staging document records live privilege checks proving the MCP principal can read immutable catalogue state but receives permission errors for authoring drafts and channel writes.

Database-backed release loading

McpScope.load(..., database_dsn=..., registry_channel=..., trust_root=...) calls store loading paths that reconstruct a verified release snapshot from PostgreSQL. DatabaseReleaseSnapshot contains a RegistryRelease and a temporary exact-byte filesystem cache used by ReleaseLoader; callers must close it to clean the temporary directory.

Active channel loading requires a trust root for published releases. PostgresRegistryStore.load_active_release selects the channel state, reconstructs release bytes, and verifies with ReleaseLoader. HTTP MCP refresh uses this to atomically replace the in-memory scope only after candidate verification.

Tests and failure cases

tests/test_postgres_publication_e2e.py::test_postgres_publish_activate_mcp_and_rollback_exact_releases covers applying migrations and importing a base release, activating base and published releases, loading active releases through MCP resources, exact manifest resource reads, optimistic draft concurrency, publication and channel generation changes, rollback by activating an older release ID, idempotency conflict detection, immutable update rejection for registry_releases, manifest byte/digest mismatch rejection, and artifact identity mismatch rejection.

tests/test_admin_api.py::test_admin_api_authors_publishes_and_lists_a_signed_release covers the same database through HTTP, proves Studio/Admin publication can feed database-backed MCP refresh, exercises explicit legacy-to-engine migration and multi-workflow step-gate publication, and includes PostgreSQL participant-marker non-persistence checks for workflow-engine request data.

Focused validation

• Store and migrations: RIMTHAN_TEST_POSTGRES_DSN=... uv run pytest tests/test_postgres_publication_e2e.py
• Admin API database flow: RIMTHAN_TEST_ADMIN_POSTGRES_DSN=... uv run pytest tests/test_admin_api.py
• Non-database publication logic: uv run pytest tests/test_workflow_publication.py