29 lines
840 B
SQL
29 lines
840 B
SQL
-- Scope organization slugs per owner instead of globally unique
|
|
ALTER TABLE organizations ADD COLUMN owner_id UUID REFERENCES users(id);
|
|
|
|
WITH first_owner AS (
|
|
SELECT DISTINCT ON (org_id) org_id, user_id
|
|
FROM memberships
|
|
WHERE role = 'owner'
|
|
ORDER BY org_id, created_at
|
|
)
|
|
UPDATE organizations o
|
|
SET owner_id = fo.user_id
|
|
FROM first_owner fo
|
|
WHERE o.id = fo.org_id;
|
|
|
|
WITH first_member AS (
|
|
SELECT DISTINCT ON (org_id) org_id, user_id
|
|
FROM memberships
|
|
ORDER BY org_id, created_at
|
|
)
|
|
UPDATE organizations o
|
|
SET owner_id = fm.user_id
|
|
FROM first_member fm
|
|
WHERE o.owner_id IS NULL
|
|
AND o.id = fm.org_id;
|
|
|
|
ALTER TABLE organizations ALTER COLUMN owner_id SET NOT NULL;
|
|
ALTER TABLE organizations DROP CONSTRAINT organizations_slug_key;
|
|
CREATE UNIQUE INDEX organizations_owner_slug_key ON organizations(owner_id, slug);
|