Chapter 14: Database Schema

Database Strategy

AdPriority stores all tenant data in a single PostgreSQL 16 database running on the shared postgres16 container. The schema follows a multi-tenant design with row-level isolation: every table includes a tenant_id foreign key, and every query is scoped to the authenticated tenant. This approach balances simplicity (one database to manage) with security (tenants cannot see each other’s data).

The data model captures eight core concerns: tenant identity, product catalog, variant-level scoring, priority rules, seasonal calendars, sync audit trails, billing state, and category mappings. Each concern maps to a dedicated table with clear foreign key relationships.

DATABASE: adpriority_db
USER:     adpriority_user
HOST:     postgres16:5432 (internal) / localhost:5433 (external)
SCHEMA:   public
ORM:      Prisma 5.x with PostgreSQL provider

Entity Relationship Diagram

                        +-------------------+
                        |     tenants       |
                        +-------------------+
                        | id (PK, UUID)     |
                        | shopify_shop_domain (UNIQUE)
                        | shopify_access_token (encrypted)
                        | plan_tier         |
                        | gmc_merchant_id   |
                        | google_sheet_id   |
                        | google_sheet_url  |
                        | status            |
                        | created_at        |
                        | updated_at        |
                        +--------+----------+
                                 |
              +------------------+------------------+------------------+
              |                  |                  |                  |
              v                  v                  v                  v
    +---------+------+  +-------+--------+  +------+---------+  +----+----------+
    |   products     |  | category_rules |  | seasonal_      |  |   billing     |
    +----------------+  +----------------+  | calendars      |  +---------------+
    | id (PK)        |  | id (PK)        |  +----------------+  | id (PK)       |
    | tenant_id (FK) |  | tenant_id (FK) |  | id (PK)        |  | tenant_id (FK)|
    | shopify_       |  | product_type_  |  | tenant_id (FK) |  | shopify_      |
    |  product_id    |  |  pattern       |  | name           |  |  charge_id    |
    | title          |  | season         |  | season         |  | plan          |
    | product_type   |  | base_priority  |  | start_month    |  | status        |
    | vendor         |  | modifiers      |  | end_month      |  | activated_at  |
    | tags           |  +----------------+  | category_      |  +---------------+
    | status         |                      |  overrides     |
    +-------+--------+                      +----------------+
            |
            v
    +-------+--------+
    |    variants     |
    +----------------+
    | id (PK)        |
    | tenant_id (FK) |
    | product_id (FK)|
    | shopify_       |
    |  variant_id    |
    | sku            |
    | price          |
    | inventory_     |
    |  quantity      |
    | gmc_product_id |  <-- generated: shopify_US_{product.shopify_product_id}_{shopify_variant_id}
    +-------+--------+
            |
            v
    +-------+-----------+
    |  priority_scores   |
    +--------------------+
    | id (PK)            |
    | tenant_id (FK)     |
    | variant_id (FK)    |
    | priority (0-5)     |
    | custom_label_0-4   |
    | calculated_at      |
    | override           |
    | override_reason    |
    +--------------------+

    +--------------------+
    |    sync_logs       |
    +--------------------+
    | id (PK)            |
    | tenant_id (FK)     |
    | sync_type          |
    | status             |
    | products_synced    |
    | errors             |
    | started_at         |
    | completed_at       |
    +--------------------+

Complete Prisma Schema

The following schema defines every model, relationship, index, and constraint used in AdPriority. Each model maps to a snake_case PostgreSQL table via @@map(), while TypeScript fields use camelCase.

// prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// ===========================================================================
// TENANTS - One row per installed Shopify store
// ===========================================================================

model Tenant {
  id                  String   @id @default(uuid()) @db.Uuid
  shopifyShopDomain   String   @unique @map("shopify_shop_domain") @db.VarChar(255)
  shopifyAccessToken  String?  @map("shopify_access_token") @db.Text
  planTier            PlanTier @default(starter) @map("plan_tier")
  gmcMerchantId       String?  @map("gmc_merchant_id") @db.VarChar(50)
  googleSheetId       String?  @map("google_sheet_id") @db.VarChar(255)
  googleSheetUrl      String?  @map("google_sheet_url") @db.Text
  status              TenantStatus @default(active)
  createdAt           DateTime @default(now()) @map("created_at")
  updatedAt           DateTime @updatedAt @map("updated_at")

  // Relations
  products            Product[]
  variants            Variant[]
  priorityScores      PriorityScore[]
  categoryRules       CategoryRule[]
  seasonalCalendars   SeasonalCalendar[]
  syncLogs            SyncLog[]
  billing             Billing?

  @@index([status])
  @@map("tenants")
}

