mirror of
https://github.com/taylanbakircioglu/flowfish.git
synced 2026-09-12 05:48:55 +00:00
taylanbakircioglu
d7ca50b387
Multi-cluster dependency mapping, real-time network monitoring, impact analysis, and CI/CD integration capabilities. Made-with: Cursor
60 lines
2.4 KiB
SQL
60 lines
2.4 KiB
SQL
-- Migration: Add API Keys table for CI/CD pipeline authentication
|
|
-- Date: 2026-01-29
|
|
-- Description: Creates api_keys table for storing API keys used by pipelines
|
|
|
|
-- Create the api_keys table
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id SERIAL PRIMARY KEY,
|
|
key_id VARCHAR(50) UNIQUE NOT NULL, -- Public identifier (key_xxx)
|
|
key_hash VARCHAR(255) NOT NULL, -- Hashed API key (never store plain!)
|
|
key_prefix VARCHAR(12) NOT NULL, -- First 8 chars for identification (fk_xxxxxxxx)
|
|
name VARCHAR(255) NOT NULL, -- Human-readable name
|
|
description TEXT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id), -- Who created this key
|
|
|
|
-- Permissions & Scope
|
|
scopes TEXT[] DEFAULT ARRAY['blast-radius'], -- Allowed API scopes
|
|
cluster_ids INTEGER[], -- NULL = all clusters, or specific IDs
|
|
|
|
-- Expiration & Status
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
expires_at TIMESTAMP, -- NULL = never expires
|
|
last_used_at TIMESTAMP,
|
|
last_used_ip VARCHAR(45),
|
|
usage_count INTEGER DEFAULT 0,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
revoked_at TIMESTAMP,
|
|
revoked_by INTEGER REFERENCES users(id),
|
|
revoke_reason TEXT,
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Create indexes for fast lookups
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_key_id ON api_keys(key_id);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_key_prefix ON api_keys(key_prefix);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_is_active ON api_keys(is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_expires ON api_keys(expires_at) WHERE expires_at IS NOT NULL;
|
|
|
|
-- Add API key permissions to permissions table
|
|
INSERT INTO permissions (resource, action, description)
|
|
VALUES
|
|
('api_keys', 'view', 'View API keys'),
|
|
('api_keys', 'create', 'Create new API keys'),
|
|
('api_keys', 'revoke', 'Revoke API keys')
|
|
ON CONFLICT (resource, action) DO NOTHING;
|
|
|
|
-- Grant API key permissions to Super Admin role
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.name = 'Super Admin'
|
|
AND p.resource = 'api_keys'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Add comment
|
|
COMMENT ON TABLE api_keys IS 'API Keys for CI/CD pipeline authentication. Keys are hashed with SHA256.';
|