Rendering markdown & diagrams...
1 # NeuralOS Database Schemas & Data Model
2
3 Comprehensive specification of all databases, tables, foreign keys, relationships, and vector representations powering the **NeuralOS Smart Cinema**, **Decentralized Torrent Swarm Streaming**, and **Neural Episodic Memory**.
4
5 ---
6
7 ## 1. Entity-Relationship Diagram (ERD)
8
9 ```mermaid
10 erDiagram
11 MEDIA_CATALOG ||--o{ MAGNETS : "indexes streams for"
12 MEDIA_CATALOG ||--o| MEDIA_METADATA : "enriches"
13 LOCAL_MEDIA ||--o| MEDIA_METADATA : "associates"
14 MAGNETS ||--o| STREAM_SAVES : "tracks TorrServer state"
15 MAGNETS ||--o| ACTIVE_DOWNLOADS : "queues to disk"
16
17 MEDIA_CATALOG {
18 string imdb_id PK "Unique IMDb ID (e.g. tt0816692)"
19 string title "Normalized Title"
20 int year "Release Year"
21 float rating "IMDb Score (e.g. 8.7)"
22 int votes "IMDb Vote Count"
23 string category "movie or series"
24 string language "Hindi or English"
25 string genres "Comma-delimited genres"
26 string poster_url "IMDb CDN Poster URL"
27 string plot_summary "Detailed synopsis"
28 blob embedding "512-dim INT8 vector (512 bytes)"
29 }
30
31 MAGNETS {
32 int id PK "Auto-increment ID"
33 string info_hash UK "40-char Hex BitTorrent Hash"
34 string title "Release name with quality tags"
35 string category "movie or series"
36 int year "Release Year"
37 string quality "2160p, 1080p, 720p, HDRip"
38 string size_str "File size (e.g. 2.4 GB)"
39 int seeders "Active swarm seed count"
40 string magnet_uri "Full URI with trackers"
41 string source "torrentio, yts, dht"
42 string imdb_id FK "References media_catalog.imdb_id"
43 string poster_url "Poster URL"
44 string language "Hindi, English, Dual Audio"
45 string genres "Genre tags"
46 timestamp created_at "Ingestion timestamp"
47 }
48
49 LOCAL_MEDIA {
50 string full_path PK "Path on 1TB HDD (/dev/sdb1) or SSD (/dev/sdb5)"
51 string rel_path "Web accessible path"
52 string filename "Exact file name"
53 string clean_title "Sanitized title"
54 int year "Release Year"
55 string category "movie or series"
56 int size_bytes "Exact byte length"
57 string size_str "Formatted size (e.g. 1.8 GB)"
58 timestamp updated_at "Last scanned"
59 }
60
61 MEDIA_METADATA {
62 string clean_title PK "Sanitized title"
63 string category PK "movie or series"
64 int year "Release Year"
65 string imdb_id FK "References media_catalog.imdb_id"
66 string rating "Rating string"
67 string genres "Genre string"
68 string poster_url "Poster URL"
69 string description "Plot synopsis"
70 timestamp updated_at "Last sync"
71 }
72
73 STREAM_SAVES {
74 string info_hash PK "References magnets.info_hash"
75 string title "Release title"
76 string file_path "TorrServer cache file"
77 int torrent_size "Total size in bytes"
78 int downloaded_bytes "Downloaded piece count"
79 string status "streaming, completed, paused"
80 int last_opened_at "Unix epoch"
81 int completed_at "Unix epoch"
82 int retention_days "Default 5 days"
83 }
84
85 ACTIVE_DOWNLOADS {
86 string info_hash PK "References magnets.info_hash"
87 string title "Release title"
88 string target "hdd (/dev/sdb1) or ssd (/dev/sdb5)"
89 string dest_dir "Destination folder"
90 string status "downloading, seeding, done"
91 int started_at "Unix epoch"
92 int updated_at "Unix epoch"
93 }
94
95 METADATA_SYNC_STATUS {
96 int id PK "Singleton row ID (1)"
97 int last_run_at "Unix epoch"
98 int next_run_at "Unix epoch"
99 int total_local_files "File count"
100 int total_movies "Movie count"
101 int total_series "Series count"
102 int enriched_count "Enriched count"
103 int is_running "1 if active, 0 if idle"
104 string last_status "idle, syncing, ok"
105 string last_message "Status message"
106 }
107 ```
108
109 ---
110
111 ## 2. System Architecture & Flowchart
112
113 ```mermaid
114 flowchart TD
115 User["User Interface / Browser Client"] -->|"Search Query / Similarity Intent"| WebStatus["web_status.py (:8080)"]
116 WebStatus -->|"Federated Search & Filter"| CinemaEngine["cinema_engine.py"]
117
118 subgraph StorageLayer["Dell Node Storage Layer (/var/neural_memory/data/)"]
119 MediaCat[("media_catalog<br/>21,114 Titles<br/>512-dim INT8 Vectors")]
120 MagnetsDB[("magnets<br/>Verified Swarm Streams<br/>4K, 1080p, 720p, Dual")]
121 LocalMediaDB[("local_media<br/>1,175 Offline Files<br/>1TB HDD + 77GB SSD")]
122 StreamSavesDB[("stream_saves<br/>Active TorrServer States")]
123 DownloadsDB[("active_downloads<br/>Persistent Disk Queues")]
124 end
125
126 CinemaEngine -->|"1. Offline First (<5ms)"| LocalMediaDB
127 CinemaEngine -->|"2. Vector Recommendation (<15ms)"| MediaCat
128 CinemaEngine -->|"3. Instant Stream Cache (<10ms)"| MagnetsDB
129 CinemaEngine -->|"4. Fallback Crawl (over Tor)"| Torrentio["Torrentio / YTS / DHT Swarm"]
130 Torrentio -->|"Sync & Ingest"| MagnetsDB
131
132 User -->|"Play Selected Stream"| Artplayer["Artplayer HTML5 HUD"]
133 Artplayer -->|"Sequential Chunk Request"| TorrServer["TorrServer Engine (:8090)"]
134 TorrServer -->|"Sequential Stream State"| StreamSavesDB
135 TorrServer -->|"P2P Swarm Download"| DHTSwarm["BitTorrent DHT Swarm"]
136 ```
137
138 ---
139
140 ## 3. Detailed Table Specifications
141
142 ### 3.1 Database: `/var/neural_memory/data/magnets.db`
143
144 #### Table: `media_catalog`
145 The primary curated library of IMDb masterworks (Ratings ≥ 7.0, Votes ≥ 50).
146 ```sql
147 CREATE TABLE media_catalog (
148 imdb_id TEXT PRIMARY KEY,
149 title TEXT NOT NULL,
150 year INTEGER,
151 rating REAL,
152 votes INTEGER,
153 category TEXT DEFAULT 'movie',
154 language TEXT DEFAULT 'English',
155 genres TEXT,
156 poster_url TEXT,
157 plot_summary TEXT,
158 embedding BLOB
159 );
160 ```
161 * **Primary Key**: `imdb_id` (format: `tt` followed by 7-8 digits)
162 * **Embedding**: 512-byte binary INT8 scalar quantized vector produced by `embeddinggemma-2:latest`. Used for unit-normalized dot-product matrix multiplication directly on CPU in under 15ms.
163
164 #### Table: `magnets`
165 The decentralized BitTorrent stream cache.
166 ```sql
167 CREATE TABLE magnets (
168 id INTEGER PRIMARY KEY AUTOINCREMENT,
169 info_hash TEXT UNIQUE,
170 title TEXT NOT NULL,
171 category TEXT DEFAULT 'movie',
172 year INTEGER,
173 quality TEXT,
174 size_str TEXT,
175 seeders INTEGER DEFAULT 0,
176 magnet_uri TEXT NOT NULL,
177 source TEXT,
178 imdb_id TEXT,
179 poster_url TEXT,
180 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
181 language TEXT DEFAULT 'English',
182 genres TEXT DEFAULT ''
183 );
184 ```
185 * **Indexes**: Unique constraint on `info_hash`.
186 * **Foreign Key**: `imdb_id` corresponds to `media_catalog.imdb_id`.
187
188 #### Table: `local_media`
189 Tracks physical video files archived on the local 1TB HDD (`/mnt/hdd1`) and 77GB SSD (`/mnt/drive_d`).
190 ```sql
191 CREATE TABLE local_media (
192 full_path TEXT PRIMARY KEY,
193 rel_path TEXT NOT NULL,
194 filename TEXT NOT NULL,
195 clean_title TEXT NOT NULL,
196 year INTEGER,
197 category TEXT DEFAULT 'movie',
198 size_bytes INTEGER DEFAULT 0,
199 size_str TEXT,
200 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
201 );
202 ```
203
204 #### Table: `media_metadata`
205 Local enrichment cache mapping cleaned directory file titles to verified IMDb metadata.
206 ```sql
207 CREATE TABLE media_metadata (
208 clean_title TEXT,
209 year INTEGER,
210 category TEXT DEFAULT 'movie',
211 imdb_id TEXT,
212 rating TEXT,
213 genres TEXT,
214 poster_url TEXT,
215 description TEXT,
216 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
217 PRIMARY KEY (clean_title, category)
218 );
219 ```
220
221 #### Table: `stream_saves`
222 Manages live TorrServer stream sessions, chunk buffers, and automated retention policies.
223 ```sql
224 CREATE TABLE stream_saves (
225 info_hash TEXT PRIMARY KEY,
226 title TEXT NOT NULL,
227 file_path TEXT,
228 torrent_size INTEGER DEFAULT 0,
229 downloaded_bytes INTEGER DEFAULT 0,
230 status TEXT DEFAULT 'streaming',
231 last_opened_at INTEGER,
232 completed_at INTEGER,
233 retention_days INTEGER DEFAULT 5
234 );
235 ```
236
237 #### Table: `active_downloads`
238 Queue manager for background bit-for-bit file downloads to disk.
239 ```sql
240 CREATE TABLE active_downloads (
241 info_hash TEXT PRIMARY KEY,
242 title TEXT NOT NULL,
243 target TEXT DEFAULT "hdd",
244 dest_dir TEXT NOT NULL,
245 status TEXT DEFAULT "downloading",
246 started_at INTEGER,
247 updated_at INTEGER
248 );
249 ```
250
251 ---
252
253 ### 3.2 Database: `/var/neural_memory/data/graph_memory.db`
254
255 #### Table: `graph_nodes`
256 ```sql
257 CREATE TABLE graph_nodes (
258 id TEXT PRIMARY KEY,
259 label TEXT NOT NULL,
260 entity_type TEXT NOT NULL,
261 content TEXT NOT NULL,
262 embedding BLOB,
263 importance_score REAL DEFAULT 0.50,
264 access_count INTEGER DEFAULT 1,
265 created_at TEXT NOT NULL,
266 last_accessed_at TEXT NOT NULL
267 );
268 ```
269
270 #### Table: `graph_edges`
271 ```sql
272 CREATE TABLE graph_edges (
273 source_id TEXT NOT NULL,
274 target_id TEXT NOT NULL,
275 relation TEXT NOT NULL,
276 weight REAL DEFAULT 0.80,
277 confidence REAL DEFAULT 1.00,
278 last_reinforced_at TEXT NOT NULL,
279 decay_rate REAL DEFAULT 0.005,
280 PRIMARY KEY (source_id, target_id, relation),
281 FOREIGN KEY (source_id) REFERENCES graph_nodes(id) ON DELETE CASCADE,
282 FOREIGN KEY (target_id) REFERENCES graph_nodes(id) ON DELETE CASCADE
283 );
284 ```
285
286 ---
287
288 ### 3.3 Database: `/var/neural_memory/data/memory.db`
289
290 #### Tables: `identity`, `facts`, `episodes`, `skills`
291 ```sql
292 CREATE TABLE identity (key TEXT PRIMARY KEY, value TEXT);
293 CREATE TABLE facts (id INTEGER PRIMARY KEY AUTOINCREMENT, category TEXT, subject TEXT, content TEXT, timestamp TEXT);
294 CREATE TABLE episodes (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, context TEXT, actions TEXT, outcome TEXT, timestamp TEXT);
295 CREATE TABLE skills (name TEXT PRIMARY KEY, trigger TEXT, command_template TEXT, description TEXT);
296 ```
297
298 ---
299
300 ## 4. Query Flow Performance
301
302 1. **Local Media Lookup**:
303 `SELECT * FROM local_media WHERE clean_title LIKE ?` (Execution time: **~0.8 ms**).
304 2. **Instant SQLite Magnet Cache**:
305 `SELECT * FROM magnets WHERE imdb_id = ? ORDER BY seeders DESC` (Execution time: **~1.2 ms**).
306 3. **512-dim INT8 Vector Recommendation**:
307 Loads 21,088 vectors into a contiguous NumPy `float32` matrix; single unit dot product against target query vector (Execution time: **~11.5 ms** on Dell CPU).
308