lorentz
b9df27b656
- Add @aws-sdk/client-route-53 dependency (official aws-sdk-js-v3 package) - Add migrations/102_route53_tables.sql: route53_zones, route53_records, route53_record_history (D-06 change ledger), route53_audit_log (D-03/D-07 attempt audit log with pending/committed/failed status) - Seed integration_settings row for key='route53' (D-10, display-only toggle) - Unbounded retention by design (D-08) — no purge job, no TTL, no DELETE
149 lines
8.1 KiB
SQL
149 lines
8.1 KiB
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;
|