Building SpendGauge #02: How I Designed a PostgreSQL Database for a Financial App — Every Decision, Every Tradeoff

Series: SpendGauge — Building a Production SaaS From Frontend to Kubernetes Article: 02 of 30 Stack: PostgreSQL · Drizzle ORM · TypeScript · Node.js

---

Designing a database is one of those things that feels simple until you're three months into production and a decision you made on day one is now causing you pain at 2 AM.

I've been there. So when I sat down to design the schema for SpendGauge — a personal finance tracker with multi-account collaboration, goal tracking, subscriptions, and AI-powered insights — I wanted to be deliberate about every call I made. Not perfect. Deliberate.

This is article two in the SpendGauge build series. In the immediate link above I explained what we're building and why. Here, I'm taking you through the full PostgreSQL schema: 24 tables, 80+ columns, and the thinking behind each major decision. I'll also be honest about the shortcuts I took and where I know I'll have to revisit things later.

The code is real. Every decision was made under real constraints. Let's walk through it together.

---

The Full Picture — What We're Working With

Before diving deep, here's how the schema is organized. I grouped everything into five areas based on what each part of the app does:

Domain	Tables	What It Handles
User Management	users, user_preferences	Who you are, your settings, your login state
Financial Tracking	expenses, budgets, recurring_expenses, goals, income_sources, income_history, safe_to_spend_cache	The actual money stuff
Collaboration	accounts, account_members	Sharing finances with other people
Analytics & Audit	analytics_snapshots, import_history, audit_logs	Historical data, what changed and when
Monetization	subscription_plans, user_subscriptions, payment_history, usage_tracking, feature_flags, promo_codes, promo_code_redemptions, referrals	The business side — payments, limits, promotions

That's 24 tables. Every single one earns its place, and I'll show you why.

---

Part 1: The Users Table and a Decision About Income

The users table looked simple on the surface. But there's one column on it that might raise eyebrows — total_monthly_income:

totalMonthlyIncome: decimal("total_monthly_income", {
  precision: 12,
  scale: 2,
})
  .notNull()
  .default("0"),

incomeLastRecalculated: timestamp("income_last_recalculated", {
  withTimezone: true,
}),

We already have a separate income_sources table that tracks every income source a user has. So why store the total on the users table as well? Isn't that redundant?

Yes. Intentionally.

Here's the core feature of SpendGauge: every time you open the app, it tells you exactly how much you can safely spend today. That calculation looks like this:

safe_to_spend_today = (monthly_income - total_spent_this_month - upcoming_recurring) / days_remaining

If monthly_income had to be calculated fresh on every page load by adding up all your income sources, the database would run that addition thousands of times a day and for a number that almost never changes that's wasteful. Storing the total on the users table means one fast read. The income_last_recalculated timestamp tells us when we last refreshed that number, so we know when to update it.

This is called denormalization — deliberately storing the same information in more than one place to make reads faster. Normalization (keeping data in one place only) is generally a best practice, but like most best practices, it has exceptions when a value is read constantly but written rarely, denormalization toward the read pattern is a performance win, not a design flaw.

Auth Tokens on the Users Table — A Conscious Shortcut

The password reset tokens and email verification tokens live directly on the users table instead of a separate auth_tokens table:

emailVerifyToken:   varchar("email_verify_token", { length: 64 }),
emailVerifyExpires: timestamp("email_verify_expires"),
passwordResetToken:  varchar("password_reset_token",  { length: 64 }),
passwordResetExpiry: timestamp("password_reset_expiry"),

A dedicated auth_tokens table would be cleaner in a world where users might have multiple active tokens at once — say, if you were sending magic login links to multiple devices simultaneously. But right now, SpendGauge only supports one active reset or verification at a time, there's no multi-token flow therefore creating a whole new table for that adds a database join to every auth check and buys flexibility we don't need yet.

This is something I will revisit if multi-device auth or magic links become a requirement. The trigger to change: if we ever need more than one active token per user per action.

The key discipline here is to name the decision and say out loud as to when you'd change it for purposes of rememberance.

---

Part 2: Never Store Money as a Float — Ever

Before foreign keys, before indexes, before anything else — let's talk about the most important data type decision in any financial application.

Do not store money as a floating-point number.

