Rendering markdown & diagrams...
1 # NeuralOS Proposed Data Model Specification (3NF Normalized)
2
3 **Document Status**: Proposed for User Audit
4 **Target Database**: `/var/neural_memory/data/magnets.db` on `dell-ss`
5 **Engine**: SQLite 3 with `PRAGMA foreign_keys = ON;`
6 **Author**: Antigravity Assistant & System Architect
7
8 ---
9
10 ## 1. Executive Summary & Design Objectives
11
12 This proposed data model resolves data redundancies across NeuralOS while introducing enterprise-grade streaming features requested for the cinema system:
13
14 1. **Strict 3NF Normalization (Zero Column Duplication)**:
15 * Attributes like `title`, `year`, `rating`, `poster_url`, `language`, and `genres` reside **exclusively** in `media_catalog`.
16 * `magnets`, `local_media`, `stream_saves`, and `active_downloads` link directly via `imdb_id` or `info_hash` Foreign Keys with `ON DELETE CASCADE`.
17 2. **Native TV Series Seasons & Episodes Hierarchy**:
18 * Introduces a dedicated `series_episodes` table mapping each season and episode number, episode title, synopsis, duration, and thumbnail.
19 * Solves the torrent season pack problem: a single magnet link for an entire season (e.g. *Breaking Bad Season 1 [1080p]*) cleanly maps to individual episodes via TorrServer's internal `file_index`.
20 3. **User Playback State & Resume Tracking**:
21 * Introduces `user_playback_state` in SQLite synchronized with browser `localStorage`.
22 * Remembers the exact last played episode, timestamp, playback position (seconds), duration, and completion status for 0ms client-side resume.
23 4. **Sequential Streaming & Sequential Disk Downloads**:
24 * Explicitly supports dual modes: **Sequential In-Memory TorrServer Streaming** (instant 0ms browser playback) and **Sequential Disk Ingestion** (downloading pieces sequentially to `/mnt/hdd1` 1TB HDD or `/mnt/drive_d` 77GB SSD).
25 5. **BestSimilar Semantic Tags for Embeddings**:
26 * Adds `style_tags` (tone, mood, narrative style) and `plot_keywords` (tropes, themes) to `media_catalog` for generating higher-fidelity 512-dim INT8 embeddings.
27
28 ---
29
30 ## 2. Entity-Relationship Diagram (ERD)
31
32 ```mermaid
33 erDiagram
34 MEDIA_CATALOG ||--o{ SERIES_EPISODES : "1:N contains episodes"
35 MEDIA_CATALOG ||--o{ MAGNETS : "1:N has swarm streams"
36 MEDIA_CATALOG ||--o{ LOCAL_MEDIA : "1:N has offline disk files"
37 MEDIA_CATALOG ||--o{ USER_PLAYBACK_STATE : "1:N tracks watch history"
38
39 SERIES_EPISODES ||--o{ EPISODE_STREAMS : "1:N maps to stream piece"
40 MAGNETS ||--o{ EPISODE_STREAMS : "1:N provides torrent files"
41 MAGNETS ||--o| STREAM_SAVES : "1:1 TorrServer active buffer"
42 MAGNETS ||--o| ACTIVE_DOWNLOADS : "1:1 disk download queue"
43
44 MEDIA_CATALOG {
45 string imdb_id PK "Unique IMDb ID (e.g. tt0903747)"
46 string title "Canonical Title"
47 int year "Release Year"
48 float rating "IMDb Score (e.g. 9.5)"
49 int votes "Total IMDb Vote Count"
50 string category "'movie' or 'series'"
51 string language "'Hindi', 'English'"
52 string genres "Comma-delimited genres"
53 string poster_url "High-res IMDb CDN Poster"
54 string plot_summary "Detailed synopsis"
55 string style_tags "BestSimilar style attributes"
56 string plot_keywords "BestSimilar plot tags"
57 blob embedding "512-dim INT8 vector (512 bytes)"
58 timestamp created_at "First indexed timestamp"
59 timestamp updated_at "Last enriched timestamp"
60 }
61
62 SERIES_EPISODES {
63 int id PK "Auto-increment ID"
64 string imdb_id FK "References media_catalog(imdb_id)"
65 int season "Season number (1, 2, ...)"
66 int episode "Episode number (1, 2, ...)"
67 string title "Episode title (e.g. 'Ozymandias')"
68 string overview "Episode synopsis"
69 string air_date "Original broadcast date"
70 string thumbnail_url "Episode preview still URL"
71 int duration_mins "Runtime in minutes"
72 }
73
74 MAGNETS {
75 string info_hash PK "40-char Hex BitTorrent Hash"
76 string imdb_id FK "References media_catalog(imdb_id)"
77 string release_title "Exact release name with codec info"
78 string quality "'2160p', '1080p', '720p', 'HDRip'"
79 int season "Season number if season pack, else NULL"
80 int episode "Episode number if single ep, else NULL"
81 int size_bytes "Total torrent size in bytes"
82 string size_str "Formatted size (e.g. '18.4 GB')"
83 int seeders "Active swarm seeders count"
84 string magnet_uri "Full magnet URI with trackers"
85 string source "'torrentio', 'yts', 'dht'"
86 string audio_language "'Hindi', 'English', 'Dual Audio'"
87 timestamp created_at "Ingestion timestamp"
88 }
89
90 EPISODE_STREAMS {
91 int id PK "Auto-increment ID"
92 int episode_id FK "References series_episodes(id)"
93 string info_hash FK "References magnets(info_hash)"
94 int file_index "Internal TorrServer torrent file index"
95 string file_name "Internal filename (e.g. 'S01E01.mkv')"
96 int file_size "Individual episode byte size"
97 string quality "'1080p', '720p', '4k'"
98 }
99
100 USER_PLAYBACK_STATE {
101 int id PK "Auto-increment ID"
102 string imdb_id FK "References media_catalog(imdb_id)"
103 int season "Season number (NULL for movies)"
104 int episode "Episode number (NULL for movies)"
105 string info_hash FK "References magnets(info_hash)"
106 int file_index "TorrServer file index"
107 float position_seconds "Current playback timestamp"
108 float duration_seconds "Total media duration"
109 int is_completed "1 if watched >= 90%, else 0"
110 timestamp last_watched_at "Last played epoch"
111 }
112
113 LOCAL_MEDIA {
114 string full_path PK "Path on /mnt/hdd1 or /mnt/drive_d"
115 string imdb_id FK "References media_catalog(imdb_id)"
116 string rel_path "Web streaming relative path"
117 string filename "Exact filename on disk"
118 int season "Season number if series, else NULL"
119 int episode "Episode number if series, else NULL"
120 int size_bytes "Byte size on disk"
121 string size_str "Formatted size"
122 string disk_target "'hdd' (/dev/sdb1) or 'ssd' (/dev/sdb5)"
123 timestamp updated_at "Last disk scan"
124 }
125
126 STREAM_SAVES {
127 string info_hash PK "References magnets(info_hash)"
128 string file_path "TorrServer cache chunk file"
129 int torrent_size "Total size in bytes"
130 int downloaded_bytes "Downloaded piece bytes"
131 string status "'streaming', 'completed', 'paused'"
132 int last_opened_at "Unix epoch"
133 int completed_at "Unix epoch"
134 int retention_days "Default 5 days"
135 }
136
137 ACTIVE_DOWNLOADS {
138 string info_hash PK "References magnets(info_hash)"
139 string target "'hdd' or 'ssd'"
140 string dest_dir "Destination directory"
141 string status "'downloading', 'seeding', 'done'"
142 int started_at "Unix epoch"
143 int updated_at "Unix epoch"
144 }
145 ```
146
147 ---
148
149 ## 3. Complete DDL Schema (SQL)
150
151 ```sql
152 -- Enable strict relational integrity
153 PRAGMA foreign_keys = ON;
154
155 -- ====================================================================
156 -- 1. MEDIA CATALOG (Single Source of Truth for Metadata & Vectors)
157 -- ====================================================================
158 CREATE TABLE IF NOT EXISTS media_catalog (
159 imdb_id TEXT PRIMARY KEY,
160 title TEXT NOT NULL,
161 year INTEGER,
162 rating REAL,
163 votes INTEGER,
164 category TEXT DEFAULT 'movie' CHECK(category IN ('movie', 'series')),
165 language TEXT DEFAULT 'English',
166 genres TEXT,
167 poster_url TEXT,
168 plot_summary TEXT,
169 style_tags TEXT DEFAULT '',
170 plot_keywords TEXT DEFAULT '',
171 embedding BLOB,
172 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
173 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
174 );
175
176 CREATE INDEX IF NOT EXISTS idx_catalog_rating ON media_catalog(rating DESC, votes DESC);
177 CREATE INDEX IF NOT EXISTS idx_catalog_year ON media_catalog(year DESC);
178 CREATE INDEX IF NOT EXISTS idx_catalog_category ON media_catalog(category);
179 CREATE INDEX IF NOT EXISTS idx_catalog_lang ON media_catalog(language);
180
181 -- ====================================================================
182 -- 2. SERIES EPISODES (Native Episode Hierarchy for TV Series)
183 -- ====================================================================
184 CREATE TABLE IF NOT EXISTS series_episodes (
185 id INTEGER PRIMARY KEY AUTOINCREMENT,
186 imdb_id TEXT NOT NULL REFERENCES media_catalog(imdb_id) ON DELETE CASCADE,
187 season INTEGER NOT NULL,
188 episode INTEGER NOT NULL,
189 title TEXT NOT NULL,
190 overview TEXT,
191 air_date TEXT,
192 thumbnail_url TEXT,
193 duration_mins INTEGER,
194 UNIQUE(imdb_id, season, episode)
195 );
196
197 CREATE INDEX IF NOT EXISTS idx_episodes_lookup ON series_episodes(imdb_id, season, episode);
198
199 -- ====================================================================
200 -- 3. MAGNETS (Swarm Stream Cache - Pure Stream Attributes)
201 -- ====================================================================
202 CREATE TABLE IF NOT EXISTS magnets (
203 info_hash TEXT PRIMARY KEY,
204 imdb_id TEXT NOT NULL REFERENCES media_catalog(imdb_id) ON DELETE CASCADE,
205 release_title TEXT NOT NULL,
206 quality TEXT NOT NULL DEFAULT '1080p',
207 season INTEGER DEFAULT NULL,
208 episode INTEGER DEFAULT NULL,
209 size_bytes INTEGER DEFAULT 0,
210 size_str TEXT,
211 seeders INTEGER DEFAULT 0,
212 magnet_uri TEXT NOT NULL,
213 source TEXT DEFAULT 'torrentio',
214 audio_language TEXT DEFAULT 'English',
215 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
216 );
217
218 CREATE INDEX IF NOT EXISTS idx_magnets_imdb ON magnets(imdb_id);
219 CREATE INDEX IF NOT EXISTS idx_magnets_quality ON magnets(quality);
220 CREATE INDEX IF NOT EXISTS idx_magnets_seeders ON magnets(seeders DESC);
221 CREATE INDEX IF NOT EXISTS idx_magnets_season_ep ON magnets(imdb_id, season, episode);
222
223 -- ====================================================================
224 -- 4. EPISODE STREAMS (Torrent Pack File Index Mapping)
225 -- ====================================================================
226 CREATE TABLE IF NOT EXISTS episode_streams (
227 id INTEGER PRIMARY KEY AUTOINCREMENT,
228 episode_id INTEGER NOT NULL REFERENCES series_episodes(id) ON DELETE CASCADE,
229 info_hash TEXT NOT NULL REFERENCES magnets(info_hash) ON DELETE CASCADE,
230 file_index INTEGER NOT NULL DEFAULT 0,
231 file_name TEXT NOT NULL,
232 file_size INTEGER DEFAULT 0,
233 quality TEXT DEFAULT '1080p',
234 UNIQUE(episode_id, info_hash)
235 );
236
237 CREATE INDEX IF NOT EXISTS idx_ep_stream_hash ON episode_streams(info_hash);
238
239 -- ====================================================================
240 -- 5. USER PLAYBACK STATE & RESUME TRACKING
241 -- ====================================================================
242 CREATE TABLE IF NOT EXISTS user_playback_state (
243 id INTEGER PRIMARY KEY AUTOINCREMENT,
244 imdb_id TEXT NOT NULL REFERENCES media_catalog(imdb_id) ON DELETE CASCADE,
245 season INTEGER DEFAULT NULL,
246 episode INTEGER DEFAULT NULL,
247 info_hash TEXT REFERENCES magnets(info_hash) ON DELETE SET NULL,
248 file_index INTEGER DEFAULT 0,
249 position_seconds REAL NOT NULL DEFAULT 0.0,
250 duration_seconds REAL NOT NULL DEFAULT 0.0,
251 is_completed INTEGER DEFAULT 0 CHECK(is_completed IN (0, 1)),
252 last_watched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
253 UNIQUE(imdb_id, season, episode)
254 );
255
256 CREATE INDEX IF NOT EXISTS idx_playback_recent ON user_playback_state(last_watched_at DESC);
257
258 -- ====================================================================
259 -- 6. LOCAL MEDIA (Physical Storage Archive - 1TB HDD & 77GB SSD)
260 -- ====================================================================
261 CREATE TABLE IF NOT EXISTS local_media (
262 full_path TEXT PRIMARY KEY,
263 imdb_id TEXT REFERENCES media_catalog(imdb_id) ON DELETE SET NULL,
264 rel_path TEXT NOT NULL,
265 filename TEXT NOT NULL,
266 season INTEGER DEFAULT NULL,
267 episode INTEGER DEFAULT NULL,
268 size_bytes INTEGER DEFAULT 0,
269 size_str TEXT,
270 disk_target TEXT DEFAULT 'hdd' CHECK(disk_target IN ('hdd', 'ssd')),
271 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
272 );
273
274 CREATE INDEX IF NOT EXISTS idx_local_imdb ON local_media(imdb_id);
275
276 -- ====================================================================
277 -- 7. STREAM SAVES (TorrServer In-Memory / Buffer State)
278 -- ====================================================================
279 CREATE TABLE IF NOT EXISTS stream_saves (
280 info_hash TEXT PRIMARY KEY REFERENCES magnets(info_hash) ON DELETE CASCADE,
281 file_path TEXT,
282 torrent_size INTEGER DEFAULT 0,
283 downloaded_bytes INTEGER DEFAULT 0,
284 status TEXT DEFAULT 'streaming' CHECK(status IN ('streaming', 'completed', 'paused')),
285 last_opened_at INTEGER,
286 completed_at INTEGER,
287 retention_days INTEGER DEFAULT 5
288 );
289
290 -- ====================================================================
291 -- 8. ACTIVE DOWNLOADS (Background Full Disk Downloads Queue)
292 -- ====================================================================
293 CREATE TABLE IF NOT EXISTS active_downloads (
294 info_hash TEXT PRIMARY KEY REFERENCES magnets(info_hash) ON DELETE CASCADE,
295 target TEXT DEFAULT 'hdd' CHECK(target IN ('hdd', 'ssd')),
296 dest_dir TEXT NOT NULL,
297 status TEXT DEFAULT 'downloading' CHECK(status IN ('downloading', 'seeding', 'done', 'error')),
298 started_at INTEGER,
299 updated_at INTEGER
300 );
301
302 -- ====================================================================
303 -- BACKWARD-COMPATIBLE SQL VIEW (For seamless legacy code support)
304 -- ====================================================================
305 CREATE VIEW IF NOT EXISTS magnets_v AS
306 SELECT
307 m.info_hash,
308 m.imdb_id,
309 c.title,
310 c.year,
311 c.category,
312 c.rating,
313 c.poster_url,
314 c.genres,
315 m.release_title,
316 m.quality,
317 m.season,
318 m.episode,
319 m.size_str,
320 m.size_bytes,
321 m.seeders,
322 m.magnet_uri,
323 m.source,
324 m.audio_language AS language,
325 m.created_at
326 FROM magnets m
327 LEFT JOIN media_catalog c ON m.imdb_id = c.imdb_id;
328 ```
329
330 ---
331
332 ## 4. How the TV Series Architecture Works
333
334 ### 4.1 The Season Pack Problem Solved
335 In BitTorrent indexers, TV series are distributed in two forms:
336 1. **Whole Season Packs**: A single 30 GB torrent containing 10 files (`S01E01.mkv`, `S01E02.mkv` ... `S01E10.mkv`).
337 2. **Single Episode Releases**: An individual 2 GB torrent for `S01E01.mkv`.
338
339 **The Solution**:
340 * When a season pack magnet is indexed, TorrServer probes the torrent's piece and file tree.
341 * Each file within the pack receives an internal `file_index` (0, 1, 2, ...).
342 * In `episode_streams`, we link:
343 `episode_id` (Season 1, Episode 3) $\rightarrow$ `info_hash` (Season 1 Pack) with `file_index = 2`.
344 * When the user clicks **Play S01E03**, Artplayer requests:
345 `http://127.0.0.1:8090/stream?link={info_hash}&index=2&play=1`
346 * TorrServer streams **only the bytes for Episode 3 sequentially**, ignoring the rest of the pack!
347
348 ### 4.2 Series UI Episode Picker Progression
349 When the user clicks on a TV Series card (e.g. *Breaking Bad*):
350 1. **Modal Header**: Series Title, Year, Rating, Total Seasons badge.
351 2. **Season Selector Tabs**: `[Season 1] [Season 2] [Season 3] [Season 4] [Season 5]`.
352 3. **Episode Grid**: Cards for each episode showing:
353 * Episode number (`E01`, `E02`...) & Episode Title (`"Pilot"`).
354 * Thumbnail still image & duration (`48 min`).
355 * Synopsis overview.
356 * **Play Button** (Instant 0ms stream via mapped magnet / file index).
357 * Progress Bar (e.g. 70% watched if in progress).
358
359 ---
360
361 ## 5. Playback State & Resume Architecture
362
363 ### 5.1 Dual-Tier Caching (Browser `localStorage` + Server SQLite Sync)
364
365 ```mermaid
366 sequenceDiagram
367 autonumber
368 actor User as User Browser
369 participant LS as Browser localStorage
370 participant Art as Artplayer HUD
371 participant API as /api/media/playback_state
372 participant DB as SQLite (user_playback_state)
373
374 User->>Art: Opens Movie or Series
375 Art->>LS: Check local cache for resume timestamp
376 LS-->>Art: Return { position: 1420.5s, season: 2, episode: 4 }
377 Art-->>User: Show Prompt: "Resume S02E04 at 23:40? [Resume] [Start Over]"
378
379 loop Every 10 Seconds of Playback
380 Art->>LS: Update local position
381 Art->>API: POST /api/media/playback_state (position, duration)
382 API->>DB: UPSERT user_playback_state
383 end
384
385 User->>Art: Reaches End of Video (>= 90%)
386 Art->>API: POST /api/media/playback_state (is_completed = 1)
387 API->>DB: Mark Episode Completed
388 Art-->>User: Auto-prompt "Next: Play S02E05 [Play Now]"
389 ```
390
391 * **Client Cache Key**: `neuralos_watch_{imdb_id}`:
392 ```json
393 {
394 "imdb_id": "tt0903747",
395 "season": 2,
396 "episode": 4,
397 "position": 1420.5,
398 "duration": 2850.0,
399 "updated_at": 1731372000
400 }
401 ```
402 * **Offline Resiliency**: If the network connection momentarily drops, `localStorage` keeps tracking position locally and syncs to SQLite on reconnect.
403
404 ---
405
406 ## 6. Sequential Streaming vs. Sequential Disk Download
407
408 ### Can Any Magnet Link Be Played?
409 **Yes.**
410 1. **Sequential Streaming (TorrServer)**:
411 * Standard BitTorrent clients download pieces randomly across the swarm.
412 * TorrServer forces **strictly sequential piece prioritization** (Piece 0, 1, 2, 3 ...).
413 * The moment the first 20–30 MB are buffered in RAM (usually within 3–8 seconds), TorrServer serves a standard HTTP 206 Partial Content video stream directly to Artplayer.
414 * **Zero Disk Wear**: The video buffers in memory; nothing is permanently written to disk unless requested.
415 2. **Sequential Background Disk Download**:
416 * If a user clicks **[⬇ Download to 1TB HDD]** or **[⬇ Download to 77GB SSD]**, TorrServer continues writing the sequential stream to `/mnt/hdd1/Media/Downloads/` or `/mnt/drive_d/Media/Downloads/`.
417 * Even for swarms with modest seeder counts (3–5 seeders), sequential download ensures the file is playable from the local HDD as soon as it finishes.
418
419 ---
420
421 ## 7. Migration Plan (Zero Data Loss)
422
423 ```sql
424 -- 1. Create new normalized tables
425 -- (Run DDL from Section 3 above)
426
427 -- 2. Migrate existing media_catalog
428 -- (Already in 3NF format; add style_tags and plot_keywords columns)
429 ALTER TABLE media_catalog ADD COLUMN style_tags TEXT DEFAULT '';
430 ALTER TABLE media_catalog ADD COLUMN plot_keywords TEXT DEFAULT '';
431
432 -- 3. Migrate existing magnets table into normalized format
433 INSERT OR IGNORE INTO magnets_new (
434 info_hash, imdb_id, release_title, quality, season, episode,
435 size_str, seeders, magnet_uri, source, audio_language, created_at
436 )
437 SELECT
438 info_hash,
439 COALESCE(imdb_id, 'UNKNOWN'),
440 title,
441 quality,
442 NULL,
443 NULL,
444 size_str,
445 seeders,
446 magnet_uri,
447 source,
448 COALESCE(language, 'English'),
449 created_at
450 FROM magnets;
451
452 -- 4. Swap tables atomically
453 DROP TABLE magnets;
454 ALTER TABLE magnets_new RENAME TO magnets;
455
456 -- 5. Re-create backward compatibility view
457 -- (magnets_v view ensures 100% compatibility with existing web_status.py queries)
458 ```
459
460 ---
461
462 ## 8. Audit Checklist & Recommendations
463
464 * [x] **Zero Duplicate Columns**: No titles, years, ratings, or posters duplicated in `magnets` or `local_media`.
465 * [x] **Series Season/Episode Support**: Explicit `series_episodes` and `episode_streams` tables.
466 * [x] **Playback State & Resume**: `user_playback_state` with client `localStorage` caching.
467 * [x] **BestSimilar Embedding Enrichment**: `style_tags` and `plot_keywords` columns added to `media_catalog`.
468 * [x] **Zero Downtime Migration**: Backward-compatible SQL view (`magnets_v`) guarantees current dashboard queries continue running without interruption.
469