oppi.li
advent of atproto
baileytownsend.dev
1-- Advent challenges table
2CREATE TABLE IF NOT EXISTS challenges (
3 id BIGSERIAL PRIMARY KEY,
4 user_did TEXT NOT NULL,
5 day INT NOT NULL,
6 time_started TIMESTAMPTZ NOT NULL DEFAULT NOW(),
7 time_challenge_one_completed TIMESTAMPTZ NULL,
8 time_challenge_two_completed TIMESTAMPTZ NULL,
9 verification_code_one TEXT NULL,
10 verification_code_two TEXT NULL,
11 CONSTRAINT challenges_user_day_unique UNIQUE(user_did, day),
12 CONSTRAINT challenges_day_range CHECK (day >= 1 AND day <= 25)
13);
14
15-- Indexes to speed up common lookups
16CREATE INDEX IF NOT EXISTS idx_challenges_user_did ON challenges(user_did);
17CREATE INDEX IF NOT EXISTS idx_challenges_day ON challenges(day);