schema.sql

PRAGMA foreign_keys = ON;

CREATE TABLE days (
    day_number INTEGER PRIMARY KEY,
    date_label TEXT NOT NULL UNIQUE
);

CREATE TABLE reflections (
    day_number INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    prompt TEXT NOT NULL,
    journal_prompt TEXT NOT NULL,
    FOREIGN KEY (day_number) REFERENCES days(day_number) ON DELETE CASCADE
);

CREATE TABLE audio_exercises (
    day_number INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    duration_seconds INTEGER NOT NULL,
    audio_url TEXT,
    FOREIGN KEY (day_number) REFERENCES days(day_number) ON DELETE CASCADE
);

CREATE TABLE habit_trackers (
    day_number INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    goal TEXT NOT NULL,
    check_in_prompt TEXT NOT NULL,
    metric TEXT NOT NULL,
    FOREIGN KEY (day_number) REFERENCES days(day_number) ON DELETE CASCADE
);