Dead release.* Schema — DROP Audit & Plan

CURRENCY NOTE (2026-08-06, SPRINT_20260722_018 RO-3) — measure before you drop. This audit’s table census no longer matches the estate. Measured live on stellaops_platform, the release schema holds 42 tables (relkind IN ('r','p')), decomposing exactly as the Release Orchestrator’s 22 + Platform’s 19 live ones (Migrations/Platform/045,046,048,051-056) + schema_migrations. The confirmed-DEAD set below is therefore EMPTY on this estate, and release.security_finding_projection (the X9 candidate) does not exist here at all. The reason is structural rather than a change of mind: the ~72-table baseline this audit was written against is unreachable twice over — StellaOps.Platform.Persistence.csproj (renamed from StellaOps.Platform.Database 2026-08-18) excluded Migrations/Release/** from its embedded resources (the stream was archived under Migrations/_archived/pre_1.0/mig061/Release/v1/ on 2026-09-14, SPRINT_20260914_005 PEF-2), and platform-web registers the assembly with ResourcePrefix = "…Migrations.Platform" — so a database created after either filter landed never gets those tables. This document still describes a database built before them, and its DEAD/LIVE reasoning per table remains the right input; only the census and the drop-set size are stale. Re-measure the target database before drafting any DROP, and treat a non-empty drop set as a finding about that database rather than as this document’s expectation.

RE-CORROBORATED 2026-08-17 (021 PLT-4 estate-wide read) and RECONCILED 2026-08-24 (SPRINT_20260722_018 RO-6) — the drop set is EMPTY on this estate on two independent measurements eleven days apart. release still holds exactly 42 tables (RO’s 22 + Platform’s 19 + schema_migrations), so the confirmed-DEAD 37-table set in §4 has zero members present here and there is nothing destructive to approve on this estate — only a reconciliation to record. RO-5 completed its live M4 window 2026-08-22 (D-RO5-9, docs/implplan/SPRINT_20260722_018_ReleaseOrchestrator_database_separation.md): RO now runs on its own stellaops_release_orchestrator database, and this document’s DROP analysis is about the legacy release.* residue left behind in stellaops_platform, retained for rollback. DROP SCHEMA release in stellaops_platform stays forbidden regardless of that move — Platform owns 19 live tables in it (§3.10; see the P: attribution correction there). A non-empty drop set found on any other estate is a finding about THAT database, not a contradiction of this note — re-run the §2 method before treating this document’s zero as universal. Sprint reference note: SPRINT_20260722_018_ReleaseOrchestrator_database_separation.md moved to docs-archive/implplan/ on 2026-08-25 (sprint archive wave); the citation above still names the completed program correctly. Status: PROPOSAL — NOT WIRED. No migration file exists for this plan. Audit performed 2026-07-04 against working tree 04673987208e (follow-up to the deployment-capability audit + the 2026-07-04 live E2E pass, SPRINT_20260703_006). The DROP SQL in §5 is quarantined in this document only; it must NOT be copied into any Migrations/ directory (and therefore into the embedded-resource glob) until an operator explicitly approves it (§7).

1. Context — two parallel schemas

The platform database carries two parallel release data models:

• release_orchestrator.* — the LIVE truth. Owned by src/ReleaseOrchestrator/__Libraries/StellaOps.ReleaseOrchestrator.Persistence/Migrations/001_v1_releaseorchestrator_baseline.sql (releases :312, release_components :329, approvals :362, gate_decisions :381, release_evidence :397, deployment_jobs :480, deployments :269, audit_entries :36, …). This is what the Release Orchestrator WebApi reads and writes for the release lifecycle.
• release.* — a large parallel model (~72 tables) created by the Platform-owned baseline src/Platform/__Libraries/StellaOps.Platform.Persistence/Migrations/_archived/pre_1.0/mig061/Release/v1/001_v1_platform_database_release_baseline.sql (archived path since 2026-09-14) (71 tables; +1 more in 004_v1_federated_telemetry_bundles.sql). Much of it is a dead predecessor model — but not all of it: a second, live owner also creates tables in the same release schema: src/ReleaseOrchestrator/__Libraries/StellaOps.ReleaseOrchestrator.Environment/Migrations/001_v1_releaseorchestrator_environment_baseline.sql (environments :115, targets :136, agents :93, integrations :71, freeze_windows :166, regions :217, infrastructure_bindings :238, topology_point_status :279, pending_deletions :305, freeze_window_exemptions :361, plus stub releases :183 and promotions :192 kept as FK targets), and src/ReleaseOrchestrator/__Libraries/StellaOps.ReleaseOrchestrator.Agent/Migrations/001_v1_releaseorchestrator_agent_baseline.sql (agents :50, agent_registration_tokens :95).

Only the owning src/ReleaseOrchestrator/__Apps/StellaOps.ReleaseOrchestrator.WebApi/Program.cs runs the Environment migrations and stores against RO’s authoritative release.* tables. Platform and Concelier stopped calling AddReleaseOrchestratorEnvironmentPostgresData and stopped applying the ReleaseOrchestrator.Environment startup migrations on 2026-07-11. CM-6 completed the boundary: Platform’s environment/script/federation compatibility hosts are deleted, Gateway routes those public operations directly to RO, and Platform persistence no longer carries ReleaseOrchestratorMigrationModulePlugin or its source reference (the whole plugin mechanism was deleted on 2026-09-14, SPRINT_20260722_021 PLT-4).

DTC-7 (SPRINT_20260703_002) still builds Platform-owned release.topology_*_inventory plus platform.context_regions / platform.context_environments, but it no longer reads the legacy Platform-database copies of release.regions/environments/targets/agents. Platform enumerates active tenants from its own shared.tenants and consumes RO’s tenant-scoped schema-v1 GET /internal/v1/release-orchestrator/topology-snapshot owner API. The call is direct (no Router route), signed for two minutes with only release:read, and has no SQL fallback. The RO response unions built-in and extended agent capabilities and maps canonical Stale status to wire offline before Platform updates its rebuildable projection. Verified against d3358c84319b0da835e944d70f360f7df526d9c5; re-verify with rg -n "PostgresTopologySourceReader|release\.(regions|environments|targets|agents)" src/Platform/StellaOps.Platform.WebService and rg -n "topology-snapshot" src/Platform src/ReleaseOrchestrator src/Router devops.

So a blanket DROP SCHEMA release is not possible. This audit classifies every table and derives the safe DROP subset.

2. Method

• Enumerated every CREATE TABLE … release.* in the active (non-_archived) migrations of both owners (71 in the Platform baseline + 1 in Platform 004 + 6 RO-Environment/Agent-only tables = 78 distinct tables).
• Grepped src/ and devops/ (*.cs, *.sql, *.sh, *.ps1, *.yml, *.yaml, *.json, *.ts) for release.<table> readers/writers, excluding _archived/**, frozen evidence under src/Web/StellaOps.Web/dist/**/docs-content/**, and the CREATE-side baseline itself.
• Filtered case-insensitive false positives (C#/TS property access such as release.Components — e.g. ArtifactGenerator.cs:56 — is not table usage).
• For every store class that references a table, verified whether any host actually wires it into DI (a store class with zero registrations is dormant code, not a live consumer).
• Checked for unqualified/dynamic table-name composition (none found for the DEAD set; note release_orchestrator_agent.agent_tasks — AgentTaskDataSource.cs:16 — is a different live table from the dead release.agent_tasks).

Classifications

3. Per-table classification

Baseline line numbers refer to src/Platform/__Libraries/StellaOps.Platform.Persistence/Migrations/_archived/pre_1.0/mig061/Release/v1/001_v1_platform_database_release_baseline.sql (P: prefix; archived path since 2026-09-14, SPRINT_20260914_005 PEF-2) and src/ReleaseOrchestrator/__Libraries/StellaOps.ReleaseOrchestrator.Environment/Migrations/001_v1_releaseorchestrator_environment_baseline.sql (E: prefix) unless stated otherwise.

3.1 Integration hub block

3.2 Environment / topology block (live)

3.3 Release-management block (Platform shape — mostly dead)

3.4 Workflow block

3.5 Deployment block

3.6 Agent history block (Platform shape)

3.7 Evidence blocks

3.8 Plugin registry block (Platform shape)

3.9 Policy-gate block

3.10 Control-bundle / trust / read-model / run blocks

P: attribution corrected (RO-1 §3, 2026-08-06; reconciled here 2026-08-24, RO-6) — the DEAD/LIVE verdicts below are unaffected. The P: line numbers in this subsection point at Migrations/Release/001_v1_platform_database_release_baseline.sql, which §1’s currency note already establishes is unreachable (excluded from StellaOps.Platform.Persistence.csproj’s embedded resources, and filtered again by platform-web’s ResourcePrefix). That file is not the live creation authority for anything that actually exists in release on this estate. The eight tables below classified LIVE-USED (control_bundle* ×4, trust_* ×4) are part of Platform’s 19 live tables and are actually created by the embedded, auto-applying Platform-category migrations 045_ReleaseControlBundleLifecycle.sql, 046_TrustSigningAdministration.sql, 048_ReleaseReadModels.sql, 051_IntegrationSourceHealth.sql, and 052–056 (the run_* block) — not the un-embedded Release baseline. The release_read_model / run_* tables classified DEAD below never had a reachable CREATE authority at all (their P: baseline is unreachable and they carry no Platform-category migration), which is consistent with §4’s confirmed-DEAD set being measured EMPTY on this estate. The P: line numbers are kept as historical pointers into the file this 2026-07-04 audit was originally written against.

3.11 Topology inventory read-model (DTC-7)

These are Platform-owned projection targets, not RO source tables. CM-6 changed their source transport from cross-owner SQL to the RO owner API; the table classification and DROP protection do not change for Platform’s copy, which 099_v6_release_topology_projection_retention_classes.sql classifies derived-rebuildable. RO’s consolidated baseline also created a copy of all nine in stellaops_release_orchestrator with no writer or reader; RO migration 009_release_orchestrator_retire_topology_inventory_projection.sql retires that copy behind a SQLSTATE 55000 emptiness guard (SPRINT_20260916_001 RFC-14, owner ruling 2026-09-23).

Related (same DTC-7 flow, platform schema, NOT in scope of any DROP): platform.context_regions (P:3294) and platform.context_environments (P:3304), served by PlatformContextService.cs.

3.12 Federated telemetry

3.13 X9 family + X-new-4 dormant blocks — retire candidates (folded in 2026-08-24, RO-6)

Neither of these was in the 2026-07-04 census that produced §3.1–§3.12; both surfaced later, at RO-1’s 2026-08-06 M0 inventory. Recorded here per RO-6’s reconciliation task rather than assumed into §4’s DROP SQL — see the per-item disposition below.

X9 — release.security_finding_projection and four siblings (CONFIRMED RETIRE, RO-1 §4). security_finding_projection, security_disposition_projection, security_sbom_component_projection, security_sbom_graph_projection are declared only in the archived Platform.Database/Migrations/_archived/pre_1.0/mig061/Release/050_SecurityDispositionProjection.sql — doubly unreachable (archived and under the excluded Release/ folder), a different and even more thoroughly dead migration file than the 001_v1_platform_database_release_baseline.sql this document otherwise audits, so they were never part of §2’s enumeration. Every live code path targets findings_security.security_finding_projection (StellaOps.Findings.Security.Persistence/…/SecurityFindingProjectionStore.cs:276,307,413,441); the sole remaining reference to the release.* name is Findings.Security’s own backfill (002_v1_backfill_from_release.sql:35), guarded by to_regclass('release.security_finding_projection') IS NOT NULL, so retiring the table only turns that guard into a permanent no-op. Measured: does not exist on this estate (to_regclass → null, RO-3 2026-08-06). Not added to §4’s DROP SQL — it has no CREATE authority to drop against on this estate, and any estate where it does exist inherited it from the archived file, which is a finding about that estate’s migration history, not something this document’s forward-only DROP should special-case.

X-new-4 — EvidenceThread (5 tables, §3.7) and PolicyGate (3 tables, §3.9), reclassified from “dormant” to retire candidates. RO-1’s 2026-08-06 pass confirmed what §3.7/§3.9 could only call ambiguous: AddEvidenceThread (StellaOps.ReleaseOrchestrator.EvidenceThread) has zero callers repo-wide. CM-6 deleted its former non-test consumer, Platform’s Endpoints/EvidenceThreadEndpoints.cs; no RO EvidenceThread runtime surface was added by that boundary cut (EvidenceLocker owns its separate live evidence surface). AddPolicyGateServices (StellaOps.ReleaseOrchestrator.PolicyGate) likewise has zero callers; its only non-test consumer is …Performance, itself consumed only by its own test project. Classification in §3.7/§3.9 stays AMBIGUOUS-KEEP, not DEAD — per §6 risk 5, a table graduates out of AMBIGUOUS-KEEP only once its store code is deleted or wired, and that deletion has not happened yet. What changes is the next step: these two libraries are now named retire candidates (delete the dormant store code, then reclassify the 8 tables), not an open question needing more evidence.

4. Confirmed-DEAD set (safe to DROP after approval)

Measured EMPTY on this estate — RO-3 (2026-08-06), re-corroborated by 021 PLT-4 (2026-08-17), reconciled here by RO-6 (2026-08-24). None of the 37 tables below exist in this estate’s release schema (see §1’s currency note); there is nothing to approve or drop here, only the reconciliation this section records. Treat a non-empty result on any other estate as a finding about that database, per §1.

37 tables (+1 partition dropped implicitly), all with zero runtime readers/writers:

workflow_step_logs, workflow_steps, workflow_runs, workflow_templates, gate_results, gate_types, approvals, evidence_export_packets, evidence_exports, evidence_packets, deployment_events, deployment_artifacts, deployment_tasks, deployment_jobs, agent_capabilities, agent_heartbeats (+ agent_heartbeats_default partition), agent_tasks, release_components, component_versions, components, release_tags, plugin_instances, plugin_versions, plugins, plugin_types, integration_health_checks, release_read_model, release_activity_projection, release_approvals_projection, run_gate_decision_ledger, run_input_snapshots, run_approval_checkpoints, run_deployment_timeline, run_capsule_replay_linkage, integration_feed_source_health, integration_vex_source_health, integration_source_sync_watermarks

Explicitly NOT in the DROP set: everything classified LIVE-USED, DTC-7-SYNCED, or AMBIGUOUS-KEEP above — in particular releases, promotions, integrations, integration_types, the policy-gate trio, and the evidence-thread block.

The migration-script tests that mention dead tables (ReleaseReadModelMigrationScriptTests, Run*MigrationScriptTests, IntegrationSourceHealthMigrationScriptTests) assert the baseline SQL text, which the DROP migration does not modify (forward-only: the baseline file is never edited) — they stay green.

5. Quarantined DROP migration (proposal — do NOT create as a file yet)

Forward-only, children-before-parents per the FK graph extracted from the Platform baseline. Written as a release-category migration (100+ numeric prefix ⇒ requires explicit stella system migrations-run, never auto-applied at startup) — but again: this SQL lives only in this document until approved.

2026-09-14 (SPRINT_20260722_021 PLT-4): the release-category applier named above no longer exists — the stella system migrations-* CLI is deleted and a 100+ file must not exist in the tree (NoManualCategoryMigrationsTests). If this drop is ever approved it is executed by hand per docs/runbooks/database/migration-recovery.md (restore-verified pg_dump -Fc, rolled-back dry run with a survivor-set diff, then the real run) and recorded in the ledger — the same three-clause drop gate PLT-4 used for the 2026-09-13/14 windows — not as a migration file.

-- PROPOSED (never created; path shown as originally proposed, project since renamed StellaOps.Platform.Persistence and Migrations/Release archived 2026-09-14):
-- src/Platform/__Libraries/StellaOps.Platform.Database/Migrations/Release/100_v1_drop_dead_release_parallel_model.sql
-- Category: Release (100+ prefix => NEVER auto-applied; operator runs it explicitly).
-- Forward-only per ADR-004. Requires a PostgreSQL snapshot first
-- (docs/runbooks/database/migration-recovery.md, "Snapshot Before Upgrade").
--
-- Drops the confirmed-DEAD subset of the legacy release.* parallel model.
-- Audit: docs/modules/release-orchestrator/data-model/dead-schema-drop-plan.md
-- (2026-07-04, working tree 04673987208e). Every table below has zero runtime
-- readers/writers in src/ and devops/; live data lives in release_orchestrator.*,
-- release_orchestrator_agent.*, or the LIVE-USED release.* tables (kept).
--
-- Ordering: FK children before parents (no CASCADE, so an unexpected dependent
-- object fails loudly instead of being silently destroyed).

-- Workflow block (steps -> runs -> templates)
DROP TABLE IF EXISTS release.workflow_step_logs;
DROP TABLE IF EXISTS release.workflow_steps;
DROP TABLE IF EXISTS release.workflow_runs;
DROP TABLE IF EXISTS release.workflow_templates;

-- Gate block (results FK gate_types + promotions[KEPT])
DROP TABLE IF EXISTS release.gate_results;
DROP TABLE IF EXISTS release.gate_types;

-- Approvals (FK promotions[KEPT]; live approvals = release_orchestrator.approvals)
DROP TABLE IF EXISTS release.approvals;

-- Evidence-packet block (export_packets FK exports + packets; packets FK deployment_jobs)
DROP TABLE IF EXISTS release.evidence_export_packets;
DROP TABLE IF EXISTS release.evidence_exports;
DROP TABLE IF EXISTS release.evidence_packets;

-- Deployment block (events FK jobs+tasks; artifacts FK jobs; tasks FK jobs+targets[KEPT];
-- jobs FK self + environments[KEPT] + promotions[KEPT])
DROP TABLE IF EXISTS release.deployment_events;
DROP TABLE IF EXISTS release.deployment_artifacts;
DROP TABLE IF EXISTS release.deployment_tasks;
DROP TABLE IF EXISTS release.deployment_jobs;

-- Agent history block (children of release.agents[KEPT]; heartbeats is partitioned —
-- dropping the parent drops the agent_heartbeats_default partition)
DROP TABLE IF EXISTS release.agent_capabilities;
DROP TABLE IF EXISTS release.agent_heartbeats;
DROP TABLE IF EXISTS release.agent_tasks;

-- Component block (release_components FK component_versions + releases[KEPT];
-- component_versions FK components; components FK integrations[KEPT])
DROP TABLE IF EXISTS release.release_components;
DROP TABLE IF EXISTS release.component_versions;
DROP TABLE IF EXISTS release.components;

-- Release tags (FK environments[KEPT] + releases[KEPT])
DROP TABLE IF EXISTS release.release_tags;

-- Plugin block (instances -> versions -> plugins -> plugin_types)
DROP TABLE IF EXISTS release.plugin_instances;
DROP TABLE IF EXISTS release.plugin_versions;
DROP TABLE IF EXISTS release.plugins;
DROP TABLE IF EXISTS release.plugin_types;

-- Integration health checks (FK integrations[KEPT])
DROP TABLE IF EXISTS release.integration_health_checks;

-- Read-model projections (children of control_bundle*[KEPT])
DROP TABLE IF EXISTS release.release_read_model;
DROP TABLE IF EXISTS release.release_activity_projection;
DROP TABLE IF EXISTS release.release_approvals_projection;

-- Run-evidence block (ledger FK run_input_snapshots; all FK control_bundle*[KEPT])
DROP TABLE IF EXISTS release.run_gate_decision_ledger;
DROP TABLE IF EXISTS release.run_input_snapshots;
DROP TABLE IF EXISTS release.run_approval_checkpoints;
DROP TABLE IF EXISTS release.run_deployment_timeline;
DROP TABLE IF EXISTS release.run_capsule_replay_linkage;

-- Integration source-health block (no dependents)
DROP TABLE IF EXISTS release.integration_feed_source_health;
DROP TABLE IF EXISTS release.integration_vex_source_health;
DROP TABLE IF EXISTS release.integration_source_sync_watermarks;

Notes on the ordering:

• No CASCADE anywhere: if a live estate has grown an out-of-band dependency (view, FK, trigger) on one of these tables, the DROP fails loudly and the operator investigates instead of silently losing the dependent object.
• release.update_updated_at_column() (E:31) is shared with live tables and is NOT dropped; per-table triggers disappear with their tables.
• On a fresh DB the baseline still creates these tables and this migration then drops them — the schema converges identically on fresh and existing databases (the bootstrap fallback devops/compose/postgres-init/00-v1-baseline.sql creates only 5 release.* tables — control_bundles, infrastructure_bindings, pending_deletions, regions, topology_point_status — none of them in the DROP set).
• Do NOT edit the applied baseline to remove the dead CREATEs — that is exactly the checksum landmine documented in docs/runbooks/database/migration-recovery.md (“Baseline re-slim checksum mismatch”).

6. RISK

1. This DROPs data on the shared live PostgreSQL instance. Even “dead” tables may contain rows written by pre-consolidation builds. The migration is irreversible (forward-only, ADR-004). It therefore requires:
   • an explicit operator go-ahead (deploying/publishing rule, AGENTS.md §4.7), and
   • a PostgreSQL snapshot first, per docs/runbooks/database/migration-recovery.md (“Snapshot Before Upgrade” / “Rollback Procedure”).
2. Must NOT be wired into the embedded-migration glob until approved. The persistence csproj embeds Migrations\**\*.sql (StellaOps.Platform.Persistence.csproj); the moment this file lands in Migrations/Platform/ (Migrations/Release/ was archived 2026-09-14), every platform-web image build carries it. Numbering it 100_* keeps it out of startup auto-apply (release category requires stella system migrations-run --module Platform --category release), but the file must still not exist in the tree until the go-ahead. (2026-09-14, PLT-4: that command is deleted and a 100_* file is now forbidden outright; see the note in §5.)
3. Row-count preflight is mandatory. Before running, the operator must verify the DEAD tables are empty or that their contents are confirmed disposable:

   SELECT relname, n_live_tup
   FROM pg_stat_user_tables
   WHERE schemaname = 'release'
     AND relname IN ('workflow_runs','evidence_packets','deployment_jobs', /* … full §4 list … */ 'agent_heartbeats')
   ORDER BY n_live_tup DESC;
   

   (n_live_tup is an estimate — for a non-zero result, confirm with SELECT count(*).) Any unexpectedly populated table ⇒ STOP and reclassify before dropping.