enum PlanTier {
  starter
  growth
  pro
  enterprise
}

enum TenantStatus {
  active
  inactive
  suspended
  uninstalled
}

// ===========================================================================
// PRODUCTS - Shopify products cached locally
// ===========================================================================

model Product {
  id                String        @id @default(uuid()) @db.Uuid
  tenantId          String        @map("tenant_id") @db.Uuid
  tenant            Tenant        @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  shopifyProductId  BigInt        @map("shopify_product_id")
  title             String        @db.VarChar(500)
  productType       String?       @map("product_type") @db.VarChar(255)
  vendor            String?       @db.VarChar(255)
  tags              String[]      @default([])
  status            ProductStatus @default(active)
  createdAt         DateTime      @default(now()) @map("created_at")
  updatedAt         DateTime      @updatedAt @map("updated_at")

  // Relations
  variants          Variant[]

  @@unique([tenantId, shopifyProductId], map: "uq_tenant_shopify_product")
  @@index([tenantId])
  @@index([tenantId, productType], map: "idx_products_tenant_type")
  @@index([tenantId, status], map: "idx_products_tenant_status")
  @@index([tenantId, shopifyProductId], map: "idx_products_tenant_shopify_id")
  @@map("products")
}

enum ProductStatus {
  active
  archived
  draft
}

// ===========================================================================
// VARIANTS - Shopify variants (the unit synced to GMC)
// ===========================================================================

model Variant {
  id                String   @id @default(uuid()) @db.Uuid
  tenantId          String   @map("tenant_id") @db.Uuid
  tenant            Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  productId         String   @map("product_id") @db.Uuid
  product           Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  shopifyVariantId  BigInt   @map("shopify_variant_id")
  sku               String?  @db.VarChar(255)
  price             Decimal  @default(0) @db.Decimal(10, 2)
  inventoryQuantity Int      @default(0) @map("inventory_quantity")

  // GMC product ID is generated at the application layer because Prisma
  // does not support computed columns that reference joined tables.
  // Format: shopify_US_{product.shopifyProductId}_{shopifyVariantId}
  gmcProductId      String?  @map("gmc_product_id") @db.VarChar(255)

  createdAt         DateTime @default(now()) @map("created_at")
  updatedAt         DateTime @updatedAt @map("updated_at")

  // Relations
  priorityScore     PriorityScore?

  @@unique([tenantId, shopifyVariantId], map: "uq_tenant_shopify_variant")
  @@index([tenantId])
  @@index([tenantId, productId], map: "idx_variants_tenant_product")
  @@index([tenantId, inventoryQuantity], map: "idx_variants_tenant_inventory")
  @@index([gmcProductId], map: "idx_variants_gmc_product_id")
  @@map("variants")
}

// ===========================================================================
// PRIORITY SCORES - The core scoring output per variant
// ===========================================================================

model PriorityScore {
  id              String   @id @default(uuid()) @db.Uuid
  tenantId        String   @map("tenant_id") @db.Uuid
  tenant          Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  variantId       String   @unique @map("variant_id") @db.Uuid
  variant         Variant  @relation(fields: [variantId], references: [id], onDelete: Cascade)
  priority        Int      @default(3) @db.SmallInt
  customLabel0    String?  @map("custom_label_0") @db.VarChar(100)
  customLabel1    String?  @map("custom_label_1") @db.VarChar(100)
  customLabel2    String?  @map("custom_label_2") @db.VarChar(100)
  customLabel3    String?  @map("custom_label_3") @db.VarChar(100)
  customLabel4    String?  @map("custom_label_4") @db.VarChar(100)
  calculatedAt    DateTime @default(now()) @map("calculated_at")
  override        Boolean  @default(false)
  overrideReason  String?  @map("override_reason") @db.Text

  @@index([tenantId])
  @@index([tenantId, priority], map: "idx_priority_scores_tenant_priority")
  @@index([tenantId, calculatedAt], map: "idx_priority_scores_tenant_calc")

  // Constraint: priority must be 0-5
  // Enforced at application layer and via CHECK constraint in migration SQL
  @@map("priority_scores")
}

// ===========================================================================
// CATEGORY RULES - Maps product types to base priorities per season
// ===========================================================================

