1 Database and Migrations

Nico edited this page 2026-05-26 00:31:28 -07:00

Table of Contents

• Database and Migrations
• Canonical Migration Directory
• Required Environment
• Commands
• Operator Flow
• Tracking Table
• Current Data Areas
• Safety Rules

Database and Migrations

Fiddy uses an external on-prem Postgres database. Do not add or assume a database container.

Canonical Migration Directory

Canonical migrations live in:

packages/db/migrations

Legacy SQL under backend/migrations is reference-only. Do not add new canonical migrations there.

Required Environment

Migration commands need:

• DATABASE_URL set in the shell.
• psql installed and available on PATH.
• The command run from the repo root.

Do not print or commit the value of DATABASE_URL.

Commands

Show status:

npm run db:migrate:status

Create a migration:

npm run db:migrate:new -- <migration-name>

Apply pending migrations:

npm run db:migrate

Verify clean state:

npm run db:migrate:verify

Check stale legacy SQL:

npm run db:migrate:stale:check

Update stale tracking:

npm run db:migrate:stale

Operator Flow

1. Confirm which database DATABASE_URL targets.
2. Run npm run db:migrate:status.
3. Create a new migration file if the code change needs schema changes.
4. Review the SQL.
5. Apply pending migrations only when the operator intends to change that database.
6. Run npm run db:migrate:verify.
7. Run the relevant app tests/builds.

Tracking Table

Applied migrations are recorded in:

schema_migrations(filename text unique, applied_at timestamptz)

Migrations apply in lexicographic filename order.

Current Data Areas

The current app stores data for:

• Users and DB-backed sessions.
• Households and household members.
• Invite links, join requests, join policy, and audit events.
• Stores, household stores, locations, and zones.
• Location-scoped list items.
• Available/catalog items.
• Item images and metadata.
• Item classification and notes.

Safety Rules

• Never run migrations against production/shared data unless explicitly asked.
• Never edit applied migration history manually unless performing a planned database repair.
• Do not log secrets, DB URLs, tokens, cookies, receipt bytes, or full invite codes.
• Invite-code logging may include last 4 characters only.
• Add tests for API behavior that depends on a new schema contract.