Skip to main content

max / audiofiles

85.6 KB · 1913 lines History Blame Raw
1 //! The ordered migration log: every schema version this build can produce.
2 //!
3 //! Extracted from the former `db.rs`; the parent re-exports [`SCHEMA_VERSION`].
4
5 use rusqlite::Connection;
6 use rusqlite::functions::FunctionFlags;
7 use sha2::{Digest, Sha256};
8
9 use super::DbError;
10
11 const MIGRATION_001: &str = r"
12 -- Sample storage and metadata
13 CREATE TABLE samples (
14 hash TEXT PRIMARY KEY,
15 original_name TEXT NOT NULL,
16 file_extension TEXT NOT NULL,
17 file_size INTEGER NOT NULL,
18 import_date INTEGER NOT NULL,
19 last_modified INTEGER NOT NULL
20 );
21
22 -- Audio analysis results
23 CREATE TABLE audio_analysis (
24 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
25 bpm REAL,
26 musical_key TEXT,
27 duration REAL NOT NULL,
28 sample_rate INTEGER NOT NULL,
29 channels INTEGER NOT NULL,
30 peak_db REAL,
31 rms_db REAL,
32 is_loop BOOLEAN,
33 spectral_centroid REAL,
34 onset_strength REAL,
35 analyzed_at INTEGER NOT NULL
36 );
37
38 -- Virtual file systems
39 CREATE TABLE vfs (
40 id INTEGER PRIMARY KEY,
41 name TEXT NOT NULL UNIQUE,
42 created_at INTEGER NOT NULL,
43 modified_at INTEGER NOT NULL
44 );
45
46 -- VFS directory/file nodes
47 CREATE TABLE vfs_nodes (
48 id INTEGER PRIMARY KEY,
49 vfs_id INTEGER NOT NULL REFERENCES vfs(id) ON DELETE CASCADE,
50 parent_id INTEGER REFERENCES vfs_nodes(id) ON DELETE CASCADE,
51 name TEXT NOT NULL,
52 node_type TEXT NOT NULL CHECK(node_type IN ('directory', 'sample')),
53 sample_hash TEXT REFERENCES samples(hash) ON DELETE CASCADE,
54 created_at INTEGER NOT NULL,
55 UNIQUE(vfs_id, parent_id, name)
56 );
57
58 -- User-defined tags
59 CREATE TABLE tags (
60 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
61 tag_name TEXT NOT NULL,
62 tag_value TEXT NOT NULL,
63 PRIMARY KEY (sample_hash, tag_name, tag_value)
64 );
65
66 -- Collections/playlists
67 CREATE TABLE collections (
68 id INTEGER PRIMARY KEY,
69 name TEXT NOT NULL UNIQUE,
70 description TEXT,
71 created_at INTEGER NOT NULL
72 );
73
74 CREATE TABLE collection_members (
75 collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
76 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
77 added_at INTEGER NOT NULL,
78 PRIMARY KEY (collection_id, sample_hash)
79 );
80
81 -- Smart folders (saved searches)
82 CREATE TABLE smart_folders (
83 id INTEGER PRIMARY KEY,
84 vfs_id INTEGER NOT NULL REFERENCES vfs(id) ON DELETE CASCADE,
85 name TEXT NOT NULL,
86 query_json TEXT NOT NULL,
87 created_at INTEGER NOT NULL
88 );
89
90 -- Performance indexes
91 CREATE INDEX idx_vfs_nodes_parent ON vfs_nodes(parent_id);
92 CREATE INDEX idx_vfs_nodes_vfs ON vfs_nodes(vfs_id);
93 CREATE INDEX idx_vfs_nodes_hash ON vfs_nodes(sample_hash);
94 CREATE INDEX idx_tags_hash ON tags(sample_hash);
95 CREATE INDEX idx_tags_name_value ON tags(tag_name, tag_value);
96 CREATE INDEX idx_analysis_bpm ON audio_analysis(bpm);
97 CREATE INDEX idx_analysis_key ON audio_analysis(musical_key);
98 ";
99
100 const MIGRATION_002: &str = r"
101 CREATE TABLE tags_v2 (
102 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
103 tag TEXT NOT NULL,
104 PRIMARY KEY (sample_hash, tag)
105 );
106
107 -- Migrate any existing data
108 INSERT OR IGNORE INTO tags_v2 (sample_hash, tag)
109 SELECT sample_hash, LOWER(tag_name || '.' || tag_value) FROM tags;
110
111 DROP TABLE tags;
112 ALTER TABLE tags_v2 RENAME TO tags;
113
114 CREATE INDEX idx_tags_hash ON tags(sample_hash);
115 CREATE INDEX idx_tags_tag ON tags(tag);
116 ";
117
118 const MIGRATION_003: &str = r"
119 ALTER TABLE audio_analysis ADD COLUMN lufs REAL;
120 ALTER TABLE audio_analysis ADD COLUMN spectral_flatness REAL;
121 ALTER TABLE audio_analysis ADD COLUMN spectral_rolloff REAL;
122 ALTER TABLE audio_analysis ADD COLUMN zero_crossing_rate REAL;
123 -- `classification` was added here. Removed when the sample-class label was
124 -- retired (docs/ml_classifier.md) rather than dropped in a later migration, so no
125 -- database ever creates the column in the first place.
126 ";
127
128 const MIGRATION_004: &str = r"
129 CREATE TABLE IF NOT EXISTS waveform_data (
130 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
131 num_buckets INTEGER NOT NULL,
132 peak_data BLOB NOT NULL,
133 sample_rate INTEGER NOT NULL,
134 duration REAL NOT NULL,
135 generated_at INTEGER NOT NULL
136 );
137 CREATE INDEX IF NOT EXISTS idx_analysis_duration ON audio_analysis(duration);
138 -- idx_analysis_classification was created here and again in M028; both removed
139 -- with the column itself.
140 CREATE INDEX IF NOT EXISTS idx_samples_name ON samples(original_name);
141 ";
142
143 const MIGRATION_005: &str = r"
144 CREATE TABLE IF NOT EXISTS user_config (key TEXT PRIMARY KEY, value TEXT NOT NULL);
145 ";
146
147 const MIGRATION_006: &str = r"
148 CREATE TABLE IF NOT EXISTS fingerprints (
149 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
150 envelope BLOB NOT NULL,
151 sample_rate INTEGER NOT NULL,
152 generated_at INTEGER NOT NULL
153 );
154 ";
155
156 const MIGRATION_007: &str = r#"
157 -- Per-VFS toggle for syncing audio file blobs to cloud (metadata always syncs)
158 ALTER TABLE vfs ADD COLUMN sync_files INTEGER NOT NULL DEFAULT 0;
159
160 -- Sync metadata key-value store
161 CREATE TABLE IF NOT EXISTS sync_state (
162 key TEXT PRIMARY KEY,
163 value TEXT NOT NULL
164 );
165 INSERT OR IGNORE INTO sync_state (key, value) VALUES
166 ('device_id', ''),
167 ('pull_cursor', ''),
168 ('auto_sync_enabled', '0'),
169 ('sync_interval_minutes', '15'),
170 ('applying_remote', '0'),
171 ('last_sync_at', ''),
172 ('initial_snapshot_done', '0');
173
174 -- Local change log for push/pull sync
175 CREATE TABLE IF NOT EXISTS sync_changelog (
176 id INTEGER PRIMARY KEY AUTOINCREMENT,
177 table_name TEXT NOT NULL,
178 op TEXT NOT NULL,
179 row_id TEXT NOT NULL,
180 timestamp TEXT NOT NULL DEFAULT (datetime('now')),
181 data TEXT,
182 pushed INTEGER NOT NULL DEFAULT 0
183 );
184 CREATE INDEX IF NOT EXISTS idx_changelog_pushed ON sync_changelog(pushed);
185
186 -- ── Triggers: record changes unless applying remote data ──
187
188 -- samples
189 CREATE TRIGGER IF NOT EXISTS sync_samples_insert AFTER INSERT ON samples
190 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
191 BEGIN
192 INSERT INTO sync_changelog (table_name, op, row_id, data)
193 VALUES ('samples', 'INSERT', NEW.hash,
194 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
195 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
196 'import_date', NEW.import_date, 'last_modified', NEW.last_modified));
197 END;
198
199 CREATE TRIGGER IF NOT EXISTS sync_samples_update AFTER UPDATE ON samples
200 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
201 BEGIN
202 INSERT INTO sync_changelog (table_name, op, row_id, data)
203 VALUES ('samples', 'UPDATE', NEW.hash,
204 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
205 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
206 'import_date', NEW.import_date, 'last_modified', NEW.last_modified));
207 END;
208
209 CREATE TRIGGER IF NOT EXISTS sync_samples_delete AFTER DELETE ON samples
210 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
211 BEGIN
212 INSERT INTO sync_changelog (table_name, op, row_id, data)
213 VALUES ('samples', 'DELETE', OLD.hash, NULL);
214 END;
215
216 -- audio_analysis
217 CREATE TRIGGER IF NOT EXISTS sync_audio_analysis_insert AFTER INSERT ON audio_analysis
218 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
219 BEGIN
220 INSERT INTO sync_changelog (table_name, op, row_id, data)
221 VALUES ('audio_analysis', 'INSERT', NEW.hash,
222 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
223 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
224 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
225 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
226 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
227 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
228 'zero_crossing_rate', NEW.zero_crossing_rate));
229 END;
230
231 CREATE TRIGGER IF NOT EXISTS sync_audio_analysis_update AFTER UPDATE ON audio_analysis
232 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
233 BEGIN
234 INSERT INTO sync_changelog (table_name, op, row_id, data)
235 VALUES ('audio_analysis', 'UPDATE', NEW.hash,
236 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
237 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
238 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
239 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
240 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
241 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
242 'zero_crossing_rate', NEW.zero_crossing_rate));
243 END;
244
245 CREATE TRIGGER IF NOT EXISTS sync_audio_analysis_delete AFTER DELETE ON audio_analysis
246 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
247 BEGIN
248 INSERT INTO sync_changelog (table_name, op, row_id, data)
249 VALUES ('audio_analysis', 'DELETE', OLD.hash, NULL);
250 END;
251
252 -- vfs
253 CREATE TRIGGER IF NOT EXISTS sync_vfs_insert AFTER INSERT ON vfs
254 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
255 BEGIN
256 INSERT INTO sync_changelog (table_name, op, row_id, data)
257 VALUES ('vfs', 'INSERT', CAST(NEW.id AS TEXT),
258 json_object('id', NEW.id, 'name', NEW.name,
259 'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
260 'sync_files', NEW.sync_files));
261 END;
262
263 CREATE TRIGGER IF NOT EXISTS sync_vfs_update AFTER UPDATE ON vfs
264 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
265 BEGIN
266 INSERT INTO sync_changelog (table_name, op, row_id, data)
267 VALUES ('vfs', 'UPDATE', CAST(NEW.id AS TEXT),
268 json_object('id', NEW.id, 'name', NEW.name,
269 'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
270 'sync_files', NEW.sync_files));
271 END;
272
273 CREATE TRIGGER IF NOT EXISTS sync_vfs_delete AFTER DELETE ON vfs
274 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
275 BEGIN
276 INSERT INTO sync_changelog (table_name, op, row_id, data)
277 VALUES ('vfs', 'DELETE', CAST(OLD.id AS TEXT), NULL);
278 END;
279
280 -- vfs_nodes
281 CREATE TRIGGER IF NOT EXISTS sync_vfs_nodes_insert AFTER INSERT ON vfs_nodes
282 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
283 BEGIN
284 INSERT INTO sync_changelog (table_name, op, row_id, data)
285 VALUES ('vfs_nodes', 'INSERT', CAST(NEW.id AS TEXT),
286 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
287 'name', NEW.name, 'node_type', NEW.node_type,
288 'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
289 END;
290
291 CREATE TRIGGER IF NOT EXISTS sync_vfs_nodes_update AFTER UPDATE ON vfs_nodes
292 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
293 BEGIN
294 INSERT INTO sync_changelog (table_name, op, row_id, data)
295 VALUES ('vfs_nodes', 'UPDATE', CAST(NEW.id AS TEXT),
296 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
297 'name', NEW.name, 'node_type', NEW.node_type,
298 'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
299 END;
300
301 CREATE TRIGGER IF NOT EXISTS sync_vfs_nodes_delete AFTER DELETE ON vfs_nodes
302 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
303 BEGIN
304 INSERT INTO sync_changelog (table_name, op, row_id, data)
305 VALUES ('vfs_nodes', 'DELETE', CAST(OLD.id AS TEXT), NULL);
306 END;
307
308 -- tags
309 CREATE TRIGGER IF NOT EXISTS sync_tags_insert AFTER INSERT ON tags
310 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
311 BEGIN
312 INSERT INTO sync_changelog (table_name, op, row_id, data)
313 VALUES ('tags', 'INSERT', NEW.sample_hash || ':' || NEW.tag,
314 json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag));
315 END;
316
317 CREATE TRIGGER IF NOT EXISTS sync_tags_delete AFTER DELETE ON tags
318 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
319 BEGIN
320 INSERT INTO sync_changelog (table_name, op, row_id, data)
321 VALUES ('tags', 'DELETE', OLD.sample_hash || ':' || OLD.tag, NULL);
322 END;
323
324 -- collections
325 CREATE TRIGGER IF NOT EXISTS sync_collections_insert AFTER INSERT ON collections
326 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
327 BEGIN
328 INSERT INTO sync_changelog (table_name, op, row_id, data)
329 VALUES ('collections', 'INSERT', CAST(NEW.id AS TEXT),
330 json_object('id', NEW.id, 'name', NEW.name,
331 'description', NEW.description, 'created_at', NEW.created_at));
332 END;
333
334 CREATE TRIGGER IF NOT EXISTS sync_collections_update AFTER UPDATE ON collections
335 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
336 BEGIN
337 INSERT INTO sync_changelog (table_name, op, row_id, data)
338 VALUES ('collections', 'UPDATE', CAST(NEW.id AS TEXT),
339 json_object('id', NEW.id, 'name', NEW.name,
340 'description', NEW.description, 'created_at', NEW.created_at));
341 END;
342
343 CREATE TRIGGER IF NOT EXISTS sync_collections_delete AFTER DELETE ON collections
344 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
345 BEGIN
346 INSERT INTO sync_changelog (table_name, op, row_id, data)
347 VALUES ('collections', 'DELETE', CAST(OLD.id AS TEXT), NULL);
348 END;
349
350 -- collection_members
351 CREATE TRIGGER IF NOT EXISTS sync_collection_members_insert AFTER INSERT ON collection_members
352 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
353 BEGIN
354 INSERT INTO sync_changelog (table_name, op, row_id, data)
355 VALUES ('collection_members', 'INSERT',
356 CAST(NEW.collection_id AS TEXT) || ':' || NEW.sample_hash,
357 json_object('collection_id', NEW.collection_id, 'sample_hash', NEW.sample_hash,
358 'added_at', NEW.added_at));
359 END;
360
361 CREATE TRIGGER IF NOT EXISTS sync_collection_members_delete AFTER DELETE ON collection_members
362 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
363 BEGIN
364 INSERT INTO sync_changelog (table_name, op, row_id, data)
365 VALUES ('collection_members', 'DELETE',
366 CAST(OLD.collection_id AS TEXT) || ':' || OLD.sample_hash, NULL);
367 END;
368
369 -- smart_folders sync triggers used to live here. Removed 2026-06-02:
370 -- M015 drops `smart_folders` and merges its contents into
371 -- `collections.filter_json`, so replaying M007 against a post-M015 schema
372 -- failed with "no such table". The triggers had no functional effect on
373 -- any install path (smart_folders is empty on first-run between M001's
374 -- CREATE and M015's DROP), so removing them is invisible. M015's
375 -- `DROP TRIGGER IF EXISTS sync_smart_folders_*` stays in place for DBs
376 -- that already applied the old M007 and need the triggers cleaned up.
377
378 -- user_config (exclude sync-internal keys)
379 CREATE TRIGGER IF NOT EXISTS sync_user_config_insert AFTER INSERT ON user_config
380 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
381 AND NEW.key NOT LIKE 'sync_%'
382 BEGIN
383 INSERT INTO sync_changelog (table_name, op, row_id, data)
384 VALUES ('user_config', 'INSERT', NEW.key,
385 json_object('key', NEW.key, 'value', NEW.value));
386 END;
387
388 CREATE TRIGGER IF NOT EXISTS sync_user_config_update AFTER UPDATE ON user_config
389 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
390 AND NEW.key NOT LIKE 'sync_%'
391 BEGIN
392 INSERT INTO sync_changelog (table_name, op, row_id, data)
393 VALUES ('user_config', 'UPDATE', NEW.key,
394 json_object('key', NEW.key, 'value', NEW.value));
395 END;
396
397 CREATE TRIGGER IF NOT EXISTS sync_user_config_delete AFTER DELETE ON user_config
398 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
399 AND OLD.key NOT LIKE 'sync_%'
400 BEGIN
401 INSERT INTO sync_changelog (table_name, op, row_id, data)
402 VALUES ('user_config', 'DELETE', OLD.key, NULL);
403 END;
404 "#;
405
406 const MIGRATION_008: &str = r"
407 -- cloud_only: 1 when the local blob has been deleted but exists in cloud storage
408 ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0;
409
410 -- Recreate samples triggers to include cloud_only in the JSON data
411 DROP TRIGGER IF EXISTS sync_samples_insert;
412 DROP TRIGGER IF EXISTS sync_samples_update;
413
414 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
415 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
416 BEGIN
417 INSERT INTO sync_changelog (table_name, op, row_id, data)
418 VALUES ('samples', 'INSERT', NEW.hash,
419 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
420 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
421 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
422 'cloud_only', NEW.cloud_only));
423 END;
424
425 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
426 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
427 BEGIN
428 INSERT INTO sync_changelog (table_name, op, row_id, data)
429 VALUES ('samples', 'UPDATE', NEW.hash,
430 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
431 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
432 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
433 'cloud_only', NEW.cloud_only));
434 END;
435 ";
436
437 const MIGRATION_009: &str = r"
438 -- Duration on samples table so it's available immediately after import (before analysis).
439 ALTER TABLE samples ADD COLUMN duration REAL;
440
441 -- Recreate samples triggers to include duration in the JSON data
442 DROP TRIGGER IF EXISTS sync_samples_insert;
443 DROP TRIGGER IF EXISTS sync_samples_update;
444
445 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
446 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
447 BEGIN
448 INSERT INTO sync_changelog (table_name, op, row_id, data)
449 VALUES ('samples', 'INSERT', NEW.hash,
450 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
451 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
452 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
453 'cloud_only', NEW.cloud_only, 'duration', NEW.duration));
454 END;
455
456 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
457 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
458 BEGIN
459 INSERT INTO sync_changelog (table_name, op, row_id, data)
460 VALUES ('samples', 'UPDATE', NEW.hash,
461 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
462 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
463 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
464 'cloud_only', NEW.cloud_only, 'duration', NEW.duration));
465 END;
466 ";
467
468 const MIGRATION_010: &str = r"
469 -- New spectral and waveform features
470 ALTER TABLE audio_analysis ADD COLUMN spectral_bandwidth REAL;
471 ALTER TABLE audio_analysis ADD COLUMN centroid_variance REAL;
472 ALTER TABLE audio_analysis ADD COLUMN crest_factor REAL;
473 ALTER TABLE audio_analysis ADD COLUMN attack_time REAL;
474
475 -- Recreate audio_analysis sync triggers to include new columns
476 DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
477 DROP TRIGGER IF EXISTS sync_audio_analysis_update;
478
479 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
480 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
481 BEGIN
482 INSERT INTO sync_changelog (table_name, op, row_id, data)
483 VALUES ('audio_analysis', 'INSERT', NEW.hash,
484 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
485 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
486 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
487 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
488 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
489 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
490 'zero_crossing_rate', NEW.zero_crossing_rate,
491 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
492 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
493 END;
494
495 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
496 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
497 BEGIN
498 INSERT INTO sync_changelog (table_name, op, row_id, data)
499 VALUES ('audio_analysis', 'UPDATE', NEW.hash,
500 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
501 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
502 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
503 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
504 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
505 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
506 'zero_crossing_rate', NEW.zero_crossing_rate,
507 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
508 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
509 END;
510 ";
511
512 const MIGRATION_011: &str = r"
513 -- Added classification_confidence and recreated the sync triggers to carry it.
514 -- The column is retired (docs/ml_classifier.md), so the ALTER is gone; the trigger
515 -- recreate stays because M018 and M032 both build on the bodies below.
516 --
517 -- Recreate audio_analysis sync triggers
518 DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
519 DROP TRIGGER IF EXISTS sync_audio_analysis_update;
520
521 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
522 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
523 BEGIN
524 INSERT INTO sync_changelog (table_name, op, row_id, data)
525 VALUES ('audio_analysis', 'INSERT', NEW.hash,
526 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
527 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
528 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
529 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
530 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
531 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
532 'zero_crossing_rate', NEW.zero_crossing_rate,
533 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
534 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
535 END;
536
537 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
538 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
539 BEGIN
540 INSERT INTO sync_changelog (table_name, op, row_id, data)
541 VALUES ('audio_analysis', 'UPDATE', NEW.hash,
542 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
543 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
544 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
545 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
546 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
547 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
548 'zero_crossing_rate', NEW.zero_crossing_rate,
549 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
550 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
551 END;
552 ";
553
554 const MIGRATION_012: &str = r"
555 -- Edit history: tracks destructive edits for future undo support
556 CREATE TABLE IF NOT EXISTS edit_history (
557 id INTEGER PRIMARY KEY AUTOINCREMENT,
558 source_hash TEXT NOT NULL,
559 result_hash TEXT NOT NULL,
560 operation TEXT NOT NULL,
561 params_json TEXT,
562 created_at INTEGER NOT NULL DEFAULT (unixepoch())
563 );
564 CREATE INDEX IF NOT EXISTS idx_edit_history_source ON edit_history(source_hash);
565 CREATE INDEX IF NOT EXISTS idx_edit_history_result ON edit_history(result_hash);
566
567 -- Sync trigger for edit_history
568 CREATE TRIGGER IF NOT EXISTS sync_edit_history_insert AFTER INSERT ON edit_history
569 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
570 BEGIN
571 INSERT INTO sync_changelog (table_name, op, row_id, data)
572 VALUES ('edit_history', 'INSERT', CAST(NEW.id AS TEXT),
573 json_object('id', NEW.id, 'source_hash', NEW.source_hash,
574 'result_hash', NEW.result_hash, 'operation', NEW.operation,
575 'params_json', NEW.params_json, 'created_at', NEW.created_at));
576 END;
577 ";
578
579 const MIGRATION_013: &str = r"
580 -- Loose-files mode: remember original file path instead of copying into vault.
581 -- NULL = normal (blob in samples/), non-NULL = loose-files (blob at this path).
582 -- Intentionally excluded from sync triggers, source_path is device-local.
583 ALTER TABLE samples ADD COLUMN source_path TEXT;
584 ";
585
586 const MIGRATION_014: &str = r"
587 -- Prevent duplicate root-level VFS node names. The existing UNIQUE(vfs_id, parent_id, name)
588 -- constraint treats NULLs as distinct, so root nodes (parent_id IS NULL) could collide.
589 CREATE UNIQUE INDEX IF NOT EXISTS idx_vfs_nodes_root_unique
590 ON vfs_nodes(vfs_id, name) WHERE parent_id IS NULL;
591 ";
592
593 const MIGRATION_015: &str = r"
594 -- Merge smart folders into collections: add a filter_json column.
595 -- NULL filter_json = manual collection, non-NULL = dynamic (saved search).
596 ALTER TABLE collections ADD COLUMN filter_json TEXT;
597 -- Migrate existing smart folders into collections with their filters.
598 INSERT OR IGNORE INTO collections (name, description, created_at, filter_json)
599 SELECT name, NULL, created_at, query_json FROM smart_folders;
600 -- Drop the smart_folders table (triggers first, then table).
601 DROP TRIGGER IF EXISTS sync_smart_folders_insert;
602 DROP TRIGGER IF EXISTS sync_smart_folders_update;
603 DROP TRIGGER IF EXISTS sync_smart_folders_delete;
604 DROP TABLE IF EXISTS smart_folders;
605 ";
606
607 const MIGRATION_016: &str = r"
608 -- Exclude loose-files mode from sync: a compromised server or second device
609 -- should not be able to silently flip a security-relevant setting.
610 DROP TRIGGER IF EXISTS sync_user_config_insert;
611 DROP TRIGGER IF EXISTS sync_user_config_update;
612 DROP TRIGGER IF EXISTS sync_user_config_delete;
613
614 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
615 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
616 AND NEW.key NOT LIKE 'sync_%'
617 AND NEW.key != 'unsafe_mode'
618 BEGIN
619 INSERT INTO sync_changelog (table_name, op, row_id, data)
620 VALUES ('user_config', 'INSERT', NEW.key,
621 json_object('key', NEW.key, 'value', NEW.value));
622 END;
623
624 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
625 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
626 AND NEW.key NOT LIKE 'sync_%'
627 AND NEW.key != 'unsafe_mode'
628 BEGIN
629 INSERT INTO sync_changelog (table_name, op, row_id, data)
630 VALUES ('user_config', 'UPDATE', NEW.key,
631 json_object('key', NEW.key, 'value', NEW.value));
632 END;
633
634 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
635 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
636 AND OLD.key NOT LIKE 'sync_%'
637 AND OLD.key != 'unsafe_mode'
638 BEGIN
639 INSERT INTO sync_changelog (table_name, op, row_id, data)
640 VALUES ('user_config', 'DELETE', OLD.key, NULL);
641 END;
642 ";
643
644 const MIGRATION_017: &str = r"
645 -- Schema-only half of the 'unsafe_mode' -> 'loose_files' rename.
646 -- Recreates the sync-exclusion triggers to reference the new key literal
647 -- in their WHEN clauses (triggers can't parameterize key names, so the
648 -- rewrite has to live in a migration). The runtime row-copy
649 -- (unsafe_mode value -> loose_files row) lives in main.rs at the
650 -- vault-open path; doing it there avoids running it against every
651 -- attached/auxiliary DB that goes through migrate().
652 DROP TRIGGER IF EXISTS sync_user_config_insert;
653 DROP TRIGGER IF EXISTS sync_user_config_update;
654 DROP TRIGGER IF EXISTS sync_user_config_delete;
655
656 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
657 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
658 AND NEW.key NOT LIKE 'sync_%'
659 AND NEW.key != 'loose_files'
660 BEGIN
661 INSERT INTO sync_changelog (table_name, op, row_id, data)
662 VALUES ('user_config', 'INSERT', NEW.key,
663 json_object('key', NEW.key, 'value', NEW.value));
664 END;
665
666 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
667 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
668 AND NEW.key NOT LIKE 'sync_%'
669 AND NEW.key != 'loose_files'
670 BEGIN
671 INSERT INTO sync_changelog (table_name, op, row_id, data)
672 VALUES ('user_config', 'UPDATE', NEW.key,
673 json_object('key', NEW.key, 'value', NEW.value));
674 END;
675
676 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
677 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
678 AND OLD.key NOT LIKE 'sync_%'
679 AND OLD.key != 'loose_files'
680 BEGIN
681 INSERT INTO sync_changelog (table_name, op, row_id, data)
682 VALUES ('user_config', 'DELETE', OLD.key, NULL);
683 END;
684 ";
685
686 /// M018, hash sensitive row_id values on the wire.
687 ///
688 /// The `sync_changelog.row_id` column is sent to the server in cleartext, so a
689 /// trigger must never put user content into it (tag strings as
690 /// `sample_hash:tag`, raw sample SHA-256s as content fingerprints, collection
691 /// bindings). The encrypted `data` field carries that content instead.
692 ///
693 /// This migration:
694 ///
695 /// 1. Generates a per-user `row_id_salt` in `sync_state` (never synced) so
696 /// even a global rainbow table over common tag strings can't deanonymise
697 /// users. SQLite's `randomblob(32)` is seeded from /dev/urandom on POSIX
698 /// and CryptGenRandom on Windows.
699 /// 2. Recreates every sync trigger to wrap row_id in
700 /// `hash_row_id(salt, canonical_key)`. The encrypted `data` field still
701 /// carries the cleartext for the receiving device.
702 /// 3. Extends DELETE triggers to emit the canonical key(s) in `data` so the
703 /// pull-side `resolve::apply_delete` can reconstruct the WHERE clause
704 /// without parsing row_id (which is now opaque).
705 /// 4. Rewrites every unpushed row in `sync_changelog` that contained
706 /// sensitive cleartext: hashes the row_id, and for DELETE rows in
707 /// composite-key tables (`tags`, `collection_members`) backfills the
708 /// canonical key from the now-being-hashed cleartext into `data`.
709 ///
710 /// Numeric-id tables (vfs, vfs_nodes, collections, smart_folders,
711 /// edit_history) and user_config are left as-is, their row_ids carry
712 /// either opaque integers or a closed set of app-defined config keys, no
713 /// user content.
714 const MIGRATION_018: &str = r"
715 -- 1. Per-user salt for row_id hashing. `INSERT OR IGNORE` so re-running
716 -- this migration after a partial crash doesn't rotate the salt and
717 -- invalidate already-hashed row_ids.
718 INSERT OR IGNORE INTO sync_state (key, value)
719 VALUES ('row_id_salt', lower(hex(randomblob(32))));
720
721 -- 2. Backfill canonical-key `data` for unpushed DELETE rows in composite-PK
722 -- tables. Must run BEFORE the row_id hash so we still have the cleartext
723 -- composite to parse.
724 UPDATE sync_changelog
725 SET data = json_object(
726 'sample_hash', substr(row_id, 1, instr(row_id, ':') - 1),
727 'tag', substr(row_id, instr(row_id, ':') + 1)
728 )
729 WHERE table_name = 'tags' AND op = 'DELETE' AND pushed = 0
730 AND data IS NULL
731 AND instr(row_id, ':') > 0;
732
733 UPDATE sync_changelog
734 SET data = json_object(
735 'collection_id', substr(row_id, 1, instr(row_id, ':') - 1),
736 'sample_hash', substr(row_id, instr(row_id, ':') + 1)
737 )
738 WHERE table_name = 'collection_members' AND op = 'DELETE' AND pushed = 0
739 AND data IS NULL
740 AND instr(row_id, ':') > 0;
741
742 -- 3. For single-PK sensitive-row_id tables, backfill canonical-key `data`
743 -- for unpushed DELETE rows so apply_delete on the pulling device can
744 -- reconstruct the WHERE clause from the encrypted data alone.
745 UPDATE sync_changelog
746 SET data = json_object('hash', row_id)
747 WHERE table_name IN ('samples', 'audio_analysis')
748 AND op = 'DELETE' AND pushed = 0 AND data IS NULL;
749
750 -- 4. Now hash the row_id for every unpushed row whose cleartext leaked user
751 -- content (sample hashes, tag strings).
752 UPDATE sync_changelog
753 SET row_id = hash_row_id(
754 (SELECT value FROM sync_state WHERE key = 'row_id_salt'),
755 row_id
756 )
757 WHERE pushed = 0
758 AND table_name IN ('samples', 'audio_analysis', 'tags', 'collection_members');
759
760 -- 5. Drop and recreate every sync trigger with hash_row_id wrapping.
761 -- DELETE triggers gain a canonical-key `data` payload.
762
763 DROP TRIGGER IF EXISTS sync_samples_insert;
764 DROP TRIGGER IF EXISTS sync_samples_update;
765 DROP TRIGGER IF EXISTS sync_samples_delete;
766 DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
767 DROP TRIGGER IF EXISTS sync_audio_analysis_update;
768 DROP TRIGGER IF EXISTS sync_audio_analysis_delete;
769 DROP TRIGGER IF EXISTS sync_vfs_insert;
770 DROP TRIGGER IF EXISTS sync_vfs_update;
771 DROP TRIGGER IF EXISTS sync_vfs_delete;
772 DROP TRIGGER IF EXISTS sync_vfs_nodes_insert;
773 DROP TRIGGER IF EXISTS sync_vfs_nodes_update;
774 DROP TRIGGER IF EXISTS sync_vfs_nodes_delete;
775 DROP TRIGGER IF EXISTS sync_tags_insert;
776 DROP TRIGGER IF EXISTS sync_tags_delete;
777 DROP TRIGGER IF EXISTS sync_collections_insert;
778 DROP TRIGGER IF EXISTS sync_collections_update;
779 DROP TRIGGER IF EXISTS sync_collections_delete;
780 DROP TRIGGER IF EXISTS sync_collection_members_insert;
781 DROP TRIGGER IF EXISTS sync_collection_members_delete;
782 -- smart_folders table was dropped in M015; M007 triggers are no-ops post-M015
783 DROP TRIGGER IF EXISTS sync_user_config_insert;
784 DROP TRIGGER IF EXISTS sync_user_config_update;
785 DROP TRIGGER IF EXISTS sync_user_config_delete;
786 DROP TRIGGER IF EXISTS sync_edit_history_insert;
787
788 -- samples (single PK: hash)
789 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
790 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
791 BEGIN
792 INSERT INTO sync_changelog (table_name, op, row_id, data)
793 VALUES ('samples', 'INSERT',
794 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
795 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
796 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
797 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
798 'duration', NEW.duration, 'cloud_only', NEW.cloud_only));
799 END;
800
801 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
802 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
803 BEGIN
804 INSERT INTO sync_changelog (table_name, op, row_id, data)
805 VALUES ('samples', 'UPDATE',
806 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
807 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
808 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
809 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
810 'duration', NEW.duration, 'cloud_only', NEW.cloud_only));
811 END;
812
813 CREATE TRIGGER sync_samples_delete AFTER DELETE ON samples
814 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
815 BEGIN
816 INSERT INTO sync_changelog (table_name, op, row_id, data)
817 VALUES ('samples', 'DELETE',
818 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
819 json_object('hash', OLD.hash));
820 END;
821
822 -- audio_analysis (single PK: hash)
823 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
824 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
825 BEGIN
826 INSERT INTO sync_changelog (table_name, op, row_id, data)
827 VALUES ('audio_analysis', 'INSERT',
828 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
829 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
830 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
831 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
832 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
833 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
834 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
835 'zero_crossing_rate', NEW.zero_crossing_rate));
836 END;
837
838 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
839 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
840 BEGIN
841 INSERT INTO sync_changelog (table_name, op, row_id, data)
842 VALUES ('audio_analysis', 'UPDATE',
843 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
844 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
845 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
846 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
847 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
848 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
849 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
850 'zero_crossing_rate', NEW.zero_crossing_rate));
851 END;
852
853 CREATE TRIGGER sync_audio_analysis_delete AFTER DELETE ON audio_analysis
854 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
855 BEGIN
856 INSERT INTO sync_changelog (table_name, op, row_id, data)
857 VALUES ('audio_analysis', 'DELETE',
858 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
859 json_object('hash', OLD.hash));
860 END;
861
862 -- vfs (numeric PK, row_id stays as id string; not sensitive)
863 CREATE TRIGGER sync_vfs_insert AFTER INSERT ON vfs
864 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
865 BEGIN
866 INSERT INTO sync_changelog (table_name, op, row_id, data)
867 VALUES ('vfs', 'INSERT', CAST(NEW.id AS TEXT),
868 json_object('id', NEW.id, 'name', NEW.name,
869 'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
870 'sync_files', NEW.sync_files));
871 END;
872
873 CREATE TRIGGER sync_vfs_update AFTER UPDATE ON vfs
874 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
875 BEGIN
876 INSERT INTO sync_changelog (table_name, op, row_id, data)
877 VALUES ('vfs', 'UPDATE', CAST(NEW.id AS TEXT),
878 json_object('id', NEW.id, 'name', NEW.name,
879 'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
880 'sync_files', NEW.sync_files));
881 END;
882
883 CREATE TRIGGER sync_vfs_delete AFTER DELETE ON vfs
884 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
885 BEGIN
886 INSERT INTO sync_changelog (table_name, op, row_id, data)
887 VALUES ('vfs', 'DELETE', CAST(OLD.id AS TEXT), json_object('id', OLD.id));
888 END;
889
890 -- vfs_nodes (numeric PK)
891 CREATE TRIGGER sync_vfs_nodes_insert AFTER INSERT ON vfs_nodes
892 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
893 BEGIN
894 INSERT INTO sync_changelog (table_name, op, row_id, data)
895 VALUES ('vfs_nodes', 'INSERT', CAST(NEW.id AS TEXT),
896 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
897 'name', NEW.name, 'node_type', NEW.node_type,
898 'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
899 END;
900
901 CREATE TRIGGER sync_vfs_nodes_update AFTER UPDATE ON vfs_nodes
902 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
903 BEGIN
904 INSERT INTO sync_changelog (table_name, op, row_id, data)
905 VALUES ('vfs_nodes', 'UPDATE', CAST(NEW.id AS TEXT),
906 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
907 'name', NEW.name, 'node_type', NEW.node_type,
908 'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
909 END;
910
911 CREATE TRIGGER sync_vfs_nodes_delete AFTER DELETE ON vfs_nodes
912 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
913 BEGIN
914 INSERT INTO sync_changelog (table_name, op, row_id, data)
915 VALUES ('vfs_nodes', 'DELETE', CAST(OLD.id AS TEXT), json_object('id', OLD.id));
916 END;
917
918 -- tags (composite PK: sample_hash + tag, both sensitive)
919 CREATE TRIGGER sync_tags_insert AFTER INSERT ON tags
920 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
921 BEGIN
922 INSERT INTO sync_changelog (table_name, op, row_id, data)
923 VALUES ('tags', 'INSERT',
924 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
925 NEW.sample_hash || ':' || NEW.tag),
926 json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag));
927 END;
928
929 CREATE TRIGGER sync_tags_delete AFTER DELETE ON tags
930 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
931 BEGIN
932 INSERT INTO sync_changelog (table_name, op, row_id, data)
933 VALUES ('tags', 'DELETE',
934 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
935 OLD.sample_hash || ':' || OLD.tag),
936 json_object('sample_hash', OLD.sample_hash, 'tag', OLD.tag));
937 END;
938
939 -- collections (numeric PK)
940 CREATE TRIGGER sync_collections_insert AFTER INSERT ON collections
941 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
942 BEGIN
943 INSERT INTO sync_changelog (table_name, op, row_id, data)
944 VALUES ('collections', 'INSERT', CAST(NEW.id AS TEXT),
945 json_object('id', NEW.id, 'name', NEW.name,
946 'description', NEW.description, 'created_at', NEW.created_at,
947 'filter_json', NEW.filter_json));
948 END;
949
950 CREATE TRIGGER sync_collections_update AFTER UPDATE ON collections
951 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
952 BEGIN
953 INSERT INTO sync_changelog (table_name, op, row_id, data)
954 VALUES ('collections', 'UPDATE', CAST(NEW.id AS TEXT),
955 json_object('id', NEW.id, 'name', NEW.name,
956 'description', NEW.description, 'created_at', NEW.created_at,
957 'filter_json', NEW.filter_json));
958 END;
959
960 CREATE TRIGGER sync_collections_delete AFTER DELETE ON collections
961 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
962 BEGIN
963 INSERT INTO sync_changelog (table_name, op, row_id, data)
964 VALUES ('collections', 'DELETE', CAST(OLD.id AS TEXT), json_object('id', OLD.id));
965 END;
966
967 -- collection_members (composite PK: collection_id + sample_hash, hash is sensitive)
968 CREATE TRIGGER sync_collection_members_insert AFTER INSERT ON collection_members
969 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
970 BEGIN
971 INSERT INTO sync_changelog (table_name, op, row_id, data)
972 VALUES ('collection_members', 'INSERT',
973 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
974 CAST(NEW.collection_id AS TEXT) || ':' || NEW.sample_hash),
975 json_object('collection_id', NEW.collection_id, 'sample_hash', NEW.sample_hash,
976 'added_at', NEW.added_at));
977 END;
978
979 CREATE TRIGGER sync_collection_members_delete AFTER DELETE ON collection_members
980 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
981 BEGIN
982 INSERT INTO sync_changelog (table_name, op, row_id, data)
983 VALUES ('collection_members', 'DELETE',
984 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
985 CAST(OLD.collection_id AS TEXT) || ':' || OLD.sample_hash),
986 json_object('collection_id', OLD.collection_id, 'sample_hash', OLD.sample_hash));
987 END;
988
989 -- smart_folders table was dropped in M015; not recreating its triggers.
990
991 -- user_config (key is app-defined closed set; not sensitive)
992 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
993 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
994 AND NEW.key NOT LIKE 'sync_%'
995 AND NEW.key != 'loose_files'
996 BEGIN
997 INSERT INTO sync_changelog (table_name, op, row_id, data)
998 VALUES ('user_config', 'INSERT', NEW.key,
999 json_object('key', NEW.key, 'value', NEW.value));
1000 END;
1001
1002 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
1003 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1004 AND NEW.key NOT LIKE 'sync_%'
1005 AND NEW.key != 'loose_files'
1006 BEGIN
1007 INSERT INTO sync_changelog (table_name, op, row_id, data)
1008 VALUES ('user_config', 'UPDATE', NEW.key,
1009 json_object('key', NEW.key, 'value', NEW.value));
1010 END;
1011
1012 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
1013 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1014 AND OLD.key NOT LIKE 'sync_%'
1015 AND OLD.key != 'loose_files'
1016 BEGIN
1017 INSERT INTO sync_changelog (table_name, op, row_id, data)
1018 VALUES ('user_config', 'DELETE', OLD.key, json_object('key', OLD.key));
1019 END;
1020
1021 -- edit_history (numeric PK)
1022 CREATE TRIGGER sync_edit_history_insert AFTER INSERT ON edit_history
1023 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1024 BEGIN
1025 INSERT INTO sync_changelog (table_name, op, row_id, data)
1026 VALUES ('edit_history', 'INSERT', CAST(NEW.id AS TEXT),
1027 json_object('id', NEW.id, 'source_hash', NEW.source_hash,
1028 'result_hash', NEW.result_hash, 'operation', NEW.operation,
1029 'params_json', NEW.params_json, 'created_at', NEW.created_at));
1030 END;
1031 ";
1032
1033 /// M019, soft-delete (tombstone) infrastructure for samples.
1034 ///
1035 /// Phase 1 of the multi-device sample-deletion design (see
1036 /// `docs/design-sample-deletion.md`). This migration only lands the
1037 /// schema and bumps the samples triggers to include the new column in
1038 /// their wire-format JSON. No code path currently sets `deleted_at`, so
1039 /// every existing read filter (`WHERE samples.deleted_at IS NULL`) is a
1040 /// no-op until Phase 2 wires up the tombstone+undelete operations.
1041 ///
1042 /// Index is partial, only tombstoned rows are indexed, so the index
1043 /// stays tiny in steady-state (most samples are live).
1044 ///
1045 /// `sample_tombstone_retain_days` defaults to 30 (matches OS Trash
1046 /// conventions). User-configurable via the existing user_config sync
1047 /// trigger; the value syncs across devices.
1048 const MIGRATION_019: &str = r"
1049 ALTER TABLE samples ADD COLUMN deleted_at INTEGER;
1050 CREATE INDEX IF NOT EXISTS idx_samples_deleted_at
1051 ON samples(deleted_at) WHERE deleted_at IS NOT NULL;
1052
1053 -- Suppress the user_config sync trigger for the duration of this seed
1054 -- INSERT, otherwise the migration would push a spurious row into
1055 -- sync_changelog on every fresh install. The trigger's WHEN clause
1056 -- short-circuits while applying_remote = '1'. Both flips run inside
1057 -- the migration's transaction, so a crash mid-migration rolls back the
1058 -- flag-set along with everything else.
1059 UPDATE sync_state SET value = '1' WHERE key = 'applying_remote';
1060 INSERT OR IGNORE INTO user_config (key, value)
1061 VALUES ('sample_tombstone_retain_days', '30');
1062 UPDATE sync_state SET value = '0' WHERE key = 'applying_remote';
1063
1064 -- Re-emit samples triggers so deleted_at flows through the wire JSON.
1065 -- Existing INSERT/UPDATE bodies list columns explicitly; the new column
1066 -- needs to be added to the json_object call (it doesn't pick up
1067 -- automatically). DELETE trigger needs the column too so the receiving
1068 -- device's apply_upsert sees the tombstone state on a re-INSERT path.
1069 DROP TRIGGER IF EXISTS sync_samples_insert;
1070 DROP TRIGGER IF EXISTS sync_samples_update;
1071 DROP TRIGGER IF EXISTS sync_samples_delete;
1072
1073 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
1074 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1075 BEGIN
1076 INSERT INTO sync_changelog (table_name, op, row_id, data)
1077 VALUES ('samples', 'INSERT',
1078 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
1079 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
1080 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
1081 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
1082 'duration', NEW.duration, 'cloud_only', NEW.cloud_only,
1083 'source_path', NEW.source_path, 'deleted_at', NEW.deleted_at));
1084 END;
1085
1086 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
1087 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1088 BEGIN
1089 INSERT INTO sync_changelog (table_name, op, row_id, data)
1090 VALUES ('samples', 'UPDATE',
1091 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
1092 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
1093 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
1094 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
1095 'duration', NEW.duration, 'cloud_only', NEW.cloud_only,
1096 'source_path', NEW.source_path, 'deleted_at', NEW.deleted_at));
1097 END;
1098
1099 CREATE TRIGGER sync_samples_delete AFTER DELETE ON samples
1100 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1101 BEGIN
1102 INSERT INTO sync_changelog (table_name, op, row_id, data)
1103 VALUES ('samples', 'DELETE',
1104 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
1105 json_object('hash', OLD.hash));
1106 END;
1107 ";
1108
1109 const MIGRATION_020: &str = r"
1110 -- Phase 0 of the hybrid tag classifier: persist the 35-element feature vector
1111 -- (9 scalar + 26 MFCC) per sample as the foundation for the rules + k-NN pipeline.
1112 -- The vector is deterministic DSP (non-reversible to audio), stored as a JSON array
1113 -- of f64. feat_version stamps the extraction layout so stale vectors can be recomputed
1114 -- rather than silently mixed.
1115 CREATE TABLE IF NOT EXISTS sample_features (
1116 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
1117 feat_version INTEGER NOT NULL,
1118 vector TEXT NOT NULL,
1119 computed_at INTEGER NOT NULL
1120 );
1121 CREATE INDEX IF NOT EXISTS idx_sample_features_version ON sample_features(feat_version);
1122
1123 -- Sync triggers (mirror audio_analysis: hashed row_id so the sample SHA-256 never
1124 -- goes on the wire; DELETE carries the canonical key in `data`).
1125 CREATE TRIGGER IF NOT EXISTS sync_sample_features_insert AFTER INSERT ON sample_features
1126 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1127 BEGIN
1128 INSERT INTO sync_changelog (table_name, op, row_id, data)
1129 VALUES ('sample_features', 'INSERT',
1130 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
1131 json_object('hash', NEW.hash, 'feat_version', NEW.feat_version,
1132 'vector', NEW.vector, 'computed_at', NEW.computed_at));
1133 END;
1134
1135 CREATE TRIGGER IF NOT EXISTS sync_sample_features_update AFTER UPDATE ON sample_features
1136 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1137 BEGIN
1138 INSERT INTO sync_changelog (table_name, op, row_id, data)
1139 VALUES ('sample_features', 'UPDATE',
1140 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
1141 json_object('hash', NEW.hash, 'feat_version', NEW.feat_version,
1142 'vector', NEW.vector, 'computed_at', NEW.computed_at));
1143 END;
1144
1145 CREATE TRIGGER IF NOT EXISTS sync_sample_features_delete AFTER DELETE ON sample_features
1146 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1147 BEGIN
1148 INSERT INTO sync_changelog (table_name, op, row_id, data)
1149 VALUES ('sample_features', 'DELETE',
1150 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
1151 json_object('hash', OLD.hash));
1152 END;
1153 ";
1154
1155 const MIGRATION_021: &str = r"
1156 -- Phase 1 of the hybrid tag classifier: deterministic tag rules (Layer A).
1157 -- Ordered IF/THEN rules over sample metadata + DSP features. Ships empty.
1158 CREATE TABLE IF NOT EXISTS tag_rules (
1159 id TEXT PRIMARY KEY,
1160 name TEXT NOT NULL,
1161 enabled INTEGER NOT NULL DEFAULT 1,
1162 priority INTEGER NOT NULL,
1163 match_mode TEXT NOT NULL,
1164 conditions TEXT NOT NULL,
1165 actions TEXT NOT NULL,
1166 created_at INTEGER NOT NULL
1167 );
1168 CREATE INDEX IF NOT EXISTS idx_tag_rules_priority ON tag_rules(priority);
1169
1170 -- Sync triggers (opaque non-sensitive id => cleartext row_id, like collections).
1171 CREATE TRIGGER IF NOT EXISTS sync_tag_rules_insert AFTER INSERT ON tag_rules
1172 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1173 BEGIN
1174 INSERT INTO sync_changelog (table_name, op, row_id, data)
1175 VALUES ('tag_rules', 'INSERT', NEW.id,
1176 json_object('id', NEW.id, 'name', NEW.name, 'enabled', NEW.enabled,
1177 'priority', NEW.priority, 'match_mode', NEW.match_mode,
1178 'conditions', NEW.conditions, 'actions', NEW.actions,
1179 'created_at', NEW.created_at));
1180 END;
1181
1182 CREATE TRIGGER IF NOT EXISTS sync_tag_rules_update AFTER UPDATE ON tag_rules
1183 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1184 BEGIN
1185 INSERT INTO sync_changelog (table_name, op, row_id, data)
1186 VALUES ('tag_rules', 'UPDATE', NEW.id,
1187 json_object('id', NEW.id, 'name', NEW.name, 'enabled', NEW.enabled,
1188 'priority', NEW.priority, 'match_mode', NEW.match_mode,
1189 'conditions', NEW.conditions, 'actions', NEW.actions,
1190 'created_at', NEW.created_at));
1191 END;
1192
1193 CREATE TRIGGER IF NOT EXISTS sync_tag_rules_delete AFTER DELETE ON tag_rules
1194 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1195 BEGIN
1196 INSERT INTO sync_changelog (table_name, op, row_id, data)
1197 VALUES ('tag_rules', 'DELETE', OLD.id, json_object('id', OLD.id));
1198 END;
1199 ";
1200
1201 const MIGRATION_022: &str = r"
1202 -- Phase 1: tag provenance. Records which machine source applied each tag so
1203 -- manual tags stay sticky (a tag with NO row here is manual). Reconciliation
1204 -- only ever touches rule-sourced tags.
1205 CREATE TABLE IF NOT EXISTS tag_provenance (
1206 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
1207 tag TEXT NOT NULL,
1208 source TEXT NOT NULL, -- 'rule' | 'ml' | 'cluster' (manual = no row)
1209 rule_id TEXT, -- tag_rules.id when source = 'rule'
1210 PRIMARY KEY (sample_hash, tag)
1211 );
1212
1213 -- Sync triggers (composite PK with sensitive sample_hash + tag => hashed row_id,
1214 -- mirroring tags; UPDATE supported because reconciliation re-stamps source/rule_id).
1215 CREATE TRIGGER IF NOT EXISTS sync_tag_provenance_insert AFTER INSERT ON tag_provenance
1216 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1217 BEGIN
1218 INSERT INTO sync_changelog (table_name, op, row_id, data)
1219 VALUES ('tag_provenance', 'INSERT',
1220 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
1221 NEW.sample_hash || ':' || NEW.tag),
1222 json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag,
1223 'source', NEW.source, 'rule_id', NEW.rule_id));
1224 END;
1225
1226 CREATE TRIGGER IF NOT EXISTS sync_tag_provenance_update AFTER UPDATE ON tag_provenance
1227 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1228 BEGIN
1229 INSERT INTO sync_changelog (table_name, op, row_id, data)
1230 VALUES ('tag_provenance', 'UPDATE',
1231 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
1232 NEW.sample_hash || ':' || NEW.tag),
1233 json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag,
1234 'source', NEW.source, 'rule_id', NEW.rule_id));
1235 END;
1236
1237 CREATE TRIGGER IF NOT EXISTS sync_tag_provenance_delete AFTER DELETE ON tag_provenance
1238 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1239 BEGIN
1240 INSERT INTO sync_changelog (table_name, op, row_id, data)
1241 VALUES ('tag_provenance', 'DELETE',
1242 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
1243 OLD.sample_hash || ':' || OLD.tag),
1244 json_object('sample_hash', OLD.sample_hash, 'tag', OLD.tag));
1245 END;
1246 ";
1247
1248 const MIGRATION_023: &str = r"
1249 -- Phase 3: per-tag ML thresholds (Layer B policy). Absent tag => default policy in code.
1250 CREATE TABLE IF NOT EXISTS tag_policy (
1251 tag TEXT PRIMARY KEY,
1252 review_threshold REAL NOT NULL,
1253 auto_threshold REAL NOT NULL
1254 );
1255
1256 -- Sync triggers (tag string is sensitive => hashed row_id, mirroring tags; UPDATE
1257 -- supported because set_policy upserts).
1258 CREATE TRIGGER IF NOT EXISTS sync_tag_policy_insert AFTER INSERT ON tag_policy
1259 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1260 BEGIN
1261 INSERT INTO sync_changelog (table_name, op, row_id, data)
1262 VALUES ('tag_policy', 'INSERT',
1263 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.tag),
1264 json_object('tag', NEW.tag, 'review_threshold', NEW.review_threshold,
1265 'auto_threshold', NEW.auto_threshold));
1266 END;
1267
1268 CREATE TRIGGER IF NOT EXISTS sync_tag_policy_update AFTER UPDATE ON tag_policy
1269 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1270 BEGIN
1271 INSERT INTO sync_changelog (table_name, op, row_id, data)
1272 VALUES ('tag_policy', 'UPDATE',
1273 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.tag),
1274 json_object('tag', NEW.tag, 'review_threshold', NEW.review_threshold,
1275 'auto_threshold', NEW.auto_threshold));
1276 END;
1277
1278 CREATE TRIGGER IF NOT EXISTS sync_tag_policy_delete AFTER DELETE ON tag_policy
1279 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1280 BEGIN
1281 INSERT INTO sync_changelog (table_name, op, row_id, data)
1282 VALUES ('tag_policy', 'DELETE',
1283 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.tag),
1284 json_object('tag', OLD.tag));
1285 END;
1286 ";
1287
1288 const MIGRATION_024: &str = r"
1289 -- Phase 6: optional trained head (per-library distilled logistic-regression classifier).
1290 -- A local, regenerable cache derived entirely from sample_features + tags; NOT synced
1291 -- (no changelog triggers) because it rebuilds from the exemplar store on any device.
1292 -- Singleton row (id = 1); the whole model travels as one JSON blob.
1293 CREATE TABLE IF NOT EXISTS trained_head (
1294 id INTEGER PRIMARY KEY CHECK (id = 1),
1295 feat_version INTEGER NOT NULL,
1296 exemplar_count INTEGER NOT NULL,
1297 model TEXT NOT NULL,
1298 trained_at INTEGER NOT NULL
1299 );
1300 ";
1301
1302 const MIGRATION_025: &str = r"
1303 -- Phase 7: classifier layers (.afcl file sharing). Imported exemplars/rules are grouped
1304 -- into removable, weightable layers; the user's own data is the implicit 'local' layer
1305 -- (not a row here). Local-only for now (no sync triggers): the .afcl file is the portable
1306 -- artifact and re-imports per device, cross-device sync of imported layers is a follow-up.
1307 CREATE TABLE IF NOT EXISTS classifier_layers (
1308 id TEXT PRIMARY KEY,
1309 name TEXT NOT NULL,
1310 kind TEXT NOT NULL, -- 'imported' | 'official'
1311 weight REAL NOT NULL DEFAULT 1.0,
1312 enabled INTEGER NOT NULL DEFAULT 1,
1313 source TEXT, -- .afcl filename / provenance
1314 imported_at INTEGER NOT NULL
1315 );
1316
1317 -- Imported exemplars: feature vector + tags only (no audio, no sample row), so they live
1318 -- here rather than in sample_features (which FKs to samples).
1319 CREATE TABLE IF NOT EXISTS classifier_exemplars (
1320 id INTEGER PRIMARY KEY,
1321 layer_id TEXT NOT NULL REFERENCES classifier_layers(id) ON DELETE CASCADE,
1322 feat_version INTEGER NOT NULL,
1323 vector TEXT NOT NULL, -- JSON array of 35 f64
1324 tags TEXT NOT NULL -- JSON array of String
1325 );
1326 CREATE INDEX IF NOT EXISTS idx_classifier_exemplars_layer ON classifier_exemplars(layer_id);
1327
1328 -- Membership of imported rules in a layer. The rules themselves are ordinary tag_rules
1329 -- rows (added disabled); this join lets a layer be removed in one action.
1330 CREATE TABLE IF NOT EXISTS classifier_layer_rules (
1331 layer_id TEXT NOT NULL REFERENCES classifier_layers(id) ON DELETE CASCADE,
1332 rule_id TEXT NOT NULL REFERENCES tag_rules(id) ON DELETE CASCADE,
1333 PRIMARY KEY (layer_id, rule_id)
1334 );
1335 ";
1336
1337 const MIGRATION_026: &str = r"
1338 -- Phase 7c: sync the classifier-layer tables across the user's own devices.
1339 -- classifier_exemplars is recreated with a TEXT primary key: the M025 INTEGER autoincrement
1340 -- id collides across devices (each device numbers from 1), which row-level sync can't
1341 -- reconcile. Imported exemplars are re-importable, so dropping any M025 rows is acceptable.
1342 DROP TABLE IF EXISTS classifier_exemplars;
1343 CREATE TABLE classifier_exemplars (
1344 id TEXT PRIMARY KEY, -- globally unique ('<layer_id>#<n>')
1345 layer_id TEXT NOT NULL REFERENCES classifier_layers(id) ON DELETE CASCADE,
1346 feat_version INTEGER NOT NULL,
1347 vector TEXT NOT NULL,
1348 tags TEXT NOT NULL
1349 );
1350 CREATE INDEX IF NOT EXISTS idx_classifier_exemplars_layer ON classifier_exemplars(layer_id);
1351
1352 -- Sync triggers (opaque non-sensitive ids => cleartext row_id, like tag_rules/collections;
1353 -- the JSON payload, which carries tag strings, is encrypted on the wire). recursive_triggers
1354 -- is off, so FK cascades don't fire these, afcl::remove_layer deletes children explicitly.
1355 CREATE TRIGGER IF NOT EXISTS sync_classifier_layers_insert AFTER INSERT ON classifier_layers
1356 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1357 BEGIN
1358 INSERT INTO sync_changelog (table_name, op, row_id, data)
1359 VALUES ('classifier_layers', 'INSERT', NEW.id,
1360 json_object('id', NEW.id, 'name', NEW.name, 'kind', NEW.kind, 'weight', NEW.weight,
1361 'enabled', NEW.enabled, 'source', NEW.source, 'imported_at', NEW.imported_at));
1362 END;
1363 CREATE TRIGGER IF NOT EXISTS sync_classifier_layers_update AFTER UPDATE ON classifier_layers
1364 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1365 BEGIN
1366 INSERT INTO sync_changelog (table_name, op, row_id, data)
1367 VALUES ('classifier_layers', 'UPDATE', NEW.id,
1368 json_object('id', NEW.id, 'name', NEW.name, 'kind', NEW.kind, 'weight', NEW.weight,
1369 'enabled', NEW.enabled, 'source', NEW.source, 'imported_at', NEW.imported_at));
1370 END;
1371 CREATE TRIGGER IF NOT EXISTS sync_classifier_layers_delete AFTER DELETE ON classifier_layers
1372 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1373 BEGIN
1374 INSERT INTO sync_changelog (table_name, op, row_id, data)
1375 VALUES ('classifier_layers', 'DELETE', OLD.id, json_object('id', OLD.id));
1376 END;
1377
1378 CREATE TRIGGER IF NOT EXISTS sync_classifier_exemplars_insert AFTER INSERT ON classifier_exemplars
1379 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1380 BEGIN
1381 INSERT INTO sync_changelog (table_name, op, row_id, data)
1382 VALUES ('classifier_exemplars', 'INSERT', NEW.id,
1383 json_object('id', NEW.id, 'layer_id', NEW.layer_id, 'feat_version', NEW.feat_version,
1384 'vector', NEW.vector, 'tags', NEW.tags));
1385 END;
1386 CREATE TRIGGER IF NOT EXISTS sync_classifier_exemplars_delete AFTER DELETE ON classifier_exemplars
1387 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1388 BEGIN
1389 INSERT INTO sync_changelog (table_name, op, row_id, data)
1390 VALUES ('classifier_exemplars', 'DELETE', OLD.id, json_object('id', OLD.id));
1391 END;
1392
1393 CREATE TRIGGER IF NOT EXISTS sync_classifier_layer_rules_insert AFTER INSERT ON classifier_layer_rules
1394 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1395 BEGIN
1396 INSERT INTO sync_changelog (table_name, op, row_id, data)
1397 VALUES ('classifier_layer_rules', 'INSERT', NEW.layer_id || ':' || NEW.rule_id,
1398 json_object('layer_id', NEW.layer_id, 'rule_id', NEW.rule_id));
1399 END;
1400 CREATE TRIGGER IF NOT EXISTS sync_classifier_layer_rules_delete AFTER DELETE ON classifier_layer_rules
1401 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1402 BEGIN
1403 INSERT INTO sync_changelog (table_name, op, row_id, data)
1404 VALUES ('classifier_layer_rules', 'DELETE', OLD.layer_id || ':' || OLD.rule_id,
1405 json_object('layer_id', OLD.layer_id, 'rule_id', OLD.rule_id));
1406 END;
1407 ";
1408
1409 const MIGRATION_027: &str = r"
1410 -- FTS5 trigram index over vfs_nodes.name for fast substring search.
1411 --
1412 -- The trigram tokenizer lets `name LIKE '%query%'` use the index instead of
1413 -- scanning all of vfs_nodes on every keystroke, which is the scale cliff the
1414 -- ultra-fuzz audit flagged. The table is content-bearing (standalone) and keyed
1415 -- by rowid = vfs_nodes.id, kept in sync by triggers that fire on EVERY vfs_nodes
1416 -- change -- including remote sync applies (no applying_remote guard), because
1417 -- the local derived index must always mirror the local rows. The FTS table and
1418 -- its shadow tables are local-only and never synced (no sync_changelog triggers).
1419 CREATE VIRTUAL TABLE IF NOT EXISTS vfs_nodes_fts USING fts5(
1420 name,
1421 tokenize = 'trigram'
1422 );
1423
1424 -- Backfill from existing rows.
1425 INSERT INTO vfs_nodes_fts(rowid, name) SELECT id, name FROM vfs_nodes;
1426
1427 CREATE TRIGGER IF NOT EXISTS vfs_nodes_fts_insert AFTER INSERT ON vfs_nodes BEGIN
1428 INSERT INTO vfs_nodes_fts(rowid, name) VALUES (NEW.id, NEW.name);
1429 END;
1430
1431 CREATE TRIGGER IF NOT EXISTS vfs_nodes_fts_delete AFTER DELETE ON vfs_nodes BEGIN
1432 DELETE FROM vfs_nodes_fts WHERE rowid = OLD.id;
1433 END;
1434
1435 CREATE TRIGGER IF NOT EXISTS vfs_nodes_fts_update AFTER UPDATE OF name ON vfs_nodes BEGIN
1436 DELETE FROM vfs_nodes_fts WHERE rowid = OLD.id;
1437 INSERT INTO vfs_nodes_fts(rowid, name) VALUES (NEW.id, NEW.name);
1438 END;
1439 ";
1440
1441 const MIGRATION_028: &str = r"
1442 -- Indexes for the search filter columns on audio_analysis. A text-less global
1443 -- filter (e.g. a BPM range with no name query) otherwise full-scans
1444 -- audio_analysis; these let the planner seek instead. musical_key is an
1445 -- equality/IN filter (most selective); bpm/peak_db/duration are range filters.
1446 -- All additive and idempotent. (An index on the retired classification column
1447 -- was here too; removed with the column.)
1448 CREATE INDEX IF NOT EXISTS idx_analysis_musical_key ON audio_analysis(musical_key);
1449 CREATE INDEX IF NOT EXISTS idx_analysis_bpm ON audio_analysis(bpm);
1450 CREATE INDEX IF NOT EXISTS idx_analysis_peak_db ON audio_analysis(peak_db);
1451 CREATE INDEX IF NOT EXISTS idx_analysis_duration ON audio_analysis(duration);
1452 ";
1453
1454 const MIGRATION_029: &str = r"
1455 -- Enforce root-node name uniqueness at the engine level. SQLite's UNIQUE
1456 -- treats every NULL as distinct, so the table-level UNIQUE(vfs_id, parent_id,
1457 -- name) does NOT cover root nodes (parent_id IS NULL); uniqueness there was
1458 -- guarded only by a COUNT-then-INSERT check, which is not atomic. Because the
1459 -- DB is shared across the CLAP host thread and the GUI thread, two concurrent
1460 -- same-name root creates could both pass the COUNT and both insert. A partial
1461 -- unique index makes that race unrepresentable; the COUNT check stays as the
1462 -- friendly-error fast path. Additive and idempotent.
1463 CREATE UNIQUE INDEX IF NOT EXISTS idx_vfs_root_name
1464 ON vfs_nodes(vfs_id, name) WHERE parent_id IS NULL;
1465 ";
1466
1467 const MIGRATION_030: &str = r#"
1468 -- HLC (hybrid logical clock) conflict resolution for sync. The local changelog
1469 -- gains an `hlc` column: the sync layer stamps each pending row with a minted
1470 -- HLC before push (the trigger can't compute one), and the same value is the
1471 -- "local pending" clock for conflict detection. `hlc_ledger` records the
1472 -- committed HLC per (table, row_id), from our own pushes and applied remotes,
1473 -- so a stale remote change (older HLC) can be dropped instead of clobbering a
1474 -- newer local value (the prior blind last-writer-wins). Both are local-only
1475 -- (not synced, no triggers); additive and idempotent.
1476 ALTER TABLE sync_changelog ADD COLUMN hlc TEXT;
1477 CREATE TABLE IF NOT EXISTS hlc_ledger (
1478 table_name TEXT NOT NULL,
1479 row_id TEXT NOT NULL,
1480 hlc TEXT NOT NULL,
1481 PRIMARY KEY (table_name, row_id)
1482 );
1483 "#;
1484
1485 const MIGRATION_031: &str = r#"
1486 -- Single source of truth for "a sample the user can see". Soft-delete
1487 -- (deleted_at set, row retained for the tombstone-retention window) is live, so
1488 -- any membership/listing/count query that reads the join tables (tags,
1489 -- collection_members, vfs_nodes) must exclude tombstoned samples. That filter
1490 -- was opt-in per query and drifted: search.rs and rules.rs filtered, but
1491 -- tags/collections/list_full_tree did not, surfacing deleted samples in tag and
1492 -- collection views and recreating dangling mirror symlinks. Defining the filter
1493 -- once as a view means callers say `FROM live_samples` and the predicate lives
1494 -- in exactly one place. Local-only derived object; no triggers, not synced.
1495 CREATE VIEW IF NOT EXISTS live_samples AS
1496 SELECT * FROM samples WHERE deleted_at IS NULL;
1497 "#;
1498
1499 const MIGRATION_032: &str = r"
1500 -- M018 recreated the audio_analysis sync triggers (to salt row_id) but copied
1501 -- the pre-M011 column list, silently dropping spectral_bandwidth,
1502 -- centroid_variance, crest_factor, and attack_time (and the since-retired
1503 -- classification_confidence) from the sync_changelog payload. The initial
1504 -- snapshot still carries them, but ongoing edits to those columns never
1505 -- propagate across devices. Recreate
1506 -- the insert/update triggers with M018's salted row_id AND the full column set.
1507 DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
1508 DROP TRIGGER IF EXISTS sync_audio_analysis_update;
1509
1510 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
1511 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1512 BEGIN
1513 INSERT INTO sync_changelog (table_name, op, row_id, data)
1514 VALUES ('audio_analysis', 'INSERT',
1515 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
1516 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
1517 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
1518 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
1519 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
1520 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
1521 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
1522 'zero_crossing_rate', NEW.zero_crossing_rate,
1523 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
1524 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
1525 END;
1526
1527 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
1528 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1529 BEGIN
1530 INSERT INTO sync_changelog (table_name, op, row_id, data)
1531 VALUES ('audio_analysis', 'UPDATE',
1532 hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
1533 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
1534 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
1535 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
1536 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
1537 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
1538 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
1539 'zero_crossing_rate', NEW.zero_crossing_rate,
1540 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
1541 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
1542 END;
1543 ";
1544
1545 const MIGRATION_033: &str = r"
1546 -- Generate the user_config export filter from the ConfigKey registry instead of
1547 -- a hand-maintained trigger predicate. `config_key_policy` is seeded at every
1548 -- open from `audiofiles_core::config_key::ConfigKey::ALL` (see
1549 -- Database::seed_config_key_policy); the triggers below enqueue a changelog row
1550 -- only for keys the registry marks replicated. This closes the CHRONIC where the
1551 -- old `NEW.key != 'loose_files'` denylist never covered mirror_path /
1552 -- mirror_enabled / import_preflight_disabled, letting a hostile server steer a
1553 -- local filesystem write root across the sync boundary (fuzz-2026-07-21 #3).
1554 -- Unknown keys are absent from the policy table and therefore never exported,
1555 -- the filter fails closed.
1556 CREATE TABLE IF NOT EXISTS config_key_policy (
1557 key TEXT PRIMARY KEY,
1558 replicated INTEGER NOT NULL
1559 );
1560
1561 DROP TRIGGER IF EXISTS sync_user_config_insert;
1562 DROP TRIGGER IF EXISTS sync_user_config_update;
1563 DROP TRIGGER IF EXISTS sync_user_config_delete;
1564
1565 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
1566 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1567 AND EXISTS (SELECT 1 FROM config_key_policy p WHERE p.key = NEW.key AND p.replicated = 1)
1568 BEGIN
1569 INSERT INTO sync_changelog (table_name, op, row_id, data)
1570 VALUES ('user_config', 'INSERT', NEW.key,
1571 json_object('key', NEW.key, 'value', NEW.value));
1572 END;
1573
1574 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
1575 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1576 AND EXISTS (SELECT 1 FROM config_key_policy p WHERE p.key = NEW.key AND p.replicated = 1)
1577 BEGIN
1578 INSERT INTO sync_changelog (table_name, op, row_id, data)
1579 VALUES ('user_config', 'UPDATE', NEW.key,
1580 json_object('key', NEW.key, 'value', NEW.value));
1581 END;
1582
1583 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
1584 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
1585 AND EXISTS (SELECT 1 FROM config_key_policy p WHERE p.key = OLD.key AND p.replicated = 1)
1586 BEGIN
1587 INSERT INTO sync_changelog (table_name, op, row_id, data)
1588 VALUES ('user_config', 'DELETE', OLD.key, json_object('key', OLD.key));
1589 END;
1590 ";
1591
1592 pub(super) const MIGRATION_034: &str = r"
1593 -- Normalise musical_key to the '<note> major' / '<note> minor' spelling.
1594 --
1595 -- detect_bpm_key stored stratum_dsp's compact DJ-style name ('Am', 'C#m', 'C')
1596 -- straight through with no normalisation, while search::compatible_keys matches
1597 -- on ends_with('minor') against a table of '<note> minor' strings and
1598 -- analysis::suggest builds its tag by replacing a space that was never there.
1599 -- Key-compatible search therefore matched nothing and the tag came out as
1600 -- 'key.am'. The detector now normalises at the boundary; this rewrites rows
1601 -- written before that.
1602 --
1603 -- substr drops only the trailing minor marker; replace(.., 'm', '') would be
1604 -- equivalent today only because no note name contains an 'm', which is not a
1605 -- property worth depending on. Rows already canonical contain a space and are
1606 -- excluded by the WHERE clause, so re-running is a no-op.
1607 --
1608 -- Suppressed from the changelog: audio_analysis carries a sync UPDATE trigger,
1609 -- so without this every analysed sample would enqueue a row on upgrade. Worse
1610 -- than the volume, replicating it is wrong. Every device runs this migration
1611 -- itself and converges on the same value, while a peer still on the old code
1612 -- would receive canonical values and keep writing 'Am' from its own detector,
1613 -- leaving a vault holding both spellings. The UPDATE is a no-op when the key is
1614 -- absent, which is the case on any vault where sync was never configured (the
1615 -- row is seeded by the sync crate, not here), and the trigger's WHEN clause
1616 -- compares NULL and never fires there either.
1617 UPDATE sync_state SET value = '1' WHERE key = 'applying_remote';
1618
1619 UPDATE audio_analysis
1620 SET musical_key = CASE
1621 WHEN musical_key LIKE '%m'
1622 THEN substr(musical_key, 1, length(musical_key) - 1) || ' minor'
1623 ELSE musical_key || ' major'
1624 END
1625 WHERE musical_key IS NOT NULL
1626 AND musical_key NOT LIKE '% major'
1627 AND musical_key NOT LIKE '% minor'
1628 AND musical_key != '';
1629
1630 UPDATE sync_state SET value = '0' WHERE key = 'applying_remote';
1631 ";
1632
1633 pub(super) const MIGRATION_035: &str = r"
1634 -- Rewrite key.* tags written from the pre-M034 key spelling.
1635 --
1636 -- M034 fixed audio_analysis.musical_key, but tags are user-accepted copies of
1637 -- what analysis::suggest proposed at the time, so a vault can still hold
1638 -- 'key.am' and 'key.c-sharpm'. suggest builds the tag as
1639 -- lowercase -> ' ' to '-' -> '#' to '-sharp', so the compact 'Am' became
1640 -- 'key.am' and 'C#m' became 'key.c-sharpm', against 'key.a-minor' and
1641 -- 'key.c-sharp-minor' now.
1642 --
1643 -- The 24 legacy spellings are enumerated rather than pattern-matched. A LIKE
1644 -- rule broad enough to catch 'key.c' would also catch any user tag in the key
1645 -- namespace, and silently mangling a hand-written tag is worse than leaving a
1646 -- stale one.
1647 CREATE TEMP TABLE legacy_key_tags (legacy TEXT PRIMARY KEY, canonical TEXT NOT NULL);
1648 INSERT INTO legacy_key_tags (legacy, canonical) VALUES
1649 ('key.c', 'key.c-major'),
1650 ('key.c-sharp', 'key.c-sharp-major'),
1651 ('key.d', 'key.d-major'),
1652 ('key.d-sharp', 'key.d-sharp-major'),
1653 ('key.e', 'key.e-major'),
1654 ('key.f', 'key.f-major'),
1655 ('key.f-sharp', 'key.f-sharp-major'),
1656 ('key.g', 'key.g-major'),
1657 ('key.g-sharp', 'key.g-sharp-major'),
1658 ('key.a', 'key.a-major'),
1659 ('key.a-sharp', 'key.a-sharp-major'),
1660 ('key.b', 'key.b-major'),
1661 ('key.cm', 'key.c-minor'),
1662 ('key.c-sharpm', 'key.c-sharp-minor'),
1663 ('key.dm', 'key.d-minor'),
1664 ('key.d-sharpm', 'key.d-sharp-minor'),
1665 ('key.em', 'key.e-minor'),
1666 ('key.fm', 'key.f-minor'),
1667 ('key.f-sharpm', 'key.f-sharp-minor'),
1668 ('key.gm', 'key.g-minor'),
1669 ('key.g-sharpm', 'key.g-sharp-minor'),
1670 ('key.am', 'key.a-minor'),
1671 ('key.a-sharpm', 'key.a-sharp-minor'),
1672 ('key.bm', 'key.b-minor');
1673
1674 -- Suppressed for the same reason as M034; see the note there.
1675 UPDATE sync_state SET value = '1' WHERE key = 'applying_remote';
1676
1677 -- Insert-then-delete rather than UPDATE: (sample_hash, tag) is the primary key,
1678 -- and a sample already carrying both spellings would collide. OR IGNORE keeps
1679 -- the existing canonical row in that case.
1680 INSERT OR IGNORE INTO tags (sample_hash, tag)
1681 SELECT t.sample_hash, m.canonical
1682 FROM tags t
1683 JOIN legacy_key_tags m ON t.tag = m.legacy;
1684
1685 DELETE FROM tags WHERE tag IN (SELECT legacy FROM legacy_key_tags);
1686
1687 UPDATE sync_state SET value = '0' WHERE key = 'applying_remote';
1688
1689 DROP TABLE legacy_key_tags;
1690 ";
1691
1692 const MIGRATION_036: &str = r"
1693 -- Indexes for the measured browse axes, same reasoning as M028: a range filter
1694 -- with no text query otherwise full-scans audio_analysis, and these three are
1695 -- now first-class browse dimensions rather than columns nothing queried.
1696 -- Additive and idempotent.
1697 CREATE INDEX IF NOT EXISTS idx_analysis_spectral_centroid ON audio_analysis(spectral_centroid);
1698 CREATE INDEX IF NOT EXISTS idx_analysis_spectral_flatness ON audio_analysis(spectral_flatness);
1699 CREATE INDEX IF NOT EXISTS idx_analysis_attack_time ON audio_analysis(attack_time);
1700 ";
1701
1702 const MIGRATION_037: &str = r"
1703 -- Cover the browse-list sort order, which is the worst-case list load: opening
1704 -- the browser with no filter applied.
1705 --
1706 -- `search_global` and `search_dir` both end `ORDER BY n.node_type, n.name
1707 -- LIMIT 500`, and nothing indexed that pair. SQLite therefore scanned every
1708 -- vfs_nodes row, built a temp B-tree over all of them, sorted, and discarded
1709 -- all but 500. SEARCH_RESULT_LIMIT bounds what comes back, never the work
1710 -- underneath it, so the cost grew with the library while the result set did not.
1711 --
1712 -- Measured on a 40,200-node database, unfiltered `search_global`:
1713 -- before 63.04 ms SCAN n + USE TEMP B-TREE FOR ORDER BY
1714 -- after 0.81 ms SCAN n USING INDEX idx_vfs_nodes_sort, no temp B-tree
1715 -- The sort disappears from the plan: SQLite walks the index in order and stops
1716 -- once the LIMIT is met.
1717 --
1718 -- The index costs about 4.6 MB at 40k nodes, so roughly 34 MB at the 289k-node
1719 -- library the extrapolation was aimed at. That is the trade, and it is the right
1720 -- way round: the list load is on the path a user waits for, and disk is not.
1721 --
1722 -- Additive and idempotent.
1723 CREATE INDEX IF NOT EXISTS idx_vfs_nodes_sort ON vfs_nodes(node_type, name);
1724 ";
1725
1726 const MIGRATION_038: &str = r"
1727 -- The persisted k-nearest-neighbour graph over the similarity features.
1728 --
1729 -- Derived data, on the waveform_data model (M004): recomputable from
1730 -- audio_analysis, and machine-dependent besides, because the distances are
1731 -- normalized against this library's global feature ranges. So it carries no
1732 -- sync triggers at all, where every hand-entered table near it carries three.
1733 --
1734 -- What it buys: a similarity query answers from a table read instead of a
1735 -- VP-tree build, and the neighbourhood becomes a structure other things can be
1736 -- built on (chains, regions, 'what is unlike everything') rather than a ranking
1737 -- computed and thrown away per ask.
1738 CREATE TABLE IF NOT EXISTS sample_neighbours (
1739 hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
1740 neighbour_hash TEXT NOT NULL,
1741 distance REAL NOT NULL,
1742 rank INTEGER NOT NULL,
1743 PRIMARY KEY (hash, neighbour_hash)
1744 );
1745 CREATE INDEX IF NOT EXISTS idx_sample_neighbours_rank ON sample_neighbours(hash, rank);
1746 -- The back-edge index. A delete has to find every source pointing AT the gone
1747 -- sample, which is the one access this table makes against the grain of its
1748 -- primary key.
1749 CREATE INDEX IF NOT EXISTS idx_sample_neighbours_back ON sample_neighbours(neighbour_hash);
1750
1751 -- Single-row provenance: the k the edges were computed for, and the
1752 -- normalization ranges they were computed under. A range that has since widened
1753 -- invalidates every stored distance in principle, so the ranges are what the
1754 -- refresh pass compares against to choose rebuild over incremental update.
1755 CREATE TABLE IF NOT EXISTS neighbour_graph_meta (
1756 id INTEGER PRIMARY KEY CHECK (id = 1),
1757 k INTEGER NOT NULL,
1758 ranges TEXT NOT NULL,
1759 stale INTEGER NOT NULL DEFAULT 0,
1760 built_at INTEGER NOT NULL
1761 );
1762
1763 -- Samples whose out-edges need recomputing: freshly analysed, or left short by
1764 -- a deleted neighbour. Drained by the refresh pass on the next similarity
1765 -- query, which is a point where a VP-tree is being built anyway. Persisted
1766 -- rather than held in worker memory so a restart mid-import does not lose track
1767 -- of which sources are behind.
1768 CREATE TABLE IF NOT EXISTS neighbour_graph_dirty (
1769 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE
1770 );
1771
1772 -- Out-edges cascade with the sample row. Back-edges do not: they name a hash
1773 -- that is not the row's own, so no foreign key connects them to the delete.
1774 -- Without this the table accumulates edges pointing at samples that are gone,
1775 -- and the sources holding them are silently short of k.
1776 CREATE TRIGGER IF NOT EXISTS neighbour_graph_delete_back_edges
1777 AFTER DELETE ON samples
1778 BEGIN
1779 INSERT OR IGNORE INTO neighbour_graph_dirty (hash)
1780 SELECT hash FROM sample_neighbours WHERE neighbour_hash = OLD.hash;
1781 DELETE FROM sample_neighbours WHERE neighbour_hash = OLD.hash;
1782 END;
1783 ";
1784
1785 const MIGRATION_039: &str = r"
1786 -- Clusters become first-class named objects.
1787 --
1788 -- Before this, `cluster_library` produced a grouping and then threw it away:
1789 -- `apply_cluster_tag` wrote tags and the pile itself stopped existing. The
1790 -- grouping was the valuable part. A persisted cluster is what answers 'what is
1791 -- this pile' for the population the filename rules cannot answer at all.
1792 --
1793 -- Membership is derived, on the `sample_neighbours` model in M038: it is
1794 -- recomputable from `sample_features`, and machine-dependent besides, because
1795 -- k-means runs over features standardized against this library's own ranges.
1796 -- So neither table carries sync triggers. The user's name is NOT derived, and
1797 -- whether it should sync is a separate decision, filed rather than answered
1798 -- here.
1799 CREATE TABLE IF NOT EXISTS clusters (
1800 id INTEGER PRIMARY KEY,
1801 -- NULL until the user names it. Naming is the whole point; an unnamed
1802 -- cluster is a pile still waiting for a word.
1803 name TEXT,
1804 -- The member nearest the centroid: a playable representative, and the key a
1805 -- re-run matches on to carry the name across. Nullable because deleting the
1806 -- representative sample must not destroy the name the user typed.
1807 medoid_hash TEXT REFERENCES samples(hash) ON DELETE SET NULL,
1808 -- The run that produced it: the k asked for, the feature extractor the
1809 -- vectors came from, and when. A cluster built under a stale
1810 -- `feature_version` is comparable to nothing built since.
1811 k INTEGER NOT NULL,
1812 feature_version INTEGER NOT NULL,
1813 run_at INTEGER NOT NULL,
1814 named_at INTEGER
1815 );
1816 CREATE INDEX IF NOT EXISTS idx_clusters_medoid ON clusters(medoid_hash);
1817
1818 CREATE TABLE IF NOT EXISTS cluster_members (
1819 cluster_id INTEGER NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
1820 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
1821 PRIMARY KEY (cluster_id, sample_hash)
1822 );
1823 -- 'which pile is this sample in' is the read the detail pane makes, and it runs
1824 -- against the grain of the primary key.
1825 CREATE INDEX IF NOT EXISTS idx_cluster_members_hash ON cluster_members(sample_hash);
1826 ";
1827
1828 /// Register `hash_row_id(salt, key) -> TEXT` as a deterministic SQLite
1829 /// function on the given connection. Used by the M018 sync triggers so the
1830 /// `sync_changelog.row_id` field never carries cleartext content (tag strings,
1831 /// raw sample SHA-256s) on the wire. The salt is a per-user random nonce
1832 /// stored in `sync_state` and never synced; without it, even a global rainbow
1833 /// table over common tag strings would deanonymise users.
1834 pub(super) fn register_hash_row_id(conn: &Connection) -> Result<(), DbError> {
1835 conn.create_scalar_function(
1836 "hash_row_id",
1837 2,
1838 FunctionFlags::SQLITE_DETERMINISTIC | FunctionFlags::SQLITE_UTF8,
1839 |ctx| {
1840 let salt: String = ctx.get(0)?;
1841 let key: String = ctx.get(1)?;
1842 let mut hasher = Sha256::new();
1843 hasher.update(salt.as_bytes());
1844 hasher.update(b":");
1845 hasher.update(key.as_bytes());
1846 let digest = hasher.finalize();
1847 let mut hex = String::with_capacity(64);
1848 for byte in digest {
1849 use std::fmt::Write;
1850 let _ = write!(hex, "{byte:02x}");
1851 }
1852 Ok(hex)
1853 },
1854 )?;
1855 Ok(())
1856 }
1857
1858 /// Every migration, in order. Index + 1 is the `PRAGMA user_version` a database
1859 /// carries once that migration has been applied, so the list's length is the
1860 /// schema version this build produces.
1861 ///
1862 /// At module scope rather than inside [`Database::migrate`] so [`SCHEMA_VERSION`]
1863 /// can be derived from it: the guard against opening a newer vault and the
1864 /// migration runner have to agree on one number, and deriving it is how they
1865 /// cannot drift.
1866 pub(super) const MIGRATIONS: &[&str] = &[
1867 MIGRATION_001,
1868 MIGRATION_002,
1869 MIGRATION_003,
1870 MIGRATION_004,
1871 MIGRATION_005,
1872 MIGRATION_006,
1873 MIGRATION_007,
1874 MIGRATION_008,
1875 MIGRATION_009,
1876 MIGRATION_010,
1877 MIGRATION_011,
1878 MIGRATION_012,
1879 MIGRATION_013,
1880 MIGRATION_014,
1881 MIGRATION_015,
1882 MIGRATION_016,
1883 MIGRATION_017,
1884 MIGRATION_018,
1885 MIGRATION_019,
1886 MIGRATION_020,
1887 MIGRATION_021,
1888 MIGRATION_022,
1889 MIGRATION_023,
1890 MIGRATION_024,
1891 MIGRATION_025,
1892 MIGRATION_026,
1893 MIGRATION_027,
1894 MIGRATION_028,
1895 MIGRATION_029,
1896 MIGRATION_030,
1897 MIGRATION_031,
1898 MIGRATION_032,
1899 MIGRATION_033,
1900 MIGRATION_034,
1901 MIGRATION_035,
1902 MIGRATION_036,
1903 MIGRATION_037,
1904 MIGRATION_038,
1905 MIGRATION_039,
1906 ];
1907
1908 /// The schema version this build produces, and the highest one it can read.
1909 ///
1910 /// A vault reporting more than this was written by a newer audiofiles and is
1911 /// refused; see [`DbError::VaultTooNew`].
1912 pub const SCHEMA_VERSION: i32 = MIGRATIONS.len() as i32;
1913