Database Schema

This section defines the complete PostgreSQL schema for Courier. All tables use snake_case naming. Migrations are managed via DbUp with raw SQL scripts, not EF Core migrations. EF Core is used only as an ORM for querying and change tracking, with entity mappings configured to match this schema.

13.1 Migration Strategy

DbUp runs numbered SQL scripts on API host startup only (the Worker validates schema version but does not execute migrations — see 13.1.1). Scripts are embedded resources in a dedicated Courier.Migrations project and executed in order.

Naming convention: XXXX_description.sql (e.g., 0001_initial_schema.sql, 0002_add_tags.sql)

DbUp configuration:

var upgrader = DeployChanges.To
    .PostgresqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(typeof(MigrationMarker).Assembly)
    .WithTransactionPerScript()
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

DbUp tracks executed scripts in a schema_versions table. Scripts are idempotent where possible. Destructive changes (column removal, table drops) are never included in the same release as the code that stops using them — they follow a two-release deprecation cycle.

13.1.1 Migration Safety in Multi-Replica Deployments

When running multiple API or Worker instances (even temporarily during a rolling deployment), database migrations must not run concurrently and must not leave the schema in an inconsistent state.

Which host runs migrations: Only the API host runs migrations. The Worker host does not include the MigrationRunner hosted service in its DI registration. This is a deliberate design decision: the Worker should be able to start (or restart) without blocking on schema changes, and migrations should be coupled to the deployment of the API which owns the schema contract.

// Courier.Api/Program.cs — API only
builder.Services.AddHostedService<MigrationRunner>();

// Courier.Worker/Program.cs — Worker does NOT register MigrationRunner
// Worker validates schema version on startup instead:
builder.Services.AddHostedService<SchemaVersionValidator>();

SchemaVersionValidator (Worker only): On startup, the Worker reads the schema_versions table and compares the highest applied migration against its expected minimum version (compiled into the Worker binary). If the database schema is behind, the Worker logs a fatal error and refuses to start, with a message directing the operator to deploy the API first. This prevents the Worker from operating against an incompatible schema.

Concurrent migration prevention: The MigrationRunner acquires a PostgreSQL advisory lock (pg_advisory_lock(12345)) before executing any scripts. This is a session-level lock — only one connection can hold it at a time. If a second API instance starts while the first is still migrating, the second blocks on the advisory lock until the first completes, then discovers all scripts are already applied and proceeds to start normally. See Section 14.6 for the full implementation.

What happens if a migration fails mid-deploy:

1. DbUp uses WithTransactionPerScript(), so each individual script runs in its own transaction. A failed script rolls back its own changes — the database is left in the state after the last successful script.
2. The MigrationRunner throws an exception, which prevents the API host from starting. The container enters a crash loop (or health check failure), which is the correct behavior — a partially-migrated API should not serve traffic.
3. The schema_versions table records which scripts have been applied. On the next deployment attempt (with the failing script fixed), DbUp skips already-applied scripts and retries from the failed one.
4. The advisory lock is released in a finally block, so even a crash releases it (PostgreSQL also auto-releases advisory locks when the session disconnects).

Failure recovery procedure: If a migration fails, the operator should: (1) check the API container logs for the specific script and SQL error, (2) fix the migration script, (3) redeploy. There is no manual rollback mechanism — DbUp is forward-only. If a rollback is needed, it must be a new forward migration that reverses the changes.

Deployment ordering: In a rolling deployment, the correct order is: (1) deploy new API hosts (which run migrations), (2) deploy new Worker hosts (which validate schema version). If the Worker is deployed first, it will refuse to start if it expects a newer schema than what's currently applied. This is safe — it just means the Worker restarts once the API has migrated the schema.

13.2 Conventions

• Primary keys: id UUID DEFAULT gen_random_uuid() on all tables
• Timestamps: TIMESTAMPTZ for all date/time columns, stored as UTC
• Soft delete: is_deleted BOOLEAN DEFAULT FALSE and deleted_at TIMESTAMPTZ on all major entities. A partial index WHERE NOT is_deleted is applied to commonly queried tables
• JSONB: Used for flexible/nested data (step configuration, audit details, directory listings)
• Foreign keys: Named fk_\{table\}_\{referenced_table\} with explicit ON DELETE behavior
• Indexes: Named ix_\{table\}_\{columns\}
• Constraints: Named ck_\{table\}_\{description\}
• Partitioning: Range partitioning by month on high-volume tables (audit_log_entries, domain_events, job_executions, step_executions, monitor_file_log)

13.3 Schema DDL

13.3.1 Job System Tables

-- ============================================================
-- JOBS
-- ============================================================
CREATE TABLE jobs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL,
    description         TEXT,
    current_version     INT NOT NULL DEFAULT 1,
    failure_policy      JSONB NOT NULL DEFAULT '{"type":"stop","max_retries":3,"backoff_base_seconds":1,"backoff_max_seconds":60}',
    is_enabled          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted          BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at          TIMESTAMPTZ
);

CREATE INDEX ix_jobs_name ON jobs (name) WHERE NOT is_deleted;
CREATE INDEX ix_jobs_is_enabled ON jobs (is_enabled) WHERE NOT is_deleted;

