Chapter 8: Multi-Tenant Design

AdPriority is a Shopify app. Every Shopify app is inherently multi-tenant: each merchant who installs the app operates within their own isolated data context. This chapter defines how AdPriority implements tenant isolation, provisioning, lifecycle management, and tier-based feature gating – all within a single shared PostgreSQL database.

6.1 Isolation Model

6.1.1 Strategy: Shared Database, Tenant-Scoped Tables

AdPriority uses a single adpriority_db database on the shared postgres16 container. Every table that stores tenant data includes a store_id column referencing the stores table. There are no per-tenant databases or schemas.

+-----------------------------------------------------------------------+
|                        adpriority_db                                  |
|                                                                       |
|  stores (tenant registry)                                             |
|  +----+----------------------------+----------+-----+                 |
|  | id | shopify_shop               | plan_tier| ... |                 |
|  +----+----------------------------+----------+-----+                 |
|  | A  | nexus-clothes.myshopify.com| growth   | ... |                 |
|  | B  | acme-store.myshopify.com   | starter  | ... |                 |
|  | C  | brand-co.myshopify.com     | pro      | ... |                 |
|  +----+----------------------------+----------+-----+                 |
|                                                                       |
|  products (all tenants, scoped by store_id)                           |
|  +----+----------+----------------------+----------+--------+         |
|  | id | store_id | shopify_product_id   | priority | ...    |         |
|  +----+----------+----------------------+----------+--------+         |
|  | 1  | A        | 8779355160808        | 4        | ...    |         |
|  | 2  | A        | 9128994570472        | 5        | ...    |         |
|  | 3  | B        | 7712345678901        | 3        | ...    |         |
|  | 4  | C        | 8834567890123        | 2        | ...    |         |
|  +----+----------+----------------------+----------+--------+         |
|                                                                       |
|  rules, seasons, sync_logs, audit_logs, subscriptions ...             |
|  (all follow same store_id scoping pattern)                           |
+-----------------------------------------------------------------------+

Why shared database, not per-tenant databases?

For a single-developer project targeting up to 1,000 tenants in Year 1, the shared database approach provides the best trade-off between simplicity and isolation. The store_id column is indexed on every table, and Prisma middleware enforces automatic scoping.

6.1.2 Tables with Tenant Scope

Every table that stores tenant-specific data includes a store_id foreign key. The only exception is the stores table itself, which serves as the tenant registry.

+---------------------+----------+------------------------------------------+
| Table               | store_id | Description                              |
+---------------------+----------+------------------------------------------+
| stores              | (is PK)  | Tenant registry (one row per shop)       |
| products            | FK       | Product priority scores and GMC mapping  |
| rules               | FK       | Priority rule definitions                |
| rule_conditions     | via rule | Conditions (joined through rules.id)     |
| seasons             | FK       | Season date ranges                       |
| season_rules        | via season| Category x season mappings               |
| sync_logs           | FK       | Sync audit trail                         |
| subscriptions       | FK (1:1) | Billing records                          |
| audit_logs          | FK       | Change tracking                          |
+---------------------+----------+------------------------------------------+

6.1.3 Query Scoping with Prisma Middleware

All database queries are automatically scoped to the authenticated tenant using Prisma client extensions. This prevents accidental cross-tenant data access.

// database/client.ts

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

/**
 * Creates a tenant-scoped Prisma client.
 * All queries through this client are automatically filtered by store_id.
 */
