Changeset 9946a68 in Klonkt for src/services

Timestamp:

09/04/2026 12:12:19 PM (5 days ago)

Author:

Robin <roboburr@…>

Branches:

main

Children:

1d1fdc9

Parents:

8151340

Message:

Tijdstempels, bronkant: een spelling bij het schrijven, plus migratie (shaer-a937)

De leeskant van 8151340 is het vangnet; dit haalt de oorzaak weg. Zolang
er twee vormen binnenkomen blijft elke nieuwe query een kans om het
opnieuw fout te doen.

ZES schrijfwegen, niet vier: naast ap_timeline, ap_interactions,
ap_outbox en ap_mentions bleken ook guardianship/delivery (directe notes)
en de archief-import in dezelfde kolommen te schrijven. Alle zes leveren
nu ISO via NU_ISO uit config/database.js, waar isoSql ook staat -- wat je
schrijft en waarmee je vergelijkt horen bij elkaar en dus op een plek.

MIGRATIE voor wat er al stond, in het bestaande idempotente patroon in
initializeDatabase: alleen rijen in de CURRENT_TIMESTAMP-vorm, alleen als
strftime ze begrijpt. Een ISO-stempel blijft ongemoeid en een onleesbare
waarde wordt niet weggegooid -- die bewaar je, ook al weet je er niets
mee.

VIER VERGELIJKINGEN die het omgekeerde deden: een kolom RAUW tegen
datetime('now') leggen. Dat is dezelfde fout gespiegeld, en hij zou juist
door deze release gaan bijten. Het gaat om de wachtwoord-reset (de bead
noemt shaer-1evq), het opruimen van geziene notes en twee queries in de
sessieopslag -- de bead schreef al dat daar de opruiming en de telling
ernaast zaten. Alle vier nu datetime(kolom) tegen datetime('now'),
hetzelfde patroon dat Scheduler al gebruikt.

BEWUST NIET: de 49 DEFAULT CURRENT_TIMESTAMP in het schema. Die vragen om
een tabel-herbouw per stuk, en ze vuren alleen als een INSERT de kolom
overslaat -- wat bij de tabellen die het betreft niet gebeurt, want daar
staat created_at expliciet in het statement.

Drie toetsen erbij, elk met eigen tegenbewijs. Een ervan moest overnieuw:
hij schreef zijn eigen ISO en kon dus niet falen op wat ik veranderde;
nu gaat hij door upsertBoostedNote, de echte schrijfweg. Onderweg zette
ik SQL met enkele quotes in een JS-string met enkele quotes -- vandaar
NU_ISO als constante in template literals, en node --check op elk
aangeraakt bestand.

Volle suite 1255 groen.

Location:

src/services

Files:

• ActivityPubService.js (modified) (2 diffs)
• ArchiveImportService.js (modified) (2 diffs)
• SqliteSessionStore.js (modified) (1 diff)
• ap-inbox.js (modified) (3 diffs)
• ap-timeline.js (modified) (2 diffs)
• guardianship/delivery.js (modified) (2 diffs)

src/services/ActivityPubService.js

@@ -19 +19 @@
 import fs from 'fs';
 import path from 'path';
-import db from '../config/database.js';
+import db, { NU_ISO } from '../config/database.js';
 import HtmlSanitizerService from './HtmlSanitizerService.js';
 import AudioEmbedService from './AudioEmbedService.js';