-- ============================================================
-- JOB STEPS
-- ============================================================
CREATE TABLE job_steps (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id              UUID NOT NULL,
    step_order          INT NOT NULL,
    name                TEXT NOT NULL,
    type_key            TEXT NOT NULL,
    configuration       JSONB NOT NULL DEFAULT '{}',
    timeout_seconds     INT NOT NULL DEFAULT 300,

    CONSTRAINT fk_job_steps_jobs FOREIGN KEY (job_id)
        REFERENCES jobs (id) ON DELETE CASCADE,
    CONSTRAINT ck_job_steps_order_positive CHECK (step_order >= 0),
    CONSTRAINT ck_job_steps_timeout_positive CHECK (timeout_seconds > 0)
);

CREATE UNIQUE INDEX ix_job_steps_job_order ON job_steps (job_id, step_order);
CREATE INDEX ix_job_steps_type_key ON job_steps (type_key);

-- ============================================================
-- JOB VERSIONS
-- ============================================================
CREATE TABLE job_versions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id              UUID NOT NULL,
    version_number      INT NOT NULL,
    config_snapshot     JSONB NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by          TEXT NOT NULL,

    CONSTRAINT fk_job_versions_jobs FOREIGN KEY (job_id)
        REFERENCES jobs (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ix_job_versions_job_version ON job_versions (job_id, version_number);

-- ============================================================
-- JOB EXECUTIONS (PARTITIONED)
-- ============================================================
CREATE TABLE job_executions (
    id                  UUID NOT NULL DEFAULT gen_random_uuid(),
    job_id              UUID NOT NULL,
    job_version_number  INT NOT NULL,
    chain_execution_id  UUID,
    triggered_by        TEXT NOT NULL,
    state               TEXT NOT NULL DEFAULT 'created',
    queued_at           TIMESTAMPTZ,
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    context_snapshot    JSONB DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT pk_job_executions PRIMARY KEY (id, created_at),
    CONSTRAINT ck_job_executions_state CHECK (
        state IN ('created', 'queued', 'running', 'paused', 'completed', 'failed', 'cancelled')
    )
) PARTITION BY RANGE (created_at);

-- Partition creation is handled by a scheduled maintenance script (see 13.5)
-- Example: CREATE TABLE job_executions_2026_02 PARTITION OF job_executions
--          FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE INDEX ix_job_executions_job_id ON job_executions (job_id, created_at DESC);
CREATE INDEX ix_job_executions_state ON job_executions (state, created_at DESC);
CREATE INDEX ix_job_executions_chain ON job_executions (chain_execution_id, created_at DESC)
    WHERE chain_execution_id IS NOT NULL;
CREATE INDEX ix_job_executions_queued ON job_executions (queued_at)
    WHERE state = 'queued';

-- ============================================================
-- STEP EXECUTIONS (PARTITIONED)
-- ============================================================
CREATE TABLE step_executions (
    id                  UUID NOT NULL DEFAULT gen_random_uuid(),
    job_execution_id    UUID NOT NULL,
    job_step_id         UUID NOT NULL,
    step_order          INT NOT NULL,
    state               TEXT NOT NULL DEFAULT 'pending',
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    duration_ms         BIGINT,
    bytes_processed     BIGINT,
    output_data         JSONB,
    error_message       TEXT,
    error_stack_trace   TEXT,
    retry_attempt       INT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT pk_step_executions PRIMARY KEY (id, created_at),
    CONSTRAINT ck_step_executions_state CHECK (
        state IN ('pending', 'running', 'completed', 'failed', 'skipped')
    )
) PARTITION BY RANGE (created_at);

CREATE INDEX ix_step_executions_job_execution ON step_executions (job_execution_id, step_order);
CREATE INDEX ix_step_executions_state ON step_executions (state, created_at DESC);

-- ============================================================
-- JOB CHAINS
-- ============================================================
CREATE TABLE job_chains (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL,
    description         TEXT,
    is_enabled          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted          BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at          TIMESTAMPTZ
);

CREATE INDEX ix_job_chains_name ON job_chains (name) WHERE NOT is_deleted;

-- ============================================================
-- JOB CHAIN MEMBERS
-- ============================================================
CREATE TABLE job_chain_members (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_id                UUID NOT NULL,
    job_id                  UUID NOT NULL,
    execution_order         INT NOT NULL,
    depends_on_member_id    UUID,
    run_on_upstream_failure BOOLEAN NOT NULL DEFAULT FALSE,

    CONSTRAINT fk_chain_members_chains FOREIGN KEY (chain_id)
        REFERENCES job_chains (id) ON DELETE CASCADE,
    CONSTRAINT fk_chain_members_jobs FOREIGN KEY (job_id)
        REFERENCES jobs (id) ON DELETE RESTRICT,
    CONSTRAINT fk_chain_members_depends FOREIGN KEY (depends_on_member_id)
        REFERENCES job_chain_members (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX ix_chain_members_chain_order ON job_chain_members (chain_id, execution_order);
CREATE INDEX ix_chain_members_job ON job_chain_members (job_id);

-- ============================================================
-- CHAIN EXECUTIONS
-- ============================================================
CREATE TABLE chain_executions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_id            UUID NOT NULL,
    triggered_by        TEXT NOT NULL,
    state               TEXT NOT NULL DEFAULT 'pending',
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT fk_chain_executions_chains FOREIGN KEY (chain_id)
        REFERENCES job_chains (id) ON DELETE CASCADE,
    CONSTRAINT ck_chain_executions_state CHECK (
        state IN ('pending', 'running', 'completed', 'failed', 'paused', 'cancelled')
    )
);

CREATE INDEX ix_chain_executions_chain ON chain_executions (chain_id, created_at DESC);
CREATE INDEX ix_chain_executions_state ON chain_executions (state);

-- ============================================================
-- JOB DEPENDENCIES
-- ============================================================
CREATE TABLE job_dependencies (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    upstream_job_id     UUID NOT NULL,
    downstream_job_id   UUID NOT NULL,
    run_on_failure      BOOLEAN NOT NULL DEFAULT FALSE,

    CONSTRAINT fk_job_deps_upstream FOREIGN KEY (upstream_job_id)
        REFERENCES jobs (id) ON DELETE CASCADE,
    CONSTRAINT fk_job_deps_downstream FOREIGN KEY (downstream_job_id)
        REFERENCES jobs (id) ON DELETE CASCADE,
    CONSTRAINT ck_job_deps_no_self_ref CHECK (upstream_job_id != downstream_job_id)
);

CREATE UNIQUE INDEX ix_job_deps_pair ON job_dependencies (upstream_job_id, downstream_job_id);
CREATE INDEX ix_job_deps_downstream ON job_dependencies (downstream_job_id);

-- ============================================================
-- JOB SCHEDULES
-- ============================================================
CREATE TABLE job_schedules (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id           UUID NOT NULL,
    schedule_type    TEXT NOT NULL,              -- 'cron' | 'one_shot'
    cron_expression  TEXT,
    run_at           TIMESTAMPTZ,
    is_enabled       BOOLEAN NOT NULL DEFAULT TRUE,
    last_fired_at    TIMESTAMPTZ,
    next_fire_at     TIMESTAMPTZ,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT ck_schedule_type CHECK (schedule_type IN ('cron', 'one_shot')),
    CONSTRAINT ck_schedule_cron CHECK (
        (schedule_type = 'cron' AND cron_expression IS NOT NULL) OR
        (schedule_type = 'one_shot' AND run_at IS NOT NULL)
    ),
    CONSTRAINT fk_schedules_jobs FOREIGN KEY (job_id)
        REFERENCES jobs (id) ON DELETE CASCADE
);

CREATE INDEX ix_job_schedules_job_id ON job_schedules (job_id);
CREATE INDEX ix_job_schedules_enabled ON job_schedules (is_enabled, schedule_type);

-- ============================================================
-- CHAIN SCHEDULES
-- ============================================================
CREATE TABLE chain_schedules (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_id         UUID NOT NULL,
    schedule_type    TEXT NOT NULL,              -- 'cron' | 'one_shot'
    cron_expression  TEXT,
    run_at           TIMESTAMPTZ,
    is_enabled       BOOLEAN NOT NULL DEFAULT TRUE,
    last_fired_at    TIMESTAMPTZ,
    next_fire_at     TIMESTAMPTZ,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT ck_chain_schedule_type CHECK (schedule_type IN ('cron', 'one_shot')),
    CONSTRAINT ck_chain_schedule_cron CHECK (
        (schedule_type = 'cron' AND cron_expression IS NOT NULL) OR
        (schedule_type = 'one_shot' AND run_at IS NOT NULL)
    ),
    CONSTRAINT fk_chain_schedules_chains FOREIGN KEY (chain_id)
        REFERENCES job_chains (id) ON DELETE CASCADE
);

CREATE INDEX ix_chain_schedules_chain_id ON chain_schedules (chain_id);
CREATE INDEX ix_chain_schedules_enabled ON chain_schedules (is_enabled, schedule_type);

13.3.2 Connection Tables

-- ============================================================
-- CONNECTIONS
-- ============================================================
CREATE TABLE connections (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                    TEXT NOT NULL,
    "group"                 TEXT,
    protocol                TEXT NOT NULL,
    host                    TEXT NOT NULL,
    port                    INT NOT NULL,
    auth_method             TEXT NOT NULL,
    username                TEXT NOT NULL,
    password_encrypted      BYTEA,
    ssh_key_id              UUID,
    host_key_policy         TEXT NOT NULL DEFAULT 'trust_on_first_use',
    stored_host_fingerprint TEXT,
    passive_mode            BOOLEAN NOT NULL DEFAULT TRUE,
    tls_version_floor       TEXT,
    tls_cert_policy         TEXT NOT NULL DEFAULT 'system_trust',
    tls_pinned_thumbprint   TEXT,
    ssh_algorithms          JSONB,
    connect_timeout_sec     INT NOT NULL DEFAULT 30,
    operation_timeout_sec   INT NOT NULL DEFAULT 300,
    keepalive_interval_sec  INT NOT NULL DEFAULT 60,
    transport_retries       INT NOT NULL DEFAULT 2,
    status                  TEXT NOT NULL DEFAULT 'active',
    fips_override           BOOLEAN NOT NULL DEFAULT FALSE,
    notes                   TEXT,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted              BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at              TIMESTAMPTZ,

    CONSTRAINT ck_connections_protocol CHECK (protocol IN ('sftp', 'ftp', 'ftps')),
    CONSTRAINT ck_connections_auth CHECK (auth_method IN ('password', 'ssh_key', 'password_and_ssh_key')),
    CONSTRAINT ck_connections_host_key CHECK (host_key_policy IN ('trust_on_first_use', 'always_trust', 'manual')),
    CONSTRAINT ck_connections_tls_cert CHECK (tls_cert_policy IN ('system_trust', 'pinned_thumbprint', 'insecure')),
    CONSTRAINT ck_connections_status CHECK (status IN ('active', 'disabled')),
    CONSTRAINT ck_connections_retries CHECK (transport_retries BETWEEN 0 AND 3),
    CONSTRAINT fk_connections_ssh_keys FOREIGN KEY (ssh_key_id)
        REFERENCES ssh_keys (id) ON DELETE SET NULL
);

CREATE INDEX ix_connections_name ON connections (name) WHERE NOT is_deleted;
CREATE INDEX ix_connections_group ON connections ("group") WHERE NOT is_deleted AND "group" IS NOT NULL;
CREATE INDEX ix_connections_protocol ON connections (protocol) WHERE NOT is_deleted;

-- ============================================================
-- KNOWN HOSTS
-- ============================================================
CREATE TABLE known_hosts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id       UUID NOT NULL,
    fingerprint         TEXT NOT NULL,
    key_type            TEXT NOT NULL,
    first_seen          TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen           TIMESTAMPTZ NOT NULL DEFAULT now(),
    approved_by         TEXT NOT NULL,

    CONSTRAINT fk_known_hosts_connections FOREIGN KEY (connection_id)
        REFERENCES connections (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ix_known_hosts_connection_fingerprint ON known_hosts (connection_id, fingerprint);

13.3.3 Key Store Tables

-- ============================================================
-- PGP KEYS
-- ============================================================
CREATE TABLE pgp_keys (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL,
    fingerprint         TEXT NOT NULL,
    short_key_id        TEXT NOT NULL,
    algorithm           TEXT NOT NULL,
    key_type            TEXT NOT NULL,
    purpose             TEXT,
    status              TEXT NOT NULL DEFAULT 'active',
    public_key_data     TEXT NOT NULL,
    private_key_data    BYTEA,
    passphrase_hash     TEXT,
    expires_at          TIMESTAMPTZ,
    successor_key_id    UUID,
    created_by          TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted          BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at          TIMESTAMPTZ,

    CONSTRAINT ck_pgp_keys_algorithm CHECK (
        algorithm IN ('rsa_2048', 'rsa_3072', 'rsa_4096', 'ecc_curve25519', 'ecc_p256', 'ecc_p384')
    ),
    CONSTRAINT ck_pgp_keys_type CHECK (key_type IN ('public_only', 'key_pair')),
    CONSTRAINT ck_pgp_keys_status CHECK (
        status IN ('active', 'expiring', 'retired', 'revoked', 'deleted')
    ),
    CONSTRAINT fk_pgp_keys_successor FOREIGN KEY (successor_key_id)
        REFERENCES pgp_keys (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX ix_pgp_keys_fingerprint ON pgp_keys (fingerprint) WHERE NOT is_deleted;
CREATE INDEX ix_pgp_keys_status ON pgp_keys (status) WHERE NOT is_deleted;
CREATE INDEX ix_pgp_keys_expires ON pgp_keys (expires_at)
    WHERE status IN ('active', 'expiring') AND expires_at IS NOT NULL;

-- ============================================================
-- SSH KEYS
-- ============================================================
CREATE TABLE ssh_keys (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL,
    key_type            TEXT NOT NULL,
    public_key_data     TEXT NOT NULL,
    private_key_data    BYTEA NOT NULL,
    passphrase_hash     TEXT,
    fingerprint         TEXT NOT NULL,
    status              TEXT NOT NULL DEFAULT 'active',
    notes               TEXT,
    created_by          TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted          BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at          TIMESTAMPTZ,

    CONSTRAINT ck_ssh_keys_type CHECK (
        key_type IN ('rsa_2048', 'rsa_4096', 'ed25519', 'ecdsa_256')
    ),
    CONSTRAINT ck_ssh_keys_status CHECK (status IN ('active', 'retired', 'deleted'))
);

CREATE UNIQUE INDEX ix_ssh_keys_fingerprint ON ssh_keys (fingerprint) WHERE NOT is_deleted;
CREATE INDEX ix_ssh_keys_status ON ssh_keys (status) WHERE NOT is_deleted;

-- ============================================================
-- PUBLIC KEY SHARE TOKENS
-- ============================================================
CREATE TABLE public_key_share_tokens (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token_hash          TEXT NOT NULL,           -- SHA-256 hash of the random token
    key_type            TEXT NOT NULL,            -- 'pgp' or 'ssh'
    key_id              UUID NOT NULL,
    expires_at          TIMESTAMPTZ NOT NULL,
    created_by          TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at          TIMESTAMPTZ,
    download_count      INT NOT NULL DEFAULT 0,

    CONSTRAINT ck_share_key_type CHECK (key_type IN ('pgp', 'ssh'))
);

CREATE UNIQUE INDEX ix_share_tokens_hash ON public_key_share_tokens (token_hash);
CREATE INDEX ix_share_tokens_key ON public_key_share_tokens (key_type, key_id);

13.3.4 File Monitor Tables

-- ============================================================
-- FILE MONITORS
-- ============================================================
CREATE TABLE file_monitors (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                        TEXT NOT NULL,
    description                 TEXT,
    watch_target                JSONB NOT NULL,
    trigger_events              INT NOT NULL,
    file_patterns               JSONB NOT NULL DEFAULT '[]',
    polling_interval_sec        INT NOT NULL DEFAULT 60,
    stability_window_sec        INT NOT NULL DEFAULT 5,
    batch_mode                  BOOLEAN NOT NULL DEFAULT TRUE,
    max_consecutive_failures    INT NOT NULL DEFAULT 5,
    consecutive_failure_count   INT NOT NULL DEFAULT 0,
    state                       TEXT NOT NULL DEFAULT 'active',
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted                  BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at                  TIMESTAMPTZ,

    CONSTRAINT ck_monitors_state CHECK (state IN ('active', 'degraded', 'paused', 'disabled', 'error')),
    CONSTRAINT ck_monitors_polling CHECK (polling_interval_sec >= 30),
    CONSTRAINT ck_monitors_stability CHECK (stability_window_sec >= 1)
);

CREATE INDEX ix_monitors_state ON file_monitors (state) WHERE NOT is_deleted;

-- ============================================================
-- MONITOR JOB BINDINGS
-- ============================================================
CREATE TABLE monitor_job_bindings (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    monitor_id          UUID NOT NULL,
    job_id              UUID,
    chain_id            UUID,

    CONSTRAINT ck_binding_target CHECK (
        (job_id IS NOT NULL AND chain_id IS NULL) OR
        (job_id IS NULL AND chain_id IS NOT NULL)
    ),
    CONSTRAINT fk_bindings_monitors FOREIGN KEY (monitor_id)
        REFERENCES file_monitors (id) ON DELETE CASCADE,
    CONSTRAINT fk_bindings_jobs FOREIGN KEY (job_id)
        REFERENCES jobs (id) ON DELETE CASCADE,
    CONSTRAINT fk_bindings_chains FOREIGN KEY (chain_id)
        REFERENCES job_chains (id) ON DELETE CASCADE
);

CREATE INDEX ix_bindings_monitor ON monitor_job_bindings (monitor_id);

-- ============================================================
-- MONITOR DIRECTORY STATE
-- ============================================================
CREATE TABLE monitor_directory_state (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    monitor_id          UUID NOT NULL,
    directory_listing   JSONB NOT NULL,
    captured_at         TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT fk_dir_state_monitors FOREIGN KEY (monitor_id)
        REFERENCES file_monitors (id) ON DELETE CASCADE
);

-- Only keep latest state per monitor; old snapshots are pruned
CREATE INDEX ix_dir_state_monitor ON monitor_directory_state (monitor_id, captured_at DESC);

-- ============================================================
-- MONITOR FILE LOG (PARTITIONED)
-- ============================================================
CREATE TABLE monitor_file_log (
    id                  UUID NOT NULL DEFAULT gen_random_uuid(),
    monitor_id          UUID NOT NULL,
    file_path           TEXT NOT NULL,
    file_size           BIGINT NOT NULL,
    file_hash           TEXT,
    last_modified       TIMESTAMPTZ NOT NULL,
    triggered_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    execution_id        UUID NOT NULL,

    CONSTRAINT pk_monitor_file_log PRIMARY KEY (id, triggered_at)
) PARTITION BY RANGE (triggered_at);

CREATE INDEX ix_monitor_file_log_monitor ON monitor_file_log (monitor_id, file_path, triggered_at DESC);
CREATE INDEX ix_monitor_file_log_execution ON monitor_file_log (execution_id);

13.3.5 Cross-Cutting Tables

-- ============================================================
-- TAGS
-- ============================================================
CREATE TABLE tags (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL,
    color               TEXT,
    category            TEXT,
    description         TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_deleted          BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at          TIMESTAMPTZ
);

CREATE UNIQUE INDEX ix_tags_name ON tags (LOWER(name)) WHERE NOT is_deleted;
CREATE INDEX ix_tags_category ON tags (category) WHERE NOT is_deleted AND category IS NOT NULL;

-- ============================================================
-- ENTITY TAGS (POLYMORPHIC JOIN)
-- ============================================================
CREATE TABLE entity_tags (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tag_id              UUID NOT NULL,
    entity_type         TEXT NOT NULL,
    entity_id           UUID NOT NULL,

    CONSTRAINT fk_entity_tags_tags FOREIGN KEY (tag_id)
        REFERENCES tags (id) ON DELETE CASCADE,
    CONSTRAINT ck_entity_tags_type CHECK (
        entity_type IN ('job', 'job_chain', 'connection', 'pgp_key', 'ssh_key', 'file_monitor')
    )
);

CREATE UNIQUE INDEX ix_entity_tags_unique ON entity_tags (tag_id, entity_type, entity_id);
CREATE INDEX ix_entity_tags_entity ON entity_tags (entity_type, entity_id);

-- ============================================================
-- AUDIT LOG (PARTITIONED)
-- ============================================================
CREATE TABLE audit_log_entries (
    id                  UUID NOT NULL DEFAULT gen_random_uuid(),
    entity_type         TEXT NOT NULL,
    entity_id           UUID NOT NULL,
    operation           TEXT NOT NULL,
    performed_by        TEXT NOT NULL,
    performed_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    details             JSONB DEFAULT '{}',

    CONSTRAINT pk_audit_log PRIMARY KEY (id, performed_at),
    CONSTRAINT ck_audit_entity_type CHECK (
        entity_type IN ('job', 'job_execution', 'step_execution', 'chain',
                        'chain_execution', 'connection', 'pgp_key', 'ssh_key', 'file_monitor')
    )
) PARTITION BY RANGE (performed_at);

CREATE INDEX ix_audit_entity ON audit_log_entries (entity_type, entity_id, performed_at DESC);
CREATE INDEX ix_audit_performed_at ON audit_log_entries (performed_at DESC);
CREATE INDEX ix_audit_performed_by ON audit_log_entries (performed_by, performed_at DESC);

-- ============================================================
-- DOMAIN EVENTS (PARTITIONED)
-- ============================================================
CREATE TABLE domain_events (
    id                  UUID NOT NULL DEFAULT gen_random_uuid(),
    event_type          TEXT NOT NULL,
    entity_type         TEXT NOT NULL,
    entity_id           UUID NOT NULL,
    payload             JSONB NOT NULL,
    occurred_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at        TIMESTAMPTZ,
    processed_by        TEXT,

    CONSTRAINT pk_domain_events PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX ix_domain_events_unprocessed ON domain_events (occurred_at)
    WHERE processed_at IS NULL;
CREATE INDEX ix_domain_events_type ON domain_events (event_type, occurred_at DESC);
CREATE INDEX ix_domain_events_entity ON domain_events (entity_type, entity_id, occurred_at DESC);

-- ============================================================
-- SYSTEM SETTINGS
-- ============================================================
CREATE TABLE system_settings (
    key                 TEXT PRIMARY KEY,
    value               TEXT NOT NULL,
    description         TEXT,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_by          TEXT NOT NULL
);

-- Seed default settings
INSERT INTO system_settings (key, value, description, updated_by) VALUES
    ('job.concurrency_limit', '5', 'Maximum concurrent job executions', 'system'),
    ('job.temp_cleanup_days', '7', 'Days before orphaned temp directories are purged', 'system'),
    ('monitor.file_log_retention_days', '30', 'Days before monitor file log entries are pruned', 'system'),
    ('key.expiration_warning_days', '30', 'Days before expiration to transition keys to Expiring', 'system'),
    ('audit.partition_retention_months', '12', 'Months of audit log partitions to retain', 'system'),
    ('security.fips_mode_enabled', 'true', 'Enforce FIPS 140-2 approved algorithms for internal operations', 'system'),
    ('security.fips_override_require_admin', 'true', 'Require Admin role to set fips_override on connections', 'system'),
    ('security.public_key_share_links_enabled', 'false', 'Allow Admins to generate unauthenticated share links for public keys', 'system'),
    ('security.max_share_link_days', '30', 'Maximum expiration period in days for public key share links', 'system'),
    ('security.insecure_trust_require_admin', 'true', 'Require Admin role to set AlwaysTrust (SSH) or Insecure (TLS) on connections', 'system'),
    ('security.insecure_trust_allow_production', 'false', 'Allow insecure trust policies in production environments', 'system');

13.3.6 Quartz.NET Scheduler Tables

Quartz.NET uses the AdoJobStore for persistent scheduling, which requires its own set of tables in the database. These tables store trigger definitions, cron expressions, job details, and scheduler state so that schedules survive application restarts.

Quartz.NET maintains an official PostgreSQL DDL script that creates approximately 12 tables with the QRTZ_ prefix:

QRTZ_JOB_DETAILS, QRTZ_TRIGGERS, QRTZ_CRON_TRIGGERS, QRTZ_SIMPLE_TRIGGERS, QRTZ_SIMPROP_TRIGGERS, QRTZ_BLOB_TRIGGERS, QRTZ_CALENDARS, QRTZ_PAUSED_TRIGGER_GRPS, QRTZ_FIRED_TRIGGERS, QRTZ_SCHEDULER_STATE, QRTZ_LOCKS

Source: The official script is available in the Quartz.NET repository at database/tables/tables_postgres.sql and should be referenced from the latest stable release matching the NuGet package version used in the project.

DbUp integration: The Quartz DDL is included as the second migration script, executed immediately after the Courier schema:

0001_initial_schema.sql          -- Courier tables (sections 13.3.1–13.3.5)
0002_quartz_scheduler.sql        -- Quartz.NET tables (copied from official source)
0003_seed_system_settings.sql    -- Default system settings

The 0002_quartz_scheduler.sql script is a direct copy of the official Quartz.NET PostgreSQL DDL with one modification: a QRTZ_ table prefix scoped to a scheduler instance name (default: CourierScheduler) to avoid collisions if the database is shared with other applications.

Quartz.NET configuration:

services.AddQuartz(q =>
{
    q.SchedulerId = "CourierScheduler";
    q.UsePersistentStore(store =>
    {
        store.UsePostgres(connectionString);
        store.UseNewtonsoftJsonSerializer();
    });
});

Important: These tables are managed entirely by Quartz.NET. Courier code never reads from or writes to QRTZ_* tables directly — all interaction goes through the Quartz.NET IScheduler API. The job_schedules and chain_schedules tables in the Courier schema (Section 13.3.1) are the application-level representation of schedules; Quartz.NET tables are the runtime execution layer. Job schedules use the "courier" Quartz group, while chain schedules use the "courier-chains" group to avoid collision.

13.4 EF Core Mapping Configuration

Although migrations are managed via DbUp, EF Core is used as the ORM. Entity mappings must match the snake_case schema exactly. Use the Npgsql.EntityFrameworkCore.PostgreSQL provider with the snake_case naming convention:

services.AddDbContext<CourierDbContext>(options =>
    options.UseNpgsql(connectionString, npgsql =>
    {
        npgsql.MigrationsHistoryTable("__ef_migrations_history");  // Not used, but configured
    })
    .UseSnakeCaseNamingConvention());  // via EFCore.NamingConventions package

Key configuration points:

public class CourierDbContext : DbContext
{
    // Aggregate roots
    public DbSet<Job> Jobs => Set<Job>();
    public DbSet<JobExecution> JobExecutions => Set<JobExecution>();
    public DbSet<JobChain> JobChains => Set<JobChain>();
    public DbSet<ChainExecution> ChainExecutions => Set<ChainExecution>();
    public DbSet<Connection> Connections => Set<Connection>();
    public DbSet<PgpKey> PgpKeys => Set<PgpKey>();
    public DbSet<SshKey> SshKeys => Set<SshKey>();
    public DbSet<FileMonitor> FileMonitors => Set<FileMonitor>();
    public DbSet<Tag> Tags => Set<Tag>();
    public DbSet<AuditLogEntry> AuditLogEntries => Set<AuditLogEntry>();
    public DbSet<DomainEvent> DomainEvents => Set<DomainEvent>();
    public DbSet<SystemSetting> SystemSettings => Set<SystemSetting>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Global soft delete filter on all major entities
        modelBuilder.Entity<Job>().HasQueryFilter(j => !j.IsDeleted);
        modelBuilder.Entity<JobChain>().HasQueryFilter(c => !c.IsDeleted);
        modelBuilder.Entity<Connection>().HasQueryFilter(c => !c.IsDeleted);
        modelBuilder.Entity<PgpKey>().HasQueryFilter(k => !k.IsDeleted);
        modelBuilder.Entity<SshKey>().HasQueryFilter(k => !k.IsDeleted);
        modelBuilder.Entity<FileMonitor>().HasQueryFilter(m => !m.IsDeleted);
        modelBuilder.Entity<Tag>().HasQueryFilter(t => !t.IsDeleted);

        // JSONB column mappings
        modelBuilder.Entity<Job>()
            .Property(j => j.FailurePolicy)
            .HasColumnType("jsonb");

        modelBuilder.Entity<JobStep>()
            .Property(s => s.Configuration)
            .HasColumnType("jsonb");

        modelBuilder.Entity<FileMonitor>()
            .Property(m => m.WatchTarget)
            .HasColumnType("jsonb");

        modelBuilder.Entity<FileMonitor>()
            .Property(m => m.FilePatterns)
            .HasColumnType("jsonb");

        modelBuilder.Entity<Connection>()
            .Property(c => c.SshAlgorithms)
            .HasColumnType("jsonb");

        // "group" is a reserved word — explicit column mapping
        modelBuilder.Entity<Connection>()
            .Property(c => c.Group)
            .HasColumnName("group");

        // SystemSetting uses Key as PK, not Id
        modelBuilder.Entity<SystemSetting>()
            .HasKey(s => s.Key);
    }
}

13.5 Partition Management

Partitioned tables require monthly partitions to be created in advance. A scheduled DbUp-compatible maintenance script or background service handles this:

-- ============================================================
-- Partition creation function
-- Called monthly by a scheduled job or on application startup
-- ============================================================
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    target_date DATE DEFAULT CURRENT_DATE + INTERVAL '1 month'
)
RETURNS void AS $$
DECLARE
    partition_start DATE := date_trunc('month', target_date);
    partition_end DATE := partition_start + INTERVAL '1 month';
    suffix TEXT := to_char(partition_start, 'YYYY_MM');
BEGIN
    -- Job executions
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS job_executions_%s PARTITION OF job_executions
         FOR VALUES FROM (%L) TO (%L)',
        suffix, partition_start, partition_end
    );

    -- Step executions
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS step_executions_%s PARTITION OF step_executions
         FOR VALUES FROM (%L) TO (%L)',
        suffix, partition_start, partition_end
    );

    -- Monitor file log
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS monitor_file_log_%s PARTITION OF monitor_file_log
         FOR VALUES FROM (%L) TO (%L)',
        suffix, partition_start, partition_end
    );

    -- Audit log
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS audit_log_entries_%s PARTITION OF audit_log_entries
         FOR VALUES FROM (%L) TO (%L)',
        suffix, partition_start, partition_end
    );

    -- Domain events
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS domain_events_%s PARTITION OF domain_events
         FOR VALUES FROM (%L) TO (%L)',
        suffix, partition_start, partition_end
    );
