MySQL Tables Catalog¶

The relational system of record — the addo_* schema inherited from the older "Addo" platform, plus related tables. Accessed via mysql2/promise through two pools (webPool / jobsPool, see connection facts).

Validation legend: ✅ verified in code · 💻 code-only · ⚠️ tech debt/risk

⚠️ Column caveat. Unlike the Mongoose models, MySQL has no schema file in the repo — column lists below are inferred from query usage in the cited files. Treat any column not seen in a verified query as "verify in code" (via DESCRIBE <table>;). PK/FK notation is logical, not necessarily enforced by DB constraints.

Domain map¶

Domain	Tables
Business & auth	`addo_business`, `business_category`, `users`, `roles`, `addo_account_mapping`, `auth_tokens`, `LSP`
Customer	`addo_contacts`, `addo_contacts_mapping`, `customers_new`
Orders	`addo_orders`, `addo_order_items`, `store_events`
Menu & items	`addo_items_master`, `addo_menu_sections`, `addo_menu_items`, `pending_item_images`
Wallet & loyalty	`wallet`, `wallet_history`, `wallet_rules`, `wallet_faq`, `business_wallet_balance`, `loyalty_milestones`, `crm_points_allocation_queue`
Campaign & promo	`addo_promo_code_engine`, `promo_contact_usage_mapping`, `addo_promo_na_businessmapping`, `mis_reports`, `shortenedurls`
Integration & config	`fcm_config`, `link_config`, `link_widget_mapping`, `parent_integration_mapping`, `petPooja_business_mapping`
Forms & surveys	`addo_forms_leads`, `addo_questions`, `addo_questions_master`
Ops	`staff_incentive_mapping`, `addo_dashboard`

1. Business & auth¶

`addo_business` ✅¶

Purpose: merchant master — the core business/outlet record. id = child_business_id across the whole system; parentId = parent business ID.

Column	Notes
`id` (PK)	business/outlet ID (= child_business_id)
`parentId`	parent business reference
`name`	business name
`city` / `locality` / `address`	location
`latitude` / `longitude`	geo
`categoryId` (FK → `business_category`)	type
`isParent`	hierarchy flag
`status`	active/inactive

Queried in: authMiddleware.js, digitalBill.js, cityName.js. Relations: master; referenced by nearly every table via businessId.

`business_category` ✅¶

Purpose: business type master (F&B, Salon, Spa…).

Column	Notes
`id` (PK)	category ID
`name`	category name
`status`	active flag

Queried in: authMiddleware.js. Relations: 1:many → addo_business.

`users` ✅¶

Purpose: application user master (staff/admin accounts).

Column	Notes
`userId` (PK)	user ID
`userName`	login
`password`	hashed
`roleId` (FK → `roles`)	role
`apiToken`	session/API token
`firstName` / `lastName`	name
`uniquePin`	security PIN
`status`	active flag

Queried in: authMiddleware.js, adminMiddleware.js. Relations: many → roles; via addo_account_mapping → businesses.

`roles` ✅¶

Purpose: role/permission definitions (access control).

Column	Notes
`roleId` (PK)	role ID
`name`	Admin / Manager / Staff
`status`	active flag

Queried in: authMiddleware.js, adminMiddleware.js. Relations: 1:many → users.

`addo_account_mapping` ✅¶

Purpose: user↔business assignment — which businesses a user manages.

Column	Notes
`userId` (FK → `users`)	user
`businessId` (FK → `addo_business`)	business
`role` / `district` / `state`	business-scoped attributes
`status`	active flag

Queried in: authMiddleware.js. Relations: many → users; many → addo_business.

`auth_tokens` 💻¶

Purpose: multi-session token registry per user.

Column	Notes
`token` (PK)	API token string
`userId` (FK → `users`)	owner
`issuedAt` / `expiresAt`	lifetime — verify in code

Queried in: authMiddleware.js. Relations: many → users.

`LSP` 💻¶

Purpose: logistics service provider registry (delivery partners).

Column	Notes
`id` (PK)	provider ID
`name`	provider name
`status`	active flag

Relations: referenced by delivery orders. Column set — verify in code.

2. Customer¶

`addo_contacts` ✅¶

Purpose: customer master — unique person records, deduplicated by mobile. The MySQL anchor for the universal mobile key.

Column	Notes
`contactId` (PK)	unique contact ID
`mobileNo`	10-digit phone (customer key)
`email` / `name`	contact fields — verify in code

Queried in: digitalBill.js, automatedCampaignQueueNew.js, customer ingestion. Relations: 1:many → addo_contacts_mapping.

`addo_contacts_mapping` ✅¶

