# Database > The D1 tables, how queries are written, how migrations ship and run, and how a schema change reaches your site. This page shows what is stored in your D1 database, how queries are written, when migrations run, and how to undo one. ## Tables Each site has one D1 database. `@goodnight-web/server` defines every table in `packages/server/src/db/schema.ts`, written with [Drizzle ORM](https://orm.drizzle.team/). | Table | Contents | | ------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `submissions` | Contact form submissions: email address, `fields` (the form values as JSON, keyed by field ID), `form_version`, `locale`, status (`unread` or `read`), `suspected_spam`, spam score and reasons, `ip_hash` (an HMAC of the IP address, never the address itself) | | `form_definitions` | Versions of the contact form definition. Append-only: one row per saved version, never changed or deleted | | `settings` | `retention_days`, `last_cleanup_at` (for [data pruning](https://goodnight-web.dev/docs/how-it-works/data-pruning.md)), and `setup_closed_at` | | `user`, `session`, `account`, `verification`, `passkey` | Better Auth: admins, sessions, and passkeys | Times are stored as Unix milliseconds. The tables of Better Auth, which handles admin sign-in, are generated by the Better Auth CLI for its Drizzle adapter. Better Auth and the rest of the server use the same schema and the same migration history. ### Status and suspected spam The status of an inquiry and its suspected spam flag are separate columns. The status is one of `SUBMISSION_STATUSES` (`['unread', 'read']`): ```ts title="packages/server/src/db/schema.ts" status: text('status', { enum: SUBMISSION_STATUSES }) .notNull() .default('unread'), /** * Set when the honeypot was filled or the spam score reached * `SPAM_SUSPECT_THRESHOLD`. Admins can clear or set it. Flagged rows are * never sent to Slack and are listed apart from the others. */ suspectedSpam: integer('suspected_spam', { mode: 'boolean' }) .notNull() .default(false), ``` A check constraint on `status` means the database itself accepts only `unread` and `read`. An index serves the unread count and the **Unread** and **Read** tabs in the admin screen: ```ts title="packages/server/src/db/schema.ts" // Unread count and the unread and read views. index('idx_submissions_status_created').on(table.status, table.createdAt), // Keep in step with SUBMISSION_STATUSES (checked by a test). check( 'submissions_status_check', sql`${table.status} in ('unread', 'read')`, ), ``` ## Contact form fields The form fields are not table columns. Admins edit them under **Form** in the admin screen. Each save adds one row to `form_definitions`, with the next `version` number and the definition as JSON. The highest version is the current form. Two triggers reject changes to existing rows: one for `UPDATE` and one for `DELETE`. ```sql title="migrations/0004_form_definition_seed.sql" CREATE TRIGGER `form_definitions_no_update` BEFORE UPDATE ON `form_definitions` BEGIN SELECT RAISE(ABORT, 'form_definitions is append-only'); END; ``` A submission stores its values in `submissions.fields`, keyed by immutable field IDs such as `f_name`. The form version used to check it goes in `submissions.form_version`. Old inquiries are always shown with the definition of their own version. See [Customizing the contact form](https://goodnight-web.dev/docs/contact-form.md). ## How queries are written [#how-queries-are-written] The server never builds SQL text from input. Every query goes through the Drizzle query builder, which sends values to D1 as bound parameters. App code reaches D1 only through `getDatabase(env.DB)` in `packages/server/src/db/database.ts`. It returns the Drizzle database for the binding. The admin list is a typical query. The filters, the page size, and the offset are all bound. The list and its total go to D1 in one batch: ```ts title="packages/server/src/features/submissions/submission-store.ts" const orm = getDatabase(db); const where = filterWhere(filter); const [rows, [counted]] = await orm.batch([ orm .select() .from(submissions) .where(where) .orderBy(...orderBy(order)) .limit(limit) .offset(offset), orm.select({ total: count() }).from(submissions).where(where), ]); ``` CI enforces this. `bun run check:sql` (part of `bun run validate`) scans `packages/server` and fails if it finds any of these outside test helpers: ```ts title="scripts/check-sql.ts" export const SQL_RULES: readonly SqlRule[] = [ { name: 'sql.raw(', pattern: /\bsql\s*(?:\.\s*raw|\[\s*['"`]raw['"`]\s*\])\s*\(/g, reason: 'inserts text into SQL unescaped; use the sql`` template', }, { name: '.prepare(', pattern: /\.\s*prepare\s*\(/g, reason: 'builds a D1 statement directly; use the Drizzle query builder', }, { name: '$client', pattern: /\$client\b/g, reason: 'reaches the raw D1 binding behind Drizzle', }, ]; ``` For the input checks and the SQL that D1 receives, see [SQL injection](https://goodnight-web.dev/docs/security.md#sql-injection) on the security page. ## How migrations ship and run The migrations ship in the npm package, and the deploy script applies them before the new code goes live. You do not write migrations for your site. ### How they are applied Your site's `wrangler.jsonc` points the D1 binding at the migrations directory in the package: ```jsonc "d1_databases": [ { "binding": "DB", "migrations_dir": "node_modules/@goodnight-web/server/migrations" } ] ``` The deploy script runs: ```sh wrangler d1 migrations apply DB --remote && wrangler deploy ``` Wrangler applies the files in name order and records each applied file in a `d1_migrations` table in your database. A file that is already recorded is skipped, so running the command again does nothing. If a file fails, D1 rolls that file back. The deploy stops before `wrangler deploy`. Do not run `wrangler d1 migrations create`. It would write a file into `node_modules`, which is not in your repository and is replaced when packages are reinstalled. ## How a schema change reaches your site [#how-a-schema-change-reaches-your-site] 1. goodnight-web changes the schema, generates a new migration file such as `0007_add_note.sql`, and publishes a new version of `@goodnight-web/server`. Released migration files are never edited or renamed. 2. Dependabot opens a pull request in your repository that updates `@goodnight-web/server`. See [Distribution and updates](https://goodnight-web.dev/docs/distribution.md). 3. You merge it. The deploy script applies `0007_add_note.sql`, then deploys the new code. From the moment the migration is applied until `wrangler deploy` finishes, the old version of the Worker runs against the new schema. Schema changes are additive (new tables and columns), and the queries list the columns they use, so the old version keeps working during that time. A build of a pull request (when Workers Builds builds non-production branches) does not run the deploy script, so it never migrates your database. The starter turns preview URLs off, so such a build has no address. If you turn preview URLs on, a preview uses your production database without the new migration, so a screen that needs the new schema can fail there. ## Undoing a migration with Time Travel D1 Time Travel restores the whole database to any minute in the past: up to 7 days back on the Workers free plan, and up to 30 days on the paid plan ([Cloudflare: Time Travel](https://developers.cloudflare.com/d1/reference/time-travel/)). ```sh wrangler d1 time-travel info DB --timestamp=2026-10-22T09:00:00Z wrangler d1 time-travel restore DB --timestamp=2026-10-22T09:00:00Z ``` A restore also removes every inquiry and every sign-in after that time. It does not change the deployed code. If you restore to a time before a migration, also deploy the matching older version of your site. Otherwise the deploy script applies the migration again on the next deploy. A restore also brings back rows that were deleted after that time, including security changes: - Passkeys and sessions deleted by [passkey recovery](https://goodnight-web.dev/docs/how-it-works/passkey-recovery.md) or by [removing an admin](https://goodnight-web.dev/docs/security.md#removing-an-admin) come back, so an old passkey can sign in again. - A restore to a time before the first passkey was registered opens setup again: anyone with the setup token (`APP_SECRET`, or `SETUP_TOKEN` if you set it) and an address in `ADMIN_EMAILS` can then create an admin. After a restore, check the `passkey` and `session` tables and the `setup_closed_at` row in `settings`, and run the recovery or removal statements again if the restore crossed one of them. ### Rolling back the Worker There are no down migrations. If you roll back to an older version of the Worker in the Cloudflare dashboard after a schema change, the older code runs against the newer schema. goodnight-web keeps schema changes additive (new tables and columns), so older code keeps working, but roll back the database with Time Travel only together with the matching code. ## The migrations in the package The package contains the SQL migrations generated by drizzle-kit: ```txt node_modules/@goodnight-web/server/migrations/ 0000_initial_schema.sql 0001_settings_defaults.sql 0002_setup_closed_trigger.sql 0003_form_definitions.sql 0004_form_definition_seed.sql 0005_drop_fixed_contact_columns.sql 0006_unread_read_status.sql ``` Migrations `0003` to `0006` changed how the contact fields and the status are stored. They shipped before the first release. Migrations `0003` to `0005` moved the contact fields from columns into the form definition: 1. `0003_form_definitions.sql` creates `form_definitions` and adds `locale`, `fields`, and `form_version` to `submissions`. 2. `0004_form_definition_seed.sql` is written by hand. It does three things: - Saves version 1 of the form with the fields the form had before (`f_name`, `f_company`, `f_phone`, `f_category`, `f_message`). - Copies each existing submission's `name`, `company`, `phone`, `category`, and `body` into `fields`, keyed by those ids. - Creates the append-only triggers. 3. `0005_drop_fixed_contact_columns.sql` drops the old columns. [Schema changes are additive](#how-a-schema-change-reaches-your-site), and this column drop is the exception. It shipped before the first release, so no deployed Worker writes those columns. `0006_unread_read_status.sql` limits the status to `unread` and `read` and adds the `suspected_spam` flag. In SQLite, changing a check constraint means rebuilding the table, so drizzle-kit copies `submissions` into a new table. The generated copy step was edited by hand to map the old statuses as follows. - `new` becomes `unread`. - Every other status becomes `read`. - Rows that were `held` or `spam` get `suspected_spam = 1`.