gitza-mcp/migrations/001_initial.sql

27 lines
1.5 KiB
SQL

CREATE TABLE IF NOT EXISTS oauth_entities (
model text NOT NULL, id_hash text NOT NULL, payload text NOT NULL,
expires_at timestamptz, consumed bigint, grant_id text, uid_hash text, user_code_hash text,
PRIMARY KEY (model, id_hash)
);
CREATE INDEX IF NOT EXISTS oauth_expiry ON oauth_entities (expires_at);
CREATE INDEX IF NOT EXISTS oauth_grant ON oauth_entities (grant_id);
CREATE INDEX IF NOT EXISTS oauth_uid ON oauth_entities (model, uid_hash);
CREATE TABLE IF NOT EXISTS upstream_accounts (
account_id text PRIMARY KEY, login text NOT NULL, credential text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS mcp_grants (
id text PRIMARY KEY, account_id text NOT NULL REFERENCES upstream_accounts(account_id) ON DELETE CASCADE,
client_id text NOT NULL, client_name text NOT NULL, repositories jsonb NOT NULL,
can_write boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now(),
last_used_at timestamptz, revoked_at timestamptz
);
CREATE INDEX IF NOT EXISTS mcp_grants_account ON mcp_grants (account_id);
CREATE TABLE IF NOT EXISTS audit_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, at timestamptz NOT NULL DEFAULT now(),
account_id text, grant_id text, tool text NOT NULL, target text, outcome text NOT NULL
);
CREATE INDEX IF NOT EXISTS audit_at ON audit_events (at);
CREATE TABLE IF NOT EXISTS rate_buckets (
key text PRIMARY KEY, count integer NOT NULL, expires_at timestamptz NOT NULL
);