Purpose: customer↔business junction — links a contact to each business they transact with. contactMappingId is the FK that the wallet/loyalty/promo tables hang off.

Column	Notes
`id` (PK)	mapping ID (= contactMappingId)
`contactId` (FK → `addo_contacts`)	customer
`businessId` (FK → `addo_business`)	business
`token`	per-customer API token
`role` / `district` / `state`	attributes
`status`	active flag

Queried in: appMiddleware.js, digitalBill.js, automatedCampaignQueueNew.js. Relations: many → contact; many → business; 1:1 → wallet.

`customers_new` ✅¶

Purpose: alternative/newer customer table used in segmentation. ⚠️ Coexists with addo_contacts — two customer models is a debt point.

Column	Notes
`id` (PK)	customer ID
`mobileNo`	phone key
`businessId` (FK → `addo_business`)	business
`name` / `email`	contact — verify in code
`status`	active flag

Queried in: getOnlySegmentCount.js, customer syncing. Relations: many → addo_business.

3. Orders¶

`addo_orders` ✅¶

Purpose: order header — all transactions across POS, online, aggregators. Source-tagged.

Column	Notes
`id` (PK)	order ID
`businessId` (FK → `addo_business`)	receiving business
`contactMappingId` (FK → `addo_contacts_mapping`)	customer
`orderDate`	timestamp
`total` / `amount`	order value — verify exact column
`source`	POS / swiggy / zomato / uengage / petpooja
`paymentMode`	payment method
`status`	pending / completed / cancelled

Queried in: dashboard MIS aggregations, order ingestion controllers. Relations: 1:many → addo_order_items; many → addo_contacts_mapping. See Common-Queries → daily MIS.

`addo_order_items` ✅¶

Purpose: order line items — itemized detail per order.

Column	Notes
`orderId` (FK → `addo_orders`)	order
`itemId` (FK → `addo_items_master`)	item
`quantity`	units
`price` / `amount`	line total

Relations: many → addo_orders; many → addo_items_master.

`store_events` 💻¶

Purpose: operational business event log (open/close, promotions) for event-driven triggers.

Column	Notes
`businessId` (FK → `addo_business`)	business
`eventType`	category
`eventData`	details
`eventAt`	timestamp
`status`	event status

Relations: many → addo_business. Columns — verify in code.

`addo_items_master` ✅¶

Purpose: product/menu item catalog.

Column	Notes
`id` (PK)	item ID
`name`	item name
`categoryId` (FK)	category
`price`	base price
`status`	active flag

Relations: referenced by addo_order_items, addo_menu_items, pending_item_images.

`addo_menu_sections` 💻¶

Purpose: menu category hierarchy — groups for organizing items.

Column	Notes
`id` (PK)	section ID
`name`	section name

Relations: referenced by addo_menu_items.

`addo_menu_items` 💻¶

Purpose: menu item records with section organization.

Column	Notes
`id` (PK)	menu item ID
`sectionId` (FK → `addo_menu_sections`)	section
`name` / `price`	verify in code

Relations: many → addo_menu_sections.

`pending_item_images` 💻¶

Purpose: media upload queue — pending item image processing.

Column	Notes
`itemId` (FK → `addo_items_master`)	item
`imageUrl`	uploaded path
`status`	0 pending / 1 processed

Relations: many → addo_items_master.

5. Wallet & loyalty¶

⚠️ This is where the non-atomic loyalty risk lives: customer points/balances are maintained here and partially in Mongo. See High-Level Architecture §9 and Technical Debt.

`wallet` ✅¶

Purpose: customer prepaid/loyalty balance per customer-business pair. The primary campaign-audience source — segment extraction queries hit this table.

Column	Notes
`id` (PK)	wallet ID
`contactMappingId` (FK → `addo_contacts_mapping`)	customer-business pair
`mobileNo`	denormalized customer phone
`parentBusinessId`	business hierarchy
`available`	current balance
`validTillDate`	balance expiry (loyalty)

Queried in: automatedCampaignQueueNew.js, getOnlySegmentCount.js, digitalBill.js. Hot query filters on (parentBusinessId, validTillDate) — see Common-Queries → wallet. Relations: 1:1 → addo_contacts_mapping; 1:many → wallet_history.

`wallet_history` ✅¶

Purpose: wallet transaction audit log — every credit/debit/reversal per customer.

Column	Notes
`contactMappingId` (FK)	customer
`type`	credit / debit / reversal
`amount`	txn amount
`date`	txn date

Queried in: walletMonthlyBalance.js. Relations: many → wallet.

`wallet_rules` ✅¶

Purpose: loyalty program config — earning model, thresholds, rates.

