02 — Databases & Multi-Tenancy
CEO Summary
Data isolation is critical for a multi-tenant SaaS. Each business gets its own set of databases (main, analytics, HR) — no tenant ever sees another tenant's data. Business value: Compliance, trust, enterprise sales. Key metrics: Tenant count, DB size per tenant, provisioning success rate. If this breaks: New signups fail; existing tenants may lose access if connection resolution fails.
Multi-Tenancy Model
Overview
Google Cloud SQL MySQL 8.0. All databases on same instance. Data isolated by tenant — each business gets its own set of databases.
Database Types
Master Database
Default name: ppos-cloudfresh-dev — override with environment variable MYSQL_MASTER_DATABASE (or MYSQL_PLATFORM_DATABASE) so staging clones or separate instances can use a different database name without code changes. Resolved in backend/config/tenantDatabase.js when getTenantConnection('master', 'main') is used.
Core tables: Tenants, Users, Organizations, tenant_employees, Sessions.
Platform barcode catalog: platform_product_catalog — platform-owned GTIN/barcode → title, brand, image (image_url: HTTPS URL or data:image/jpeg;base64,… stored as MEDIUMTEXT), JSON attributes (enrichment for inventory and POS; not tenant stock). Migrations: backend/migrations/create-platform-product-catalog.sql, alter-platform-catalog-image-url-mediumtext.sql (legacy VARCHAR→MEDIUMTEXT). The service can also CREATE TABLE IF NOT EXISTS and auto-widen image_url on first use.
Per-Tenant Databases
Prefixes (env): MYSQL_TENANT_DB_PREFIX (default dev-), MYSQL_ANALYTICS_DB_PREFIX (default dev-analytics-). Example: if prefix is staging-, main DB is staging-{slug}.
| Type | Name Pattern | Purpose |
|---|---|---|
| Main | {MYSQL_TENANT_DB_PREFIX}{slug} | Invoices, Customers, Staff, POS, Inventory |
| Analytics | {MYSQL_ANALYTICS_DB_PREFIX}{slug} | Business/sales analytics |
| HR | dev-hr-{slug} | Employees, payroll, PTO (separate resolver; pattern may differ) |
Template Databases
dev-template, dev-analytics-template, dev-hr-template — Schema only, no data.
Tenant Resolution Order
- Already resolved
- Master admin
- Session tenantSlug
- Headers (X-Tenant-Slug, X-Org-Id)
- Session tenantId
- API key
- Subdomain
- Dev fallback
- Otherwise 401
Connection Managers
tenantDatabase.js: getTenantConnection(tenantName, dbType) — main | analytics. No HR.
multiDatabaseManager.js: getConnection(tenantIdOrSlug, type) — MAIN | ANALYTICS | HR.
| Use Case | Use |
|---|---|
| POS, devices, catalog, inventory | tenantDatabase.getTenantConnection(slug, 'main') |
| HR routes | multiDatabaseManager.getConnection(slug, 'HR') |
| Auth, provisioning | multiDatabaseManager |
Tenant Provisioning
Trigger: POST /api/public/signup
Service: comprehensiveTenantProvisioning.js
- Validate signup data
- Create Organization, Tenant, User in master
- Generate slug
- Create DBs: dev-{slug}, dev-analytics-{slug}, dev-hr-{slug}
- Copy schema from templates
- Create owner user in tenant main
- Add owner to Staff
- Register in tenant_employees
- Create default catalog, locations
- On failure: rollback
Key Schema (Main Tenant)
Invoices, InvoiceLineItems, Products, CatalogItems, Customers, Staff, Devices, Locations, InventoryItems, inventory_ledger, pos_carts, Transactions, GiftCards, Tickets, Recipes, Preparables, SignageFolders, SignageAds, SignageDisplays (digital signage — created on first use; see 26 — Signage)
Database Naming
| Context | Pattern | Example |
|---|---|---|
| Master | MYSQL_DATABASE | ppos-cloudfresh-dev |
| Tenant main | dev-{slug} | dev-mariospizza |
| Tenant analytics | dev-analytics-{slug} | dev-analytics-mariospizza |
| Tenant HR | dev-hr-{slug} | dev-hr-mariospizza |
Tenant Configs (tenants/)
Per-tenant folders for local/dev: MasterTenant-Tenant001, MarioRossi-Tenant004, etc.
| Path | Purpose |
|---|---|
| config/settings.js | Tenant-specific settings |
| config/database.js | DB connection config |
| .env.local | Env vars for local run |
| Dockerfile, docker-compose.yml | Containerized run |
Used when running tenant-specific dev or Docker. Relationship: multiDatabaseManager reads from master DB; tenant configs are for local overrides.
Seeding the platform barcode catalog (optional)
Third-party dumps (e.g. Kaggle Universal Product Code Database) can jump-start enrichment: mostly UPC-A + product description, no images. Always read the dataset license on Kaggle before loading into production; many community datasets are for research — confirm redistribution/commercial use fits your policy.
Alternatives: Commercial barcode APIs (GS1, aggregators) for higher accuracy and support; category-specific open data (e.g. Open Food Facts) for groceries with richer attributes.
Repo script: backend/scripts/seed-platform-catalog-from-file.js — stream CSV/TSV into platform_product_catalog with source='import', INSERT IGNORE so existing GTINs (e.g. manual) are not overwritten. Example: cd backend && node scripts/seed-platform-catalog-from-file.js --file /path/to/file.csv --dry-run --limit 5000 then run without --dry-run. Copy the unzipped file to the server or run from a machine with DB access.
Migrations
Path: backend/migrations/
| File | Purpose |
|---|---|
| create-platform-product-catalog.sql | Master DB: platform_product_catalog (barcode/GTIN enrichment) |
| create-recipe-system-tables.sql | Recipes, preparables |
| update-pos-cart-schema.js | POS cart schema updates |
| create-ticket-settings-table.js | Ticket settings |
| create-ticket-categories-table.js | Ticket categories |
| add-parentId-to-ticket-categories.js | Category hierarchy |
Key Provisioning & Schema Scripts
Path: backend/scripts/
| Script | Purpose |
|---|---|
| create-template-database.js | Create main template |
| create-hr-template-database.js | Create HR template |
| create-hr-database-for-tenant.js | Provision HR DB for tenant |
| migrate-tenant-schemas.js | Apply migrations to tenants |
| update-template-database.js | Update template schema |
| add-seating-layouts-table.js | Seating layouts |
| initialize-tenant-databases.js | Initialize tenant DBs |
| setup-tenant-databases.js | Setup tenant DBs |
Other scripts: seeders (seed.js, seed-admin.js), fixes (fix-*.js), tests (test-*.js). See backend/scripts/ for full list.
Browser storage (not MySQL)
The POS offline mutation queue uses each user’s IndexedDB database pawspos_offline (object store queue). It is not a MySQL database and is not provisioned on the server. See 14 — Frontend and 06 — POS & Invoicing.