One Command, One File: Generating the Whole MySQL Schema from Drizzle

Our schema lives in TypeScript. That is great for the application — every query is typed, every column rename is a compile error — and terrible for every conversation that starts with "so how does the data actually fit together?"

Drizzle's drizzle/*.sql folder is a migration log, not a schema. Read it top to bottom and you get the history of every column we ever regretted. What people actually want is a snapshot: 18 tables, 33 foreign keys, 6 views, one file, current as of right now.

So we added one command.

pnpm --filter @ff/db db:ddl

It writes infra/mysql/family_finance_all_tables.sql — the complete DDL, no database connection required. This post walks through how the generator works, and then uses the output to explain the schema it produces.

The generator

Drizzle Kit already ships the primitive we need:

drizzle-kit export --sql

export diffs the schema against an empty state, which is exactly the definition of "full DDL". No live database, no migration replay. It emits tables, then foreign keys, then indexes, then views — already in dependency-safe order.

Three rough edges made it worth a wrapper script:

1. It prints a banner to stdout. Reading schema files: /path/to/schema/index.ts lands in your .sql file if you naively redirect. The script drops everything before the first CREATE/ALTER/DROP/INSERT.
2. No header. A generated file with no provenance gets hand-edited within a week. Ours opens with a GENERATED FILE — do not edit by hand block naming the source and the regenerate command.
3. No session guards. We wrap the body in SET NAMES utf8mb4 and SET FOREIGN_KEY_CHECKS = 0/1, so the file is order-independent and safe to pipe straight into mysql.

That is the entire script — packages/db/src/scripts/export-ddl.ts, about 80 lines, execFileSync plus string handling. The interesting part is not the code, it is that the output is verifiable.

Verifying a generated schema

A schema snapshot that silently drifts from reality is worse than no snapshot. So we checked it the only way that counts: applied the file to an empty database and compared it against the real dev database, which was built by running every migration in order.

The only difference is __drizzle_migrations and its two indexes — Drizzle Kit's own bookkeeping table, which correctly does not belong in a schema snapshot. Everything else matches exactly, including all six views.

Snapshot, not migration. This file bootstraps an empty database and documents the current shape. To change an existing database you still use pnpm db:generate / pnpm db:migrate. The header says so, because someone will try.

What the schema looks like

Now the payoff. Here is the DDL, rendered as diagrams instead of 380 lines of backticks.

The shape at a glance

Every domain row hangs off households. That is not decoration — it is the tenancy boundary, and it is enforced in the schema itself (more on that below).

Five clusters, one root. The rest of this section expands each one.

Identity and auth

Two details worth pausing on.

users.household_id is nullable with ON DELETE SET NULL. A user exists before they join a household — that is the onboarding flow — and deleting a household orphans its members rather than deleting people. Every other domain table uses ON DELETE CASCADE, because a transaction without a household is meaningless.

The auth_* tables are Better Auth's, with string UUID primary keys, sitting alongside our BIGINT domain tables. Mixed key types in one database look untidy until you remember these tables are owned by a library and shaped by its contract. auth_verification has no foreign key at all: it holds pre-account tokens, keyed by an email address that may not correspond to a row yet.

The money ledger

This is the core of the product, and the most opinionated part of the schema.

Money is BIGINT đồng. Not DECIMAL, not a float, never a JavaScript number. Vietnamese dong has no minor unit in practice, and BIGINT gives us exact arithmetic up to nine quintillion — comfortably past any household budget. It is serialized as a decimal string in JSON, because Number.MAX_SAFE_INTEGER is a bug waiting for a large enough portfolio.

occurred_month is a generated stored column. DATE_FORMAT(occurred_at, '%Y-%m-01'), computed by MySQL, indexed alongside household_id and category_id. Monthly rollups are the single most common query in the app, and this turns "group by month" from a function call on every row into an index range scan. The database owns the derivation, so no application code can disagree about which month a transaction belongs to.

A CHECK constraint enforces the transaction shape. TRANSFER rows must have a transfer_direction and no category; INCOME/EXPENSE rows must have a category and no direction:

CONSTRAINT `chk_tx_type_fields` CHECK ((
  (`type` = 'TRANSFER' AND `category_id` IS NULL AND `transfer_direction` IS NOT NULL)
  OR
  (`type` IN ('INCOME','EXPENSE') AND `category_id` IS NOT NULL AND `transfer_direction` IS NULL)
))

This is the kind of rule that usually lives in a service method, gets duplicated in a background job, and then diverges. Here it is impossible to violate.