This isn't a preference. It's a rule. The reason is how computers store decimal numbers internally. The standard format (called IEEE 754 floating-point) can't represent numbers like 0.1 or 0.2 exactly. The classic example:

SELECT 0.1 + 0.2;
-- In most languages: 0.30000000000000004

That's a real result. In a banking or finance context, that's real money disappearing or appearing out of nowhere when you add things up its not acceptable.

PostgreSQL has a data type called NUMERIC (also called DECIMAL) that stores exact numbers with no rounding errors. Every single monetary column in SpendGauge uses it:

decimal("amount", { precision: 12, scale: 2 })

• precision: 12 means we can store up to 12 significant digits — numbers up to $9,999,999,999.99.
• scale: 2 means two decimal places — cents.

One thing that surprises developers when they first use Drizzle ORM with PostgreSQL DECIMAL: it comes back as a string in TypeScript, not a number.

const expense = await db.query.expenses.findFirst(...);
console.log(typeof expense.amount); // "string"

This is correct. It's Drizzle and PostgreSQL protecting you from JavaScript's imprecise number handling. When you need to do math with these values, use a library built for exact decimals — decimal.js or big.js:

import Decimal from "decimal.js";

const safeToSpend = new Decimal(user.totalMonthlyIncome)
  .minus(totalSpent)
  .minus(upcomingRecurring)
  .dividedBy(daysRemaining)
  .toDecimalPlaces(2);

Never use JavaScript's native Number for financial math. That's how you get penny errors at scale.

---

Part 3: Foreign Keys — Three Different Delete Strategies

A foreign key is a column in one table that points to a row in another table, for example, an expense's user_id column points to a user in the users table. Foreign keys enforce that you can't have an expense that doesn't belong to a real user.

The interesting question is: what happens when you delete the parent? If you delete a user, what happens to all their expenses? PostgreSQL gives you several options through the ON DELETE setting. I used three different strategies across the schema, and each one is a deliberate choice.

Strategy 1: CASCADE — Delete the children too

// expenses.ts
userId: uuid("user_id")
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),

CASCADE means: when a user is deleted, automatically delete all their expenses too.

This makes sense for expenses, budgets, goals, recurring expenses, and anything else that is meaningless without the user it belongs to. If someone deletes their account (or submits a GDPR data erasure request — a legal right in many countries requiring companies to delete all your personal data on request), there's no reason to keep their financial records. The cascade makes the deletion clean and automatic.

I use this for: expenses, budgets, goals, recurring_expenses, income_sources, notifications, tags, usage_tracking, account_members.

One thing to watch: cascade deletes run inside a single database transaction. If a user has 50,000 expenses, deleting their account deletes all 50,000 rows at once. At scale, this gets slow and can lock up the database. When SpendGauge reaches high user counts, I'll need to move to a soft-delete and async cleanup strategy instead of hard cascades. For now, cascade is clean and sufficient.

Strategy 2: SET NULL — Keep the record, remove the link

// expenses.ts
accountId: uuid("account_id").references(() => accounts.id, {
  onDelete: "set null",
}),

An expense can belong to a shared account — say, a household budget you share with a partner. But if that shared account gets deleted, the expense itself shouldn't disappear. It's still your expense. It just goes back to being a personal one (with accountId set to null).

SET NULL keeps the child record alive but removes the reference to the parent that no longer exists.

I also use this for audit_logs.userId:

userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),

If a user is deleted, their audit trail stays — but the reference to the user gets nulled out. This is a compliance decision. An audit log (a record of what happened, when, and from which IP address) needs to survive even after the user account is gone. You keep the event; you lose the actor. That's acceptable for security and compliance purposes.

Strategy 3: No ON DELETE — Let the database protect you

// user_subscriptions.ts
planId: uuid("plan_id")
  .notNull()
  .references(() => subscriptionPlans.id),
// No onDelete specified — defaults to RESTRICT

When you don't specify ON DELETE, PostgreSQL defaults to RESTRICT — which means it will throw an error if you try to delete a plan that has active subscribers.

This is intentional. You should never be able to delete a subscription plan while people are still paying for it. The error isn't a bug — it's the database refusing to let you make a catastrophic mistake. The correct process is to mark the plan as inactive first and migrate subscribers to another plan. Letting PostgreSQL enforce this prevents the scenario where someone accidentally orphans 500 paying subscribers by deleting a plan from an admin dashboard.

