Platform internals

Database map

One production MySQL database holds the platform. Mongo holds only reference data. This page is the map you need before writing any query.

Where things live

Store	Contents
MySQL `safety_prod`	Everything operational: about 90 tables covering orgs, programs, products, offers, catalogs, cards, events, members, orders, invoices. Including `cards` and `cardevents`.
MongoDB (production cluster)	Reference only: `catalogs.niq_products` (about 5.8M NielsenIQ product docs used to build catalogs) and a job-scheduler queue. No member or card data.
DigitalOcean droplet	The scraper server. Runs the retail scrapers whose output lands in the droplet's own private Mongo, `scraping.listings`: Walmart (about 219,000 listings) and Kroger via the Fred Meyer banner (about 10,300 listings). Checked live 2026-07-31. Also hosts this internal site and its Ask service. There is no Safeway scraper; Safeway is an Albertsons banner and that data comes from Snowflake.
Snowflake	The Albertsons item feed. Source of Albertsons/Safeway product data (`UPC_ON_PACK` and friends) used for catalog builds. Not part of the live platform; it feeds catalog work.

ℹ️

Common mix-up: card data is not in MongoDB.

cardevents.SourceData is a JSON document, so people assume it lives in Mongo. It does not. It is a text column on a MySQL table. All card, member, and transaction data is MySQL; the Mongos hold only scraped and reference product data.

How the tables connect

Every join you need to get from one table to another, in plain language. "Real FK" means the database enforces the link; a string join is only a naming convention you must match yourself.

From → to	How they join	Real FK?
Catalog → its items	`incommcatalogitems.IncommCatalogId` = `incommcatalogs.Id` (the local Id, not `IncommId`)	Yes
Offer → its catalogs	`incommoffers.PrimaryCatalog` holds a comma-separated list of `incommcatalogs.Tag` values; split it, then match on Tag	No, tag strings
Offer → its products	`products.IncommOfferProductId` (a legacy string copy also sits in `products.IncommOffer`)	Yes
Offer → its banners	join table `incommoffer_x_banner`; banners are rows in `retailers`	Yes
Product → its programs	one `programproducts` row per attachment (`ProductId` + `ProgramId`)	Yes
Program → org → parent company	`programs.OrganizationId`, then `organizations.ParentCompanyId`	Yes
Advanced product → its children	one `groupedproducts` row per child (`AdvanceProductId` = parent, `ProductId` = child)	Yes
Order → line items → cards	`orderproducts.OrderId`, then `cards.OrderProductId`. This is also the only route from a card to its program: `cards → orderproducts → orders → ProgramId`	Yes
Card → its member	`cards.MemberId`	Yes
Card → its offer	`cards.OfferNumber` equals `incommoffers.Number`	No, string
Card → its replacement	`cards.ParentCardId` points at the replaced card	Yes
Card → events → per-item rows	`cardevents.CardId`; then `incommskuleveltransactions.CardEventId` (InComm) or `walmartfileimporttransactions.CardEventId` (Walmart)	Yes
Eligibility file → member	`membereligibilities.ExternalId` equals `members.ExternalId` (there is no MemberId column)	No, string

Table inventory

Layer	Tables (approx. rows)
Org	`parentcompanies` (15) · `organizations` (117) · `membergroups` (5.4k)
Program	`programs` (235) · `programproducts` (1.4k) · `programmembers` (424k)
Product	`products` (886) · `groupedproducts` · `groupedproductlogs` · `productcategories` (9)
Offer / catalog	`incommoffers` (205) · `incommcatalogs` (111) · `incommcatalogitems` (14.9M) · `incommoffer_x_banner` · `promocodes`
Card	`cards` (564k) · `cardevents` (2.3M) · `cardeventtypes` (13) · `incommskuleveltransactions` (1.3M) · `walmartfileimporttransactions` · `cardtemplates`
Member	`members` (214k) · `memberenrollments` · `membereligibilities`
Retail	`retailers` (85) · `networks` (66) · `retailer_x_network`
Commerce	`orders` (132k) · `orderproducts` (160k) · invoices and payments cluster · `journals` audit log (1.6M)

String joins vs real foreign keys

Three load-bearing relationships are not foreign keys. Queries that assume referential integrity here will lie to you:

cards.OfferNumber is a varchar snapshot of incommoffers.Number. Join on the string.
incommoffers.PrimaryCatalog is a comma-separated list of incommcatalogs.Tag values. You must split it before joining.
products.IncommOffer is a legacy string copy of the offer number. The real FK is products.IncommOfferProductId. Both are maintained; prefer the FK.

Conventions and traps

Money is in cents on InComm-facing fields. incommoffers.Value = 9200 means $92. Portal-facing fields named AmountUsd are dollars.
incommcatalogitems.IncommCatalogId points at the local incommcatalogs.Id. The InComm-side id is the separate IncommId column. Do not mix them.
membereligibilities has no MemberId. Match on ExternalId.
There is no card→program key. Go cards → orderproducts → orders → ProgramId.
UPCs in incommcatalogitems.Upc and in event items are 14-digit InComm-space values. See UPC format before comparing them to anything external.