Platform, Architecture & Foundations

5. Database, Migrations and Multi-Tenancy

Datastore

Persistence is on PostgreSQL 16 accessed with PGX v5 direct SQL and no ORM, using connection pooling and prepared statements throughout. Redis 7 provides caching via pkg/cache/. For development and test runs an embedded Postgres is available through pkg/embedpg/.

Migrations

The schema is managed by 209 sequential SQL migrations under the migrations package, each with explicit up and down files. A custom migration tool at cmd/migrate/main.go supports run, rollback, status, and create operations. Row-Level Security policies exist on every multi-tenant table.

Tenant isolation

Multi-tenant isolation is enforced in two complementary ways. Every entity carries an organization_id column for application-layer filtering, and PostgreSQL Row-Level Security is applied as defence in depth. The current tenant is set per transaction through the app.current_tenant session variable, wired via DB.WithTenantTransaction(). A typical request therefore opens a tenant-scoped transaction, which causes RLS policies to restrict every query to the active organization even if an application-layer filter were missed. This layered approach means a bug in one layer does not by itself leak data across tenants.