Tulip ERP — putting the domain model in the database

The decision that shaped everything

Almost all the business logic lives in PL/pgSQL, not Python. The FastAPI layer is a thin HTTP-to-CALL adapter; most endpoints are a single line:

cursor.execute("call schema_sales.add_sales(%s)", (sale.model_dump_json(),))

Procedures take one jsonb argument and unpack it internally, so partial updates work without building SQL strings. update_customer uses the ? containment operator to tell “field absent” apart from “field explicitly null”, then COALESCE(v_new, existing) in the SET list — a partial update with no dynamic SQL anywhere.

What it cost. Validation errors arrive as a database round-trip instead of a fast 422, and the Pydantic models ended up as little more than shape declarations — which makes the Python layer misleading to read, because it looks like it validates and doesn't. If I were starting again I'd mirror the cheap checks (ranges, enums, required fields) in Pydantic for fast feedback and keep the database as the authority, rather than treating it as either/or.

Three pieces of domain logic I'm glad I got right

1. Bills are snapshots, not live sums

The naive version recomputes a month's bill from current sales every time you look at it. That's wrong: delete a sale from March and March's history silently changes. But freezing too early is also wrong — a sale added after the bill was generated would never get charged.

The resolution is three-way:

CASE
    WHEN bfm.amt    IS NULL  THEN COALESCE(sfm.amt, 0)   -- no bill yet: live total
    WHEN bfm.status = 'paid' THEN bfm.amt                -- paid: frozen for good
    ELSE GREATEST(bfm.amt, COALESCE(sfm.amt, 0))         -- unpaid: new sales count,
END                                                      -- deletions never do

GREATEST is the load-bearing word. It makes the number move in one direction only, which is the behaviour the plant owner actually needs.

2. Cash sales must survive a billing reversal

A sale paid in cash at the counter and a sale paid through the monthly bill are both status = 'paid', but they are not the same thing. If the owner reverses a month's billing, the counter sales must not be dragged back to unpaid.

I added a billing_locked flag maintained by two BEFORE triggers: anything inserted or updated to paid gets locked. The billing module, which legitimately needs to override that, sets a transaction-local flag its own trigger checks:

IF current_setting('app.skip_billing_lock', true) = 'on' THEN
    RETURN NEW;
END IF;

So a human clicking “paid” in the UI locks the row permanently; the billing module flipping the same column leaves it reversible. Same column, same trigger, different meaning depending on who is writing — and the distinction is enforced rather than remembered.

3. Audit attribution without touching every UPDATE

Every row records who last changed it. Rather than threading modified_by through a dozen UPDATE statements, each procedure sets a transaction-local GUC once:

PERFORM set_config('app.user_id', v_user_id::text, true);

and a single trigger on customers, sales and bills stamps modified_at and modified_by from it. The true matters — it scopes the setting to the transaction, so it can't leak across a pooled connection into someone else's request.

Dispatch, and the one place atomicity is load-bearing

The daily dispatch writes a lock row plus one sale per customer — about forty inserts. If insert thirty fails validation, a half-dispatched day is worse than no dispatch: the lock says “done”, the sales say otherwise, and nobody can tell which customers were recorded.

One transaction per HTTP request, opened by a generator dependency that commits only after the handler returns:

def GET_DB():
    connection = connect(**DB_CONFIG)
    cursor = connection.cursor()
    yield cursor
    connection.commit()

A RAISE EXCEPTION on any sale rolls back the lock row with it. The day stays unlocked and the operator retries cleanly.

Elsewhere I leaned on constraints rather than locks. send_bills_for_month uses INSERT ... ON CONFLICT DO NOTHING RETURNING bill_id inside a CTE and counts the rows returned, so “send all bills” is safely re-clickable and honestly reports how many were actually new. The uniqueness guarantee is UNIQUE (cust_id, bill_month, bill_year) — the database refuses the duplicate, so the application never has to check for one.

What I'd change

Writing this up made the gaps obvious, and they're more interesting than the parts that went well.

Indexing, and the reason indexes wouldn't have helped

There isn't a single CREATE INDEX in the migration set. Worse, adding one wouldn't have fixed much, because every hot predicate is non-sargable — created_at::date = now()::date, EXTRACT(MONTH FROM created_at) = p_month, date_trunc('month', created_at). Wrapping the column in a function means Postgres can't use an index on it at all, so every stats and billing read is a sequential scan. The fix is to rewrite them as half-open ranges (created_at >= $1 AND created_at < $1 + interval '1 month') and then index (created_at) and (cust_id, created_at). At a hundred customers the plant will never notice; at ten thousand rows a month it would be the first thing to fall over. I learned the difference between “has an index” and “can use an index” from this.

The migration set can't rebuild the database

One SQL file is entirely commented out while the API still calls the functions it defines, and the sales table DDL is truncated mid-statement. The functions are live in production because I created them there, so everything works — but a clean rebuild from the repo produces an app that 500s. For a project whose whole premise is “the logic lives in Postgres”, that's the most serious structural flaw in it: the logic isn't actually in version control, it's in a server. Numbered, idempotent, tested-from-scratch migrations are the fix, and it's the first thing I'd do differently.

Dispatch is N+1 by design

Forty customers means forty CALL round trips. Passing a jsonb array and looping server-side would make it one.

Concurrency has a real hole

The “already dispatched today” check is a separate request from the dispatch itself, and the dispatch log has no unique constraint on the date — so two tabs, or one fast double-click, dispatches twice. The guard is client-side UI state, which is not a guard. A unique index on (dispatched_at::date) closes it in one line. It's a textbook time-of-check / time-of-use race and I shipped it without seeing it.

Stack