model CategoryRule {
  id                 String   @id @default(uuid()) @db.Uuid
  tenantId           String   @map("tenant_id") @db.Uuid
  tenant             Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  productTypePattern String   @map("product_type_pattern") @db.VarChar(255)
  season             Season?
  basePriority       Int      @map("base_priority") @db.SmallInt
  modifiers          Json?    @default("{}")

  createdAt          DateTime @default(now()) @map("created_at")
  updatedAt          DateTime @updatedAt @map("updated_at")

  @@unique([tenantId, productTypePattern, season], map: "uq_tenant_pattern_season")
  @@index([tenantId])
  @@index([tenantId, season], map: "idx_category_rules_tenant_season")
  @@map("category_rules")
}

enum Season {
  winter
  spring
  summer
  fall
}

// ===========================================================================
// SEASONAL CALENDARS - Defines when each season starts and ends
// ===========================================================================

model SeasonalCalendar {
  id                 String   @id @default(uuid()) @db.Uuid
  tenantId           String   @map("tenant_id") @db.Uuid
  tenant             Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  name               String   @db.VarChar(100)
  season             Season
  startMonth         Int      @map("start_month") @db.SmallInt
  endMonth           Int      @map("end_month") @db.SmallInt
  categoryOverrides  Json?    @default("{}") @map("category_overrides")

  createdAt          DateTime @default(now()) @map("created_at")
  updatedAt          DateTime @updatedAt @map("updated_at")

  @@unique([tenantId, season], map: "uq_tenant_season")
  @@index([tenantId])
  @@map("seasonal_calendars")
}

// ===========================================================================
// SYNC LOGS - Audit trail for every sync operation
// ===========================================================================

model SyncLog {
  id             String     @id @default(uuid()) @db.Uuid
  tenantId       String     @map("tenant_id") @db.Uuid
  tenant         Tenant     @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  syncType       SyncType   @map("sync_type")
  status         SyncStatus
  productsSynced Int        @default(0) @map("products_synced")
  errors         Json?      @default("[]")
  startedAt      DateTime   @default(now()) @map("started_at")
  completedAt    DateTime?  @map("completed_at")

  @@index([tenantId])
  @@index([tenantId, syncType], map: "idx_sync_logs_tenant_type")
  @@index([startedAt(sort: Desc)], map: "idx_sync_logs_started_desc")
  @@map("sync_logs")
}

enum SyncType {
  shopify
  gmc
  sheet
}

enum SyncStatus {
  started
  completed
  failed
  cancelled
}

// ===========================================================================
// BILLING - Shopify subscription state
// ===========================================================================

model Billing {
  id              String        @id @default(uuid()) @db.Uuid
  tenantId        String        @unique @map("tenant_id") @db.Uuid
  tenant          Tenant        @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  shopifyChargeId BigInt?       @map("shopify_charge_id")
  plan            PlanTier
  status          BillingStatus
  activatedAt     DateTime?     @map("activated_at")

  createdAt       DateTime      @default(now()) @map("created_at")
  updatedAt       DateTime      @updatedAt @map("updated_at")

  @@index([status], map: "idx_billing_status")
  @@map("billing")
}

enum BillingStatus {
  pending
  active
  frozen
  cancelled
  expired
}

Table Reference

tenants

The tenant table is the root of the multi-tenant hierarchy. Every other table references tenants.id as a foreign key. The shopify_shop_domain column is the canonical identifier, using the format store-name.myshopify.com.

ColumnTypeConstraintsDescription
idUUIDPK, default uuidInternal tenant identifier
shopify_shop_domainVARCHAR(255)UNIQUE, NOT NULLe.g. nexus-clothes.myshopify.com
shopify_access_tokenTEXTnullableAES-256 encrypted at rest
plan_tierENUMNOT NULL, default starterstarter, growth, pro, enterprise
gmc_merchant_idVARCHAR(50)nullableGoogle Merchant Center account ID
google_sheet_idVARCHAR(255)nullableGoogle Sheets spreadsheet ID
google_sheet_urlTEXTnullableFull URL for admin reference
statusENUMNOT NULL, default activeactive, inactive, suspended, uninstalled
created_atTIMESTAMPNOT NULL, default now()Installation timestamp
updated_atTIMESTAMPNOT NULL, auto-updatedLast modification

products

Products are cached from the Shopify Admin API. Each product belongs to exactly one tenant and may have multiple variants. The product_type field is the key input to the category rules engine.

ColumnTypeConstraintsDescription
idUUIDPKInternal product identifier
tenant_idUUIDFK -> tenants.id, CASCADEOwning tenant
shopify_product_idBIGINTNOT NULLShopify numeric product ID
titleVARCHAR(500)NOT NULLProduct display name
product_typeVARCHAR(255)nullablee.g. Men-Tops-Hoodies & Sweatshirts
vendorVARCHAR(255)nullablee.g. Jordan Craig
tagsTEXT[]default {}Array of tag strings
statusENUMNOT NULL, default activeactive, archived, draft
created_atTIMESTAMPNOT NULLProduct creation time
updated_atTIMESTAMPNOT NULLLast update

