Dead release.* Schema — DROP Audit & Plan

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.Database/Migrations/Release/001_v1_platform_database_release_baseline.sql (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 release.*. Platform and Concelier both stopped calling AddReleaseOrchestratorEnvironmentPostgresData and stopped applying the ReleaseOrchestrator.Environment startup migrations on 2026-07-11. Platform’s compatibility endpoints proxy the owning WebApi; the gateway’s topology-setup aliases route directly to it. In addition, DTC-7 (SPRINT_20260703_002) actively projects release.environments/targets/agents/regions into the release.topology_*_inventory read-model tables and mirrors regions/ environments into platform.context_regions / platform.context_environments (src/Platform/StellaOps.Platform.WebService/Program.cs:471-481, Services/TopologyInventorySynchronizer.cs:16-17, Services/PostgresTopologySourceReader.cs:29-53, Services/PostgresTopologyInventoryStore.cs, Services/PlatformContextService.cs).

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.Database/Migrations/Release/001_v1_platform_database_release_baseline.sql (P: prefix) 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

3.11 Topology inventory read-model (DTC-7)

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

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

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.

-- PROPOSED: 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/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/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/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.Database.csproj); the moment this file lands in Migrations/Release/, 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.
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.