Column	Notes
`parentBusinessId` (FK)	org
`loyalty_type`	points / tiered / …
thresholds, earning rates	verify in code

Queried in: processCommonIngestion.js. Relations: many → parent business.

`wallet_faq` 💻¶

Purpose: loyalty help/FAQ content served by API.

Column	Notes
`id` (PK)	ID
question / answer	content — verify in code

Utility table (no FK).

`business_wallet_balance` 💻¶

Purpose: business-level CRM credit balance summary (distinct from customer wallet).

Column	Notes
`businessId` (FK → `addo_business`)	business
`balance`	current credit
`lastUpdated`	sync timestamp

Relations: many → addo_business. Mirrors Mongo business_config.wallet_balance — reconciliation risk ⚠️.

`loyalty_milestones` 💻¶

Purpose: loyalty tier/milestone achievement events.

Column	Notes
`contactMappingId` (FK)	customer
`milestoneType`	Gold / Platinum / …
`achievedAt`	timestamp

Relations: many → addo_contacts_mapping.

`crm_points_allocation_queue` ✅¶

Purpose: pending loyalty-point credits awaiting processing (MySQL-side work queue).

Column	Notes
`contactMappingId` (FK)	customer
`points`	points to credit
`orderId` (FK → `addo_orders`)	source order (optional)
`status`	0 pending / 1 processed

Queried in: loyalty point ETL crons. Relations: many → addo_contacts_mapping.

`addo_promo_code_engine` ✅¶

Purpose: coupon/promo code master.

Column	Notes
`id` (PK)	promo ID
`code`	promo code string
`campaignId` (FK)	campaign
`parent_business_id`	org
`status`	active flag

Queried in: uengageProcessDynamicCouponCodeROICalculationHelper.js. Relations: 1:many → promo_contact_usage_mapping.

`promo_contact_usage_mapping` ✅¶

Purpose: coupon redemption ledger — which customer used which coupon (ROI attribution).

Column	Notes
`contactMappingId` (FK)	customer
`promoCodeId` (FK → `addo_promo_code_engine`)	coupon
redemption details	verify in code

Queried in: getOnlySegmentCount.js (WHERE clauses). Relations: many → wallet/contact; many → addo_promo_code_engine.

`addo_promo_na_businessmapping` 💻¶

Purpose: promo exclusion rules — businesses excluded from a promo.

Column	Notes
`promoId` (FK → `addo_promo_code_engine`)	promo
`businessId` (FK → `addo_business`)	excluded business

Relations: many-to-many promo × business.

`mis_reports` ✅¶

Purpose: campaign MIS time-series — hourly/daily campaign metrics. ⚠️ Dynamic columns (<countType> like sms_count, push_count) are inserted at runtime by processCampaign.js — the schema is not fixed.

Column	Notes
`reportId` (PK)	report ID
`userId`	campaign owner
`year` / `month` / `date` / `hour`	time partitioning
`count`	total sends in period
`<countType>`	runtime-added metric columns
`insertedAt`	creation timestamp

Queried in: processCampaign.js. Fact table (denormalized).

`shortenedurls` ✅¶

Purpose: URL shortener — campaign link tracking.

Column	Notes
`id` (PK)	ID
`businessId` (FK → `addo_business`)	owner
`short_url`	short code
`status`	active flag

Queried in: uengageProcess.js, automatedCampaignQueueNew.js. Relations: many → addo_business.

7. Integration & config¶

`fcm_config` ✅¶

Purpose: Firebase Cloud Messaging credentials per business.

Column	Notes
`id` (PK)	ID
`businessId` (FK → `addo_business`)	business
`fcm_key`	service-account JSON / API key
`status`	active flag

Queried in: newFcmSendCode.js, push setup. Relations: many → addo_business.

`link_config` ✅¶

Purpose: digital-bill / link tracking config per business.

Column	Notes
`id` (PK)	ID
`businessId` (FK → `addo_business`)	business
`parentId` (FK)	parent
`bill_platform`	enabled platforms (JSON/delimited)
`mode`	0 disabled / 1 enabled
`status`	active flag

Queried in: digitalBill.js. Relations: many → addo_business.

`link_widget_mapping` 💻¶

Purpose: link/widget appearance customization.

Column	Notes
customization fields	verify in code

Utility table for link/widget rendering.

`parent_integration_mapping` 💻¶

Purpose: third-party aggregator credentials (Zomato, Swiggy) per parent org.

Column	Notes
`parentBusinessId` (FK)	org
`integrationName`	integration type
credentials/tokens	verify in code

Used by: order ingestion from aggregators. Relations: many → parent business.

`petPooja_business_mapping` ✅¶

Purpose: PetPooja POS integration config.

