---
title: "DB schema notes"
url: https://memory.wiki/HsxL91R7
updated: 2026-05-16T14:17:17.622Z
hub: https://memory.wiki/hub/demo
bundle_count: 1
concept_count: 12
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.


---

## Concepts in this document
- **Supabase** _(entity)_
  Backend-as-a-service providing authentication, database, and row-level security without separate auth overhead.
- **Vendor consolidation** _(concept)_
  The operational principle of reducing authentication surfaces, SDKs, and control planes by keeping vector search within the existing Postgres infrastructure.
- **pgvector** _(entity)_
  PostgreSQL extension providing vector data type and HNSW indexing for efficient similarity search.
- **Authentication** _(tag)_
  Core security domain covering user identity, session management, and authorization patterns.
- **Database Design** _(tag)_
  Data modeling domain including schema design, migrations, and query optimization strategies.
- **API Design** _(tag)_
  RESTful interface design covering endpoints, error handling, and client-server communication.
- **Resend** _(entity)_
  Email service provider supporting both transactional and marketing communication channels.
- **Next.js App Router** _(entity)_
  Chosen routing framework leveraging RSC and Server Actions to match the project's data-heavy, read-mostly workload.
- **Vercel AI Gateway** _(entity)_
  AI provider abstraction layer offering failover and zero data retention for model access.
- **Next.js 15** _(entity)_
  Chosen frontend framework leveraging React Server Components and caching for performance.
- **Row-level security** _(concept)_
  Supabase RLS capability co-locating authentication and authorization with data, justifying single-vendor consolidation.
- **React Hook Form** _(entity)_
  Primary form handling library paired with Zod schemas for both client and server validation.

## Concept relations (within this doc's concepts)
- **Supabase** enables approach **Vendor consolidation**
- **Supabase** implements **Row-level security**
- **Supabase** hosts **pgvector**
- **Vercel AI Gateway** similar pattern **Resend**
- **Supabase** exemplifies approach **Vendor consolidation**
- **Row-level security** supports decision **Vendor consolidation**
- **Supabase** reduces complexity **Vendor consolidation**
- **Row-level security** supports **Vendor consolidation**
- **Supabase** integrates **pgvector**
- **Supabase** exemplifies strategy **Vendor consolidation**
- **Supabase** provides **Row-level security**
- **Vendor consolidation** guides **Supabase**
- **Vendor consolidation** guides **Vercel AI Gateway**

## Bundles containing this document
- [Cross-tool dev workflow](https://memory.wiki/b/p_mdtSk0)
  > Acme Pulse, a fictional landing-page builder. The seven docs in this bundle are the project's living context — README, auth pattern, DB schema, API conventions, UI patterns, decision log, open questio

_Hub canonical:_ https://memory.wiki/hub/demo
_Concept digest:_ https://memory.wiki/raw/hub/demo?digest=1&compact=1