Three city governments in one database with no data leaks: Postgres RLS with NestJS and Next.js

This is a concept case — a technical demo built by Crazy Diamond, with no client and no commercial deadline. It is not a system delivered to a real city government, and we will not present it as one. What it proves is architectural command: multi-tenancy with real isolation, role control and a production deployment.

Why this case exists

It started from a public administration ERP job for multiple city governments, with 28 competing proposals, a client with no history and an undefined scope. The decision was not to bid on the job and to build our own case instead — because the question that blocks this kind of contract is never "can you build CRUD", it is "can you guarantee City A never sees City B's data".

The core technical decision

Multi-tenancy can be implemented with a simple tenant filter in the query. It works — until the day someone writes a new endpoint and forgets the WHERE. The leak happens silently, with no error, no log, and nobody noticing.

Here isolation was built with Row-Level Security in Postgres, per tenant. The policy lives in the database, not in the application. If the code forgets the filter, the database denies it anyway. That is the difference between trusting the discipline of whoever writes the code and having a structural guarantee.

It would make no sense to prove "multi-tenancy" with a filter only in the query. The silent false negative is worse than the visible error.

How isolation was validated

Three test scenarios, executed both against the local development database and against the production Postgres over a pooled connection: a city manager cannot read or write in another tenant; an auditor reads across tenants but writes in none; and a request with no session context fails closed — it does not return everything, it returns nothing.

That last point is the one most often missed. A misconfigured system, with no context, hands back the entire database. This one hands back empty.

Authentication and roles

JWT with a 15-minute access token and a rotating 7-day refresh token, with reuse detection. During development, implementing rotating refresh surfaced a real transaction bug that was found and fixed — exactly the kind of problem that only appears when you implement rotation properly, instead of issuing a new token and hoping.

Three roles enforced through guards: staff (read), manager (read and write within their own tenant only) and auditor (read across tenants, no writes). Above them, a super-admin with a consolidated view.

What was built

A budget module with line items, appropriations and simplified execution across the commitment → verification → payment flow, on an append-only ledger. A tax collection module with taxpayer registration, tax assessment and payment and delinquency status.

A management dashboard with KPIs, charts of collection against budget and delinquency, plus a cross-tenant comparison visible only to the super-admin. And a public transparency portal, read-only, with aggregated data per tenant.

The database runs on six paired migrations — each with its rollback tested, all the way back down to the first. Seeds with three fictional city governments, eight users, six appropriations and six tax assessments.

Front-end architecture and deployment

The frontend uses a BFF architecture: the browser only talks to Next.js, which forwards the cookie to the backend server-side. No CORS, no cross-site cookie, no token exposed in the browser.

The NestJS backend was refactored to run as a serverless function on Vercel, keeping the local and Docker entrypoint working without duplicating logic. All in production on a real subdomain, with a full regression — login, roles, isolation, 404, public portal — validated against the live domain, not just a test environment.

What is not included

HR and payroll, procurement and bidding, document management, and real banking integration are out of scope — they are roadmap, not delivery. The choice was depth across two complete modules instead of surface across six.

Stack

NestJS · Next.js 15 App Router · TypeScript strict · PostgreSQL with Row-Level Security · Neon · Tailwind · shadcn/ui · Recharts · Swagger · Vercel