A multi-tenant records API on FastAPI, PostgreSQL, and Redis. The thing I cared about most here is where tenant isolation lives: it's in the database, enforced by Postgres Row-Level Security, not in application code. A request that skips the app layer entirely still can't read another tenant's rows.
flowchart LR
Client([Client App]) -->|JWT Bearer| API[FastAPI]
API -->|1. Decode JWT| Auth[Auth Layer]
Auth -->|2. Extract client_id| Tenant[Tenant Middleware]
Tenant -->|3. Check cache| Redis[(Redis)]
Tenant -->|4. SET LOCAL client_id| PG[(PostgreSQL)]
subgraph PostgreSQL
PG -->|5. RLS evaluates policy| Policy{tenant_isolation}
Policy -->|client_id matches| Rows[Filtered Rows]
Policy -->|no match| Empty[Zero Rows]
end
Rows -->|6. Serialize + cache| Redis
Rows -->|7. Return page| Client
A GET /records comes in with a JWT. The auth dependency decodes it and pulls out the client_id. Before touching Postgres I check Redis, and on a hit the request returns right there, without ever opening a database connection. On a miss I open a transaction, run SET LOCAL app.current_client_id, and run the query. Notice there's no WHERE client_id anywhere; RLS handles that. The page gets cached for five minutes, the transaction commits, and the SET LOCAL evaporates with it.
docker compose up --build -d # postgres + redis + api (migrations run on boot)
docker compose exec api python scripts/generate_data.py --records 7000000 # ~1 GB dataset (unattended)
# get a token, then fetch records
curl -s -X POST http://localhost:8000/auth/token \
-H "Content-Type: application/json" -d '{"email": "user1@client1.com"}'
curl -s http://localhost:8000/records -H "Authorization: Bearer <token>"
# verification + performance
docker compose exec api python scripts/verify_rls.py # 5 RLS enforcement cases
docker compose exec api python scripts/explain_analyze.py # query plans on the full dataset
docker compose exec api python scripts/benchmark.py # cache hit vs miss timingsAPI docs live at http://localhost:8000/docs.
This is the core of the submission, so it's worth being precise.
RLS is turned on for the records table:
ALTER TABLE records ENABLE ROW LEVEL SECURITY;
ALTER TABLE records FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON records
USING (client_id = NULLIF(current_setting('app.current_client_id', true), '')::integer);A few things make this hold up. It fails closed: if nothing sets the tenant context, current_setting(...) comes back NULL (or an empty string after a SET LOCAL scope ends, which the NULLIF mops up), and client_id = NULL is never true, so you get zero rows. No context, no data. The policy also can't be sidestepped by the table owner, because of FORCE, and the API connects as app_user, which isn't a superuser and simply can't turn RLS off. (Superusers can bypass RLS in Postgres, which is exactly why fountain is reserved for migrations and nothing else.) Finally, I use SET LOCAL rather than SET so the context is scoped to the transaction and disappears when it ends, which matters the moment a connection pooler sits in front.
Why go to this trouble instead of just adding WHERE client_id = ? to every query? Because that works right up until someone forgets the filter once, and one forgotten filter is a data breach. Pushing the check into the database kills that whole category of mistake. A missing WHERE, a new hire's first query, even a direct psql session as the app's own role: none of them see anything without setting the context first.
On privileges, there are two roles. fountain owns the schema and runs migrations; app_user is what the API uses, and it can only read and write rows, not ALTER, DROP, or touch the policy. So even a fully compromised app can't disable the isolation it's subject to.
One honest gap: only records has a policy. users and clients don't, and that's deliberate, because login has to find a user by email before there's any tenant context to scope by. The cost is that app_user can read every row in those two tables (it could enumerate emails, for instance). For this exercise that's acceptable, since the sensitive per-tenant data is in records. In a real system I'd put the email lookup behind a SECURITY DEFINER function and apply RLS to users too.
Auth is deliberately thin. POST /auth/token takes an email and hands back a signed JWT with user_id and client_id in it. There's no password check, on purpose: the interesting part of this assessment is how identity flows down to the isolation layer, not credential plumbing. The JWT signature prevents clients from modifying the client_id claim. In production this token would come from a real identity provider (Supabase Auth, say) and get verified the same way.
Cursor-based, ordered by (created_at DESC, id DESC). I went with cursors over offset because offset falls apart at this size: OFFSET 500000 makes Postgres walk and throw away half a million rows, and the results shift if anyone inserts between your requests. The cursor instead does WHERE (created_at, id) < (:ts, :id), which rides the composite index and stays roughly flat no matter how deep you page. The price is that you can't jump to an arbitrary page, which for this API is a fine trade.
The cursor carries both the timestamp and the id, not just the timestamp, because otherwise two records sharing a timestamp would be ambiguous and you'd skip or repeat rows at the boundary. It's also HMAC-signed (SHA-256, truncated), so a client can't hand-craft a cursor to poke at boundaries or smuggle something through the parameter. A bad signature gets a 400.
{ "items": [ {"id": 42, "title": "...", "amount": "150.00", "created_at": "2024-01-15T10:30:00+00:00"} ],
"next_cursor": "ZDJh...", "has_more": true }Pass next_cursor back as the cursor query parameter for the next page.
I cache whole pages of records in Redis, keyed records:{client_id}:{cursor}:{limit}. The client_id in the key keeps tenants from ever sharing an entry, and folding in the cursor and limit means each distinct page is its own entry. TTL is five minutes: long enough to soak up repeated hits, short enough that anything stale fixes itself without intervention. The cache is checked before any database work, so a hit costs zero DB time.
I deliberately don't warm the cache. There's no sensible set of pages to pre-compute, since the cursor/limit combinations are effectively unbounded, so warming would just burn memory on pages nobody asked for. Lazy fill is the right call.
There's no invalidation today because the API is read-only. The moment a write endpoint shows up, the plan is to drop that tenant's keys with SCAN (never KEYS, which stalls the whole Redis event loop) over records:{client_id}:* right after the write commits, then let the pages refill on demand. I'd clear the whole tenant rather than try to patch individual pages, because inserting one row can shuffle every page after it.
CREATE INDEX ix_records_client_created ON records (client_id, created_at DESC, id DESC);
CREATE INDEX ix_users_client_id ON users (client_id);That one composite index is doing three jobs: it's what RLS uses to locate a tenant's rows (leading client_id), it satisfies the ORDER BY without a sort step, and it backs the cursor's range scan. Drop it and every query becomes a full scan plus a sort, which is a non-starter at a few million rows. A covering index with INCLUDE (title, amount) would let Postgres skip the heap entirely, but I didn't add it; the extra storage isn't worth it unless EXPLAIN starts showing a lot of heap fetches. Run scripts/explain_analyze.py to see the plans.
Every request is logged with a request_id, the method, path, and status, how long it took, the client_id, and whether it was a cache hit. The X-Request-ID header is echoed back so you can line a log line up with a specific call.
At roughly 100M rows and thousands of concurrent users, here's what gives first and what I'd do about it:
| Bottleneck | Mitigation |
|---|---|
| Postgres reads saturate | Read replicas behind PgBouncer |
| Table outgrows the buffer cache | Range-partition records by created_at |
| Connection exhaustion | PgBouncer in transaction mode (works with SET LOCAL) |
| Redis memory | Redis Cluster, sharded by client_id |
| API compute | Horizontal scaling (it's stateless, so just add instances) |
There's a list of things I'd add as load grows but pointedly haven't yet: PgBouncer, read replicas, time-based partitioning, Redis Cluster, per-tenant rate limits, autoscaling on Kubernetes, and tracing with OpenTelemetry. None of them earn their complexity at today's scale, and adding them now would be the wrong call.
And a few I wouldn't touch even at 100x, because they already hold: RLS doesn't change at all with scale, cursor pagination is already flat, the cache strategy stays put (you add Redis nodes, not new logic), and the single index is enough until EXPLAIN says otherwise.
| Decision | Why it's worth it | What it costs |
|---|---|---|
| RLS over app-side filtering | Can't be bypassed, even with direct DB access | More setup; you have to be disciplined about SET LOCAL |
| Cursor over offset | Stays fast at any depth, stable under writes | No jumping to page N |
| HMAC-signed cursors | Clients can't forge or inject through them | Extra logic; cursors break if the secret rotates |
| Two DB roles | A compromised app still can't alter schema or RLS | Another role to manage |
| SCAN-based invalidation | Non-blocking, safe in production | Can miss a key mid-iteration (the TTL covers it) |
| No password auth | Keeps the focus on the architecture being tested | Not production-ready without an identity provider |
| Lazy cache, no warm-up | Memory-efficient and simple | First request after invalidation eats the DB latency |
Python 3.12, FastAPI, SQLAlchemy 2.0 (async with asyncpg), PostgreSQL 16 with RLS, Redis 7, Alembic, Pydantic v2, Docker Compose.