---

Part 4: Why I Used Integers for Booleans

Throughout the schema I did things similar to:

isActive: integer("is_active").default(1), // 0 = false, 1 = true
canAddExpenses: integer("can_add_expenses").default(1),
emailVerified: integer("email_verified").default(0).notNull(),

A question you might ask is PostgreSQL has a proper BOOLEAN type that stores true and false. So why use INTEGER with 0 and 1? Honest answer: cross-system compatibility.

SpendGauge will eventually send and receive data from external systems — Stripe/Paystack webhooks, bank feeds, third-party APIs. Not all of these speak PostgreSQL booleans as some send "true" as a string, some use 1/0. Some use yes/no thus handling incoming data from multiple sources, having our database consistently use 0 and 1 makes the contract predictable.

There's a real tradeoff though as we lose the clean WHERE is_active = true syntax in favor of WHERE is_active = 1, on a purely modern stack talking only to itself, native BOOLEAN is cleaner but, when third-party integrations are a day-one reality, INTEGER with a documented convention is defensible.

If you go this route, add a check constraint to the database to prevent nonsense values:

ALTER TABLE expenses ADD CONSTRAINT chk_is_recurring CHECK (is_recurring IN (0, 1));

A check constraint is a rule at the database level that rejects any row that doesn't meet a condition as without this constraint, nothing stops someone from accidentally writing is_active = 42 to the database. With it, PostgreSQL will reject that insert immediately.

Drizzle doesn't expose check constraints through its schema builder in all versions, so this lives in a raw SQL migration file rather than the TypeScript schema definition. Either way, don't skip it.

---

Part 5: Accounts and Permissions — Why Roles Aren't Enough

SpendGauge supports shared accounts that is multiple people tracking finances together. The collaboration model separates two things that are easy to confuse: who owns the account and what each member can do.

// accounts.ts — the account itself
ownerId: uuid("owner_id")
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),

// account_members.ts — what each member is allowed to do
role: accountRoleEnum("role").notNull().default("member"),
canAddExpenses: integer("can_add_expenses").default(1),
canEditExpenses: integer("can_edit_expenses").default(1),
canDeleteExpenses: integer("can_delete_expenses").default(0),
canManageBudgets: integer("can_manage_budgets").default(1),
canManageMembers: integer("can_manage_members").default(0),
canViewReports: integer("can_view_reports").default(1),

The accounts table has an ownerId — the one person who can ultimately delete the account. The account_members table has a permission matrix for every member.

Why not just use the role column (owner, admin, member, viewer) to determine what people can do?

Roles are shortcuts since they map a label to a predefined set of permissions but the real world, you might want your teenager to see the household budget but not be able to delete transactions. You might want a read-only collaborator who can generate reports but can't touch anything else. A simple role system can't express this without creating an ever-growing list of role types.

Explicit permission columns give you per-user, per-action control without that role sprawl.

The role enum still exists — but only for display purposes (showing "Admin" or "Member" in the UI) and for quick routing in the application layer. The actual permission check always reads from the explicit column, never from the role label. The role is a badge. The column is the truth.

The most important index in this part of the schema:

accountUserIndex: index("idx_account_members_account_user").on(
  table.accountId,
  table.userId
),

An index in a database is like the index at the back of a book — instead of reading every page to find what you're looking for, the database jumps directly to the right location. Every time the app checks "does this user have permission to do this in this account?", it runs a lookup on accountId + userId together. The composite index on those two columns makes that lookup instant instead of scanning the entire table.

---

Part 6: Tracking Income — Three Layers for Three Problems

The income model has three layers, each solving a different problem.

Layer 1: income_sources — the current truth. Each row is one income source: salary, freelance work, rental income, side project. It stores the current amount and how often it pays out (monthly, weekly, etc.).

Layer 2: income_history — an append-only audit log of every change to total monthly income. Each row records a snapshot: what the total was, what changed, and the period it was active (effectiveFrom / effectiveTo, where null means still current).

This lets you answer "what was my total monthly income in March?" without reconstructing a timeline from scratch.

Note: this is an audit log pattern, not SCD Type 2. The source record in income_sources is mutable — history is tracked in a separate table rather than by versioning the source rows themselves.

Layer 3: users.total_monthly_income — the denormalized cache from Part 1.

