← back to Nineoh Guide
add rights-first Postgres schema + original legal pages (disclaimer/DMCA/privacy)
ade979f8ec315aa3e1947e6732b6fe1122718960 · 2026-07-25 09:46:57 -0700 · Steve
Files touched
A db/schema.sqlA docs/legal/disclaimer.mdA docs/legal/dmca.mdA docs/legal/privacy.md
Diff
commit ade979f8ec315aa3e1947e6732b6fe1122718960
Author: Steve <steve@designerwallcoverings.com>
Date: Sat Jul 25 09:46:57 2026 -0700
add rights-first Postgres schema + original legal pages (disclaimer/DMCA/privacy)
---
db/schema.sql | 148 +++++++++++++++++++++++++++++++++++++++++++++++
docs/legal/disclaimer.md | 18 ++++++
docs/legal/dmca.md | 29 ++++++++++
docs/legal/privacy.md | 34 +++++++++++
4 files changed, 229 insertions(+)
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..e9bd306
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,148 @@
+-- Unofficial 90210 Guide — canonical Postgres schema
+-- Architecture-agnostic: consumed by the shared backend regardless of repo layout.
+-- Rights-first design: the assets table stores LICENSE metadata, not just files,
+-- so no image renders without a recorded, cleared right to use it.
+
+create extension if not exists "uuid-ossp";
+
+-- ── Core show / season / episode ───────────────────────────────────────────
+create table if not exists shows (
+ id uuid primary key default uuid_generate_v4(),
+ canonical_title text not null,
+ display_title text not null,
+ franchise_title text,
+ description_original text not null, -- ORIGINAL, never a copied synopsis
+ official_source_url text,
+ disclaimer_text text not null, -- app-level "unofficial" disclaimer
+ created_at timestamptz not null default now()
+);
+
+create table if not exists seasons (
+ id uuid primary key default uuid_generate_v4(),
+ show_id uuid not null references shows(id) on delete cascade,
+ season_number int not null,
+ title text,
+ episode_count int,
+ source_last_verified_at timestamptz,
+ unique (show_id, season_number)
+);
+
+create table if not exists episodes (
+ id uuid primary key default uuid_generate_v4(),
+ show_id uuid not null references shows(id) on delete cascade,
+ season_id uuid references seasons(id) on delete set null,
+ season_number int not null,
+ episode_number int not null,
+ production_code text,
+ title text not null,
+ air_date date,
+ writer text,
+ director text,
+ summary_short_original text, -- spoiler-safe, ORIGINAL
+ summary_full_original text, -- expanded, ORIGINAL
+ spoiler_rating int default 0,
+ source_url text,
+ source_type text,
+ external_ids jsonb default '{}', -- tvmaze/wikidata ids etc.
+ last_verified_at timestamptz,
+ created_at timestamptz not null default now(),
+ unique (show_id, season_number, episode_number)
+);
+
+-- ── Cast / characters / credits ────────────────────────────────────────────
+create table if not exists cast_people (
+ id uuid primary key default uuid_generate_v4(),
+ name text not null,
+ biography_original text, -- ORIGINAL bio
+ official_site_url text,
+ verified_social_links jsonb default '[]',
+ headshot_asset_id uuid, -- FK added after assets exists
+ publicity_risk_level text default 'editorial-only',
+ last_verified_at timestamptz,
+ created_at timestamptz not null default now()
+);
+
+create table if not exists characters (
+ id uuid primary key default uuid_generate_v4(),
+ name text not null,
+ description_original text,
+ show_id uuid references shows(id) on delete cascade
+);
+
+create table if not exists credits (
+ id uuid primary key default uuid_generate_v4(),
+ episode_id uuid references episodes(id) on delete cascade,
+ person_id uuid references cast_people(id) on delete cascade,
+ character_id uuid references characters(id) on delete set null,
+ credit_type text,
+ billing_order int
+);
+
+-- ── News snippets (short, attributed, linked out) ──────────────────────────
+create table if not exists news_items (
+ id uuid primary key default uuid_generate_v4(),
+ headline text not null,
+ summary_original text not null, -- one-sentence ORIGINAL summary
+ canonical_url text not null,
+ publisher_name text,
+ published_at timestamptz,
+ topic_tags text[] default '{}',
+ person_ids uuid[] default '{}',
+ show_ids uuid[] default '{}',
+ snippet_status text default 'pending',
+ editor_reviewed_at timestamptz,
+ created_at timestamptz not null default now()
+);
+
+-- ── Assets = the RIGHTS LEDGER (the legally critical table) ─────────────────
+create table if not exists assets (
+ id uuid primary key default uuid_generate_v4(),
+ type text not null, -- image | thumbnail | ...
+ file_url text, -- null while PENDING a cleared license
+ thumbnail_url text,
+ license_type text not null, -- public-domain | cc-by | cc-by-sa | licensed | PENDING
+ license_url text,
+ attribution_text text,
+ usage_scope text, -- editorial | marketing | icon ...
+ source_url text,
+ rights_notes text,
+ approved_for_marketing boolean default false, -- publicity-rights guard
+ license_purchase_status text default 'none', -- none | drafted-for-approval | purchased
+ created_at timestamptz not null default now()
+);
+
+-- headshot FK now that assets exists
+do $$ begin
+ alter table cast_people
+ add constraint cast_people_headshot_fk
+ foreign key (headshot_asset_id) references assets(id) on delete set null;
+exception when duplicate_object then null; end $$;
+
+-- ── UGC + DMCA (moderation-first, per Apple/Play UGC rules) ─────────────────
+create table if not exists ugc_posts (
+ id uuid primary key default uuid_generate_v4(),
+ user_id uuid,
+ entity_type text, -- episode | cast | news
+ entity_id uuid,
+ content text,
+ spoiler_flag boolean default false,
+ status text default 'pending', -- pending | published | removed
+ report_count int default 0,
+ created_at timestamptz not null default now()
+);
+
+create table if not exists dmca_cases (
+ id uuid primary key default uuid_generate_v4(),
+ complainant text,
+ material_url text,
+ notice_received_at timestamptz,
+ status text default 'open',
+ resolution text,
+ counter_notice_deadline timestamptz
+);
+
+-- Fast lookups
+create index if not exists idx_episodes_show_season on episodes(show_id, season_number, episode_number);
+create index if not exists idx_news_published on news_items(published_at desc);
+create index if not exists idx_assets_license on assets(license_type);
+create index if not exists idx_assets_pending on assets(license_purchase_status) where license_purchase_status <> 'purchased';
diff --git a/docs/legal/disclaimer.md b/docs/legal/disclaimer.md
new file mode 100644
index 0000000..f96aad7
--- /dev/null
+++ b/docs/legal/disclaimer.md
@@ -0,0 +1,18 @@
+# Disclaimer
+
+**This is an unofficial fan-made guide.** The Unofficial 90210 Guide is an
+independent, fan-created reference and is **not** affiliated with, authorized,
+endorsed, or sponsored by the producers, network, distributors, cast, or any
+rights-holders of the television series it discusses. All series titles,
+character names, and trademarks referenced belong to their respective owners
+and are used here for **identification and editorial commentary only**, on a
+nominative/referential basis.
+
+All episode summaries, cast biographies, and news write-ups in this app are
+**original works** written by our editorial team. We do not reproduce official
+synopses, promotional artwork, logos, or press-kit photography. Images shown are
+either in the public domain, offered under a Creative Commons license, or used
+under a separate license we have obtained; each carries its own attribution.
+
+If you believe any material infringes your rights, please see our
+[Copyright / DMCA Policy](./dmca.md).
diff --git a/docs/legal/dmca.md b/docs/legal/dmca.md
new file mode 100644
index 0000000..516230f
--- /dev/null
+++ b/docs/legal/dmca.md
@@ -0,0 +1,29 @@
+# Copyright / DMCA Policy
+
+We respect intellectual property rights and respond to notices of alleged
+infringement under the Digital Millennium Copyright Act (17 U.S.C. § 512).
+
+## Designated agent
+Send notices to our designated DMCA contact: **dmca@[APP-DOMAIN]** *(to be set
+at launch)*.
+
+## What a valid notice must include
+1. Identification of the copyrighted work claimed to be infringed.
+2. Identification of the allegedly infringing material and its location (URL / screen).
+3. Your contact information (name, address, email, phone).
+4. A statement that you have a good-faith belief the use is not authorized.
+5. A statement, under penalty of perjury, that the information is accurate and
+ that you are authorized to act on behalf of the rights-holder.
+6. Your physical or electronic signature.
+
+## Our process
+1. We triage notices promptly and may remove or disable the disputed material
+ while we investigate.
+2. We notify the uploader (for user-submitted content) and provide a
+ counter-notice path.
+3. We reinstate material only if the statutory counter-notice process is
+ satisfied and residual legal risk is acceptable.
+
+## Repeat-infringer policy
+We maintain and enforce a policy of terminating, in appropriate circumstances,
+the accounts of users who are repeat infringers.
diff --git a/docs/legal/privacy.md b/docs/legal/privacy.md
new file mode 100644
index 0000000..7a97495
--- /dev/null
+++ b/docs/legal/privacy.md
@@ -0,0 +1,34 @@
+# Privacy Policy
+
+This policy describes what the Unofficial 90210 Guide collects and how it is used.
+It is written to support accurate Apple App Store privacy "nutrition" labels and
+Google Play Data Safety disclosures, including the practices of any integrated
+third-party SDKs.
+
+## What we collect
+- **Account data** (if you create an account): email, display name.
+- **User content**: reviews, comments, lists you choose to post.
+- **Usage & diagnostics**: app interactions and crash logs, to improve the app.
+- **Advertising / analytics identifiers**: only if/when ads or analytics are
+ enabled, and only per your consent choices (see below).
+
+## Tracking & consent
+- On the **web**, we use a consent banner with Google Consent Mode v2: a default
+ (denied) state is set before any measurement fires and updated only after your choice.
+- In the **mobile app**, we present the platform consent flow (Google UMP) and,
+ on iOS, the App Tracking Transparency prompt before any cross-app tracking.
+- You can change your choices at any time from Settings → Privacy.
+
+## Third parties
+Any analytics, advertising, crash-reporting, or push providers we integrate are
+listed here with links to their policies before launch. We do not sell personal
+information.
+
+## Your rights
+You may request access to, correction of, or deletion of your data by contacting
+**privacy@[APP-DOMAIN]** *(to be set at launch)*. We honor applicable rights under
+CCPA/CPRA and comparable laws.
+
+## Children
+This app is not directed to children under 13, and we do not knowingly collect
+their personal information.
← ac84507 initial scaffold: build brief + gitignore for Unofficial 902
·
back to Nineoh Guide
·
iteration 1: Turborepo scaffold + working Next.js web MVP (S 3c61104 →