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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK, default uuid | Internal tenant identifier |
shopify_shop_domain | VARCHAR(255) | UNIQUE, NOT NULL | e.g. nexus-clothes.myshopify.com |
shopify_access_token | TEXT | nullable | AES-256 encrypted at rest |
plan_tier | ENUM | NOT NULL, default starter | starter, growth, pro, enterprise |
gmc_merchant_id | VARCHAR(50) | nullable | Google Merchant Center account ID |
google_sheet_id | VARCHAR(255) | nullable | Google Sheets spreadsheet ID |
google_sheet_url | TEXT | nullable | Full URL for admin reference |
status | ENUM | NOT NULL, default active | active, inactive, suspended, uninstalled |
created_at | TIMESTAMP | NOT NULL, default now() | Installation timestamp |
updated_at | TIMESTAMP | NOT NULL, auto-updated | Last 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Internal product identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE | Owning tenant |
shopify_product_id | BIGINT | NOT NULL | Shopify numeric product ID |
title | VARCHAR(500) | NOT NULL | Product display name |
product_type | VARCHAR(255) | nullable | e.g. Men-Tops-Hoodies & Sweatshirts |
vendor | VARCHAR(255) | nullable | e.g. Jordan Craig |
tags | TEXT[] | default {} | Array of tag strings |
status | ENUM | NOT NULL, default active | active, archived, draft |
created_at | TIMESTAMP | NOT NULL | Product creation time |
updated_at | TIMESTAMP | NOT NULL | Last 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Internal variant identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE | Owning tenant |
product_id | UUID | FK -> products.id, CASCADE | Parent product |
shopify_variant_id | BIGINT | NOT NULL | Shopify numeric variant ID |
sku | VARCHAR(255) | nullable | Merchant SKU (e.g. 107438) |
price | DECIMAL(10,2) | default 0 | Variant price |
inventory_quantity | INT | default 0 | Current stock level |
gmc_product_id | VARCHAR(255) | nullable | Generated: shopify_US_{shopifyProductId}_{shopifyVariantId} |
created_at | TIMESTAMP | NOT NULL | Record creation |
updated_at | TIMESTAMP | NOT NULL | Last 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Score record identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE | Owning tenant |
variant_id | UUID | FK -> variants.id, CASCADE, UNIQUE | Scored variant |
priority | SMALLINT | NOT NULL, default 3 | Calculated score 0-5 |
custom_label_0 | VARCHAR(100) | nullable | priority-{0..5} |
custom_label_1 | VARCHAR(100) | nullable | Season: winter, spring, summer, fall |
custom_label_2 | VARCHAR(100) | nullable | Category group: outerwear-heavy, t-shirts, etc. |
custom_label_3 | VARCHAR(100) | nullable | Status: new-arrival, in-stock, low-inventory, etc. |
custom_label_4 | VARCHAR(100) | nullable | Brand tier: name-brand, store-brand, off-brand |
calculated_at | TIMESTAMP | NOT NULL, default now() | When score was last computed |
override | BOOLEAN | NOT NULL, default false | True if merchant manually set this score |
override_reason | TEXT | nullable | Why 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Rule identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE | Owning tenant |
product_type_pattern | VARCHAR(255) | NOT NULL | Pattern to match against product types |
season | ENUM | nullable | winter, spring, summer, fall, or NULL for all seasons |
base_priority | SMALLINT | NOT NULL | Default score when this rule matches |
modifiers | JSONB | default {} | Tag adjustments, brand boosts, etc. |
created_at | TIMESTAMP | NOT NULL | Rule creation |
updated_at | TIMESTAMP | NOT NULL | Last 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Calendar identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE | Owning tenant |
name | VARCHAR(100) | NOT NULL | Display name (e.g. Winter 2026) |
season | ENUM | NOT NULL | winter, spring, summer, fall |
start_month | SMALLINT | NOT NULL | 1-12 |
end_month | SMALLINT | NOT NULL | 1-12 |
category_overrides | JSONB | default {} | Per-category seasonal priorities |
created_at | TIMESTAMP | NOT NULL | Record creation |
updated_at | TIMESTAMP | NOT NULL | Last 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Log entry identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE | Owning tenant |
sync_type | ENUM | NOT NULL | shopify, gmc, sheet |
status | ENUM | NOT NULL | started, completed, failed, cancelled |
products_synced | INT | default 0 | Number of products successfully processed |
errors | JSONB | default [] | Array of error objects |
started_at | TIMESTAMP | NOT NULL, default now() | When the sync began |
completed_at | TIMESTAMP | nullable | When 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.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Billing record identifier |
tenant_id | UUID | FK -> tenants.id, CASCADE, UNIQUE | Owning tenant |
shopify_charge_id | BIGINT | nullable | Shopify RecurringApplicationCharge ID |
plan | ENUM | NOT NULL | starter, growth, pro, enterprise |
status | ENUM | NOT NULL | pending, active, frozen, cancelled, expired |
activated_at | TIMESTAMP | nullable | When the subscription became active |
created_at | TIMESTAMP | NOT NULL | Record creation |
updated_at | TIMESTAMP | NOT NULL | Last 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
| Table | Retention | Cleanup Strategy |
|---|---|---|
tenants | Forever | Soft-delete via status = 'uninstalled' |
products | While tenant active | Cascade delete on tenant removal |
variants | While tenant active | Cascade delete on product removal |
priority_scores | While tenant active | Cascade delete on variant removal |
category_rules | While tenant active | Cascade delete on tenant removal |
seasonal_calendars | While tenant active | Cascade delete on tenant removal |
sync_logs | 90 days | Cron job deletes logs older than 90 days |
billing | Forever | Retained 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:
| Concern | Solution |
|---|---|
| Large product lists | Cursor-based pagination using id as cursor, indexed |
| Priority distribution | Pre-computed in priority_scores table, single COUNT + GROUP BY |
| Sync operations | Batch upserts using createMany with skipDuplicates |
| Category rule matching | Rules loaded once per scoring run, matched in-memory |
| Seasonal lookups | Calendars cached in Redis with 1-hour TTL |
Estimated Table Sizes (Nexus Baseline)
| Table | Rows (Nexus) | Rows (100 tenants) | Row Size | Total Size |
|---|---|---|---|---|
tenants | 1 | 100 | ~500 B | ~50 KB |
products | 5,582 | 500,000 | ~300 B | ~150 MB |
variants | ~20,000 | 2,000,000 | ~200 B | ~400 MB |
priority_scores | ~20,000 | 2,000,000 | ~250 B | ~500 MB |
category_rules | 80 | 8,000 | ~500 B | ~4 MB |
seasonal_calendars | 4 | 400 | ~1 KB | ~400 KB |
sync_logs | ~2,000 | 200,000 | ~500 B | ~100 MB |
billing | 1 | 100 | ~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.