working study · MITmulti-tenant saas2026

Kairos

Multi-tenant OKR tracker that enforces tenant isolation in PostgreSQL with Row-Level Security, not in application code.

The problem

Most multi-tenant apps enforce isolation in the application layer — every query needs a WHERE workspace_id = ?. One forgotten clause leaks one tenant's data to another. Kairos pushes isolation into the database with PostgreSQL Row-Level Security, so a bug in the API still cannot cross the tenant boundary.

Architecture (the request lifecycle)

A request carries the workspace identity in a header. A Spring filter reads it into a per-request context. When JPA borrows a connection from the HikariCP pool, a wrapper sets the Postgres session variable app.current_workspace on that connection; RLS policies on every tenant table filter rows against it. A proxy clears the variable when the connection returns to the pool, so a pooled connection can't carry tenant context to the next request. Writes additionally pass through idempotency and rate-limit filters, recompute the parent objective's progress, append to the audit log, and broadcast a live event over SSE.

Key decisions

DECISIONCHOICEWHY

Tenant isolationPostgreSQL Row-Level Security with FORCE RLSA missed WHERE clause can't leak data when the database enforces the boundary. FORCE is required because the app and Flyway connect as the table-owning role, which RLS otherwise exempts.

Workspace contextapp.current_workspace set on HikariCP connection checkout, cleared on closeA pooled connection must not carry one request's tenant context into the next.

IdempotencyStripe-style Idempotency-Key, 24h Redis cache of 2xx responsesNetwork retries and double-submits must not double-write. Failures aren't cached — they should be retried for real.

Rate limitingRedis ZSET sliding window, per workspaceAvoids the boundary-burst problem of a fixed-window counter.

ResilienceIdempotency and rate-limit filters fail open if Redis is downRedis here is an optimisation, not a correctness guarantee — a cache outage should degrade the limiter, not take down the API.

AuditInsert-only, monthly-partitioned audit_log with before/after JSONBA queryable, append-only record of every mutation.

Live updatesSSE for the dashboardUpdates are one-directional; SSE is simpler than WebSockets and auto-reconnects. Single-instance — cross-instance fan-out via Redis Pub/Sub is on the roadmap.

Status

Working project built to explore the RLS isolation pattern. Auth is stubbed (header-based, standing in for a verified JWT); SSE is single-instance; the project is a study, not production software.

Java 21Spring Boot 3.3PostgreSQL 16Redis 7Next.js 14OpenTelemetryJaegerMicrometerPrometheusGrafana