konnosDashboard
0038_mcp_keys.sql33 lines · main
1-- 0038_mcp_keys — MCP / Agent-Access keys (B Phase 5).
2--
3-- The on/off + key-issuing surface for the future MCP server. A key is the
4-- credential an agent / MCP client presents to reach a project once MCP is
5-- enabled for it. Same one-time-reveal discipline as api_keys /
6-- briven_auth_sdk_keys: the plaintext is returned exactly once on issue; only
7-- a sha-256 hex digest is stored, alongside a constant `prefix` and the 4-char
8-- `suffix` so the dashboard can render `pk_briven_mcp_•••<suffix>`.
9--
10-- The GLOBAL on/off flag (mcp.enabled) and per-project enablement
11-- (mcp.project.<id>) live in platform_settings — not here. The mcp.* audit
12-- trail reuses the existing audit_logs table via the audit() helper; no
13-- dedicated audit table is created. IF NOT EXISTS guards keep this safe if a
14-- partial create ever ran.
15CREATE TABLE IF NOT EXISTS "mcp_keys" (
16 "id" text PRIMARY KEY NOT NULL,
17 "project_id" text NOT NULL,
18 "name" text NOT NULL,
19 "hash" text NOT NULL,
20 "prefix" text NOT NULL,
21 "suffix" varchar(4) NOT NULL,
22 "scope" text DEFAULT 'read' NOT NULL,
23 "enabled" boolean DEFAULT true NOT NULL,
24 "created_by" text NOT NULL,
25 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
26 "last_used_at" timestamp with time zone,
27 "revoked_at" timestamp with time zone,
28 CONSTRAINT "mcp_keys_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action,
29 CONSTRAINT "mcp_keys_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action
30);
31--> statement-breakpoint
32CREATE UNIQUE INDEX IF NOT EXISTS "mcp_keys_hash_idx" ON "mcp_keys" USING btree ("hash");--> statement-breakpoint
33CREATE INDEX IF NOT EXISTS "mcp_keys_project_idx" ON "mcp_keys" USING btree ("project_id");