Skip to content
ENNO
← Back to blog
·3 min read·

Put the Tenant Column in the First Migration

One extra column per table on day one, against a backfill across fourteen populated tables later. What went into the first migration, and the seven lookups that still assume one brand.

Put the Tenant Column in the First Migration

A client asked me for a marketing content engine for one brand, with a note in the brief that there might be more brands later. I've learned to pay attention to "maybe more later" in a spec. It usually gets ignored until later actually shows up, and by then it's expensive.

If the schema starts out single brand, every table assumes there's one of everything. Adding a second brand afterwards means a new column on every table, a backfill, making it non-null, rewriting every unique constraint that should have been per brand, and then finding every query that forgot to filter. That last part is where the bugs live. A missing filter doesn't crash anything, it just shows one brand's content to another brand.

What went into the first migration

So every content table got a brand_id from the very first migration, with exactly one brand in the database. It's a foreign key with cascade delete, so a brand and everything it owns go together.

csharp
migrationBuilder.CreateTable( name: "audiences", columns: table => new { id = table.Column<Guid>(type: "uuid", nullable: false), brand_id = table.Column<Guid>(type: "uuid", nullable: false), name = table.Column<string>(type: "text", nullable: false), // ... }, constraints: table => { table.PrimaryKey("PK_audiences", x => x.id); table.ForeignKey( name: "FK_audiences_brands_brand_id", column: x => x.brand_id, principalTable: "brands", principalColumn: "id", onDelete: ReferentialAction.Cascade); });

Anything that has to be unique is unique per brand. An audience name, a weekly plan, a prompt version. The prompts table is the one I like best, because only one version of each prompt can be active per brand and per content type, and Postgres enforces that with a filtered index instead of me trusting the code:

csharp
entity.HasIndex(p => new { p.BrandId, p.ContentType, p.Version }).IsUnique(); entity.HasIndex(p => new { p.BrandId, p.ContentType }) .IsUnique() .HasFilter("is_active = true");

The project grew to 18 tables over five milestones, and the rule held the whole way. Each new table either carried a brand or hung off something that did.

Grid of the 18 tables in the final schema. 14 carry a brand_id foreign key, including audiences, plans, content items, prompts, scores and AI call logs. Four don't: brands itself, admin users, approval transitions which hang off a content item that already has a brand, and the audit log.
14 of 18 tables carry the brand. The other four don't need to.

On day one that's one extra column per table and a slightly longer key. Nobody using the system notices.

Where the single-brand assumption still lives

There's still only one brand, and it would be silly to make the UI pretend otherwise. So the app finds "the" brand with a plain lookup, in seven places. Six weeks in, the client asked me straight out whether anything I'd built would make a second brand expensive, so I went and counted them instead of answering from memory.

csharp
// three of these: throws the moment a second brand exists var brand = await dbContext.Brands.SingleOrDefaultAsync(ct); // four of these: quietly picks whichever brand comes first var brand = await db.Brands.FirstOrDefaultAsync(ct);

The first kind I'm fine with. It throws, so a second brand gets found on the first request. The second kind is the one I'd fix before anyone adds that row, because it wouldn't fail at all. It would just hand one brand's settings to the other brand's content. Seven lookups and a seed file is an afternoon. A brand key backfilled across fourteen populated tables is a migration nobody wants to run on a live database.

I didn't put a global query filter on the brand either. With one brand there's nothing for it to filter yet, and it's the first thing I'd add next to the switcher.

When I skip it

If there's no realistic chance of a second tenant, I don't add one. The cost is small but it isn't zero, and building for things nobody will need makes its own mess. What I listen for is the client saying "later" about brands, customers, regions or companies. That one is usually worth designing for.

The project was handed over after five milestones, still on its first brand. Whoever adds the second one gets a new row and those seven lookups, not every table. This is the kind of .NET backend work I do.

#.NET#EF Core#Postgres#multi-tenant#database design