Database Schema (PostgreSQL)

As-built note (2026-07-04, SPRINT_20260703_002 / DTC-7) — this document is the design-time spec; the deployed reality differs. Verified against src/:

Live truth (do not remove):

• release_orchestrator.* — the Release Orchestrator app schema (18 tables), owned and auto-migrated by src/ReleaseOrchestrator/__Libraries/StellaOps.ReleaseOrchestrator.Persistence.
• release.environments / release.targets / release.agents / release.regions (+ integrations, freeze_windows, infrastructure_bindings, topology_point_status, pending_deletions, promotions, releases, freeze_window_exemptions) — the authoritative environment/target/agent world, owned and auto-migrated by src/ReleaseOrchestrator/__Libraries/StellaOps.ReleaseOrchestrator.Environment (served by /api/v1/environments, /api/v1/environments/{env}/targets, /api/v1/agents; platform-web hosts the same library in-process).
• release.topology_*_inventory + release.topology_sync_watermarks — the topology read-model projections behind /api/v2/topology/*. Live as of DTC-7: the Platform topology inventory sync (TopologyInventorySynchronizer in src/Platform/StellaOps.Platform.WebService, startup + 5-minute interval) projects the authoritative rows above into these tables (they were created in Pack 22 but had no writer/reader until DTC-7 — the split-brain that left topology/header/targets/agents surfaces empty on real installs). ReleaseOrchestrator remains the schema and migration owner; forward migration 008_topology_inventory.sql converges the projection tables on fresh and existing databases before Platform reads them.
• release.control_bundle_*, release.trust_*, release.federated_telemetry_bundles — actively served by platform-web (release-control bundles, trust signing, federation).

Dead parallel model (slated for removal): the remainder of the release.* schema created by src/Platform/__Libraries/StellaOps.Platform.Database/Migrations/Release/001_v1_platform_database_release_baseline.sql (71 CREATE TABLE release.* statements total, inherited from the pre-1.0 mig061 pack — duplicate releases/promotions/deployments/workflow/gate models) has no reader or writer in src/ outside the live groups listed above. Removal precondition: (1) a fresh reader/writer audit of each table name across src/ at removal time, (2) a PostgreSQL snapshot backup (forward-only migrations, ADR-004), (3) a dedicated forward-only DROP migration in Platform.Database — do not edit the applied baseline (checksum-locked).

This document specifies the complete PostgreSQL schema for the Release Orchestrator.

Schema Organization

Release Orchestrator-owned migrations reside in the release_orchestrator schema:

CREATE SCHEMA IF NOT EXISTS release_orchestrator;
SET search_path TO release_orchestrator, public;

Core Tables

Tenant and Authority Extensions

-- Extended: Add release-related permissions
ALTER TABLE permissions ADD COLUMN IF NOT EXISTS
  resource_type VARCHAR(50) CHECK (resource_type IN (
    'environment', 'release', 'promotion', 'target', 'workflow', 'plugin'
  ));

Integration Hub

CREATE TABLE integration_types (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                VARCHAR(100) NOT NULL UNIQUE,
    category            VARCHAR(50) NOT NULL CHECK (category IN (
                          'scm', 'ci', 'registry', 'vault', 'target', 'router'
                        )),
    plugin_id           UUID REFERENCES plugins(id),
    config_schema       JSONB NOT NULL,
    secrets_schema      JSONB NOT NULL,
    is_builtin          BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE integrations (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    integration_type_id UUID NOT NULL REFERENCES integration_types(id),
    name                VARCHAR(255) NOT NULL,
    config              JSONB NOT NULL,
    credential_ref      VARCHAR(500),              -- Vault path or encrypted ref
    status              VARCHAR(50) NOT NULL DEFAULT 'unknown' CHECK (status IN (
                          'healthy', 'degraded', 'unhealthy', 'unknown'
                        )),
    last_health_check   TIMESTAMPTZ,
    last_health_message TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by          UUID REFERENCES users(id),
    UNIQUE (tenant_id, name)
);

CREATE INDEX idx_integrations_tenant ON integrations(tenant_id);
CREATE INDEX idx_integrations_type ON integrations(integration_type_id);

CREATE TABLE connection_profiles (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    user_id             UUID NOT NULL REFERENCES users(id),
    integration_type_id UUID NOT NULL REFERENCES integration_types(id),
    name                VARCHAR(255) NOT NULL,
    config_defaults     JSONB NOT NULL,
    is_default          BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, user_id, integration_type_id, name)
);

Environment & Inventory

CREATE TABLE environments (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name                VARCHAR(100) NOT NULL,
    display_name        VARCHAR(255) NOT NULL,
    order_index         INTEGER NOT NULL,
    config              JSONB NOT NULL DEFAULT '{}',
    freeze_windows      JSONB NOT NULL DEFAULT '[]',
    required_approvals  INTEGER NOT NULL DEFAULT 0,
    require_sod         BOOLEAN NOT NULL DEFAULT FALSE,
    auto_promote_from   UUID REFERENCES environments(id),
    promotion_policy    VARCHAR(255),
    deployment_timeout  INTEGER NOT NULL DEFAULT 600,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, name)
);

CREATE INDEX idx_environments_tenant ON environments(tenant_id);
CREATE INDEX idx_environments_order ON environments(tenant_id, order_index);

CREATE TABLE target_groups (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    environment_id      UUID NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    labels              JSONB NOT NULL DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, environment_id, name)
);

CREATE TABLE targets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    environment_id      UUID NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
    target_group_id     UUID REFERENCES target_groups(id),
    name                VARCHAR(255) NOT NULL,
    target_type         VARCHAR(100) NOT NULL,
    connection          JSONB NOT NULL,
    capabilities        JSONB NOT NULL DEFAULT '[]',
    labels              JSONB NOT NULL DEFAULT '{}',
    deployment_directory VARCHAR(500),
    health_status       VARCHAR(50) NOT NULL DEFAULT 'unknown' CHECK (health_status IN (
                          'healthy', 'degraded', 'unhealthy', 'unknown'
                        )),
    last_health_check   TIMESTAMPTZ,
    current_digest      VARCHAR(100),
    agent_id            UUID REFERENCES agents(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, environment_id, name)
);

CREATE INDEX idx_targets_tenant_env ON targets(tenant_id, environment_id);
CREATE INDEX idx_targets_type ON targets(target_type);
CREATE INDEX idx_targets_labels ON targets USING GIN (labels);

CREATE TABLE agents (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    version             VARCHAR(50) NOT NULL,
    capabilities        JSONB NOT NULL DEFAULT '[]',
    labels              JSONB NOT NULL DEFAULT '{}',
    status              VARCHAR(50) NOT NULL DEFAULT 'offline' CHECK (status IN (
                          'online', 'offline', 'degraded'
                        )),
    last_heartbeat      TIMESTAMPTZ,
    resource_usage      JSONB,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, name)
);

CREATE INDEX idx_agents_tenant ON agents(tenant_id);
CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_agents_capabilities ON agents USING GIN (capabilities);

Release Management

CREATE TABLE components (
    tenant_id                       TEXT        NOT NULL,
    component_id                    TEXT        NOT NULL,
    name                            TEXT        NOT NULL,
    display_name                    TEXT        NOT NULL,
    image_repository                TEXT        NOT NULL,
    registry_integration_id          TEXT        NOT NULL,
    versioning_strategy             TEXT        NOT NULL,
    status                          TEXT        NOT NULL,
    default_channel                 TEXT        NOT NULL,
    default_version_label           TEXT        NULL,
    created_at                      TIMESTAMPTZ NOT NULL,
    updated_at                      TIMESTAMPTZ NOT NULL,
    component_json                  JSONB       NOT NULL,

    CONSTRAINT pk_ro_components PRIMARY KEY (tenant_id, component_id),
    CONSTRAINT ck_ro_components_status CHECK (status IN ('active', 'retired')),
    CONSTRAINT ck_ro_components_versioning_strategy CHECK (versioning_strategy IN ('semver', 'branch-tag', 'label'))
);

CREATE UNIQUE INDEX ux_ro_components_tenant_name
    ON components (tenant_id, lower(name));

CREATE INDEX ix_ro_components_registry
    ON components (tenant_id, registry_integration_id, image_repository);

CREATE INDEX ix_ro_components_status
    ON components (tenant_id, status, updated_at DESC);

CREATE TABLE component_versions (
    tenant_id                       TEXT        NOT NULL,
    version_id                      TEXT        NOT NULL,
    component_id                    TEXT        NOT NULL,
    tag                             TEXT        NOT NULL,
    digest                          TEXT        NOT NULL,
    version_label                   TEXT        NOT NULL,
    channel                         TEXT        NOT NULL,
    source_commit_sha               TEXT        NULL,
    source_repo_url                 TEXT        NULL,
    source_branch                   TEXT        NULL,
    scm_manually_set                BOOLEAN     NOT NULL DEFAULT FALSE,
    discovered_at                   TIMESTAMPTZ NOT NULL,
    built_at                        TIMESTAMPTZ NULL,
    version_json                    JSONB       NOT NULL,

    CONSTRAINT pk_ro_component_versions PRIMARY KEY (tenant_id, version_id),
    CONSTRAINT fk_ro_component_versions_component FOREIGN KEY (tenant_id, component_id)
        REFERENCES components (tenant_id, component_id)
        ON DELETE CASCADE,
    CONSTRAINT ck_ro_component_versions_digest CHECK (digest LIKE 'sha256:%')
);

CREATE UNIQUE INDEX ux_ro_component_versions_tag_digest
    ON component_versions (tenant_id, component_id, tag, digest);

CREATE INDEX ix_ro_component_versions_component_time
    ON component_versions (tenant_id, component_id, discovered_at DESC, version_id ASC);

CREATE INDEX ix_ro_component_versions_component_channel
    ON component_versions (tenant_id, component_id, channel, discovered_at DESC);

CREATE INDEX ix_ro_component_versions_label
    ON component_versions (tenant_id, component_id, version_label, channel);

CREATE INDEX ix_ro_component_versions_digest
    ON component_versions (tenant_id, digest);

CREATE INDEX ix_ro_component_versions_scm_complete
    ON component_versions (tenant_id, source_repo_url, source_commit_sha)
    WHERE source_repo_url IS NOT NULL AND source_commit_sha IS NOT NULL;

CREATE TABLE releases (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    display_name        VARCHAR(255) NOT NULL,
    components          JSONB NOT NULL,            -- [{componentId, componentVersionId, digest, versionLabel, tag, role}]
    source_ref          JSONB,                     -- {scmIntegrationId, commitSha, ciIntegrationId, buildId}
    status              VARCHAR(50) NOT NULL DEFAULT 'draft' CHECK (status IN (
                          'draft', 'ready', 'promoting', 'deployed', 'deprecated', 'archived'
                        )),
    metadata            JSONB NOT NULL DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by          UUID REFERENCES users(id),
    UNIQUE (tenant_id, name)
);

CREATE INDEX idx_releases_tenant ON releases(tenant_id);
CREATE INDEX idx_releases_status ON releases(status);
CREATE INDEX idx_releases_created ON releases(created_at DESC);

CREATE TABLE release_environment_state (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    environment_id      UUID NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
    release_id          UUID NOT NULL REFERENCES releases(id),
    status              VARCHAR(50) NOT NULL CHECK (status IN (
                          'deployed', 'deploying', 'failed', 'rolling_back', 'rolled_back'
                        )),
    deployed_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deployed_by         UUID REFERENCES users(id),
    promotion_id        UUID,                      -- will reference promotions
    evidence_ref        VARCHAR(255),
    UNIQUE (tenant_id, environment_id)
);

CREATE INDEX idx_release_env_state_env ON release_environment_state(environment_id);
CREATE INDEX idx_release_env_state_release ON release_environment_state(release_id);

Workflow Engine

CREATE TABLE workflow_templates (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID REFERENCES tenants(id) ON DELETE CASCADE, -- NULL for builtin
    name                VARCHAR(255) NOT NULL,
    display_name        VARCHAR(255) NOT NULL,
    description         TEXT,
    version             INTEGER NOT NULL DEFAULT 1,
    nodes               JSONB NOT NULL,
    edges               JSONB NOT NULL,
    inputs              JSONB NOT NULL DEFAULT '[]',
    outputs             JSONB NOT NULL DEFAULT '[]',
    is_builtin          BOOLEAN NOT NULL DEFAULT FALSE,
    tags                JSONB NOT NULL DEFAULT '[]',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by          UUID REFERENCES users(id),
    UNIQUE (tenant_id, name, version)
);

CREATE INDEX idx_workflow_templates_tenant ON workflow_templates(tenant_id);
CREATE INDEX idx_workflow_templates_builtin ON workflow_templates(is_builtin);

CREATE TABLE workflow_runs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    template_id         UUID NOT NULL REFERENCES workflow_templates(id),
    template_version    INTEGER NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'created' CHECK (status IN (
                          'created', 'running', 'paused', 'succeeded', 'failed', 'cancelled'
                        )),
    context             JSONB NOT NULL,            -- inputs, variables, release info
    outputs             JSONB,
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    error_message       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    triggered_by        UUID REFERENCES users(id)
);

CREATE INDEX idx_workflow_runs_tenant ON workflow_runs(tenant_id);
CREATE INDEX idx_workflow_runs_status ON workflow_runs(status);
CREATE INDEX idx_workflow_runs_template ON workflow_runs(template_id);

CREATE TABLE step_runs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_run_id     UUID NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
    node_id             VARCHAR(100) NOT NULL,
    step_type           VARCHAR(100) NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN (
                          'pending', 'running', 'succeeded', 'failed', 'skipped', 'retrying', 'cancelled'
                        )),
    inputs              JSONB NOT NULL,
    config              JSONB NOT NULL,
    outputs             JSONB,
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    attempt_number      INTEGER NOT NULL DEFAULT 1,
    error_message       TEXT,
    error_type          VARCHAR(100),
    logs                TEXT,
    artifacts           JSONB NOT NULL DEFAULT '[]',
    t_hlc               BIGINT,                    -- Hybrid Logical Clock for ordering (optional)
    ts_wall             TIMESTAMPTZ,               -- Wall-clock timestamp for debugging (optional)
    UNIQUE (workflow_run_id, node_id, attempt_number)
);

CREATE INDEX idx_step_runs_workflow ON step_runs(workflow_run_id);
CREATE INDEX idx_step_runs_status ON step_runs(status);

Promotion & Approval

CREATE TABLE promotions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    release_id          UUID NOT NULL REFERENCES releases(id),
    source_environment_id UUID REFERENCES environments(id),
    target_environment_id UUID NOT NULL REFERENCES environments(id),
    status              VARCHAR(50) NOT NULL DEFAULT 'pending_approval' CHECK (status IN (
                          'pending_approval', 'pending_gate', 'approved', 'rejected',
                          'deploying', 'deployed', 'failed', 'cancelled', 'rolled_back'
                        )),
    decision_record     JSONB,
    workflow_run_id     UUID REFERENCES workflow_runs(id),
    requested_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    requested_by        UUID NOT NULL REFERENCES users(id),
    request_reason      TEXT,
    decided_at          TIMESTAMPTZ,
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    evidence_packet_id  UUID,
    t_hlc               BIGINT,                    -- Hybrid Logical Clock for ordering (optional)
    ts_wall             TIMESTAMPTZ                -- Wall-clock timestamp for debugging (optional)
);

CREATE INDEX idx_promotions_tenant ON promotions(tenant_id);
CREATE INDEX idx_promotions_release ON promotions(release_id);
CREATE INDEX idx_promotions_status ON promotions(status);
CREATE INDEX idx_promotions_target_env ON promotions(target_environment_id);

-- Add FK to release_environment_state
ALTER TABLE release_environment_state
  ADD CONSTRAINT fk_release_env_state_promotion
  FOREIGN KEY (promotion_id) REFERENCES promotions(id);

CREATE TABLE approvals (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    promotion_id        UUID NOT NULL REFERENCES promotions(id) ON DELETE CASCADE,
    approver_id         UUID NOT NULL REFERENCES users(id),
    action              VARCHAR(50) NOT NULL CHECK (action IN ('approved', 'rejected')),
    comment             TEXT,
    approved_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    approver_role       VARCHAR(255),
    approver_groups     JSONB NOT NULL DEFAULT '[]'
);

CREATE INDEX idx_approvals_promotion ON approvals(promotion_id);
CREATE INDEX idx_approvals_approver ON approvals(approver_id);

CREATE TABLE approval_policies (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    environment_id      UUID NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
    required_count      INTEGER NOT NULL DEFAULT 1,
    required_roles      JSONB NOT NULL DEFAULT '[]',
    required_groups     JSONB NOT NULL DEFAULT '[]',
    require_sod         BOOLEAN NOT NULL DEFAULT FALSE,
    allow_self_approval BOOLEAN NOT NULL DEFAULT FALSE,
    expiration_minutes  INTEGER NOT NULL DEFAULT 1440,
    UNIQUE (tenant_id, environment_id)
);

Deployment

CREATE TABLE deployment_jobs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    promotion_id        UUID NOT NULL REFERENCES promotions(id),
    release_id          UUID NOT NULL REFERENCES releases(id),
    environment_id      UUID NOT NULL REFERENCES environments(id),
    status              VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN (
                          'pending', 'running', 'succeeded', 'failed', 'cancelled', 'rolling_back', 'rolled_back'
                        )),
    strategy            VARCHAR(50) NOT NULL DEFAULT 'all-at-once',
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    artifacts           JSONB NOT NULL DEFAULT '[]',
    rollback_of         UUID REFERENCES deployment_jobs(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    t_hlc               BIGINT,                    -- Hybrid Logical Clock for ordering (optional)
    ts_wall             TIMESTAMPTZ                -- Wall-clock timestamp for debugging (optional)
);

CREATE INDEX idx_deployment_jobs_promotion ON deployment_jobs(promotion_id);
CREATE INDEX idx_deployment_jobs_status ON deployment_jobs(status);

CREATE TABLE deployment_tasks (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id              UUID NOT NULL REFERENCES deployment_jobs(id) ON DELETE CASCADE,
    target_id           UUID NOT NULL REFERENCES targets(id),
    digest              VARCHAR(100) NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN (
                          'pending', 'running', 'succeeded', 'failed', 'cancelled', 'skipped'
                        )),
    agent_id            UUID REFERENCES agents(id),
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    exit_code           INTEGER,
    logs                TEXT,
    previous_digest     VARCHAR(100),
    sticker_written     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_deployment_tasks_job ON deployment_tasks(job_id);
CREATE INDEX idx_deployment_tasks_target ON deployment_tasks(target_id);
CREATE INDEX idx_deployment_tasks_status ON deployment_tasks(status);

CREATE TABLE generated_artifacts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    deployment_job_id   UUID REFERENCES deployment_jobs(id) ON DELETE CASCADE,
    artifact_type       VARCHAR(50) NOT NULL CHECK (artifact_type IN (
                          'compose_lock', 'script', 'sticker', 'evidence', 'config'
                        )),
    name                VARCHAR(255) NOT NULL,
    content_hash        VARCHAR(100) NOT NULL,
    content             BYTEA,                     -- for small artifacts
    storage_ref         VARCHAR(500),              -- for large artifacts (S3, etc.)
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_generated_artifacts_job ON generated_artifacts(deployment_job_id);

Progressive Delivery

CREATE TABLE ab_releases (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    environment_id      UUID NOT NULL REFERENCES environments(id),
    name                VARCHAR(255) NOT NULL,
    variations          JSONB NOT NULL,            -- [{name, releaseId, targetGroupId, trafficPercentage}]
    active_variation    VARCHAR(50) NOT NULL DEFAULT 'A',
    traffic_split       JSONB NOT NULL,
    rollout_strategy    JSONB NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'created' CHECK (status IN (
                          'created', 'deploying', 'running', 'promoting', 'completed', 'rolled_back'
                        )),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at        TIMESTAMPTZ,
    created_by          UUID REFERENCES users(id)
);

CREATE INDEX idx_ab_releases_tenant_env ON ab_releases(tenant_id, environment_id);
CREATE INDEX idx_ab_releases_status ON ab_releases(status);

CREATE TABLE canary_stages (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ab_release_id       UUID NOT NULL REFERENCES ab_releases(id) ON DELETE CASCADE,
    stage_number        INTEGER NOT NULL,
    traffic_percentage  INTEGER NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN (
                          'pending', 'running', 'succeeded', 'failed', 'skipped'
                        )),
    health_threshold    DECIMAL(5,2),
    duration_seconds    INTEGER,
    require_approval    BOOLEAN NOT NULL DEFAULT FALSE,
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    health_result       JSONB,
    UNIQUE (ab_release_id, stage_number)
);

Release Evidence

CREATE TABLE evidence_packets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    promotion_id        UUID NOT NULL REFERENCES promotions(id),
    packet_type         VARCHAR(50) NOT NULL CHECK (packet_type IN (
                          'release_decision', 'deployment', 'rollback', 'ab_promotion'
                        )),
    content             JSONB NOT NULL,
    content_hash        VARCHAR(100) NOT NULL,
    signature           TEXT,
    signer_key_ref      VARCHAR(255),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
    -- Note: No UPDATE or DELETE allowed (append-only)
);

CREATE INDEX idx_evidence_packets_promotion ON evidence_packets(promotion_id);
CREATE INDEX idx_evidence_packets_created ON evidence_packets(created_at DESC);

-- Append-only enforcement via trigger
CREATE OR REPLACE FUNCTION prevent_evidence_modification()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Evidence packets are immutable and cannot be modified or deleted';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER evidence_packets_immutable
BEFORE UPDATE OR DELETE ON evidence_packets
FOR EACH ROW EXECUTE FUNCTION prevent_evidence_modification();

CREATE TABLE version_stickers (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    target_id           UUID NOT NULL REFERENCES targets(id),
    deployment_job_id   UUID REFERENCES deployment_jobs(id),
    release_id          UUID NOT NULL REFERENCES releases(id),
    digest              VARCHAR(100) NOT NULL,
    sticker_content     JSONB NOT NULL,
    written_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    verified_at         TIMESTAMPTZ,
    verification_status VARCHAR(50) CHECK (verification_status IN ('valid', 'mismatch', 'missing'))
);

CREATE INDEX idx_version_stickers_target ON version_stickers(target_id);
CREATE INDEX idx_version_stickers_release ON version_stickers(release_id);

Plugin Infrastructure

CREATE TABLE plugins (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                VARCHAR(255) NOT NULL UNIQUE,
    display_name        VARCHAR(255) NOT NULL,
    version             VARCHAR(50) NOT NULL,
    description         TEXT,
    manifest            JSONB NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'inactive' CHECK (status IN (
                          'active', 'inactive', 'error'
                        )),
    error_message       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE plugin_instances (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    plugin_id           UUID NOT NULL REFERENCES plugins(id),
    config              JSONB NOT NULL DEFAULT '{}',
    enabled             BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, plugin_id)
);

CREATE INDEX idx_plugin_instances_tenant ON plugin_instances(tenant_id);

Hybrid Logical Clock (HLC) for Distributed Ordering

Optional Enhancement: For strict distributed ordering and multi-region support, the following tables include optional t_hlc (Hybrid Logical Clock timestamp) and ts_wall (wall-clock timestamp) columns:

• promotions — Promotion state transitions
• deployment_jobs — Deployment task ordering
• step_runs — Workflow step execution ordering

When to use HLC:

• Multi-region deployments requiring strict causal ordering
• Deterministic replay across distributed systems
• Timeline event ordering in audit logs

HLC Schema:

t_hlc    BIGINT       -- HLC timestamp (monotonic, skew-tolerant)
ts_wall  TIMESTAMPTZ  -- Wall-clock timestamp (informational)

Usage:

• t_hlc is generated by IHybridLogicalClock.Tick() on state transitions
• ts_wall is populated by TimeProvider.GetUtcNow() for debugging
• Index on t_hlc for ordering queries: CREATE INDEX idx_promotions_hlc ON promotions(t_hlc);

Reference: See Implementation Guide for HLC usage patterns.

Row-Level Security (Multi-Tenancy)

All tables with tenant_id should have RLS enabled:

-- Enable RLS on all release tables
ALTER TABLE integrations ENABLE ROW LEVEL SECURITY;
ALTER TABLE environments ENABLE ROW LEVEL SECURITY;
ALTER TABLE targets ENABLE ROW LEVEL SECURITY;
ALTER TABLE releases ENABLE ROW LEVEL SECURITY;
ALTER TABLE promotions ENABLE ROW LEVEL SECURITY;
-- ... etc.

-- Example policy
CREATE POLICY tenant_isolation ON integrations
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::UUID);