Initial Control system

1 files changed, 30 insertions, 0 deletions

diff --git a/makima/migrations/20250110000003_create_api_keys_table.sql b/makima/migrations/20250110000003_create_api_keys_table.sql
new file mode 100644
index 0000000..33832e8
--- /dev/null
+++ b/makima/migrations/20250110000003_create_api_keys_table.sql
@@ -0,0 +1,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';