schema.sql

-- Optional: run this in the Supabase SQL Editor if you want a history of
-- every verse shown during a service (e.g. to publish "today's references"
-- afterward, or review what was preached on over time).
--
-- The app works without this table — it's purely for logging/history,
-- not required for the live detector -> display flow, which happens
-- entirely over Realtime broadcast.

create table if not exists scripture_log (
  id            bigint generated always as identity primary key,
  reference     text not null,
  verse_text    text,
  translation   text,
  source        text,        -- "auto-detected" or "manual"
  shown_at      timestamptz not null default now()
);

create index if not exists scripture_log_shown_at_idx on scripture_log (shown_at desc);

-- If you want the detector page to write here too, un-comment the insert
-- call in detector.html's handleDetectedRef() function and point it at
-- this table using the Supabase client already set up on that page.