mirror of
https://github.com/paperclipai/paperclip.git
synced 2026-10-12 08:19:54 +02:00
DottaandPaperclip
1de0a3bb1e
## Thinking Path > - Paperclip is the open source app people use to manage AI agents for work > - Governed MCP access spans contracts, runtime enforcement, adapters, UI surfaces, and operator verification > - The parity reference PR #9534 is too large for effective automated or human review > - The feature therefore needs a linear stack whose individual diffs stay below the 100-file review limit > - This pull request is split 2/8 and focuses on database schema and shared governance contracts > - The benefit is a standalone, testable review boundary while preserving byte-for-byte parity at the top of the stack ## Linked Issues or Issue Description - Related parity reference: #9534 - Problem: The governed access model needs additive persistence and synchronized shared types before server enforcement can compile. - Proposed solution: Adds migrations 0148–0169, tool-access and Smoke Lab schema, shared types/validators/gallery helpers, and the minimal compile-required contract consumers identified by boundary testing. - Alternatives considered: keeping #9534 as one 403-file review, or rewriting the feature to manufacture seams; both were rejected in favor of path extraction plus compile-driven boundary moves. - Roadmap alignment: this advances the existing governed MCP/tool-access work already represented by #9534; it does not introduce a separate roadmap initiative. - Stack position: base branch is `pap10341-split/01-demo-servers`. - Merge policy: merge bottom-up, in order, only after the complete eight-PR stack has been reviewed and the top-of-stack parity gate remains empty. - Requested review: QA for migrations/validators; Greptile on every PR. ## What Changed - Adds migrations 0148–0169, tool-access and Smoke Lab schema, shared types/validators/gallery helpers, and the minimal compile-required contract consumers identified by boundary testing. - Keeps this PR below 100 changed files and independently typecheckable. - Preserves the final tree from #9534 when combined with the other seven stack levels. ## Verification - `pnpm typecheck` — passed, including migration numbering and safety checks - `pnpm --filter @paperclipai/db test` — passed - `pnpm --filter @paperclipai/shared test` — passed ## Risks - Migration or contract mistakes could affect every upper layer; all migrations are additive/idempotent and compile consumers are included in this boundary. - Stack risk: merging out of order can expose incomplete layers; mitigate by following the documented bottom-up merge policy. - Parity risk: later edits to an intermediate branch can drift from #9534; mitigate by re-running the empty top-of-stack diff before merge. > For core feature work, check [`ROADMAP.md`](ROADMAP.md) first and discuss it in `#dev` before opening the PR. Feature PRs that overlap with planned core work may need to be redirected — check the roadmap first. See `CONTRIBUTING.md`. ## Model Used - OpenAI Codex, exact model ID `gpt-5.4`; runtime-managed context window; medium reasoning with repository, shell, Git, GitHub CLI, and code-execution tools enabled. ## Checklist - [x] I have included a thinking path that traces from project context to this change - [x] I have specified the model used (with version and capability details) - [x] I have checked ROADMAP.md and confirmed this PR does not duplicate planned core work - [x] I have searched GitHub for duplicate or related PRs and linked them above - [x] I have either (a) linked existing issues with `Fixes: #` / `Closes #` / `Refs #` OR (b) described the issue in-PR following the relevant issue template - [x] Internal references are omitted except the execution-plan link explicitly required for this coordinated split stack - [x] My branch name describes the change and contains no internal Paperclip ticket id or instance-derived details - [x] I have run tests locally and they pass - [x] I have added or updated tests where applicable - [x] I have updated relevant documentation to reflect my changes - [x] I have considered and documented any risks above - [ ] All Paperclip CI gates are green - [ ] Greptile is 5/5 with no open P2s, recommendations, or follow-ups - [x] I will address all Greptile and reviewer comments before requesting merge ## Stack Coordination - Internal execution plan: [PAP-13874](/PAP/issues/PAP-13874#document-plan) - Parity reference: #9534 - Stack: #9556 → #9557 → #9558 → #9559 → #9560 → #9561 → #9562 → #9563 - Merge bottom-up only after full-stack review and an empty parity diff at #9563. --------- Co-authored-by: Paperclip <noreply@paperclip.ing>
73 lines
4.6 KiB
SQL
73 lines
4.6 KiB
SQL
CREATE TABLE IF NOT EXISTS "tool_mcp_gateways" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"company_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"slug" text NOT NULL,
|
|
"description" text,
|
|
"status" text DEFAULT 'active' NOT NULL,
|
|
"profile_id" uuid NOT NULL,
|
|
"agent_id" uuid,
|
|
"project_id" uuid,
|
|
"issue_id" uuid,
|
|
"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
"created_by_agent_id" uuid,
|
|
"created_by_user_id" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "tool_mcp_gateway_tokens" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"company_id" uuid NOT NULL,
|
|
"gateway_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"token_hash" text NOT NULL,
|
|
"expires_at" timestamp with time zone,
|
|
"last_used_at" timestamp with time zone,
|
|
"revoked_at" timestamp with time zone,
|
|
"created_by_agent_id" uuid,
|
|
"created_by_user_id" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateways_company_id_companies_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateways" ADD CONSTRAINT "tool_mcp_gateways_company_id_companies_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."companies"("id") ON DELETE cascade ON UPDATE no action;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateways_profile_id_tool_profiles_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateways" ADD CONSTRAINT "tool_mcp_gateways_profile_id_tool_profiles_id_fk" FOREIGN KEY ("profile_id") REFERENCES "public"."tool_profiles"("id") ON DELETE restrict ON UPDATE no action;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateways_agent_id_agents_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateways" ADD CONSTRAINT "tool_mcp_gateways_agent_id_agents_id_fk" FOREIGN KEY ("agent_id") REFERENCES "public"."agents"("id") ON DELETE set null ON UPDATE no action;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateways_project_id_projects_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateways" ADD CONSTRAINT "tool_mcp_gateways_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateways_issue_id_issues_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateways" ADD CONSTRAINT "tool_mcp_gateways_issue_id_issues_id_fk" FOREIGN KEY ("issue_id") REFERENCES "public"."issues"("id") ON DELETE set null ON UPDATE no action;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateway_tokens_company_id_companies_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateway_tokens" ADD CONSTRAINT "tool_mcp_gateway_tokens_company_id_companies_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."companies"("id") ON DELETE cascade ON UPDATE no action;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'tool_mcp_gateway_tokens_gateway_id_tool_mcp_gateways_id_fk') THEN
|
|
ALTER TABLE "tool_mcp_gateway_tokens" ADD CONSTRAINT "tool_mcp_gateway_tokens_gateway_id_tool_mcp_gateways_id_fk" FOREIGN KEY ("gateway_id") REFERENCES "public"."tool_mcp_gateways"("id") ON DELETE cascade ON UPDATE no action;
|
|
END IF;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "tool_mcp_gateways_company_idx" ON "tool_mcp_gateways" USING btree ("company_id");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "tool_mcp_gateways_company_status_idx" ON "tool_mcp_gateways" USING btree ("company_id", "status");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "tool_mcp_gateways_profile_idx" ON "tool_mcp_gateways" USING btree ("company_id", "profile_id");
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "tool_mcp_gateways_company_slug_uq" ON "tool_mcp_gateways" USING btree ("company_id", "slug");
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "tool_mcp_gateways_company_name_uq" ON "tool_mcp_gateways" USING btree ("company_id", "name");
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "tool_mcp_gateway_tokens_token_hash_uq" ON "tool_mcp_gateway_tokens" USING btree ("token_hash");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "tool_mcp_gateway_tokens_gateway_idx" ON "tool_mcp_gateway_tokens" USING btree ("company_id", "gateway_id");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "tool_mcp_gateway_tokens_company_expires_idx" ON "tool_mcp_gateway_tokens" USING btree ("company_id", "expires_at");
|