Chart of Accounts

Money is never stored as a floating-point number. Every monetary column is an integer count of cents, and the conversion to dollars happens once, at the boundary where a value is rendered or handed to the model.

Two integer widths are in use, and the split is not arbitrary. Bank-side amounts are bigint; payroll-side amounts are 32-bit integer. The bank columns are the ones that accumulate an account balance and must tolerate an arbitrary transaction size; a single payroll line item has a natural ceiling.

Direction, not sign

The upstream bank API signs its amounts: a payment out arrives as a negative number. The sync layer splits that single signed value into an unsigned magnitude and a direction discriminator, so that summing outflows never requires an ABS() and a category rollup cannot silently cancel itself out.

// calculateDirection() — exported from src/server/sync/transactions.ts
calculateDirection(100)     → 'inflow'
calculateDirection(50000)   → 'inflow'
calculateDirection(-100)    → 'outflow'
calculateDirection(-50000)  → 'outflow'
calculateDirection(0)       → 'inflow'   // zero is classified as inflow

// and the magnitude is stored unsigned:
// upstream amount -15000  →  amount = 15000, direction = 'outflow'

The zero case is deliberate and carries its own test, titled “should return ‘inflow’ for zero (edge case)”. A fee reversal of exactly zero therefore lands on the inflow side of every aggregate.

Formatting at the boundary

src/lib/format.ts holds the presentation rules. Its behaviour is fixed by exact-equality assertions, which makes it one of the few modules whose output can be quoted verbatim.

Source: __tests__/lib/metrics/index.test.ts, “Format Functions for KPIs”.

Every table and every column as at migration 0002, read from drizzle/meta/0002_snapshot.json. Primary keys are all uuid defaulting to gen_random_uuid(); that default is omitted from the drawing for legibility.

Column types in aggregate

Computed from drizzle/meta/0002_snapshot.json. The single numeric column is payroll_line_items.hours_worked — the one quantity in the schema that is genuinely fractional rather than a count of cents.

Categories are seeded, not configured. The seed script writes twelve rows with is_default = true, and the seed test asserts each name and colour, that every type falls in {revenue, expense, transfer}, that every colour matches /^#[0-9a-fA-F]{6}$/, and that every default row carries a non-empty icon.

The same twelve names are the closed vocabulary handed to the classifier. A model reply naming anything outside this list falls back to Other.

Source: __tests__/server/db/seed.test.ts, which reads the live table and compares against a hard-coded expectation array.

Creating a category

createCategory is a server action, not an API route. It validates before it writes and returns a discriminated result rather than throwing.

createCategory({ name: '',    type: 'expense', color: '#6366f1' })
  → { success: false, message: 'Category name is required' }

createCategory({ name: '   ', type: 'expense', color: '#6366f1' })
  → { success: false, message: 'Category name is required' }   // trimmed first

createCategory({ name: 'X',   type: 'invalid', color: '#6366f1' })
  → { success: false, message: 'Invalid category type' }

// accepted types: 'expense' | 'revenue' | 'transfer'
// the Add Category form defaults to type 'expense', colour '#6366f1'

Sources: __tests__/server/actions/settings.test.ts for the messages; __tests__/components/settings/category-settings.test.tsx for the form defaults.

Three migrations, all applied within a single eighteen-hour window. The journal records each with a millisecond timestamp, which makes the sequence in which the system's scope widened unusually legible.

1ALTER TABLE "employees" ADD COLUMN "employment_type" text DEFAULT 'w2_fulltime';
 --> statement-breakpoint
2ALTER TABLE "employees" ADD COLUMN "hourly_rate" integer;

The default value 'w2_fulltime' is the only place in the schema that encodes a US employment classification. Contractor rows are distinguished from it by taking a different value in the same column.

How migrations are generated

drizzle.config.ts points the generator at a single schema module and emits into ./drizzle. Credentials come from .env.local, loaded by dotenv inside the config file itself rather than by the shell.

1import { config } from 'dotenv';
2import { defineConfig } from 'drizzle-kit';
3
4// Load environment variables from .env.local
5config({ path: '.env.local' });
6
7export default defineConfig({
8  schema: './src/server/db/schema.ts',
9  out: './drizzle',
10  dialect: 'postgresql',
11  dbCredentials: {
12    url: process.env.DATABASE_URL!,
13  },
14});

A single schema file, src/server/db/schema.ts, is the sole source of truth for all twelve tables. Every table and type name quoted on this sheet is exported from it.

Reads go through src/lib/queries/ rather than being written inline in page components. Two modules exist: transactions.ts and chat.ts.

getTransactions

The pagination contract is defensive on both ends. Page numbers below one are clamped up; page sizes above one hundred are clamped down. The clamped value — not the requested one — is what reaches the LIMIT clause.

TransactionFilters

search?: string

Free text. Empty and whitespace-only values are accepted without special-casing.

categoryId?: string

Resolved from a human category name by the agent layer before it reaches this point.

direction?

Exactly 'inflow' | 'outflow'.

startDate?: Date
endDate?: Date

Date objects, not strings. Callers passing ISO strings — the agent tool handler, for instance — parse them first.

A where() clause is always issued, even for an empty filter object. There is no branch that skips it.

Conversation queries

Source: __tests__/lib/queries/chat.test.ts, an integration suite that runs against a real database and creates and tears down its own fixtures.