makima/migrations/20250110000003_create_api_keys_table.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Create API keys table for programmatic access (daemons, CLI, integrations)
CREATE TABLE IF NOT EXISTS api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- SHA-256 hash of the key (never store plain key)
    key_hash VARCHAR(64) NOT NULL,
    -- First 8 chars of key for display/identification (e.g., "mk_abc123")
    key_prefix VARCHAR(16) NOT NULL,
    -- User-provided label for the key
    name VARCHAR(255),
    -- Tracking
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Null if active, timestamp if revoked
    revoked_at TIMESTAMPTZ
);

-- Index for key lookup (only active keys)
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash) WHERE revoked_at IS NULL;
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX idx_api_keys_key_prefix ON api_keys(key_prefix);

-- Partial unique index: only one active key per user
CREATE UNIQUE INDEX idx_api_keys_one_active_per_user
    ON api_keys(user_id) WHERE revoked_at IS NULL;

COMMENT ON TABLE api_keys IS 'API keys for programmatic access. Max one active key per user.';
COMMENT ON COLUMN api_keys.key_hash IS 'SHA-256 hash of the full API key';
COMMENT ON COLUMN api_keys.key_prefix IS 'First 8 chars of key for identification (e.g., "mk_abc123")';
COMMENT ON COLUMN api_keys.revoked_at IS 'NULL if active, timestamp when revoked';