Tenancy

How the kit keeps organizations apart: the shared database with the tenant column and query filters by default, and the opt-in schema per organization for a product's own tables.

Every organization is a tenant. By default all tenants share the database: each tenant-scoped row carries its organization, every query is filtered to the current one, and a write to another organization's row is refused. A product that must keep its tenants' data apart physically can opt in to one PostgreSQL schema per organization for its own tables.

The shared database

Cloverheap.Tenancy (packages/tenancy) gives every tenant-scoped entity (ITenantScoped) an indexed tenant column, the named query filter Cloverheap.Tenancy and the save-changes guard. The API sets the tenant from the route (/v1/organizations/{organizationId}) after it checked the caller's membership; messages and jobs carry their organization. Reading across organizations takes an explicit scope (TenantContext.AllowCrossTenant()), which the super-admin area logs. This is Tenancy:Mode SharedDatabase, the default, and the right choice for most products.

A schema per organization

What it changes

With Tenancy:Mode SchemaPerTenant, each organization has its own schema, tenant_ followed by its identifier's 32 hexadecimal digits (Tenancy:SchemaPrefix changes the start). A product context's connection for an organization searches that schema first, then the shared one (Tenancy:SharedSchema, public), so the product's tables are read, written and migrated in the organization's schema while the kit's shared tables stay reachable. The tenant column and the query filters stay in force as well.

The kit's own tables (people, organizations, memberships, invitations, billing, the audit log, feature flags, the message and job stores) stay in the shared schema in both modes: they are read across organizations by design, and ColdenDbContext names its schema.

Turning it on

Set it on every host before the first organization exists, the same on the api and the worker:

Tenancy__Mode=SchemaPerTenant
# optional
Tenancy__SchemaPrefix=tenant_
Tenancy__SharedSchema=public

The prefix is a lowercase letter or underscore and up to 29 lowercase letters, digits or underscores, so every schema name stays within PostgreSQL's 63 characters and needs no quoting. The kit registers SchemaPerTenantConnectionResolver as ITenantConnectionResolver on ConnectionStrings:Postgres. Switching an existing installation between modes moves no data; plan that move as a migration of its own.

A product's context of tenant tables

A product keeps its tenant tables in its own DbContext, whose model names no schema:

// Program.cs of the api and the worker
builder.Services.AddColdenTenantDbContext<ProjectsDbContext>();

public sealed class ProjectsDbContext(DbContextOptions<ProjectsDbContext> options, ITenantContext tenantContext)
    : DbContext(options), ITenantAwareDbContext
{
    public ITenantContext TenantContext { get; } = tenantContext;

    public DbSet<Project> Projects => Set<Project>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Project>().ToTable("projects");
        modelBuilder.ApplyTenantQueryFilters(this);
    }
}

AddColdenTenantDbContext gives the context the current organization's connection (or the shared one in the shared database mode), the kit's snake case naming and the tenant guards. Its migrations live in the context's assembly; their history is its own table, __ef_migrations_history_ and the context's name in snake case (__ef_migrations_history_projects_db_context), in the organization's schema or beside the kit's.

Migrations

Generate the product's migrations with the EF tools as usual (dotnet ef migrations add <Name> --context ProjectsDbContext). The api's migrate command applies the kit's migrations, then the product contexts' migrations: once in the shared database, or in the schema of every organization, creating a schema that is missing:

dotnet run --project apps/api/Colden.Api -- migrate
# in a cluster, the api image: migrate

The kit's migrate image applies the kit's migrations only; a product with tenant contexts runs the api's migrate command as its own step before the hosts (a Helm hook next to the kit's), so every organization's schema is current before the new code serves it.

New organizations

Creating an organization (POST /v1/organizations, and seed-demo for the demo) creates its schema and applies the product contexts' migrations before the answer, so its first request finds its tables. A failure there is logged (event 2601) and does not undo the organization; the next migrate creates what is missing.

Limits

• Each organization's connection string has its own connection pool. Size Maximum Pool Size in ConnectionStrings:Postgres for the organizations that are active at once, or put PgBouncer in front of the server.
• Migrating every schema takes longer as organizations grow; migrate migrates them one after the other.
• Cross-organization reports over product tables must query each schema; the shared database mode is simpler when a product needs them often.
• The schema per organization is tested on PostgreSQL only, the kit's database.