END;
$$ LANGUAGE plpgsql;

-- Create partitions for current month and next 2 months on first run
SELECT create_monthly_partitions(CURRENT_DATE);
SELECT create_monthly_partitions(CURRENT_DATE + INTERVAL '1 month');
SELECT create_monthly_partitions(CURRENT_DATE + INTERVAL '2 months');

A background service in Courier (PartitionMaintenanceService) runs weekly and calls create_monthly_partitions for the upcoming month to ensure partitions always exist ahead of time. If a partition is missing when data is inserted, PostgreSQL raises an error — the service creates partitions 2 months in advance to prevent this.

13.6 Data Retention & Archival

High-volume partitioned tables support retention policies configured via system_settings:

-- ============================================================
-- Partition drop function for data retention
-- Drops partitions older than the configured retention period
-- ============================================================
CREATE OR REPLACE FUNCTION drop_old_partitions(
    table_name TEXT,
    retention_months INT
)
RETURNS void AS $$
DECLARE
    cutoff_date DATE := date_trunc('month', CURRENT_DATE - (retention_months || ' months')::INTERVAL);
    suffix TEXT := to_char(cutoff_date - INTERVAL '1 month', 'YYYY_MM');
    partition_name TEXT := table_name || '_' || suffix;
BEGIN
    -- Only drop if the partition exists
    IF EXISTS (
        SELECT 1 FROM pg_tables
        WHERE tablename = partition_name
    ) THEN
        EXECUTE format('DROP TABLE %I', partition_name);
        RAISE NOTICE 'Dropped partition: %', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