Unique constraint: (tenant_id, shopify_product_id) – a product cannot be duplicated within a tenant.

variants

Variants are the atomic unit of the priority system. Every variant maps to exactly one GMC product via the generated gmc_product_id. The inventory_quantity field drives inventory-based scoring modifiers.

ColumnTypeConstraintsDescription
idUUIDPKInternal variant identifier
tenant_idUUIDFK -> tenants.id, CASCADEOwning tenant
product_idUUIDFK -> products.id, CASCADEParent product
shopify_variant_idBIGINTNOT NULLShopify numeric variant ID
skuVARCHAR(255)nullableMerchant SKU (e.g. 107438)
priceDECIMAL(10,2)default 0Variant price
inventory_quantityINTdefault 0Current stock level
gmc_product_idVARCHAR(255)nullableGenerated: shopify_US_{shopifyProductId}_{shopifyVariantId}
created_atTIMESTAMPNOT NULLRecord creation
updated_atTIMESTAMPNOT NULLLast update

Unique constraint: (tenant_id, shopify_variant_id) – variant IDs are unique within a tenant.

GMC Product ID Generation: The gmc_product_id is computed at the application layer when a variant is created or updated. The format matches what Shopify’s Google channel writes to GMC.

// src/utils/gmc.ts

export function generateGmcProductId(
  shopifyProductId: bigint,
  shopifyVariantId: bigint,
  countryCode: string = 'US'
): string {
  return `shopify_${countryCode}_${shopifyProductId}_${shopifyVariantId}`;
}

// Example:
// generateGmcProductId(8779355160808n, 46050142748904n)
// => "shopify_US_8779355160808_46050142748904"

priority_scores

This table stores the calculated priority output for each variant. It has a one-to-one relationship with variants (via the UNIQUE constraint on variant_id). The five custom label columns hold the exact strings written to the Google Sheets supplemental feed.

ColumnTypeConstraintsDescription
idUUIDPKScore record identifier
tenant_idUUIDFK -> tenants.id, CASCADEOwning tenant
variant_idUUIDFK -> variants.id, CASCADE, UNIQUEScored variant
prioritySMALLINTNOT NULL, default 3Calculated score 0-5
custom_label_0VARCHAR(100)nullablepriority-{0..5}
custom_label_1VARCHAR(100)nullableSeason: winter, spring, summer, fall
custom_label_2VARCHAR(100)nullableCategory group: outerwear-heavy, t-shirts, etc.
custom_label_3VARCHAR(100)nullableStatus: new-arrival, in-stock, low-inventory, etc.
custom_label_4VARCHAR(100)nullableBrand tier: name-brand, store-brand, off-brand
calculated_atTIMESTAMPNOT NULL, default now()When score was last computed
overrideBOOLEANNOT NULL, default falseTrue if merchant manually set this score
override_reasonTEXTnullableWhy the override was applied

Score constraint: The priority column must be between 0 and 5 inclusive. This is enforced at the application layer via Zod validation and at the database layer via a CHECK constraint added in the migration SQL.

category_rules

Category rules map product type patterns to base priority scores. Each rule can optionally be scoped to a specific season. The modifiers JSON column stores tag-based adjustments and other conditional logic.

ColumnTypeConstraintsDescription
idUUIDPKRule identifier
tenant_idUUIDFK -> tenants.id, CASCADEOwning tenant
product_type_patternVARCHAR(255)NOT NULLPattern to match against product types
seasonENUMnullablewinter, spring, summer, fall, or NULL for all seasons
base_prioritySMALLINTNOT NULLDefault score when this rule matches
modifiersJSONBdefault {}Tag adjustments, brand boosts, etc.
created_atTIMESTAMPNOT NULLRule creation
updated_atTIMESTAMPNOT NULLLast modification

Unique constraint: (tenant_id, product_type_pattern, season) – prevents duplicate rules for the same pattern and season.

Modifiers JSON structure:

{
  "tagAdjustments": {
    "NAME BRAND": { "adjustment": 1, "reason": "Premium brand boost" },
    "Sale": { "adjustment": -1, "reason": "Already discounted" },
    "DEAD50": { "override": 0, "reason": "Dead stock excluded" },
    "archived": { "override": 0, "reason": "Archived product excluded" }
  },
  "inventoryModifiers": {
    "zeroStock": { "override": 0, "reason": "Out of stock" },
    "lowStock": { "adjustment": -1, "threshold": 5 },
    "overstock": { "adjustment": -1, "threshold": 50 }
  },
  "newArrival": {
    "daysThreshold": 30,
    "minimumPriority": 5
  }
}

