-- 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';