-- devist worker — Supabase L2 event log
--
-- Apply once on your Supabase project:
--
--   psql "$SUPABASE_DB_URL" -f migrations/0001_worker_events.sql
--
-- Or paste into the Supabase SQL editor.
--
-- The daemon writes with the service-role key, which bypasses RLS.
-- Add SELECT policies below to expose data to your web frontend.

create table if not exists public.worker_events (
    id                bigserial primary key,
    client_id         text        not null,
    client_event_id   bigint      not null,
    project           text        not null,
    event_type        text        not null,
    path              text,
    payload           jsonb       not null default '{}'::jsonb,
    severity          text        not null default 'info',
    created_at        timestamptz not null,
    pushed_at         timestamptz not null default now(),
    acked_at          timestamptz
);

-- Idempotency: re-pushing the same local row is a no-op.
create unique index if not exists worker_events_client_unique
    on public.worker_events (client_id, client_event_id);

-- Common access patterns:
create index if not exists worker_events_project_created
    on public.worker_events (project, created_at desc);

create index if not exists worker_events_severity
    on public.worker_events (severity)
    where severity in ('warn', 'block');

create index if not exists worker_events_event_type
    on public.worker_events (event_type);

-- Enforce row-level security. By default no role can read/write.
-- The daemon uses the service-role key, which bypasses RLS.
alter table public.worker_events enable row level security;

-- Example read policy for authenticated frontend users (uncomment to enable):
-- create policy "authenticated read all"
--   on public.worker_events
--   for select
--   to authenticated
--   using (true);
