schema.sql

-- attioom: full schema
--
-- Run this in the Supabase SQL editor. Idempotent — safe to re-run.
-- See docs/setup.md for the full setup guide (Zoom + Attio + Supabase).

-- ============================================================================
-- 1. Bridge queue table
--
-- One row per Zoom recording. The webhook enqueues a row (status=pending);
-- a cron job advances each row through a resumable ladder (staging, pushing,
-- completed/failed) via a lease-claimed queue. See docs/architecture.md.
-- ============================================================================

create table if not exists public.zoom_attio_bridge_recordings (
  id uuid primary key default gen_random_uuid(),
  zoom_meeting_uuid text not null,
  zoom_meeting_id text,
  host_email text,
  topic text,
  started_at timestamptz,
  duration_seconds int,
  zoom_recording_file_id text,
  staging_path text,
  staging_public_url text,
  attio_meeting_id uuid,
  attio_call_recording_id uuid,
  attio_web_url text,
  status text not null default 'pending',
  skip_reason text,
  error text,
  raw jsonb,
  attempts int not null default 0,
  processing_started_at timestamptz,
  transcript_status text,
  transcript_raw jsonb,
  transcript_attempts int not null default 0,
  transcript_error text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists zoom_attio_bridge_recordings_uuid_uq
  on public.zoom_attio_bridge_recordings (zoom_meeting_uuid);

create index if not exists zoom_attio_bridge_recordings_status_updated_idx
  on public.zoom_attio_bridge_recordings (status, updated_at desc);

create index if not exists zoom_attio_bridge_recordings_host_started_idx
  on public.zoom_attio_bridge_recordings (host_email, started_at desc);

-- Supports the atomic lease-claim query pattern in src/bridge/bridge.ts.
-- NOTE: the claim logic deliberately issues two sequential single-column
-- UPDATEs (.is / .lt) rather than one .or()-filtered UPDATE — PostgREST
-- applies an or= logic-tree filter on an UPDATE to the RETURNING
-- representation, not to the WHERE clause, which silently sets the lease
-- while returning zero rows. See the comment on claimBridgeRowLease in
-- src/bridge/bridge.ts before "simplifying" this back to one .or() call.
create index if not exists zoom_attio_bridge_recordings_claim_idx
  on public.zoom_attio_bridge_recordings (status, processing_started_at, updated_at);

create index if not exists zoom_attio_bridge_recordings_transcript_claim_idx
  on public.zoom_attio_bridge_recordings (status, transcript_status, transcript_attempts, processing_started_at);

alter table public.zoom_attio_bridge_recordings enable row level security;

-- No authenticated/anon read policy: rows carry Zoom download metadata and
-- staged-file paths. All access goes through server code using the service
-- role key (which bypasses RLS by design).

-- ============================================================================
-- 2. Scheduler run-state table
--
-- Single-row bookkeeping for the auto-recording scheduler: when it last
-- ran, whether it succeeded, and summary stats from the last run.
-- ============================================================================

create table if not exists public.bridge_scheduler_state (
  id boolean primary key default true check (id),  -- enforces exactly one row
  cursor jsonb,
  last_synced_at timestamptz,
  last_success_at timestamptz,
  last_error_at timestamptz,
  last_error text,
  updated_at timestamptz not null default now()
);

alter table public.bridge_scheduler_state enable row level security;

-- ============================================================================
-- 3. Webhook dedup / claim-lifecycle table
--
-- Generic durable dedup store used by src/webhook-dedup.ts. Two-phase
-- claim -> process -> commit pattern so a crash mid-handler can't
-- permanently swallow an event: 'claimed' rows past a staleness window
-- (default 5 min) are reclaimable by src/webhook-dedup.ts's sweep function,
-- which your own cron/scheduler should call periodically.
-- ============================================================================

create table if not exists public.webhook_event_seen (
  source text not null,
  event_id text not null,
  status text not null default 'processed'
    check (status in ('claimed', 'processed', 'failed_permanent')),
  claimed_at timestamptz,
  processed_at timestamptz,
  first_seen_at timestamptz not null default now(),
  primary key (source, event_id)
);

create index if not exists webhook_event_seen_created_idx
  on public.webhook_event_seen (first_seen_at desc);

-- Partial index: sweeper only scans stuck 'claimed' rows, not the whole table.
create index if not exists webhook_event_seen_claimed_at_idx
  on public.webhook_event_seen (claimed_at)
  where status = 'claimed';

alter table public.webhook_event_seen enable row level security;

-- Service role only for all three tables above (no policies granted to
-- anon/authenticated — RLS enabled with zero policies denies all access
-- to those roles by default).

-- ============================================================================
-- 4. Private storage bucket for staged recordings
--
-- Recordings are staged here briefly between download-from-Zoom and
-- upload-to-Attio, served via short-lived signed URLs — never a public
-- route. Remember to also raise your project's storage file-size limit to
-- at least 500MB (Settings -> Storage in the Supabase dashboard) — this
-- can't be done from SQL.
-- ============================================================================

insert into storage.buckets (id, name, public)
values ('zoom-recording-staging', 'zoom-recording-staging', false)
on conflict (id) do nothing;