Multi-Tenant API

Part of the jotive.dev technical portfolio — Backend Engineer · Python · Node.js

Production-grade multi-tenant REST API. Schema-per-tenant isolation in PostgreSQL, JWT with tenant claims, tenant-scoped rate limiting, and admin onboarding flow. Built to demonstrate the architectural patterns required to safely serve multiple businesses from a single application.

---

The Problem

Valentina is founding a B2B SaaS in Colombia that helps logistics companies manage their fleets. She has 3 early customers onboarded manually — each in a separate database instance. Her first customer asked: "what happens if your system leaks my data to another company?" She couldn't answer confidently.

Her current setup: one database per customer, provisioned manually. Each new customer means a new Postgres instance, new credentials, updated connection string in a config file. 3 customers = 3 databases. At 20 customers this breaks operationally. At 100 it's impossible.

The naive fix is a shared table with a tenant_id column and an application-level filter. That fix relies on every single query remembering to filter. One forgotten WHERE clause exposes every customer's data. That's not a fix — that's risk.

The real fix is schema-per-tenant: each customer gets their own Postgres schema, enforced at the connection pool level via SET search_path. A forgotten filter returns empty — not another tenant's data.

This project demonstrates that isolation pattern running end-to-end.

---

What this solves

• Data isolation enforced at the database level, not application-level filters
• JWT with tenant_id in claims — middleware resolves schema before any handler runs
• Tenant onboarding via admin API — schema created, migrated, and seeded in one call
• Tenant-scoped rate limiting — one tenant cannot starve others
• Clean separation between platform-level tables and tenant-level tables

---

What this demonstrates

• Multi-tenancy: Schema-per-tenant isolation with SET search_path per request
• Auth: JWT with custom claims (tenant_id, role), middleware-enforced tenant context
• SQL Modeling: Platform schema + per-tenant schema, Alembic multi-head migrations
• Rate limiting: Tenant-scoped token bucket in Redis (Lua-atomic)
• API Design: Versioned REST, RFC 7807 errors, cursor pagination
• Admin API: Tenant CRUD, schema provisioning, migration runner
• Testing: Cross-tenant isolation tests (deliberately try to leak data, assert empty result)

---

Stack

Backend: Python 3.12 · FastAPI · Pydantic v2 · SQLAlchemy 2.0 Auth: JWT (PyJWT) · bcrypt Database: PostgreSQL 16 · Alembic (multi-head) Cache / Rate limit: Redis 7 · Lua scripts Runtime: Docker · docker-compose CI/CD: GitHub Actions · ruff · pytest · coverage

---

Architecture

flowchart LR
    Client([Client]) -->|JWT Bearer| MW[Auth Middleware\ntenant_id → search_path]
    MW -->|SET search_path| Pool[Connection Pool\nSQLAlchemy async]
    Pool --> PG[(PostgreSQL 16\nschema: tenant_{id})]
    Pool --> PGP[(PostgreSQL 16\nschema: platform)]
    MW -->|rate limit check| Redis[(Redis 7\ntenant counters)]
    Admin([Admin API]) -->|provision| Provisioner[Tenant Provisioner\nCREATE SCHEMA + migrate]
    Provisioner --> PG

    classDef store fill:#0b5,stroke:#062,color:#fff
    classDef infra fill:#248,stroke:#124,color:#fff
    class PG,PGP,Redis store
    class MW,Provisioner infra

Loading

Request flow:

1. Client presents JWT. Middleware extracts tenant_id, validates signature.
2. Middleware calls SET search_path = tenant_{id}, public on the connection.
3. All queries in that request scope to tenant's schema — no application filter needed.
4. Rate limiter checks tenant:{id}:tokens counter in Redis.
5. Admin POST /tenants → creates schema → runs Alembic migrations against it → returns credentials.

See /docs/adr/ for trade-off analysis.

---

Architecture Decision Records

ADR	Decision	Status
001	Schema-per-tenant over row-level security	Accepted
002	JWT with tenant_id claim over API key lookup	Accepted
003	Alembic multi-head migrations for tenant schemas	Accepted
004	Per-tenant rate limit over global limit	Accepted
005	Tenant provisioning strategy	Pending

---

Local development

# Requirements: Docker Desktop, Python 3.12+

make up              # builds + starts api + Postgres 16 + Redis 7
make migrate         # platform schema migration
make create-tenant   # provision a sample tenant schema
make logs            # tail api logs
make down            # stop stack

API at http://localhost:8000. Admin API at /api/v1/admin. Liveness at /health.

See PROGRESS.md for the current build log.

---

Author

Jotive.dev — Backend Engineer · Python · Node.js GitHub · LinkedIn

---

License

MIT