Column	Notes
`businessId` (FK → `addo_business`)	business
`petpooja_outlet_id`	outlet ID in PetPooja
`status`	active flag

Queried in: petPoojaHelpers.js. Relations: many → addo_business.

8. Forms & surveys¶

`addo_forms_leads` 💻¶

Purpose: lead-generation form submissions.

Column	Notes
`id` (PK)	lead ID
`businessId` (FK → `addo_business`)	business
`leadData`	form fields (JSON/text)
`submittedAt`	timestamp
`status`	lead status

Relations: many → addo_business.

`addo_questions` 💻¶

Purpose: dynamic survey/form question definitions.

Column	Notes
`id` (PK)	question ID
`businessId` (FK → `addo_business`)	business
`questionText`	prompt
`questionType`	text / select / …
`options`	choices

Relations: many → addo_business; references addo_questions_master.

`addo_questions_master` 💻¶

Purpose: reusable pre-defined question library.

Column	Notes
`id` (PK)	ID
`questionText`	prompt
`category`	category

Relations: referenced by addo_questions.

9. Ops¶

`staff_incentive_mapping` 💻¶

Purpose: staff commission/incentive tracking per business.

Column	Notes
`staffId` (FK)	staff
`businessId` (FK → `addo_business`)	business
`incentiveRate`	% or fixed
`status`	active flag

Relations: many → addo_business.

`addo_dashboard` 💻¶

Purpose: per-user/business dashboard layout config.

Column	Notes
`businessId` (FK → `addo_business`)	business
`layoutConfig`	widget layout (JSON)

Relations: many → addo_business.

Database Overview · MongoDB Collections · Common Queries
High-Level Architecture · Modules · Technical Debt

MySQL Tables Catalog¶

Domain map¶

1. Business & auth¶

addo_business ✅¶

business_category ✅¶

users ✅¶

roles ✅¶

addo_account_mapping ✅¶

auth_tokens 💻¶

LSP 💻¶

2. Customer¶

addo_contacts ✅¶

addo_contacts_mapping ✅¶

customers_new ✅¶

3. Orders¶

addo_orders ✅¶

addo_order_items ✅¶

store_events 💻¶

4. Menu & items¶

addo_items_master ✅¶

addo_menu_sections 💻¶

addo_menu_items 💻¶

pending_item_images 💻¶

5. Wallet & loyalty¶

wallet ✅¶

wallet_history ✅¶

wallet_rules ✅¶

wallet_faq 💻¶

business_wallet_balance 💻¶

loyalty_milestones 💻¶

crm_points_allocation_queue ✅¶

6. Campaign & promo¶

addo_promo_code_engine ✅¶

promo_contact_usage_mapping ✅¶

addo_promo_na_businessmapping 💻¶

mis_reports ✅¶

shortenedurls ✅¶

7. Integration & config¶

fcm_config ✅¶

link_config ✅¶

link_widget_mapping 💻¶

parent_integration_mapping 💻¶

petPooja_business_mapping ✅¶

8. Forms & surveys¶

addo_forms_leads 💻¶

addo_questions 💻¶

addo_questions_master 💻¶

9. Ops¶

staff_incentive_mapping 💻¶

addo_dashboard 💻¶

Related¶

`addo_business` ✅¶

`business_category` ✅¶

`users` ✅¶

`roles` ✅¶

`addo_account_mapping` ✅¶

`auth_tokens` 💻¶

`LSP` 💻¶

`addo_contacts` ✅¶

`addo_contacts_mapping` ✅¶

`customers_new` ✅¶

`addo_orders` ✅¶

`addo_order_items` ✅¶

`store_events` 💻¶

`addo_items_master` ✅¶

`addo_menu_sections` 💻¶

`addo_menu_items` 💻¶

`pending_item_images` 💻¶

`wallet` ✅¶

`wallet_history` ✅¶

`wallet_rules` ✅¶

`wallet_faq` 💻¶

`business_wallet_balance` 💻¶

`loyalty_milestones` 💻¶

`crm_points_allocation_queue` ✅¶

`addo_promo_code_engine` ✅¶

`promo_contact_usage_mapping` ✅¶

`addo_promo_na_businessmapping` 💻¶

`mis_reports` ✅¶

`shortenedurls` ✅¶

`fcm_config` ✅¶

`link_config` ✅¶

`link_widget_mapping` 💻¶

`parent_integration_mapping` 💻¶

`petPooja_business_mapping` ✅¶

`addo_forms_leads` 💻¶

`addo_questions` 💻¶

`addo_questions_master` 💻¶

`staff_incentive_mapping` 💻¶

`addo_dashboard` 💻¶