archive.rs1//! The archive, kept where every isolate sees the same rows: one Durable 2//! Object with a SQL table of every request that was answered, and one of 3//! the questions people asked. The messages are `archive`'s. 4//! 5//! The same request is never sent to the same model twice (the user, 6//! 2026-10-02), unless a visitor asks for it to be (`Pick::Fresh`, the page's 7//! ↻), and then every response it got is kept, numbered. Three things make 8//! that hold: 9//! 10//! - `versions` has `(sent_to, request, version)` as its primary key: the 11//! exact body, where it went, and which time. No hash stands in for the 12//! body, so two different requests cannot be taken for one. 13//! - A call is looked up before anything is spent, and its response is kept 14//! before anyone is told. 15//! - The archive makes the call itself. Identical asks that arrive while it 16//! is on the wire wait on that one call (`running`) and are told what it 17//! was told. The call is handed to `wait_until`, so it finishes and is 18//! kept even if every visitor waiting on it has left. 19//! 20//! A call with no response (an error, a timeout) keeps nothing: there is 21//! nothing to return next time, so the next ask sends it. 22 23use std::cell::RefCell; 24use std::collections::HashMap; 25use std::rc::Rc; 26 27use archive::{Unsaid, Answer, Ask, Asker, Called, Cursor, Entry, Pick, Place, Rating, Seen, Vote, FEED, Fetch, Home, Live, MOST, Pot, Record, Stats, Told}; 28use ask::{Outcome, Wanted}; 29use budget::Which; 30use futures_util::FutureExt; 31use futures_util::future::{LocalBoxFuture, Shared}; 32use jev_client::Runtime; 33use jev_worker::WorkerRuntime; 34use llm::Model; 35use serde::Deserialize; 36use worker::{DurableObject, Env, Request, Response, SqlStorage, SqlStorageValue, State, WebSocket, WebSocketIncomingMessage, WebSocketPair, console_error, durable_object}; 37 38use crate::meter::{self, Refused}; 39use crate::past::Past; 40use crate::{NoJev, ai}; 41 42/// The binding's name in `wrangler.toml`. 43const BINDING: &str = "ARCHIVE"; 44/// One object for the whole Worker: a request anyone had answered is 45/// answered for everyone. 46const NAME: &str = ::archive::OBJECT; 47 48/// The schema, as the steps that made it, in order. A step runs once: the 49/// object records how many it has run (`migrated`) and runs the rest when it 50/// starts. Never edit a step that has shipped; add one. 51/// 52/// `calls.sent_to` is Jev's endpoint or a Workers AI model id. (A Jev body 53/// names its own model, so the endpoint is enough.) 54const MIGRATIONS: [&[&str]; 21] = [ 55 // Written `IF NOT EXISTS` because this step first shipped before 56 // `migrated` existed, so it meets its own tables on an older object. 57 &[ 58 "CREATE TABLE IF NOT EXISTS calls ( 59 sent_to TEXT NOT NULL, 60 request TEXT NOT NULL, 61 response TEXT NOT NULL, 62 request_id TEXT, 63 attempts INTEGER NOT NULL, 64 took_ms REAL NOT NULL, 65 answered_ms REAL NOT NULL, 66 PRIMARY KEY (sent_to, request) 67 )", 68 "CREATE TABLE IF NOT EXISTS asked ( 69 input TEXT PRIMARY KEY, 70 answers TEXT NOT NULL, 71 asked_ms REAL NOT NULL 72 )", 73 "CREATE INDEX IF NOT EXISTS asked_lately ON asked (asked_ms DESC)", 74 ], 75 // How often a question was asked, whether it may be shown, whether it 76 // needed an LLM (every question before this step did), and the tally of 77 // requests sent and not sent. 78 &[ 79 "ALTER TABLE asked ADD COLUMN times INTEGER NOT NULL DEFAULT 1", 80 "ALTER TABLE asked ADD COLUMN listed INTEGER NOT NULL DEFAULT 1", 81 "ALTER TABLE asked ADD COLUMN llm INTEGER NOT NULL DEFAULT 1", 82 "CREATE TABLE counts (name TEXT PRIMARY KEY, n INTEGER NOT NULL)", 83 ], 84 // A correction to the data, not the schema. In the first ten minutes 85 // these counts existed (2026-10-02), nearly everything counted was the 86 // agent load-testing the live site: one question reached "asked 67 87 // times". Counting starts again from here, and the question the agent 88 // invented to test the feed's moderation is removed. 89 &[ 90 "UPDATE asked SET times = 1", 91 "DELETE FROM asked WHERE input = 'is my neighbour John Smith of 12 Elm Street a paedophile?'", 92 "DELETE FROM counts", 93 ], 94 // A second correction (2026-10-02): the agent checked four deploys by 95 // asking the live site this question, which was already kept, so each 96 // check sent nothing and only counted. It had been asked 3 times; the 97 // checks made it 7. Deploys are now checked with `/gate` on new text. 98 &["UPDATE asked SET times = times - 4 WHERE input = 'Is a slot machine a good retirement plan?' AND times >= 5"], 99 // Each browser counts once per question (the owner, 2026-10-02): a 100 // question's `times` goes up only for a browser not in `askers` for it. 101 // Counts from before stay as they were. 102 &["CREATE TABLE askers (input TEXT NOT NULL, who TEXT NOT NULL, PRIMARY KEY (input, who))"], 103 // Votes on Jev's answers (the owner, 2026-10-02): one per browser per 104 // answer, `answer` being the SHA-256 of the answers as kept, so an 105 // answer that changes starts again from no votes. 106 &["CREATE TABLE ratings (input TEXT NOT NULL, answer TEXT NOT NULL, who TEXT NOT NULL, vote INTEGER NOT NULL, PRIMARY KEY (input, answer, who))"], 107 // A request may be sent again when a visitor asks (the owner, 108 // 2026-10-02: the page's ↻), and every response it got is kept: `calls` 109 // becomes `versions`, numbered from 1, and what was kept is version 1. 110 &[ 111 "CREATE TABLE versions ( 112 sent_to TEXT NOT NULL, 113 request TEXT NOT NULL, 114 version INTEGER NOT NULL, 115 response TEXT NOT NULL, 116 request_id TEXT, 117 attempts INTEGER NOT NULL, 118 took_ms REAL NOT NULL, 119 answered_ms REAL NOT NULL, 120 PRIMARY KEY (sent_to, request, version) 121 )", 122 "INSERT INTO versions SELECT sent_to, request, 1, response, request_id, attempts, took_ms, answered_ms FROM calls", 123 "DROP TABLE calls", 124 ], 125 // Nothing is dropped (the owner, 2026-10-03): every event is kept whole 126 // in `events`, whose columns after `at_ms` are `archive::Event::COLUMNS` 127 // (a test holds the two together). And the owner may overrule the rules 128 // on what the feed shows: `moderated` is their say, NULL while they have 129 // none, and `listed` stays Jev's. 130 &[ 131 "CREATE TABLE events ( 132 id INTEGER PRIMARY KEY AUTOINCREMENT, 133 at_ms REAL NOT NULL, 134 what TEXT NOT NULL, 135 method TEXT NOT NULL, 136 host TEXT NOT NULL, 137 path TEXT NOT NULL, 138 query TEXT NOT NULL, 139 input TEXT NOT NULL, 140 detail TEXT NOT NULL, 141 status REAL NOT NULL, 142 sent REAL NOT NULL, 143 kept REAL NOT NULL, 144 llm REAL NOT NULL, 145 took_ms REAL NOT NULL, 146 first REAL NOT NULL, 147 daily REAL NOT NULL, 148 session REAL NOT NULL, 149 referrer TEXT NOT NULL, 150 source TEXT NOT NULL, 151 client TEXT NOT NULL, 152 family TEXT NOT NULL, 153 os TEXT NOT NULL, 154 device TEXT NOT NULL, 155 language TEXT NOT NULL, 156 agent TEXT NOT NULL, 157 browser TEXT NOT NULL, 158 ip TEXT NOT NULL, 159 country TEXT NOT NULL, 160 region TEXT NOT NULL, 161 city TEXT NOT NULL, 162 postcode TEXT NOT NULL, 163 timezone TEXT NOT NULL, 164 latitude REAL, 165 longitude REAL, 166 asn REAL, 167 network TEXT NOT NULL, 168 colo TEXT NOT NULL, 169 protocol TEXT NOT NULL 170 )", 171 "CREATE INDEX events_lately ON events (at_ms DESC)", 172 "CREATE INDEX events_browser ON events (browser, at_ms)", 173 "CREATE INDEX events_ip ON events (ip, at_ms)", 174 "ALTER TABLE asked ADD COLUMN moderated INTEGER", 175 ], 176 // What only the page can say (the owner, 2026-10-03, of what an 177 // analytics product would have): the link's other campaign tags, the 178 // screen and window, and how far down a page was seen. `read` and `out` 179 // events carry them (`POST /seen`). 180 &[ 181 "ALTER TABLE events ADD COLUMN medium TEXT NOT NULL DEFAULT ''", 182 "ALTER TABLE events ADD COLUMN campaign TEXT NOT NULL DEFAULT ''", 183 "ALTER TABLE events ADD COLUMN screen TEXT NOT NULL DEFAULT ''", 184 "ALTER TABLE events ADD COLUMN viewport TEXT NOT NULL DEFAULT ''", 185 "ALTER TABLE events ADD COLUMN scroll REAL NOT NULL DEFAULT 0", 186 ], 187 // `who` is a hash of a browser's id and the question, so it is 188 // different on every row and says nothing to someone reading the table 189 // (the owner, 2026-10-03: "this is the same guy over and over"). The 190 // browser's id is kept beside it. `who` stays the key: it is still what 191 // counts a browser once per question. Rows from before are filled in 192 // from `events` where they can be (`ASKERS_NAMED`). 193 &["ALTER TABLE askers ADD COLUMN browser TEXT NOT NULL DEFAULT ''", "ALTER TABLE ratings ADD COLUMN browser TEXT NOT NULL DEFAULT ''"], 194 // The feeds in the order they are read, so a page of one is the rows it 195 // shows and not the whole table sorted: on 2026-10-03 the archive read 196 // three million rows by mid-morning, of five a day, and every home page 197 // was a scan of every question. (The old `asked_lately` has only 198 // `asked_ms`, and the feed's order breaks ties by `input`.) 199 &["CREATE INDEX asked_feed ON asked (asked_ms DESC, input DESC)", "CREATE INDEX asked_most ON asked (times DESC, asked_ms DESC)"], 200 // Views for the owner's backend to drill into (the owner, 2026-10-03: 201 // "i like pushing logic down into the database"). Each tree is two 202 // views. `page_hits` is every event once for its page and once for each 203 // folder above it, so `/lmjtfy.git/apps/x` is a hit on `/lmjtfy.git`, 204 // on `/lmjtfy.git/apps` and on itself; `pages` adds those up, so 205 // `WHERE parent IS NULL` is the top of the site and `WHERE parent = 206 // '/lmjtfy.git'` is what is under it. `place_hits` and `places` are the 207 // same for country, region, city and network. A `_hits` view keeps 208 // `at_ms`, and a condition on it reaches the index on `events`; the 209 // added-up views are of all time. A path is split with `json_each`, 210 // since SQLite has no function for it and an object's SQLite takes none 211 // of our own. 212 &[r#"CREATE VIEW page_hits AS 213SELECT h.event, h.at_ms, h.parent, h.page, CASE WHEN h.name = '' THEN '/' ELSE h.name END AS name, h.depth, 214 (h.page = h.path OR h.page || '/' = h.path) AS landed, h.what, h.client, h.status, h.browser, h.ip, h.country 215FROM ( 216 SELECT e.id AS event, e.at_ms, e.path, s.key AS depth, s.value AS name, 217 CASE WHEN s.key > 1 THEN (SELECT group_concat(p.value, '/' ORDER BY p.key) FROM json_each('[' || replace(json_quote(e.path), '/', '","') || ']') p WHERE p.key < s.key) END AS parent, 218 (SELECT group_concat(p.value, '/' ORDER BY p.key) FROM json_each('[' || replace(json_quote(e.path), '/', '","') || ']') p WHERE p.key <= s.key) AS page, 219 e.what, e.client, e.status, e.browser, e.ip, e.country 220 FROM events e, json_each('[' || replace(json_quote(e.path), '/', '","') || ']') s 221 WHERE s.key > 0 AND (s.value != '' OR s.key = 1) 222) h"#, 223 r#"CREATE VIEW pages AS 224SELECT parent, page, MAX(name) AS name, MAX(depth) AS depth, MAX(NOT landed) AS has_more, 225 SUM(client = 'browser' AND status < 400) AS views, 226 COUNT(DISTINCT CASE WHEN client = 'browser' AND browser != '' THEN browser END) AS browsers, 227 SUM(client = 'bot') AS by_bots, SUM(status >= 400) AS not_found, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms 228FROM page_hits WHERE what = 'view' GROUP BY parent, page"#, 229 r#"CREATE VIEW place_hits AS 230SELECT id AS event, at_ms, NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, what, client, status, browser, ip, path FROM events WHERE country != '' 231UNION ALL 232SELECT id, at_ms, country, country || ' / ' || COALESCE(NULLIF(region, ''), '?'), COALESCE(NULLIF(region, ''), '?'), 'region', 2, what, client, status, browser, ip, path FROM events WHERE country != '' 233UNION ALL 234SELECT id, at_ms, country || ' / ' || COALESCE(NULLIF(region, ''), '?'), country || ' / ' || COALESCE(NULLIF(region, ''), '?') || ' / ' || COALESCE(NULLIF(city, ''), '?'), COALESCE(NULLIF(city, ''), '?'), 'city', 3, what, client, status, browser, ip, path FROM events WHERE country != '' 235UNION ALL 236SELECT id, at_ms, country || ' / ' || COALESCE(NULLIF(region, ''), '?') || ' / ' || COALESCE(NULLIF(city, ''), '?'), country || ' / ' || COALESCE(NULLIF(region, ''), '?') || ' / ' || COALESCE(NULLIF(city, ''), '?') || ' / ' || COALESCE(NULLIF(network, ''), '?'), COALESCE(NULLIF(network, ''), '?'), 'network', 4, what, client, status, browser, ip, path FROM events WHERE country != ''"#, 237 r#"CREATE VIEW places AS 238SELECT parent, place, MAX(name) AS name, MAX(kind) AS kind, MAX(depth) < 4 AS has_more, COUNT(*) AS events, 239 SUM(what = 'view' AND client = 'browser' AND status < 400) AS views, 240 COUNT(DISTINCT CASE WHEN client = 'browser' AND browser != '' THEN browser END) AS browsers, 241 COUNT(DISTINCT ip) AS addresses, SUM(client = 'bot') AS by_bots, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms 242FROM place_hits GROUP BY parent, place"#], 243 // `pages` and `places` again, a tenth of the cost. Read through 244 // `page_hits`, `pages` split every event's path, and one query read 245 // 2,523,507 rows of 57,000 events (the rows `json_each` makes are 246 // counted). Most events are the same page from the same browser, so 247 // they are counted first and each different one is split once: 327,936 248 // rows for the same answer, and 115,738 for `places` where it was 249 // 285,203 (measured on the live archive, 2026-10-03). `page_hits` and 250 // `place_hits` stay as they were: a condition on `at_ms` reaches the 251 // index through them, and would not through a window. 252 &["DROP VIEW pages", "DROP VIEW places", 253 r#"CREATE VIEW pages AS 254SELECT parent, page, MAX(CASE WHEN name = '' THEN '/' ELSE name END) AS name, MAX(depth) AS depth, MAX(NOT (page = path OR page || '/' = path)) AS has_more, 255 SUM(CASE WHEN client = 'browser' AND NOT missing THEN n ELSE 0 END) AS views, 256 COUNT(DISTINCT CASE WHEN client = 'browser' AND browser != '' THEN browser END) AS browsers, 257 SUM(CASE WHEN client = 'bot' THEN n ELSE 0 END) AS by_bots, SUM(CASE WHEN missing THEN n ELSE 0 END) AS not_found, 258 MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms 259FROM ( 260 SELECT g.path, g.client, g.missing, g.browser, g.n, g.first_ms, g.last_ms, s.key AS depth, s.value AS name, 261 group_concat(s.value, '/') OVER w AS page, 262 CASE WHEN s.key > 1 THEN group_concat(s.value, '/') OVER (PARTITION BY g.path, g.client, g.missing, g.browser ORDER BY s.key ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END AS parent 263 FROM (SELECT path, client, status >= 400 AS missing, browser, COUNT(*) AS n, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms FROM events WHERE what = 'view' GROUP BY path, client, missing, browser) g, 264 json_each('[' || replace(json_quote(g.path), '/', '","') || ']') s 265 WINDOW w AS (PARTITION BY g.path, g.client, g.missing, g.browser ORDER BY s.key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 266) WHERE depth > 0 AND (name != '' OR depth = 1) GROUP BY parent, page"#, 267 r#"CREATE VIEW places AS 268WITH g AS ( 269 SELECT country, COALESCE(NULLIF(region, ''), '?') AS region, COALESCE(NULLIF(city, ''), '?') AS city, COALESCE(NULLIF(network, ''), '?') AS network, 270 CASE WHEN client = 'browser' THEN browser ELSE '' END AS browser, ip, client = 'bot' AS bot, 271 COUNT(*) AS n, SUM(what = 'view' AND client = 'browser' AND status < 400) AS views, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms 272 FROM events WHERE country != '' GROUP BY 1, 2, 3, 4, 5, 6, 7 273), t AS ( 274 SELECT NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, browser, ip, bot, n, views, first_ms, last_ms FROM g 275 UNION ALL SELECT country, country || ' / ' || region, region, 'region', 2, browser, ip, bot, n, views, first_ms, last_ms FROM g 276 UNION ALL SELECT country || ' / ' || region, country || ' / ' || region || ' / ' || city, city, 'city', 3, browser, ip, bot, n, views, first_ms, last_ms FROM g 277 UNION ALL SELECT country || ' / ' || region || ' / ' || city, country || ' / ' || region || ' / ' || city || ' / ' || network, network, 'network', 4, browser, ip, bot, n, views, first_ms, last_ms FROM g 278) 279SELECT parent, place, MAX(name) AS name, MAX(kind) AS kind, MAX(depth) < 4 AS has_more, SUM(n) AS events, SUM(views) AS views, 280 COUNT(DISTINCT NULLIF(browser, '')) AS browsers, COUNT(DISTINCT ip) AS addresses, SUM(CASE WHEN bot THEN n ELSE 0 END) AS by_bots, 281 MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms 282FROM t GROUP BY parent, place"#], 283 // What Cloudflare says of a request that was being thrown away: the 284 // region's code (the owner, 2026-10-03, wants a map of every level, and 285 // a region's outline is found by its code, not its name), the 286 // continent, the metro area, and what kind of bot it has verified the 287 // request as from. Then `place_hits` and `places` again, with a place's 288 // code and where it is: a region's code, and the middle of where its 289 // visitors were, which is where a city is drawn. 290 &["ALTER TABLE events ADD COLUMN region_code TEXT NOT NULL DEFAULT ''", "ALTER TABLE events ADD COLUMN continent TEXT NOT NULL DEFAULT ''", 291 "ALTER TABLE events ADD COLUMN metro TEXT NOT NULL DEFAULT ''", "ALTER TABLE events ADD COLUMN bot TEXT NOT NULL DEFAULT ''", 292 "DROP VIEW places", "DROP VIEW place_hits", 293 r#"CREATE VIEW place_hits AS 294SELECT id AS event, at_ms, NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, country AS code, latitude, longitude, what, client, status, browser, ip, path FROM events WHERE country != '' 295UNION ALL 296SELECT id, at_ms, country, country || ' / ' || COALESCE(NULLIF(region, ''), '?'), COALESCE(NULLIF(region, ''), '?'), 'region', 2, region_code, latitude, longitude, what, client, status, browser, ip, path FROM events WHERE country != '' 297UNION ALL 298SELECT id, at_ms, country || ' / ' || COALESCE(NULLIF(region, ''), '?'), country || ' / ' || COALESCE(NULLIF(region, ''), '?') || ' / ' || COALESCE(NULLIF(city, ''), '?'), COALESCE(NULLIF(city, ''), '?'), 'city', 3, '', latitude, longitude, what, client, status, browser, ip, path FROM events WHERE country != '' 299UNION ALL 300SELECT id, at_ms, country || ' / ' || COALESCE(NULLIF(region, ''), '?') || ' / ' || COALESCE(NULLIF(city, ''), '?'), country || ' / ' || COALESCE(NULLIF(region, ''), '?') || ' / ' || COALESCE(NULLIF(city, ''), '?') || ' / ' || COALESCE(NULLIF(network, ''), '?'), COALESCE(NULLIF(network, ''), '?'), 'network', 4, '', latitude, longitude, what, client, status, browser, ip, path FROM events WHERE country != ''"#, 301 r#"CREATE VIEW places AS 302WITH g AS ( 303 SELECT country, COALESCE(NULLIF(region, ''), '?') AS region, COALESCE(NULLIF(city, ''), '?') AS city, COALESCE(NULLIF(network, ''), '?') AS network, 304 CASE WHEN client = 'browser' THEN browser ELSE '' END AS browser, ip, client = 'bot' AS bot, MAX(region_code) AS region_code, 305 COUNT(*) AS n, SUM(what = 'view' AND client = 'browser' AND status < 400) AS views, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms, 306 SUM(latitude) AS latitudes, SUM(longitude) AS longitudes, COUNT(latitude) AS located 307 FROM events WHERE country != '' GROUP BY 1, 2, 3, 4, 5, 6, 7 308), t AS ( 309 SELECT NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, country AS code, browser, ip, bot, n, views, first_ms, last_ms, latitudes, longitudes, located FROM g 310 UNION ALL SELECT country, country || ' / ' || region, region, 'region', 2, region_code, browser, ip, bot, n, views, first_ms, last_ms, latitudes, longitudes, located FROM g 311 UNION ALL SELECT country || ' / ' || region, country || ' / ' || region || ' / ' || city, city, 'city', 3, '', browser, ip, bot, n, views, first_ms, last_ms, latitudes, longitudes, located FROM g 312 UNION ALL SELECT country || ' / ' || region || ' / ' || city, country || ' / ' || region || ' / ' || city || ' / ' || network, network, 'network', 4, '', browser, ip, bot, n, views, first_ms, last_ms, latitudes, longitudes, located FROM g 313) 314SELECT parent, place, MAX(name) AS name, MAX(kind) AS kind, MAX(depth) < 4 AS has_more, SUM(n) AS events, SUM(views) AS views, 315 COUNT(DISTINCT NULLIF(browser, '')) AS browsers, COUNT(DISTINCT ip) AS addresses, SUM(CASE WHEN bot THEN n ELSE 0 END) AS by_bots, 316 MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms, MAX(code) AS code, 317 ROUND(SUM(latitudes) / SUM(located), 3) AS latitude, ROUND(SUM(longitudes) / SUM(located), 3) AS longitude 318FROM t GROUP BY parent, place"#], 319 // The owner's backend asked `events` the same questions on every page, 320 // from SQL written in Rust (the owner, 2026-10-04: port them into the 321 // database "as composable functions"). SQLite has no functions of our 322 // own and no stored views, so each question is a view with a `day` to 323 // ask it of, and a day that is over is copied into a table beside it 324 // once (`Shelf::finish`): never worked out again, since it cannot change. 325 // 326 // `events.day` is worked out from `at_ms` and indexed, so a view asked 327 // of one day reads that day's events and no others. `hits` is every 328 // event with what it counts as (a page viewed, an ask, a visit begun), 329 // said once. `hits_unfinished` is the events of the days not copied 330 // yet, and is what every `<name>_live` view reads. `<name>_finished` 331 // holds the days that are over, `finished` says through which day, and 332 // `<name>` is the two together: the one to ask. A `_by_day` view is a 333 // number for each label of each day. Different browsers on two days do 334 // not add up, so beside each such number is a view of who they were 335 // (`country_visitors`), to count a stretch of days from. 336 &[ 337 r#"ALTER TABLE events ADD COLUMN day INTEGER GENERATED ALWAYS AS (CAST(at_ms / 86400000 AS INTEGER)) VIRTUAL"#, 338 r#"CREATE INDEX events_day ON events (day)"#, 339 r#"CREATE TABLE finished (name TEXT PRIMARY KEY, through INTEGER NOT NULL) WITHOUT ROWID"#, 340 r#"CREATE VIEW hits AS 341SELECT id AS event, at_ms, day, CAST(at_ms / 3600000 AS INTEGER) AS hour, what, detail, path, input, client, status, browser, ip, 342 CASE WHEN browser = '' THEN ip ELSE browser END AS visitor, 343 (what = 'view' AND client = 'browser' AND status < 400) AS viewed, daily AS visited, session AS began, first AS arrived, 344 (what = 'gate') AS typed, (what = 'answer') AS asked, (what = 'answer' AND detail = 'answered') AS answered, (what = 'vote') AS voted, (what = 'fetch') AS fetched, 345 CASE WHEN what = 'read' THEN took_ms ELSE 0 END AS read_ms, 346 CASE WHEN what = 'view' AND referrer != '' AND referrer NOT LIKE 'https://lmjtfy.fun/%' THEN CASE WHEN instr(substr(referrer, instr(referrer, '://') + 3), '/') > 0 THEN substr(substr(referrer, instr(referrer, '://') + 3), 1, instr(substr(referrer, instr(referrer, '://') + 3), '/') - 1) ELSE substr(referrer, instr(referrer, '://') + 3) END ELSE '' END AS came_from, 347 country, region, city, network, family || ' on ' || os AS agent_kind 348FROM events"#, 349 r#"CREATE VIEW hits_unfinished AS 350SELECT * FROM hits WHERE day > (SELECT MIN(through) FROM finished)"#, 351 r#"CREATE VIEW hours_live AS 352SELECT day, hour, SUM(viewed) AS views, SUM(visited) AS visitors, SUM(began) AS sessions, SUM(arrived) AS new, SUM(asked) AS asks, SUM(answered) AS answered, SUM(voted) AS votes, SUM(fetched) AS fetches, SUM(read_ms) AS read_ms FROM hits_unfinished GROUP BY day, hour"#, 353 r#"CREATE TABLE hours_finished (day INTEGER NOT NULL, hour INTEGER NOT NULL, views, visitors, sessions, new, asks, answered, votes, fetches, read_ms, PRIMARY KEY (day, hour)) WITHOUT ROWID"#, 354 r#"INSERT INTO finished (name, through) VALUES ('hours', -1)"#, 355 r#"CREATE VIEW hours AS 356SELECT day, hour, views, visitors, sessions, new, asks, answered, votes, fetches, read_ms FROM hours_finished UNION ALL SELECT day, hour, views, visitors, sessions, new, asks, answered, votes, fetches, read_ms FROM hours_live WHERE day > (SELECT through FROM finished WHERE name = 'hours')"#, 357 r#"CREATE VIEW browser_days_live AS 358SELECT day, browser, MAX(what = 'view') AS viewed, MAX(typed) AS typed, MAX(asked) AS asked, MAX(voted) AS voted, MAX(arrived) AS first, MAX(client = 'browser') AS seen FROM hits_unfinished WHERE browser != '' GROUP BY day, browser"#, 359 r#"CREATE TABLE browser_days_finished (day INTEGER NOT NULL, browser TEXT NOT NULL, viewed, typed, asked, voted, first, seen, PRIMARY KEY (day, browser)) WITHOUT ROWID"#, 360 r#"INSERT INTO finished (name, through) VALUES ('browser_days', -1)"#, 361 r#"CREATE VIEW browser_days AS 362SELECT day, browser, viewed, typed, asked, voted, first, seen FROM browser_days_finished UNION ALL SELECT day, browser, viewed, typed, asked, voted, first, seen FROM browser_days_live WHERE day > (SELECT through FROM finished WHERE name = 'browser_days')"#, 363 r#"CREATE VIEW outcomes_by_day_live AS 364SELECT day, what AS label, COUNT(*) AS n FROM hits_unfinished GROUP BY day, label"#, 365 r#"CREATE TABLE outcomes_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 366 r#"INSERT INTO finished (name, through) VALUES ('outcomes_by_day', -1)"#, 367 r#"CREATE VIEW outcomes_by_day AS 368SELECT day, label, n FROM outcomes_by_day_finished UNION ALL SELECT day, label, n FROM outcomes_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'outcomes_by_day')"#, 369 r#"CREATE VIEW ask_endings_by_day_live AS 370SELECT day, detail AS label, COUNT(*) AS n FROM hits_unfinished WHERE asked GROUP BY day, label"#, 371 r#"CREATE TABLE ask_endings_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 372 r#"INSERT INTO finished (name, through) VALUES ('ask_endings_by_day', -1)"#, 373 r#"CREATE VIEW ask_endings_by_day AS 374SELECT day, label, n FROM ask_endings_by_day_finished UNION ALL SELECT day, label, n FROM ask_endings_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'ask_endings_by_day')"#, 375 r#"CREATE VIEW pages_by_day_live AS 376SELECT day, path AS label, COUNT(*) AS n FROM hits_unfinished WHERE viewed GROUP BY day, label"#, 377 r#"CREATE TABLE pages_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 378 r#"INSERT INTO finished (name, through) VALUES ('pages_by_day', -1)"#, 379 r#"CREATE VIEW pages_by_day AS 380SELECT day, label, n FROM pages_by_day_finished UNION ALL SELECT day, label, n FROM pages_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'pages_by_day')"#, 381 r#"CREATE VIEW questions_by_day_live AS 382SELECT day, input AS label, COUNT(*) AS n FROM hits_unfinished WHERE asked AND input != '' GROUP BY day, label"#, 383 r#"CREATE TABLE questions_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 384 r#"INSERT INTO finished (name, through) VALUES ('questions_by_day', -1)"#, 385 r#"CREATE VIEW questions_by_day AS 386SELECT day, label, n FROM questions_by_day_finished UNION ALL SELECT day, label, n FROM questions_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'questions_by_day')"#, 387 r#"CREATE VIEW referrers_by_day_live AS 388SELECT day, came_from AS label, COUNT(*) AS n FROM hits_unfinished WHERE came_from != '' GROUP BY day, label"#, 389 r#"CREATE TABLE referrers_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 390 r#"INSERT INTO finished (name, through) VALUES ('referrers_by_day', -1)"#, 391 r#"CREATE VIEW referrers_by_day AS 392SELECT day, label, n FROM referrers_by_day_finished UNION ALL SELECT day, label, n FROM referrers_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'referrers_by_day')"#, 393 r#"CREATE VIEW countries_by_day_live AS 394SELECT day, country AS label, COUNT(DISTINCT visitor) AS n FROM hits_unfinished WHERE client = 'browser' GROUP BY day, label"#, 395 r#"CREATE TABLE countries_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 396 r#"INSERT INTO finished (name, through) VALUES ('countries_by_day', -1)"#, 397 r#"CREATE VIEW countries_by_day AS 398SELECT day, label, n FROM countries_by_day_finished UNION ALL SELECT day, label, n FROM countries_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'countries_by_day')"#, 399 r#"CREATE VIEW country_visitors_live AS 400SELECT day, country AS label, visitor AS who FROM hits_unfinished WHERE client = 'browser' GROUP BY day, label, who"#, 401 r#"CREATE TABLE country_visitors_finished (day INTEGER NOT NULL, label NOT NULL, who NOT NULL, PRIMARY KEY (day, label, who)) WITHOUT ROWID"#, 402 r#"INSERT INTO finished (name, through) VALUES ('country_visitors', -1)"#, 403 r#"CREATE VIEW country_visitors AS 404SELECT day, label, who FROM country_visitors_finished UNION ALL SELECT day, label, who FROM country_visitors_live WHERE day > (SELECT through FROM finished WHERE name = 'country_visitors')"#, 405 r#"CREATE VIEW cities_by_day_live AS 406SELECT day, city || ', ' || country AS label, COUNT(DISTINCT visitor) AS n FROM hits_unfinished WHERE client = 'browser' AND city != '' GROUP BY day, label"#, 407 r#"CREATE TABLE cities_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 408 r#"INSERT INTO finished (name, through) VALUES ('cities_by_day', -1)"#, 409 r#"CREATE VIEW cities_by_day AS 410SELECT day, label, n FROM cities_by_day_finished UNION ALL SELECT day, label, n FROM cities_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'cities_by_day')"#, 411 r#"CREATE VIEW city_visitors_live AS 412SELECT day, city || ', ' || country AS label, visitor AS who FROM hits_unfinished WHERE client = 'browser' AND city != '' GROUP BY day, label, who"#, 413 r#"CREATE TABLE city_visitors_finished (day INTEGER NOT NULL, label NOT NULL, who NOT NULL, PRIMARY KEY (day, label, who)) WITHOUT ROWID"#, 414 r#"INSERT INTO finished (name, through) VALUES ('city_visitors', -1)"#, 415 r#"CREATE VIEW city_visitors AS 416SELECT day, label, who FROM city_visitors_finished UNION ALL SELECT day, label, who FROM city_visitors_live WHERE day > (SELECT through FROM finished WHERE name = 'city_visitors')"#, 417 r#"CREATE VIEW networks_by_day_live AS 418SELECT day, network AS label, COUNT(DISTINCT visitor) AS n FROM hits_unfinished WHERE client = 'browser' GROUP BY day, label"#, 419 r#"CREATE TABLE networks_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 420 r#"INSERT INTO finished (name, through) VALUES ('networks_by_day', -1)"#, 421 r#"CREATE VIEW networks_by_day AS 422SELECT day, label, n FROM networks_by_day_finished UNION ALL SELECT day, label, n FROM networks_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'networks_by_day')"#, 423 r#"CREATE VIEW network_visitors_live AS 424SELECT day, network AS label, visitor AS who FROM hits_unfinished WHERE client = 'browser' GROUP BY day, label, who"#, 425 r#"CREATE TABLE network_visitors_finished (day INTEGER NOT NULL, label NOT NULL, who NOT NULL, PRIMARY KEY (day, label, who)) WITHOUT ROWID"#, 426 r#"INSERT INTO finished (name, through) VALUES ('network_visitors', -1)"#, 427 r#"CREATE VIEW network_visitors AS 428SELECT day, label, who FROM network_visitors_finished UNION ALL SELECT day, label, who FROM network_visitors_live WHERE day > (SELECT through FROM finished WHERE name = 'network_visitors')"#, 429 r#"CREATE VIEW agents_by_day_live AS 430SELECT day, agent_kind AS label, COUNT(DISTINCT browser) AS n FROM hits_unfinished WHERE client = 'browser' AND browser != '' GROUP BY day, label"#, 431 r#"CREATE TABLE agents_by_day_finished (day INTEGER NOT NULL, label NOT NULL, n, PRIMARY KEY (day, label)) WITHOUT ROWID"#, 432 r#"INSERT INTO finished (name, through) VALUES ('agents_by_day', -1)"#, 433 r#"CREATE VIEW agents_by_day AS 434SELECT day, label, n FROM agents_by_day_finished UNION ALL SELECT day, label, n FROM agents_by_day_live WHERE day > (SELECT through FROM finished WHERE name = 'agents_by_day')"#, 435 r#"CREATE VIEW agent_browsers_live AS 436SELECT day, agent_kind AS label, browser AS who FROM hits_unfinished WHERE client = 'browser' AND browser != '' GROUP BY day, label, who"#, 437 r#"CREATE TABLE agent_browsers_finished (day INTEGER NOT NULL, label NOT NULL, who NOT NULL, PRIMARY KEY (day, label, who)) WITHOUT ROWID"#, 438 r#"INSERT INTO finished (name, through) VALUES ('agent_browsers', -1)"#, 439 r#"CREATE VIEW agent_browsers AS 440SELECT day, label, who FROM agent_browsers_finished UNION ALL SELECT day, label, who FROM agent_browsers_live WHERE day > (SELECT through FROM finished WHERE name = 'agent_browsers')"#, 441 r#"CREATE VIEW days AS 442SELECT day, SUM(views) AS views, SUM(visitors) AS visitors, SUM(sessions) AS sessions, SUM(new) AS new, SUM(asks) AS asks, SUM(answered) AS answered, SUM(votes) AS votes, SUM(fetches) AS fetches, SUM(read_ms) AS read_ms FROM hours GROUP BY day"#, 443 r#"CREATE VIEW today_by_hour AS 444SELECT * FROM hours WHERE day = CAST(unixepoch() / 86400 AS INTEGER)"#, 445 ], 446 // `pages` and `places`, the two trees, by the day as well. They were 447 // still a pass over every event at each click (118,000 rows for a 448 // level of `places` on 2026-10-04, and more each day). A tree's level 449 // for a day is `page_days` and `place_days`; who was there, to count 450 // once over many days, is `page_visitors`, `place_visitors` and 451 // `place_addresses`. `pages` and `places` are those added up, with the 452 // columns they had. A `_finished` table's key cannot be NULL, so the 453 // top of a tree is kept as '' and shown as NULL, which is what 454 // `WHERE parent IS NULL` asks for. `hits` gains what a place needs. 455 // The new views gave the same rows as the old on a copy of the live 456 // archive, both read live and after a day was finished. 457 &[ 458 r#"DROP VIEW pages"#, 459 r#"DROP VIEW places"#, 460 r#"DROP VIEW hits"#, 461 r#"CREATE VIEW hits AS 462SELECT id AS event, at_ms, day, CAST(at_ms / 3600000 AS INTEGER) AS hour, what, detail, path, input, client, status, browser, ip, 463 CASE WHEN browser = '' THEN ip ELSE browser END AS visitor, 464 (what = 'view' AND client = 'browser' AND status < 400) AS viewed, daily AS visited, session AS began, first AS arrived, 465 (what = 'gate') AS typed, (what = 'answer') AS asked, (what = 'answer' AND detail = 'answered') AS answered, (what = 'vote') AS voted, (what = 'fetch') AS fetched, 466 CASE WHEN what = 'read' THEN took_ms ELSE 0 END AS read_ms, 467 CASE WHEN what = 'view' AND referrer != '' AND referrer NOT LIKE 'https://lmjtfy.fun/%' THEN CASE WHEN instr(substr(referrer, instr(referrer, '://') + 3), '/') > 0 THEN substr(substr(referrer, instr(referrer, '://') + 3), 1, instr(substr(referrer, instr(referrer, '://') + 3), '/') - 1) ELSE substr(referrer, instr(referrer, '://') + 3) END ELSE '' END AS came_from, 468 country, region, city, network, family || ' on ' || os AS agent_kind, region_code, latitude, longitude 469FROM events"#, 470 r#"CREATE VIEW page_days_live AS 471SELECT day, COALESCE(parent, '') AS parent, page, MAX(CASE WHEN name = '' THEN '/' ELSE name END) AS name, MAX(depth) AS depth, MAX(NOT (page = path OR page || '/' = path)) AS has_more, 472 SUM(CASE WHEN client = 'browser' AND NOT missing THEN n ELSE 0 END) AS views, SUM(CASE WHEN client = 'bot' THEN n ELSE 0 END) AS by_bots, 473 SUM(CASE WHEN missing THEN n ELSE 0 END) AS not_found, MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms 474FROM ( 475 SELECT g.day, g.path, g.client, g.missing, g.n, g.first_ms, g.last_ms, s.key AS depth, s.value AS name, 476 group_concat(s.value, '/') OVER w AS page, 477 CASE WHEN s.key > 1 THEN group_concat(s.value, '/') OVER (PARTITION BY g.day, g.path, g.client, g.missing ORDER BY s.key ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END AS parent 478 FROM (SELECT day, path, client, status >= 400 AS missing, COUNT(*) AS n, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms FROM hits_unfinished WHERE what = 'view' GROUP BY day, path, client, missing) g, 479 json_each('[' || replace(json_quote(g.path), '/', '","') || ']') s 480 WINDOW w AS (PARTITION BY g.day, g.path, g.client, g.missing ORDER BY s.key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 481) WHERE depth > 0 AND (name != '' OR depth = 1) GROUP BY day, 2, page"#, 482 r#"CREATE TABLE page_days_finished (day INTEGER NOT NULL, parent TEXT NOT NULL, page TEXT NOT NULL, name, depth, has_more, views, by_bots, not_found, first_ms, last_ms, PRIMARY KEY (day, parent, page)) WITHOUT ROWID"#, 483 r#"INSERT INTO finished (name, through) VALUES ('page_days', -1)"#, 484 r#"CREATE VIEW page_days AS 485SELECT day, NULLIF(parent, '') AS parent, page, name, depth, has_more, views, by_bots, not_found, first_ms, last_ms FROM page_days_finished UNION ALL SELECT day, NULLIF(parent, '') AS parent, page, name, depth, has_more, views, by_bots, not_found, first_ms, last_ms FROM page_days_live WHERE day > (SELECT through FROM finished WHERE name = 'page_days')"#, 486 r#"CREATE VIEW page_visitors_live AS 487SELECT day, page, who FROM ( 488 SELECT g.day, g.who, s.key AS depth, s.value AS name, group_concat(s.value, '/') OVER (PARTITION BY g.day, g.path, g.who ORDER BY s.key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS page 489 FROM (SELECT DISTINCT day, path, browser AS who FROM hits_unfinished WHERE what = 'view' AND client = 'browser' AND browser != '') g, 490 json_each('[' || replace(json_quote(g.path), '/', '","') || ']') s 491) WHERE depth > 0 AND (name != '' OR depth = 1) GROUP BY day, page, who"#, 492 r#"CREATE TABLE page_visitors_finished (day INTEGER NOT NULL, page TEXT NOT NULL, who TEXT NOT NULL, PRIMARY KEY (day, page, who)) WITHOUT ROWID"#, 493 r#"INSERT INTO finished (name, through) VALUES ('page_visitors', -1)"#, 494 r#"CREATE VIEW page_visitors AS 495SELECT day, page, who FROM page_visitors_finished UNION ALL SELECT day, page, who FROM page_visitors_live WHERE day > (SELECT through FROM finished WHERE name = 'page_visitors')"#, 496 r#"CREATE VIEW place_days_live AS 497WITH g AS ( 498 SELECT day, country, COALESCE(NULLIF(region, ''), '?') AS region, COALESCE(NULLIF(city, ''), '?') AS city, COALESCE(NULLIF(network, ''), '?') AS network, MAX(region_code) AS region_code, COUNT(*) AS n, SUM(viewed) AS views, SUM(client = 'bot') AS by_bots, MIN(at_ms) AS first_ms, MAX(at_ms) AS last_ms, 499 SUM(latitude) AS latitudes, SUM(longitude) AS longitudes, COUNT(latitude) AS located 500 FROM hits_unfinished WHERE country != '' GROUP BY 1, 2, 3, 4, 5 501), t AS ( 502 SELECT day, NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, country AS code, n, views, by_bots, first_ms, last_ms, latitudes, longitudes, located FROM g 503 UNION ALL SELECT day, country, country || ' / ' || region, region, 'region', 2, region_code, n, views, by_bots, first_ms, last_ms, latitudes, longitudes, located FROM g 504 UNION ALL SELECT day, country || ' / ' || region, country || ' / ' || region || ' / ' || city, city, 'city', 3, '', n, views, by_bots, first_ms, last_ms, latitudes, longitudes, located FROM g 505 UNION ALL SELECT day, country || ' / ' || region || ' / ' || city, country || ' / ' || region || ' / ' || city || ' / ' || network, network, 'network', 4, '', n, views, by_bots, first_ms, last_ms, latitudes, longitudes, located FROM g 506) 507SELECT day, COALESCE(parent, '') AS parent, place, MAX(name) AS name, MAX(kind) AS kind, MAX(depth) AS depth, SUM(n) AS events, SUM(views) AS views, SUM(by_bots) AS by_bots, 508 MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms, MAX(code) AS code, SUM(latitudes) AS latitudes, SUM(longitudes) AS longitudes, SUM(located) AS located 509FROM t GROUP BY day, 2, place"#, 510 r#"CREATE TABLE place_days_finished (day INTEGER NOT NULL, parent TEXT NOT NULL, place TEXT NOT NULL, name, kind, depth, events, views, by_bots, first_ms, last_ms, code, latitudes, longitudes, located, PRIMARY KEY (day, parent, place)) WITHOUT ROWID"#, 511 r#"INSERT INTO finished (name, through) VALUES ('place_days', -1)"#, 512 r#"CREATE VIEW place_days AS 513SELECT day, NULLIF(parent, '') AS parent, place, name, kind, depth, events, views, by_bots, first_ms, last_ms, code, latitudes, longitudes, located FROM place_days_finished UNION ALL SELECT day, NULLIF(parent, '') AS parent, place, name, kind, depth, events, views, by_bots, first_ms, last_ms, code, latitudes, longitudes, located FROM place_days_live WHERE day > (SELECT through FROM finished WHERE name = 'place_days')"#, 514 r#"CREATE VIEW place_visitors_live AS 515WITH g AS ( 516 SELECT DISTINCT day, country, COALESCE(NULLIF(region, ''), '?') AS region, COALESCE(NULLIF(city, ''), '?') AS city, COALESCE(NULLIF(network, ''), '?') AS network, '' AS region_code, browser AS who FROM hits_unfinished WHERE country != '' AND client = 'browser' AND browser != '' 517), t AS ( 518 SELECT day, NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, country AS code, who FROM g 519 UNION ALL SELECT day, country, country || ' / ' || region, region, 'region', 2, region_code, who FROM g 520 UNION ALL SELECT day, country || ' / ' || region, country || ' / ' || region || ' / ' || city, city, 'city', 3, '', who FROM g 521 UNION ALL SELECT day, country || ' / ' || region || ' / ' || city, country || ' / ' || region || ' / ' || city || ' / ' || network, network, 'network', 4, '', who FROM g 522) 523SELECT day, place, who FROM t GROUP BY day, place, who"#, 524 r#"CREATE TABLE place_visitors_finished (day INTEGER NOT NULL, place TEXT NOT NULL, who TEXT NOT NULL, PRIMARY KEY (day, place, who)) WITHOUT ROWID"#, 525 r#"INSERT INTO finished (name, through) VALUES ('place_visitors', -1)"#, 526 r#"CREATE VIEW place_visitors AS 527SELECT day, place, who FROM place_visitors_finished UNION ALL SELECT day, place, who FROM place_visitors_live WHERE day > (SELECT through FROM finished WHERE name = 'place_visitors')"#, 528 r#"CREATE VIEW place_addresses_live AS 529WITH g AS ( 530 SELECT DISTINCT day, country, COALESCE(NULLIF(region, ''), '?') AS region, COALESCE(NULLIF(city, ''), '?') AS city, COALESCE(NULLIF(network, ''), '?') AS network, '' AS region_code, ip AS who FROM hits_unfinished WHERE country != '' 531), t AS ( 532 SELECT day, NULL AS parent, country AS place, country AS name, 'country' AS kind, 1 AS depth, country AS code, who FROM g 533 UNION ALL SELECT day, country, country || ' / ' || region, region, 'region', 2, region_code, who FROM g 534 UNION ALL SELECT day, country || ' / ' || region, country || ' / ' || region || ' / ' || city, city, 'city', 3, '', who FROM g 535 UNION ALL SELECT day, country || ' / ' || region || ' / ' || city, country || ' / ' || region || ' / ' || city || ' / ' || network, network, 'network', 4, '', who FROM g 536) 537SELECT day, place, who FROM t GROUP BY day, place, who"#, 538 r#"CREATE TABLE place_addresses_finished (day INTEGER NOT NULL, place TEXT NOT NULL, who TEXT NOT NULL, PRIMARY KEY (day, place, who)) WITHOUT ROWID"#, 539 r#"INSERT INTO finished (name, through) VALUES ('place_addresses', -1)"#, 540 r#"CREATE VIEW place_addresses AS 541SELECT day, place, who FROM place_addresses_finished UNION ALL SELECT day, place, who FROM place_addresses_live WHERE day > (SELECT through FROM finished WHERE name = 'place_addresses')"#, 542 r#"CREATE VIEW pages AS 543SELECT d.parent, d.page, d.name, d.depth, d.has_more, d.views, COALESCE(v.browsers, 0) AS browsers, d.by_bots, d.not_found, d.first_ms, d.last_ms 544FROM (SELECT parent, page, MAX(name) AS name, MAX(depth) AS depth, MAX(has_more) AS has_more, SUM(views) AS views, SUM(by_bots) AS by_bots, SUM(not_found) AS not_found, 545 MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms FROM page_days GROUP BY parent, page) d 546LEFT JOIN (SELECT page, COUNT(DISTINCT who) AS browsers FROM page_visitors GROUP BY page) v ON v.page = d.page"#, 547 r#"CREATE VIEW places AS 548SELECT d.parent, d.place, d.name, d.kind, d.depth < 4 AS has_more, d.events, d.views, COALESCE(v.browsers, 0) AS browsers, COALESCE(a.addresses, 0) AS addresses, d.by_bots, 549 d.first_ms, d.last_ms, d.code, ROUND(d.latitudes / d.located, 3) AS latitude, ROUND(d.longitudes / d.located, 3) AS longitude 550FROM (SELECT parent, place, MAX(name) AS name, MAX(kind) AS kind, MAX(depth) AS depth, SUM(events) AS events, SUM(views) AS views, SUM(by_bots) AS by_bots, 551 MIN(first_ms) AS first_ms, MAX(last_ms) AS last_ms, MAX(code) AS code, SUM(latitudes) AS latitudes, SUM(longitudes) AS longitudes, SUM(located) AS located 552 FROM place_days GROUP BY parent, place) d 553LEFT JOIN (SELECT place, COUNT(DISTINCT who) AS browsers FROM place_visitors GROUP BY place) v ON v.place = d.place 554LEFT JOIN (SELECT place, COUNT(DISTINCT who) AS addresses FROM place_addresses GROUP BY place) a ON a.place = d.place"#, 555 ], 556 // What is kept is not changed (the owner, 2026-10-02: every response 557 // is kept; 2026-10-03: nothing is dropped). That was a rule for 558 // whoever wrote the next statement. Now the archive refuses: a row of 559 // `versions` or `events` can be added and nothing else. A migration 560 // that must correct one drops the trigger first, in the open. 561 &[ 562 "CREATE TRIGGER versions_are_not_changed BEFORE UPDATE ON versions BEGIN SELECT RAISE(ABORT, 'a kept response is never changed'); END", 563 "CREATE TRIGGER versions_are_not_deleted BEFORE DELETE ON versions BEGIN SELECT RAISE(ABORT, 'a kept response is never deleted'); END", 564 "CREATE TRIGGER events_are_not_changed BEFORE UPDATE ON events BEGIN SELECT RAISE(ABORT, 'an event is never changed'); END", 565 "CREATE TRIGGER events_are_not_deleted BEFORE DELETE ON events BEGIN SELECT RAISE(ABORT, 'an event is never deleted'); END", 566 ], 567 // When a step ran, and the build that ran it. The way back from a step 568 // that went wrong is the archive as it was just before it 569 // (`Admin::Restore`), and until now nothing said when that was. Steps 570 // before this one have no time. And `asked_lately` goes: `asked_feed` 571 // (step 11) is the same order with the tie broken, so nothing has read 572 // it since, and every question asked was written to it. 573 &["ALTER TABLE migrated ADD COLUMN at_ms REAL", "ALTER TABLE migrated ADD COLUMN build TEXT", "DROP INDEX asked_lately"], 574 // A comment on a vote (the owner, 2026-10-05: thumbs up or down opens a 575 // box, "Comments?"). It is a table of its own, not a column of `ratings` 576 // and not an `events` row, because of what each is for. `ratings` is the 577 // current vote and is deleted when a vote is taken back, so a column 578 // there would lose the words with the vote, and "nothing is dropped" 579 // (2026-10-03). `events` is the request log, and its columns are what 580 // `Event` says: free text from the public in `detail` would run through 581 // every `hits` view and the owner's copies of it. So `comments` is 582 // append-only like `events` (triggers below), a row per Save, tied to 583 // the vote by the same key as `ratings` (`input`, `answer`, `who`) with 584 // the vote as it was then (`vote`), so a vote changed or taken back 585 // later does not change what the comment was said of. `vote_comments` is 586 // every comment beside the vote as it is now; `votes_commented` is every 587 // vote now standing beside its latest comment, if it has one. Neither is 588 // asked by the day, so neither has a `_live`/`_finished` pair. The text 589 // is the public's own: the admin must show it as text, never as markup. 590 &[ 591 "CREATE TABLE comments (id INTEGER PRIMARY KEY, at_ms REAL NOT NULL, input TEXT NOT NULL, answer TEXT NOT NULL, who TEXT NOT NULL, browser TEXT NOT NULL, vote INTEGER NOT NULL, comment TEXT NOT NULL)", 592 "CREATE INDEX comments_vote ON comments (input, answer, who, id)", 593 "CREATE INDEX comments_lately ON comments (at_ms DESC)", 594 "CREATE TRIGGER comments_are_not_changed BEFORE UPDATE ON comments BEGIN SELECT RAISE(ABORT, 'a comment is never changed'); END", 595 "CREATE TRIGGER comments_are_not_deleted BEFORE DELETE ON comments BEGIN SELECT RAISE(ABORT, 'a comment is never deleted'); END", 596 r#"CREATE VIEW vote_comments AS 597SELECT c.id, c.at_ms, CAST(c.at_ms / 86400000 AS INTEGER) AS day, c.input, c.browser, 598 CASE c.vote WHEN 1 THEN 'up' ELSE 'down' END AS vote_then, 599 CASE r.vote WHEN 1 THEN 'up' WHEN -1 THEN 'down' END AS vote_now, 600 c.id = (SELECT MAX(l.id) FROM comments l WHERE l.input = c.input AND l.answer = c.answer AND l.who = c.who) AS latest, 601 c.comment 602FROM comments c LEFT JOIN ratings r ON r.input = c.input AND r.answer = c.answer AND r.who = c.who"#, 603 r#"CREATE VIEW votes_commented AS 604SELECT r.input, r.browser, CASE r.vote WHEN 1 THEN 'up' ELSE 'down' END AS vote, c.at_ms AS commented_ms, c.comment 605FROM ratings r LEFT JOIN comments c ON c.id = (SELECT MAX(l.id) FROM comments l WHERE l.input = r.input AND l.answer = r.answer AND l.who = r.who)"#, 606 ], 607 // Questions Jev cannot take, so a link to one can unfurl as "choose an 608 // LLM" and not as "Let me Jev that for you". Apart from `asked` because 609 // nothing was answered: they are not counted, listed or toasted, and 610 // `asked` is all of those. Only a question, and when; who asked is in 611 // `events`. 612 &["CREATE TABLE declined (input TEXT PRIMARY KEY, declined_ms REAL NOT NULL, times INTEGER NOT NULL DEFAULT 1)"], 613 // Votes and comments on a question Jev declined. A vote is on an 614 // answer, keyed by the hash of the answers kept (`ratings.answer`); a 615 // declined question has no answer, so its key is the word `declined`, 616 // which no hash can equal, and it is tied to the `declined` row by `input` 617 // (`ratings.input = declined.input AND ratings.answer = 'declined'`). The 618 // key stays one column, so every row is still readable and unique as it 619 // was. `about` says which kind a row is, worked out from `answer` and 620 // not written, so the two cannot disagree. The thumbs mean otherwise 621 // there: up is "right to decline", down is "Jev could have answered". 622 // The two views carry `about`. 623 &[ 624 "ALTER TABLE ratings ADD COLUMN about TEXT GENERATED ALWAYS AS (CASE WHEN answer = 'declined' THEN 'declined' ELSE 'answered' END) VIRTUAL", 625 "ALTER TABLE comments ADD COLUMN about TEXT GENERATED ALWAYS AS (CASE WHEN answer = 'declined' THEN 'declined' ELSE 'answered' END) VIRTUAL", 626 "DROP VIEW vote_comments", 627 "DROP VIEW votes_commented", 628 r#"CREATE VIEW vote_comments AS 629SELECT c.id, c.at_ms, CAST(c.at_ms / 86400000 AS INTEGER) AS day, c.input, c.browser, c.about, 630 CASE c.vote WHEN 1 THEN 'up' ELSE 'down' END AS vote_then, 631 CASE r.vote WHEN 1 THEN 'up' WHEN -1 THEN 'down' END AS vote_now, 632 c.id = (SELECT MAX(l.id) FROM comments l WHERE l.input = c.input AND l.answer = c.answer AND l.who = c.who) AS latest, 633 c.comment 634FROM comments c LEFT JOIN ratings r ON r.input = c.input AND r.answer = c.answer AND r.who = c.who"#, 635 r#"CREATE VIEW votes_commented AS 636SELECT r.input, r.browser, r.about, CASE r.vote WHEN 1 THEN 'up' ELSE 'down' END AS vote, c.at_ms AS commented_ms, c.comment 637FROM ratings r LEFT JOIN comments c ON c.id = (SELECT MAX(l.id) FROM comments l WHERE l.input = r.input AND l.answer = r.answer AND l.who = r.who)"#, 638 ], 639]; 640 641/// The key of a vote on a question Jev declined, in the place of the hash of 642/// the answers a vote on an answer has (MIGRATIONS step 21). No hash is this. 643pub const DECLINED: &str = "declined"; 644 645/// The step (from 1) from which `migrated` says when a step ran. 646const TIMED: usize = 18; 647 648/// The views whose finished days are kept: each has a `<name>_live` view, 649/// a `<name>_finished` table of the same columns, and a row of `finished`. 650const FINISHED: [&str; 20] = [ 651 "hours", "browser_days", "outcomes_by_day", "ask_endings_by_day", "pages_by_day", "questions_by_day", "referrers_by_day", 652 "countries_by_day", "country_visitors", "cities_by_day", "city_visitors", "networks_by_day", "network_visitors", "agents_by_day", "agent_browsers", 653 "page_days", "page_visitors", "place_days", "place_visitors", "place_addresses", 654]; 655 656/// The statements that copy `day` of the view `name` into its table, each 657/// with the day as its one value. Whatever was copied of the day is put 658/// aside first, so a copy that was cut short is done again whole. Neither 659/// touches a day `finished` already has: its events are no longer in 660/// `<name>_live`, and putting it aside would lose it. 661fn finish_sql(name: &str) -> [String; 2] { 662 let unfinished = format!("?1 > (SELECT through FROM finished WHERE name = '{name}')"); 663 [format!("DELETE FROM {name}_finished WHERE day = ?1 AND {unfinished}"), format!("INSERT INTO {name}_finished SELECT * FROM {name}_live WHERE day = ?1 AND {unfinished}")] 664} 665 666const DAY_MS: f64 = 86_400_000.0; 667 668/// The step (from 1) that gave `askers` and `ratings` their `browser`, after 669/// which the rows already there are named from `events`, once. 670const ASKERS_NAMED: usize = 10; 671 672// SQLite's numbers arrive as JS numbers, so every number is read as an f64. 673#[derive(Deserialize)] 674struct CallRow { 675 response: String, 676 request_id: Option<String>, 677 attempts: f64, 678 took_ms: f64, 679 answered_ms: f64, 680} 681 682#[derive(Deserialize)] 683struct AskedRow { 684 input: String, 685 answers: String, 686 asked_ms: f64, 687 times: f64, 688} 689 690impl From<AskedRow> for Entry { 691 fn from(row: AskedRow) -> Self { 692 Entry { 693 input: row.input, 694 answers: ::archive::stored(&row.answers), 695 asked_ms: row.asked_ms, 696 times: row.times as u32, 697 } 698 } 699} 700 701#[derive(Deserialize)] 702struct Number { 703 n: f64, 704} 705 706#[derive(Deserialize)] 707struct Tally { 708 questions: f64, 709 asks: f64, 710 no_llm: f64, 711} 712 713/// The repository whose clones and pulls the pages show. jevcrates is 714/// counted too, but a clone of lmjtfy fetches it, so it is not shown twice. 715const CODE: &str = "lmjtfy.git"; 716 717/// The names in `counts`. 718const SENT: &str = "sent"; 719const KEPT: &str = "kept"; 720 721/// A call on the wire, which every identical ask waits on. 722type Running = Shared<LocalBoxFuture<'static, Called>>; 723 724/// What a call needs after the request that started it has gone: shared, so 725/// the call can outlive that request. 726struct Shelf { 727 env: Env, 728 sql: SqlStorage, 729 running: RefCell<HashMap<(String, String), Running>>, 730 /// What the home page shows, kept from the last time it was read until 731 /// something it is made of changes (`changed`). Every home page asks 732 /// for it, and its numbers are a pass over every question. 733 home: RefCell<Option<Home>>, 734 /// Every question the feed may show, for suggesting as a visitor types: 735 /// read once, and again only after a question is asked or moderated 736 /// (`relisted`). A suggestion is wanted on every pause in the typing, 737 /// and a pass over `asked` each time would be most of the rows read. 738 listed: RefCell<Option<::archive::Listed>>, 739 /// The last day known to be kept (days since 1970), so that only the 740 /// first event of a new day looks. 741 finished_to: std::cell::Cell<i64>, 742} 743 744impl Shelf { 745 /// How many responses are kept for a request. 746 fn versions(&self, sent_to: &str, request: &str) -> worker::Result<u32> { 747 let rows: Vec<Number> = self 748 .sql 749 .exec("SELECT COUNT(*) AS n FROM versions WHERE sent_to = ? AND request = ?", vec![sent_to.into(), request.into()])? 750 .to_array()?; 751 Ok(rows.first().map_or(0, |row| row.n as u32)) 752 } 753 754 /// A kept response: `version`, or the newest. 755 fn kept(&self, sent_to: &str, request: &str, version: Option<u32>) -> worker::Result<Option<Record>> { 756 let versions = self.versions(sent_to, request)?; 757 let Some(version) = version.filter(|version| (1..=versions).contains(version)).or((versions > 0).then_some(versions)) else { 758 return Ok(None); 759 }; 760 let rows: Vec<CallRow> = self 761 .sql 762 .exec( 763 "SELECT response, request_id, attempts, took_ms, answered_ms FROM versions WHERE sent_to = ? AND request = ? AND version = ?", 764 vec![sent_to.into(), request.into(), i64::from(version).into()], 765 )? 766 .to_array()?; 767 Ok(rows.into_iter().next().map(|row| Record { 768 response: row.response, 769 request_id: row.request_id, 770 attempts: row.attempts as u32, 771 took_ms: row.took_ms, 772 answered_ms: row.answered_ms, 773 sent_now: false, 774 version, 775 versions, 776 })) 777 } 778 779 /// Keeps a response as the request's newest version, and says which it 780 /// is. A plain INSERT: two calls numbering the same version would mean 781 /// the archive sent twice what it meant to send once, and that should 782 /// fail here, not be papered over. 783 fn keep(&self, sent_to: &str, request: &str, record: &Record) -> worker::Result<u32> { 784 let version = self.versions(sent_to, request)? + 1; 785 self.sql.exec( 786 "INSERT INTO versions (sent_to, request, version, response, request_id, attempts, took_ms, answered_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", 787 vec![ 788 sent_to.into(), 789 request.into(), 790 i64::from(version).into(), 791 record.response.as_str().into(), 792 record.request_id.as_deref().map_or(worker::SqlStorageValue::Null, Into::into), 793 i64::from(record.attempts).into(), 794 record.took_ms.into(), 795 record.answered_ms.into(), 796 ], 797 )?; 798 Ok(version) 799 } 800 801 /// Runs the steps not yet run. Each is all or nothing (`Past::atomically`): 802 /// a step that fails leaves the archive as it was before it, to be 803 /// fixed and tried again, and not half made. 804 fn migrate(shelf: &Rc<Shelf>, past: &Past) -> Result<(), String> { 805 shelf.sql.exec("CREATE TABLE IF NOT EXISTS migrated (version INTEGER PRIMARY KEY)", None).map_err(|error| error.to_string())?; 806 let done = shelf.number("SELECT COUNT(*) AS n FROM migrated").map_err(|error| error.to_string())? as usize; 807 for (index, step) in MIGRATIONS.iter().enumerate().skip(done) { 808 let shelf = shelf.clone(); 809 past.atomically(move || { 810 for statement in *step { 811 shelf.sql.exec(statement, None)?; 812 } 813 shelf.sql.exec("INSERT INTO migrated (version) VALUES (?)", vec![(index as i64 + 1).into()])?; 814 if index + 1 >= TIMED { 815 shelf.sql.exec("UPDATE migrated SET at_ms = ?, build = ? WHERE version = ?", vec![js_sys::Date::now().into(), crate::BUILD.into(), (index as i64 + 1).into()])?; 816 } 817 if index + 1 == ASKERS_NAMED { 818 shelf.name_askers()?; 819 } 820 Ok(()) 821 }) 822 .map_err(|error| format!("step {}: {error}", index + 1))?; 823 } 824 Ok(()) 825 } 826 827 /// Keeps an event, whole, and says which row it is. 828 fn happened(&self, event: &::archive::Event, now_ms: f64) -> worker::Result<f64> { 829 let columns = ::archive::Event::COLUMNS; 830 let marks = vec!["?"; columns.len() + 1].join(", "); 831 let mut values: Vec<SqlStorageValue> = vec![now_ms.into()]; 832 values.extend(event.values().into_iter().map(|cell| match cell { 833 ::archive::event::Cell::Text(text) => text.into(), 834 ::archive::event::Cell::Number(number) => number.into(), 835 ::archive::event::Cell::Null => SqlStorageValue::Null, 836 })); 837 self.sql.exec(&format!("INSERT INTO events (at_ms, {}) VALUES ({marks})", columns.join(", ")), values)?; 838 let row = self.number("SELECT last_insert_rowid() AS n"); 839 // The first event of a day is when the day before is known to be 840 // over. Keeping it must not lose the event that noticed. 841 let yesterday = (now_ms / DAY_MS).floor() as i64 - 1; 842 if self.finished_to.get() < yesterday { 843 self.finished_to.set(yesterday); 844 if let Err(error) = self.finish(yesterday) { 845 console_error!("the days up to {yesterday} were not kept: {error}"); 846 } 847 } 848 row 849 } 850 851 /// Copies each finished day not yet kept, up to `last`, from every kept 852 /// view into its table. A day read here is not read from `events` for 853 /// that view again. 854 fn finish(&self, last: i64) -> worker::Result<()> { 855 let began: Vec<Number> = self.sql.exec("SELECT COALESCE(MIN(day), ?) AS n FROM events", vec![(last + 1).into()])?.to_array()?; 856 let began = began.first().map_or(last + 1, |row| row.n as i64); 857 for name in FINISHED { 858 let through: Vec<Number> = self.sql.exec("SELECT through AS n FROM finished WHERE name = ?", vec![name.into()])?.to_array()?; 859 let Some(through) = through.first().map(|row| row.n as i64) else { continue }; 860 let [put_aside, copy] = finish_sql(name); 861 for day in (through + 1).max(began)..=last { 862 self.sql.exec(&put_aside, vec![day.into()])?; 863 self.sql.exec(©, vec![day.into()])?; 864 self.sql.exec("UPDATE finished SET through = ? WHERE name = ?", vec![day.into(), name.into()])?; 865 } 866 } 867 Ok(()) 868 } 869 870 /// A page that connected (the event in row `came`) has gone: the same 871 /// event again, as `left`, with how long it stayed. 872 fn left(&self, came: f64, stayed_ms: f64, now_ms: f64) -> worker::Result<()> { 873 let columns = ::archive::Event::COLUMNS; 874 let copied: Vec<&str> = columns 875 .iter() 876 .map(|column| match *column { 877 "what" => "'left'", 878 "took_ms" => "?", 879 column => column, 880 }) 881 .collect(); 882 self.sql.exec( 883 &format!("INSERT INTO events (at_ms, {}) SELECT ?, {} FROM events WHERE id = ?", columns.join(", "), copied.join(", ")), 884 vec![now_ms.into(), stayed_ms.into(), came.into()], 885 )?; 886 Ok(()) 887 } 888 889 /// Whether the feed may show `input`: the owner's say if they have one, 890 /// Jev's otherwise. 891 fn shown(&self, input: &str) -> worker::Result<bool> { 892 let rows: Vec<Number> = self.sql.exec("SELECT COALESCE(moderated, listed) AS n FROM asked WHERE input = ?", vec![input.into()])?.to_array()?; 893 Ok(rows.first().is_some_and(|row| row.n == 1.0)) 894 } 895 896 /// The admin backend's requests (`archive::Admin`). 897 fn admin(&self, ask: ::archive::Admin) -> ::archive::Answered { 898 use ::archive::{Admin, Answered}; 899 let refused = |error: worker::Error| Answered::Refused(error.to_string()); 900 match ask { 901 Admin::Select { sql, .. } if !::archive::reads_only(&sql) => Answered::Refused("only one statement that reads".into()), 902 Admin::Select { sql, values } => { 903 let values: Vec<SqlStorageValue> = values 904 .into_iter() 905 .map(|value| match value { 906 serde_json::Value::Null => SqlStorageValue::Null, 907 serde_json::Value::Bool(flag) => i64::from(flag).into(), 908 serde_json::Value::Number(number) => number.as_f64().unwrap_or_default().into(), 909 serde_json::Value::String(text) => text.into(), 910 other => other.to_string().into(), 911 }) 912 .collect(); 913 let cursor = match self.sql.exec(&sql, values) { 914 Ok(cursor) => cursor, 915 Err(error) => return refused(error), 916 }; 917 let columns = cursor.column_names(); 918 let mut rows = Vec::new(); 919 for row in cursor.raw() { 920 let row = match row { 921 Ok(row) => row, 922 Err(error) => return refused(error), 923 }; 924 rows.push( 925 row.into_iter() 926 .map(|value| match value { 927 SqlStorageValue::Null => serde_json::Value::Null, 928 SqlStorageValue::Boolean(flag) => flag.into(), 929 SqlStorageValue::Integer(number) => number.into(), 930 SqlStorageValue::Float(number) => number.into(), 931 SqlStorageValue::String(text) => text.into(), 932 SqlStorageValue::Blob(bytes) => format!("<{} bytes>", bytes.len()).into(), 933 }) 934 .collect(), 935 ); 936 } 937 Answered::Rows { columns, rows, read: cursor.rows_read() as u64 } 938 } 939 Admin::Moderate { input, listed } => { 940 self.changed(); 941 self.relisted(); 942 let say: SqlStorageValue = listed.map_or(SqlStorageValue::Null, |listed| i64::from(listed).into()); 943 match self.sql.exec("UPDATE asked SET moderated = ? WHERE input = ?", vec![say, input.into()]) { 944 Ok(_) => Answered::Done, 945 Err(error) => refused(error), 946 } 947 } 948 // Answered by the object itself (`Archive::fetch`): they need 949 // its state, and must work when the schema does not. 950 Admin::Bookmark { .. } | Admin::Restore { .. } => Answered::Refused("not a question for the tables".into()), 951 } 952 } 953 954 /// Fills in whose each old `askers` and `ratings` row is, where an 955 /// event says which browser asked or voted on that question: the 956 /// browser's id and the question hash to the row's `who`. A row from 957 /// before events were kept stays unnamed. 958 fn name_askers(&self) -> worker::Result<()> { 959 #[derive(Deserialize)] 960 struct Seen { 961 input: String, 962 browser: String, 963 } 964 let seen: Vec<Seen> = self.sql.exec("SELECT DISTINCT input, browser FROM events WHERE browser != '' AND input != '' AND what IN ('answer', 'vote')", None)?.to_array()?; 965 for Seen { input, browser } in seen { 966 let who = crate::asker(&browser, &input); 967 for table in ["askers", "ratings"] { 968 self.sql.exec( 969 &format!("UPDATE {table} SET browser = ? WHERE input = ? AND who = ? AND browser = ''"), 970 vec![browser.as_str().into(), input.as_str().into(), who.0.as_str().into()], 971 )?; 972 } 973 } 974 Ok(()) 975 } 976 977 fn number(&self, query: &str) -> worker::Result<f64> { 978 let rows: Vec<Number> = self.sql.exec(query, None)?.to_array()?; 979 Ok(rows.first().map_or(0.0, |row| row.n)) 980 } 981 982 /// Adds one to a tally. A failure loses one from a number on the home 983 /// page and nothing else, so it is not allowed to fail a call. 984 fn count(&self, name: &str) { 985 self.changed(); 986 let counted = self.sql.exec( 987 "INSERT INTO counts (name, n) VALUES (?, 1) ON CONFLICT (name) DO UPDATE SET n = n + 1", 988 vec![name.into()], 989 ); 990 if let Err(error) = counted { 991 console_error!("the archive could not count a {name} request: {error}"); 992 } 993 } 994 995 fn counted(&self, name: &str) -> worker::Result<u32> { 996 let rows: Vec<Number> = self.sql.exec("SELECT n FROM counts WHERE name = ?", vec![name.into()])?.to_array()?; 997 Ok(rows.first().map_or(0, |row| row.n as u32)) 998 } 999 1000 /// Records what Jev said to `input`, and whether this is a new asking of 1001 /// it: by a browser that has not asked it before, or by one that gave no 1002 /// id. Only a new asking counts and moves it up the feed; Jev's answer 1003 /// is kept either way. 1004 fn note(&self, input: &str, answers: &[Answer], llm: bool, listed: bool, who: Option<&Asker>, browser: &str, now_ms: f64) -> worker::Result<bool> { 1005 self.changed(); 1006 self.relisted(); 1007 let answers = serde_json::to_string(answers).map_err(|e| worker::Error::from(e.to_string()))?; 1008 let new = match who { 1009 Some(who) => { 1010 let cursor = self.sql.exec("INSERT OR IGNORE INTO askers (input, who, browser) VALUES (?, ?, ?)", vec![input.into(), who.0.as_str().into(), browser.into()])?; 1011 cursor.rows_written() > 0 1012 } 1013 None => true, 1014 }; 1015 if !new { 1016 self.sql.exec( 1017 "UPDATE asked SET answers = ?, listed = ?, llm = ? WHERE input = ?", 1018 vec![answers.into(), i64::from(listed).into(), i64::from(llm).into(), input.into()], 1019 )?; 1020 return Ok(false); 1021 } 1022 self.sql.exec( 1023 "INSERT INTO asked (input, answers, asked_ms, times, listed, llm) VALUES (?, ?, ?, 1, ?, ?) 1024 ON CONFLICT (input) DO UPDATE SET answers = excluded.answers, asked_ms = excluded.asked_ms, 1025 times = times + 1, listed = excluded.listed, llm = excluded.llm", 1026 vec![input.into(), answers.into(), now_ms.into(), i64::from(listed).into(), i64::from(llm).into()], 1027 )?; 1028 Ok(true) 1029 } 1030 1031 /// What a vote on `input` is on: the SHA-256 of the answers as kept, or 1032 /// `DECLINED` for a question Jev declined and has not answered since. 1033 /// `None` if it was neither, so there is nothing to vote on. 1034 fn answer_key(&self, input: &str) -> worker::Result<Option<String>> { 1035 #[derive(Deserialize)] 1036 struct Kept { 1037 answers: String, 1038 } 1039 let rows: Vec<Kept> = self.sql.exec("SELECT answers FROM asked WHERE input = ?", vec![input.into()])?.to_array()?; 1040 match rows.first() { 1041 Some(kept) => { 1042 use sha2::{Digest, Sha256}; 1043 Ok(Some(Sha256::digest(kept.answers.as_bytes()).iter().map(|byte| format!("{byte:02x}")).collect())) 1044 } 1045 None => Ok(self.was_declined(input)?.then(|| DECLINED.to_owned())), 1046 } 1047 } 1048 1049 /// `who`'s vote on the answer to `input` as kept now; the vote it already 1050 /// has takes it back. 1051 fn rate(&self, input: &str, who: &Asker, browser: &str, vote: Vote) -> worker::Result<Option<Rating>> { 1052 let Some(answer) = self.answer_key(input)? else { return Ok(None) }; 1053 let value = match vote { 1054 Vote::Up => 1, 1055 Vote::Down => -1, 1056 }; 1057 let had = self.rating_for(input, &answer, Some(who))?.mine; 1058 if had == Some(vote) { 1059 self.sql.exec( 1060 "DELETE FROM ratings WHERE input = ? AND answer = ? AND who = ?", 1061 vec![input.into(), answer.as_str().into(), who.0.as_str().into()], 1062 )?; 1063 } else { 1064 self.sql.exec( 1065 "INSERT INTO ratings (input, answer, who, vote, browser) VALUES (?, ?, ?, ?, ?) 1066 ON CONFLICT (input, answer, who) DO UPDATE SET vote = excluded.vote", 1067 vec![input.into(), answer.as_str().into(), who.0.as_str().into(), value.into(), browser.into()], 1068 )?; 1069 } 1070 Ok(Some(self.rating_for(input, &answer, Some(who))?)) 1071 } 1072 1073 /// Keeps `comment` beside `who`'s standing vote on the answer to `input`. 1074 /// The same words again for the same vote are not kept twice. The text is 1075 /// a bound value only: it is in no message and no log. 1076 fn comment(&self, input: &str, who: &Asker, browser: &str, comment: &str) -> worker::Result<Result<(), Unsaid>> { 1077 let Some(answer) = self.answer_key(input)? else { return Ok(Err(Unsaid::NoVote)) }; 1078 let Some(vote) = self.rating_for(input, &answer, Some(who))?.mine else { return Ok(Err(Unsaid::NoVote)) }; 1079 #[derive(Deserialize)] 1080 struct Last { 1081 comment: String, 1082 vote: f64, 1083 } 1084 let last: Vec<Last> = self 1085 .sql 1086 .exec( 1087 "SELECT comment, vote FROM comments WHERE input = ? AND answer = ? AND who = ? ORDER BY id DESC LIMIT 1", 1088 vec![input.into(), answer.as_str().into(), who.0.as_str().into()], 1089 )? 1090 .to_array()?; 1091 let value = match vote { 1092 Vote::Up => 1, 1093 Vote::Down => -1, 1094 }; 1095 if last.first().is_some_and(|last| last.comment == comment && last.vote == f64::from(value)) { 1096 return Ok(Ok(())); 1097 } 1098 self.sql.exec( 1099 "INSERT INTO comments (at_ms, input, answer, who, browser, vote, comment) VALUES (?, ?, ?, ?, ?, ?, ?)", 1100 vec![js_sys::Date::now().into(), input.into(), answer.as_str().into(), who.0.as_str().into(), browser.into(), value.into(), comment.into()], 1101 )?; 1102 Ok(Ok(())) 1103 } 1104 1105 fn rating(&self, input: &str, who: Option<&Asker>) -> worker::Result<Option<Rating>> { 1106 match self.answer_key(input)? { 1107 Some(answer) => Ok(Some(self.rating_for(input, &answer, who)?)), 1108 None => Ok(None), 1109 } 1110 } 1111 1112 fn rating_for(&self, input: &str, answer: &str, who: Option<&Asker>) -> worker::Result<Rating> { 1113 #[derive(Deserialize)] 1114 struct Votes { 1115 up: f64, 1116 down: f64, 1117 } 1118 #[derive(Deserialize)] 1119 struct Mine { 1120 vote: f64, 1121 } 1122 let votes: Vec<Votes> = self 1123 .sql 1124 .exec( 1125 "SELECT COALESCE(SUM(vote = 1), 0) AS up, COALESCE(SUM(vote = -1), 0) AS down FROM ratings WHERE input = ? AND answer = ?", 1126 vec![input.into(), answer.into()], 1127 )? 1128 .to_array()?; 1129 let mine = match who { 1130 Some(who) => { 1131 let rows: Vec<Mine> = self 1132 .sql 1133 .exec( 1134 "SELECT vote FROM ratings WHERE input = ? AND answer = ? AND who = ?", 1135 vec![input.into(), answer.into(), who.0.as_str().into()], 1136 )? 1137 .to_array()?; 1138 rows.first().map(|row| if row.vote > 0.0 { Vote::Up } else { Vote::Down }) 1139 } 1140 None => None, 1141 }; 1142 let votes = votes.first(); 1143 Ok(Rating { up: votes.map_or(0, |v| v.up as u32), down: votes.map_or(0, |v| v.down as u32), mine, declined: answer == DECLINED }) 1144 } 1145 1146 /// What Jev said to `input`, listed or not: it is for whoever holds the 1147 /// link. 1148 fn answer(&self, input: &str) -> worker::Result<Option<Entry>> { 1149 let rows: Vec<AskedRow> = self 1150 .sql 1151 .exec("SELECT input, answers, asked_ms, times FROM asked WHERE input = ?", vec![input.into()])? 1152 .to_array()?; 1153 Ok(rows.into_iter().next().map(Entry::from)) 1154 } 1155 1156 /// Keeps that `input` was a question Jev cannot take. 1157 fn decline(&self, input: &str, now_ms: f64) -> worker::Result<()> { 1158 self.sql.exec( 1159 "INSERT INTO declined (input, declined_ms) VALUES (?, ?) 1160 ON CONFLICT (input) DO UPDATE SET declined_ms = excluded.declined_ms, times = times + 1", 1161 vec![input.into(), now_ms.into()], 1162 )?; 1163 Ok(()) 1164 } 1165 1166 /// Was `input` declined, and never since answered? 1167 fn was_declined(&self, input: &str) -> worker::Result<bool> { 1168 #[derive(Deserialize)] 1169 struct Found { 1170 #[allow(dead_code)] 1171 input: String, 1172 } 1173 let found: Vec<Found> = self.sql.exec("SELECT input FROM declined WHERE input = ?", vec![input.into()])?.to_array()?; 1174 Ok(!found.is_empty() && self.answer(input)?.is_none()) 1175 } 1176 1177 /// The listed questions after `after` in the feed's order. 1178 fn older(&self, after: &Cursor) -> worker::Result<Vec<Entry>> { 1179 let rows: Vec<AskedRow> = self 1180 .sql 1181 .exec( 1182 "SELECT input, answers, asked_ms, times FROM asked 1183 WHERE COALESCE(moderated, listed) = 1 AND (asked_ms < ? OR (asked_ms = ? AND input < ?)) 1184 ORDER BY asked_ms DESC, input DESC LIMIT ?", 1185 vec![after.asked_ms.into(), after.asked_ms.into(), after.input.as_str().into(), i64::from(FEED).into()], 1186 )? 1187 .to_array()?; 1188 Ok(rows.into_iter().map(Entry::from).collect()) 1189 } 1190 1191 fn entries(&self, query: &str, limit: u32) -> worker::Result<Vec<Entry>> { 1192 let rows: Vec<AskedRow> = self.sql.exec(query, vec![i64::from(limit).into()])?.to_array()?; 1193 Ok(rows.into_iter().map(Entry::from).collect()) 1194 } 1195 1196 /// What the home page is made of has changed: read it again next time. 1197 fn changed(&self) { 1198 self.home.replace(None); 1199 } 1200 1201 /// The questions the feed may show have changed. 1202 fn relisted(&self) { 1203 self.listed.replace(None); 1204 } 1205 1206 /// The feed's questions that `typed` could be the start of. 1207 fn suggest(&self, typed: &str) -> worker::Result<Vec<Entry>> { 1208 if self.listed.borrow().is_none() { 1209 let rows: Vec<AskedRow> = self.sql.exec("SELECT input, answers, asked_ms, times FROM asked WHERE COALESCE(moderated, listed) = 1", None)?.to_array()?; 1210 self.listed.replace(Some(::archive::Listed::new(rows.into_iter().map(Entry::from)))); 1211 } 1212 Ok(self.listed.borrow().as_ref().map(|listed| listed.suggest(typed)).unwrap_or_default()) 1213 } 1214 1215 fn home(&self) -> worker::Result<Home> { 1216 if let Some(home) = self.home.borrow().as_ref() { 1217 return Ok(home.clone()); 1218 } 1219 let home = self.read_home()?; 1220 self.home.replace(Some(home.clone())); 1221 Ok(home) 1222 } 1223 1224 fn read_home(&self) -> worker::Result<Home> { 1225 let tally: Vec<Tally> = self 1226 .sql 1227 .exec( 1228 "SELECT COUNT(*) AS questions, COALESCE(SUM(times), 0) AS asks, 1229 COALESCE(SUM(llm = 0), 0) AS no_llm FROM asked", 1230 None, 1231 )? 1232 .to_array()?; 1233 let tally = tally.first(); 1234 Ok(Home { 1235 lately: self.entries( 1236 "SELECT input, answers, asked_ms, times FROM asked WHERE COALESCE(moderated, listed) = 1 ORDER BY asked_ms DESC, input DESC LIMIT ?", 1237 FEED, 1238 )?, 1239 most: self.entries( 1240 "SELECT input, answers, asked_ms, times FROM asked WHERE COALESCE(moderated, listed) = 1 AND times > 1 1241 ORDER BY times DESC, asked_ms DESC LIMIT ?", 1242 MOST, 1243 )?, 1244 stats: Stats { 1245 questions: tally.map_or(0, |tally| tally.questions as u32), 1246 asks: tally.map_or(0, |tally| tally.asks as u32), 1247 no_llm: tally.map_or(0, |tally| tally.no_llm as u32), 1248 sent: self.counted(SENT)?, 1249 kept: self.counted(KEPT)?, 1250 clones: self.counted(&Fetch::Clone.counted(CODE))?, 1251 pulls: self.counted(&Fetch::Pull.counted(CODE))?, 1252 }, 1253 }) 1254 } 1255} 1256 1257#[durable_object] 1258pub struct Archive { 1259 state: State, 1260 shelf: Rc<Shelf>, 1261 past: Past, 1262 /// Why the schema could not be brought up to date, if it could not. 1263 /// Such an archive answers nothing but the owner's `Bookmark` and 1264 /// `Restore`, which are how it is put right. 1265 broken: Option<String>, 1266} 1267 1268fn failed(error: impl Into<String>) -> Called { 1269 Called::Failed { error: error.into(), request_id: None, took_ms: 0.0 } 1270} 1271 1272/// Sends one Jev request, inside the day's Jev budget. 1273async fn jev(shelf: Rc<Shelf>, input: String, wanted: Wanted, request: String) -> Called { 1274 let jev = match crate::jev(&shelf.env) { 1275 Ok(jev) => jev, 1276 Err(NoJev::Offline) => return failed("the Worker has no TypeSafe API key"), 1277 Err(NoJev::Broken(error)) => return failed(error), 1278 }; 1279 let prepared = match ask::wanted(&jev.model, &input, &wanted) { 1280 Ok(prepared) => prepared, 1281 Err(error) => return failed(error.to_string()), 1282 }; 1283 // The page shows `request` and the row is kept under it, so it has to be 1284 // the body that goes out. They can differ only while a deploy has the 1285 // Worker and this object on different code. 1286 if prepared.request != request { 1287 return failed("the archive would send a different request than the page prepared, so nothing was sent"); 1288 } 1289 let hold = match meter::hold(&shelf.env, Which::JevDollars, prepared.worst_case_dollars, jev.dollars_per_day).await { 1290 Ok(hold) => hold, 1291 Err(Refused::Spent(_)) => return Called::Spent(Pot::Jev), 1292 Err(Refused::Unreachable(error)) => return failed(error), 1293 }; 1294 let outcome = ask::send(&jev.client, &WorkerRuntime, &prepared).await; 1295 meter::settle(&shelf.env, Which::JevDollars, hold, outcome.dollars()).await; 1296 match outcome { 1297 Outcome::Answered { body, request_id, attempts, took, .. } => Called::Answered(Record { 1298 response: body, 1299 request_id, 1300 attempts, 1301 took_ms: took.as_secs_f64() * 1000.0, 1302 answered_ms: js_sys::Date::now(), 1303 sent_now: true, 1304 // Numbered when it is kept (`Shelf::keep`). 1305 version: 0, 1306 versions: 0, 1307 }), 1308 Outcome::Failed { error, request_id, took, .. } => { 1309 Called::Failed { error, request_id, took_ms: took.as_secs_f64() * 1000.0 } 1310 } 1311 } 1312} 1313 1314/// Sends one request to the LLM, inside the day's neuron budget. 1315async fn llm(shelf: Rc<Shelf>, id: String, request: String) -> Called { 1316 let Some(model) = Model::find(&id) else { 1317 return failed(format!("{id} is not one of llm's candidates")); 1318 }; 1319 let hold = match meter::hold(&shelf.env, Which::Neurons, model.worst_case_neurons(&request), llm::FREE_NEURONS_PER_DAY).await { 1320 Ok(hold) => hold, 1321 Err(Refused::Spent(_)) => return Called::Spent(Pot::Llm), 1322 Err(Refused::Unreachable(error)) => return failed(error), 1323 }; 1324 let started = WorkerRuntime.now(); 1325 let ran = ai::run(&shelf.env, model.id, &request).await; 1326 let took_ms = (WorkerRuntime.now() - started).as_secs_f64() * 1000.0; 1327 // A call that never produced a reply is counted as free; one that did is 1328 // counted at what the reply says it used. 1329 let neurons = ran.as_ref().ok().and_then(|body| llm::parse(body).ok()).map_or(0.0, |reply| model.neurons(reply.usage)); 1330 meter::settle(&shelf.env, Which::Neurons, hold, neurons).await; 1331 match ran { 1332 Ok(response) => Called::Answered(Record { 1333 response, 1334 request_id: None, 1335 attempts: 1, 1336 took_ms, 1337 answered_ms: js_sys::Date::now(), 1338 sent_now: true, 1339 // Numbered when it is kept (`Shelf::keep`). 1340 version: 0, 1341 versions: 0, 1342 }), 1343 Err(error) => Called::Failed { error, request_id: None, took_ms }, 1344 } 1345} 1346 1347impl Archive { 1348 /// The response to `request`, sent at most once: the kept one, the one a 1349 /// call already on the wire is about to get, or the one `call` gets now. 1350 /// 1351 /// From the lookup to the insert into `running` nothing awaits, so no 1352 /// other ask can run in between and start the same call. 1353 /// 1354 /// `pick` says which kept response is wanted, and whether to send it 1355 /// again regardless (`Pick::Fresh`) or never (`Pick::KeptOnly`). 1356 async fn once<F>(&self, sent_to: String, request: String, pick: Pick, call: impl FnOnce(Rc<Shelf>) -> F) -> worker::Result<Called> 1357 where 1358 F: Future<Output = Called> + 'static, 1359 { 1360 let wanted = match pick { 1361 Pick::Version(version) => Some(version), 1362 _ => None, 1363 }; 1364 if pick != Pick::Fresh 1365 && let Some(record) = self.shelf.kept(&sent_to, &request, wanted)? 1366 { 1367 self.shelf.count(KEPT); 1368 return Ok(Called::Answered(record)); 1369 } 1370 let key = (sent_to, request); 1371 let running = self.shelf.running.borrow().get(&key).cloned(); 1372 if pick == Pick::KeptOnly && running.is_none() { 1373 return Ok(Called::NotKept); 1374 } 1375 if let Some(running) = running { 1376 return Ok(match running.await { 1377 Called::Answered(record) => { 1378 self.shelf.count(KEPT); 1379 Called::Answered(record.kept()) 1380 } 1381 other => other, 1382 }); 1383 } 1384 let shelf = self.shelf.clone(); 1385 let call = call(shelf.clone()); 1386 let done = key.clone(); 1387 let work: Running = async move { 1388 let mut called = call.await; 1389 if let Called::Answered(record) = &mut called { 1390 shelf.count(SENT); 1391 match shelf.keep(&done.0, &done.1, record) { 1392 Ok(version) => { 1393 record.version = version; 1394 record.versions = version; 1395 } 1396 Err(error) => console_error!("the archive could not keep a response, so its request may be sent again: {error}"), 1397 } 1398 } 1399 shelf.running.borrow_mut().remove(&done); 1400 called 1401 } 1402 .boxed_local() 1403 .shared(); 1404 self.shelf.running.borrow_mut().insert(key, work.clone()); 1405 self.state.wait_until(work.clone().map(|_| ())); 1406 Ok(work.await) 1407 } 1408} 1409 1410impl Archive { 1411 /// Sends `live` to every open page. A page that cannot be told has gone, 1412 /// and its close is on its way. 1413 fn broadcast(&self, live: &Live) { 1414 self.send(&self.state.get_websockets(), live); 1415 } 1416 1417 fn send(&self, sockets: &[WebSocket], live: &Live) { 1418 let Ok(text) = serde_json::to_string(live) else { return }; 1419 for socket in sockets { 1420 let _ = socket.send_with_str(&text); 1421 } 1422 } 1423 1424 /// Sends every open page the activity feeds as they now stand: `asked` 1425 /// at the top of the feed, if it was a question the feed may show, and 1426 /// the most asked and the tally whole. Nobody open, nothing to render. 1427 fn activity(&self, asked: Option<&str>) { 1428 if self.state.get_websockets().is_empty() { 1429 return; 1430 } 1431 let top = match asked.map(|input| self.shelf.answer(input)) { 1432 Some(Ok(entry)) => entry, 1433 Some(Err(error)) => { 1434 console_error!("the archive could not read the question to send: {error}"); 1435 None 1436 } 1437 None => None, 1438 }; 1439 match self.shelf.home() { 1440 Ok(home) => { 1441 let markup = maud::html! { 1442 @if let Some(top) = &top { (crate::view::lately_top(top)) } 1443 (crate::view::most(&home.most)) 1444 (crate::view::tally(Some(&home.stats))) 1445 }; 1446 self.broadcast(&Live::Patch(markup.into_string())); 1447 } 1448 Err(error) => console_error!("the archive could not read the feeds to send: {error}"), 1449 } 1450 } 1451 1452 /// The pages open now: every socket still open, `leaving` not counted. 1453 fn open(&self, leaving: Option<&WebSocket>) -> Vec<WebSocket> { 1454 self.state 1455 .get_websockets() 1456 .into_iter() 1457 .filter(|socket| { 1458 let raw: &worker::web_sys::WebSocket = socket.as_ref(); 1459 raw.ready_state() == worker::web_sys::WebSocket::OPEN 1460 }) 1461 .filter(|socket| leaving.is_none_or(|leaving| !same(socket, leaving))) 1462 .collect() 1463 } 1464 1465 /// Tells `to` how many pages are open and where they are. 1466 fn online(&self, to: &[WebSocket], open: &[WebSocket]) { 1467 self.send(to, &Live::Online(open.len() as u32)); 1468 let places = open.iter().map(|socket| seen(socket).map(|seen| seen.place).unwrap_or_default()); 1469 self.send(to, &Live::Patch(crate::view::places(&::archive::places(places)).into_string())); 1470 } 1471 1472 /// Everyone hears that pages came or went once, `ONLINE_EVERY_MS` after 1473 /// the first change, however many changed in between. A deploy closes 1474 /// every socket and they all reconnect within a second or two; telling 1475 /// every page about every one would be pages² messages. 1476 async fn soon(&self) -> worker::Result<()> { 1477 let storage = self.state.storage(); 1478 if storage.get_alarm().await?.is_none() { 1479 storage.set_alarm(std::time::Duration::from_millis(ONLINE_EVERY_MS)).await?; 1480 } 1481 Ok(()) 1482 } 1483} 1484 1485/// What is kept on a page's socket, if anything. 1486fn seen(socket: &WebSocket) -> Option<Seen> { 1487 socket.deserialize_attachment::<Seen>().ok().flatten() 1488} 1489 1490/// Whether two handles are the same socket. 1491fn same(a: &WebSocket, b: &WebSocket) -> bool { 1492 let a: &worker::web_sys::WebSocket = a.as_ref(); 1493 let b: &worker::web_sys::WebSocket = b.as_ref(); 1494 js_sys::Object::is(a.as_ref(), b.as_ref()) 1495} 1496 1497impl DurableObject for Archive { 1498 fn new(state: State, env: Env) -> Self { 1499 let (past, state) = Past::of(state); 1500 let shelf = Rc::new(Shelf { env, sql: state.storage().sql(), running: RefCell::new(HashMap::new()), home: RefCell::new(None), listed: RefCell::new(None), finished_to: std::cell::Cell::new(i64::MIN) }); 1501 // An archive that cannot bring its schema up to date must not serve: 1502 // it would keep responses in a shape the next start cannot read. 1503 let broken = Shelf::migrate(&shelf, &past).err(); 1504 if let Some(why) = &broken { 1505 console_error!("the archive's migrations did not run: {why}"); 1506 } 1507 Archive { state, shelf, past, broken } 1508 } 1509 1510 async fn fetch(&self, mut request: Request) -> worker::Result<Response> { 1511 // `/live`: an open page, told what happens while it is open. The 1512 // socket is the object's, accepted for hibernation, so an idle page 1513 // keeps nothing awake. 1514 // The admin backend's door. Only its own Worker sends a request here 1515 // (`archive::ADMIN`); this site's Worker has no code that does. 1516 if request.path() == ::archive::ADMIN { 1517 let ask: ::archive::Admin = serde_json::from_str(&request.text().await?).map_err(|e| worker::Error::from(e.to_string()))?; 1518 let said = |answered: ::archive::Answered| Response::ok(serde_json::to_string(&answered).map_err(|e| worker::Error::from(e.to_string()))?); 1519 let named = |bookmark: Result<String, String>| bookmark.map_or_else(::archive::Answered::Refused, ::archive::Answered::Bookmark); 1520 match &ask { 1521 ::archive::Admin::Bookmark { at_ms } => return said(named(self.past.bookmark(*at_ms).await)), 1522 ::archive::Admin::Restore { bookmark } => { 1523 let undo = self.past.restore(bookmark).await; 1524 if undo.is_ok() { 1525 // The answer leaves first; then the object ends, and 1526 // starts again as it was. 1527 let past = self.past.clone(); 1528 wasm_bindgen_futures::spawn_local(async move { 1529 worker::Delay::from(std::time::Duration::from_millis(250)).await; 1530 past.restart(); 1531 }); 1532 } 1533 return said(named(undo)); 1534 } 1535 _ => {} 1536 } 1537 if let Some(why) = &self.broken { 1538 return said(::archive::Answered::Refused(format!("the archive's migrations did not run: {why}"))); 1539 } 1540 let moderated = matches!(ask, ::archive::Admin::Moderate { .. }); 1541 let answered = self.shelf.admin(ask); 1542 if moderated { 1543 // Open pages get the feeds as they now are. 1544 self.activity(None); 1545 } 1546 return Response::ok(serde_json::to_string(&answered).map_err(|e| worker::Error::from(e.to_string()))?); 1547 } 1548 if let Some(why) = &self.broken { 1549 return Response::error(format!("the archive's migrations did not run: {why}"), 503); 1550 } 1551 if request.headers().get("upgrade")?.is_some_and(|upgrade| upgrade.eq_ignore_ascii_case("websocket")) { 1552 let pair = WebSocketPair::new()?; 1553 self.state.accept_web_socket(&pair.server); 1554 // Where it connected from, as the Worker passed it on: kept on the 1555 // socket, which hibernation keeps, and gone when it closes. 1556 let header = |name: &str| -> Option<String> { 1557 let value = request.headers().get(name).ok().flatten()?; 1558 ::archive::percent_decoded(&value) 1559 }; 1560 // Who connected, kept as an event; the socket remembers which, 1561 // so the event that says it left can say who. 1562 let now = js_sys::Date::now(); 1563 let came = header(EVENT) 1564 .and_then(|event| serde_json::from_str::<::archive::Event>(&event).ok()) 1565 .and_then(|event| self.shelf.happened(&event.named("live"), now).ok()); 1566 let seen = Seen { place: Place::new(header(COUNTRY).as_deref(), header(CITY).as_deref()), passed: header(PLACED).is_some(), at_ms: now, event: came }; 1567 let _ = pair.server.serialize_attachment(&seen); 1568 self.send(std::slice::from_ref(&pair.server), &Live::Build(crate::BUILD.to_owned())); 1569 // The page that came hears how things stand now, itself counted; 1570 // the others hear it with the next change. 1571 let open = self.open(None); 1572 self.online(std::slice::from_ref(&pair.server), &open); 1573 self.soon().await?; 1574 // A page from an earlier build hears what this one changed. A 1575 // page says its build in `?build=`; one too old to say is earlier. 1576 let theirs = request.url()?.query_pairs().find(|(key, _)| key == "build").map(|(_, build)| build.into_owned()); 1577 if !crate::NOTE.is_empty() && theirs.as_deref() != Some(crate::BUILD) { 1578 self.send(std::slice::from_ref(&pair.server), &Live::Toast(crate::view::toast::note(crate::NOTE).into_string())); 1579 } 1580 return Response::from_websocket(pair.client); 1581 } 1582 let text = request.text().await?; 1583 let ask: Ask = serde_json::from_str(&text).map_err(|e| worker::Error::from(e.to_string()))?; 1584 let told = match ask { 1585 Ask::Jev { input, wanted, request, pick } => { 1586 let body = request.clone(); 1587 Told::Called(self.once(jev_protocol::ENDPOINT.to_owned(), request, pick, |shelf| jev(shelf, input, wanted, body)).await?) 1588 } 1589 Ask::Llm { model, request, pick } => { 1590 let body = request.clone(); 1591 Told::Called(self.once(model.clone(), request, pick, |shelf| llm(shelf, model, body)).await?) 1592 } 1593 Ask::Asked { input, answers, llm, listed, who, browser } => { 1594 // A browser asking what it asked before is not news. 1595 if self.shelf.note(&input, &answers, llm, listed, who.as_ref(), &browser, js_sys::Date::now())? { 1596 // Only what the feed may show is shown; anything else is 1597 // "someone asked". The owner's say comes before Jev's. 1598 let listed = self.shelf.shown(&input)?; 1599 let shown = listed.then_some((input.as_str(), answers.as_slice())); 1600 self.broadcast(&Live::Toast(crate::view::toast::asked(shown).into_string())); 1601 self.activity(listed.then_some(input.as_str())); 1602 } 1603 Told::Noted 1604 } 1605 Ask::Event(event) => { 1606 self.shelf.happened(&event, js_sys::Date::now())?; 1607 Told::Noted 1608 } 1609 Ask::Fetched { repo, fetch } => { 1610 self.shelf.count(&fetch.counted(&repo)); 1611 // jevcrates comes along with every project cloned with its 1612 // submodules, so its own fetches would toast twice for one 1613 // clone; it is counted and not told. 1614 if crate::clone::Repo::named(&repo).is_some_and(|repo| repo != crate::clone::Repo::Jevcrates) { 1615 self.broadcast(&Live::Toast(crate::view::toast::fetched(fetch, &repo).into_string())); 1616 self.activity(None); 1617 } 1618 Told::Noted 1619 } 1620 Ask::Home => Told::Home(self.shelf.home()?), 1621 Ask::Answer { input } => Told::Answer(self.shelf.answer(&input)?), 1622 Ask::Declined { input } => { 1623 self.shelf.decline(&input, js_sys::Date::now())?; 1624 Told::Noted 1625 } 1626 Ask::WasDeclined { input } => Told::WasDeclined(self.shelf.was_declined(&input)?), 1627 Ask::Older { after } => Told::Older(self.shelf.older(&after)?), 1628 Ask::Suggest { typed } => Told::Suggested(self.shelf.suggest(&typed)?), 1629 Ask::Rate { input, who, vote, browser } => Told::Rating(self.shelf.rate(&input, &who, &browser, vote)?), 1630 Ask::Rating { input, who } => Told::Rating(self.shelf.rating(&input, who.as_ref())?), 1631 Ask::Comment { input, who, browser, comment } => Told::Commented(self.shelf.comment(&input, &who, &browser, &comment)?), 1632 }; 1633 Response::ok(serde_json::to_string(&told).map_err(|e| worker::Error::from(e.to_string()))?) 1634 } 1635 1636 /// A page says nothing the archive listens to. 1637 async fn websocket_message(&self, _socket: WebSocket, _message: WebSocketIncomingMessage) -> worker::Result<()> { 1638 Ok(()) 1639 } 1640 1641 async fn websocket_close(&self, socket: WebSocket, _code: usize, _reason: String, _clean: bool) -> worker::Result<()> { 1642 // The page has gone: the event that said it came, again, as `left`. 1643 if let Some(seen) = seen(&socket) 1644 && let Some(came) = seen.event 1645 { 1646 let now = js_sys::Date::now(); 1647 let _ = self.shelf.left(came, now - seen.at_ms, now); 1648 } 1649 self.soon().await 1650 } 1651 1652 async fn websocket_error(&self, _socket: WebSocket, _error: worker::Error) -> worker::Result<()> { 1653 self.soon().await 1654 } 1655 1656 /// Pages came or went since the last time everyone was told. A page that 1657 /// came through a Worker that passes no place (during a deploy) is asked 1658 /// to reconnect once the deploy has settled, so it gets one; until then 1659 /// the alarm comes back for it. 1660 async fn alarm(&self) -> worker::Result<Response> { 1661 let now = js_sys::Date::now(); 1662 let mut waiting = false; 1663 for socket in self.open(None) { 1664 let seen = seen(&socket); 1665 if Seen::stale(seen.as_ref(), now) { 1666 let _ = socket.close(Some(1012), Some("reconnect to be placed")); 1667 } else if seen.is_some_and(|seen| !seen.passed) { 1668 waiting = true; 1669 } 1670 } 1671 let open = self.open(None); 1672 self.online(&open, &open); 1673 if waiting { 1674 self.state.storage().set_alarm(std::time::Duration::from_millis(::archive::REPLACE_AFTER_MS as u64)).await?; 1675 } 1676 Response::ok("") 1677 } 1678} 1679 1680async fn tell(env: &Env, ask: Ask) -> Result<Told, String> { 1681 crate::object::tell(env, BINDING, NAME, "the archive", &ask).await 1682} 1683 1684/// A call's response: kept, or sent now. Every Jev and LLM call the Worker 1685/// makes goes through here. 1686pub async fn call(env: &Env, ask: Ask) -> Result<Called, String> { 1687 match tell(env, ask).await? { 1688 Told::Called(called) => Ok(called), 1689 _ => Err("the archive answered a call with something else".into()), 1690 } 1691} 1692 1693/// Records that `input` was asked and what Jev said. A failure loses one 1694/// line of the feed and nothing else. 1695pub async fn asked(env: &Env, input: &str, answers: Vec<Answer>, llm: bool, listed: bool, browser: Option<&str>) { 1696 let who = browser.map(|browser| crate::asker(browser, input)); 1697 let _ = tell(env, Ask::Asked { input: input.to_owned(), answers, llm, listed, who, browser: browser.unwrap_or_default().to_owned() }).await; 1698} 1699 1700/// What Jev said to `input` the last time it was asked, if it ever was. 1701/// Reading it sends nothing, so a link preview costs nothing. 1702pub async fn answer(env: &Env, input: &str) -> Option<Entry> { 1703 match tell(env, Ask::Answer { input: input.to_owned() }).await { 1704 Ok(Told::Answer(entry)) => entry, 1705 _ => None, 1706 } 1707} 1708 1709/// Keeps that `input` was a question Jev cannot take. A failure loses the 1710/// card's wording for that one link and nothing else. 1711pub async fn declined(env: &Env, input: &str) { 1712 let _ = tell(env, Ask::Declined { input: input.to_owned() }).await; 1713} 1714 1715/// Was `input` a question Jev could not take, and never answered since? 1716/// Sends nothing to anyone, so a link preview costs nothing. 1717pub async fn was_declined(env: &Env, input: &str) -> bool { 1718 matches!(tell(env, Ask::WasDeclined { input: input.to_owned() }).await, Ok(Told::WasDeclined(true))) 1719} 1720 1721/// The feed and the numbers, for the home page. `None` if the archive 1722/// cannot be asked. 1723/// Someone cloned or pulled `repo`. 1724pub async fn fetched(env: &Env, repo: &str, fetch: Fetch) { 1725 let _ = tell(env, Ask::Fetched { repo: repo.to_owned(), fetch }).await; 1726} 1727 1728/// `/live`: the request, handed to the archive, which keeps the socket. 1729/// How long after pages come or go every page is told, at most once. 1730const ONLINE_EVERY_MS: u64 = 1000; 1731 1732/// The headers the Worker passes a page's place to the archive in. 1733pub const COUNTRY: &str = "x-lmjtfy-country"; 1734pub const CITY: &str = "x-lmjtfy-city"; 1735/// Set by a Worker that passes places on, with or without one to pass. 1736pub const PLACED: &str = "x-lmjtfy-placed"; 1737/// The request as an `archive::Event`, JSON and percent-encoded: who 1738/// connected. Set by the Worker, over any the page sent. 1739pub const EVENT: &str = "x-lmjtfy-event"; 1740 1741/// Keeps an event. A failure loses that one row and nothing else. 1742pub async fn event(env: &Env, event: ::archive::Event) { 1743 let _ = tell(env, Ask::Event(Box::new(event))).await; 1744} 1745 1746pub async fn live(env: &Env, request: Request) -> worker::Result<Response> { 1747 let stub = env.durable_object(BINDING)?.id_from_name(NAME)?.get_stub()?; 1748 stub.fetch_with_request(request).await 1749} 1750 1751/// `who`'s vote on Jev's answer to `input`, and the votes after it. `None` 1752/// if there is nothing to vote on or the archive cannot be asked. 1753pub async fn rate(env: &Env, input: &str, browser: &str, vote: Vote) -> Option<Rating> { 1754 match tell(env, Ask::Rate { input: input.to_owned(), who: crate::asker(browser, input), vote, browser: browser.to_owned() }).await { 1755 Ok(Told::Rating(rating)) => rating, 1756 _ => None, 1757 } 1758} 1759 1760/// `who`'s comment on their vote on Jev's answer to `input`. The text goes to 1761/// the archive and nowhere else: a failure says only that it failed. 1762pub async fn comment(env: &Env, input: &str, browser: &str, comment: String) -> Result<(), Unsaid> { 1763 match tell(env, Ask::Comment { input: input.to_owned(), who: crate::asker(browser, input), browser: browser.to_owned(), comment }).await { 1764 Ok(Told::Commented(done)) => done, 1765 _ => Err(Unsaid::Failed), 1766 } 1767} 1768 1769/// The votes on Jev's answer to `input`, and `who`'s. 1770pub async fn rating(env: &Env, input: &str, who: Option<Asker>) -> Option<Rating> { 1771 match tell(env, Ask::Rating { input: input.to_owned(), who }).await { 1772 Ok(Told::Rating(rating)) => rating, 1773 _ => None, 1774 } 1775} 1776 1777/// The page of the feed after `after`. `None` if the archive cannot be 1778/// asked. 1779pub async fn older(env: &Env, after: Cursor) -> Option<Vec<Entry>> { 1780 match tell(env, Ask::Older { after }).await { 1781 Ok(Told::Older(entries)) => Some(entries), 1782 _ => None, 1783 } 1784} 1785 1786/// The feed's questions that what has been typed could be the start of. 1787/// Nothing, if the archive cannot be asked: a suggestion is a convenience. 1788pub async fn suggest(env: &Env, typed: &str) -> Vec<Entry> { 1789 match tell(env, Ask::Suggest { typed: typed.to_owned() }).await { 1790 Ok(Told::Suggested(entries)) => entries, 1791 _ => Vec::new(), 1792 } 1793} 1794 1795pub async fn home(env: &Env) -> Option<Home> { 1796 match tell(env, Ask::Home).await { 1797 Ok(Told::Home(home)) => Some(home), 1798 _ => None, 1799 } 1800} 1801 1802#[cfg(test)] 1803mod tests { 1804 use super::{FINISHED, MIGRATIONS, finish_sql}; 1805 1806 /// Every `(table, column)` the migrations make: `CREATE TABLE` columns 1807 /// and `ALTER TABLE ... ADD COLUMN`. 1808 fn columns() -> Vec<(String, String)> { 1809 let mut found = Vec::new(); 1810 for statement in MIGRATIONS.iter().flat_map(|step| step.iter()) { 1811 let words: Vec<&str> = statement.split_whitespace().collect(); 1812 // A table a later step drops is not in the archive any more. 1813 if words.first() == Some(&"DROP") { 1814 let dropped = words.last().copied().unwrap_or_default(); 1815 found.retain(|(table, _)| table != dropped); 1816 continue; 1817 } 1818 if let Some(at) = words.iter().position(|word| *word == "TABLE") { 1819 // A `_finished` table has its view's columns, which a test 1820 // below holds; the views are in the README's table. 1821 if words.get(at + 1).is_some_and(|table| table.ends_with("_finished")) { 1822 continue; 1823 } 1824 let table = words[at..].iter().find(|word| !matches!(**word, "TABLE" | "IF" | "NOT" | "EXISTS")).copied(); 1825 let Some(table) = table.map(|table| table.trim_end_matches('(')) else { continue }; 1826 if words.first() == Some(&"CREATE") { 1827 let body = statement.split_once('(').map(|(_, body)| body).unwrap_or_default(); 1828 // Columns until the table's own key clause, whose list of 1829 // names is not columns. 1830 for line in body.split(',') { 1831 let column = line.split_whitespace().next().unwrap_or_default(); 1832 if column == "PRIMARY" { 1833 break; 1834 } 1835 if !column.is_empty() { 1836 found.push((table.to_owned(), column.to_owned())); 1837 } 1838 } 1839 } else if let Some(at) = words.iter().position(|word| *word == "COLUMN") { 1840 found.push((table.to_owned(), words[at + 1].to_owned())); 1841 } 1842 } 1843 } 1844 found 1845 } 1846 1847 const DAY_MS: f64 = 86_400_000.0; 1848 1849 /// An archive as the migrations make it, each step all or nothing. 1850 fn archive() -> rusqlite::Connection { 1851 let mut archive = rusqlite::Connection::open_in_memory().unwrap(); 1852 // As `Shelf::migrate` begins. 1853 archive.execute("CREATE TABLE IF NOT EXISTS migrated (version INTEGER PRIMARY KEY)", []).unwrap(); 1854 for (index, step) in MIGRATIONS.iter().enumerate() { 1855 let all = archive.transaction().unwrap(); 1856 for statement in *step { 1857 all.execute(statement, []).unwrap_or_else(|error| panic!("step {}: {error}\n{statement}", index + 1)); 1858 } 1859 all.execute("INSERT INTO migrated (version) VALUES (?)", [index as i64 + 1]).unwrap(); 1860 all.commit().unwrap(); 1861 } 1862 archive 1863 } 1864 1865 fn happened(archive: &rusqlite::Connection, at_ms: f64, event: &::archive::Event) { 1866 let columns = ::archive::Event::COLUMNS; 1867 let mut values: Vec<rusqlite::types::Value> = vec![at_ms.into()]; 1868 values.extend(event.values().into_iter().map(|cell| match cell { 1869 ::archive::event::Cell::Text(text) => text.into(), 1870 ::archive::event::Cell::Number(number) => number.into(), 1871 ::archive::event::Cell::Null => rusqlite::types::Value::Null, 1872 })); 1873 let marks = vec!["?"; values.len()].join(", "); 1874 archive.execute(&format!("INSERT INTO events (at_ms, {}) VALUES ({marks})", columns.join(", ")), rusqlite::params_from_iter(values)).unwrap(); 1875 } 1876 1877 /// Every row of `sql`, as text, in order. 1878 fn rows(archive: &rusqlite::Connection, sql: &str) -> Vec<String> { 1879 let mut statement = archive.prepare(sql).unwrap(); 1880 let width = statement.column_count(); 1881 let rows = statement.query_map([], |row| Ok((0..width).map(|at| format!("{:?}", row.get_unwrap::<_, rusqlite::types::Value>(at))).collect::<Vec<_>>().join("|"))).unwrap(); 1882 let mut rows: Vec<String> = rows.map(Result::unwrap).collect(); 1883 rows.sort(); 1884 rows 1885 } 1886 1887 /// Three days of a few visitors: a browser that comes back, one from a 1888 /// link, a bot, and a script with no cookie. 1889 fn visited(archive: &rusqlite::Connection) { 1890 let event = |what: &str, path: &str, client: &str, browser: &str, ip: &str, country: &str, city: &str| ::archive::Event { 1891 what: what.into(), 1892 path: path.into(), 1893 client: client.into(), 1894 browser: browser.into(), 1895 status: 200.0, 1896 family: "Firefox".into(), 1897 os: "Linux".into(), 1898 origin: ::archive::event::Origin { ip: ip.into(), country: country.into(), city: city.into(), network: "A Net".into(), ..Default::default() }, 1899 ..Default::default() 1900 }; 1901 for day in 100..103 { 1902 let at = |hour: f64| (f64::from(day) + hour / 24.0) * DAY_MS; 1903 happened(archive, at(1.0), &::archive::Event { first: f64::from(day == 100), daily: 1.0, session: 1.0, ..event("view", "/", "browser", "a", "1.1.1.1", "US", "Austin") }); 1904 happened(archive, at(1.1), &event("gate", "/gate", "browser", "a", "1.1.1.1", "US", "Austin")); 1905 happened(archive, at(1.2), &::archive::Event { input: "is it?".into(), detail: "answered".into(), ..event("answer", "/ask", "browser", "a", "1.1.1.1", "US", "Austin") }); 1906 happened(archive, at(5.0), &event("view", "/rules", "bot", "", "9.9.9.9", "DE", "")); 1907 happened(archive, at(5.0), &::archive::Event { status: 404.0, ..event("view", "/.env", "other", "", "8.8.8.8", "", "") }); 1908 } 1909 happened(archive, 101.5 * DAY_MS, &::archive::Event { referrer: "https://news.example/item?id=1".into(), first: 1.0, daily: 1.0, session: 1.0, ..event("view", "/", "browser", "b", "2.2.2.2", "GB", "Bexley") }); 1910 happened(archive, 101.6 * DAY_MS, &::archive::Event { took_ms: 9000.0, ..event("read", "/", "browser", "b", "2.2.2.2", "GB", "Bexley") }); 1911 happened(archive, 101.7 * DAY_MS, &::archive::Event { referrer: "https://lmjtfy.fun/rules".into(), ..event("view", "/rules", "browser", "b", "2.2.2.2", "GB", "Bexley") }); 1912 } 1913 1914 #[test] 1915 fn every_migration_runs_and_makes_what_is_finished() { 1916 let archive = archive(); 1917 let mut tables = rows(&archive, "SELECT substr(name, 1, length(name) - 9) FROM sqlite_master WHERE type = 'table' AND name LIKE '%\\_finished' ESCAPE '\\'"); 1918 let mut named = rows(&archive, "SELECT name FROM finished"); 1919 let mut wanted: Vec<String> = FINISHED.iter().map(|name| format!("Text({name:?})")).collect(); 1920 (tables.sort(), named.sort(), wanted.sort()); 1921 assert_eq!(tables, wanted); 1922 assert_eq!(named, wanted); 1923 for name in FINISHED { 1924 // The table is the shape of the view it is copied from, and of the one that is asked. 1925 let columns = |of: &str| rows(&archive, &format!("SELECT cid, name FROM pragma_table_info('{of}')")); 1926 assert_eq!(columns(&format!("{name}_finished")), columns(&format!("{name}_live")), "{name}"); 1927 assert_eq!(columns(&format!("{name}_finished")), columns(name), "{name}"); 1928 } 1929 } 1930 1931 #[test] 1932 fn a_finished_day_is_what_it_was_live() { 1933 let archive = archive(); 1934 visited(&archive); 1935 let all = |archive: &rusqlite::Connection| FINISHED.map(|name| rows(archive, &format!("SELECT * FROM {name}"))); 1936 let live = all(&archive); 1937 assert!(live.iter().all(|rows| !rows.is_empty()), "{live:?}"); 1938 // Days 100 and 101 are over; 102 is today. A day finished again is left as it is. 1939 for _ in 0..2 { 1940 for name in FINISHED { 1941 let [put_aside, copy] = finish_sql(name); 1942 for day in [100, 101] { 1943 archive.execute(&put_aside, [day]).unwrap(); 1944 archive.execute(©, [day]).unwrap(); 1945 archive.execute("UPDATE finished SET through = ? WHERE name = ?", rusqlite::params![day, name]).unwrap(); 1946 } 1947 } 1948 } 1949 assert_eq!(all(&archive), live); 1950 for name in FINISHED { 1951 // What is over is in the table, and only today is read from `events`. 1952 assert_eq!(rows(&archive, &format!("SELECT COUNT(*) FROM {name}_finished WHERE day NOT IN (100, 101)")), ["Integer(0)"], "{name}"); 1953 assert_eq!(rows(&archive, &format!("SELECT COUNT(*) FROM {name}_live WHERE day != 102")), ["Integer(0)"], "{name}"); 1954 } 1955 } 1956 1957 #[test] 1958 fn the_views_count_what_the_site_means() { 1959 let archive = archive(); 1960 visited(&archive); 1961 // A page given to a browser is a view; a bot's and one not found are not. 1962 assert_eq!(rows(&archive, "SELECT day, views, visitors, new, asks, answered, read_ms FROM days"), ["Integer(100)|Integer(1)|Real(1.0)|Real(1.0)|Integer(1)|Integer(1)|Integer(0)", "Integer(101)|Integer(3)|Real(2.0)|Real(1.0)|Integer(1)|Integer(1)|Real(9000.0)", "Integer(102)|Integer(1)|Real(1.0)|Real(0.0)|Integer(1)|Integer(1)|Integer(0)"]); 1963 assert_eq!(rows(&archive, "SELECT label, n FROM pages_by_day WHERE day = 101"), ["Text(\"/\")|Integer(2)", "Text(\"/rules\")|Integer(1)"]); 1964 assert_eq!(rows(&archive, "SELECT label, n FROM questions_by_day WHERE day = 100"), ["Text(\"is it?\")|Integer(1)"]); 1965 // The site's own pages are not where a visitor came from. 1966 assert_eq!(rows(&archive, "SELECT day, label, n FROM referrers_by_day"), ["Integer(101)|Text(\"news.example\")|Integer(1)"]); 1967 // Browser a came on all three days: three days' numbers, one visitor. 1968 assert_eq!(rows(&archive, "SELECT label, SUM(n) FROM countries_by_day GROUP BY label"), ["Text(\"GB\")|Integer(1)", "Text(\"US\")|Integer(3)"]); 1969 assert_eq!(rows(&archive, "SELECT label, COUNT(DISTINCT who) FROM country_visitors GROUP BY label"), ["Text(\"GB\")|Integer(1)", "Text(\"US\")|Integer(1)"]); 1970 assert_eq!(rows(&archive, "SELECT label, n FROM cities_by_day WHERE day = 101"), ["Text(\"Austin, US\")|Integer(1)", "Text(\"Bexley, GB\")|Integer(1)"]); 1971 assert_eq!(rows(&archive, "SELECT label, n FROM agents_by_day WHERE day = 101"), ["Text(\"Firefox on Linux\")|Integer(2)"]); 1972 // The trees: a page under a folder is a hit on the folder too, and the top has no parent. 1973 happened(&archive, 101.8 * DAY_MS, &::archive::Event { what: "view".into(), path: "/lmjtfy.git/apps".into(), client: "browser".into(), browser: "b".into(), status: 200.0, ..Default::default() }); 1974 assert_eq!(rows(&archive, "SELECT page, views, browsers, by_bots, not_found FROM pages WHERE parent IS NULL"), ["Text(\"/\")|Integer(4)|Integer(2)|Integer(0)|Integer(0)", "Text(\"/.env\")|Integer(0)|Integer(0)|Integer(0)|Integer(3)", "Text(\"/lmjtfy.git\")|Integer(1)|Integer(1)|Integer(0)|Integer(0)", "Text(\"/rules\")|Integer(1)|Integer(1)|Integer(3)|Integer(0)"]); 1975 assert_eq!(rows(&archive, "SELECT page, has_more FROM pages WHERE parent = '/lmjtfy.git'"), ["Text(\"/lmjtfy.git/apps\")|Integer(0)"]); 1976 assert_eq!(rows(&archive, "SELECT place, kind, browsers, addresses FROM places WHERE parent IS NULL"), ["Text(\"DE\")|Text(\"country\")|Integer(0)|Integer(1)", "Text(\"GB\")|Text(\"country\")|Integer(1)|Integer(1)", "Text(\"US\")|Text(\"country\")|Integer(1)|Integer(1)"]); 1977 assert_eq!(rows(&archive, "SELECT place, kind FROM places WHERE parent = 'GB / ?'"), ["Text(\"GB / ? / Bexley\")|Text(\"city\")"]); 1978 assert_eq!(rows(&archive, "SELECT browser, viewed, typed, asked, first FROM browser_days WHERE day = 101"), ["Text(\"a\")|Integer(1)|Integer(1)|Integer(1)|Real(0.0)", "Text(\"b\")|Integer(1)|Integer(0)|Integer(0)|Real(1.0)"]); 1979 } 1980 1981 #[test] 1982 fn what_is_kept_can_be_added_to_and_not_changed() { 1983 let archive = archive(); 1984 visited(&archive); 1985 archive.execute("INSERT INTO versions (sent_to, request, version, response, attempts, took_ms, answered_ms) VALUES ('jev', '{}', 1, '{}', 1, 1.0, 1.0)", []).unwrap(); 1986 for (statement, why) in [ 1987 ("UPDATE versions SET response = 'other'", "a kept response is never changed"), 1988 ("DELETE FROM versions", "a kept response is never deleted"), 1989 ("UPDATE events SET input = ''", "an event is never changed"), 1990 ("DELETE FROM events WHERE what = 'gate'", "an event is never deleted"), 1991 ] { 1992 let refused = archive.execute(statement, []).unwrap_err().to_string(); 1993 assert!(refused.contains(why), "{statement}: {refused}"); 1994 } 1995 assert_eq!(rows(&archive, "SELECT COUNT(*) FROM versions"), ["Integer(1)"]); 1996 } 1997 1998 #[test] 1999 fn the_feed_is_read_in_its_order_from_an_index() { 2000 let archive = archive(); 2001 // `asked_lately` went in step 18: the feed's own index is what reads it. 2002 let plan = rows(&archive, "EXPLAIN QUERY PLAN SELECT input FROM asked WHERE COALESCE(moderated, listed) = 1 ORDER BY asked_ms DESC, input DESC LIMIT 50").join(" "); 2003 assert!(plan.contains("asked_feed") && !plan.contains("TEMP B-TREE"), "{plan}"); 2004 assert_eq!(rows(&archive, "SELECT name FROM pragma_table_info('migrated')"), ["Text(\"at_ms\")", "Text(\"build\")", "Text(\"version\")"]); 2005 } 2006 2007 #[test] 2008 fn a_comment_is_read_beside_its_vote_and_never_changed() { 2009 let archive = archive(); 2010 let rate = |who: &str, vote: i64| archive.execute("INSERT INTO ratings (input, answer, who, vote, browser) VALUES ('is it?', 'h', ?, ?, ?)", rusqlite::params![who, vote, format!("b-{who}")]).unwrap(); 2011 let say = |who: &str, vote: i64, text: &str| archive.execute("INSERT INTO comments (at_ms, input, answer, who, browser, vote, comment) VALUES (1000, 'is it?', 'h', ?, ?, ?, ?)", rusqlite::params![who, format!("b-{who}"), vote, text]).unwrap(); 2012 rate("a", 1); 2013 rate("b", -1); 2014 rate("c", 1); 2015 say("a", 1, "first"); 2016 say("a", 1, "<b>second</b>"); 2017 say("b", -1, "gone"); 2018 archive.execute("DELETE FROM ratings WHERE who = 'b'", []).unwrap(); 2019 // Every comment, with the vote then and now, and whether it is the latest. 2020 assert_eq!( 2021 rows(&archive, "SELECT browser, vote_then, vote_now, latest, comment FROM vote_comments"), 2022 [ 2023 "Text(\"b-a\")|Text(\"up\")|Text(\"up\")|Integer(0)|Text(\"first\")", 2024 "Text(\"b-a\")|Text(\"up\")|Text(\"up\")|Integer(1)|Text(\"<b>second</b>\")", 2025 "Text(\"b-b\")|Text(\"down\")|Null|Integer(1)|Text(\"gone\")", 2026 ] 2027 ); 2028 // Every vote standing, with its latest comment or none. 2029 assert_eq!( 2030 rows(&archive, "SELECT browser, vote, comment FROM votes_commented"), 2031 ["Text(\"b-a\")|Text(\"up\")|Text(\"<b>second</b>\")", "Text(\"b-c\")|Text(\"up\")|Null"] 2032 ); 2033 for refused in ["UPDATE comments SET comment = 'x'", "DELETE FROM comments"] { 2034 assert!(archive.execute(refused, []).unwrap_err().to_string().contains("a comment is never"), "{refused}"); 2035 } 2036 } 2037 2038 #[test] 2039 fn the_events_table_is_the_shape_of_an_event() { 2040 let made: Vec<String> = columns().into_iter().filter(|(table, _)| table == "events").map(|(_, column)| column).collect(); 2041 let mut wanted = vec!["id", "at_ms"]; 2042 wanted.extend(::archive::Event::COLUMNS); 2043 // Worked out from `at_ms`, not written. 2044 wanted.push("day"); 2045 assert_eq!(made, wanted); 2046 } 2047 2048 #[test] 2049 fn the_readme_diagram_has_every_column_the_migrations_make() { 2050 let readme = include_str!("../README.md"); 2051 let diagram = readme.split("### The archive").nth(1).and_then(|rest| rest.split("```").nth(1)).expect("the archive's erDiagram"); 2052 let columns = columns(); 2053 assert!(columns.len() >= 15, "{columns:?}"); 2054 for (table, column) in columns { 2055 let entity = diagram.split(&format!(" {table} {{")).nth(1).and_then(|rest| rest.split('}').next()); 2056 let entity = entity.unwrap_or_else(|| panic!("the diagram has no `{table}`")); 2057 assert!( 2058 entity.lines().any(|line| line.split_whitespace().nth(1) == Some(column.as_str())), 2059 "the diagram's `{table}` lacks `{column}`" 2060 ); 2061 } 2062 assert!(!diagram.contains(" calls {"), "`calls` became `versions` (MIGRATIONS step 7)"); 2063 } 2064 2065 #[test] 2066 fn a_vote_on_a_declined_question_is_keyed_apart_and_tied_to_its_record() { 2067 use sha2::{Digest, Sha256}; 2068 let archive = archive(); 2069 let hash: String = Sha256::digest(b"[]").iter().map(|byte| format!("{byte:02x}")).collect(); 2070 assert_ne!(hash, super::DECLINED); 2071 archive.execute("INSERT INTO declined (input, declined_ms) VALUES ('what is rayleigh scattering?', 5)", []).unwrap(); 2072 archive.execute("INSERT INTO ratings (input, answer, who, vote, browser) VALUES ('what is rayleigh scattering?', 'declined', 'w', -1, 'b-w')", []).unwrap(); 2073 archive.execute("INSERT INTO ratings (input, answer, who, vote, browser) VALUES ('is it?', ?, 'w', 1, 'b-w')", [&hash]).unwrap(); 2074 archive.execute("INSERT INTO comments (at_ms, input, answer, who, browser, vote, comment) VALUES (7, 'what is rayleigh scattering?', 'declined', 'w', 'b-w', -1, 'it is physics')", []).unwrap(); 2075 // Which kind a row is, worked out and not written. 2076 assert_eq!(rows(&archive, "SELECT input, about FROM ratings ORDER BY input"), ["Text(\"is it?\")|Text(\"answered\")", "Text(\"what is rayleigh scattering?\")|Text(\"declined\")"]); 2077 assert!(archive.execute("INSERT INTO ratings (input, answer, who, vote, browser, about) VALUES ('x', 'y', 'z', 1, 'b', 'declined')", []).is_err()); 2078 // The admin can join a vote to the declined record, and a comment to its vote. 2079 assert_eq!( 2080 rows(&archive, "SELECT d.declined_ms, v.about, v.vote, v.comment FROM votes_commented v JOIN declined d ON d.input = v.input AND v.about = 'declined'"), 2081 ["Real(5.0)|Text(\"declined\")|Text(\"down\")|Text(\"it is physics\")"] 2082 ); 2083 assert_eq!(rows(&archive, "SELECT about, vote_then, vote_now, latest FROM vote_comments"), ["Text(\"declined\")|Text(\"down\")|Text(\"down\")|Integer(1)"]); 2084 } 2085 2086 #[test] 2087 fn step_21_leaves_the_old_rows_readable_as_answered() { 2088 // An archive as it was at step 20, with a vote and a comment on an answer. 2089 let mut archive = rusqlite::Connection::open_in_memory().unwrap(); 2090 archive.execute("CREATE TABLE IF NOT EXISTS migrated (version INTEGER PRIMARY KEY)", []).unwrap(); 2091 let run = |archive: &mut rusqlite::Connection, steps: std::ops::Range<usize>| { 2092 for index in steps { 2093 let all = archive.transaction().unwrap(); 2094 for statement in MIGRATIONS[index] { 2095 all.execute(statement, []).unwrap_or_else(|error| panic!("step {}: {error}", index + 1)); 2096 } 2097 all.commit().unwrap(); 2098 } 2099 }; 2100 run(&mut archive, 0..20); 2101 archive.execute("INSERT INTO ratings (input, answer, who, vote, browser) VALUES ('is it?', 'abc', 'w', 1, 'b')", []).unwrap(); 2102 archive.execute("INSERT INTO comments (at_ms, input, answer, who, browser, vote, comment) VALUES (1, 'is it?', 'abc', 'w', 'b', 1, 'yes')", []).unwrap(); 2103 run(&mut archive, 20..21); 2104 assert_eq!(rows(&archive, "SELECT about, vote, comment FROM votes_commented"), ["Text(\"answered\")|Text(\"up\")|Text(\"yes\")"]); 2105 assert_eq!(rows(&archive, "SELECT about FROM vote_comments"), ["Text(\"answered\")"]); 2106 } 2107}