arch-atlas
SaaS Atlas·Pillar 04 / 12

Data Layer

Postgres · Drizzle ORM · Supabase · Migrations

The data layer is the only place where SaaS bets stop being reversible: the schema you ship in week 2 is the schema you'll fight in year 4. Pick Postgres (versatile, RLS-native, the entire 2026 SaaS stack assumes it), use Drizzle for type-safe queries that don't fight RLS, and bake migrations + backups into deployment from day one. Every table gets org_id, every query goes through a single tenant-aware helper, every change goes through a migration — no exceptions.

7Nodes2Vendors1Decisions12Checklist2Flows

Primary stack

The default picks this pillar assumes

  • Supabase

Alternates worth knowing

Swap in when scale, cost or constraints change

  • Neon
01
SchemaManaged (Supabase / Neon)

Postgres

The OLTP database. Managed Postgres (Supabase or Neon) gets you point-in-time recovery, branch databases, and an HTTP gateway out of the box. Don't run Postgres yourself — the operational cost is real and the failover stories are worse than you think.

How it flows

step by step
  1. 01

    Provisioned via Terraform / vendor UI with row-level security ON globally.

    step
  2. 02

    Two roles: `authenticated` (used by the app via JWT) and `service_role` (admin / migrations).

    step
  3. 03

    Connection pooling via PgBouncer / Supavisor (vendor-provided) — never connect directly from serverless.

    step
  4. 04

    Read replicas for heavy analytics queries (Phase 2; default to one writer until you have evidence).

    step

Cases & edge cases

watch for these

Serverless cold starts kill direct Postgres connections — always use the HTTP gateway or a pool.

Don't enable extensions you don't need (pg_cron, pgvector, postgis) — every extension is a version-pin risk.

Replication lag on read replicas → never read your own writes from a replica; mark write-followed reads.

Vendors & tools

what implements this

Supabase

primarysite

Managed Postgres with auth + storage + realtime baked in.

pricingFree tier; Pro $25/mo + usage

Pros

  • RLS is first-class
  • Drizzle has @supabase imports
  • Edge-friendly via HTTP gateway

Cons

  • Newer than RDS — less battle-tested at extreme scale (10M+ rows on hot tables)

Neon

alternatesite

Serverless Postgres with branching (DB-per-PR).

Pros

  • Branching for preview deploys is magic
  • Scales to zero

Cons

  • No built-in auth; you'll need Clerk or similar

Decisions

pick once, live with it

  • Decision 01

    Supabase or Neon for new SaaS in 2026?

    pickSupabase if you want the integrated stack (auth + storage + realtime); Neon if you're combining with Clerk and want branching.

    whyBoth are excellent. Supabase wins on integration density, Neon on scale-to-zero + DB-per-PR.

02
QueryType-safe queries

Drizzle ORM

SQL-first, schema-as-code ORM. Inferred types, no decorators, doesn't fight RLS. Queries read like SQL but with autocomplete. Replaces Prisma for most 2026 Next.js SaaS builds.

How it flows

