One Codebase, 8 Brands, 100+ Stores: How Configuration Replaced Forking

The client came in with a straightforward request: extend the inventory system to 7 more brands. Version one served one brand. They wanted 8.

The obvious move was to fork the codebase eight times. Spin up separate instances, copy the configs, deploy independently. Three weeks of work, done.

I spent most of a day thinking through what maintaining 8 forks looks like at month six. Bug in the upload pipeline? Eight PRs, eight reviews, eight deployments. Schema change? Eight migration runs in the right order — and you hope someone doesn't skip brand 4. Feature request from the VP? Eight development tickets. I wrote "configuration-driven multi-tenant" in my notes and spent the extra week building it right.

That was the easy decision. The harder ones came later.

The actual scope

This wasn't a small internal tool. The system served 100+ retail stores across Luzon, Visayas, and Mindanao — eight fashion brands, each with their own stores, their own inventory, their own operations hierarchy. Brand assistants, managers, AVPs, VPs, and executives all needed visibility into different cuts of the same data.

Before this system, every month looked like this: store staff exported transaction data, brand assistants collected it across all their stores, operations consolidated everything in Excel, someone validated it, someone else reviewed it, and by the time executives saw the numbers, weeks had passed. Data accuracy was around 94% on a good month. Decisions that should take a day were taking a month.

The target: 500K+ records per month, automated. 15K to 30K records per CSV upload, per brand, per cycle.

The architecture: one JSON file, eight brands

Every brand is a JSON object in brand.config.json:

[
  { "brand_label": "Brand A", "brand_database": "brand_a", "key": "brand_a", "brand_port": 3022 },
  { "brand_label": "Brand B", "brand_database": "brand_b", "key": "brand_b", "brand_port": 3023 },
  { "brand_label": "Brand C", "brand_database": "brand_c", "key": "brand_c", "brand_port": 3025 }
  // ... 5 more
]

Each brand gets its own MySQL database. No shared tables, no WHERE brand_id = ? filtering across a single schema. The databases are completely isolated. A bug in the application layer can't leak data across brands because there's no shared data path to leak through.

The application reads the config at startup. PM2 uses it to spin up instances per brand, each with its own DATABASE_URL derived from the config at process start.

Adding a new brand: add one JSON object, restart PM2. Under 5 minutes. No code changes, no pipeline modifications. The same migration files run against the new database.

This only works when the business logic is genuinely the same across brands. In this case it was — same inventory structure, same sales process, same reporting hierarchy. The brands were different labels on the same operational model. The moment you start adding if (brand === 'x') conditionals into business logic, the single-codebase argument starts collapsing. It held here because we kept that line clear.

The CPU was sitting idle

Deployment ran on 4-core on-premise servers — client-owned hardware, no cloud, no VPC. One problem discovered after going live: Next.js was running as a single Node.js process. 25% CPU utilization. The other 3 cores were doing nothing while users hit service interruptions during peak hours.

PM2 cluster mode:

// pm2.config.js (abbreviated)
const pm2Config = {
  apps: brandList.map(brand => ({
    name: brand.key,
    script: 'script.js',
    args: `${brand.key} ${brand.brand_port}`,
    instances: appConfig?.cpu_cores || 4,  // 4 workers per brand
    exec_mode: 'cluster',                   // round-robin across cores
    max_memory_restart: '1G',
    autorestart: true,
    env: {
      DATABASE_URL: db({ host: dbHost, dbName: brand.brand_database }),
      PORT: brand.brand_port,
      NODE_ENV: environment,
    }
  }))
};
// 8 brands × 4 cores = 32 worker processes total

PM2 handles round-robin load balancing automatically. 32 total processes (4 per brand), each with a 1GB memory ceiling and auto-restart on failure. Throughput went from ~100 req/sec to ~800 req/sec. Uptime from 95% to 99.95%.

The hardware didn't change. The server was already capable — it just wasn't being used.

Where Prisma failed

This is the part I wasn't expecting.

The initial implementation used Prisma for everything — schema definition, migrations, and query execution. When upload files hit 20K records, the endpoint started timing out. Then failing. Then the connection pool would exhaust and take down other requests with it.

Prisma has a few characteristics that compound badly at this volume:

Connection pool exhaustion. Every batch insert opens a database transaction. At 20K records, you burn through the pool before the first batch finishes.

No native SQL JOINs. Prisma fetches related tables separately and joins them in its Rust query engine. For standard CRUD this is invisible. For a query spanning 100+ stores with inventory, sales, and delivery data, it means the join is happening in application memory, not in MySQL where it belongs.

Query engine overhead. Every query passes through a network request to the Prisma query engine process. One extra hop per operation, times 20,000 operations.

Codedamn published a detailed breakdown of exactly these issues after migrating off Prisma themselves. We were hitting all of it.

The fix: keep Prisma for schema definition and migrations — it's genuinely good at this — and use MySQL2 with raw SQL for all data processing.

// Before: Prisma — fails silently above ~15K records
await prisma.deliveryItem.createMany({ data: records });
 
