migrations/0002_api_keys.sql

-- 0002_api_keys.sql

CREATE TABLE IF NOT EXISTS api_keys (
    key_id VARCHAR PRIMARY KEY, -- This is the public part of the key
    merchant_id VARCHAR NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    name VARCHAR NOT NULL DEFAULT 'Default Key',
    secret_hash VARCHAR NOT NULL, -- Hashed secret
    scopes JSONB NOT NULL DEFAULT '["read", "write"]'::jsonb,
    last_used_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_api_keys_merchant ON api_keys(merchant_id);