58 lines
3.1 KiB
SQL
58 lines
3.1 KiB
SQL
-- Local SQLite schema (Room). No server, no accounts, no network: all data stays on the device.
|
|
-- Instants are INTEGER epoch milliseconds UTC. Alarm times are wall-clock (hour, minute) in the device zone.
|
|
|
|
CREATE TABLE alarms (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
hour INTEGER NOT NULL CHECK (hour BETWEEN 0 AND 23),
|
|
minute INTEGER NOT NULL CHECK (minute BETWEEN 0 AND 59),
|
|
enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0,1)),
|
|
days INTEGER NOT NULL DEFAULT 0 CHECK (days BETWEEN 0 AND 127), -- bit 0 = Monday … bit 6 = Sunday; 0 = once
|
|
label TEXT NOT NULL DEFAULT '',
|
|
ringtone_uri TEXT, -- NULL = default alarm sound, 'silent' = none
|
|
vibrate INTEGER NOT NULL DEFAULT 1 CHECK (vibrate IN (0,1)),
|
|
skip_until TEXT, -- ISO date: paused until (inclusive)
|
|
is_bedtime_wake INTEGER NOT NULL DEFAULT 0 CHECK (is_bedtime_wake IN (0,1)),
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX alarms_time ON alarms(hour, minute);
|
|
CREATE UNIQUE INDEX alarms_one_bedtime ON alarms(is_bedtime_wake) WHERE is_bedtime_wake = 1;
|
|
|
|
-- The next concrete ring of an alarm. Exactly one row per enabled alarm; rewritten after every change,
|
|
-- reboot, time or time zone change. Snooze and "dismiss early" live here, not on the alarm.
|
|
CREATE TABLE alarm_instances (
|
|
alarm_id INTEGER PRIMARY KEY REFERENCES alarms(id) ON DELETE CASCADE,
|
|
fire_at INTEGER NOT NULL,
|
|
state TEXT NOT NULL DEFAULT 'scheduled'
|
|
CHECK (state IN ('scheduled','snoozed','ringing','missed','dismissed')),
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX alarm_instances_fire ON alarm_instances(state, fire_at);
|
|
|
|
CREATE TABLE timers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
label TEXT NOT NULL DEFAULT '',
|
|
duration_ms INTEGER NOT NULL CHECK (duration_ms > 0),
|
|
remaining_ms INTEGER NOT NULL CHECK (remaining_ms >= 0), -- valid while paused/idle
|
|
end_at INTEGER, -- set while running
|
|
state TEXT NOT NULL DEFAULT 'running' CHECK (state IN ('running','paused','ringing','idle')),
|
|
created_at INTEGER NOT NULL,
|
|
CHECK ((state = 'running') = (end_at IS NOT NULL))
|
|
);
|
|
CREATE INDEX timers_state ON timers(state, end_at);
|
|
|
|
CREATE TABLE cities (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
zone_id TEXT NOT NULL UNIQUE, -- IANA, e.g. Europe/Paris
|
|
name TEXT NOT NULL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX cities_sort ON cities(sort_order);
|
|
|
|
-- Single stopwatch and bedtime schedule live in DataStore (one instance each, no relations):
|
|
-- stopwatch: state (idle|running|stopped), started_at_elapsed, accumulated_ms, laps (json array of ms)
|
|
-- bedtime: enabled, bed_hour, bed_minute, days, reminder_min, sunrise, sleep_sound
|
|
-- settings: silence_after_min, snooze_min, alarm_volume, gradual_volume_s, volume_buttons,
|
|
-- week_start, home_zone, auto_home_clock, clock_style, show_seconds,
|
|
-- timer_sound, timer_vibrate, night_mode, theme
|