CRE::Persistence::Postgres::Migrations
Constants
MIGRATIONS = [Step.new(1, ["CREATE TABLE IF NOT EXISTS credentials (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n external_id TEXT NOT NULL,\n kind TEXT NOT NULL,\n name TEXT NOT NULL,\n tags JSONB NOT NULL DEFAULT '{}'::jsonb,\n current_version_id UUID,\n pending_version_id UUID,\n previous_version_id UUID,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n UNIQUE (kind, external_id)\n)", "CREATE INDEX IF NOT EXISTS credentials_tags_gin ON credentials USING gin (tags jsonb_path_ops)", "CREATE TABLE IF NOT EXISTS credential_versions (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n credential_id UUID NOT NULL REFERENCES credentials(id),\n ciphertext BYTEA NOT NULL,\n dek_wrapped BYTEA NOT NULL,\n kek_version INT NOT NULL,\n algorithm_id SMALLINT NOT NULL,\n metadata JSONB NOT NULL DEFAULT '{}'::jsonb,\n generated_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n revoked_at TIMESTAMPTZ\n)", "CREATE TABLE IF NOT EXISTS rotations (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n credential_id UUID NOT NULL REFERENCES credentials(id),\n rotator_kind TEXT NOT NULL,\n state TEXT NOT NULL,\n started_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n completed_at TIMESTAMPTZ,\n step_outcomes JSONB NOT NULL DEFAULT '{}'::jsonb,\n failure_reason TEXT\n)", "CREATE INDEX IF NOT EXISTS rotations_in_flight\n ON rotations(state)\n WHERE state NOT IN ('completed','failed','aborted','inconsistent')", "CREATE TABLE IF NOT EXISTS audit_events (\n seq BIGSERIAL PRIMARY KEY,\n event_id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),\n occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n event_type TEXT NOT NULL,\n actor TEXT NOT NULL,\n target_id UUID,\n payload JSONB NOT NULL,\n prev_hash BYTEA NOT NULL,\n content_hash BYTEA NOT NULL,\n hmac BYTEA NOT NULL,\n hmac_key_version INT NOT NULL\n)", "CREATE TABLE IF NOT EXISTS audit_batches (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n start_seq BIGINT NOT NULL,\n end_seq BIGINT NOT NULL,\n merkle_root BYTEA NOT NULL,\n signature BYTEA NOT NULL,\n signing_key_version INT NOT NULL,\n sealed_at TIMESTAMPTZ NOT NULL DEFAULT now()\n)", "CREATE TABLE IF NOT EXISTS kek_versions (\n version INT PRIMARY KEY,\n source TEXT NOT NULL,\n source_ref TEXT,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n retired_at TIMESTAMPTZ\n)", "CREATE OR REPLACE FUNCTION audit_no_modify() RETURNS trigger LANGUAGE plpgsql AS $$\nBEGIN RAISE EXCEPTION 'audit_events is append-only'; END $$", "DROP TRIGGER IF EXISTS audit_events_no_update ON audit_events", "CREATE TRIGGER audit_events_no_update\n BEFORE UPDATE OR DELETE OR TRUNCATE\n ON audit_events\n EXECUTE FUNCTION audit_no_modify()"]), Step.new(2, ["ALTER TABLE credentials ADD COLUMN IF NOT EXISTS last_rotated_at TIMESTAMPTZ", "UPDATE credentials SET last_rotated_at = updated_at WHERE last_rotated_at IS NULL"]), Step.new(3, ["CREATE INDEX IF NOT EXISTS credentials_last_rotated_at ON credentials(last_rotated_at)", "CREATE INDEX IF NOT EXISTS credential_versions_credential_id ON credential_versions(credential_id)", "CREATE UNIQUE INDEX IF NOT EXISTS rotations_one_in_flight_per_cred\n ON rotations(credential_id)\n WHERE state NOT IN ('completed','failed','aborted','inconsistent')"])]