Repository navigation
Expand file tree
/
Copy pathschema_db0.12.sql
More file actions
555 lines (527 loc) · 21.1 KB
/
Copy pathschema_db0.12.sql
File metadata and controls
555 lines (527 loc) · 21.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
-- schema_db0.12.sql
--
-- The Ragger memory schema at db_version '0.12'. Building on v4:
-- 1. `turn_summaries` split out of `summaries` — level='turn' rows move to
-- their own table with a real `turn_id` FK (ON DELETE SET NULL), so a
-- pruned raw turn's summary survives with an explicit "no raw backing"
-- marker instead of being matched by a fragile (session_id, created_at)
-- compound key. `summaries` now only ever holds
-- 'episode' | 'session' | 'project' rows.
-- 2. Every timestamp column across all tables becomes INTEGER (Unix epoch
-- seconds — no milliseconds; nothing in this codebase needs sub-second
-- precision). EXCEPTION: `documents.imported_at` stays TEXT 'YYYYMMDD'
-- — it's a date-only batch-grouping key, not a real timestamp, and
-- `documents` rows exist specifically to keep metadata in its most
-- legible form.
-- 3. `users.created`/`users.modified` renamed to `users.created_at`/
-- `users.updated_at` to match every other table's naming convention.
-- 4. A `_view` per table exposes epoch columns as human-readable
-- `datetime(...)` strings and collapses `embedding`/`phon` BLOB/TEXT
-- columns to `has_embedding`/`has_phon` (0/1) — these views are meant
-- to be opened directly in a plain SQLite browser by a human; raw
-- embedding bytes and phonetic codes are noise in that context.
--
-- Column order per Reid's preferred pattern (unchanged from v4):
-- 1. Primary key (id)
-- 2. Unique key fields (if any)
-- 3. Supporting data fields
-- 4. created/modified timestamps
-- 5. embedding and phon columns (at the end, after timestamps)
--
-- Applies to all BASE TABLES only. FTS5 virtual tables and their triggers
-- reference base table rows by rowid and are unaffected by the timestamp
-- type change.
--
-- ---------------------------------------------------------------------------
-- users -- who else has read access to documents.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
token_hash TEXT NOT NULL,
password_hash TEXT,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE TRIGGER IF NOT EXISTS users_modified
AFTER UPDATE ON users
BEGIN
UPDATE users SET updated_at = unixepoch() WHERE id = NEW.id;
END;
CREATE VIEW IF NOT EXISTS users_view AS
SELECT id, username, token_hash, password_hash,
datetime(created_at, 'unixepoch', 'localtime') AS created_at,
datetime(updated_at, 'unixepoch', 'localtime') AS updated_at
FROM users;
-- ---------------------------------------------------------------------------
-- models -- lookup; turns + summaries + turn_summaries reference it
-- ---------------------------------------------------------------------------
CREATE TABLE models (
model_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE VIEW IF NOT EXISTS models_view AS
SELECT model_id, name,
datetime(created_at, 'unixepoch', 'localtime') AS created_at
FROM models;
-- ---------------------------------------------------------------------------
-- sessions -- lookup; turns + summaries + turn_summaries reference it.
-- Normalizes the long conversation GUIDs (from the agent's turn hook) to a
-- compact integer id, mirroring `models`. The grouping key for session
-- summaries and recipes.
-- ---------------------------------------------------------------------------
CREATE TABLE sessions (
session_id INTEGER PRIMARY KEY AUTOINCREMENT,
guid TEXT NOT NULL UNIQUE,
name TEXT,
name_source TEXT,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE VIEW IF NOT EXISTS sessions_view AS
SELECT session_id, guid, name, name_source,
datetime(created_at, 'unixepoch', 'localtime') AS created_at
FROM sessions;
CREATE INDEX IF NOT EXISTS idx_sessions_name ON sessions(name);
-- ---------------------------------------------------------------------------
-- turns (L1) -- raw verbatim exchanges
-- ---------------------------------------------------------------------------
CREATE TABLE turns (
turn_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_text TEXT NOT NULL,
assistant_text TEXT,
model_id INTEGER REFERENCES models(model_id),
session_id INTEGER REFERENCES sessions(session_id),
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
embedding BLOB, -- embed(user_text + assistant_text)
phon TEXT -- phonize(user_text + assistant_text): Double Metaphone "sounds-like"
);
CREATE INDEX idx_turns_created_at ON turns(created_at);
CREATE INDEX idx_turns_session ON turns(session_id);
CREATE VIEW IF NOT EXISTS turns_view AS
SELECT turn_id, user_text, assistant_text, model_id, session_id,
datetime(created_at, 'unixepoch', 'localtime') AS created_at,
CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,
CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon
FROM turns;
-- ---------------------------------------------------------------------------
-- turn_summaries (L2) -- NEW at db_version 0.12. One row per summarized
-- turn. `turn_id` is a REAL FK, ON DELETE SET NULL: pruning the raw turn
-- leaves the summary behind with turn_id NULL instead of stranding it via a
-- fragile (session_id, created_at) match. `turn_datetime`/`turn_model_id`
-- are copied in explicitly at insert time (not derived via join) so both
-- survive `turns` row deletion. `summary_model_id` (who/what wrote the
-- summary text) is intentionally separate from `turn_model_id` (who
-- answered originally) — conflating them would lose "who actually
-- answered" the moment `turns` is truncated.
--
-- Sentinel for "not yet summarized": summary_model_id IS NULL — NOT
-- text IS NULL. `text` is populated at capture time with the raw
-- "User: ...\n\nAssistant: ..." placeholder (searchable immediately) and
-- overwritten in place once the summarizer produces a real summary;
-- `summary_model_id` only gets set on that second write. Mirrors the v4
-- `summaries.model_id IS NULL` placeholder convention exactly.
-- ---------------------------------------------------------------------------
CREATE TABLE turn_summaries (
turn_summary_id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT,
turn_id INTEGER REFERENCES turns(turn_id) ON DELETE SET NULL,
session_id INTEGER REFERENCES sessions(session_id),
turn_model_id INTEGER REFERENCES models(model_id),
summary_model_id INTEGER REFERENCES models(model_id),
turn_datetime INTEGER NOT NULL,
summarized_on INTEGER NOT NULL DEFAULT (unixepoch()),
embedding BLOB,
phon TEXT
);
CREATE INDEX idx_turn_summaries_turn_id ON turn_summaries(turn_id);
CREATE INDEX idx_turn_summaries_session_id ON turn_summaries(session_id);
CREATE INDEX idx_turn_summaries_datetime ON turn_summaries(turn_datetime);
-- Upsert target for finalize_turn_summary: one row per turn_id. NULL
-- turn_id (a pruned turn) is excepted — multiple such rows are fine, they
-- no longer represent "the" summary for any live turn.
CREATE UNIQUE INDEX idx_turn_summaries_turn_id_uniq
ON turn_summaries(turn_id) WHERE turn_id IS NOT NULL;
CREATE VIEW IF NOT EXISTS turn_summaries_view AS
SELECT
turn_summary_id, text, turn_id, session_id, turn_model_id, summary_model_id,
datetime(turn_datetime, 'unixepoch', 'localtime') AS turn_datetime,
datetime(summarized_on, 'unixepoch', 'localtime') AS summarized_on,
CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,
CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon
FROM turn_summaries;
-- ---------------------------------------------------------------------------
-- summaries (L3/L4) -- episode/session/project rollups only as of 0.12.
-- Turn-level rows moved to `turn_summaries` above.
-- created_at: (re)write / span-start time. updated_at: running rows
-- (session/project) record their last regenerate time; 'episode' rows
-- carry the span-end; on any first insert updated_at == created_at.
-- model_id: the model that produced the summary.
-- ---------------------------------------------------------------------------
CREATE TABLE summaries (
summary_id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
level TEXT NOT NULL, -- 'episode' | 'session' | 'project'
tags TEXT DEFAULT '' NOT NULL,
session_id INTEGER REFERENCES sessions(session_id),
model_id INTEGER REFERENCES models(model_id),
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
embedding BLOB,
phon TEXT -- phonize(text): Double Metaphone "sounds-like"
);
CREATE INDEX idx_summaries_level ON summaries(level);
CREATE INDEX idx_summaries_created_at ON summaries(created_at);
CREATE INDEX idx_summaries_session ON summaries(session_id);
CREATE VIEW IF NOT EXISTS summaries_view AS
SELECT summary_id, text, level, tags, session_id, model_id,
datetime(created_at, 'unixepoch', 'localtime') AS created_at,
datetime(updated_at, 'unixepoch', 'localtime') AS updated_at,
CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,
CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon
FROM summaries;
-- ---------------------------------------------------------------------------
-- decisions (L6)
-- ---------------------------------------------------------------------------
CREATE TABLE decisions (
decision_id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'current', -- current|superseded|revisit|deprecated
tags TEXT NOT NULL DEFAULT '',
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
embedding BLOB,
phon TEXT -- phonize(text): Double Metaphone "sounds-like"
);
CREATE INDEX idx_decisions_status ON decisions(status);
CREATE VIEW IF NOT EXISTS decisions_view AS
SELECT decision_id, text, status, tags,
datetime(created_at, 'unixepoch', 'localtime') AS created_at,
CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,
CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon
FROM decisions;
-- ---------------------------------------------------------------------------
-- documents (L5) -- user-curated RAG; the only sharable table.
-- imported_at STAYS TEXT 'YYYYMMDD' — a date-only batch-grouping key, not a
-- real timestamp; documents rows exist to capture maximal legible metadata
-- up front, which an opaque epoch int works against. Confirmed decision,
-- 2026-07-25 — do not convert this column in a future pass without
-- re-confirming.
-- ---------------------------------------------------------------------------
CREATE TABLE documents (
document_id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL, -- body; chapter/section headings inline
path TEXT,
title TEXT, -- identifies the publication
tags TEXT NOT NULL DEFAULT '', -- groups + describes
year INTEGER, -- publish year
chunk_index INTEGER,
imported_at TEXT NOT NULL, -- 'YYYYMMDD', shared per import batch — TEXT by design, see above
embedding BLOB,
phon TEXT -- phonize(text): Double Metaphone "sounds-like"
);
CREATE INDEX idx_documents_imported_at ON documents(imported_at);
CREATE VIEW IF NOT EXISTS documents_view AS
SELECT document_id, text, path, title, tags, year, chunk_index, imported_at,
CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,
CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon
FROM documents;
-- ---------------------------------------------------------------------------
-- settings -- key/value store (embedding_model, dimensions, recipe,
-- vector_type, db_version, ...). No timestamp/embedding columns; no view.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
-- Stamp db_version immediately so a FRESH install (no migration involved —
-- these CREATE TABLE IF NOT EXISTS statements just ran for the first time)
-- passes the binary's startup version-gate check on its very first run,
-- same as a migrated old DB does after migrate_to_db0.12.sh runs.
INSERT OR IGNORE INTO settings (key, value) VALUES ('db_version', '0.12');
-- ---------------------------------------------------------------------------
-- FTS5 -- external-content virtual tables + sync triggers (text search)
-- ---------------------------------------------------------------------------
CREATE VIRTUAL TABLE turns_fts USING fts5 (
user_text,
assistant_text,
content='turns',
content_rowid='turn_id'
);
CREATE TRIGGER turns_ai
AFTER INSERT
ON turns
BEGIN
INSERT INTO turns_fts(rowid, user_text, assistant_text)
VALUES (new.turn_id, new.user_text, new.assistant_text);
END;
CREATE TRIGGER turns_ad
AFTER DELETE
ON turns
BEGIN
INSERT INTO turns_fts(turns_fts, rowid, user_text, assistant_text)
VALUES ('delete', old.turn_id, old.user_text, old.assistant_text);
END;
CREATE TRIGGER turns_au
AFTER UPDATE
ON turns
BEGIN
INSERT INTO turns_fts(turns_fts, rowid, user_text, assistant_text)
VALUES ('delete', old.turn_id, old.user_text, old.assistant_text);
INSERT INTO turns_fts(rowid, user_text, assistant_text)
VALUES (new.turn_id, new.user_text, new.assistant_text);
END;
CREATE VIRTUAL TABLE summaries_fts USING fts5 (
text,
tags,
content='summaries',
content_rowid='summary_id'
);
CREATE TRIGGER summaries_ai
AFTER INSERT
ON summaries
BEGIN
INSERT INTO summaries_fts(rowid, text, tags)
VALUES (new.summary_id, new.text, new.tags);
END;
CREATE TRIGGER summaries_ad
AFTER DELETE
ON summaries
BEGIN
INSERT INTO summaries_fts(summaries_fts, rowid, text, tags)
VALUES ('delete', old.summary_id, old.text, old.tags);
END;
CREATE TRIGGER summaries_au
AFTER UPDATE
ON summaries
BEGIN
INSERT INTO summaries_fts(summaries_fts, rowid, text, tags)
VALUES ('delete', old.summary_id, old.text, old.tags);
INSERT INTO summaries_fts(rowid, text, tags)
VALUES (new.summary_id, new.text, new.tags);
END;
-- turn_summaries_fts: mirrors turns_fts's shape (no tags column, unlike
-- summaries_fts) since turn_summaries has no tags column.
CREATE VIRTUAL TABLE turn_summaries_fts USING fts5 (
text,
content='turn_summaries',
content_rowid='turn_summary_id'
);
CREATE TRIGGER turn_summaries_ai
AFTER INSERT
ON turn_summaries
BEGIN
INSERT INTO turn_summaries_fts(rowid, text)
VALUES (new.turn_summary_id, new.text);
END;
CREATE TRIGGER turn_summaries_ad
AFTER DELETE
ON turn_summaries
BEGIN
INSERT INTO turn_summaries_fts(turn_summaries_fts, rowid, text)
VALUES ('delete', old.turn_summary_id, old.text);
END;
CREATE TRIGGER turn_summaries_au
AFTER UPDATE
ON turn_summaries
BEGIN
INSERT INTO turn_summaries_fts(turn_summaries_fts, rowid, text)
VALUES ('delete', old.turn_summary_id, old.text);
INSERT INTO turn_summaries_fts(rowid, text)
VALUES (new.turn_summary_id, new.text);
END;
CREATE VIRTUAL TABLE decisions_fts USING fts5 (
text,
tags,
content='decisions',
content_rowid='decision_id'
);
CREATE TRIGGER decisions_ai
AFTER INSERT
ON decisions
BEGIN
INSERT INTO decisions_fts(rowid, text, tags)
VALUES (new.decision_id, new.text, new.tags);
END;
CREATE TRIGGER decisions_ad
AFTER DELETE
ON decisions
BEGIN
INSERT INTO decisions_fts(decisions_fts, rowid, text, tags)
VALUES ('delete', old.decision_id, old.text, old.tags);
END;
CREATE TRIGGER decisions_au
AFTER UPDATE
ON decisions
BEGIN
INSERT INTO decisions_fts(decisions_fts, rowid, text, tags)
VALUES ('delete', old.decision_id, old.text, old.tags);
INSERT INTO decisions_fts(rowid, text, tags)
VALUES (new.decision_id, new.text, new.tags);
END;
CREATE VIRTUAL TABLE documents_fts USING fts5 (
title,
text,
tags,
content='documents',
content_rowid='document_id'
);
CREATE TRIGGER documents_ai
AFTER INSERT
ON documents
BEGIN
INSERT INTO documents_fts(rowid, title, text, tags)
VALUES (new.document_id, new.title, new.text, new.tags);
END;
CREATE TRIGGER documents_ad
AFTER DELETE
ON documents
BEGIN
INSERT INTO documents_fts(documents_fts, rowid, title, text, tags)
VALUES ('delete', old.document_id, old.title, old.text, old.tags);
END;
CREATE TRIGGER documents_au
AFTER UPDATE
ON documents
BEGIN
INSERT INTO documents_fts(documents_fts, rowid, title, text, tags)
VALUES ('delete', old.document_id, old.title, old.text, old.tags);
INSERT INTO documents_fts(rowid, title, text, tags)
VALUES (new.document_id, new.title, new.text, new.tags);
END;
-- ---------------------------------------------------------------------------
-- Phonetic "sounds-like" (dolphining) FTS5 -- one external-content index over
-- each context table's `phon` column (space-joined Double Metaphone codes),
-- kept in sync by its own triggers. Separate from the text FTS above so phon
-- produces an INDEPENDENT bm25 score for the three-way search blend
-- (vector + text + phon; see config phon_weight).
-- ---------------------------------------------------------------------------
CREATE VIRTUAL TABLE turns_phon_fts USING fts5 (
phon,
content='turns',
content_rowid='turn_id'
);
CREATE TRIGGER turns_pai
AFTER INSERT
ON turns
BEGIN
INSERT INTO turns_phon_fts(rowid, phon) VALUES (new.turn_id, new.phon);
END;
CREATE TRIGGER turns_pad
AFTER DELETE
ON turns
BEGIN
INSERT INTO turns_phon_fts(turns_phon_fts, rowid, phon)
VALUES ('delete', old.turn_id, old.phon);
END;
CREATE TRIGGER turns_pau
AFTER UPDATE
ON turns
BEGIN
INSERT INTO turns_phon_fts(turns_phon_fts, rowid, phon)
VALUES ('delete', old.turn_id, old.phon);
INSERT INTO turns_phon_fts(rowid, phon) VALUES (new.turn_id, new.phon);
END;
CREATE VIRTUAL TABLE summaries_phon_fts USING fts5 (
phon,
content='summaries',
content_rowid='summary_id'
);
CREATE TRIGGER summaries_pai
AFTER INSERT
ON summaries
BEGIN
INSERT INTO summaries_phon_fts(rowid, phon) VALUES (new.summary_id, new.phon);
END;
CREATE TRIGGER summaries_pad
AFTER DELETE
ON summaries
BEGIN
INSERT INTO summaries_phon_fts(summaries_phon_fts, rowid, phon)
VALUES ('delete', old.summary_id, old.phon);
END;
CREATE TRIGGER summaries_pau
AFTER UPDATE
ON summaries
BEGIN
INSERT INTO summaries_phon_fts(summaries_phon_fts, rowid, phon)
VALUES ('delete', old.summary_id, old.phon);
INSERT INTO summaries_phon_fts(rowid, phon) VALUES (new.summary_id, new.phon);
END;
CREATE VIRTUAL TABLE turn_summaries_phon_fts USING fts5 (
phon,
content='turn_summaries',
content_rowid='turn_summary_id'
);
CREATE TRIGGER turn_summaries_pai
AFTER INSERT
ON turn_summaries
BEGIN
INSERT INTO turn_summaries_phon_fts(rowid, phon) VALUES (new.turn_summary_id, new.phon);
END;
CREATE TRIGGER turn_summaries_pad
AFTER DELETE
ON turn_summaries
BEGIN
INSERT INTO turn_summaries_phon_fts(turn_summaries_phon_fts, rowid, phon)
VALUES ('delete', old.turn_summary_id, old.phon);
END;
CREATE TRIGGER turn_summaries_pau
AFTER UPDATE
ON turn_summaries
BEGIN
INSERT INTO turn_summaries_phon_fts(turn_summaries_phon_fts, rowid, phon)
VALUES ('delete', old.turn_summary_id, old.phon);
INSERT INTO turn_summaries_phon_fts(rowid, phon) VALUES (new.turn_summary_id, new.phon);
END;
CREATE VIRTUAL TABLE decisions_phon_fts USING fts5 (
phon,
content='decisions',
content_rowid='decision_id'
);
CREATE TRIGGER decisions_pai
AFTER INSERT
ON decisions
BEGIN
INSERT INTO decisions_phon_fts(rowid, phon) VALUES (new.decision_id, new.phon);
END;
CREATE TRIGGER decisions_pad
AFTER DELETE
ON decisions
BEGIN
INSERT INTO decisions_phon_fts(decisions_phon_fts, rowid, phon)
VALUES ('delete', old.decision_id, old.phon);
END;
CREATE TRIGGER decisions_pau
AFTER UPDATE
ON decisions
BEGIN
INSERT INTO decisions_phon_fts(decisions_phon_fts, rowid, phon)
VALUES ('delete', old.decision_id, old.phon);
INSERT INTO decisions_phon_fts(rowid, phon) VALUES (new.decision_id, new.phon);
END;
CREATE VIRTUAL TABLE documents_phon_fts USING fts5 (
phon,
content='documents',
content_rowid='document_id'
);
CREATE TRIGGER documents_pai
AFTER INSERT
ON documents
BEGIN
INSERT INTO documents_phon_fts(rowid, phon) VALUES (new.document_id, new.phon);
END;
CREATE TRIGGER documents_pad
AFTER DELETE
ON documents
BEGIN
INSERT INTO documents_phon_fts(documents_phon_fts, rowid, phon)
VALUES ('delete', old.document_id, old.phon);
END;
CREATE TRIGGER documents_pau
AFTER UPDATE
ON documents
BEGIN
INSERT INTO documents_phon_fts(documents_phon_fts, rowid, phon)
VALUES ('delete', old.document_id, old.phon);
INSERT INTO documents_phon_fts(rowid, phon) VALUES (new.document_id, new.phon);
END;