StudyMesh/apps/studymesh/docs/supabase-repair-delete-cascade.sql at main · CosmeValera/StudyMesh · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- StudyMesh Supabase cascade repair
-- Run once if deleting an Auth user did not remove their dashboards/widgets.
--
-- Why this exists:
-- `create table if not exists` does not retrofit foreign keys onto tables that
-- already existed. This script removes orphaned rows, then recreates the FK
-- constraints with `on delete cascade`.

begin;

create table if not exists public.user_study_guides (
  id text not null,
  owner_id uuid not null references public.profiles(id) on delete cascade,
  title text not null,
  folder_name text not null default 'Study Guide',
  description text,
  study_path jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (owner_id, id)
);

-- Remove rows for profiles/Auth users that no longer exist. App tables are
-- profile-owned, and profiles cascade from auth.users.
delete from public.user_widget_versions
where owner_id not in (select id from auth.users);

delete from public.user_widget_versions
where owner_id not in (select id from public.profiles);

delete from public.user_widget_versions versions
where not exists (
  select 1
  from public.user_widgets widgets
  where widgets.owner_id = versions.owner_id
    and widgets.id = versions.widget_id
);

delete from public.user_dashboards
where owner_id not in (select id from auth.users);

delete from public.user_dashboards
where owner_id not in (select id from public.profiles);

delete from public.user_study_guides
where owner_id not in (select id from auth.users);

delete from public.user_study_guides
where owner_id not in (select id from public.profiles);

delete from public.user_widgets
where owner_id not in (select id from auth.users);

delete from public.user_widgets
where owner_id not in (select id from public.profiles);

delete from public.user_workspace_state
where owner_id not in (select id from auth.users);

delete from public.user_workspace_state
where owner_id not in (select id from public.profiles);

delete from public.profiles
where id not in (select id from auth.users);

-- Recreate profile ownership foreign keys with cascade behavior.
alter table public.profiles
  drop constraint if exists profiles_id_fkey;

alter table public.profiles
  add constraint profiles_id_fkey
  foreign key (id)
  references auth.users(id)
  on delete cascade;

alter table public.user_dashboards
  drop constraint if exists user_dashboards_owner_id_fkey;

alter table public.user_dashboards
  add constraint user_dashboards_owner_id_fkey
  foreign key (owner_id)
  references public.profiles(id)
  on delete cascade;

alter table public.user_widgets
  drop constraint if exists user_widgets_owner_id_fkey;

alter table public.user_widgets
  add constraint user_widgets_owner_id_fkey
  foreign key (owner_id)
  references public.profiles(id)
  on delete cascade;

alter table public.user_study_guides
  drop constraint if exists user_study_guides_owner_id_fkey;

alter table public.user_study_guides
  add constraint user_study_guides_owner_id_fkey
  foreign key (owner_id)
  references public.profiles(id)
  on delete cascade;

alter table public.user_widget_versions
  drop constraint if exists user_widget_versions_owner_id_fkey;

alter table public.user_widget_versions
  add constraint user_widget_versions_owner_id_fkey
  foreign key (owner_id)
  references public.profiles(id)
  on delete cascade;

alter table public.user_widget_versions
  drop constraint if exists user_widget_versions_owner_id_widget_id_fkey;

alter table public.user_widget_versions
  add constraint user_widget_versions_owner_id_widget_id_fkey
  foreign key (owner_id, widget_id)
  references public.user_widgets(owner_id, id)
  on delete cascade;

alter table public.user_workspace_state
  drop constraint if exists user_workspace_state_owner_id_fkey;

alter table public.user_workspace_state
  add constraint user_workspace_state_owner_id_fkey
  foreign key (owner_id)
  references public.profiles(id)
  on delete cascade;

commit;