Five database design decisions I'd fail in a textbook and why I made them anyway for SpendGauge
Every database design course teaches you the rules. Normalize everything. Use native boolean types. Index your foreign keys. Follow the conventions and you'll be fine.
SpendGauge is a personal finance app. Every cent matters. A rounding error isn't a UI glitch, it's a user's budget being wrong. That pressure made me stop asking "what does convention say?" and start asking a better question: "what query requirements will change my schema six months from now?"
That single question led me to five decisions that look wrong on the surface and are deliberate underneath. Here's each one, why I made it, and where Drizzle ORM either forced or enabled the call.
1. The money problem: why I let Drizzle return strings and stopped fighting it
Floating point for money is a settled argument 0.1 + 0.2 !== 0.3 in binary, and in a budgeting app where numbers compound across dozens of calculations per dashboard load, those rounding errors stack into figures that are visibly, embarrassingly wrong. Nobody serious uses FLOAT for currency anymore.
The live debate is between two correct-ish options: integer cents (1050 representing $10.50) or PostgreSQL's native DECIMAL type. Integer cents has better raw performance and zero precision ambiguity. The cost is a conversion step everywhere currency values appear in your application code, and "everywhere" is exactly the kind of word that means "someone will forget one path eventually."
I went with DECIMAL:
// expenses table
amount: decimal("amount", { precision: 12, scale: 2 }).notNull()Here's the part nobody mentions in the comparison posts: Drizzle returns DECIMAL columns as TypeScript string, not number. That's not a quirk it's the correct behavior, because JavaScript's number type is a 64-bit float and can't represent all decimal values exactly. Drizzle is being honest with you.
But "honest" can still be dangerous. A string that's secretly a decimal looks fine to TypeScript and to your linter. amount + tax compiles without complaint and produces string concatenation. You have to build the discipline yourself: parse the string once at the boundary, do all arithmetic using decimal.js, format back to string on the way out, never touch the raw string in a calculation.
import Decimal from "decimal.js";
// Correct
const total = new Decimal(expense.amount).plus(new Decimal(expense.tax));
// Silent bug — TypeScript won't catch this
const total = expense.amount + expense.tax; // "25.502.50" ← string concatThe ORM gives you the accurate type. The discipline to use it correctly is a code review checklist item, not a library feature.
2. Denormalization: I cached a derived value on the users table, and the staleness timestamp is proof I knew what I was doing
The classic rule is one source of truth. I broke it.
totalMonthlyIncome lives on the users table, even though it's derived from the income_sources table where each income stream salary, freelance, rental, side hustle has its own amount and frequency:
// users table — the cache
totalMonthlyIncome: decimal("total_monthly_income", { precision: 12, scale: 2 })
.notNull()
.default("0"),
incomeLastRecalculated: timestamp("income_last_recalculated", { withTimezone: true }),// income_sources table — the source of truth
amount: decimal("amount", { precision: 12, scale: 2 }).notNull(),
frequency: incomeFrequencyEnum("frequency").notNull(),
// "weekly", "biweekly", "monthly", "annually" — not directly summableThe problem with computing this on every read is that frequency normalization is not trivial math. A biweekly salary and a monthly freelance contract aren't directly summable you have to convert both to a monthly equivalent, handle edge cases like semi-monthly pay schedules (1st and 15th isn't exactly twice monthly), and do this across every income source a user has, multiplying that by every dashboard load, every budget calculation, every "safe to spend" figure you get real CPU on every page view.
totalMonthlyIncome is read constantly and written only when a user adds or changes an income source which is rare. This cache was the answer to a read-heavy, write-light access pattern.
The incomeLastRecalculated timestamp is the tell that this is intentional, not lazy. It makes staleness visible both to the application and to any engineer reading the schema. Whenever an income source is created, updated, or deleted, a function runs inside the same transaction to recalculate the total and stamp the timestamp.
The real risk isn't the denormalization,it's missing a code path. A bulk CSV import, an admin backfill script, a migration that touches income_sources, any of those that skip the recalculation function leaves totalMonthlyIncome silently wrong with no error thrown, no constraint violated. The user just sees a wrong number. That's why the recalculation lives in a single shared function that is the only exported way to write to income_sources.
3. alertTriggered as integer: the boolean I rejected because I could see the future query
This is the decision I'm most confident about, and the one that looks most like a mistake at first glance.
The budgets table has a field that tracks whether a budget alert has fired. Convention says: that's a boolean.
// What convention says
alertTriggered: boolean("alert_triggered").default(false)I shipped this instead:
// What I actually did
alertTriggered: integer("alert_triggered").default(0)Here's the question I asked: what will this field need to be in twelve months?
Right now, a user wants to know if their budget was alerted, simply a yes/no. But the natural product evolution is "how many times have you blown this budget this month?" That's a count, not a flag. If I model it as a boolean and that feature request lands, I need a schema migration, a data backfill, a new column, and a coordinated deploy. If I model it as an integer from the start, incrementing the counter is a one-line change and the field already means what I need it to mean.
There's also a real SQL ergonomics difference. Compare what aggregate queries look like:
-- With integer: clean and direct
SELECT SUM(alert_triggered) FROM budgets WHERE user_id = $1;
-- With boolean: requires a cast or a CASE expression
SELECT SUM(CASE WHEN alert_triggered THEN 1 ELSE 0 END) FROM budgets WHERE user_id = $1;
-- or
SELECT SUM(alert_triggered::int) FROM budgets WHERE user_id = $1;And checking for active alerts reads naturally either way:
-- Integer check, reads fine
WHERE alert_triggered > 0The one thing you give up with integer over native boolean is the database-level guarantee that no value other than 0 or 1 can be stored, Postgres BOOLEAN enforces that for free, where INTEGER doesn't, for the route I chose a CHECK (alert_triggered >= 0) constraint closes that gap.
4. onDelete: cascade vs onDelete: set null — two foreign keys, two different ownership stories
The budgets table has two foreign keys. They use different deletion strategies, and that's not an accident.
// budgets table
userId: uuid("user_id")
.notNull()
.references(() => users.id, { onDelete: "cascade" }),
accountId: uuid("account_id")
.references(() => accounts.id, { onDelete: "set null" }),A budget cannot exist without a user. If a user deletes their account, their budgets should go with them which is is GDPR right-to-erasure behavior, and cascade gives it to you automatically regardless of which code path triggered the deletion. No application-level cleanup code needed, no orphaned rows, no compliance gap.
A budget can exist without an account. SpendGauge supports group accounts; family, shared, business where multiple people manage finances together. If that group account is deleted, the individual user's budgets shouldn't vanish. They should fall back to being personal budgets. set null encodes exactly that: the account association disappears, the budget survives.
What makes this worth calling out isn't the specific choice it's that the onDelete policy is where your application's ownership model becomes executable. It's enforced at the database level regardless of what code does the deleting. A future admin tool, a bulk cleanup script, a migration all of them get the right behavior for free because it's in the schema, not in application logic that can be forgotten.
Drizzle's references() syntax makes this a one-line, reviewable decision per relationship. A schema review that doesn't ask "cascade or set null, and why" for every foreign key is skipping the part of the review that actually prevents data-loss incidents.
5. Composite indexing from query patterns, not from a "index your foreign keys" checklist
Indexing the foreign key user_id on expenses is insufficient.
The actual hot query path for SpendGauge's dashboard is: give me this user's expenses in category X between date A and date B. That's a category breakdown view, a budget-vs-actual comparison, a monthly summary. It has three predicates, not one.
// expenses table
userCategoryExpenseDateIndex: index("idx_expenses_user_category_date").on(
table.userId,
table.category,
table.expenseDate
)Column order in a composite index is a leftmost-prefix decision, not cosmetic. This index efficiently serves:
WHERE userId = ?WHERE userId = ? AND category = ?WHERE userId = ? AND category = ? AND expenseDate BETWEEN ? AND ?
It does not efficiently serve a query that skips userId for example, a cross-user aggregate by category. That's intentional. There is no query in the application that scans across all users by category without a user filter. Cross-user analytics belong to a separate code path (SpendGauge has an analyticsSnapshots table for exactly this reason, pre-aggregated rollups that don't tax the hot transactional indexes with cold analytical queries).
The meta-lesson here is actually about Drizzle specifically: when your indexes live in TypeScript next to the columns they cover, a PR that changes a hot query path without touching the index block is a visible gap in the diff. You can see it in code review. With a separate migration file or a GUI tool, that same omission is invisible until a slow query log surfaces it weeks later.
The actual takeaway
None of these decisions came from Drizzle's documentation. They came from one question asked early and repeatedly: what query will I need to run against this column in a year?
Drizzle made two specific things easier. First, schema-as-TypeScript means every index, every cascade policy, every column type is a version-controlled, reviewable artifact, not something buried in a migration file nobody reads after merge. Second, Drizzle's type inference is honest rather than convenient: DECIMAL as string is the accurate type, even though it's the uncomfortable one, and that honesty forces a decision point that a more "helpful" ORM would let you paper over until production.
The ORM doesn't make these decisions. It just makes them impossible to hide.
SpendGauge https://spendgauge.devsip.tech is a personal finance app that I built to give users a daily safe to spend amount across each of their budget catetories. This is article #03 in a series on the technical decisions behind it. Follow me on X: @koomekelvink I discuss matters programming and cloud