Spaces:
Sleeping
Sleeping
File size: 2,464 Bytes
3493993 c91c7db 3493993 c91c7db 3493993 c91c7db 3493993 c91c7db 3493993 c91c7db | 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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | -- 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);
|