Moving the "Brain" to the Database: Architecting a Deterministic, Multi-Tenant OS for UK Trades

Kaspars Rancans — Sun, 22 Feb 2026 20:01:18 +0000

The Goal: "Admin-Zero"

Most SaaS tools for tradespeople are just CRUD wrappers with a pretty UI. When building ForgeDash, we threw out the "client-side logic" playbook. We needed a Financial Black Box—a system where the tech disappears, the math is bulletproof, and compliance is a background process.

Here is a breakdown of the "Database-First" architecture we engineered to solve the complexity of UK trade compliance.

1. Deterministic Finance: The "Dumb Frontend" Strategy

In fintech, trusting the client with money is a liability. We isolated 100% of the financial, tax, and business logic inside PostgreSQL RPCs.

The Problem:
UK tax law is a labyrinth of thresholds, Class 4 NICs, and allowance tapers.
The Solution: We implemented a Deterministic Tax Engine directly in PL/pgSQL (fn_calc_uk_tax_25_26). It natively handles edge cases like the Personal Allowance Taper (reducing PA by £1 for every £2 earned >£100k) and auto-calculates multi-plan Student Loans (Plans 1, 2, 4, and Postgrad).

The Result:
Whether a user is on a spotty 4G connection or a desktop, their "Safe-to-Spend" (StP) figure is mathematically identical because it is calculated at the source of truth.

2. The "Ledger Orchestrator" Pattern

Compliance shouldn't be a manual task. We built a centralized trigger function (fn_orchestrate_ledger_update) attached directly to the ledger tables.

Autonomous Recalculation:

Every INSERT, UPDATE, or DELETE on a transaction automatically triggers a top-down refresh of the Organization’s financial "Pots" (Tax, VAT, NI).
The VAT Sentinel: The orchestrator continuously monitors rolling 12-month turnover. As soon as a business approaches the £90,000 UK VAT threshold, the system proactively alerts the user before they breach compliance.

3. Immutable Audit Trails (Cryptographic Chaining)

To ensure "professional-grade" trust, we implemented blockchain-style chaining for the audit logs using pgcrypto.

Tamper-Evidence:
Every record in the audit_logs table is mathematically linked to the previous entry.

The Seal:
A BEFORE INSERT trigger generates a SHA-256 hash of the current payload concatenated with the previous_hash. If a single byte of financial history is manually altered by a DBA or a bug, the chain breaks, providing a verifiable corruption record for accountants.

4. "Vertical Molding" & Dynamic Schema Injection

Mechanics, plumbers, and electricians use different jargon. Instead of managing multiple codebases, we built a Modular Vertical Framework.
JSONB Domain Injection: Industry-specific terminology (e.g., "VRM" vs. "Asset ID") is stored in a vertical_registry. A custom hook, useMoldedUI, fetches this manifest to inject the correct semantics and UI tokens (accent colors, border radii) at runtime.

Feature Slots:
We use a "Slot Pattern" to load vertical-specific modules—like a 2D Vehicle Damage Walkaround for mechanics—while keeping the core application logic industry-agnostic.

5. The "Greasy Finger" UX Standard

We engineered for the workshop floor, not the boardroom.
Touch Targets: We enforce a strict 48px minimum for all interactive elements to accommodate gloved hands.

Visual Precision:
We use Tabular Numerals (tabular-nums) for all financial data. This prevents the "layout jitter" common in React apps when numbers update in real-time.

Privacy:
All PII is encrypted with AES-256 at rest within the database.

The Stack
Frontend: React 19, Tailwind CSS v4, Vite.
Backend: Supabase / PostgreSQL (Heavy PL/pgSQL & Row Level Security).
AI Integration: n8n + Gemini 3.0 (Multimodal RAG for OCR receipt scanning).

ForgeDash is Compliance-as-Code. It is a high-performance co-pilot designed to make the modern trade business "Admin-Zero."

(Open to feedback on the PL/pgSQL approach—let me know your thoughts in the comments!)

Forem: Kaspars Rancans

Moving the "Brain" to the Database: Architecting a Deterministic, Multi-Tenant OS for UK Trades