Skip to content

Database ​

Cloudflare D1 (SQLite) through Drizzle. Schema: packages/db/src/schema. Migrations: packages/db/src/migrations/<timestamp>_<name>/migration.sql (drizzle-kit v1 layout). Alchemy applies them on deploy and on alchemy dev (migrations in packages/infra/alchemy.run.ts).

Changing the schema ​

bash
# 1. edit packages/db/src/schema/*.ts
( cd packages/db && bunx drizzle-kit generate --name=add_leads )   # generates a new migration folder
# 2. review migration.sql; commit schema and migration together
bun run db:check                             # confirms you only added files

Rules:

  1. Append-only. Never edit, rename or delete a migration that has been committed to a shared branch. Mistake in a migration? Add another migration that fixes it. bun run db:check (and CI) fails when a migration that exists in the base ref is modified or deleted. In CI it compares against MIGRATION_BASE=origin/main.
  2. One concern per migration, with a name that says what changed.
  3. Never rewrite data in a schema migration without a plan. Backfills go in their own migration, written so they can run on a database that already has some rows converted.
  4. Statuses are text. Adding a status needs no migration; renaming or removing one does, and must include a data migration.
  5. Money is integer pence, time is epoch milliseconds (UTC).
  6. Hand-written SQL (triggers, backfills) uses bunx drizzle-kit generate --custom --name=..., which keeps the snapshot chain intact.

Before the first deployment only

While nothing is deployed anywhere, regenerating the initial migrations is acceptable (it is how the two current ones were produced). After the first deploy to any shared database, rule 1 is absolute.

Current tables ​

GroupTables
Auth (Better Auth, generated)user, session, account, verification, organization, member, invitation, two_factor
Systemsetting, template_version, audit_log

Regenerate the auth tables after changing Better Auth plugins: from packages/auth, bunx auth@latest generate --config src/auth.cli.ts --output ../db/src/schema/auth.ts, review the diff, then bun run db:generate.

Immutability triggers ​

Migration immutability_triggers makes history tamper-proof at the database level:

TableRule
settingNo UPDATE, no DELETE. Insert a new revision.
audit_logNo UPDATE, no DELETE.
template_versionA draft row can be edited or deleted. A published row is frozen except that it may move to retired with nothing else changed.

History tables deliberately have no foreign keys to user: a user-id column that cascades or nulls would require updating immutable rows. Triggers are not tracked by drizzle-kit; change them with a new custom migration that drops and recreates them.

Reading and writing versioned data ​

  • Current setting value: select revisions for the key, then resolveEffective(rows, now) from @shootgrow/domain; validate with parseSetting(key, value); fall back to getSettingDefault(key) when there is no row.
  • Writing a setting: insert revision max(revision)+1 with an effectiveFrom, the actor, and a reason; write an audit_log row in the same batch.
  • Publishing a template: set status = 'published', content_hash, published_at on a draft; never edit it again.

Details and examples: Versioning.

Internal documentation. Not for clients.