supabase/migrations/0012_constructs.sql

-- 0012_constructs.sql — the "construct" entity: a sequence + all its derived
-- artifacts (variant library, plasmid, CRISPR guides, primers) + its loop
-- phase. This is the grouping key the app was missing: plasmids /
-- crispr_designs / primer_analyses / libraries were silos with no backref to
-- their source sequence, so nothing tied a user's work together.
--
-- Run this in the Supabase SQL editor (Dashboard → SQL → New query → Run)
-- before/at deploy of the construct-centric UI (Mission Control + Bench).
-- Idempotent: safe to run more than once. Mirrors public.plasmids (0005)
-- exactly for the security posture — owner-only RLS, a TTL via expires_at
-- (NULL = keep forever, for future pro plans), and list/expiry indexes. The
-- Space backend writes/reads with the service-role key (bypasses RLS) and
-- also filters on user_id in code; the RLS policies are the defense-in-depth
-- layer for any direct anon-key client access.

create table if not exists public.constructs (
    id            uuid primary key default gen_random_uuid(),
    user_id       uuid not null references auth.users (id) on delete cascade,
    created_at    timestamptz not null default now(),
    updated_at    timestamptz not null default now(),
    expires_at    timestamptz,                          -- NULL = never expires
    name          text not null default 'Untitled construct',

    -- Sequence identity (the grouping key).
    sequence_hash text not null,                        -- sha256(seq)[:16], from _hash_sequence
    wt_identifier text,                                 -- e.g. ">MC1R" / gene symbol
    wt_protein    text not null default '',             -- amino-acid sequence
    sequence_dna  text not null default '',             -- full WT coding DNA

    -- Loop phase.
    phase         text not null default 'Design',       -- 'Design' | 'Build' | 'Edit' | 'Learn'

    -- Denormalised artifact references (nullable; populated as tools run).
    library_id    uuid,                                 -- latest DE library
    plasmid_ids   uuid[] not null default '{}',
    crispr_ids    uuid[] not null default '{}',
    primer_ids    uuid[] not null default '{}',

    meta          jsonb not null default '{}'::jsonb
);

comment on table public.constructs is
    'A sequence + all its derived artifacts + loop phase. Groups a user''s '
    'work by source sequence (sequence_hash). Owner-only RLS; TTL via expires_at.';

alter table public.constructs enable row level security;

drop policy if exists "constructs_select_own" on public.constructs;
create policy "constructs_select_own"
    on public.constructs for select
    using (auth.uid() = user_id);

drop policy if exists "constructs_insert_own" on public.constructs;
create policy "constructs_insert_own"
    on public.constructs for insert
    with check (auth.uid() = user_id);

drop policy if exists "constructs_update_own" on public.constructs;
create policy "constructs_update_own"
    on public.constructs for update
    using (auth.uid() = user_id);

drop policy if exists "constructs_delete_own" on public.constructs;
create policy "constructs_delete_own"
    on public.constructs for delete
    using (auth.uid() = user_id);

-- List query: a user's constructs, newest activity first.
create index if not exists constructs_user_updated_idx
    on public.constructs (user_id, updated_at desc);

-- Dedup / grouping lookup by source sequence.
create index if not exists constructs_user_seq_hash_idx
    on public.constructs (user_id, sequence_hash);

-- Lazy expiry sweep target.
create index if not exists constructs_expires_idx
    on public.constructs (expires_at)
    where expires_at is not null;