31 lines
1.6 KiB
SQL
31 lines
1.6 KiB
SQL
-- Sādhana streak: one row per user per day on which they chanted.
|
|
--
|
|
-- A "day complete" (what the streak + the growing-plant animation are
|
|
-- built on) is the conjunction of two daily disciplines:
|
|
-- 1. chanting today's target rounds → recorded here (chant_done)
|
|
-- 2. posting at least one quote today → derived live from the posts
|
|
-- table (a post whose created_at falls on the same calendar day)
|
|
-- so this table only needs to capture the chanting half.
|
|
--
|
|
-- log_date is a DATE (not a timestamp) so the streak rolls over at UTC
|
|
-- midnight without any timezone math, matching how the rest of the app
|
|
-- buckets "today". chant_done is stored explicitly — decided at save time
|
|
-- as rounds_completed >= the user's target THEN — so later edits to a
|
|
-- user's daily_chanting_rounds never retroactively rewrite history.
|
|
CREATE TABLE IF NOT EXISTS sadhna_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
log_date DATE NOT NULL,
|
|
rounds_completed INTEGER NOT NULL DEFAULT 0,
|
|
chant_done BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (user_id, log_date),
|
|
CHECK (rounds_completed >= 0 AND rounds_completed <= 1008)
|
|
);
|
|
|
|
-- The streak/growth computation walks a user's recent days newest-first;
|
|
-- this serves both that scan and the "today" lookup.
|
|
CREATE INDEX IF NOT EXISTS sadhna_logs_user_date_idx
|
|
ON sadhna_logs (user_id, log_date DESC);
|