-- GOD COMPLEX — core schema. -- -- Every table lives in the `app` schema, which Supabase's Data API (PostgREST) -- does not expose: nothing in it is reachable with the anon or publishable key. -- The web server talks to the database only through the `public.gc_*` wrapper -- functions defined in 20261014000300_api.sql, and only with the service-role -- key. RLS is still enabled on every table, with no policies, as a second wall. -- -- Rollback guidance: this migration creates objects only. To roll back a fresh -- install, `drop schema app cascade;` and drop the `public.gc_*` functions. -- Never do that on a database holding real votes: export first. create schema if not exists app; -- ---------------------------------------------------------------- questions create table app.questions ( id uuid primary key default gen_random_uuid(), slug text not null unique check (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$' and char_length(slug) between 3 and 80), locale text not null default 'en' check (locale ~ '^[a-z]{2}(-[A-Z]{2})?$'), category text not null check (category in ( 'ethics', 'money', 'friendship', 'relationships', 'society', 'human-nature', 'technology', 'philosophy', 'absurdity')), text text not null check (char_length(text) between 10 and 200), option_a text not null check (char_length(option_a) between 1 and 40), option_b text not null check (char_length(option_b) between 1 and 40), prediction_prompt text not null check (char_length(prediction_prompt) between 10 and 160), -- draft: in the queue, not dated. scheduled: has a publication date. -- withdrawn: pulled before publication, kept for the audit trail. status text not null default 'draft' check (status in ('draft', 'scheduled', 'withdrawn')), publication_date date, closing_at timestamptz, -- Queue position for drafts; lower is published first by auto-scheduling. editorial_order integer, -- Monetisation slot. Null means not sponsored, which is every question at -- launch. A sponsored question must be labelled as such in the UI. sponsor_name text check (sponsor_name is null or char_length(sponsor_name) between 2 and 60), created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint questions_scheduled_has_date check ( status <> 'scheduled' or (publication_date is not null and closing_at is not null)), -- An evaluation runs for exactly one UTC calendar day. constraint questions_closes_next_utc_midnight check ( publication_date is null or closing_at = ((publication_date + 1)::timestamp at time zone 'UTC')) ); -- One official evaluation per UTC day. create unique index questions_one_per_day on app.questions (publication_date) where status = 'scheduled'; create index questions_draft_queue on app.questions (editorial_order, created_at) where status = 'draft'; comment on table app.questions is 'The daily evaluations. A scheduled question publishes at 00:00 UTC on publication_date and closes at the next 00:00 UTC.'; -- The deity's question-specific lines: reactions to each option, and the final -- verdict templates shown once the evaluation has closed. create table app.question_responses ( id bigint generated always as identity primary key, question_id uuid not null references app.questions (id) on delete cascade, context text not null check (context in ('selected', 'verdict')), option text not null check (option in ('a', 'b', 'tie')), position smallint not null default 0 check (position between 0 and 20), body text not null check (char_length(body) between 1 and 300), constraint question_responses_selected_option check (context <> 'selected' or option in ('a', 'b')), unique (question_id, context, option, position) ); -- ------------------------------------------------- subjects and identities -- A subject is a player: an anonymous browser, an account, or both after a -- merge. Its public face is the pseudonym "Subject #004218". create table app.subjects ( id uuid primary key default gen_random_uuid(), public_number integer not null unique check (public_number between 1 and 999999), leaderboard_visible boolean not null default true, created_at timestamptz not null default now(), last_seen_at timestamptz not null default now() ); -- A signed, HttpOnly cookie carries a random token; only its SHA-256 is stored. create table app.anonymous_identities ( id uuid primary key default gen_random_uuid(), key_hash text not null unique check (key_hash ~ '^[0-9a-f]{64}$'), subject_id uuid not null references app.subjects (id) on delete cascade, created_at timestamptz not null default now(), last_seen_at timestamptz not null default now() ); create index anonymous_identities_subject on app.anonymous_identities (subject_id); -- An account (Supabase Auth user) and the subject it plays as. Created lazily: -- on the first vote while signed in, or when a guest attaches their history. create table app.profiles ( user_id uuid primary key references auth.users (id) on delete cascade, subject_id uuid not null unique references app.subjects (id) on delete cascade, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); -- Administrators are granted explicitly, by an operator, in SQL. No code path -- in the application can insert into this table. create table app.admins ( user_id uuid primary key references auth.users (id) on delete cascade, note text, created_at timestamptz not null default now() ); -- -------------------------------------------------------------------- votes create table app.votes ( id bigint generated always as identity primary key, question_id uuid not null references app.questions (id) on delete restrict, -- Set to null when the subject is deleted: the tally stays honest, the link -- to the person does not survive. subject_id uuid references app.subjects (id) on delete set null, choice text not null check (choice in ('a', 'b')), prediction smallint not null check (prediction between 0 and 100), created_at timestamptz not null default now(), -- Written once, by finalisation. Never accepted from a client. prediction_error smallint check (prediction_error between 0 and 100), score smallint check (score between 0 and 100), constraint votes_score_matches_error check ( (score is null and prediction_error is null) or score = 100 - prediction_error) ); -- The rule that makes duplicate votes impossible, whatever the application does. -- Null subjects (deleted or merged-away duplicates) are distinct, as intended. create unique index votes_one_per_subject on app.votes (question_id, subject_id); create index votes_subject on app.votes (subject_id, question_id); create index votes_question_choice on app.votes (question_id, choice); -- Final tallies, written when the evaluation closes. create table app.question_results ( question_id uuid primary key references app.questions (id) on delete restrict, total integer not null check (total >= 0), votes_a integer not null check (votes_a >= 0), votes_b integer not null check (votes_b >= 0), -- Percentage choosing A, rounded half away from zero to a whole number. -- Null when nobody voted: there is no percentage of nobody. pct_a smallint check (pct_a between 0 and 100), finalized_at timestamptz not null default now(), constraint question_results_sum check (total = votes_a + votes_b), constraint question_results_pct check ((total = 0) = (pct_a is null)) ); -- ------------------------------------------- precomputed public aggregates -- Rebuilt whenever an evaluation is finalised (or a subject is merged or -- deleted), so the Global Damnation page never aggregates votes per request. create table app.leaderboard_entries ( period text not null check (period in ('all', '30d')), subject_id uuid not null references app.subjects (id) on delete cascade, points integer not null, evaluations integer not null, avg_error numeric(5, 2) not null, accurate integer not null, current_streak integer not null, first_scored_at timestamptz not null, primary key (period, subject_id) ); create index leaderboard_rank on app.leaderboard_entries (period, points desc, avg_error, evaluations desc, first_scored_at); -- Single-row Humanity Repair Status™ snapshot. create table app.humanity_index ( id boolean primary key default true check (id), scored_predictions integer not null, accurate_predictions integer not null, closed_evaluations integer not null, value numeric(4, 1), computed_at timestamptz not null default now() ); -- ------------------------------------------------- operations and auditing create table app.administrative_audit ( id bigint generated always as identity primary key, admin_user_id uuid references auth.users (id) on delete set null, action text not null check (char_length(action) between 2 and 60), target_type text not null check (char_length(target_type) between 2 and 40), target_id text, details jsonb not null default '{}'::jsonb, created_at timestamptz not null default now() ); create index administrative_audit_created on app.administrative_audit (created_at desc); -- Fixed-window counters. Buckets are ":"; the raw -- IP is never stored. Rows older than a day are purged opportunistically. create table app.rate_limit_hits ( bucket text not null check (char_length(bucket) between 3 and 200), window_start timestamptz not null, hits integer not null default 1, primary key (bucket, window_start) ); -- -------------------------------------------------------------- RLS and grants alter table app.questions enable row level security; alter table app.question_responses enable row level security; alter table app.subjects enable row level security; alter table app.anonymous_identities enable row level security; alter table app.profiles enable row level security; alter table app.admins enable row level security; alter table app.votes enable row level security; alter table app.question_results enable row level security; alter table app.leaderboard_entries enable row level security; alter table app.humanity_index enable row level security; alter table app.administrative_audit enable row level security; alter table app.rate_limit_hits enable row level security; -- No policies are defined: anon and authenticated can read and write nothing. revoke all on schema app from public, anon, authenticated; revoke all on all tables in schema app from public, anon, authenticated; revoke all on all sequences in schema app from public, anon, authenticated; -- --------------------------------------------------------------- triggers create function app.touch_updated_at() returns trigger language plpgsql set search_path = app, pg_catalog as $$ begin new.updated_at := now(); return new; end $$; create trigger questions_touch before update on app.questions for each row execute function app.touch_updated_at(); create trigger profiles_touch before update on app.profiles for each row execute function app.touch_updated_at(); -- A question that has been published is a historical record. Its wording, -- options and date can no longer change, whoever holds the connection. create function app.guard_published_question() returns trigger language plpgsql set search_path = app, pg_catalog as $$ begin if old.status = 'scheduled' and old.publication_date <= (clock_timestamp() at time zone 'UTC')::date and (new.text, new.option_a, new.option_b, new.prediction_prompt, new.status, new.publication_date, new.closing_at, new.slug, new.category) is distinct from (old.text, old.option_a, old.option_b, old.prediction_prompt, old.status, old.publication_date, old.closing_at, old.slug, old.category) then raise exception 'GC_PUBLISHED_IMMUTABLE: question % has been published and cannot be changed', old.slug; end if; return new; end $$; create trigger questions_guard_published before update on app.questions for each row execute function app.guard_published_question(); create function app.guard_published_responses() returns trigger language plpgsql set search_path = app, pg_catalog as $$ declare v_question_id uuid := coalesce(new.question_id, old.question_id); begin if exists ( select 1 from app.questions q where q.id = v_question_id and q.status = 'scheduled' and q.publication_date <= (clock_timestamp() at time zone 'UTC')::date ) then raise exception 'GC_PUBLISHED_IMMUTABLE: responses of a published question cannot be changed'; end if; return coalesce(new, old); end $$; create trigger question_responses_guard before update or delete on app.question_responses for each row execute function app.guard_published_responses();