seasonal_calendars

Each tenant defines four seasonal calendars (or more if using micro-seasons). The category_overrides JSON column stores per-category priority overrides for that season.

ColumnTypeConstraintsDescription
idUUIDPKCalendar identifier
tenant_idUUIDFK -> tenants.id, CASCADEOwning tenant
nameVARCHAR(100)NOT NULLDisplay name (e.g. Winter 2026)
seasonENUMNOT NULLwinter, spring, summer, fall
start_monthSMALLINTNOT NULL1-12
end_monthSMALLINTNOT NULL1-12
category_overridesJSONBdefault {}Per-category seasonal priorities
created_atTIMESTAMPNOT NULLRecord creation
updated_atTIMESTAMPNOT NULLLast modification

Unique constraint: (tenant_id, season) – one calendar entry per season per tenant.

Category overrides JSON structure (Nexus example):

{
  "outerwear-heavy": 5,
  "hoodies-sweatshirts": 5,
  "headwear-cold": 5,
  "jeans-pants": 4,
  "joggers": 4,
  "long-sleeve-tops": 4,
  "headwear-caps": 3,
  "t-shirts": 2,
  "shorts": 0,
  "footwear-sandals": 0,
  "swim-shorts": 0
}

sync_logs

Every sync operation (Shopify product import, Google Sheets push, GMC Content API update) creates a log entry. This table serves as both an audit trail and a debugging tool.

ColumnTypeConstraintsDescription
idUUIDPKLog entry identifier
tenant_idUUIDFK -> tenants.id, CASCADEOwning tenant
sync_typeENUMNOT NULLshopify, gmc, sheet
statusENUMNOT NULLstarted, completed, failed, cancelled
products_syncedINTdefault 0Number of products successfully processed
errorsJSONBdefault []Array of error objects
started_atTIMESTAMPNOT NULL, default now()When the sync began
completed_atTIMESTAMPnullableWhen the sync finished

Errors JSON structure:

[
  {
    "variantId": "uuid-here",
    "shopifyVariantId": 46050142748904,
    "error": "GMC product not found",
    "timestamp": "2026-02-10T09:02:15Z"
  }
]

billing

One-to-one relationship with tenants. Tracks the Shopify recurring application charge state.

ColumnTypeConstraintsDescription
idUUIDPKBilling record identifier
tenant_idUUIDFK -> tenants.id, CASCADE, UNIQUEOwning tenant
shopify_charge_idBIGINTnullableShopify RecurringApplicationCharge ID
planENUMNOT NULLstarter, growth, pro, enterprise
statusENUMNOT NULLpending, active, frozen, cancelled, expired
activated_atTIMESTAMPnullableWhen the subscription became active
created_atTIMESTAMPNOT NULLRecord creation
updated_atTIMESTAMPNOT NULLLast modification

Indexing Strategy

Indexes are designed around the most common query patterns. Every table is indexed on tenant_id because every query is tenant-scoped. Composite indexes support the specific access patterns of each feature.

INDEX STRATEGY BY ACCESS PATTERN
=================================

  Products page (paginated, filtered):
    idx_products_tenant_type       --> WHERE tenant_id = ? AND product_type = ?
    idx_products_tenant_status     --> WHERE tenant_id = ? AND status = 'active'
    idx_products_tenant_shopify_id --> WHERE tenant_id = ? AND shopify_product_id = ?

  Priority dashboard (distribution chart):
    idx_priority_scores_tenant_priority --> WHERE tenant_id = ? GROUP BY priority

  Sync operations (find pending variants):
    idx_variants_tenant_inventory  --> WHERE tenant_id = ? AND inventory_quantity > 0
    idx_variants_gmc_product_id    --> WHERE gmc_product_id = ? (reconciliation)

  Sync history (recent first):
    idx_sync_logs_started_desc     --> ORDER BY started_at DESC

  Category rules lookup (scoring engine):
    idx_category_rules_tenant_season --> WHERE tenant_id = ? AND season = ?

  Score recalculation (find stale scores):
    idx_priority_scores_tenant_calc --> WHERE tenant_id = ? ORDER BY calculated_at

Prisma Middleware for Tenant Scoping

Every database query must be filtered by tenant_id to prevent data leakage between tenants. Rather than relying on developers to remember this in every query, AdPriority uses Prisma middleware that automatically injects the tenant scope.

