← back to AsSeenInMovies
data/schema.sql
155 lines
-- AsSeenInMovies — DB schema (lives in dw_unified alongside thesetdecorator data
-- so cross-references between set-decorators, films, and SPOTTED products
-- stay cheap. Tables are namespaced asim_* to keep them clearly separable.)
--
-- IMPORTANT: only PUBLIC data goes in here. Steve's standing rule:
-- - facts (titles, dates, names, runtimes, credits) — public domain, ingest freely
-- - poster/headshot IMAGES — never copied from IMDb. Sources we accept:
-- 'made-with-ai' (our generated placeholder)
-- 'wikidata-commons' (Wikimedia, CC-BY-SA or CC0)
-- 'tmdb-attribution' (TMDB, with required "powered by TMDB" attribution)
-- 'production-still-pd' (truly public-domain, pre-1929)
-- 'set-decorator-claim' (uploaded by claimed decorator per their license)
-- 'studio-press-kit' (when explicitly press-kit / EPK)
CREATE TABLE IF NOT EXISTS asim_movies (
id BIGSERIAL PRIMARY KEY,
tmdb_id BIGINT UNIQUE,
imdb_id TEXT,
wikidata_id TEXT,
title TEXT NOT NULL,
original_title TEXT,
release_year INT,
release_date DATE,
overview TEXT,
tagline TEXT,
runtime_min INT,
genres TEXT[],
original_language CHAR(2),
homepage TEXT,
status TEXT, -- 'Released', 'Post Production', 'Rumored', etc.
budget BIGINT,
revenue BIGINT,
vote_average REAL,
vote_count INT,
popularity REAL,
collection_id BIGINT,
collection_name TEXT,
poster_source TEXT DEFAULT 'made-with-ai',
poster_url TEXT,
backdrop_source TEXT DEFAULT 'made-with-ai',
backdrop_url TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_asim_movies_tmdb ON asim_movies(tmdb_id);
CREATE INDEX IF NOT EXISTS idx_asim_movies_imdb ON asim_movies(imdb_id);
CREATE INDEX IF NOT EXISTS idx_asim_movies_year ON asim_movies(release_year);
CREATE INDEX IF NOT EXISTS idx_asim_movies_title ON asim_movies USING gin (to_tsvector('english', title));
CREATE TABLE IF NOT EXISTS asim_people (
id BIGSERIAL PRIMARY KEY,
tmdb_id BIGINT UNIQUE,
imdb_id TEXT UNIQUE,
wikidata_id TEXT,
sag_aftra_id TEXT, -- if we can confirm via SAG directory
dga_id TEXT, -- if we can confirm via DGA directory
full_name TEXT NOT NULL,
also_known_as TEXT[],
birth_year INT,
birth_date DATE,
death_year INT,
death_date DATE,
birth_place TEXT,
primary_profession TEXT[],
union_memberships TEXT[], -- 'sag-aftra' | 'dga' | 'wga' | 'pga' | 'iatse-44' | 'iatse-600' | etc.
agent_name TEXT,
agent_company TEXT,
publicist_name TEXT,
bio TEXT,
headshot_source TEXT DEFAULT 'made-with-ai',
headshot_url TEXT,
-- Premium upgrade tier (paid claim by the actor / actress / crew member):
claimed_by_email TEXT,
claimed_at TIMESTAMPTZ,
claim_verification TEXT, -- 'manual' | 'stripe-customer-portal' | 'union-letterhead' | etc.
premium_tier TEXT, -- 'basic' | 'verified' | 'flagship'
premium_expires_at TIMESTAMPTZ,
premium_extras JSONB, -- {reels:[], current_projects:[], contact_form_enabled, custom_bio, etc.}
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_asim_people_tmdb ON asim_people(tmdb_id);
CREATE INDEX IF NOT EXISTS idx_asim_people_imdb ON asim_people(imdb_id);
CREATE INDEX IF NOT EXISTS idx_asim_people_name ON asim_people USING gin (to_tsvector('english', full_name));
CREATE INDEX IF NOT EXISTS idx_asim_people_prof ON asim_people USING gin (primary_profession);
CREATE INDEX IF NOT EXISTS idx_asim_people_union ON asim_people USING gin (union_memberships);
CREATE TABLE IF NOT EXISTS asim_credits (
id BIGSERIAL PRIMARY KEY,
person_id BIGINT NOT NULL REFERENCES asim_people(id) ON DELETE CASCADE,
movie_id BIGINT NOT NULL REFERENCES asim_movies(id) ON DELETE CASCADE,
role TEXT NOT NULL, -- canonical role bucket: actor/director/writer/producer/cinematographer/editor/composer/production_designer/set_decorator/costume_designer/sound_designer/vfx_supervisor/etc.
character_name TEXT,
order_idx INT,
job_title TEXT, -- specific job title from source (e.g., 'Lead Set Decorator')
department TEXT, -- 'Acting' | 'Directing' | 'Writing' | 'Camera' | 'Editing' | 'Sound' | 'Art' | 'Costume & Make-Up' | etc.
episode_count INT, -- for TV
source TEXT NOT NULL, -- 'tmdb' | 'wikidata' | 'iatse-44-roster' | 'sag-aftra-public' | 'thesetdecorator-roster' | etc.
source_record_id TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (person_id, movie_id, role, character_name, job_title)
);
CREATE INDEX IF NOT EXISTS idx_asim_credits_person ON asim_credits(person_id);
CREATE INDEX IF NOT EXISTS idx_asim_credits_movie ON asim_credits(movie_id);
CREATE INDEX IF NOT EXISTS idx_asim_credits_role ON asim_credits(role);
-- The SPOTTED feature — products visible in scenes. Ties into
-- thesetdecorator catalog, dw_unified.shopify_products, and any other
-- retailer the production / decorator wants to surface.
CREATE TABLE IF NOT EXISTS asim_spotted (
id BIGSERIAL PRIMARY KEY,
movie_id BIGINT NOT NULL REFERENCES asim_movies(id) ON DELETE CASCADE,
product_name TEXT NOT NULL,
product_category TEXT, -- 'wallpaper' | 'lamp' | 'sofa' | 'wardrobe' | 'prop' | 'wallcovering' | 'art' | 'tableware' | 'rug' | 'lighting' | 'plant' | etc.
brand TEXT,
vendor TEXT,
scene_description TEXT,
scene_timecode TEXT, -- 'HH:MM:SS' approximate
-- Outbound retailer links:
shop_url TEXT, -- the canonical shop link
thesetdecorator_id TEXT, -- cross-ref into thesetdecorator catalog
dw_sku TEXT, -- cross-ref into dw_unified (designer wallcoverings + sister brands)
affiliate_partner TEXT, -- 'thesetdecorator' | 'amazon-associates' | 'designer-wallcoverings' | etc.
-- Image policy:
image_source TEXT DEFAULT 'made-with-ai',
image_url TEXT,
-- Provenance:
submitted_by_email TEXT,
submitted_by_role TEXT, -- 'set_decorator' | 'fan' | 'production_stylist' | 'editor'
verified_by TEXT, -- internal staff or claimed decorator who confirmed
verified_at TIMESTAMPTZ,
votes_up INT DEFAULT 0,
votes_down INT DEFAULT 0,
is_premium_pick BOOLEAN DEFAULT false, -- featured in landing carousels (premium upgrade)
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_asim_spotted_movie ON asim_spotted(movie_id);
CREATE INDEX IF NOT EXISTS idx_asim_spotted_category ON asim_spotted(product_category);
CREATE INDEX IF NOT EXISTS idx_asim_spotted_verified ON asim_spotted(verified_at);
CREATE INDEX IF NOT EXISTS idx_asim_spotted_dw_sku ON asim_spotted(dw_sku);
-- Cross-reference to existing thesetdecorator data. decorator_roster_jsonl is
-- already 61k rows; this view lets us join without duplicating storage.
-- Loader populates asim_people from the roster; this table holds the link.
CREATE TABLE IF NOT EXISTS asim_people_sources (
id BIGSERIAL PRIMARY KEY,
person_id BIGINT NOT NULL REFERENCES asim_people(id) ON DELETE CASCADE,
source TEXT NOT NULL, -- 'thesetdecorator-roster' | 'sag-aftra-public' | 'iatse-44-roster' | etc.
source_record_id TEXT NOT NULL, -- the foreign key in the source system (e.g. 'nm0002325' for IMDb)
source_url TEXT,
ingested_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (source, source_record_id)
);