makima/migrations/20250110000004_create_api_key_events_table.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Audit log for API key operations
CREATE TABLE IF NOT EXISTS api_key_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    event_type VARCHAR(32) NOT NULL,
    -- Request metadata
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_event_type CHECK (
        event_type IN ('created', 'used', 'revoked', 'refreshed')
    )
);

CREATE INDEX idx_api_key_events_api_key_id ON api_key_events(api_key_id);
CREATE INDEX idx_api_key_events_created_at ON api_key_events(created_at DESC);
CREATE INDEX idx_api_key_events_event_type ON api_key_events(event_type);

COMMENT ON TABLE api_key_events IS 'Audit log for API key lifecycle events';