-- ClientFlow v4 migration 001: core operational indexes and migration ledger.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_outbox_target_status_created
ON integration_outbox(target_system, status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_outbox_status_created
ON integration_outbox(status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_raw_events_source_created
ON raw_events(source_system, created_at DESC);