47 lines
1.6 KiB
SQL
47 lines
1.6 KiB
SQL
-- Item tier enum
|
|
create type item_tier as enum ('fixed', 'variable', 'one_off');
|
|
|
|
-- Templates table (one per user)
|
|
create table templates (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
name text not null default 'My Template',
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique(user_id)
|
|
);
|
|
|
|
create index templates_user_id_idx on templates(user_id);
|
|
|
|
alter table templates enable row level security;
|
|
|
|
create policy "Users can CRUD own template"
|
|
on templates for all
|
|
using (user_id = auth.uid())
|
|
with check (user_id = auth.uid());
|
|
|
|
-- Template items table
|
|
create table template_items (
|
|
id uuid primary key default gen_random_uuid(),
|
|
template_id uuid not null references templates(id) on delete cascade,
|
|
category_id uuid not null references categories(id) on delete restrict,
|
|
item_tier item_tier not null check (item_tier in ('fixed', 'variable')),
|
|
budgeted_amount numeric(12,2) not null default 0,
|
|
sort_order int not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index template_items_template_id_idx on template_items(template_id);
|
|
|
|
alter table template_items enable row level security;
|
|
|
|
create policy "Users can CRUD own template items"
|
|
on template_items for all
|
|
using (
|
|
template_id in (select id from templates where user_id = auth.uid())
|
|
)
|
|
with check (
|
|
template_id in (select id from templates where user_id = auth.uid())
|
|
);
|