export function getTenantClient(storeId: string) {
  return prisma.$extends({
    query: {
      $allModels: {
        async findMany({ args, query }) {
          args.where = { ...args.where, storeId };
          return query(args);
        },
        async findFirst({ args, query }) {
          args.where = { ...args.where, storeId };
          return query(args);
        },
        async findUnique({ args, query }) {
          // findUnique uses unique fields; add storeId check post-query
          const result = await query(args);
          if (result && (result as any).storeId !== storeId) {
            return null; // Tenant mismatch
          }
          return result;
        },
        async create({ args, query }) {
          args.data = { ...args.data, storeId };
          return query(args);
        },
        async update({ args, query }) {
          args.where = { ...args.where, storeId } as any;
          return query(args);
        },
        async delete({ args, query }) {
          args.where = { ...args.where, storeId } as any;
          return query(args);
        },
        async updateMany({ args, query }) {
          args.where = { ...args.where, storeId };
          return query(args);
        },
        async deleteMany({ args, query }) {
          args.where = { ...args.where, storeId };
          return query(args);
        },
      },
    },
  });
}

Usage in route handlers:

// api/routes/products.ts

router.get('/products', async (req, res) => {
  const storeId = req.session.storeId; // From Shopify session token
  const db = getTenantClient(storeId);

  // This query is automatically scoped to the tenant.
  // No need to manually add WHERE store_id = ?
  const products = await db.product.findMany({
    where: { syncStatus: 'pending' },
    orderBy: { priority: 'desc' },
    take: 50,
  });

  res.json({ products });
});

6.2 Shopify as Natural Multi-Tenancy

The Shopify app model provides built-in multi-tenancy primitives that AdPriority leverages.

Shopify App Store
    |
    | Merchant clicks "Add app"
    v
+-------------------+
| Shopify OAuth     |
| Install Flow      |
|                   |
| 1. Consent screen |
| 2. Permissions    |
| 3. Callback       |
+--------+----------+
         |
         | shop = acme-store.myshopify.com
         | access_token = shpat_xxxxx
         v
+-------------------+
| AdPriority        |
| /auth/callback    |
|                   |
| UPSERT stores     |
| SET shop, token   |
+-------------------+

How Shopify enforces tenant boundaries:

AdPriority maps the Shopify shop domain (e.g., nexus-clothes.myshopify.com) to a row in the stores table. The stores.id UUID then serves as the store_id foreign key across all other tables.

6.3 Tenant Provisioning

6.3.1 Install Flow

When a merchant installs AdPriority from the Shopify App Store, the following provisioning sequence executes automatically.

Step  Action                                   Table/System
----  ---------------------------------------- -------------------
  1   Merchant clicks "Add app" in Shopify     Shopify App Store
      |
  2   Shopify redirects to /auth/shopify       AdPriority backend
      with shop domain + auth code
      |
  3   Exchange auth code for access token      Shopify OAuth API
      |
  4   UPSERT store record:                     stores table
      - shopify_shop = 'acme.myshopify.com'
      - shopify_access_token = encrypted(token)
      - plan_tier = 'starter' (default)
      - default_priority = 3
      - new_arrival_priority = 5
      - new_arrival_days = 14
      - is_active = true
      - installed_at = now()
      |
  5   Register Shopify webhooks:               Shopify Webhooks API
      - products/create
      - products/update
      - products/delete
      - app/uninstalled
      |
  6   Create default seasons:                  seasons table
      - Winter (Dec 1 - Feb 28)
      - Spring (Mar 1 - May 31)
      - Summer (Jun 1 - Aug 31)
      - Fall (Sep 1 - Nov 30)
      |
  7   Queue product import job:                Inline worker
      - Fetch all products from Shopify
      - Apply default priority (3)
      - Store in products table
      |
  8   Redirect to app dashboard:               Frontend (React)
      - Show onboarding wizard
      - Step 1: Connect Google account
      - Step 2: Configure category rules
      - Step 3: Review priorities
      - Step 4: Connect Google Sheet to GMC

6.3.2 Provisioning Timing

The merchant sees the dashboard within 6 seconds of completing OAuth. Product import runs in the background, with a progress indicator on the dashboard.

6.4 Google Integration Per Tenant

Each tenant connects their own Google account. AdPriority never shares Google credentials or Sheet access between tenants.

