Database — jbwst (MySQL 8.0.44 on RDS)

Inspected live 2026-07-07 (read-only: information_schema + routine definitions) against the production instance.

Instances & Proxies (verified)

	Production	Staging
Instance	jbw-st-integration (db.m6gd.xlarge)	jbw-staging-st-integration (db.t4g.medium)
Direct endpoint	jbw-st-integration.czu4p9n2bibm.us-east-1.rds.amazonaws.com	jbw-staging-st-integration.czu4p9n2bibm.us-east-1.rds.amazonaws.com
Proxy	jbw-prod-st-integration-proxy → targets jbw-st-integration	jbw-st-integration-proxy → targets jbw-staging-st-integration
Proxy endpoint	jbw-prod-st-integration-proxy.proxy-czu4p9n2bibm.us-east-1.rds.amazonaws.com	jbw-st-integration-proxy.proxy-czu4p9n2bibm.us-east-1.rds.amazonaws.com
Auth	IAM (rds-db:connect), managed secret rds!db-de12ec20-…	IAM, managed secret rds!db-18e624a3-…

⚠ Naming trap for the new team: the production instance is named jbw-st-integration (no “prod” in the name); only the proxy carries the prod prefix. Both proxies were available with healthy targets.

Tables (44) — grouped, with live row counts

ServiceTitan landing zone

Table	Rows	Purpose
tblInvoice / tblInvoiceItem	6.3M / 14.5M	Invoices + line items pulled by extraction
tblInvoiceHistory / tblInvoiceItemHistory	3.4M / 8.2M	Archive (rows older than 9 months, moved by jbw_loadHistoryTables)
tblSTLocationData / tblSTLocationDataHistory	15.2M / 8.0M	ST locations pulled per tenant for matching; archived after matching completes
tblSTInstalledEquipmentData	794K	ST installed equipment
tblSTLocationDataWithAgreement	474K	Locations enriched with agreement links
tblWorkizLocationData / tblWorkizAccounts	5.1K / 7	Workiz job-locations + account registry

JBW agreements & matching

Table	Rows	Purpose
tblJBWAgreementData	201K	Agreements pulled from the JBW public API per dealer
tblAgreementMatchingData	155K	Output of jbw_st_dataSync — ST location ↔ JBW agreement matches (UI Map/Approve grid)
tblAgreement / tblAgreementEquipments	71K / 215K	Agreements being created in JBW from invoices + their equipment
tblAgreementSynUpload	6.1K	CSV/file-upload agreement sync
tblSTRemoveAgreementTag	11.6K	Queue of expired/cancelled agreement tags to strip from ST (consumed by jbwagreementreferenceremovalfromst)
tblPlanRenewableMapping	9.8K	Renewal SKU → parent SKU mapping (drives renewal linking)

Claims

Table	Rows	Purpose
tblClaims	306K	Potential + filed claims; flags isClaimFiled, jbwClaimId, isReadyForST, isSentToST, ignoreInvoice

Dealers, tenants, reference

Table	Rows	Purpose
tblDealer	15.7K	Dealer master (incl. per-dealer claimWaitingPeriod, claimExceptionDays)
tblDealerOnboard / tblDealerRegistration	626 / 1.5K	Onboarding config / portal users (incl. email opt-in frequency)
tblTenant	610	ST tenants: OAuth creds, status, autoProcessAgreements, skipEquipment, batchId, syncStartDate
tblTenantCustomFields / tblTenantCustomTags	4.8K / 1.8K	Per-tenant ST custom field/tag ids (used by the ST write-back)
tblCustomFields / tblCustomTags	8 / 4	Master list of required ST fields/tags
tblProducts	5.2K	JBW product/SKU catalog (productCode, active)
tblBusinessUnit	7.6K	ST business units
tblCategories / tblServices / tblEquipmentType	336 / 2.1K / 19	Reference data
tblTransmissionDetails / …History	480 / 796K	Per-tenant sync audit (written by extraction)
tblEmailNotification	0	Notification inbox (currently empty)

Dormant / scratch

• tblVendor* (6 tables, all 0 rows) — an unused/future vendor integration surface.
• TempAgreementDates (132K), tempPaschalAgreementSync, tempStradaSTGAgreements — one-off migration/fix scratch tables left in place.

Views (12)

vwOnboardedDealers (drives the sync trigger), vwAgreements, vwJB360Agreements, vwClaims, vwPendingClaims, vwInvoices, vwAdminInvoices, vwagreementmatchdata, vwagreementnolocationdata, vwDealerEmailData, vwRegisteredDealers, vwWorkizDataPush.

Stored Procedures (10) — the matching/batch brain

Full definitions can be re-dumped anytime with SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='jbwst'.

jbw_st_dataSync(_dealerID, _platform) — the matching engine

Called by jbwagreementsdatamatch after each pull. For ServiceTitan:

