# Worked example: the mood check-in

> The full schema and tests of the mood check-in pebble that pebblebase's own tests run.

This is the mood check-in pebble that pebblebase tests itself against: daily check-ins, a bad-mood streak, a nudge, and three daily reminders. Use it as a pattern, and build yours from your own request, tests first.

From tests/golden/checkin in the pebblebase repository.

## Schema

```sql
-- Golden check-in reference: daily mood check-ins, a bad-mood streak, a nudge in pebblebase_outbox, note search,
-- and three daily reminders in pebblebase_schedules.

CREATE TABLE checkins (
  id INTEGER PRIMARY KEY,
  day TEXT NOT NULL DEFAULT (date('now', 'localtime')) CHECK (day IS date(day)),
  slot TEXT NOT NULL CHECK (slot IN ('morning', 'midday', 'night')),
  mood TEXT NOT NULL CHECK (mood IN ('good', 'ok', 'bad')),
  note TEXT,
  UNIQUE (day, slot)
);

-- A reusable counter model. Its methods are the views below: insert a name to call one.
CREATE TABLE streaks (
  name TEXT PRIMARY KEY,
  current INTEGER NOT NULL DEFAULT 0
);

CREATE VIEW streaks_hit AS SELECT name FROM streaks;

CREATE TRIGGER streaks_hit INSTEAD OF INSERT ON streaks_hit
BEGIN
  INSERT INTO streaks (name, current) VALUES (new.name, 1)
    ON CONFLICT (name) DO UPDATE SET current = current + 1;
END;

CREATE VIEW streaks_break AS SELECT name FROM streaks;

CREATE TRIGGER streaks_break INSTEAD OF INSERT ON streaks_break
BEGIN
  INSERT INTO streaks (name, current) VALUES (new.name, 0)
    ON CONFLICT (name) DO UPDATE SET current = 0;
END;

CREATE VIRTUAL TABLE checkins_search USING fts5 (note, content = 'checkins', content_rowid = 'id');

CREATE TRIGGER checkins_after_insert AFTER INSERT ON checkins
BEGIN
  INSERT INTO checkins_search (rowid, note) VALUES (new.id, new.note);
  INSERT INTO streaks_hit (name) SELECT 'bad_mood' WHERE new.mood = 'bad';
  INSERT INTO streaks_break (name) SELECT 'bad_mood' WHERE new.mood <> 'bad';
  INSERT INTO pebblebase_outbox (body)
    SELECT 'Three bad check-ins in a row.'
    FROM streaks WHERE name = 'bad_mood' AND current = 3;
END;

CREATE TRIGGER checkins_after_update AFTER UPDATE OF note ON checkins
BEGIN
  INSERT INTO checkins_search (checkins_search, rowid, note) VALUES ('delete', old.id, old.note);
  INSERT INTO checkins_search (rowid, note) VALUES (new.id, new.note);
END;

CREATE TRIGGER checkins_after_delete AFTER DELETE ON checkins
BEGIN
  INSERT INTO checkins_search (checkins_search, rowid, note) VALUES ('delete', old.id, old.note);
END;

CREATE VIEW week AS
  SELECT * FROM checkins WHERE day > date('now', 'localtime', '-7 days') ORDER BY day, slot;

CREATE VIEW bad_streak AS
  SELECT coalesce((SELECT current FROM streaks WHERE name = 'bad_mood'), 0) AS current;

CREATE VIEW mood_trend AS
  SELECT day, avg(CASE mood WHEN 'good' THEN 2 WHEN 'ok' THEN 1 ELSE 0 END) AS mood
  FROM week GROUP BY day ORDER BY day;

-- The person's time zone: 'localtime' above means America/Chicago, across daylight-saving changes.
INSERT INTO pebblebase_settings (name, value) VALUES ('time_zone', 'America/Chicago');

-- What the app shows with the pebble's name: one line of at most 60 characters.
INSERT INTO pebblebase_settings (name, value) VALUES ('description', 'Daily mood check-ins and a bad-mood streak');

-- What the next session needs: how to log, read, fix and backfill.
INSERT INTO pebblebase_notes (name, instructions) VALUES
  ('purpose', 'Daily mood check-ins in three slots, a bad-mood streak, a nudge in pebblebase_outbox after three bad check-ins in a row, and note search.'),
  ('log', 'INSERT INTO checkins (slot, mood, note) VALUES (?, ?, ?). slot is morning, midday or night; mood is good, ok or bad; note is optional. day defaults to the local date, and each slot takes one check-in per day.'),
  ('read', 'Bad-mood streak, one number: SELECT current FROM bad_streak. Last seven local days: SELECT * FROM week. Notes: SELECT c.* FROM checkins_search s JOIN checkins c ON c.id = s.rowid WHERE checkins_search MATCH ?. Views: show_view week, bad_streak or mood_trend.'),
  ('fix', 'UPDATE checkins SET mood = ?, note = ? WHERE day = ? AND slot = ?, or DELETE the row. Search follows note changes. An update or delete does not move the streak or queue a nudge.'),
  ('backfill', 'INSERT INTO checkins (day, slot, mood, note) VALUES (?, ?, ?, ?) with day as YYYY-MM-DD. The streak follows logging order, not day order, so a backfilled bad check-in counts toward it and can queue the nudge.'),
  ('reminders', 'pebblebase_schedules sends a reminder at 09:00, 13:00 and 22:00 local, through the pebblebase app. Each opens the checkins form with its slot filled. Change a time by updating cron; never set reminders anywhere else.');

-- How the checkins form draws: the note field shows this help under it.
INSERT INTO pebblebase_fields (table_name, column_name, help) VALUES ('checkins', 'note', 'Optional. Note search finds it.');

-- What the person sees: show_view runs each row's query read-only.
-- week names checkins as its table, for labels, kinds and links to each check-in's record.
-- bad_streak is the app's Dashboard card.
INSERT INTO pebblebase_views (name, kind, query, settings) VALUES
  ('week', 'table', 'SELECT * FROM week', '{"table": "checkins"}'),
  ('bad_streak', 'streak', 'SELECT current FROM bad_streak', '{"dashboard": 1}'),
  ('mood_trend', 'chart', 'SELECT * FROM mood_trend', NULL);

-- What reaches the person: pebblebase's clock sends each row as a push at its cron, in local time.
INSERT INTO pebblebase_schedules (name, cron, body, form, prefill) VALUES
  ('morning', '0 9 * * *', 'Morning check-in', 'checkins', '{"slot":"morning"}'),
  ('midday', '0 13 * * *', 'Midday check-in', 'checkins', '{"slot":"midday"}'),
  ('night', '0 22 * * *', 'Evening check-in', 'checkins', '{"slot":"night"}');
```

