Reference: Data Contracts

This is the schema gospel. Any time we touch Supabase, we anchor back to these contracts from supabase/migrations/0001_init.sql.

profiles

Per-user metadata + AI preferences.

  • id uuid PK → auth.users(id)
  • email text, display_name text
  • focus_tags text[] — mirrors selected focus topics for quick querying.
  • ai_settings jsonb — stores sliders, Gemini keys, etc.
  • Timestamps: created_at, updated_at (auto-updated via handle_updated_at).

sources

Every RSS feed or X handle a user follows.

  • id uuid PK, user_id uuid
  • type text enum (rss, twitter)
  • url text (RSS) / handle text (Twitter)
  • display_name text, status text, last_polled_at
  • Unique constraints on (user_id, url) and (user_id, handle) prevent duplicates.

items

News stories pulled from sources.

  • id uuid PK
  • source_id uuid
  • title text, summary text, url text
  • published_at timestamptz
  • metadata jsonb — houses tweet metrics, Gemini summaries, popularity scores.
  • focus_topics text[]
  • content_hash text — dedupe fingerprint.
  • Unique (source_id, url) keeps duplicates out.

user_item_scores

Future personalization hooks.

  • Composite PK (user_id, item_id)
  • score numeric, seen_at timestamptz, dismissed boolean

bookmarks

Saved stories with resurfacing support.

  • id uuid PK
  • user_id uuid, item_id uuid
  • bookmarked_at, surfaced_at timestamps

jobs

Ingestion audit trail.

  • id uuid PK
  • source_id uuid
  • status text, attempts int
  • scheduled_for, last_run_at
  • error text

RLS shorthand

Every table above has RLS enabled with policies scoped to auth.uid(). We only bypass RLS with the Supabase service-role key inside Edge Functions and cron handlers.