app/security/migrations/0001_api_key_security.sql

-- MediaRouter security schema migration 0001 (SQLite and PostgreSQL).
-- Use migration management in production; application metadata creation is
-- retained only for local development/backward compatibility.
CREATE TABLE IF NOT EXISTS api_keys (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  key_prefix VARCHAR(40) NOT NULL,
  key_hash VARCHAR(64) NOT NULL UNIQUE,
  environment VARCHAR(16) NOT NULL,
  status VARCHAR(16) NOT NULL,
  role VARCHAR(64),
  scopes JSON NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  last_used_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ,
  grace_expires_at TIMESTAMPTZ,
  created_by VARCHAR(120),
  notes TEXT,
  rotated_from_id VARCHAR(36) REFERENCES api_keys(id) ON DELETE SET NULL,
  requests_per_minute INTEGER NOT NULL,
  concurrent_jobs INTEGER NOT NULL,
  uploads_per_hour INTEGER NOT NULL,
  processing_bytes_per_day BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_api_keys_key_prefix ON api_keys(key_prefix);
CREATE UNIQUE INDEX IF NOT EXISTS ix_api_keys_key_hash ON api_keys(key_hash);
CREATE INDEX IF NOT EXISTS ix_api_keys_status ON api_keys(status);
CREATE INDEX IF NOT EXISTS ix_api_keys_expires_at ON api_keys(expires_at);

CREATE TABLE IF NOT EXISTS audit_logs (
  id VARCHAR(36) PRIMARY KEY,
  request_id VARCHAR(36) NOT NULL,
  api_key_id VARCHAR(36) REFERENCES api_keys(id) ON DELETE SET NULL,
  key_name VARCHAR(120),
  ip_address VARCHAR(64),
  user_agent VARCHAR(512),
  endpoint VARCHAR(1024) NOT NULL,
  http_method VARCHAR(16) NOT NULL,
  response_code INTEGER NOT NULL,
  processing_time_ms INTEGER NOT NULL,
  bytes_uploaded BIGINT NOT NULL DEFAULT 0,
  bytes_downloaded BIGINT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_audit_logs_api_key_id ON audit_logs(api_key_id);
CREATE INDEX IF NOT EXISTS ix_audit_logs_created_at ON audit_logs(created_at);
CREATE INDEX IF NOT EXISTS ix_audit_logs_request_id ON audit_logs(request_id);

CREATE TABLE IF NOT EXISTS rate_limits (
  api_key_id VARCHAR(36) NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
  bucket_type VARCHAR(32) NOT NULL,
  bucket_start TIMESTAMPTZ NOT NULL,
  count BIGINT NOT NULL DEFAULT 0,
  units BIGINT NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY(api_key_id, bucket_type, bucket_start)
);
CREATE INDEX IF NOT EXISTS ix_rate_limits_api_key_id ON rate_limits(api_key_id);
CREATE INDEX IF NOT EXISTS ix_rate_limits_bucket_start ON rate_limits(bucket_start);