+------------------+     +------------------+     +------------------+
|  Tenant A        |     |  Tenant B        |     |  Tenant C        |
|  (Nexus)         |     |  (Acme)          |     |  (BrandCo)       |
+------------------+     +------------------+     +------------------+
| Google Account:  |     | Google Account:  |     | Google Account:  |
| will@nexus.com   |     | ads@acme.com     |     | team@brandco.com |
|                  |     |                  |     |                  |
| GMC Merchant ID: |     | GMC Merchant ID: |     | GMC Merchant ID: |
| 123456789        |     | 987654321        |     | 456789123        |
|                  |     |                  |     |                  |
| Google Sheet:    |     | Google Sheet:    |     | Google Sheet:    |
| (own sheet)      |     | (own sheet)      |     | (own sheet)      |
|                  |     |                  |     |                  |
| Refresh Token:   |     | Refresh Token:   |     | Refresh Token:   |
| encrypted(xxx)   |     | encrypted(yyy)   |     | encrypted(zzz)   |
+------------------+     +------------------+     +------------------+

Per-tenant Google configuration (stored in stores table):

Google OAuth flow per tenant:

Tenant clicks "Connect Google" in Settings
    |
    v
Redirect to Google OAuth consent
(scope: spreadsheets + content API)
    |
    v
Google returns auth code to /auth/google/callback
    |
    v
Exchange for refresh token + access token
    |
    v
Store encrypted refresh token in stores table
    |
    v
Tenant selects or creates Google Sheet
    |
    v
Store sheet_id in stores table
    |
    v
Google connection complete

6.5 Tier Limits and Feature Gating

6.5.1 Tier Definitions

+------------------+----------+-----------+----------+--------------+
| Feature          | Starter  | Growth    | Pro      | Enterprise   |
|                  | $29/mo   | $79/mo    | $199/mo  | Custom       |
+------------------+----------+-----------+----------+--------------+
| Product limit    | 500      | Unlimited | Unlimited| Unlimited    |
| Priority scoring | Yes      | Yes       | Yes      | Yes          |
| GMC sync         | Daily    | Hourly    | 15 min   | Real-time    |
| Category rules   | Basic    | Advanced  | Advanced | Advanced     |
| Seasonal calendar| No       | Yes       | Yes      | Yes          |
| New arrival boost| No       | Yes       | Yes      | Yes          |
| Google Ads data  | No       | No        | Yes      | Yes          |
| AI recommends    | No       | No        | Yes      | Yes          |
| Multi-store      | No       | No        | No       | Yes          |
| API access       | No       | No        | No       | Yes          |
+------------------+----------+-----------+----------+--------------+

6.5.2 Feature Gating Implementation

Feature availability is checked at the API layer using middleware.

// api/middleware/tierGate.ts

const TIER_FEATURES: Record<string, string[]> = {
  starter:    ['products', 'rules_basic', 'sync_daily', 'export'],
  growth:     ['products', 'rules_advanced', 'sync_hourly', 'export',
               'seasonal', 'new_arrival'],
  pro:        ['products', 'rules_advanced', 'sync_frequent', 'export',
               'seasonal', 'new_arrival', 'google_ads', 'ai_recommendations'],
  enterprise: ['products', 'rules_advanced', 'sync_realtime', 'export',
               'seasonal', 'new_arrival', 'google_ads', 'ai_recommendations',
               'multi_store', 'api_access'],
};

export function requireFeature(feature: string) {
  return (req: Request, res: Response, next: NextFunction) => {
    const store = req.session.store;
    const allowed = TIER_FEATURES[store.planTier] || [];

    if (!allowed.includes(feature)) {
      return res.status(403).json({
        error: {
          code: 'PLAN_LIMIT',
          message: `This feature requires the Growth plan or higher.`,
          currentPlan: store.planTier,
          requiredFeature: feature,
        },
      });
    }

    next();
  };
}

