# Database Schema (PostgreSQL)

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

## Schema Organization

All release orchestration tables reside in the `release` schema:

```sql
CREATE SCHEMA IF NOT EXISTS release;
SET search_path TO release, public;
```

## Core Tables

### Tenant and Authority Extensions

```sql
-- 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

```sql
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

```sql
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

```sql
CREATE TABLE components (
    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,
    image_repository    VARCHAR(500) NOT NULL,
    registry_integration_id UUID REFERENCES integrations(id),
    versioning_strategy JSONB NOT NULL DEFAULT '{"type": "semver"}',
    deployment_template VARCHAR(255),
    default_channel     VARCHAR(50) NOT NULL DEFAULT 'stable',
    metadata            JSONB NOT NULL DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, name)
);

CREATE INDEX idx_components_tenant ON components(tenant_id);

CREATE TABLE version_maps (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    component_id        UUID NOT NULL REFERENCES components(id) ON DELETE CASCADE,
    tag                 VARCHAR(255) NOT NULL,
    digest              VARCHAR(100) NOT NULL,
    semver              VARCHAR(50),
    channel             VARCHAR(50) NOT NULL DEFAULT 'stable',
    prerelease          BOOLEAN NOT NULL DEFAULT FALSE,
    build_metadata      VARCHAR(255),
    resolved_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    source              VARCHAR(50) NOT NULL DEFAULT 'auto' CHECK (source IN ('auto', 'manual')),
    UNIQUE (tenant_id, component_id, digest)
);

CREATE INDEX idx_version_maps_component ON version_maps(component_id);
CREATE INDEX idx_version_maps_digest ON version_maps(digest);
CREATE INDEX idx_version_maps_semver ON version_maps(semver);

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, digest, semver, 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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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**:
```sql
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](../implementation-guide.md#hybrid-logical-clock-hlc-for-distributed-ordering) for HLC usage patterns.

---

## Row-Level Security (Multi-Tenancy)

All tables with `tenant_id` should have RLS enabled:

```sql
-- 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);
```