wulf-pulse/migrations/102_route53_tables.sql

-- AWS Route 53 DNS sync — Phase 24 dedicated Postgres schema.
--
-- Lays down the full Route 53 schema this phase's downstream plans (sync
-- service, CRUD routes, health check, admin UI) will populate and consume.
--
--   • Hosted zones (mirror of AWS Route 53 zones)     -> route53_zones
--   • Resource record sets (mirror of AWS records)    -> route53_records
--   • Append-only change ledger (drift + CRUD)        -> route53_record_history
--   • Append-only attempt audit log (D-03/D-07)        -> route53_audit_log
--
-- Retention is unbounded by design (D-08) — no purge job, no TTL, and no
-- DELETE statement against route53_record_history or route53_audit_log
-- anywhere in this phase, matching existing Pulse convention (itglue_writes,
-- itglue_asset_audits, phishing audit_events are all forever-retained too).

-- ---------------------------------------------------------------------------
-- 1. route53_zones — mirror of AWS Route 53 hosted zones
-- ---------------------------------------------------------------------------
-- id is the AWS hosted zone id with the '/hostedzone/' prefix stripped — the
-- natural stable identifier Route 53 already provides, no synthetic UUID
-- needed. authoritative_name_servers backs the D-12 NS-delegation health
-- check planned in 24-04.

CREATE TABLE IF NOT EXISTS route53_zones (
    id                          TEXT PRIMARY KEY,
    name                        TEXT NOT NULL,
    comment                     TEXT,
    private_zone                BOOLEAN NOT NULL DEFAULT false,
    record_count                INTEGER NOT NULL DEFAULT 0,
    authoritative_name_servers  JSONB,
    raw_payload                 JSONB,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    synced_at                   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    is_deleted                  BOOLEAN NOT NULL DEFAULT false,
    deleted_at                  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_route53_zones_is_deleted ON route53_zones(is_deleted);
CREATE INDEX IF NOT EXISTS idx_route53_zones_name        ON route53_zones(name);

-- ---------------------------------------------------------------------------
-- 2. route53_records — mirror of AWS Route 53 resource record sets
-- ---------------------------------------------------------------------------
-- record_key is the composite string `zoneId:name:type:setIdentifier` (empty
-- string when there is no set identifier) — Route 53 recordsets are uniquely
-- identified by zone+name+type+SetIdentifier; there is no AWS-side record id
-- to use as a natural primary key.

CREATE TABLE IF NOT EXISTS route53_records (
    record_key       TEXT PRIMARY KEY,
    zone_id          TEXT NOT NULL REFERENCES route53_zones(id) ON DELETE CASCADE,
    name             TEXT NOT NULL,
    type             TEXT NOT NULL,
    set_identifier   TEXT,
    ttl              INTEGER,
    resource_records  JSONB,
    alias_target     JSONB,
    raw_payload      JSONB,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    synced_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    is_deleted       BOOLEAN NOT NULL DEFAULT false,
    deleted_at       TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_route53_records_zone         ON route53_records(zone_id);
CREATE INDEX IF NOT EXISTS idx_route53_records_is_deleted   ON route53_records(is_deleted);
CREATE INDEX IF NOT EXISTS idx_route53_records_name_type    ON route53_records(zone_id, name, type);

-- ---------------------------------------------------------------------------
-- 3. route53_record_history — append-only change ledger (D-06)
-- ---------------------------------------------------------------------------
-- Written by BOTH the sync service (source='sync_detected_drift', when a
-- previously-synced record's live AWS value no longer matches Postgres) and
-- the CRUD routes (source='pulse_crud', on every successful write). Distinct
-- from route53_audit_log below: this table is a per-record timeline of
-- resolved changes; the audit log below is a per-attempt ledger including
-- failures. audit_log_id is a soft (non-FK) reference so a history row
-- survives independent of the audit log's own lifecycle.

CREATE TABLE IF NOT EXISTS route53_record_history (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    zone_id             TEXT NOT NULL REFERENCES route53_zones(id) ON DELETE CASCADE,
    record_key          TEXT NOT NULL,
    record_name         TEXT NOT NULL,
    record_type         TEXT NOT NULL,
    change_action       TEXT NOT NULL CHECK (change_action IN ('create','update','delete')),
    before_value        JSONB,
    after_value         JSONB,
    source              TEXT NOT NULL CHECK (source IN ('pulse_crud','sync_detected_drift')),
    changed_by_user_id  TEXT REFERENCES "user"(id) ON DELETE SET NULL,
    changed_by_email    TEXT,
    audit_log_id        UUID,  -- soft ref -> route53_audit_log(id), no hard FK
    changed_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_route53_record_history_record ON route53_record_history(record_key, changed_at DESC);
CREATE INDEX IF NOT EXISTS idx_route53_record_history_source ON route53_record_history(source);
CREATE INDEX IF NOT EXISTS idx_route53_record_history_zone   ON route53_record_history(zone_id, changed_at DESC);

-- ---------------------------------------------------------------------------
-- 4. route53_audit_log — append-only attempt audit log (D-03/D-07)
-- ---------------------------------------------------------------------------
-- One row per attempted CRUD/sync operation, including failures. zone_id is
-- deliberately NOT an FK here (unlike route53_record_history above) — a
-- failed attempt against a zone that was never synced must still be
-- recordable. D-03 accepted tradeoff: destructive record operations execute
-- immediately with no staged approval gate; this table is the compensating
-- post-hoc traceability control (actor, timestamp, before/after for every
-- attempt), not a pre-write block.

CREATE TABLE IF NOT EXISTS route53_audit_log (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    operation             TEXT NOT NULL CHECK (operation IN ('create','update','delete','sync')),
    zone_id               TEXT,
    record_key            TEXT,
    record_name           TEXT,
    record_type           TEXT,
    before_value          JSONB,
    after_value           JSONB,
    performed_by_user_id  TEXT REFERENCES "user"(id) ON DELETE SET NULL,
    performed_by_email    TEXT,
    performed_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at          TIMESTAMPTZ,
    status                TEXT NOT NULL CHECK (status IN ('pending','committed','failed')),
    aws_change_id         TEXT,
    aws_change_status     TEXT,
    aws_response          JSONB,
    error_message         TEXT
);

CREATE INDEX IF NOT EXISTS ix_route53_audit_log_record ON route53_audit_log(zone_id, record_key, performed_at DESC);
CREATE INDEX IF NOT EXISTS ix_route53_audit_log_status ON route53_audit_log(status);

COMMENT ON TABLE route53_record_history IS
  'Append-only per-record change timeline. source distinguishes CRUD writes (pulse_crud) from sync-detected drift (sync_detected_drift). Unbounded retention by design (D-08) — no purge job.';
COMMENT ON TABLE route53_audit_log IS
  'Append-only per-attempt audit log including failures. D-03 accepted tradeoff: destructive record ops execute immediately with no pre-write approval gate; this table is the post-hoc compensating control. Unbounded retention by design (D-08) — no purge job.';

-- ---------------------------------------------------------------------------
-- 5. integration_settings seed row (D-10)
-- ---------------------------------------------------------------------------
-- Display-only toggle on /admin/integrations — no sync/CRUD-blocking
-- behavior anywhere in this phase (unlike PAX8's disable-gates-fullSync
-- exception). Extends the existing seed list from migrations/081 rather
-- than editing that committed file.

INSERT INTO integration_settings (key, disabled) VALUES ('route53', false) ON CONFLICT (key) DO NOTHING;