When income changes, all three layers need to update together. This must happen in a transaction — a database feature that groups multiple operations so they either all succeed or all fail together, with no partial state in between. If the app crashes halfway through the update, the database rolls everything back to where it started.

await db.transaction(async (tx) => {
  // 1. Update the income source
  await tx.update(incomeSources)
    .set({ amount: newAmount, updatedAt: new Date() })
    .where(eq(incomeSources.id, sourceId));

  // 2. Close the current history record
  await tx.update(incomeHistory)
    .set({ effectiveTo: new Date() })
    .where(and(
      eq(incomeHistory.incomeSourceId, sourceId),
      isNull(incomeHistory.effectiveTo)
    ));

  // 3. Insert a new history record
  await tx.insert(incomeHistory).values({
    userId,
    incomeSourceId: sourceId,
    totalMonthlyIncome: newTotal,
    changeType: "amount_increased",
    changeAmount: newAmount.minus(oldAmount).toString(),
    effectiveFrom: new Date(),
    effectiveTo: null,
  });

  // 4. Recalculate and update the cached total on the user
  const newMonthlyTotal = await calculateTotalMonthlyIncome(tx, userId);

  await tx.update(users)
    .set({
      totalMonthlyIncome: newMonthlyTotal.toString(),
      incomeLastRecalculated: new Date(),
    })
    .where(eq(users.id, userId));

  // 5. Invalidate the safe-to-spend cache for this user
  await tx.delete(safeToSpendCache)
    .where(eq(safeToSpendCache.userId, userId));
});

Notice the last step: we delete the safe_to_spend_cache row rather than trying to update it. The cache will be rebuilt the next time the user opens the app. Deleting is simpler and safer — if the rebuild fails, the app returns a cache miss and recalculates on the spot. You never serve stale data.

---

Part 7: The Safe-to-Spend Cache — Why Not a Materialized View?

When I first designed this, someone asked: why not use a PostgreSQL materialized view?

A materialized view is a feature in PostgreSQL where you write a complex query (with joins, calculations, aggregations), and PostgreSQL stores the result as if it were a table. You refresh it when the underlying data changes, and reads are fast because the result is pre-computed.

Sounds perfect for safe-to-spend, right?

The problem is this: REFRESH MATERIALIZED VIEW refreshes the entire view. You can't refresh just one user's row. At 100,000 users, a refresh touches millions of rows when only one user's income changed. That's wildly inefficient.

The explicit cache table gives us surgical control — we delete exactly one row for exactly one user. The cache rebuilds on demand, per user, with fresh numbers.

The calculation_version column is a nice detail worth explaining:

calculationVersion: integer("calculation_version").notNull().default(1),

When the safe-to-spend formula changes — say we add a new factor to the calculation, or fix a rounding bug — we increment the expected version number in the application code. When the app reads a cache row, it compares the stored version to the current expected version. If they don't match, it treats the cache as stale and recalculates. This means formula updates roll out gracefully without needing to delete every user's cache at once.

---

Part 8: Drizzle ORM — Type-Safe Queries Without the Pain

Drizzle's relational query API is one of the cleanest parts of working on this stack. You define how tables relate to each other once, then query with with to load related data:

// Load a user's dashboard data in one go
const dashboardData = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    expenses: {
      where: and(
        gte(expenses.expenseDate, startOfMonth),
        lte(expenses.expenseDate, endOfMonth)
      ),
      orderBy: [desc(expenses.expenseDate)],
      limit: 50,
    },
    budgets: {
      where: eq(budgets.isActive, 1),
    },
    goals: {
      where: eq(goals.status, "active"),
    },
  },
});

Drizzle runs this efficiently under the hood and returns a TypeScript object with the exact shape you'd expect — fully typed, no any anywhere.

The N+1 Problem — and Why It Matters

The N+1 problem is one of the most common performance mistakes in apps that talk to databases. Here's what it looks like:

// WRONG — this is N+1
const users = await db.select().from(users).limit(100); // 1 query
for (const user of users) {
  const expenses = await db.select()
    .from(expenses)
    .where(eq(expenses.userId, user.id)); // 1 query PER user = 100 queries
}
// Total: 101 queries to load 100 users with their expenses

You fetched 100 users, and then made 100 more queries — one per user. That's 101 round trips to the database. As the list grows, so does the damage. 1,000 users = 1,001 queries. This is N+1: one initial query, then N more.