Before dropping, the PartitionMaintenanceService optionally exports the partition data to cold storage (e.g., Azure Blob Storage as compressed CSV) for compliance. The export-then-drop flow is:

1. Export partition to compressed file: COPY (SELECT * FROM audit_log_entries_2025_01) TO '/tmp/audit_2025_01.csv.gz' WITH CSV HEADER
2. Upload to Azure Blob Storage
3. Drop the partition

This is configurable per table. Job execution and audit data typically have longer retention (12+ months) while monitor file logs can be shorter (3–6 months).

13.6.1 Partition Maintenance Failure Modes

The PartitionMaintenanceService is a background job, and background jobs fail. If partition creation or archival stops working, the consequences are predictable and must be planned for.

Failure: Partition creation stops (service down, migration broken, Worker crashed)

If create_monthly_partitions fails to run, existing partitions continue to work until the last pre-created partition's date range is exhausted. Since partitions are created 2 months in advance, the system has a ~2-month grace period before inserts begin failing.

When the grace period expires, any INSERT into a partitioned table (audit log, job execution, step execution, domain event, monitor file log) raises a PostgreSQL error: ERROR: no partition of relation "audit_log_entries" found for row. This is a hard failure — the row is lost, and the calling code receives an exception. For audit log entries, this means audit data is silently dropped unless the caller retries. For job executions, the job fails to record its state transition.