## Tests

```sql
-- Golden check-in behavior as stored tests. run_tests runs each on a copy; expect rows are failures.

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'nudge_waits_for_third_bad',
  'INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''morning'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''midday'', ''bad'');',
  'SELECT body FROM pebblebase_outbox
   UNION ALL SELECT ''bad_streak is '' || current FROM bad_streak WHERE current <> 2'
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'nudge_once_after_three_bad',
  'INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''morning'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''midday'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''night'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-15'', ''morning'', ''bad'');',
  'SELECT ''nudges: '' || count(*) FROM pebblebase_outbox HAVING count(*) <> 1'
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'good_checkin_resets_bad_streak',
  'INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''morning'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''midday'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-14'', ''night'', ''bad'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-15'', ''morning'', ''good'');',
  'SELECT ''bad_streak is '' || current FROM bad_streak WHERE current <> 0
   UNION ALL SELECT ''pebblebase_outbox rows: '' || count(*) FROM pebblebase_outbox HAVING count(*) <> 1'
);

INSERT INTO pebblebase_tests (name, given, expect_error) VALUES (
  'meh_is_rejected',
  'INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-15'', ''morning'', ''meh'');',
  'CHECK constraint failed'
);

INSERT INTO pebblebase_tests (name, given, expect_error) VALUES (
  'one_checkin_per_slot',
  'INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-15'', ''morning'', ''good'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-01-15'', ''morning'', ''ok'');',
  'UNIQUE constraint failed'
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'week_is_the_last_seven_days',
  'SELECT pebblebase_set_clock(''2026-10-10 12:00:00'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-10-07'', ''morning'', ''good'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-10-02'', ''morning'', ''good'');',
  'SELECT ''missing 2026-10-07'' WHERE NOT EXISTS (SELECT 1 FROM week WHERE day = ''2026-10-07'')
   UNION ALL SELECT ''included '' || day FROM week WHERE day = ''2026-10-02'''
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'night_checkin_is_local_today',
  'SELECT pebblebase_set_clock(''2026-01-16 04:00:00'');
   INSERT INTO checkins (slot, mood) VALUES (''night'', ''good'');',
  'SELECT ''day is '' || day FROM checkins WHERE day IS NOT ''2026-01-15''
   UNION ALL SELECT ''UTC date is '' || date(''now'') WHERE date(''now'') IS NOT ''2026-01-16'''
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'night_checkin_after_spring_forward',
  'SELECT pebblebase_set_clock(''2026-03-09 03:00:00'');
   INSERT INTO checkins (slot, mood) VALUES (''night'', ''good'');',
  'SELECT ''day is '' || day FROM checkins WHERE day IS NOT ''2026-03-08'''
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'night_checkin_after_fall_back',
  'SELECT pebblebase_set_clock(''2026-11-02 04:00:00'');
   INSERT INTO checkins (slot, mood) VALUES (''night'', ''good'');',
  'SELECT ''day is '' || day FROM checkins WHERE day IS NOT ''2026-11-01'''
);

INSERT INTO pebblebase_tests (name, given, expect) VALUES (
  'week_uses_local_days',
  'SELECT pebblebase_set_clock(''2026-03-10 04:30:00'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-03-02'', ''night'', ''good'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-03-03'', ''night'', ''good'');
   INSERT INTO checkins (day, slot, mood) VALUES (''2026-03-09'', ''night'', ''good'');',
  'SELECT ''week holds '' || group_concat(day) FROM week HAVING group_concat(day) IS NOT ''2026-03-03,2026-03-09'''
);
```