Multi-Tenant Transaction Ledger

A transaction ledger where many organisations share one database and one schema, and no organisation can ever see another's data.

The interesting problem here isn't CRUD — it's tenant isolation, enforced in four independent layers so that no single mistake can leak data across a boundary.

Stack: Flask · PostgreSQL · SQLAlchemy · React

---

What it does

Organisations ("tenants") sign up, invite team members, and record financial transactions. Every user belongs to exactly one tenant, and every query is confined to that tenant automatically.

• Tenants — an isolation boundary, created on registration along with its first owner
• Users — scoped to a tenant, with owner / admin / member roles
• Transactions — amount, category, description, and when the money actually moved, with server-side filtering, sorting, and pagination

---

Tenant isolation

Shared database, shared schema, tenant_id discriminator column. Database-per-tenant and schema-per-tenant both multiply migrations and connection pools, and neither is demonstrable on a free hosting tier; shared-schema is also what most production SaaS runs.

The whole design rests on not trusting any single enforcement point:

1. Tenant identity comes only from the JWT. It's a signed claim, lifted onto the request context. It is never read from a body, query string, or client header — an endpoint that accepts a client-supplied tenant_id isn't multi-tenant at all.

2. Query scoping is automatic, not remembered. A session-level SQLAlchemy listener injects the tenant predicate into every SELECT, including eager-loaded relationships. Hand-written .filter_by(tenant_id=...) fails the one time someone forgets, and that failure is a data breach.

3. Postgres row-level security covers writes. Layer 2 filters reads but won't stop an INSERT carrying the wrong tenant_id. RLS policies with USING and WITH CHECK close that, and they apply even to a bug in the Python. The app connects as a role that is neither superuser nor BYPASSRLS, so the policies genuinely bind it; an unset tenant fails closed to zero rows rather than erroring open.

4. Tests prove it. Cross-tenant reads, updates, and deletes are asserted to return 404 — never 403, because a 403 confirms the record exists in someone else's tenant and turns the API into an existence oracle.

---

API

POST   /api/auth/register        creates tenant + owner atomically
POST   /api/auth/login           → access + refresh tokens
POST   /api/auth/refresh
GET    /api/me

GET    /api/transactions         ?category=&q=&page=&limit=&sort=-occurred_at
POST   /api/transactions         → 201 + Location
GET    /api/transactions/{id}
PATCH  /api/transactions/{id}
DELETE /api/transactions/{id}    → 204

GET    /api/users                tenant members          (admin+)
POST   /api/users/invite         (admin+)
PATCH  /api/users/{id}/role      (owner only)

GET    /api/categories
GET    /api/health

Interactive OpenAPI docs at /api/docs.

Conventions. Lists return an envelope — {items, total, page, limit} — rather than a bare array, so pagination is real on both sides. Updates are PATCH, since they're partial. Every failure uses one error shape, so the frontend has a single error path:

{ "error": { "code": "validation_error",
             "message": "Request body is invalid",
             "details": [ { "field": "amount", "issue": "must be positive" } ] } }

---

Schema notes

A few choices that differ from the obvious defaults:

• UUID primary keys. Sequential integers leak row counts and invite enumeration across tenant boundaries.
• email is unique per tenant, not globally. The same person can belong to two organisations; a global unique index here is hard to undo once there's data.
• amount is NUMERIC(12,2), never float. Binary floating point can't represent 0.10 exactly, and the error compounds under SUM().
• occurred_at is distinct from created_at. When the money moved is user data; when the row was written is system data.
• Every index leads with tenant_id, because every query filters on it.

---

Running it

Requires Docker and Python 3.11+.

cd backend
docker compose up -d                 # Postgres, with app_user provisioned

python3.11 -m venv .venv
.venv/bin/pip install -r requirements.txt

cp .env.example .env
MIGRATION_DATABASE_URL=postgresql://postgres:postgres@localhost:5432/ledger \
  .venv/bin/python -m flask db upgrade

FLASK_APP=wsgi:app .venv/bin/python wsgi.py

The API is at http://localhost:5000, docs at /api/docs.

Two database roles by design: migrations run as the schema owner, while the app connects as app_user, which cannot alter schema and cannot bypass row-level security. That split is what makes layer 3 above meaningful.

cd frontend
npm install
npm run dev                          # http://localhost:5173

Tests

cd backend && .venv/bin/python -m pytest

The suite runs against real Postgres rather than SQLite, because row-level security has no SQLite equivalent — testing isolation against a database that can't enforce it would prove nothing.

---

Status

Built in phases; the isolation layer is the milestone, and everything after it is application surface.

	Phase	State
1	App factory, config, OpenAPI, error envelope, health	Done
2	Models, constraints, migrations	Done
3	Registration, login, refresh tokens	Planned
4	Isolation: query scoping + RLS policies	Planned
5	Isolation test suite	Planned
6	Transactions CRUD	Planned
7	Roles and invitations	Planned
8	OpenAPI polish	Planned
9	React frontend	Planned
10	Deployment	Planned

Endpoints above beyond /api/health describe the target surface and are not yet implemented. The frontend is currently a Vite scaffold.