kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1-- =================================================================
2-- Setup: Ensure required tables and constraints are handled idempotently
3-- =================================================================
4
5-- Create invitation table if it doesn't exist
6CREATE TABLE IF NOT EXISTS "invitation" (
7 "id" text PRIMARY KEY NOT NULL,
8 "workspace_id" text NOT NULL,
9 "email" text NOT NULL,
10 "role" text,
11 "team_id" text, -- This column exists from your previous snippet.
12 "status" text DEFAULT 'pending' NOT NULL,
13 "expires_at" timestamp NOT NULL,
14 "inviter_id" text NOT NULL
15);
16
17-- Create team_member table if it doesn't exist
18CREATE TABLE IF NOT EXISTS "team_member" (
19 "id" text PRIMARY KEY NOT NULL,
20 "team_id" text NOT NULL,
21 "user_id" text NOT NULL,
22 "created_at" timestamp
23);
24
25-- Create team table if it doesn't exist
26CREATE TABLE IF NOT EXISTS "team" (
27 "id" text PRIMARY KEY NOT NULL,
28 "name" text NOT NULL,
29 "workspace_id" text NOT NULL,
30 "created_at" timestamp NOT NULL,
31 "updated_at" timestamp
32);
33
34-- Add unique constraint for workspace_member (needed for UPSERT)
35-- This needs to be done before the INSERT with ON CONFLICT.
36DO $$
37BEGIN
38 IF NOT EXISTS (
39 SELECT 1 FROM pg_catalog.pg_constraint
40 WHERE conname = 'workspace_member_workspace_id_user_id_unique'
41 AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')
42 AND conrelid = 'workspace_member'::regclass
43 ) THEN
44 ALTER TABLE "workspace_member"
45 ADD CONSTRAINT "workspace_member_workspace_id_user_id_unique"
46 UNIQUE ("workspace_id", "user_id");
47 END IF;
48END $$;
49
50-- Add foreign key constraints idempotently
51DO $$
52BEGIN
53 -- For invitation table
54 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'invitation_workspace_id_workspace_id_fk' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
55 ALTER TABLE "invitation" ADD CONSTRAINT "invitation_workspace_id_workspace_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id") ON DELETE CASCADE;
56 END IF;
57 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'invitation_inviter_id_user_id_fk' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
58 ALTER TABLE "invitation" ADD CONSTRAINT "invitation_inviter_id_user_id_fk" FOREIGN KEY ("inviter_id") REFERENCES "public"."user"("id") ON DELETE CASCADE;
59 END IF;
60
61 -- For team tables
62 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'team_workspace_id_workspace_id_fk' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
63 ALTER TABLE "team" ADD CONSTRAINT "team_workspace_id_workspace_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id") ON DELETE CASCADE;
64 END IF;
65 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'team_member_team_id_team_id_fk' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
66 ALTER TABLE "team_member" ADD CONSTRAINT "team_member_team_id_team_id_fk" FOREIGN KEY ("team_id") REFERENCES "public"."team"("id") ON DELETE CASCADE;
67 END IF;
68 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'team_member_user_id_user_id_fk' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
69 ALTER TABLE "team_member" ADD CONSTRAINT "team_member_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE CASCADE;
70 END IF;
71END $$;
72
73
74-- =================================================================
75-- Data Migration: Persist owners and convert pending members to invites
76-- =================================================================
77
78-- Clean up potential duplicates in workspace_member before applying unique constraint
79-- This MUST run before the ON CONFLICT DML if the constraint doesn't exist yet,
80-- or if it was dropped and re-added. If the constraint already exists and is enforced,
81-- this step would be necessary *before* attempting to create the constraint.
82-- Since we're handling constraint creation idempotently above, this DELETE is safe.
83DO $$
84BEGIN
85 -- Check if the constraint exists before attempting the delete to avoid errors if it's already in place
86 IF EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'workspace_member_workspace_id_user_id_unique' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
87 DELETE FROM "workspace_member"
88 WHERE id NOT IN (
89 SELECT DISTINCT ON (workspace_id, user_id) id
90 FROM "workspace_member"
91 ORDER BY workspace_id, user_id, joined_at DESC
92 );
93 END IF;
94END $$;
95
96
97-- Part A: Migrate existing workspace owners to workspace_member roles.
98-- Uses ON CONFLICT to safely handle cases where an owner might already be a member.
99INSERT INTO "workspace_member" (
100 id,
101 workspace_id,
102 user_id,
103 role,
104 status,
105 joined_at
106)
107SELECT
108 gen_random_uuid()::text,
109 w.id,
110 w.owner_id,
111 'owner',
112 'active', -- Assuming existing owners are active members
113 NOW()
114FROM
115 "workspace" w
116WHERE
117 w.owner_id IS NOT NULL
118ON CONFLICT (workspace_id, user_id)
119DO UPDATE SET
120 role = 'owner', -- Ensure owner role is set even if they were a different role before
121 status = 'active'; -- Ensure status is active
122
123-- Part B: Migrate existing "pending" workspace_members to the invitation table.
124-- This should happen after Part A so we can correctly assign inviter_id.
125INSERT INTO "invitation" (
126 id,
127 workspace_id,
128 email,
129 role,
130 status,
131 expires_at,
132 inviter_id
133)
134SELECT
135 gen_random_uuid()::text,
136 wm.workspace_id,
137 u.email,
138 wm.role, -- Carry over the role if it was defined in workspace_member
139 'pending',
140 NOW() + INTERVAL '1 month', -- Set an expiry for the invitation
141 COALESCE(
142 (SELECT user_id FROM workspace_member owner WHERE owner.workspace_id = wm.workspace_id AND owner.role = 'owner' LIMIT 1),
143 (SELECT id FROM "user" LIMIT 1) -- Fallback: use the first user if no owner is found for this workspace yet
144 )
145FROM
146 "workspace_member" wm
147JOIN
148 "user" u ON wm.user_id = u.id
149WHERE
150 wm.status = 'pending'
151ON CONFLICT DO NOTHING; -- Safely avoid inserting duplicates if the migration runs again
152
153
154-- =================================================================
155-- Schema Alterations: Add/Drop Columns and Constraints
156-- =================================================================
157
158DO $$
159BEGIN
160 -- Add new columns to existing tables (using IF NOT EXISTS logic with pg_attribute)
161 IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'session'::regclass AND attname = 'active_workspace_id' AND NOT attisdropped) THEN
162 ALTER TABLE "session" ADD COLUMN "active_workspace_id" text;
163 END IF;
164
165 IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'session'::regclass AND attname = 'active_team_id' AND NOT attisdropped) THEN
166 ALTER TABLE "session" ADD COLUMN "active_team_id" text;
167 END IF;
168
169 IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'workspace'::regclass AND attname = 'slug' AND NOT attisdropped) THEN
170 ALTER TABLE "workspace" ADD COLUMN "slug" text;
171 END IF;
172
173 IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'workspace'::regclass AND attname = 'logo' AND NOT attisdropped) THEN
174 ALTER TABLE "workspace" ADD COLUMN "logo" text;
175 END IF;
176
177 IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'workspace'::regclass AND attname = 'metadata' AND NOT attisdropped) THEN
178 ALTER TABLE "workspace" ADD COLUMN "metadata" text;
179 END IF;
180
181 IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'invitation'::regclass AND attname = 'team_id' AND NOT attisdropped) THEN
182 ALTER TABLE "invitation" ADD COLUMN "team_id" text;
183 END IF;
184
185 -- Drop columns (using IF EXISTS logic with pg_attribute)
186 IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'workspace'::regclass AND attname = 'owner_id' AND NOT attisdropped) THEN
187 ALTER TABLE "workspace" DROP COLUMN "owner_id";
188 END IF;
189
190 IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'workspace_member'::regclass AND attname = 'status' AND NOT attisdropped) THEN
191 ALTER TABLE "workspace_member" DROP COLUMN "status";
192 END IF;
193END $$;
194
195-- Add the unique constraint for workspace slug (idempotently)
196DO $$
197BEGIN
198 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = 'workspace_slug_unique' AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) THEN
199 ALTER TABLE "workspace" ADD CONSTRAINT "workspace_slug_unique" UNIQUE("slug");
200 END IF;
201END $$;