Mitigation: The PartitionMaintenanceService runs weekly and creates partitions 2 months ahead, providing a large failure window. A health check endpoint (/health/partitions) verifies that the next 2 months of partitions exist and returns Unhealthy if any are missing. The alerting system (V2) should monitor this health check. As a belt-and-suspenders defense, the API startup also runs create_monthly_partitions for the current and next month as part of the migration runner, so a fresh deployment always ensures the immediate future is covered.

Failure: Archival stops (Azure Blob unreachable, export query fails, disk full)

If the export-then-drop flow fails, the PartitionMaintenanceService retains the partition — it never drops a partition that hasn't been successfully exported. The partition continues to exist in PostgreSQL, consuming disk and contributing to query planner overhead.

Disk and bloat impact of deferred archival:

Autovacuum interaction: Partitions that are no longer receiving writes (past months) settle into a steady state where autovacuum has little to do. But if those partitions had in-progress job executions when the month rolled over (state updates crossing the partition boundary), they accumulate dead tuples from those final updates. PostgreSQL's autovacuum eventually cleans these, but with many stale partitions, the cumulative autovacuum load can delay vacuum runs on actively-written partitions, causing live table bloat.

Recovery procedure: If archival is overdue, the operator should: (1) fix the underlying issue (Blob Storage connectivity, disk space), (2) manually trigger the archival for each overdue month via POST /api/v1/admin/partitions/\{table\}/\{month\}/archive, (3) verify the export in Blob Storage, (4) let the service drop the archived partitions on its next run. The PartitionMaintenanceService processes months in chronological order, so it will catch up month by month.