Soft deletes are the default for user-facing financial rows. transactions, receipts, assets, and liabilities all carry deleted_at, and every read filters on IS NULL. Financial history is not something you hard-delete because someone tapped the wrong row.

Composite foreign keys as a tenancy guard

Here is the trick I would steal for other multi-tenant schemas. Look closely at the transaction → category link:

ALTER TABLE `transactions`
  ADD CONSTRAINT `fk_tx_category`
  FOREIGN KEY (`category_id`, `household_id`)
  REFERENCES `categories`(`id`, `household_id`) ON DELETE CASCADE;

Not category_id → categories.id. Both columns. Backed by a redundant-looking UNIQUE(id, household_id) on every parent table.

The single-column version lets you write a transaction in household A that points at a category in household B. Your service layer would never do that on purpose — but one missing WHERE household_id = ? in one query builder, and you have a cross-tenant data leak that no test catches because both rows look valid.

The composite version makes that row unrepresentable. MySQL rejects it. The same pattern secures budgets → categories, base_rate_history → liabilities, liability_payments → liabilities, and transaction_category_prediction → transactions.

The cost is one extra unique index per parent table. Cheap insurance.

Wealth: assets and liabilities

Interest rates are stored in basis points as integers (int unsigned), same reasoning as money: 7.35% is 735, not a float that rounds badly across a 240-month amortization schedule.

base_rate_history exists because floating-rate loans in Vietnam reprice against a bank's published base rate, and the amortization schedule has to be recomputed from the rate that was actually in effect on each due date. Storing only the current rate would make historical payments unreproducible.

liability_payments.tx_id is a nullable link into the ledger with ON DELETE SET NULL — a scheduled payment exists before it is paid, and a payment row survives the deletion of the transaction that settled it.

AI plumbing: outbox and prediction audit

The app enriches transactions with semantic search over a Weaviate vector index, which raises the classic problem: MySQL and Weaviate cannot participate in the same transaction.

transaction_vector_outbox is a transactional outbox. Writing a transaction and enqueueing its index update happen in one MySQL commit; a worker drains the queue afterward. If Weaviate is down, rows accumulate with attempts incremented and available_at pushed out — nothing is lost, and no user-facing write fails because a vector database hiccuped. The claim index is (processed_at, available_at, id), which is exactly the "next batch of pending work" query.

transaction_category_prediction is the part I would argue for in any ML-touching schema. It stores not just what the model predicted, but why: which source produced it (vector neighbors, LLM, or both), the confidence band, a reason code, how many pieces of evidence were found, whether the user accepted or overrode it — plus prediction_version, index_version, and embedding_model. One UNIQUE(transaction_id) means one prediction record per transaction.

That table is how you answer "did the categorizer get better after we changed the embedding model?" with a query instead of a hunch.

Views as a narrow interface for the agent

The last six statements in the file are views, all prefixed agent_:

Our LangGraph agent writes SQL. Pointing it at raw tables means every generated query needs to remember deleted_at IS NULL, archived_at IS NULL, and the right join keys — and it will forget, on the query that matters. These views bake the filters in and are exposed through a read-only database user.

Note what the views deliberately do not do: they carry household_id rather than filtering by it. Tenant scoping stays with the caller's session, where it belongs. The view removes the mistakes an agent makes within a tenant; it is not the tenancy boundary.

Takeaways

Two lessons, one about tooling and one about schemas.

On tooling: the single-file DDL took under an hour because drizzle-kit export already existed. The work was not generating SQL, it was making the output trustworthy — a provenance header so nobody hand-edits it, and a diff against a migration-built database proving the snapshot matches reality. A generated artifact nobody trusts gets regenerated by hand.

On schemas: every constraint in this file is one that would otherwise live in application code and drift. The composite foreign keys make cross-tenant rows unrepresentable. The CHECK makes malformed transactions unrepresentable. The generated column makes "which month is this" a single answer. The outbox makes a Weaviate outage a delay instead of a failure.

You can enforce all of that in a service layer. You just have to enforce it in every service layer, forever, including the background job someone writes next year.

Reproduce it

pnpm --filter @ff/db db:ddl                       # -> infra/mysql/family_finance_all_tables.sql
pnpm --filter @ff/db db:ddl -- path/to/other.sql  # custom destination

No database connection needed — drizzle-kit export diffs against an empty state.

• Generator: packages/db/src/scripts/export-ddl.ts
• Schema source: packages/db/src/schema/*.ts
• Output: infra/mysql/family_finance_all_tables.sql
• Migrations (for existing databases): packages/db/drizzle/*.sql