CodeIssuesDiscussionsWikiPull RequestsProjectsCommitsActionsReleasesContributorsPulse● GatesSecuritySettingsDeploymentsPipelineInsightsAgents✨ Explain✨ Ask AI✨ Workspace✨ Spec✨ Tests▓ Debt Map✨ NL Search🏛 Archaeology
Blame · Line-by-line history
0015_projects.sql
Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.
| 1e162a8 | 1 | -- Gluecron migration 0015: Block E1 — Projects / kanban boards. |
| 2 | -- | |
| 3 | -- Tables: | |
| 4 | -- projects — top-level board scoped to a repo (or org later) | |
| 5 | -- project_columns — ordered columns (To Do / Doing / Done, etc) | |
| 6 | -- project_items — cards on the board. Can be a freeform note OR linked | |
| 7 | -- to an existing issue / pull_request (polymorphic fk). | |
| 8 | -- | |
| 9 | -- Schema follows a lightweight GitHub Projects v2 model but scoped to a repo | |
| 10 | -- for v1 (cross-repo + org boards can be added by nulling repository_id later). | |
| 11 | ||
| 12 | --> statement-breakpoint | |
| 13 | CREATE TABLE IF NOT EXISTS "projects" ( | |
| 14 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 15 | "number" serial NOT NULL, | |
| 16 | "repository_id" uuid NOT NULL, | |
| 17 | "owner_id" uuid NOT NULL, | |
| 18 | "title" text NOT NULL, | |
| 19 | "description" text NOT NULL DEFAULT '', | |
| 20 | "state" text NOT NULL DEFAULT 'open', | |
| 21 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 22 | "updated_at" timestamp DEFAULT now() NOT NULL, | |
| 23 | CONSTRAINT "projects_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories"("id") ON DELETE cascade, | |
| 24 | CONSTRAINT "projects_owner_fk" FOREIGN KEY ("owner_id") REFERENCES "users"("id") | |
| 25 | ); | |
| 26 | ||
| 27 | --> statement-breakpoint | |
| 28 | CREATE INDEX IF NOT EXISTS "projects_repo" ON "projects" ("repository_id"); | |
| 29 | --> statement-breakpoint | |
| 30 | CREATE UNIQUE INDEX IF NOT EXISTS "projects_repo_number" ON "projects" ("repository_id", "number"); | |
| 31 | ||
| 32 | --> statement-breakpoint | |
| 33 | CREATE TABLE IF NOT EXISTS "project_columns" ( | |
| 34 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 35 | "project_id" uuid NOT NULL, | |
| 36 | "name" text NOT NULL, | |
| 37 | "position" integer NOT NULL DEFAULT 0, | |
| 38 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 39 | CONSTRAINT "project_columns_project_fk" FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE cascade | |
| 40 | ); | |
| 41 | ||
| 42 | --> statement-breakpoint | |
| 43 | CREATE INDEX IF NOT EXISTS "project_columns_project" ON "project_columns" ("project_id"); | |
| 44 | ||
| 45 | --> statement-breakpoint | |
| 46 | CREATE TABLE IF NOT EXISTS "project_items" ( | |
| 47 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 48 | "project_id" uuid NOT NULL, | |
| 49 | "column_id" uuid NOT NULL, | |
| 50 | "position" integer NOT NULL DEFAULT 0, | |
| 51 | -- Card content. A card is EITHER a free-form note (note + title set) OR | |
| 52 | -- a linked issue / pull_request (item_type + item_id set). | |
| 53 | "item_type" text NOT NULL DEFAULT 'note', -- note | issue | pr | |
| 54 | "item_id" uuid, -- FK handled per-type at app level | |
| 55 | "title" text NOT NULL DEFAULT '', | |
| 56 | "note" text NOT NULL DEFAULT '', | |
| 57 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 58 | "updated_at" timestamp DEFAULT now() NOT NULL, | |
| 59 | CONSTRAINT "project_items_project_fk" FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE cascade, | |
| 60 | CONSTRAINT "project_items_column_fk" FOREIGN KEY ("column_id") REFERENCES "project_columns"("id") ON DELETE cascade | |
| 61 | ); | |
| 62 | ||
| 63 | --> statement-breakpoint | |
| 64 | CREATE INDEX IF NOT EXISTS "project_items_project" ON "project_items" ("project_id"); | |
| 65 | --> statement-breakpoint | |
| 66 | CREATE INDEX IF NOT EXISTS "project_items_column" ON "project_items" ("column_id", "position"); |