Monitoring: The dashboard exposes partition health: oldest retained partition date, partition count per table, estimated size per partition (via pg_total_relation_size), and export status (exported / pending / failed). A system setting partition.archive_overdue_warning_months (default: 2) triggers a warning in the health check when any table has partitions older than retention + warning threshold.

13.7 Performance Considerations

13.7.1 JSONB Column Index Strategy

Courier uses JSONB columns across 10 tables. Most JSONB columns should not have GIN indexes by default — GIN indexes are expensive to maintain (every key/value in the JSON document is indexed) and only justified by specific, frequent query patterns. The principle is: start without GIN indexes, add them when query profiling shows a need, and prefer jsonb_path_ops (supports @> containment queries only) over the default jsonb_ops (supports @>, ?, ?|, ?& but is ~3× larger).

JSONB column inventory and index recommendations:

Recommended V1 indexes (only audit_log_entries):

-- GIN index on audit details for subsystem-specific queries
-- jsonb_path_ops is smaller and faster than default ops, sufficient for @> queries
CREATE INDEX ix_audit_details_gin ON audit_log_entries
    USING GIN (details jsonb_path_ops);

This enables queries like:

-- Find all audit entries for a specific connection
SELECT * FROM audit_log_entries
WHERE details @> '{"connection_id": "a1b2c3d4-..."}'
  AND performed_at >= '2026-01-01'
  AND performed_at < '2026-02-01';

