标签：#multitenancy

When you're building a multi-tenant SaaS, the first architectural question is usually: how do you keep tenant data isolated? The options range from separate databases per tenant (maximum isolation, maximum cost) to a shared database with row-level filtering (minimum cost, more careful coding required). But there's an equally important question that gets less attention: how does your API know which tenant context a request belongs to? This post covers a pattern we've used in production: a custom request header for tenant scoping, combined with JWT authentication. Simple to implement, easy to audit, and flexible enough to support multi-tenant access from a single user account. The Three Common Approaches 1. Subdomain-based ( tenant.yourdomain.com ) The tenant is encoded in the hostname. Each subdomain routes to the same backend, which extracts the tenant from the Host header. Good: Intuitive, visible in the URL. Bad: Requires wildcard TLS certs, more complex DNS setup, awkward in development, doesn't work for mobile API clients the same way. 2. URL path-based ( /api/tenants/{tenantId}/... ) The tenant identifier is part of every route path. Good: RESTful, self-documenting. Bad: Bloats all route definitions, requires every endpoint to include the tenant segment, makes API versioning messier. 3. Header-based ( x-tenant-id: <id> ) A custom header carries the tenant context. Routes stay clean. The tenant scope is resolved in middleware before the handler runs. Good: Routes stay simple, middleware handles scoping uniformly, works well with JWT auth, easy to test. Bad: Less visible (the tenant isn't in the URL), requires clients to always include the header. We use the header approach. The Implementation The API accepts two forms of auth: A JWT token in the Authorization header — identifies who is making the request A tenant ID in the x-tenant-id header — identifies on behalf of which tenant POST /api/v1/members Authorization: Bearer eyJhbGciOiJIUzI1NiIs... x-tenant-id: ten

You're building teams/workspaces on Supabase. A row belongs to a workspace, and a user can touch it if they're a member. Simple enough — until your policies start throwing infinite recursion and you have no idea why. Here's the trap and the pattern that avoids it. The setup A membership table, with RLS on it too (of course): create table workspace_members ( workspace_id uuid references workspaces , user_id uuid references auth . users , primary key ( workspace_id , user_id ) ); alter table workspace_members enable row level security ; create policy "read own memberships" on workspace_members for select using ( user_id = ( select auth . uid ())); The trap Now you scope a tenant table by asking "which workspaces is this user in?" — by querying workspace_members inside the policy : -- ⚠️ this can recurse create policy "members read projects" on projects for select using ( workspace_id in ( select workspace_id from workspace_members where user_id = ( select auth . uid ()) ) ); The policy on projects queries workspace_members , which has its own RLS policy, which re-evaluates… you've built a loop. The fix: a security-definer function Resolve membership with a function that runs with the definer's rights, so reading workspace_members doesn't re-trigger RLS. The policy becomes a plain IN check — no recursion: create or replace function public . user_workspace_ids () returns setof uuid language sql security definer set search_path = '' -- pin it: this is the safety bit as $$ select workspace_id from public . workspace_members where user_id = auth . uid (); $$ ; create policy "members read projects" on projects for select using ( workspace_id in ( select public . user_workspace_ids ()) ); Why it's safe: the function is read-only, returns only the caller's own workspace ids, and search_path = '' prevents search-path hijacking (the classic SECURITY DEFINER footgun). It never exposes another user's memberships. Do this for every tenant table (reads and writes — remember WITH CH

PostgreSQL Partitioning for Multi-Tenant Audit Logs: Querying 100M Events Without Table Scans I'll be direct: if you're running a SaaS with compliance requirements and your audit_logs table is approaching 50M rows, you're three months away from pain. I've watched audit queries go from 200ms to 8 seconds in production at 2am because someone ran a "give me all logs for tenant X" report. Partitioning isn't optimization theater—it's table-stakes infrastructure. At CitizenApp, we store 9 months of audit logs across 50+ tenants. Without partitioning, a single compliance query would full-table scan 100M+ rows. With it, we hit the same data in <100ms. This post is exactly how we do it. Why Partitioning Matters (The Reality Check) Most developers treat audit_logs like any other table. You add an index on tenant_id and created_at , call it done, and move on. Then your compliance officer runs a query like: SELECT * FROM audit_logs WHERE tenant_id = 'acme-corp' AND created_at >= '2024-01-01' ORDER BY created_at DESC ; At 50M rows, even with a composite index, PostgreSQL has to: Index scan → finds millions of matching rows Random I/O all over the table Spill to disk if sorting is large Hope the OS cache is warm Partitioning solves this by eliminating the data you don't need from day one . Instead of scanning a 100GB table and filtering it down, PostgreSQL can skip entire partitions. A query against January 2024 data simply ignores partitions for February–December. I prefer partitioning because it's native PostgreSQL—no external caching layer, no read replicas, no Redis gymnastics. It's boring infrastructure that works. The Partitioning Strategy: Composite Partitioning (Range + List) I use a two-level partitioning scheme: Range partition by month ( created_at ) — keeps each partition to ~5–10GB List subpartition by tenant — ensures compliance queries are single-partition scans This is deliberately opinionated. You could do range-only, but then a multi-tenant query still scans the

PostgreSQL LISTEN/NOTIFY for Real-Time Multi-Tenant Events: Ditching Polling and WebSocket Complexity I've shipped real-time features in CitizenApp using three different approaches: naive polling (embarrassing), Redis pub/sub (overkill), and now PostgreSQL's native LISTEN/NOTIFY. The third option is what I should have started with. Most teams reach for Redis or RabbitMQ the moment they need real-time updates. It's the conventional wisdom. But here's the truth: if you're already running PostgreSQL, you have a battle-tested pub/sub system sitting right there. It handles multi-tenancy correctly, scales to thousands of concurrent connections, and eliminates an entire infrastructure dependency—which matters when you're deploying to Render or Vercel where every added service is friction. Why LISTEN/NOTIFY beats the alternatives Polling is dead. HTTP requests every 2-5 seconds for "new notifications"? That's technical debt masquerading as simplicity. It wastes bandwidth, kills your database with unnecessary queries, and users see stale data. Redis is powerful but expensive. Not just in dollars—in operational overhead. You need to manage connection pools, handle failover, monitor memory usage, and keep another service running in production. At CitizenApp's scale (thousands of concurrent tenants), we were paying $50/month for Redis on top of Render just to broadcast notifications that PostgreSQL could handle natively. WebSockets without a broker are a nightmare. If you're running multiple FastAPI workers (and you should be), a WebSocket connection to Worker A doesn't know about events published by Worker B. You need a message broker to fan-out events across processes. Unless you use PostgreSQL LISTEN/NOTIFY, which handles that automatically. PostgreSQL's pub/sub is: Transactional. Notifications only fire after a transaction commits. Tenant-aware. Use channel names like tenant_123_notifications and broadcast only to the right subscribers. Zero extra infrastructure. It's part