// Usage in routes:
router.get('/seasons', requireFeature('seasonal'), seasonsController.list);
router.post('/sync/trigger', requireFeature('sync_hourly'), syncController.trigger);

6.5.3 Product Limit Enforcement

The Starter tier limits tenants to 500 products. This is enforced during product import and webhook processing.

// services/products/importService.ts

async function importProduct(storeId: string, shopifyProduct: any) {
  const store = await db.store.findUnique({ where: { id: storeId } });

  // Check product limit for Starter tier
  if (store.planTier === 'starter') {
    const count = await db.product.count({ where: { storeId } });
    if (count >= store.productsLimit) {
      return {
        success: false,
        error: 'PRODUCT_LIMIT_REACHED',
        message: `Starter plan is limited to ${store.productsLimit} products. Upgrade to Growth for unlimited.`,
      };
    }
  }

  // Continue with import...
}

6.5.4 Sync Frequency by Tier

+------------------+---------------------+------------------------------+
| Tier             | Sync Mechanism      | Cron Schedule                |
+------------------+---------------------+------------------------------+
| Starter          | Google Sheet, daily  | 0 3 * * *   (once at 3 AM)  |
| Growth           | Google Sheet, hourly | 0 * * * *   (every hour)    |
| Pro              | Content API, 15 min  | */15 * * * * (every 15 min) |
| Enterprise       | Content API, on-demand| Event-driven (real-time)    |
+------------------+---------------------+------------------------------+

6.6 Tenant Lifecycle

6.6.1 State Machine

                    +------------+
                    |            |
          install   |  INSTALL   |
       +----------->|            |
       |            +-----+------+
       |                  |
       |            provision tenant
       |                  |
       |            +-----v------+
       |            |            |
       |            |   SETUP    |  Onboarding wizard
       |            |            |  (connect Google, configure rules)
       |            +-----+------+
       |                  |
       |            complete setup
       |                  |
       |            +-----v------+
       |            |            |
       |            |   TRIAL    |  14-day free trial (Growth features)
       |            |            |
       |            +--+---+--+--+
       |               |   |  |
       |         subscribe |  | trial expires
       |               |   |  | (no payment)
       |               v   |  v
       |      +--------+   |  +-----------+
       |      |        |   |  |           |
       |      | ACTIVE |   |  |  LIMITED  |  View-only, no sync
       |      |        |   |  |           |
       |      +--+--+--+   |  +-----+-----+
       |         |  |       |        |
       |  upgrade|  |downgrade       | subscribe
       |         |  |       |        |
       |         v  v       |        v
       |      (tier change) |   +----+------+
       |                    |   |           |
       |                    +-->|  ACTIVE   |
       |                        |           |
       |                        +-----+-----+
       |                              |
       |                        uninstall
       |                              |
       |                        +-----v------+
       |                        |            |
       |                        | UNINSTALLED|  Data retained 30 days
       |                        |            |
       |                        +-----+------+
       |                              |
       |                        30 days
       |                              |
       |                        +-----v------+
       |                        |            |
       |          reinstall     |   PURGED   |  Data permanently deleted
       +------------------------            |
                                +------------+

6.6.2 Lifecycle Events

6.6.3 Uninstall and Data Retention

When a merchant uninstalls AdPriority, data is retained for 30 days to allow easy restoration if they reinstall. After 30 days, a cleanup job permanently deletes all tenant data.

app/uninstalled webhook received
    |
    v
1. Mark store as inactive
   UPDATE stores SET is_active = false, uninstalled_at = now()
   WHERE shopify_shop = 'acme.myshopify.com'
    |
    v
2. Stop all sync jobs for this tenant
   (hourly sync skips inactive stores)
    |
    v
3. Revoke Google OAuth tokens (if connected)
    |
    v
4. Clear Google Sheet data (remove rows)
   (GMC will drop labels on next fetch)
    |
    v
5. Schedule purge job for 30 days from now
    |
    v
