123// Exact DDL is mirrored in bench/fixtures/apps/com.lattice.habits/schema.sql.exportconstDDL =
"CREATE TABLE IF NOT EXISTS habits (\n id TEXT PRIMARY KEY, /* UUID */\n title TEXT NOT NULL CHECK (length(trim(title)) > 0),\n schedule TEXT NOT NULL CHECK (schedule IN ('daily','weekdays','weekly')),\n weekdays TEXT NOT NULL DEFAULT '[]', /* JSON array: 0 Sunday through 6 Saturday, used only by weekdays */\n weekly_target INTEGER NOT NULL DEFAULT 1 CHECK (weekly_target BETWEEN 1 AND 7), /* required distinct checked days per week, only used by weekly schedule */\n start_date TEXT NOT NULL, /* inclusive local YYYY-MM-DD */\n archived_on TEXT, /* local YYYY-MM-DD, exclusive end, NULL means active */\n created_at TEXT NOT NULL /* UTC ISO 8601, e.g. 2026-10-15T12:00:00.000Z */\n);\nCREATE TABLE IF NOT EXISTS habit_checks (\n habit_id TEXT NOT NULL REFERENCES habits(id),\n date TEXT NOT NULL, /* local YYYY-MM-DD, one check per habit/day */\n checked_at TEXT NOT NULL, /* UTC ISO 8601 */\n PRIMARY KEY (habit_id, date)\n);\nCREATE TABLE IF NOT EXISTS habit_periods (\n habit_id TEXT NOT NULL REFERENCES habits(id),\n period_end TEXT NOT NULL, /* inclusive local YYYY-MM-DD, Sunday for weekly schedule */\n period_start TEXT NOT NULL, /* inclusive first eligible YYYY-MM-DD */\n target INTEGER NOT NULL, /* required distinct checked dates in this assessed period */\n completed INTEGER NOT NULL, /* distinct checked dates at assessment time */\n assessed_at TEXT NOT NULL, /* UTC ISO 8601, snapshot is not rewritten by backdating */\n PRIMARY KEY (habit_id, period_end)\n);\nCREATE TABLE IF NOT EXISTS habit_milestones (\n habit_id TEXT NOT NULL REFERENCES habits(id),\n period_start TEXT NOT NULL, /* local YYYY-MM-DD of check, Monday for weekly schedule */\n milestone INTEGER NOT NULL, /* scheduled days or weeks according to habit schedule */\n created_at TEXT NOT NULL, /* UTC ISO 8601 */\n PRIMARY KEY (habit_id, period_start, milestone)\n);\nCREATE TABLE IF NOT EXISTS event_outbox (\n id TEXT PRIMARY KEY, /* stable deduplication key for an outgoing event */\n name TEXT NOT NULL,\n payload TEXT NOT NULL, /* JSON object conforming to the manifest event schema */\n created_at TEXT NOT NULL, /* UTC ISO 8601 */\n delivered_at TEXT /* UTC ISO 8601, NULL until host accepts event */\n);\nCREATE TABLE IF NOT EXISTS listener_receipts (\n event_hash TEXT PRIMARY KEY, /* host event hash, scoped by listener name */\n processed_at TEXT NOT NULL /* UTC ISO 8601 */\n);\n";