Drizzle's relational API solves this in 2 queries regardless of how many users you're loading:

// CORRECT — 2 queries total
const usersWithExpenses = await db.query.users.findMany({
  limit: 100,
  with: { expenses: true },
});

One query for the users, one query to fetch all related expenses at once. Done.

Aggregations — When You Need to Count and Sum

The relational API is excellent for loading data shapes, but for analytics — summing, averaging, grouping — you drop down to the SQL builder:

// Monthly spending broken down by category
const categoryBreakdown = await db
  .select({
    category: expenses.category,
    total: sql<string>`SUM(${expenses.amount})`,
    count: sql<number>`COUNT(*)`,
    avgAmount: sql<string>`AVG(${expenses.amount})`,
  })
  .from(expenses)
  .where(
    and(
      eq(expenses.userId, userId),
      gte(expenses.expenseDate, startOfMonth),
      lte(expenses.expenseDate, endOfMonth)
    )
  )
  .groupBy(expenses.category)
  .orderBy(desc(sql`SUM(${expenses.amount})`));

The sql<string> tag lets you write raw SQL expressions inside Drizzle while keeping the TypeScript type inference intact. SUM() returns a string from PostgreSQL (because of numeric precision), so we type it as sql<string> and handle the conversion downstream.

Getting Budget Utilization Right With LEFT JOIN

The budget dashboard needs to show each budget alongside how much has been spent against it this month. This requires joining the budgets table with the expenses table:

const budgetUtilization = await db
  .select({
    budgetId: budgets.id,
    category: budgets.category,
    name: budgets.name,
    monthlyLimit: budgets.monthlyLimit,
    alertThreshold: budgets.alertThreshold,
    totalSpent: sql<string>`COALESCE(SUM(${expenses.amount}), '0')`,
    transactionCount: sql<number>`COUNT(${expenses.id})`,
  })
  .from(budgets)
  .leftJoin(
    expenses,
    and(
      eq(expenses.userId, budgets.userId),
      eq(expenses.category, budgets.category),
      gte(expenses.expenseDate, startOfMonth),
      lte(expenses.expenseDate, endOfMonth)
    )
  )
  .where(
    and(
      eq(budgets.userId, userId),
      eq(budgets.isActive, 1)
    )
  )
  .groupBy(
    budgets.id,
    budgets.category,
    budgets.name,
    budgets.monthlyLimit,
    budgets.alertThreshold
  );

The LEFT JOIN is important here. A left join keeps all rows from the left table (budgets) even if there are no matching rows in the right table (expenses). Without it — using an inner join, which only returns rows that match on both sides — budgets with zero spending this month would silently disappear from the results. The COALESCE handles the null case: if there are no expenses, it returns '0' instead of null.

---

Part 9: Indexing — Every Index Has a Cost

Indexes make reads faster but writes slower. Every INSERT, UPDATE, and DELETE must update all the indexes on that table. So the discipline isn't "index everything" — it's "index the right things."

Here's how I approached it:

Index every foreign key column

userIdIndex: index("idx_expenses_user_id").on(table.userId),
accountIdIndex: index("idx_expenses_account_id").on(table.accountId),

Every query that reads expenses filters by userId. Without this index, a user with 10,000 expenses causes a full table scan — PostgreSQL reads every single row to find the matching ones. That's unacceptable.

Unlike MySQL, PostgreSQL does not automatically create indexes on foreign key columns. You have to add them yourself.

Composite indexes for hot query paths

// "Give me this user's expenses in this category this month"
userCategoryDateIndex: index("idx_expenses_user_category_date").on(
  table.userId,
  table.category,
  table.expenseDate
),

A composite index covers multiple columns. The column order matters: PostgreSQL can use this index for queries filtering on userId alone, userId + category, or userId + category + expenseDate. It can't use it for queries filtering on category alone without also filtering on userId first. Put the most commonly filtered column first.

Partial indexes for active-record queries

A partial index only indexes rows that meet a condition:

CREATE INDEX idx_budgets_active_user
ON budgets (user_id, category)
WHERE is_active = 1;