// src/database/middleware/tenantScope.ts

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

/**
 * Prisma middleware that automatically scopes all queries to the
 * current tenant. The tenantId is injected into the Prisma client
 * extension context by the auth middleware.
 *
 * This prevents accidental cross-tenant data access.
 */
export function createTenantScopedClient(
  prisma: PrismaClient,
  tenantId: string
) {
  return prisma.$extends({
    query: {
      // Apply to all models that have a tenantId field
      $allModels: {
        async findMany({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId };
          }
          return query(args);
        },

        async findFirst({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId };
          }
          return query(args);
        },

        async findUnique({ model, args, query }) {
          // findUnique does not support adding arbitrary where clauses,
          // so we validate after fetch
          const result = await query(args);
          if (result && hasTenantId(model) && (result as any).tenantId !== tenantId) {
            return null; // Deny cross-tenant access
          }
          return result;
        },

        async create({ model, args, query }) {
          if (hasTenantId(model)) {
            args.data = { ...args.data, tenantId };
          }
          return query(args);
        },

        async update({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId } as any;
          }
          return query(args);
        },

        async updateMany({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId };
          }
          return query(args);
        },

        async delete({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId } as any;
          }
          return query(args);
        },

        async deleteMany({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId };
          }
          return query(args);
        },

        async count({ model, args, query }) {
          if (hasTenantId(model)) {
            args.where = { ...args.where, tenantId };
          }
          return query(args);
        },
      },
    },
  });
}

// Models that require tenant scoping (all except Tenant itself)
const TENANT_SCOPED_MODELS = new Set([
  'Product',
  'Variant',
  'PriorityScore',
  'CategoryRule',
  'SeasonalCalendar',
  'SyncLog',
  'Billing',
]);

function hasTenantId(model: string): boolean {
  return TENANT_SCOPED_MODELS.has(model);
}

Usage in the request lifecycle:

// src/api/middleware/auth.ts

import { createTenantScopedClient } from '../../database/middleware/tenantScope';

export async function authMiddleware(req: Request, res: Response, next: NextFunction) {
  const session = await validateShopifySession(req);
  if (!session) {
    return res.status(401).json({ error: { code: 'UNAUTHORIZED', message: 'Invalid session' } });
  }

  const tenant = await prisma.tenant.findUnique({
    where: { shopifyShopDomain: session.shop },
  });

  if (!tenant || tenant.status !== 'active') {
    return res.status(403).json({ error: { code: 'FORBIDDEN', message: 'Tenant not active' } });
  }

  // Create a tenant-scoped Prisma client for this request
  req.tenantId = tenant.id;
  req.db = createTenantScopedClient(prisma, tenant.id);

  next();
}

Token Encryption

Shopify access tokens are encrypted at rest using AES-256-GCM before being stored in the database. The encryption key is stored in the ENCRYPTION_KEY environment variable and never logged or exposed.

// src/utils/encryption.ts

import { createCipheriv, createDecipheriv, randomBytes } from 'crypto';

const ALGORITHM = 'aes-256-gcm';
const IV_LENGTH = 16;
const TAG_LENGTH = 16;

export function encrypt(plaintext: string): string {
  const key = Buffer.from(process.env.ENCRYPTION_KEY!, 'hex');
  const iv = randomBytes(IV_LENGTH);
  const cipher = createCipheriv(ALGORITHM, key, iv);

  let encrypted = cipher.update(plaintext, 'utf8', 'hex');
  encrypted += cipher.final('hex');

  const tag = cipher.getAuthTag();

  // Format: iv:tag:ciphertext (all hex-encoded)
  return `${iv.toString('hex')}:${tag.toString('hex')}:${encrypted}`;
}

export function decrypt(encryptedString: string): string {
  const key = Buffer.from(process.env.ENCRYPTION_KEY!, 'hex');
  const [ivHex, tagHex, ciphertext] = encryptedString.split(':');

  const iv = Buffer.from(ivHex, 'hex');
  const tag = Buffer.from(tagHex, 'hex');
  const decipher = createDecipheriv(ALGORITHM, key, iv);
  decipher.setAuthTag(tag);

  let decrypted = decipher.update(ciphertext, 'hex', 'utf8');
  decrypted += decipher.final('utf8');

  return decrypted;
}

Migration Strategy

AdPriority uses Prisma Migrate for schema evolution. Unlike the push-based approach used in some other projects on this NAS, migrations provide a reversible audit trail of every schema change.

Initial Setup

