Files
likang 7e01147566
CI / typecheck + test + build (push) Has been cancelled
Initial commit: import VibeMake PCB Studio
2026-11-03 11:20:08 +08:00

66 lines
2.7 KiB
SQL

-- Ziro Designer, cloud project sync
-- Run once in the Supabase SQL editor (Dashboard → SQL → New query).
-- Creates a per-user `projects` table with Row Level Security so each user
-- can only read/write their own rows. Files are stored gzipped + base64 in a
-- JSONB column, mirroring the app's IndexedDB record shape.
-- The primary key is (user_id, id), not id alone. `id` comes from the browser's
-- local store, so it is only unique within one machine, and making it globally
-- unique meant one account's project id could name another account's row. An
-- upsert then took its ON CONFLICT UPDATE path against a row it did not own and
-- was refused by row-level security ("violates ... (USING expression)"). Scoped
-- this way, two accounts holding the same local id simply keep separate rows.
create table if not exists public.projects (
id uuid not null, -- shared with the local IndexedDB id
user_id uuid not null references auth.users (id) on delete cascade,
name text not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
-- [{ "name": "board.kicad_pcb", "gzB64": "..." }, ...]
files jsonb not null default '[]'::jsonb,
primary key (user_id, id)
);
-- Migration for a database created before the key was scoped. Safe to re-run:
-- `id` was unique, so no (user_id, id) pair can collide.
do $$
begin
if exists (
select 1 from pg_constraint
where conname = 'projects_pkey'
and conrelid = 'public.projects'::regclass
and array_length(conkey, 1) = 1
) then
alter table public.projects drop constraint projects_pkey;
alter table public.projects add primary key (user_id, id);
end if;
end $$;
create index if not exists projects_user_id_idx on public.projects (user_id);
create index if not exists projects_updated_at_idx on public.projects (user_id, updated_at desc);
alter table public.projects enable row level security;
-- Recreate policies idempotently.
drop policy if exists "projects_select_own" on public.projects;
drop policy if exists "projects_insert_own" on public.projects;
drop policy if exists "projects_update_own" on public.projects;
drop policy if exists "projects_delete_own" on public.projects;
create policy "projects_select_own"
on public.projects for select
using (auth.uid() = user_id);
create policy "projects_insert_own"
on public.projects for insert
with check (auth.uid() = user_id);
create policy "projects_update_own"
on public.projects for update
using (auth.uid() = user_id)
with check (auth.uid() = user_id);
create policy "projects_delete_own"
on public.projects for delete
using (auth.uid() = user_id);