// After: MySQL2 bulk INSERT — handles 30K records in ~8 seconds
const placeholders = values.map(() => '(?, ?, ?, ?, ?)').join(', ');
await connection.execute(
  `INSERT INTO delivery_items (parent_id, merchandise_id, quantity, amount, created_at)
   VALUES ${placeholders}`,
  values.flat()
);

Prisma-generated types still work for type safety across the application. The query engine just isn't in the data path anymore.

The upload pipeline

With MySQL2 in place, the upload became a 5-phase transaction:

Phase 1 — Validation. Multi-stage checks before touching the database. Malformed records rejected with line numbers. No silent failures, no partial uploads.

Phase 2 — Deduplication. O(1) lookup maps using JavaScript Map. The naive approach checks existence per record — that's an N+1 query per item. Instead: fetch everything relevant in one query, build the index, classify every incoming record against it.

const existingIndex = new Map(
  existingRecords.map(r => [`${r.parent_id}-${r.merchandise_id}`, r])
);
 
for (const item of items) {
  const key = `${parentId}-${item.merchandise_id}`;
  const existing = existingIndex.get(key); // O(1) — no additional queries
  existing
    ? itemsToUpdate.push({ id: existing.id, ...item })
    : itemsToInsert.push({ parent_id: parentId, ...item });
}

Phase 3 — Batch fetching. Existing records fetched in 3,000-record batches to cap memory during concurrent uploads from multiple brands.

Phase 4 — Bulk operations. Single INSERT with multi-row VALUES. Batch UPDATE with CASE WHEN. Not 20,000 individual statements.

// Bulk UPDATE — one query, N records
const quantityCases = itemsToUpdate
  .map(op => `WHEN ${op.id} THEN ${op.quantity}`)
  .join(' ');
 
await connection.execute(
  `UPDATE delivery_items
   SET quantity = CASE id ${quantityCases} END,
       modified_at = CURRENT_TIMESTAMP
   WHERE id IN (${itemsToUpdate.map(op => op.id).join(',')})`,
);

Phase 5 — Inventory sync. All 4 related tables updated in the same ACID transaction: inv_location, sales_inventory, sales_history, and parent totals. The transaction either fully commits or fully rolls back. No partial states.

Memory per operation dropped from 2GB (full dataset loaded at once) to 128MB (chunked). The crashes stopped.

The query problem

MOS (Margin Over Sales) reports were taking 2+ minutes to load. 100+ stores, 30 days of data, multiple joined tables. The slow part wasn't the data volume — it was Prisma fetching each table separately and joining in application memory.

Switching to raw SQL with proper INNER JOIN and adding composite indexes on the frequently-joined columns:

-- Composite indexes added
CREATE INDEX idx_transactions_store_date ON transactions (store_id, transaction_date);
CREATE INDEX idx_inventory_store_active  ON inventory   (store_id, stock_level, is_active);
CREATE INDEX idx_stores_active_region    ON stores      (is_active, region, store_id);
 
-- Single optimized query instead of multiple fetches + application-level join
SELECT
  s.store_name,
  s.region,
  SUM(t.amount)              AS total_sales,
  i.stock_level,
  d.delivery_count
FROM stores s
  INNER JOIN transactions t ON s.store_id = t.store_id
  INNER JOIN inventory    i ON s.store_id = i.store_id
  LEFT  JOIN deliveries   d ON s.store_id = d.store_id
WHERE t.transaction_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND s.is_active = 1
GROUP BY s.store_id, s.store_name, s.region, i.stock_level, d.delivery_count
HAVING total_sales > 0
ORDER BY total_sales DESC;

MOS reports dropped to 30–40 seconds. Not instant, but usable as a daily workflow. Connection pool bumped from 10 to 20 connections per instance to handle concurrent brand operations without timeouts.

On the frontend, React Query with a 5-minute stale time and 30-minute cache cut redundant API calls by 70%. Dashboard loads that were hitting 3–4 seconds dropped under 1 second on repeat views.

Stock reconciliation

Before automation, reconciling stock transfers and restocking took 2 days per week — manual work across 4 related tables that all need to stay in sync. About 104 hours per month across the operations team.

The automated process updates all 4 tables in a single transaction. 3 seconds per upload cycle. The 2-day reconciliation became under an hour a day.

Numbers

What I'd do differently

The Prisma pivot cost about a week of debugging that could have been avoided by running the actual upload workload against Prisma early. The lesson isn't "don't use Prisma" — it's that ORM performance should be tested against your real data volumes before the architecture depends on it. 10K records in development doesn't tell you what happens at 20K in production.

On-premise deployment has constraints cloud deployments don't. No autoscaling, no managed failover. PM2 auto-restart handles process crashes, but hardware failure means real downtime. For this client, on-premise wasn't negotiable — data sovereignty, existing infrastructure, cost. You design for the environment you actually have.

The configuration-driven model worked because the 8 brands were genuinely the same system. That line is worth holding deliberately. The moment brand-specific conditionals start appearing in business logic, the single-codebase argument starts collapsing. It held here because every difference stayed in the config, never in the code.