# Create the database and user on the shared postgres16 container
docker exec -it postgres16 psql -U postgres << 'EOF'
CREATE DATABASE adpriority_db;
CREATE USER adpriority_user WITH PASSWORD 'AdPrioritySecure2026';
GRANT ALL PRIVILEGES ON DATABASE adpriority_db TO adpriority_user;
\c adpriority_db
GRANT ALL ON SCHEMA public TO adpriority_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO adpriority_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO adpriority_user;
EOF

Running Migrations

# Development: create and apply migration
cd /volume1/docker/adpriority/backend
npx prisma migrate dev --name init

# Production: apply pending migrations only
npx prisma migrate deploy

# Generate the Prisma client after schema changes
npx prisma generate

Adding CHECK Constraints

Prisma does not natively support CHECK constraints, so they are added via a custom migration SQL file after the initial schema migration.

-- prisma/migrations/YYYYMMDDHHMMSS_add_check_constraints/migration.sql

-- Enforce priority score range 0-5
ALTER TABLE priority_scores
  ADD CONSTRAINT chk_priority_range
  CHECK (priority >= 0 AND priority <= 5);

-- Enforce month ranges 1-12
ALTER TABLE seasonal_calendars
  ADD CONSTRAINT chk_start_month_range
  CHECK (start_month >= 1 AND start_month <= 12);

ALTER TABLE seasonal_calendars
  ADD CONSTRAINT chk_end_month_range
  CHECK (end_month >= 1 AND end_month <= 12);

-- Enforce base_priority range 0-5
ALTER TABLE category_rules
  ADD CONSTRAINT chk_base_priority_range
  CHECK (base_priority >= 0 AND base_priority <= 5);

Data Retention Policy

TableRetentionCleanup Strategy
tenantsForeverSoft-delete via status = 'uninstalled'
productsWhile tenant activeCascade delete on tenant removal
variantsWhile tenant activeCascade delete on product removal
priority_scoresWhile tenant activeCascade delete on variant removal
category_rulesWhile tenant activeCascade delete on tenant removal
seasonal_calendarsWhile tenant activeCascade delete on tenant removal
sync_logs90 daysCron job deletes logs older than 90 days
billingForeverRetained for accounting and compliance

The sync log cleanup job runs daily:

// src/scheduler/cleanup.ts

import cron from 'node-cron';
import { prisma } from '../database/client';

// Run daily at 3:00 AM
cron.schedule('0 3 * * *', async () => {
  const cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - 90);

  const { count } = await prisma.syncLog.deleteMany({
    where: {
      startedAt: { lt: cutoff },
    },
  });

  console.log(`[cleanup] Deleted ${count} sync logs older than 90 days`);
});

Sample Seed Data

The following seed script creates a complete test environment using Nexus Clothing data.

// prisma/seed.ts

import { PrismaClient, PlanTier, Season } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // 1. Create Nexus tenant
  const tenant = await prisma.tenant.create({
    data: {
      shopifyShopDomain: 'nexus-clothes.myshopify.com',
      planTier: PlanTier.growth,
      status: 'active',
    },
  });

  // 2. Create seasonal calendars
  const seasons = [
    { name: 'Winter', season: Season.winter, startMonth: 11, endMonth: 2 },
    { name: 'Spring', season: Season.spring, startMonth: 3,  endMonth: 4 },
    { name: 'Summer', season: Season.summer, startMonth: 5,  endMonth: 8 },
    { name: 'Fall',   season: Season.fall,   startMonth: 9,  endMonth: 10 },
  ];

  for (const s of seasons) {
    await prisma.seasonalCalendar.create({
      data: {
        tenantId: tenant.id,
        name: s.name,
        season: s.season,
        startMonth: s.startMonth,
        endMonth: s.endMonth,
        categoryOverrides: getSeasonOverrides(s.season),
      },
    });
  }

  // 3. Create category rules (season-agnostic defaults)
  const defaults: Array<{ pattern: string; priority: number }> = [
    { pattern: 'outerwear-heavy',       priority: 3 },
    { pattern: 'outerwear-medium',      priority: 3 },
    { pattern: 'outerwear-light',       priority: 3 },
    { pattern: 'hoodies-sweatshirts',   priority: 3 },
    { pattern: 'jeans-pants',           priority: 4 },
    { pattern: 't-shirts',             priority: 3 },
    { pattern: 'shorts',               priority: 3 },
    { pattern: 'headwear-caps',        priority: 3 },
    { pattern: 'headwear-cold',        priority: 2 },
    { pattern: 'headwear-summer',      priority: 2 },
    { pattern: 'underwear-socks',      priority: 2 },
    { pattern: 'accessories',          priority: 2 },
    { pattern: 'joggers',             priority: 3 },
    { pattern: 'footwear-sandals',     priority: 2 },
    { pattern: 'footwear-shoes',       priority: 3 },
    { pattern: 'sweatpants',          priority: 3 },
    { pattern: 'long-sleeve-tops',     priority: 3 },
    { pattern: 'swim-shorts',         priority: 2 },
    { pattern: 'women-apparel',        priority: 2 },
    { pattern: 'exclude',             priority: 0 },
  ];

  for (const rule of defaults) {
    await prisma.categoryRule.create({
      data: {
        tenantId: tenant.id,
        productTypePattern: rule.pattern,
        basePriority: rule.priority,
        modifiers: {
          tagAdjustments: {
            'NAME BRAND': { adjustment: 1 },
            'Sale': { adjustment: -1 },
            'DEAD50': { override: 0 },
            'archived': { override: 0 },
            'warning_inv_1': { adjustment: -1 },
            'in-stock': { adjustment: 1 },
          },
        },
      },
    });
  }

  console.log('Seed complete: Nexus tenant with 4 seasons and 20 category rules');
}