4. Dual-owner shape hazard (standing risk, not introduced by this plan): seven tables (integrations, agents, environments, targets, freeze_windows, releases, promotions) are created with different shapes by both the Platform baseline and the RO-Environment baseline under IF NOT EXISTS — whichever host migrates first on a fresh DB wins. The DROP plan deliberately keeps all of them; unifying ownership is a separate follow-up (recommended: RO-Environment becomes the single owner and the Platform baseline’s copies are retired from the next pre-release re-baseline, with the checksum-recovery procedure in mind).
5. AMBIGUOUS-KEEP tables need a product decision, not a DROP: the dormant PolicyGate (policy_profiles, policy_simulations, feed_freshness), EvidenceThread (5 tables), and IntegrationHub (integrations writer path) stores are complete, tested code that no host wires. Either wire them or delete the store code first; only then can their tables be reclassified.

7. Approval checklist (all boxes required before the migration file is created)

• [ ] Operator go-ahead recorded (who/when) in the owning sprint’s Decisions & Risks.
• [ ] PostgreSQL snapshot taken and verified (pg-snapshot-pre-platform-web-…tar.gz).
• [ ] §6.3 row-count preflight executed; output attached to the sprint evidence.
• [ ] Migration file created exactly as §5 (release category, 100_ prefix), reviewed.
• [ ] Post-run verification: platform-web, release-orchestrator, and concelier healthy; DTC-7 sync loop still writing watermarks (release.topology_sync_watermarks); /api/v2/topology/* serving.