mirror of
https://github.com/lobehub/lobehub.git
synced 2026-09-20 04:56:13 +08:00
Arvin Xu
ad0b410f15
* 🗃️ feat(acceptance): a table for collaboration on an acceptance The first of three layers behind commenting on an acceptance: the schema, the model, and the shared types. Nothing calls them yet — the server and the UI arrive in the PRs stacked on this one — so this can land, or be reverted, on its own. One table carries every human row on an acceptance: a remark in the discussion, a region circled on an evidence image, a reply, a reviewer's approval, an emoji, and the note an agent ships a round with. They differ by `kind` and `anchor_type` rather than by table, because they share a thread shape, an author, permissions and deletion, and splitting them would have meant maintaining that four times. Two things the shape buys, both load-bearing: `evidence_id` and `context_run_id` are separate columns. Someone reading round 4 may circle a screenshot round 4 inherited from round 2; both facts decide whether that note is still about something real. `(acceptance_id, author_user_id, client_id)` is unique, so every write is idempotent from a caller-derived key and no path needs a read before it writes — a reaction, an agent's round note and a retried post all rely on it. Ids are twelve characters rather than a uuid: this one is the addressable part of a comment's URL, and 62^12 leaves a one-in-a-billion collision chance at ~2.5M comments, against a primary key that would turn one into a failed insert rather than two rows sharing an address. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> * 🐛 fix(acceptance): regenerate the comment migration metadata and harden deletion Folding three draft migrations into one edited the SQL by hand and left the generated artifacts describing the pre-fold shape: the snapshot and the DBML still recorded a uuid `id` with a database default, a uuid `parent_comment_id`, and no `author_agent_id` / `editor_data` / `attachments`. The next generated migration would have read that as its baseline and tried to convert types and re-add columns on databases where 0161 already created the final shape. Deleted the draft artifacts and regenerated from the schema; the SQL comes back identical apart from index order, so only the metadata was ever wrong. Two fixes in the deletion path: - A tombstoned root cleared only `content`, leaving the editor tree and the attached files readable through the list endpoint. Clear all three. - Deleting the last two replies at once left the empty tombstone standing forever, because each transaction locked only its own reply and counted the other's uncommitted sibling. Lock the thread root first, in a fixed root- before-child order that also rules out a deadlock with a root deletion. The ordering test spaced its rows apart in time: five writes in a loop can share a microsecond, and the tie then breaks on a random id. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> --------- Co-authored-by: Claude Opus 5 <noreply@anthropic.com>
47 lines
4.8 KiB
SQL
47 lines
4.8 KiB
SQL
CREATE TABLE IF NOT EXISTS "acceptance_comments" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"acceptance_id" uuid NOT NULL,
|
|
"author_user_id" text,
|
|
"author_agent_id" text,
|
|
"workspace_id" text,
|
|
"parent_comment_id" text,
|
|
"kind" text DEFAULT 'comment' NOT NULL,
|
|
"anchor_type" text DEFAULT 'acceptance' NOT NULL,
|
|
"context_run_id" uuid,
|
|
"check_item_id" text,
|
|
"evidence_id" uuid,
|
|
"anchor_rect" jsonb,
|
|
"content" text NOT NULL,
|
|
"editor_data" jsonb,
|
|
"attachments" jsonb,
|
|
"client_id" text NOT NULL,
|
|
"resolved_at" timestamp with time zone,
|
|
"resolved_by_user_id" text,
|
|
"deleted_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_acceptance_id_acceptances_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_acceptance_id_acceptances_id_fk" FOREIGN KEY ("acceptance_id") REFERENCES "public"."acceptances"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_author_user_id_users_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_author_user_id_users_id_fk" FOREIGN KEY ("author_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_author_agent_id_agents_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_author_agent_id_agents_id_fk" FOREIGN KEY ("author_agent_id") REFERENCES "public"."agents"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_workspace_id_workspaces_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_workspace_id_workspaces_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_parent_comment_id_acceptance_comments_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_parent_comment_id_acceptance_comments_id_fk" FOREIGN KEY ("parent_comment_id") REFERENCES "public"."acceptance_comments"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_context_run_id_verify_runs_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_context_run_id_verify_runs_id_fk" FOREIGN KEY ("context_run_id") REFERENCES "public"."verify_runs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_evidence_id_verify_evidence_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_evidence_id_verify_evidence_id_fk" FOREIGN KEY ("evidence_id") REFERENCES "public"."verify_evidence"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" DROP CONSTRAINT IF EXISTS "acceptance_comments_resolved_by_user_id_users_id_fk";--> statement-breakpoint
|
|
ALTER TABLE "acceptance_comments" ADD CONSTRAINT "acceptance_comments_resolved_by_user_id_users_id_fk" FOREIGN KEY ("resolved_by_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "acceptance_comments_acceptance_id_author_user_id_client_id_unique" ON "acceptance_comments" USING btree ("acceptance_id","author_user_id","client_id");--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "acceptance_comments_acceptance_id_created_at_id_idx" ON "acceptance_comments" USING btree ("acceptance_id","created_at","id");--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "acceptance_comments_parent_comment_id_idx" ON "acceptance_comments" USING btree ("parent_comment_id");--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "acceptance_comments_evidence_id_idx" ON "acceptance_comments" USING btree ("evidence_id");--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "acceptance_comments_author_user_id_idx" ON "acceptance_comments" USING btree ("author_user_id");--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "acceptance_comments_author_agent_id_idx" ON "acceptance_comments" USING btree ("author_agent_id");--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "acceptance_comments_workspace_id_idx" ON "acceptance_comments" USING btree ("workspace_id"); |