---
title: "DB schema notes"
url: https://mdfy.app/HsxL91R7
updated: 2026-05-16T14:17:17.622Z
source: "demo-seed"
---
# 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.

```text
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.