max / audiofiles

9.7 KB · 267 lines History Blame Raw

1	# audiofiles Database Schema
2
3	SQLite schema reference. 12 inline migrations. Migrations are embedded as Rust string constants in `crates/audiofiles-core/src/db.rs` and applied via `PRAGMA user_version` tracking -- not separate SQL files.
4
5	## Domain Map
6
7	\| Domain \| Tables \| Purpose \|
8	\|--------\|--------\|---------\|
9	\| Samples \| 1 \| Content-addressed sample storage and metadata \|
10	\| Analysis \| 3 \| Audio analysis, waveform data, fingerprints \|
11	\| VFS \| 2 \| Virtual file system directories and nodes \|
12	\| Organization \| 4 \| Tags, collections, collection members, smart folders \|
13	\| Preferences \| 1 \| User configuration key-value store \|
14	\| SyncKit \| 2 \| Cloud sync metadata and local changelog \|
15	\| History \| 1 \| Destructive edit tracking \|
16
17	---
18
19	## Samples
20
21	### samples
22	Content-addressed sample storage. The hash (of file content) is the primary key -- duplicate files are impossible by design.
23
24	\| Column \| Type \| Notes \|
25	\|--------\|------\|-------\|
26	\| `hash` \| TEXT PK \| Content hash (SHA-256) \|
27	\| `original_name` \| TEXT \| Filename at import \|
28	\| `file_extension` \| TEXT \| e.g., `wav`, `mp3` \|
29	\| `file_size` \| INTEGER \| Bytes \|
30	\| `import_date` \| INTEGER \| Unix timestamp \|
31	\| `last_modified` \| INTEGER \| Unix timestamp \|
32	\| `cloud_only` \| INTEGER \| 1 when local blob deleted but exists in cloud (migration 008), default 0 \|
33	\| `duration` \| REAL \| Seconds, available immediately after import (migration 009), nullable \|
34
35	Index: `original_name`.
36
37	---
38
39	## Analysis
40
41	### audio_analysis
42	Spectral, temporal, and ML analysis results. One row per sample. FK to samples via hash.
43
44	\| Column \| Type \| Notes \|
45	\|--------\|------\|-------\|
46	\| `hash` \| TEXT PK FK \| -> samples (CASCADE) \|
47	\| `bpm` \| REAL \| Beats per minute, nullable \|
48	\| `musical_key` \| TEXT \| Detected key, nullable \|
49	\| `duration` \| REAL \| Seconds \|
50	\| `sample_rate` \| INTEGER \| Hz \|
51	\| `channels` \| INTEGER \| Mono=1, stereo=2 \|
52	\| `peak_db` \| REAL \| Peak amplitude, nullable \|
53	\| `rms_db` \| REAL \| RMS amplitude, nullable \|
54	\| `is_loop` \| INTEGER \| Boolean, nullable \|
55	\| `spectral_centroid` \| REAL \| Nullable \|
56	\| `onset_strength` \| REAL \| Nullable \|
57	\| `analyzed_at` \| INTEGER \| Unix timestamp \|
58	\| `lufs` \| REAL \| Loudness (migration 003), nullable \|
59	\| `spectral_flatness` \| REAL \| Migration 003, nullable \|
60	\| `spectral_rolloff` \| REAL \| Migration 003, nullable \|
61	\| `zero_crossing_rate` \| REAL \| Migration 003, nullable \|
62	\| `classification` \| TEXT \| ML category label (migration 003), nullable \|
63	\| `spectral_bandwidth` \| REAL \| Migration 010, nullable \|
64	\| `centroid_variance` \| REAL \| Migration 010, nullable \|
65	\| `crest_factor` \| REAL \| Migration 010, nullable \|
66	\| `attack_time` \| REAL \| Migration 010, nullable \|
67	\| `classification_confidence` \| REAL \| ML confidence score (migration 011), nullable \|
68
69	Indexes: `bpm`, `musical_key`, `duration`, `classification`.
70
71	### waveform_data
72	Pre-computed waveform visualizations. Migration 004.
73
74	\| Column \| Type \| Notes \|
75	\|--------\|------\|-------\|
76	\| `hash` \| TEXT PK FK \| -> samples (CASCADE) \|
77	\| `num_buckets` \| INTEGER \| Resolution \|
78	\| `peak_data` \| BLOB \| Binary peak envelope \|
79	\| `sample_rate` \| INTEGER \| Hz \|
80	\| `duration` \| REAL \| Seconds \|
81	\| `generated_at` \| INTEGER \| Unix timestamp \|
82
83	### fingerprints
84	Audio fingerprints for similarity search (VP-tree indexed). Migration 006.
85
86	\| Column \| Type \| Notes \|
87	\|--------\|------\|-------\|
88	\| `hash` \| TEXT PK FK \| -> samples (CASCADE) \|
89	\| `envelope` \| BLOB \| Binary envelope data \|
90	\| `sample_rate` \| INTEGER \| Hz \|
91	\| `generated_at` \| INTEGER \| Unix timestamp \|
92
93	---
94
95	## VFS
96
97	### vfs
98	Virtual file systems. Users create named VFS instances to organize samples independently of disk layout.
99
100	\| Column \| Type \| Notes \|
101	\|--------\|------\|-------\|
102	\| `id` \| INTEGER PK \| \|
103	\| `name` \| TEXT \| Unique \|
104	\| `created_at` \| INTEGER \| Unix timestamp \|
105	\| `modified_at` \| INTEGER \| Unix timestamp \|
106	\| `sync_files` \| INTEGER \| Per-VFS toggle for syncing audio blobs to cloud (migration 007), default 0 \|
107
108	### vfs_nodes
109	Directory and sample nodes within a VFS. Self-referential tree structure.
110
111	\| Column \| Type \| Notes \|
112	\|--------\|------\|-------\|
113	\| `id` \| INTEGER PK \| \|
114	\| `vfs_id` \| INTEGER FK \| -> vfs (CASCADE) \|
115	\| `parent_id` \| INTEGER FK \| -> vfs_nodes (CASCADE), nullable (root nodes) \|
116	\| `name` \| TEXT \| \|
117	\| `node_type` \| TEXT \| CHECK: `directory` or `sample` \|
118	\| `sample_hash` \| TEXT FK \| -> samples (CASCADE), nullable (directories) \|
119	\| `created_at` \| INTEGER \| Unix timestamp \|
120
121	Unique: `(vfs_id, parent_id, name)`. Indexes: `parent_id`, `vfs_id`, `sample_hash`.
122
123	---
124
125	## Organization
126
127	### tags
128	Flat dot-namespaced tags on samples. Migration 002 replaced the original key-value `tags` table with this simpler model.
129
130	\| Column \| Type \| Notes \|
131	\|--------\|------\|-------\|
132	\| `sample_hash` \| TEXT FK \| -> samples (CASCADE) \|
133	\| `tag` \| TEXT \| e.g., `genre.techno`, `type.kick` \|
134
135	PK: `(sample_hash, tag)`. Indexes: `sample_hash`, `tag`.
136
137	### collections
138	Named sample collections (playlists, kits).
139
140	\| Column \| Type \| Notes \|
141	\|--------\|------\|-------\|
142	\| `id` \| INTEGER PK \| \|
143	\| `name` \| TEXT \| Unique \|
144	\| `description` \| TEXT \| Nullable \|
145	\| `created_at` \| INTEGER \| Unix timestamp \|
146
147	### collection_members
148	Samples within a collection. Many-to-many.
149
150	\| Column \| Type \| Notes \|
151	\|--------\|------\|-------\|
152	\| `collection_id` \| INTEGER FK \| -> collections (CASCADE) \|
153	\| `sample_hash` \| TEXT FK \| -> samples (CASCADE) \|
154	\| `added_at` \| INTEGER \| Unix timestamp \|
155
156	PK: `(collection_id, sample_hash)`.
157
158	### smart_folders
159	Saved searches within a VFS. The query is stored as JSON and evaluated at runtime.
160
161	\| Column \| Type \| Notes \|
162	\|--------\|------\|-------\|
163	\| `id` \| INTEGER PK \| \|
164	\| `vfs_id` \| INTEGER FK \| -> vfs (CASCADE) \|
165	\| `name` \| TEXT \| \|
166	\| `query_json` \| TEXT \| Serialized search query \|
167	\| `created_at` \| INTEGER \| Unix timestamp \|
168
169	---
170
171	## Preferences
172
173	### user_config
174	Key-value preferences store. Migration 005.
175
176	\| Column \| Type \| Notes \|
177	\|--------\|------\|-------\|
178	\| `key` \| TEXT PK \| \|
179	\| `value` \| TEXT \| \|
180
181	---
182
183	## SyncKit
184
185	### sync_state
186	Sync metadata key-value store. Migration 007.
187
188	\| Column \| Type \| Notes \|
189	\|--------\|------\|-------\|
190	\| `key` \| TEXT PK \| \|
191	\| `value` \| TEXT \| \|
192
193	Seeded keys: `device_id`, `pull_cursor`, `auto_sync_enabled`, `sync_interval_minutes`, `applying_remote`, `last_sync_at`, `initial_snapshot_done`.
194
195	### sync_changelog
196	Local change log for push/pull sync. Migration 007.
197
198	\| Column \| Type \| Notes \|
199	\|--------\|------\|-------\|
200	\| `id` \| INTEGER PK \| AUTOINCREMENT \|
201	\| `table_name` \| TEXT \| Source table name \|
202	\| `op` \| TEXT \| `INSERT`, `UPDATE`, or `DELETE` \|
203	\| `row_id` \| TEXT \| PK of changed row \|
204	\| `timestamp` \| TEXT \| ISO datetime, default `datetime('now')` \|
205	\| `data` \| TEXT \| JSON snapshot of row, nullable \|
206	\| `pushed` \| INTEGER \| Boolean, default 0 \|
207
208	Index: `pushed`.
209
210	---
211
212	## History
213
214	### edit_history
215	Tracks destructive audio edits (trim, normalize, etc.) for future undo support. Migration 012.
216
217	\| Column \| Type \| Notes \|
218	\|--------\|------\|-------\|
219	\| `id` \| INTEGER PK \| AUTOINCREMENT \|
220	\| `source_hash` \| TEXT \| Hash of original sample \|
221	\| `result_hash` \| TEXT \| Hash of edited result \|
222	\| `operation` \| TEXT \| Edit operation name \|
223	\| `params_json` \| TEXT \| Operation parameters as JSON, nullable \|
224	\| `created_at` \| INTEGER \| Unix timestamp, default `unixepoch()` \|
225
226	Indexes: `source_hash`, `result_hash`.
227
228	---
229
230	## Design Patterns
231
232	- Content-addressed storage: `samples.hash` is a content hash -- the same file always produces the same PK, making deduplication automatic
233	- Inline migrations: All schema DDL is embedded as Rust `const` strings in `db.rs`, applied transactionally via `PRAGMA user_version` -- no external SQL files
234	- VFS abstraction: Virtual file systems decouple organization from disk layout; one sample can appear in multiple VFS trees via `vfs_nodes.sample_hash`
235	- Self-referential tree: `vfs_nodes.parent_id` references `vfs_nodes.id` for arbitrary directory nesting
236	- Dot-namespaced tags: Flat `tag` strings with dot convention (e.g., `genre.techno`) replace the original key-value tag model (migration 002)
237	- Sync guard triggers: All sync triggers check `applying_remote != '1'` to prevent echo loops
238	- Sync-excluded keys: `user_config` sync triggers skip keys matching `sync_%` to avoid syncing sync-internal state
239	- Cloud-only samples: `samples.cloud_only` flag allows local blob eviction while keeping metadata and cloud copy
240	- Composite row IDs for sync: Compound PKs encoded as `a:b` strings in `sync_changelog.row_id`
241	- Synced tables: `samples`, `audio_analysis`, `vfs`, `vfs_nodes`, `tags`, `collections`, `collection_members`, `smart_folders`, `user_config`, `edit_history`
242
243	## Key Indexes
244
245	- Content lookup on samples (original_name), tags (sample_hash, tag)
246	- Analysis on audio_analysis (bpm, musical_key, duration, classification)
247	- VFS tree on vfs_nodes (parent_id, vfs_id, sample_hash)
248	- History on edit_history (source_hash, result_hash)
249	- Sync on sync_changelog (pushed) for pending push detection
250
251	## Schema Evolution
252
253	\| Migration \| Change \|
254	\|-----------\|--------\|
255	\| 001 \| Initial schema (samples, audio_analysis, vfs, vfs_nodes, tags, collections, collection_members, smart_folders) \|
256	\| 002 \| Tags v2: replaced key-value (tag_name, tag_value) with flat dot-namespaced tags \|
257	\| 003 \| Extended analysis: lufs, spectral_flatness, spectral_rolloff, zero_crossing_rate, classification \|
258	\| 004 \| Waveform data table, additional analysis + sample indexes \|
259	\| 005 \| User config key-value store \|
260	\| 006 \| Fingerprints table (audio similarity search) \|
261	\| 007 \| SyncKit integration (sync_state, sync_changelog, sync triggers on all domain tables, vfs.sync_files) \|
262	\| 008 \| Cloud-only samples (cloud_only column, updated sync triggers) \|
263	\| 009 \| Duration on samples table (available before analysis) \|
264	\| 010 \| Extended spectral features (spectral_bandwidth, centroid_variance, crest_factor, attack_time) \|
265	\| 011 \| ML classification confidence score \|
266	\| 012 \| Edit history table (destructive edit tracking) \|
267