@@ -1482 +1482 @@
 function iStmts() {
   if (!_insI) {
-    _insI = db.prepare('INSERT OR IGNORE INTO ap_interactions (kind, post_id, object_uri, actor_uri, actor_name, actor_handle, actor_url, actor_icon, content, published, parent_uri, visibility, emoji_json, actor_emoji_json, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)');
+    _insI = db.prepare(`INSERT OR IGNORE INTO ap_interactions (kind, post_id, object_uri, actor_uri, actor_name, actor_handle, actor_url, actor_icon, content, published, parent_uri, visibility, emoji_json, actor_emoji_json, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,${NU_ISO})`);
     _delLA = db.prepare('DELETE FROM ap_interactions WHERE kind = ? AND post_id = ? AND actor_uri = ?');
     _delReply = db.prepare("DELETE FROM ap_interactions WHERE kind = 'reply' AND object_uri = ?");
     _listI = db.prepare('SELECT id, kind, object_uri, parent_uri, actor_uri, actor_name, actor_handle, actor_url, actor_icon, content, published, created_at, acted_boost, acted_like, visibility, emoji_json, actor_emoji_json FROM ap_interactions WHERE post_id = ? ORDER BY created_at ASC');
     _getI = db.prepare('SELECT * FROM ap_interactions WHERE id = ?');
-    _insO = db.prepare('INSERT INTO ap_outbox (id, site_slug, post_id, post_slug, in_reply_to, to_actor, to_handle, content, language, attachments, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)');
+    _insO = db.prepare(`INSERT INTO ap_outbox (id, site_slug, post_id, post_slug, in_reply_to, to_actor, to_handle, content, language, attachments, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,${NU_ISO})`);
     _listO = db.prepare('SELECT * FROM ap_outbox WHERE post_id = ? ORDER BY created_at ASC');
     _getO = db.prepare('SELECT * FROM ap_outbox WHERE id = ?');

src/services/ArchiveImportService.js

@@ -23 +23 @@
 import crypto from 'crypto';
 import { randomUUID } from 'crypto';
-import db from '../config/database.js';
+import db, { NU_ISO } from '../config/database.js';
 import { MEDIA_ROOT, AUDIO_ROOT } from '../config/paths.js';
 import { FORMAT_VERSION, parseFollowingCsv } from './ArchiveExportService.js';
@@ -413 +413 @@
   const insReply = db.prepare(`INSERT OR IGNORE INTO ap_interactions
     (kind, post_id, object_uri, actor_uri, actor_name, actor_handle, content, published, parent_uri, created_at)
-    VALUES ('reply', ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)`);
+    VALUES ('reply', ?, ?, ?, ?, ?, ?, ?, ?, ${NU_ISO})`);
 
   db.transaction(() => {

src/services/SqliteSessionStore.js

@@ -35 +35 @@
     this._stmtDestroy = db.prepare('DELETE FROM sessions WHERE sid = ?');
     this._stmtTouch = db.prepare('UPDATE sessions SET expiresAt = ? WHERE sid = ?');
-    this._stmtCount = db.prepare("SELECT COUNT(*) AS c FROM sessions WHERE expiresAt > datetime('now')");
+    this._stmtCount = db.prepare("SELECT COUNT(*) AS c FROM sessions WHERE datetime(expiresAt) > datetime('now')");
     this._stmtClear = db.prepare('DELETE FROM sessions');
-    this._stmtAll = db.prepare("SELECT sid, data FROM sessions WHERE expiresAt > datetime('now')");
-    this._stmtGc = db.prepare("DELETE FROM sessions WHERE expiresAt <= datetime('now')");
+    this._stmtAll = db.prepare("SELECT sid, data FROM sessions WHERE datetime(expiresAt) > datetime('now')");
+    this._stmtGc = db.prepare("DELETE FROM sessions WHERE datetime(expiresAt) <= datetime('now')");
   }
 

src/services/ap-inbox.js

@@ -17 +17 @@
  * guardian-broers in de dienst, zoals gateOutgoingFollow bij stap 7.
  */
-import db from '../config/database.js';
+import db, { NU_ISO } from '../config/database.js';
 import HtmlSanitizerService from './HtmlSanitizerService.js';
 import * as Guardianship from './guardianship/index.js';
@@ -130 +130 @@
     if (++_seenSinceSnoei >= 500) {
       _seenSinceSnoei = 0;
-      const r = db.prepare(`DELETE FROM ap_seen_notes WHERE created_at < datetime('now', '-${SEEN_NOTES_DAYS} days')`).run();
+      const r = db.prepare(`DELETE FROM ap_seen_notes WHERE datetime(created_at) < datetime('now', '-${SEEN_NOTES_DAYS} days')`).run();
       if (r.changes) console.log(`[AP] seen notes: ${r.changes} pruned`);
     }
@@ -751 +751 @@
             const ouder = safeUrl(typeof o.inReplyTo === 'string' ? o.inReplyTo : (o.inReplyTo && o.inReplyTo.id)) || null;
             const r = db.prepare(`INSERT OR IGNORE INTO ap_mentions (slug, object_uri, note_url, actor_uri, actor_name, actor_handle, actor_icon, actor_url, content, published, in_reply_to, help_request, wave, has_guardians, emoji_json, actor_emoji_json, media_json, created_at)
-                                  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)`)
+                                  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,${NU_ISO})`)
               .run(slug, o.id, safeUrl(o.url) || null, actorUri, ai.name, ai.handle, ai.icon, ai.url, html, o.published || null, ouder, help ? 1 : 0, wave ? 1 : 0, hasG ? 1 : 0,
                 extractEmojiTags(o.tag), emojiJsonOf(ai.emojis), mediaFromNote(o));

src/services/ap-timeline.js

@@ -13 +13 @@
  * wireTimeline, hetzelfde injectiepatroon als guardianship en ap-c2s.
  */
-import db, { isoSql } from '../config/database.js';
+import db, { isoSql, NU_ISO } from '../config/database.js';
 
 // De helper woont sinds shaer-a937 in config/database.js: elke plek die
@@ -30 +30 @@
 export function tlStmts() {
   if (!_insTl) {
-    _insTl = db.prepare('INSERT OR IGNORE INTO ap_timeline (id, slug, author_uri, author_name, author_handle, author_icon, author_url, content, url, published, media_json, nsfw, cw, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)');
+    _insTl = db.prepare(`INSERT OR IGNORE INTO ap_timeline (id, slug, author_uri, author_name, author_handle, author_icon, author_url, content, url, published, media_json, nsfw, cw, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,${NU_ISO})`);
     _listTl = db.prepare(`SELECT * FROM ap_timeline WHERE slug = ? ORDER BY ${STEMPEL('COALESCE(published, created_at)')} DESC LIMIT ? OFFSET ?`);
     _delTl = db.prepare('DELETE FROM ap_timeline WHERE id = ?');

src/services/guardianship/delivery.js

@@ -12 +12 @@
  */
 import crypto from 'crypto';
-import db from '../../config/database.js';
+import db, { NU_ISO } from '../../config/database.js';
 import { carriesGuardians } from './context.js';
 
@@ -124 +124 @@
   const id = crypto.randomUUID();
   db.prepare(`INSERT INTO ap_outbox (id, site_slug, post_id, post_slug, in_reply_to, to_actor, to_handle, content, language, attachments, visibility, to_actors, help_request, wave, away_until, created_at)
-              VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)`)
+              VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,${NU_ISO})`)
     .run(id, site.slug, '', null, inReplyTo || null, resolved[0].uri, resolved[0].handle, content, lang, media.length ? JSON.stringify(media) : null, 'direct', JSON.stringify(resolved.map((r) => r.uri)), helpRequest ? 1 : 0, wave ? 1 : 0, awayUntil || null);
   const row = getOutboxRow(id);