1. Deletes unmapped rows for the dealer, excludes already-matched and expired/cancelled agreements.
2. Pass 1: match JBW agreement → ST location on first 5 chars of customer name (LIKE against locationName) + first 7 chars of street + zip5.
3. Pass 2 (leftovers): address-only match (street7 + zip5).
4. In both passes, agreements matching multiple locations are dropped from auto-match (left for manual mapping); remaining unmatched agreements are inserted with NULL location.
5. Flags hasDuplicateLocation when one location holds >1 agreement.

For Workiz (_platform='Workiz'): single-pass match on clientName (5 chars) + address (7 chars) against tblWorkizLocationData. There is no separate jbw_workiz_dataSync procedure — the code reference’s mention of one reflects a code-side name; in the live DB, Workiz is a branch of jbw_st_dataSync.

jbw_st_dataTransformation() — daily agreement factory (scheduled via jbwequipmentsyncstoredpro)

• Flags invoice items whose SKU looks like a JB plan (%JB%, %install%, %warranty%, %plan%).
• Creates tblAgreement rows from invoices whose line items match an active tblProducts SKU (last 7 days; Sea-Coast invoices get an S-suffixed product code).
• Links renewals: new agreements whose SKU is a renewal SKU (tblPlanRenewableMapping) get renewedAgreementId/parentProductCode from agreements already at the same location.
• Marks invoices/items processed, populates tblAgreementEquipments from installed-equipment line items.
• Auto-process flow per tenant flags (autoProcessAgreements, skipEquipment): sets isReadyForJBW=1, else writes a missing-data comment (start date / name / address / equipment).
• Sets isSentToST=1 once both location and equipment write-backs completed; backfills phone/email from the invoice.
• Finally CALLs jbw_multipleAgreements.

jbw_multipleAgreements()

When one invoice legitimately carries multiple warranty SKUs (or quantity > 1, since 2025-09-01), clones the agreement + equipment rows so each unit gets its own agreement (parentAgreementId set, createdBy='MultiAgreement_SP').

jbw_claimsData() — potential-claim detection (scheduled via jbwpendingclaimstoredproc)

• Candidate invoices: no agreement created, has job number, < 90 days old.
• Joins to in-force agreements at the same location/tenant, enforcing the coverage window: service date before expiration, after coverage start, more than tblDealer.claimWaitingPeriod days after start, within claimExceptionDays.
• Inserts tblClaims rows with status “Possible Claim identified within Invoice” (one per location, agreement ids GROUP_CONCATed).
• Auto-expires unfiled claims past claimExceptionDays → status “Claim ignored by Dealer” (dealers 18911 and 5904 exempted).

jbw_removeSTTagList()

Marks expired matches (isExpired=1) and PAID→EXPIRED agreements past warrantyEndDate, then queues location/installed-equipment tag removals into tblSTRemoveAgreementTag (consumed daily by prod_jbwagreementreferenceremovalfromst). Auto-marks removals done for inactive tenants and a hardcoded tenant exclusion list.

Supporting procedures

Procedure	Role
jbw_agreementSyncList(_dealerID,_dataType,_pageQuery)	Paged data for the UI match grid (joins matching data to JBW agreement data). ⚠ Appears defective as written: it string-concatenates its parameters into a variable and compares dealerId = queryParam instead of executing dynamic SQL — worth reviewing whether it’s still the live path (the Lambda may query directly).
jbw_getInvoiceDetails(_dealerId,_tenantId,_jobNumber)	Invoice lookup with JSON-aggregated items/agreements/claims/location-agreements (last 7 days or by job number, LIMIT 1000). Backs the invoice admin tools.
jbw_DealerEmailData(_reqType)	Builds the dealer notification-email dataset (respects emailOptIn + Daily/Weekly-Monday/Monthly-1st frequency), JSON per-tenant counts. Backs EmailPendingData.
jbw_loadHistoryTables()	Archival: moves invoices/items > 9 months old and matched tenants’ ST location data (500K/batch) into *History tables inside a transaction.
jbw_staging_cleanUp()	Nulls location fields on duplicate matches and re-flags hasDuplicateLocation (relies on a TempDuplicates table created by its caller).

Operational Notes

• Data ages: with tblSTLocationData at 15M rows and history at 8M, jbw_loadHistoryTables (and who schedules it) matters for keeping match performance acceptable — verify it is on a schedule (it wasn’t among the EventBridge targets; it may be invoked by a Lambda or manually).
• Matching is deliberately fuzzy (5-char name / 7-char street / zip5) — expect false negatives to land in the manual “Map Location” tab rather than false positives, since ambiguous matches are dropped.
• Several procedures embed hardcoded dealer/tenant IDs (claims exemptions 18911, 5904; tag-removal tenant list) — institutional knowledge the new team should confirm is still wanted.