ADR-001 · Single-database multi-tenancy

Status: Accepted

Context

NexSchool serves many schools from one product at low per-student pricing (₹50–130/student/year). That economics only works if tenants share infrastructure — a separate database per school would multiply operational cost and complexity far beyond what the price point supports. At the same time, a school’s data (students, fees, attendance) is sensitive and must never leak to another school.

The tension: maximise sharing (for cost) while guaranteeing isolation (for trust).

Decision

Use single-database, shared-schema multi-tenancy:

• Every business row carries a tenant_id; tenant-scoped models inherit TenantBaseModel.
• Queries are automatically scoped to the current tenant via SQLAlchemy with_loader_criteria in core/database.py, so scoping is the default, not a per-query responsibility.
• Unique constraints are scoped per tenant (UNIQUE(email, tenant_id)).
• The tenant is resolved per request from subdomain / headers (core/tenant.py) into g.tenant_id.

Consequences

Positive

• One database, one migration stream, one app to operate — sustainable at the target price.
• Isolation is enforced by default; a developer who forgets to filter still gets scoped results.

Negative / cost

• Isolation lives in application code, so it must be defended relentlessly. A single scoping bug is a cross-tenant leak.
• A concrete example: an earlier before_compile scoping approach dropped the tenant WHERE clause under LIMIT/OFFSET, leaking rows on every paginated endpoint. This forced the move to with_loader_criteria and a layered defense (no-store caching, Vary headers, tenant-suffixed query keys, branch-scope fail-closed). See Multi-tenancy.

Alternatives considered

• Database-per-tenant — strongest isolation, but operationally and financially incompatible with the pricing model at pilot scale.
• Schema-per-tenant — middle ground, but still multiplies migration and connection overhead; the shared-schema approach with enforced scoping was judged sufficient with defense-in-depth.

Related

• Multi-tenancy · Auth & RBAC