GearDB/supabase-schema.sql at main · DRusetsk/GearDB · 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
131
132
133
134
135
-- ──────────────────────────────────────────────────────────
-- 1. TABLES
-- ──────────────────────────────────────────────────────────

create table if not exists public.gear_items (
  id           uuid primary key default gen_random_uuid(),
  user_id      uuid references auth.users on delete cascade not null,
  type         text not null check (type in ('Guitar', 'Amp', 'Pedal', 'Bass', 'Keyboard')),
  name         text not null,
  brand        text,
  year         integer,
  notes        text,
  photo_url    text,
  fields       jsonb not null default '{}',
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

create table if not exists public.amp_presets (
  id           uuid primary key default gen_random_uuid(),
  gear_item_id uuid references public.gear_items on delete cascade not null,
  name         text not null,
  gain         integer not null default 5 check (gain between 0 and 10),
  bass         integer not null default 5 check (bass between 0 and 10),
  mid          integer not null default 5 check (mid between 0 and 10),
  treble       integer not null default 5 check (treble between 0 and 10),
  presence     integer not null default 5 check (presence between 0 and 10),
  reverb       integer not null default 5 check (reverb between 0 and 10),
  volume       integer not null default 5 check (volume between 0 and 10),
  created_at   timestamptz not null default now()
);

-- ──────────────────────────────────────────────────────────
-- 2. UPDATED_AT TRIGGER
-- ──────────────────────────────────────────────────────────

create or replace function public.handle_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists set_updated_at on public.gear_items;
create trigger set_updated_at
  before update on public.gear_items
  for each row execute function public.handle_updated_at();

-- ──────────────────────────────────────────────────────────
-- 3. ROW LEVEL SECURITY
-- ──────────────────────────────────────────────────────────

alter table public.gear_items  enable row level security;
alter table public.amp_presets enable row level security;

-- gear_items policies
create policy "Users select own gear"
  on public.gear_items for select
  using (auth.uid() = user_id);

create policy "Users insert own gear"
  on public.gear_items for insert
  with check (auth.uid() = user_id);

create policy "Users update own gear"
  on public.gear_items for update
  using (auth.uid() = user_id);

create policy "Users delete own gear"
  on public.gear_items for delete
  using (auth.uid() = user_id);

-- amp_presets policies (ownership via gear_items join)
create policy "Users select own presets"
  on public.amp_presets for select
  using (
    exists (
      select 1 from public.gear_items
      where id = gear_item_id and user_id = auth.uid()
    )
  );

create policy "Users insert own presets"
  on public.amp_presets for insert
  with check (
    exists (
      select 1 from public.gear_items
      where id = gear_item_id and user_id = auth.uid()
    )
  );

create policy "Users update own presets"
  on public.amp_presets for update
  using (
    exists (
      select 1 from public.gear_items
      where id = gear_item_id and user_id = auth.uid()
    )
  );

create policy "Users delete own presets"
  on public.amp_presets for delete
  using (
    exists (
      select 1 from public.gear_items
      where id = gear_item_id and user_id = auth.uid()
    )
  );

-- ──────────────────────────────────────────────────────────
-- 4. STORAGE BUCKET
-- ──────────────────────────────────────────────────────────

-- Create the bucket (ignore error if it already exists)
insert into storage.buckets (id, name, public, file_size_limit)
values ('gear-photos', 'gear-photos', true, 10485760)  -- 10 MB limit
on conflict (id) do nothing;

-- Storage policies
create policy "Authenticated users can upload gear photos"
  on storage.objects for insert
  with check (bucket_id = 'gear-photos' and auth.uid() is not null);

create policy "Public read access for gear photos"
  on storage.objects for select
  using (bucket_id = 'gear-photos');

create policy "Users can update their own gear photos"
  on storage.objects for update
  using (bucket_id = 'gear-photos' and auth.uid() is not null);

create policy "Users can delete their own gear photos"
  on storage.objects for delete
  using (bucket_id = 'gear-photos' and auth.uid() is not null);