PostgreSQL staging service, migrations, seeds, tenant-domain schema, and backups.

Database

ByteBite uses PostgreSQL as the sole primary database, accessed through Drizzle ORM in packages/db.

Staging PostgreSQL

Attribute	Value
Engine	PostgreSQL 18
Management	Dokploy PostgreSQL service
Image	`postgres:18`
Display name (observed)	`bytebite-staging-postgres`
Generated hostname (example)	`bytebite-staging-postgres-vmg94p`
Database name	`bytebite`
User	`bytebite`
Port	5432 (internal Docker network only)
Public port 5432	CLOSED (verified 2026-09-08)

Generated hostnames and full container names (e.g. bytebite-staging-postgres-vmg94p.1.z4anjftsr84p1xhkvx6hz8jne) are Dokploy-runtime identifiers — not permanent. Backup scripts discover the running container dynamically.

Connection

migrate and api receive DATABASE_URL through Dokploy environment configuration. The connection string contains credentials and must never appear in documentation or Git.

Local development uses a separate PostgreSQL instance (postgres:17-alpine in compose.yml, published on 127.0.0.1:5432).

Drizzle and migrations

Component	Location
Schema	`packages/db/src/schema/`
SQL migrations	`packages/db/migrations/`
Migration runner	`packages/db/src/migrate.ts` → bundled as `db/dist/migrate.js`
Drizzle config	`packages/db/drizzle.config.ts`

The API process does not auto-migrate on startup. Staging (and later production, when it reuses compose.deploy.yml) applies pending migrations via the migrate one-shot before api starts.

Running migrations (staging)

Automatic on every Dokploy deploy: compose service migrate runs node db/dist/migrate.js.

Manual fallback (emergency only):

node db/dist/migrate.js

From a machine with staging DATABASE_URL:

pnpm deploy:db:migrate

Staging seeds

Staging tenant seeds are separate from local dev seeds:

Command	Hostnames	When
`node db/dist/seed-staging.js`	`luigi.staging.getbytebite.co`, `mario.staging.getbytebite.co`	Fresh staging DB, once
`pnpm db:seed` (local only)	`luigi.bytebite.test`, `mario.bytebite.test`	Local dev

Never run seed-staging.js against production. Never run it against the local dev database (it overwrites domain hostnames for shared row IDs).

Implementation: packages/db/seeds/staging.ts.

Infrastructure-relevant schema

`restaurants`

Stores restaurant identity, status (ACTIVE / INACTIVE), and branding.

`restaurant_domains`

Maps normalized hostnames to restaurants. Tenant resolution is an exact match on hostname after normalization.

Column	Infrastructure relevance
`hostname`	Unique, normalized — the tenant lookup key
`type`	`BYTEBITE_SUBDOMAIN` or `CUSTOM_DOMAIN`
`is_primary`	At most one primary per restaurant
`verified_at`	Required for custom domains (future)

DNS wildcard existence does not create rows here. Unknown hostnames fail tenant lookup.

Failure expectations

Condition	API behavior
Unknown/inactive tenant	HTTP 404 (`Restaurant not found`) on tenant-aware routes
Database unreachable	HTTP 503 on tenant-aware routes
`/health`	Always available (no tenant lookup)
`/health/db`	Returns `degraded` if DB unreachable

Backups

Aspect	Status
Backup type	Logical (`pg_dump --format=custom --no-owner --no-acl`)
Schedule	Daily at 02:15 UTC (systemd timer)
Off-host storage	Encrypted via Restic → Backblaze B2
Restore verified	Yes — 2026-09-08
Restore drill	Manual script only — not scheduled

Staging PostgreSQL backups are automated, encrypted, and stored off-host. Off-host restore to an isolated PostgreSQL 18 instance was verified without modifying the live staging database. Restore drills verify restaurants, restaurant_domains, and staging tenant domains (luigi.staging.getbytebite.co, mario.staging.getbytebite.co).

Production backup is not applicable — production is not deployed.

Full backup architecture, scripts, retention, and credential model: Backups. Recovery procedures: Disaster recovery. Health checks: Validation.

Tenancy & storefront — full tenant model
ADR-002 Primary Database
Staging deployment — migration and seed runbook
Backups — backup architecture and automation
Disaster recovery — PostgreSQL failure runbook

Database

On this page