-- Find all file transfers exceeding 1 GB
SELECT * FROM audit_log_entries
WHERE details @> '{"operation": "file_transfer"}'
  AND (details->>'bytes_transferred')::bigint > 1073741824
  AND performed_at >= '2026-01-01';

Note: the second query uses a cast expression that cannot be optimized by GIN — only the @> containment check uses the index; the size filter is applied as a post-filter. For frequent size-based queries, a functional B-tree index on the extracted field would be more efficient:

-- Only add if "transfers larger than X" is a frequent audit query
CREATE INDEX ix_audit_bytes ON audit_log_entries (((details->>'bytes_transferred')::bigint))
    WHERE details ? 'bytes_transferred';

Index size considerations: A GIN jsonb_path_ops index on the audit log is roughly 20–40% the size of the underlying data, depending on JSON document complexity. For 12 months of audit data at ~10 entries/second, this is approximately 2–8 GB of index space. Partition-local indexes keep individual partition indexes smaller (each month's index is built independently), and dropping old partitions drops their indexes too.

13.7.2 General Performance Notes

Partial indexes: Used extensively on is_deleted columns to keep index sizes small. The WHERE NOT is_deleted filter means deleted records don't bloat indexes used for normal operations.

Connection pooling: Aspire configures Npgsql connection pooling. Default pool size of 20 is sufficient for the expected load. The pool is shared across the Job Engine, File Monitor service, and API layer.

Vacuum and analyze: PostgreSQL's autovacuum handles most maintenance. For partitioned tables with high churn (step_executions, audit_log_entries), consider increasing autovacuum_vacuum_scale_factor on those tables to trigger more frequent cleanup. See Section 13.6.1 for the interaction between stale partitions and autovacuum.

---