# Database (https://lazyfrog.dev/docs/guide/database)

{/* Generated by scripts/generate-docs.ts from services/gateway/src/guide.md. Do not edit. */}

- Each environment (preview, production) has its own PostgreSQL 19; `DATABASE_URL` and the `PG*`
  variables are set for services and `exec`. Extensions available, each turned on in a migration
  before it is used (`CREATE EXTENSION IF NOT EXISTS pgcrypto;`): pgvector, PostGIS, pg_trgm,
  citext, hstore, uuid-ossp, pgcrypto (`gen_random_bytes`; `gen_random_uuid()` needs none),
  btree_gist, pg_stat_statements. `"pool": true` on a
  service adds `DATABASE_POOL_URL` (PgBouncer, transaction mode) for many short queries; LISTEN,
  advisory locks and session `SET` need `DATABASE_URL`. A `"migrate"` command (the framework's
  own: Prisma, Drizzle, Alembic, Django, Rails, Ecto, EF Core, Flyway…) replaces the `migrations/` folder: keep one or the other. Bookings that mustn't
  overlap: an exclusion constraint (`EXCLUDE USING gist (tstzrange(starts_at, ends_at) WITH &&)`)
  holds even when two people book at once. Two inserts at the same moment can fail with `40P01`
  (deadlock) as well as `23P01` (the constraint): treat both as "that time was just taken" (or
  retry once), not as a crash page.
- Schema changes: add SQL files to `migrations/` named `0002_description.sql`, `0003_...`; apply them
  with `db migrate`. Each runs in its own transaction; never edit an applied migration, add a new
  one. A framework's own migration command can replace this: set `"migrate"` in services.json.
- `db_query` runs SQL (reads directly; writes on preview after an automatic snapshot);
  `db_schema` shows tables. `VACUUM`, `ANALYZE`, `REINDEX` and `CREATE`/`DROP INDEX
  CONCURRENTLY` run outside a transaction when sent alone (one statement per call, confirmed like
  a write); in a migration use a plain `CREATE INDEX`.
- The live database: `db_read` and `db_schema` with `env: "production"` read it (owner and
  editors, no extra permission; read-only, 5 seconds, the result says it is live data). Writing
  to it takes `db:production`, `confirm: true` and a snapshot; a `DROP`, `TRUNCATE`, `ALTER`, or an
  `UPDATE`/`DELETE` without `WHERE` or over about a thousand rows waits for the owner's approval
  in the portal (see "Two jobs" above).
- Dates: `TZ` is the project's zone, so node-pg reads a `date` column as local midnight, and in
  zones ahead of UTC (most of Europe in summer, Africa, Asia) `toISOString()` shows the day before. Keep dates as text with
  `pg.types.setTypeParser(1082, (v) => v)`; psycopg returns `date` objects, which are safe.
- Production data is never copied from preview by a publish (the one-time copy at the first
  publish, when the user asks for it, is the only exception: see Publishing).
- Each environment's database runs PostgreSQL 19. One sandbox runs a project's
  services and its database together: on Pro, 2 vCPU and 2 GB for all of them, 50 connections
  (more through the pool), one instance of each service. That serves a small SaaS (hundreds of
  customers with modest traffic) well; its email goes through the owner's own email provider
  (Email, AI and PDFs), and keep each service's memory in mind: the preview's dev server (`next dev` about 1 GB) takes much of
  the 2 GB, a worker 300 MB or more, and Postgres what it builds indexes with. On Free the
  sandbox is 0.5 vCPU and 768 MB: a Next.js dev server takes about 600 MB of it, so keep to one
  web service, don't run `build`, a type check or heavy scripts in the preview
  (`deploy preflight` builds and checks types with more room, as publishing does), and lean towards a static site or a light server where the app allows; Hobby has 1 GB.

**Multi-tenant apps (SaaS: many businesses on one app)**: one database with a `tenant_id` on every
row, enforced. `DATABASE_URL` is a **superuser, so row-level security never applies to it, even
with `FORCE`**: create a `NOLOGIN` role in a migration (`CREATE ROLE tenant_rw NOLOGIN`, grant it
the tables), and run each request in a transaction with `SET LOCAL ROLE tenant_rw` and
`set_config('app.tenant_id', $1, true)`, with policies on
`nullif(current_setting('app.tenant_id', true), '')::uuid` (the setting reverts to an empty
string, not NULL). This works through `DATABASE_POOL_URL`. Prove it with a test that reads
another tenant's rows as the role and gets none. `db_read` and `db_query` run as the superuser, so
they can't show it: to show the user, exec `psql "$DATABASE_URL"` with `BEGIN; SELECT
set_config('app.tenant_id', '<tenant A>', true); SET LOCAL ROLE tenant_rw;`, then count tenant B's
rows (expect 0) and try inserting one (expect a row-level security error), then `ROLLBACK`. Composite foreign keys `(id, tenant_id)` stop
cross-tenant links. Serve tenants under paths (`/s/<tenant>`): wildcard domains aren't supported
yet, and each own domain counts toward the plan's domains per project. Send as the tenant (`from_name`, and
`reply_to` the tenant's address, or replies reach the user). Keep the user's own dashboard across
tenants behind an in-app role, never only a hidden URL. A restore of the live database rewinds
every tenant, and a snapshot can only be restored whole: there is no way to read one tenant's
old rows from it. So keep a change history per tenant from the start (an append-only table of
each record's previous version, written in the same transaction), and bring a tenant's rows back
from it with a confirmed `db_query`, with the owner's approval. Materialized views have no row-level security: one granted
to the role shows every tenant's rows, so use rollup tables with the same policy (or a view over
the MV filtered on the setting). Set the settings before `SET LOCAL ROLE` when a lookup under the
role needs them, index the column each policy compares, and keep policy helpers `STABLE` and
simple: they run on every row.

**Data-heavy apps (imports, reports)**: import in a worker, never in a request (the edge gives up
on a response that hasn't started within about 2 minutes, with a 524). Batch with `unnest(...)`
and `ON CONFLICT` (about 5,000 rows a transaction: 15,000 rows a second or more on Pro), keep a
checkpoint so a re-import is idempotent, and export with `COPY ... TO STDOUT` (flat memory).
Rewritten rollups leave dead rows: `VACUUM (ANALYZE)` after a big import. Delete the raw upload
once imported. Disk is per project (1 GB on Free, 5 GB on Hobby, 20 GB on Pro and Business) for the live files of the code,
both databases, `/data` and releases together; snapshots don't count, so deleting a file frees its
space at once. At the limit writes fail and Postgres stops: `du -sh /workspace/* /data/* | sort
-h` shows what is big; once there is room the database starts again by itself, and meanwhile
exec, the `fs_` tools and `logs_tail` still work (`service_status` says what's wrong).