If most of your queries filter by is_active = 1 (which ours do — you're almost always looking at active budgets), this index is both smaller and more efficient than a full index on the same columns. Smaller index = less storage, faster maintenance, faster queries.

Drizzle doesn't expose partial index creation through its schema builder yet, so this lives in a raw SQL migration file.

Don't index low-cardinality columns alone

A column with only two possible values — like is_active with 0 or 1 — is a poor candidate for a standalone index. If 95% of rows have is_active = 1, a query for WHERE is_active = 1 would have to read almost the entire table anyway. The database query planner would often ignore the index entirely and just scan the table. Save the index overhead for columns that actually narrow things down significantly.

---

Part 10: Stripe and Subscriptions — Reference, Don't Couple

The payments schema is designed around one principle: Stripe is an external system you talk to, not one you tie your database to.

// user_subscriptions.ts
stripeCustomerId: varchar("stripe_customer_id", { length: 255 }),
stripeSubscriptionId: varchar("stripe_subscription_id", { length: 255 }).unique(),
stripeCurrentPeriodStart: timestamp("stripe_current_period_start", ...),
stripeCurrentPeriodEnd: timestamp("stripe_current_period_end", ...),
stripeCancelAt: timestamp("stripe_cancel_at", ...),

The Stripe IDs are stored as plain text columns — not as foreign keys to a "Stripe table" in our database, because Stripe doesn't live in our database. These are lookup keys: when you need to call Stripe's API, you use them to find the right subscription or customer on Stripe's side.

The critical architectural decision: user_subscriptions is the source of truth for access control, not Stripe. When Stripe sends a webhook (an automated notification to your server saying "this payment succeeded" or "this subscription was canceled"), our app updates user_subscriptions to reflect the new state. Authorization checks read from our database — fast, local, reliable. They never call Stripe's API in real time.

This means:

• If Stripe has a brief outage, our app keeps working
• Permission checks are fast database reads, not external API calls
• We can reason about subscription state without depending on Stripe's uptime

The status column is the gatekeeper:

status: subscriptionStatusEnum("status").notNull().default("active"),
// "active" | "trialing" | "past_due" | "canceled" | "unpaid" | "paused" | "expired"

Notice that past_due doesn't grant access. If a payment fails, the user loses access once the grace period ends. This is a business rule encoded directly in the query — not a comment somewhere, not an application config. It lives in the database logic:

async function canAccessFeature(userId: string, feature: PremiumFeature): Promise<boolean> {
  const subscription = await db.query.userSubscriptions.findFirst({
    where: and(
      eq(userSubscriptions.userId, userId),
      inArray(userSubscriptions.status, ["active", "trialing"])
    ),
    with: { plan: true },
  });

  if (!subscription) return false;
  return subscription.plan[feature] === 1;
}

Usage Tracking as a First-Class Table

usage_tracking is often the thing teams bolt on as an afterthought. I made it a proper table from day one:

expensesCount: integer("expenses_count").default(0),
budgetsCount: integer("budgets_count").default(0),
hasReachedExpenseLimit: integer("has_reached_expense_limit").default(0),
hasReachedBudgetLimit: integer("has_reached_budget_limit").default(0),

The has_reached_*_limit flags are set by a background job that periodically compares counts against plan limits. On every new expense creation, the check becomes a single row read:

const usage = await db.query.usageTracking.findFirst({
  where: eq(usageTracking.userId, userId),
});

if (usage?.hasReachedExpenseLimit) {
  throw new AppError("USAGE_LIMIT_REACHED", "Upgrade to add more expenses");
}

Without this, enforcing limits would require a COUNT(*) query on the expenses table on every single write. That's an expensive read for a guard that fires rarely. The denormalized flag makes limit checks near-instant.

---

Part 11: Enums — When to Use Them, When to Avoid Them

PostgreSQL enums let you define a set of allowed values for a column, with the database enforcing that only those values can be stored:

export const categoryEnum = pgEnum("category", [
  "food", "transport", "entertainment", "bills",
  "health", "utilities", "shopping", "education",
  "housing", "insurance", "savings", "investments",
  "debt", "gifts", "personal", "other",
]);

The advantage is defense in depth — even if your application has a bug that tries to insert category = "fun", PostgreSQL rejects it at the database level.

The disadvantage: adding new values requires running a database migration (ALTER TYPE category ADD VALUE 'new_category'). And in PostgreSQL, you can add values to an enum but you can't remove them or reorder them without dropping and recreating the type entirely. So enums come with a migration cost baked in.

My rule: enums for small, stable value sets that won't change often (status, role, tier). VARCHAR for anything likely to grow or be user-configurable.

The periodType in analytics_snapshots is a VARCHAR because I fully expect to add quarterly and yearly options later:

periodType: varchar("period_type", { length: 20 }).notNull(), // 'daily', 'weekly', 'monthly'

The tradeoff is losing database-level validation. I accept that here because the analytics system validates input before inserting it.

---

Part 12: Audit Logs — Making Immutability Obvious

The audit_logs table has no updated_at column. This is intentional:

export const auditLogs = pgTable("audit_logs", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
  eventType: auditEventTypeEnum("event_type").notNull(),
  createdAt: timestamp("created_at", ...).notNull().defaultNow(),
  // No updatedAt — audit logs are write-once records
});

