Spaces:
Sleeping
Sleeping
File size: 2,584 Bytes
8af1084 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | -- ReelShield Database Schema (SQLite)
-- Migration: 001_initial_schema.sql
-- ─── MOVIES TABLE ────────────────────────────────────────────
-- Caches TMDB metadata to avoid repeated API calls.
-- metadata_json stores the full bundle (genres, keywords, poster, etc.)
-- so we never need to re-fetch from TMDB for the same film.
CREATE TABLE IF NOT EXISTS movies (
tmdb_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
year TEXT,
imdb_id TEXT,
runtime_min INTEGER,
metadata_json TEXT NOT NULL, -- JSON blob: full movie bundle
last_updated TEXT NOT NULL -- ISO 8601 datetime
);
-- Index on title for future text search support
CREATE INDEX IF NOT EXISTS idx_movies_title ON movies(title);
-- Index on last_updated for cache invalidation queries
CREATE INDEX IF NOT EXISTS idx_movies_updated ON movies(last_updated);
-- ─── CONTENT WARNINGS TABLE ──────────────────────────────────
-- Caches Gemini-generated warnings keyed to tmdb_id.
-- 1:1 with movies — each film has at most one cached warning set.
-- warnings_json structure:
-- {
-- "disclaimer": "Gemini knowledge-based",
-- "spoiler_free": {
-- "violence_gore": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "self_harm_suicide": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "miscarriage_pregnancy_loss": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "sexual_content_nudity": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "animal_abuse": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "substances": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "language": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "horror_intensity": {"severity": 0-3, "confidence": 0-1, "notes": ""},
-- "flashing_lights": {"severity": 0-3, "confidence": 0-1, "notes": ""}
-- }
-- }
CREATE TABLE IF NOT EXISTS content_warnings (
tmdb_id INTEGER PRIMARY KEY,
warnings_json TEXT NOT NULL, -- JSON blob: all 9 warning categories
last_updated TEXT NOT NULL, -- ISO 8601 datetime
FOREIGN KEY (tmdb_id) REFERENCES movies(tmdb_id) ON DELETE CASCADE
);
-- Index on last_updated for identifying stale warnings
CREATE INDEX IF NOT EXISTS idx_warnings_updated ON content_warnings(last_updated);
|