DB schema notes

Core tables

  • users — owned by Supabase Auth
  • organizations — top-level tenant
  • organization_members — (user_id, organization_id, role)
  • pages — a landing page
  • page_versions — append-only history per page
  • page_blocks — section-level content (headline / hero / cta / form / etc.)
  • ab_tests — variant assignment + outcome

Migration naming

NNN_descriptive_name.sql where NNN is zero-padded. Don't reuse numbers. If two migrations land same day, the one merged later gets the higher number — no editing history.

001_initial_schema.sql
002_pages_and_blocks.sql
003_ab_tests.sql

Views vs functions

  • View when the result is a deterministic projection of base tables. No side effects.
  • Function (PL/pgSQL) when you need parameters or row-by-row logic.
  • Avoid materialised views for now — refresh complexity > query speed gain at our scale.

Index strategy

Default: index every column used in a WHERE clause from the API. Partial indexes for is_published = true style filters. Vector indexes (HNSW) for embedding columns; m=16, ef_construction=64 is fine to start.

Things that bit us

  • Forgot to add ON DELETE CASCADE on page_blocks → pages — orphan rows after page delete. Fixed in migration 014.
  • Indexed JSONB content column by default — slowed inserts. Removed; add per-key GIN if a specific filter needs it.

Backup / restore

Supabase nightly snapshots. We don't do point-in-time yet — review at $1k MRR.