--- 30 days later ---
    |
    v
6. Purge job executes:
   DELETE FROM audit_logs   WHERE store_id = ?;
   DELETE FROM sync_logs    WHERE store_id = ?;
   DELETE FROM products     WHERE store_id = ?;
   DELETE FROM rule_conditions WHERE rule_id IN
     (SELECT id FROM rules WHERE store_id = ?);
   DELETE FROM rules        WHERE store_id = ?;
   DELETE FROM season_rules WHERE season_id IN
     (SELECT id FROM seasons WHERE store_id = ?);
   DELETE FROM seasons      WHERE store_id = ?;
   DELETE FROM subscriptions WHERE store_id = ?;
   DELETE FROM stores       WHERE id = ?;
    |
    v
7. Log purge completion

Reinstall within 30 days:

Merchant reinstalls app
    |
    v
OAuth callback detects existing store record (is_active = false)
    |
    v
1. Reactivate store:
   UPDATE stores SET is_active = true, uninstalled_at = null
    |
    v
2. Update access token (new OAuth token)
    |
    v
3. Re-register webhooks
    |
    v
4. Cancel scheduled purge job
    |
    v
5. Trigger product reconciliation
   (sync with current Shopify catalog)
    |
    v
6. Resume sync jobs
    |
    v
All previous rules, seasons, and configuration restored

6.7 Data Isolation Verification

6.7.1 Automated Tests

The test suite includes multi-tenant isolation tests that verify no cross-tenant data leakage occurs.

// tests/integration/tenant-isolation.test.ts

describe('Tenant Isolation', () => {
  const storeA = 'store-a-uuid';
  const storeB = 'store-b-uuid';

  beforeAll(async () => {
    // Seed products for both tenants
    await db.product.create({ data: { storeId: storeA, title: 'A-Product', priority: 5, ... } });
    await db.product.create({ data: { storeId: storeB, title: 'B-Product', priority: 3, ... } });
  });

  test('Tenant A cannot see Tenant B products', async () => {
    const clientA = getTenantClient(storeA);
    const products = await clientA.product.findMany();

    expect(products).toHaveLength(1);
    expect(products[0].title).toBe('A-Product');
    expect(products.some(p => p.title === 'B-Product')).toBe(false);
  });

  test('Tenant B cannot update Tenant A products', async () => {
    const clientB = getTenantClient(storeB);
    const productA = await db.product.findFirst({ where: { storeId: storeA } });

    const result = await clientB.product.updateMany({
      where: { id: productA.id },
      data: { priority: 0 },
    });

    expect(result.count).toBe(0); // No rows updated (store_id mismatch)
  });

  test('Bulk operations respect tenant scope', async () => {
    const clientA = getTenantClient(storeA);
    const deleted = await clientA.product.deleteMany({});

    // Should only delete Tenant A's products
    expect(deleted.count).toBe(1);

    // Tenant B's product should still exist
    const bProducts = await db.product.findMany({ where: { storeId: storeB } });
    expect(bProducts).toHaveLength(1);
  });
});

6.7.2 Database-Level Safeguards

In addition to application-level scoping, the database schema provides structural safeguards.

-- Foreign key CASCADE ensures tenant deletion is complete
-- (no orphaned records from other tenants)
ALTER TABLE products
  ADD CONSTRAINT fk_products_store
  FOREIGN KEY (store_id) REFERENCES stores(id)
  ON DELETE CASCADE;

-- Composite unique constraints prevent cross-tenant collisions
-- (same Shopify product ID can exist in multiple tenants)
CREATE UNIQUE INDEX idx_products_tenant_shopify
  ON products(store_id, shopify_product_id, shopify_variant_id);

-- All indexed queries include store_id for partition-like performance
CREATE INDEX idx_products_store_priority
  ON products(store_id, priority);

CREATE INDEX idx_products_store_sync
  ON products(store_id, sync_status);

6.8 Chapter Summary