function getSeasonOverrides(season: Season): Record<string, number> {
  const matrix: Record<Season, Record<string, number>> = {
    winter: {
      'outerwear-heavy': 5, 'hoodies-sweatshirts': 5, 'headwear-cold': 5,
      'jeans-pants': 4, 'joggers': 4, 'sweatpants': 4, 'long-sleeve-tops': 4,
      'outerwear-medium': 4, 'headwear-caps': 3, 't-shirts': 2,
      'shorts': 0, 'swim-shorts': 0, 'footwear-sandals': 0, 'headwear-summer': 0,
    },
    spring: {
      'jeans-pants': 4, 't-shirts': 4, 'outerwear-light': 4,
      'headwear-caps': 3, 'joggers': 3, 'hoodies-sweatshirts': 3, 'shorts': 3,
      'outerwear-heavy': 1, 'headwear-cold': 1,
    },
    summer: {
      'shorts': 5, 't-shirts': 5, 'swim-shorts': 5, 'footwear-sandals': 5,
      'headwear-summer': 4, 'headwear-caps': 3, 'jeans-pants': 3,
      'hoodies-sweatshirts': 1, 'outerwear-heavy': 0, 'headwear-cold': 0,
    },
    fall: {
      'jeans-pants': 5, 'hoodies-sweatshirts': 5,
      'outerwear-medium': 4, 'long-sleeve-tops': 4, 'joggers': 4,
      'headwear-cold': 3, 't-shirts': 3,
      'shorts': 1, 'footwear-sandals': 0,
    },
  };

  return matrix[season];
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Performance Considerations

Query Performance at Scale

The schema is designed to handle the Growth tier target of 10,000+ products per tenant (roughly 30,000-50,000 variants including size and color permutations). Key optimizations include:

ConcernSolution
Large product listsCursor-based pagination using id as cursor, indexed
Priority distributionPre-computed in priority_scores table, single COUNT + GROUP BY
Sync operationsBatch upserts using createMany with skipDuplicates
Category rule matchingRules loaded once per scoring run, matched in-memory
Seasonal lookupsCalendars cached in Redis with 1-hour TTL

Estimated Table Sizes (Nexus Baseline)

TableRows (Nexus)Rows (100 tenants)Row SizeTotal Size
tenants1100~500 B~50 KB
products5,582500,000~300 B~150 MB
variants~20,0002,000,000~200 B~400 MB
priority_scores~20,0002,000,000~250 B~500 MB
category_rules808,000~500 B~4 MB
seasonal_calendars4400~1 KB~400 KB
sync_logs~2,000200,000~500 B~100 MB
billing1100~200 B~20 KB

At 100 tenants averaging 5,000 products each, the total database size is approximately 1.2 GB – well within the capacity of the shared postgres16 container.


Summary

The database schema captures eight tables with clear ownership chains rooted in the tenants table. Every query is tenant-scoped through Prisma middleware, preventing cross-tenant data leakage. The Prisma ORM provides type-safe access with migration-based schema evolution, and the indexing strategy supports the key access patterns of the product listing, priority scoring, and sync pipeline features.

The schema is deliberately normalized at the variant level because variants are the atomic unit synced to Google Merchant Center. The priority_scores table maintains a one-to-one relationship with variants, keeping the scoring output separate from the product catalog cache. This separation allows the scoring engine to recalculate priorities without modifying the product data, and the sync pipeline to read scores without joining through the full product hierarchy.