Database

Database layout

packages/db/src/schema.ts defines the Drizzle schema. Versioned SQL lives in packages/db/migrations, and root drizzle.config.ts uses the SQLite dialect. The Worker accesses D1 through its DB binding; Wrangler provides local D1 without requiring a remote database first.

The database stores authentication, orders, credit ledger entries, file metadata, tasks, webhook events, audit records, and rate-limit state. R2 stores file bodies while D1 stores ownership and indexes. They serve different purposes.

Initialize local data

pnpm install --frozen-lockfile
pnpm muse:init
pnpm dev

Init applies local migrations and creates development accounts. Re-running it preserves existing account passwords and never seeds production. Set MUSE_SEED_PASSWORD before the first init to choose a password of at least eight characters. Check the local Wrangler state before deleting a database to troubleshoot a login problem.

Local state belongs to the development machine and is ignored by Git. Reinitializing on a new machine creates fresh development data; it does not restore production backups.

Add a field safely

1. Edit schema.ts with explicit nullability, defaults, uniqueness, foreign keys, and indexes.
2. Generate and review the SQL:

pnpm db:generate

3. Run pnpm muse:init to apply pending local migrations. Check both existing and newly created records.
4. Update queries, API schemas, UI types, and contracts together.
5. Commit the schema and migration. Add a new migration to repair deployed behavior rather than rewriting an applied file.

For a required field on populated tables, consider a staged nullable field, backfill, validation, and later constraint. Review production volume and locking effects separately from local success.

Respect D1 boundaries

Use supported D1 batches and the repository's existing persistence helpers. Do not add a Node database driver that requires long-lived connections or copy callback transaction patterns from another database. Existing time columns use integer milliseconds; do not accidentally interpret them as seconds or text dates.

Credit reservations, ledger entries, and outbox writes have atomic and idempotent boundaries. Webhooks, admin actions, and queue retries can arrive concurrently. Reuse those constraints instead of reading a balance and updating it in unrelated statements.

Review production identity and migrations

Confirm target database names in packages/cli/src/target-resources.ts. A new product needs its own resource names; an existing product must preserve its account and resource identity. The example production database flare-musemvp-com-d1 belongs to the demo deployment.

pnpm muse:doctor --target production --json
pnpm muse:deploy --target production --plan --json

Inspect migrations and resource actions. Unexpected creation during an upgrade usually warrants checking the account, names, and local deployment state before proceeding. The CLI blocks high-risk forms such as DROP, TRUNCATE, DELETE, and UPDATE SET in pending SQL. Review, back up, apply, and record such changes separately instead of removing the guard.

Verify and troubleshoot

• Missing local tables: confirm init completed and uses this checkout's migration directory.
• Missing production history: verify D1 bindings, account, and resource names before writing to an unexpected empty database.
• Unique constraint failure: determine whether the same business event is being retried and reuse its idempotent outcome.
• Worker rollback: reverting code does not revert a D1 migration; check schema compatibility separately.

Run pnpm type-check, pnpm test:unit, and pnpm test:workers before reviewing the remote deployment plan.