-- Devist Reso — unified memory store.
--
-- Replaces the split between worker_rules (declarative) + mem0
-- (inferred). Supabase is canonical (backed up via standard Supabase
-- snapshots); mem0 stays as a semantic search index that can be
-- rebuilt from this table at any time.
--
-- Apply once after 0001..0012.

create table if not exists public.memories (
    id          bigserial primary key,
    text        text        not null,
    scope       text        not null check (scope in ('project', 'tech', 'user')),
    priority    text        not null check (priority in ('constraint', 'strong', 'preference', 'info')),
    source      text        not null check (source in ('user', 'claude', 'imported')),
    project     text,
    tech        text[]      not null default '{}',
    mem0_id     text,
    created_at  timestamptz not null default now(),
    updated_at  timestamptz not null default now(),
    deleted_at  timestamptz
);

-- Auto-touch updated_at
create or replace function public.memories_touch_updated_at()
returns trigger language plpgsql as $$
begin
    new.updated_at := now();
    return new;
end;
$$;

drop trigger if exists memories_touch on public.memories;
create trigger memories_touch
    before update on public.memories
    for each row execute function public.memories_touch_updated_at();

-- Common access patterns
create index if not exists memories_active_idx
    on public.memories (priority, scope, project)
    where deleted_at is null;

create index if not exists memories_tech_idx
    on public.memories using gin (tech)
    where deleted_at is null;

create index if not exists memories_project_idx
    on public.memories (project)
    where deleted_at is null;

alter table public.memories enable row level security;

drop policy if exists "auth read memories" on public.memories;
create policy "auth read memories"
    on public.memories
    for select
    to authenticated
    using (true);

drop policy if exists "auth insert memories" on public.memories;
create policy "auth insert memories"
    on public.memories
    for insert
    to authenticated
    with check (true);

drop policy if exists "auth update memories" on public.memories;
create policy "auth update memories"
    on public.memories
    for update
    to authenticated
    using (true)
    with check (true);

-- Daemon writes via service-role which bypasses RLS.

alter publication supabase_realtime add table public.memories;