Audit logs are the system's memory of what happened. They must never be modified. The absence of updatedAt is a signal: if you find yourself writing db.update(auditLogs), your design is wrong.

You can reinforce this at the database level with a trigger — a database function that automatically runs when someone tries to update or delete a row:

CREATE OR REPLACE FUNCTION prevent_audit_update()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Audit log records are immutable';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_logs_immutable
BEFORE UPDATE OR DELETE ON audit_logs
FOR EACH ROW EXECUTE FUNCTION prevent_audit_update();

Now even if application code tries to modify an audit log, PostgreSQL will throw an exception and refuse.

The changes column uses JSONB to store before/after state for update events:

changes: jsonb("changes"),
// Example:
// {
//   "before": { "amount": "25.00", "category": "food" },
//   "after": { "amount": "30.00", "category": "food" }
// }

JSONB (Binary JSON) is PostgreSQL's more powerful JSON storage type. Unlike plain JSON (which stores data as text), JSONB stores data in a decomposed binary format that enables advanced queries and indexing. If you later need to query "find all audit logs where the amount changed to over $100," JSONB makes that possible with operator queries. Plain JSON would force you to read every row and parse the text yourself.

---

Part 13: Migrations — Schema as Code

Every schema change in SpendGauge goes through a Drizzle migration:

npx drizzle-kit generate:pg  # Generates a migration file from schema changes
npx drizzle-kit push:pg      # Applies the migration to the database

The generated migration files are plain SQL, committed to version control alongside the application code. This means:

• Every database state in history is reproducible
• Schema changes go through code review just like any other change
• The CI/CD pipeline applies migrations before deploying new application code
• Rollbacks are explicit — you write a new migration that undoes the change

The rule I follow strictly: never edit a migration file after it has been applied to any environment. If something needs to change, write a new migration. The migration history is append-only — like the audit log, it's the record of how the database arrived at its current state.

---

The Full Decision Record — A Quick Reference

Here's every major decision summarized in one place:

Decision	Choice Made	Why
Money storage	DECIMAL(12,2)	Exact arithmetic — floating point is wrong for financial data
Boolean representation	INTEGER (0/1)	Cross-system compatibility with external integrations
Soft deletes	is_active INTEGER	Preserves data for recovery; keeps referential integrity intact
Income total on users	Denormalized cache	Hot read path; income changes rarely
Safe-to-spend	Explicit cache table	Per-user surgical invalidation; versioned formula support
Auth tokens	On users table	Single-slot tokens don't need their own table yet
Permissions	Explicit columns + role label	Role for display; columns for actual authorization
Stripe IDs	VARCHAR reference	External system — reference it, don't couple to it
Enums	Native pgEnum for stable sets	Database validation; VARCHAR for extensible sets
Audit logs	No updatedAt	Immutability as a schema-level contract
Index strategy	FK indexes + composite on hot paths	Write overhead justified by read performance gains
Cascade strategy	Three tiers by relationship semantics	Data lifecycle matches business logic

---

What's Coming Next

Article 03 covers why i chose Drizzle ORM and what is my take to anyone choosing a Typescript ORM in 2026.

---

SpendGauge is being built in public, article by article. The full schema code is available in the repository. If you disagree with a decision I made — or you've solved the same problem differently — I'd genuinely like to hear it on Am also open for web projects please message me ->my x handle .

---

Tags: postgresql database-design drizzle-orm typescript schema-design financial-applications foreign-keys database-indexing saas-architecture backend-engineering nodejs sql