Retrofitting Multi-Tenancy Into a Live App

The question seemed innocent enough: "What if other clubs want to use this?"

It was July 2025. I'd spent six months building Capo, a football team management platform, for my local Thursday night football group. The app tracked stats, balanced teams using a genetic algorithm, managed match schedules. It worked well. Other organizers had noticed.

The problem was that the entire codebase assumed there was only one club. One set of players. One set of matches. One set of stats. The database schema, the API routes, the frontend components, the background jobs — everything was built for a single tenant.

Adding multi-tenancy meant touching nearly every file in the system.

The Scope

I didn't fully appreciate what "multi-tenant" meant until I started listing what needed to change:

• 33 database tables needed a tenant_id column
• 70+ API routes needed tenant scoping
• 13 SQL functions needed tenant parameters
• 28 React Query hooks needed tenant-aware cache keys
• All unique constraints needed to become tenant-scoped

A player named "Dave" could exist in one club. But if two clubs both had a Dave, the database would reject the second one. Every uniqueness constraint in the system had this problem.

The background job system was worse. Jobs were processing data without any tenant context. A stats calculation job would update aggregated tables for all players, regardless of which club they belonged to. In a multi-tenant world, that's a data leak waiting to happen.

The Migration Strategy

The first decision was how to handle existing data. There were six months of match history, player stats, and configuration. Losing it wasn't an option.

The approach: create a default tenant, assign all existing data to it, then add the tenant_id column with that default. No data migration needed — everything automatically belongs to the original club.

-- Create default tenant
INSERT INTO tenants (id, name, slug) 
VALUES ('default-uuid', 'Berko TNF', 'berko-tnf');

-- Add tenant_id to players table
ALTER TABLE players ADD COLUMN tenant_id UUID REFERENCES tenants(id);
UPDATE players SET tenant_id = 'default-uuid';
ALTER TABLE players ALTER COLUMN tenant_id SET NOT NULL;

This pattern repeated 33 times. The AI generated the migration scripts. I reviewed each one, checking for foreign key relationships and constraint conflicts.

The API Pattern

Every API route needed to know which tenant was making the request. I established a wrapper pattern:

export async function GET(request: NextRequest) {
  return withTenantContext(request, async (tenantId) => {
    const players = await prisma.players.findMany({
      where: withTenantFilter(tenantId)
    });
    return NextResponse.json({ data: players });
  });
}

The withTenantContext wrapper resolves the current tenant from the request (via cookies, headers, or URL path) and passes it to the handler. The withTenantFilter helper ensures every database query includes the tenant constraint.

This pattern had to be applied to over 70 routes. The AI handled the bulk transformation. I reviewed each change, looking for routes that had special logic that might break with the new pattern.

URL Architecture

I had to decide how tenants would be identified in URLs. Two main options:

Subdomains: berko-tnf.capo.app/dashboard
Path-based: capo.app/clubs/berko-tnf/dashboard

Subdomains look cleaner but require wildcard DNS configuration, complicate SSL certificates, and fragment SEO across multiple domains. Path-based routing keeps everything on one domain, simplifies deployment, and consolidates search ranking.

I chose path-based. The AI analyzed both approaches and recommended path-based for this use case. The reasoning was sound: simpler infrastructure, better SEO, and subdomain support could always be added later if needed.

One subtle decision: slugs are immutable. Once a club is created as berko-tnf, that slug never changes, even if the club renames itself. This preserves bookmarks, shared links, and external references. The AI suggested this, and it's proven valuable — clubs have changed their display names without breaking any URLs.

The Challenges

Unique Constraints

The original schema had constraints like UNIQUE(name) on the players table. Two clubs couldn't both have a player named Dave. The fix:

-- Before
UNIQUE(name)

-- After
UNIQUE(tenant_id, name)

Every unique constraint in the system needed this treatment. Some were obvious (player names). Some were hidden in indexes. The AI helped find them all by analyzing the schema.

Background Jobs

Jobs that ran on a schedule had no concept of "current tenant." A job that recalculates player statistics would process all players in the database, mixing data from different clubs.

The solution: every job payload includes a tenant ID. The job processor validates the tenant exists before processing. TypeScript types enforce this — a job payload without tenant_id won't compile.

Advisory Locks

PostgreSQL advisory locks prevent concurrent operations from conflicting. I used them to ensure only one process could update stats for a match at a time. But the lock keys were global — lock key 12345 meant the same thing regardless of tenant.

Two clubs processing match #12345 simultaneously would block each other, even though they're operating on completely separate data.

The fix: hash the tenant ID into the lock key. Now locks are tenant-scoped. Club A's match #12345 doesn't block Club B's match #12345.

Cache Isolation

React Query caches API responses to avoid redundant network requests. But if the cache key is just ['players'], switching tenants shows cached data from the previous tenant. That's a data leak.

Every cache key needed the tenant ID: ['players', tenantId]. Switching tenants now means a new cache key, which means fresh data. The AI caught this during the React Query implementation and built tenant-awareness into every hook from the start.

The Timeline

The retrofit took four weeks:

• Week 1-2: Database migrations. Adding columns, backfilling data, updating constraints.
• Week 3: API route updates. Applying the withTenantContext pattern everywhere.
• Week 4: Testing and validation. Finding edge cases, fixing bugs, verifying isolation.

I talk more about my overall build process in How I Actually Vibe Code. The multi-tenancy retrofit was the largest single architectural change in the project.

What Broke Anyway

Despite careful planning, issues emerged after deployment. The RLS Wars article covers the biggest one — Row-Level Security policies that seemed correct but failed intermittently due to connection pooling.

I also found SQL functions that had accumulated over months without tenant parameters. A helper function like get_current_season_start_date() would query the seasons table without filtering by tenant, returning the wrong club's season dates. Each of these required tracking down all callers and adding the tenant parameter.

The booking system I built later benefited from having multi-tenancy in place from the start. Race conditions for the last spot in a match are hard enough without also worrying about cross-tenant data leaks.

The Lesson

Multi-tenancy from day one would have saved four weeks. Every table would have had tenant_id from the start. Every API route would have been tenant-scoped by default. Every developer (or AI) working on the codebase would have understood that tenant isolation is non-negotiable.

But I didn't know on day one that other clubs would want to use the app. The single-tenant version shipped faster and validated the product. The retrofit was painful but survivable.

If you're building something that might become multi-tenant, consider adding the tenant_id column early, even if you only have one tenant. The migration cost is near zero when the table is empty. The retrofit cost is substantial when you have six months of production data.

The AI made the retrofit possible. Updating 70+ API routes manually would have taken weeks and introduced countless bugs. The AI applied the pattern consistently, and I reviewed the changes. That division of labor — AI for repetitive transformation, human for architectural decisions — worked well.

Four weeks of surgery. Zero data lost. The app now serves multiple clubs, each with complete isolation. The architecture that seemed like overkill became the foundation for everything that followed.