Platform Architecture
← Back to homeThe end-to-end platform: every channel flows through two trust-zoned MuleSoft gateways — an edge gateway (:8060) for member-facing calls and an internal system gateway (:8061) for the service-to-service mesh — into the systems of record. IDMC CDI then moves the data from Postgres into Google BigQuery (raw → staging → analytics_idmc), where ALL transformations — cleansing, golden-record match/merge and the DQ rule marts — run as BigQuery SQL. From the Gold marts, activation-api writes clean golden records back to the CRM, POS and financial systems through the internal gateway, the vectorizer makes them AI-ready, and Grafana reads BigQuery directly.
Phase 2 — the process as it runs now
The live flow since 2026-09-25, re-verified on 2026-09-26: Informatica IDMC executes every batch lane on its own schedule, Informatica CAI and Database Ingestion handle the two real-time paths, MuleSoft governs every API call, the real Salesforce org is fed by both engines and feeds Data 360, and BigQuery gold is the one source for Tableau, Grafana and CLAIRE. Every Informatica asset lives in project Aries/aries.
Real time · ~2 sInformatica CAI (Application Integration) → Salesforce
Member event
sign-up · membership · booking · visit · payment (web · mobile · kiosk)
Postgres trigger
stadium_event_notify → pg_notify (event already in CAI input shape)
idmc-control relay
LISTEN → POST to CAI · burst guard 200 / 5 min · GET /events
cai_stadium_activate_member
cleanse + ACTIVATE / REVIEW decision (~0.3 s)
cai_stadium_sfdc_contact · _membership · _booking · _visit
service connector aries_stadium_sfdc_rest · OAuth client credentials · REST upsert on Golf_Member_Id__c
Salesforce record
Contact in ~2 s, then Golf_Membership__c / Golf_Booking__c / Golf_Visit__c
Fail-open: a sign-up never waits on Salesforce. The IDMC batch block later overwrites the same record with the golden values. cai_stadium_sync_payment mirrors payments to the loyalty profile.
Real time · < 1 minIDMC Database Ingestion CDC → BigQuery live views
Postgres logical replication
publication aries_stadium_cdc_pub · slot aries_stadium_cdc · REPLICA IDENTITY FULL
dbmi_stadium_cdc_rt
Database Ingestion task, CDC_COMBINED, Secure Agent aries-devtools-vm
BigQuery stadium_cdc
raw change tables, FlushPeriod-bound
analytics_rt views
33 live views with the same names and SQL as analytics_idmc (golden record, facts, DQ)
Tableau Live tab · Grafana
member visible ~20–30 s after the click
Measured 2026-09-26: CDC row 21 s, golden record in analytics_rt 7 s later. Watchdog cdc_watch every 5 min (job state, slot lag, freshness).
Per sign-up · ~4–6 minIDMC taskflow wf_mg_members (16 tasks)
Sign-up request
idmc-control coalesces bursts · held :28–:36 around the scheduled run
Land → raw
mt_mg_land_members → mt_mg_raw_members (Postgres → BigQuery)
Cleanse + DQ
mt_mg_stg_members (staging layer)
Golden record
match / merge survivorship → members_curated (person_key)
DQ scorecard
rule tasks → dq_scorecard · dq_results
Reverse-ETL
mt_mg_activation_payloads → mt_mg_revetl_crm · _toast · _accounting (upsert) + Salesforce golden enrichment
Per event · ~4 minIDMC taskflow wf_mg_activity (12 tasks)
Booking · visit · membership · payment
event-triggered by idmc-control
Land → raw
bookings · visits · memberships · payments
Marts
booking_facts · visit_facts · channel_mix · spend_events
Every 4 h at :35 · ~22 minIDMC schedule aries_golf_hourly → wf_mg_full (61 tasks)
Land 13 tables
members · memberships · bookings · visits · payments · pos_orders · gl_entries · billing_schedule · dunning · email_log · rule_events · audit_log · venues
Raw → staging
Bulk + write-truncate via GCS · cleanse + DQ flags
Gold marts
22 marts: golden records · facts · billing_health · member_vs_guest · rule_enforcement · DQ scorecard
Reverse-ETL
CRM · Toast · Accounting (IDMC upsert, non-empty-only contract)
Salesforce block (11 tasks)
Accounts → Contacts (golden overwrites CAI) → Memberships → Bookings · Visits · Activities · Invoices · DQ scorecard, with sf_ids read-backs · runs on the Informatica Cloud Hosted Agent
DQ circuit breaker: the Salesforce push is skipped if DQ-valid golden members < 80% of what Salesforce holds. Developer-Edition storage (5 MB): 60-day retention on bookings / visits / activities, Golf_Payment__c stays empty, Contact carries the lifetime-value roll-ups.
On file dropIDMC taskflow wf_mg_files (5 tasks)
Venue invoice Excel
watched drop folder
Land (Secure Agent flat file)
mt_mg_land_venue_invoices → BigQuery
Cleanse + DQ
mt_mg_stg_venue_invoices · 8 reject reasons
Valid → Accounting AR
mt_mg_venue_invoices_ready → mt_mg_post_ar_invoices (idempotent)
Rejects → quarantine
mt_mg_venue_invoice_quarantine, reason per row
Daily 06:40 + on demandIDMC Data Quality · Catalog · CLAIRE
CDQ profiles
DQ_Stadium_Members · _Payments · _Bookings (+ Esri) → rule scores
CDGC catalog
sources aries_golf_bq · aries_idmc_cdi · aries_golf_sfdc · glossary (6 terms) · data dictionary
Lineage
Postgres → IDMC mappings → BigQuery → Salesforce (Golf_Booking__c: 23 upstream assets, 5 hops)
CLAIRE GPT
catalog Q&A + live BigQuery data queries through the Secure Agent data gateway
MCP for agents
stadium-mcp gateway: stadium_bq_* · stadium_pg_* tools + Informatica dq_* / jobs_* tools
Systems, one by one
Salesforce + Data 360
| Org | Agentforce Developer Edition · integration user stadium-sync (API only) · Connected App “Aries Stadium CAI” (client credentials, IP-relaxed) · trusted IPs: VM + Informatica Cloud Hosted Agent |
| Objects | Contact + Account stadium fields (Stadium Member Id, Tier, DQ Score, DQ Valid, Home Venue, Lifetime Value, Visits/Payments count, Last Synced…) · Golf_Membership__c · Golf_Booking__c · Golf_Visit__c · Golf_Activity__c · Golf_Invoice__c · Golf_DQ_Scorecard__c · Golf_Payment__c (empty by design). Labels say Stadium; API names keep the Golf_ prefix on purpose (renaming = rebuilding IDMC, CDC and Data 360). |
| Writers | CAI real time (~2 s) and the wf_mg_full Salesforce block (every 4 h, golden values win) — both write the same records keyed on Golf_Member_Id__c |
| Data 360 streams | Salesforce CRM connector, batch upsert about every 20 min: Contact_Home · Account_Home · Golf_Membership__c_Home · Golf_Booking__c_Home · Golf_Visit__c_Home → data lake objects → data model (Individual, Contact Points, Golf_Booking__dlm, Golf_Membership__dlm, Golf_Visit__dlm) |
| Data 360 identity | Identity resolution ruleset “Stadium Members” (scheduled, 645 source → 645 unified; 0 merges because Contact is already the golden 1:1 view) |
| Data 360 insights | Calculated insights: Visit Engagement (days since last visit) · Booking Value · Membership Profile (tier, monthly revenue) → segment “Lapsing Gold Members” = Gold, revenue > 0, no visit for ≥ 21 days (4 members) → activation targets (none configured yet) |
MuleSoft Anypoint Flex Gateway
| External gateway :8060 (Local mode) | Routes /membership → membership-api and /pos → pos-sim. Policies in order: tiered rate limits by x-member-tier (bronze 30 · silver 120 · gold 600 per minute, untiered 300), CORS, governance headers (x-governed-by: mulesoft-flex, x-request-id, x-request-path), header injection, client-id enforcement (client_id + client_secret headers; the end-user Bearer is passed through as x-user-authorization) |
| Internal gateway :8061 (loopback, Anypoint-connected) | Routes /crm → fake-crm · /toast → michele-toast · /accounting → accounting-sim · /activation → activation-api · /vectorizer → vectorizer. Governs every service-to-service call; not reachable from the internet. |
| Callers | membership-web and admin-web server routes, pos-sim, live-traffic and activation-api call through the external gateway with the consumer credentials; membership-api reaches CRM / Toast / Accounting through the internal gateway (audit rows in audit.reverse_etl_writes). The mobile app is a public client: it calls the API with the member’s own Keycloak token, never with a gateway secret. |
| Verified | no credentials → 401 · with credentials → 200 + governance headers · bronze tier 30 × 200 then 429 · 108/108 app calls routed through the gateway, 0 direct |
Analytics: BigQuery gold → Tableau · Grafana · CLAIRE
| BigQuery (project michele-golf) | idmc_land / raw (STRING landing) → idmc_staging (cleansed) → analytics_idmc (22 gold marts + DQ tables, rebuilt by IDMC) · analytics_rt (33 live views on the CDC tables, same names) · authorized datasets so the read-only reporting account sees gold only |
| Tableau Cloud | Project “Michele Stadium's”: 11 tabs on live BigQuery — CEO overview · Stadium overview · Live (analytics_rt) · Members 360 · Venues & programs · Data trust · Lineage · Pipeline · Activation · Salesforce · Invoice files. Pulse: 4 metrics (new members, new-member DQ score, revenue, refunds) on Hyper extracts refreshed every 2 h. Built and published by API (publish.py / dashboard.py); a saved BigQuery credential (service account) must exist in Tableau for the live tabs. |
| Grafana | 5 dashboards on BigQuery: IDMC Pipeline · Venue Invoices → AR · Data Quality Command Center · DQ & Data Governance · Program Success (live panels read analytics_rt) |
| CLAIRE GPT | answers catalog questions and runs live BigQuery queries (data gateway on the Secure Agent); lineage and quality scores come from CDGC / CDQ |
| Optional BI mirror | Apache Doris + Superset on the same VM, refreshed every 3 h by wf_dobi_landing (no BigQuery cost) |
Applications, identity and systems of record
| Public surfaces | demo.stadium.ariesview.com (member site, Next.js) · demo.admin.ariesview.com (back office + DQ console) · demo.mobile.ariesview.com (Expo web) · kiosk POS :8081 · old golf names 301 to these |
| Identity | Keycloak realm topstadium: clients membership-web · admin-web · mobile-app · customer-web (public, password grant + PKCE). Self-registration is off — the apps create users through the admin API at sign-up. Demo credential: admin / demo everywhere (realm role admin); Caddy basic auth on the back-office consoles. |
| membership-api :8000 | system of engagement: /signup · /memberships · /checkout · /payments · /bookings · /members/lookup · /dq/* (admin) · /admin/*. Mirrors contacts to the CRM without a tier; the tier follows the membership purchase and is cleared on cancel. |
| Postgres topstadium | system of record (members, memberships, bookings, visits, payments, pos_orders, gl_entries, billing_schedule, dunning_attempts, email_log, rule_events, audit_log, venues) · idmc_src views chunk JSON for the IDMC string(255) limit · audit schema for reverse-ETL write-backs |
| Satellite systems | fake-crm (Salesforce-analog contacts) · michele-toast (loyalty profiles) · accounting-sim (AR ledger) · activation-api (reverse-ETL closed loop, Python, also on the gateway) · vectorizer (AI vector store built only from DQ-valid curated rows) · live-traffic (~10 organic sign-ups a day, cap 5000) · Mailpit (all e-mail) |
How fast each path is
| What | Latency | Path |
|---|---|---|
| Salesforce Contact | ~2 s | CAI cai_stadium_sfdc_contact (fired by membership-api at sign-up, fail-open) |
| Salesforce booking / visit / membership | ~1.5 s | Postgres trigger → idmc-control relay → CAI Upsert_Record |
| BigQuery change row | ~20–30 s | Database Ingestion CDC → stadium_cdc |
| Golden record in analytics_rt | ~30 s | live view over the CDC tables (Tableau Live, Grafana) |
| Golden record in analytics_idmc + reverse-ETL | ~4–6 min | wf_mg_members |
| Activity marts | ~4 min | wf_mg_activity |
| Full rebuild + Salesforce golden enrichment | every 4 h at :35, ~22 min | wf_mg_full (61 tasks) |
| Data 360 data streams | ~20 min | Salesforce CRM connector batch cadence |
| Tableau live tabs | on open | live BigQuery query (needs the saved credential) |
| Tableau Pulse | 2 h | Hyper extract refresh task |
| DQ profiles + catalog | daily 06:40 | CDQ profiles; CDGC rescans on demand |
What changed from Phase 1
| Area | Phase 1 | Phase 2 (now) |
|---|---|---|
| Orchestration | Python scheduler starting tasks | IDMC taskflows + IDMC schedule; Python (idmc-control) only relays events, triggers lanes and monitors |
| Real time | none (hourly batch) | CAI → Salesforce in ~2 s · Database Ingestion CDC → BigQuery live views in < 1 min |
| Reverse-ETL | activation-api (Python) via gateway | IDMC upsert tasks into CRM / Toast / Accounting (activation-api kept for the closed-loop demo) |
| Salesforce | fake-crm stand-in | Real Salesforce org (CAI + IDMC) and Data 360 on top of it: streams, identity resolution, insights, segment |
| New member latency | up to ~1 hour | ~2 s Contact · ~30 s live BigQuery · ~5 min golden record · ≤ 4 h full enrichment |
| Analytics | Grafana on ClickHouse | Grafana · Tableau Cloud (11 tabs + Pulse) · CLAIRE GPT · MCP tools — all on BigQuery gold |
| Governance | run log only | CDGC lineage Postgres → IDMC → BigQuery → Salesforce, CDQ scores, glossary; all assets in Aries/aries |
| API governance | one gateway, no enforcement | Two Flex Gateways: public routes with client-id + tiered rate limits, internal routes for service-to-service traffic |