step by step
  1. 01

    Schema declared in drizzle/schema/*.ts as table objects (pgTable).

    step
  2. 02

    Queries: db.select().from(orgs).where(eq(orgs.slug, slug)) — types inferred end-to-end.

    step
  3. 03

    RLS-aware: queries run inside withTenant() which sets the org_id session var.

    step
  4. 04

    Migrations: drizzle-kit generate diffs schema vs DB → emits SQL → human reviews → drizzle-kit migrate applies.

    step

Cases & edge cases

watch for these

Joins with RLS: the policy applies to the OUTER row; joined tables also need policies. Don't assume.

Avoid prepared statements at the pool boundary in serverless — bypass the pool, use the HTTP gateway.

Don't pre-fetch into TypeScript when you can filter in SQL. The DB's smarter than your `.filter(...)`.

Checklist

ship readiness

  • Schema in code, migrations in version control

    must

  • withTenant() wrapper used everywhere

    must

  • Drizzle's logger enabled in dev

    should

Where it lives

code references
References
  • drizzle/schema/
  • drizzle.config.ts
  • lib/db/index.ts // pooled client + withTenant
03
SchemaTables · IDs · Soft-delete

Schema Design

Conventions that compound: UUIDv7 IDs (sortable, K-orderable), created_at/updated_at on every table, soft-delete with deleted_at, org_id NOT NULL with RLS, JSONB for genuinely-variable data only.

How it flows

step by step
  1. 01

    Every table: id uuid PK DEFAULT uuid_generate_v7(), org_id uuid NOT NULL FK, created_at, updated_at.

    step
  2. 02

    Soft-delete: deleted_at timestamptz; partial index WHERE deleted_at IS NULL on hot lookups.

    step
  3. 03

    Naming: snake_case in DB, camelCase in TS, Drizzle maps via `name:` in column defs.

    step
  4. 04

    Foreign keys: ON DELETE CASCADE for child-to-parent within an org; RESTRICT for cross-org refs.

    step

Cases & edge cases

watch for these

UUIDv4 IDs kill BTree locality → use UUIDv7 (time-ordered).

updated_at via trigger, not app code — app code forgets.

Soft-delete is forever or it isn't — once you ship it, you can't quietly hard-delete or it breaks history.

Checklist

ship readiness

  • UUIDv7 (not v4) PKs

    should

  • created_at + updated_at via trigger

    must

  • Partial indexes on deleted_at IS NULL

    should

Where it lives

code references
References
  • drizzle/schema/_helpers.ts // baseColumns(), softDelete()

Notes

margin

Add `created_by` (user uuid) and `updated_by` to every audit-relevant table. Costs 16 bytes, saves your investigation.

04
MigrationsDrizzle-kit · Forward-only

Migrations

Forward-only SQL migrations checked into the repo, applied by a CI job before deployment. Never edit a shipped migration. Rollback is a new forward migration (a 'reverse').

How it flows

step by step
  1. 01

    Edit schema → pnpm drizzle-kit generate → produces drizzle/migrations/NNNN_<name>.sql.

    step
  2. 02

    Review the SQL by hand. Edit if needed (e.g. add CONCURRENTLY).

    step
  3. 03

    Commit → CI runs migration against staging on PR open.

    step
  4. 04

    Deploy → migration step runs before app boot. If it fails, deploy is blocked.

    step

Cases & edge cases

watch for these

CREATE INDEX without CONCURRENTLY locks the table — production migrations must use CONCURRENTLY.

Renaming a column is a 3-step dance: add new col → backfill + dual-write → drop old col (two deploys).

Lock timeout: SET lock_timeout = '5s' protects you from a slow migration starving writes.

Checklist

ship readiness

  • lock_timeout configured in every migration script

    must

  • CONCURRENTLY for index creation on tables >100k rows

    must

  • Forward-only — never edit a merged migration

    must

Where it lives

code references
References
  • drizzle/migrations/
  • .github/workflows/migrate.yml
05
QueryWhere queries actually live

Indexes & Performance

Multi-tenant Postgres lives or dies on index design: composite indexes leading with org_id, partial indexes for soft-delete + status filters, BRIN for time-series. EXPLAIN ANALYZE every slow query before optimizing further down the stack.

How it flows

step by step
  1. 01

    Identify hot queries from logs (slow query log, pg_stat_statements).

    step
  2. 02

    EXPLAIN ANALYZE — confirm it's not using an index or is doing a sequential scan.

    step
  3. 03

    Add composite index: (org_id, <filter_col>, <sort_col>).

    step
  4. 04

    Re-EXPLAIN — confirm plan changed and rows visited dropped.

    step
  5. 05

    Commit + monitor query metrics in prod for a week.

    step

Cases & edge cases

watch for these

Don't pre-index. Every index slows writes. Wait for evidence (slow query, pg_stat_statements top entry).

Composite indexes are leading-prefix sensitive: (org_id, status) supports WHERE org_id=… AND status=…, NOT WHERE status=…

JSONB indexes use GIN — only when you query JSONB paths repeatedly. Most JSONB columns don't need an index.

Where it lives

code references
References
  • drizzle/migrations/ // index DDL

Notes

margin

Have pg_stat_statements enabled and a weekly 'top 20 slow queries' review until you have <5 over the threshold.

06
OpsPoint-in-time recovery

Backups & PITR

Supabase + Neon include PITR (point-in-time recovery) on paid plans. Daily logical dumps offsite for true paranoid recovery. Test the restore quarterly — backups you haven't restored aren't backups.

How it flows

step by step
  1. 01

    Managed provider runs continuous WAL archiving → 7-day PITR window standard.

    step
  2. 02

    Nightly pg_dump → encrypted to a separate cloud bucket (different vendor if possible).

    step
  3. 03

    Quarterly: spin up a new DB from yesterday's dump, run smoke tests, document the time.

    step
  4. 04

    Annual: full disaster recovery drill from offsite backup.

    step

Cases & edge cases

watch for these

PITR doesn't survive vendor account compromise — offsite dumps do.

Encrypt backups with a key NOT stored in the same vendor (KMS in a separate AWS account or similar).

Verify restore time. If it's > your RTO commitment, escalate to a hot standby pattern.

Checklist

ship readiness

  • PITR enabled (managed Postgres)

    must

  • Offsite dumps to a different vendor

    must

  • Restore tested quarterly with documented time

    must
07
SchemaWho did what, when

Audit Trail

An append-only table that logs every mutation: actor, action, target, before/after. Required for SOC 2, indispensable for support, prevents 90% of 'who deleted my data' tickets.

How it flows

step by step
  1. 01

    Every server action that mutates state calls audit.log(actor, action, targetId, diff).

    step
  2. 02

    Trigger-based audit on the DB side as a defense-in-depth (catches direct SQL).

    step
  3. 03

    Audit table is partitioned by month, vacuum-aware, never updated/deleted.

    step
  4. 04

    Export view for customer-facing 'activity log' surfaces.

    step

Cases & edge cases

watch for these

Don't put the audit table inside the same RLS perimeter — admins need cross-tenant queries.

Diffs are JSON of changed fields, not full snapshots — keeps the row size manageable.

Audit entries are never edited. Append-only. If you need to redact (GDPR), tombstone with a reason.

Where it lives

code references
References
  • drizzle/schema/audit-log.ts
  • lib/audit/log.ts

Notes

margin

If you can't answer 'who did X on Tuesday at 3pm and what changed?', your audit trail is broken — fix it before someone asks.

Annotate0 strokes

Apple Pencil draws with pressure. Fingers still scroll. Saved per pillar.