Skip to main content

max / goingson

7.2 KB · 158 lines History Blame Raw
1 -- Contexts: a state that frames days, as distinct from an event that occupies
2 -- them.
3 --
4 -- Ruled by Max 2026-08-18 (task c5f74e0f). Some things occupy your day and
5 -- others frame it. An occupancy consumes hours and competes for them; a context
6 -- consumes none, and things happen inside it rather than beside it. Being on
7 -- leave does not clash with a meeting, it changes what having a meeting means.
8 --
9 -- What this replaces is a distinction the code kept making geometrically.
10 -- `day_planning.rs` derived `is_all_day` from whether a span happened to fill a
11 -- calendar day, so a conference running Wed 14:00 to Fri 17:00 was an occupancy
12 -- on Wednesday, a context on Thursday, and an occupancy again on Friday.
13 -- Nothing about the event changed; only whether it covered a midnight.
14 --
15 -- A context is AUTHORED AS A SPAN and READ PER DAY. Leave taken the 3rd to the
16 -- 17th is one decision, not fifteen. `weekly_reviews.vacation_days` stored the
17 -- per-day projection as if it were the fact, which is why it displayed fine and
18 -- felt wrong to write.
19
20 CREATE TABLE contexts (
21 id TEXT PRIMARY KEY NOT NULL,
22 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
23
24 label TEXT NOT NULL,
25
26 -- Vacation | Trip | Illness | Sprint | Other. Open rather than a CHECK
27 -- constraint: the kinds are the user's, and a newer client's kind reads as
28 -- Other here rather than failing to insert. `ContextKind::from_str` says
29 -- the same thing on the Rust side.
30 kind TEXT NOT NULL DEFAULT 'Other',
31
32 -- Both ends INCLUSIVE, YYYY-MM-DD. "Off until the 17th" means the 17th is
33 -- off; an exclusive end would make every reader subtract one, which is the
34 -- arithmetic that is right in four places and wrong in the fifth.
35 starts_on TEXT NOT NULL,
36 ends_on TEXT NOT NULL,
37
38 -- Provenance for the conversions below, and the condition under which the
39 -- migration was allowed to guess at all (ruling on task 8ee5c4fe): a
40 -- converted record names the event it came from, and putting it back is
41 -- clearing this and un-hiding that event. Nothing is reconstructed by hand.
42 migrated_from_event_id TEXT REFERENCES events(id) ON DELETE SET NULL,
43
44 created_at TEXT NOT NULL DEFAULT (datetime('now')),
45 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
46
47 -- No `group_id`. Every shareable table carries one and this is not one: a
48 -- context is a state a person is in, and sharing "I am on leave" with a
49 -- project group is a different feature with a different consent question.
50 -- Adding the column later is one ALTER; taking sharing back is not.
51 );
52
53 CREATE INDEX idx_contexts_user_span ON contexts(user_id, starts_on, ends_on);
54 CREATE INDEX idx_contexts_migrated_from ON contexts(migrated_from_event_id);
55
56 -- An event that became a context is not deleted, it is hidden.
57 --
58 -- The ruling accepted a conversion that guesses on the condition that a wrong
59 -- guess is not silent and not costly to undo. Deleting the row and keeping only
60 -- its id would leave the user retyping the times of a deadline marker; keeping
61 -- the row and pointing at it makes reversal a single UPDATE. It also means this
62 -- migration destroys nothing, which is worth more than the tidiness of a
63 -- delete for a change that reaches the sync manifest and the restore path.
64 ALTER TABLE events ADD COLUMN converted_to_context_id TEXT REFERENCES contexts(id) ON DELETE SET NULL;
65 CREATE INDEX idx_events_converted ON events(converted_to_context_id);
66
67 -- RULING 1: a multi-day event becomes a context. A single-day one does not.
68 --
69 -- The test is whether the event covers a WHOLE calendar day, which is exactly
70 -- the case that produced the Wed/Thu/Fri inconsistency. It is deliberately
71 -- narrower than "ends on a later date than it starts":
72 --
73 -- Wed 14:00 -> Fri 17:00 covers Thursday whole -> context
74 -- Mon 22:00 -> Tue 06:00 an overnight flight -> stays an event
75 -- Mon 00:00 -> Tue 00:00 a lone all-day marker -> stays an event
76 -- Mon 00:00 -> Wed 00:00 two whole days -> context
77 --
78 -- Written as "the event is still running at the second midnight after it
79 -- starts". Days are UTC here because the columns are; a context is read per
80 -- local day afterwards, and one migration heuristic answering the zone question
81 -- differently from the reader is a rounding error on a span of days.
82 INSERT INTO contexts (id, user_id, label, kind, starts_on, ends_on, migrated_from_event_id, created_at, updated_at)
83 SELECT
84 lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(6))),
85 e.user_id,
86 e.title,
87 'Other',
88 date(e.start_time),
89 -- The last day INSIDE it. An event ending exactly at midnight does not
90 -- reach into that day, so a second is taken off before the date is read.
91 date(datetime(e.end_time, '-1 second')),
92 e.id,
93 datetime('now'),
94 datetime('now')
95 FROM events e
96 WHERE e.end_time IS NOT NULL
97 AND e.user_id IS NOT NULL
98 AND datetime(e.end_time) >= datetime(date(e.start_time), '+2 days');
99
100 UPDATE events
101 SET converted_to_context_id = (
102 SELECT c.id FROM contexts c WHERE c.migrated_from_event_id = events.id
103 )
104 WHERE id IN (SELECT migrated_from_event_id FROM contexts WHERE migrated_from_event_id IS NOT NULL);
105
106 -- RULING 2: vacation_days becomes contexts, and a run crossing two weekly
107 -- reviews becomes ONE context rather than two adjacent ones.
108 --
109 -- review W1: [.. Thu Fri Sat Sun]
110 -- review W2: [Mon Tue .. ] -> one context, Thu..Tue
111 --
112 -- The split is an artifact of storing a per-week projection; nobody recording a
113 -- nine-day holiday meant two holidays. Gaps-and-islands: number the days in
114 -- order, subtract the row number from the date, and every consecutive run
115 -- collapses to one constant.
116 --
117 -- `vacation_days` itself is NOT dropped here. Dropping it is sync-visible and
118 -- the version gate that makes a mixed-version sync safe (ruling 3) lives in the
119 -- SyncKit SDK and does not exist yet, so the column stays as a legacy mirror
120 -- that new code no longer writes. Dropping it is its own migration, after the
121 -- gate.
122 INSERT INTO contexts (id, user_id, label, kind, starts_on, ends_on, created_at, updated_at)
123 WITH RECURSIVE offsets(i) AS (
124 SELECT 0 UNION ALL SELECT i + 1 FROM offsets WHERE i < 6
125 ),
126 marked AS (
127 SELECT DISTINCT
128 wr.user_id AS user_id,
129 date(wr.week_start_date, '+' || o.i || ' days') AS day
130 FROM weekly_reviews wr
131 JOIN offsets o
132 WHERE wr.vacation_days <> ''
133 AND wr.user_id IS NOT NULL
134 -- The stored form is comma-separated weekday indices ("0,1,4"). Padding
135 -- both sides is what stops "1" matching inside "11"; there are only seven
136 -- indices, but the padding is what makes that irrelevant rather than
137 -- lucky.
138 AND ',' || wr.vacation_days || ',' LIKE '%,' || o.i || ',%'
139 ),
140 islands AS (
141 SELECT
142 user_id,
143 day,
144 date(day, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) || ' days') AS run
145 FROM marked
146 )
147 SELECT
148 lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(6))),
149 user_id,
150 'Vacation',
151 'Vacation',
152 MIN(day),
153 MAX(day),
154 datetime('now'),
155 datetime('now')
156 FROM islands
157 GROUP BY user_id, run;
158