Appearance
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 filesRules:
- 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 againstMIGRATION_BASE=origin/main. - One concern per migration, with a name that says what changed.
- 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.
- Statuses are text. Adding a status needs no migration; renaming or removing one does, and must include a data migration.
- Money is integer pence, time is epoch milliseconds (UTC).
- 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
| Group | Tables |
|---|---|
| Auth (Better Auth, generated) | user, session, account, verification, organization, member, invitation, two_factor |
| System | setting, 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:
| Table | Rule |
|---|---|
setting | No UPDATE, no DELETE. Insert a new revision. |
audit_log | No UPDATE, no DELETE. |
template_version | A 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 withparseSetting(key, value); fall back togetSettingDefault(key)when there is no row. - Writing a setting: insert revision
max(revision)+1with aneffectiveFrom, the actor, and a reason; write anaudit_logrow in the same batch. - Publishing a template: set
status = 'published',content_hash,published_aton a draft; never edit it again.
Details and examples: Versioning.