The challenge
A multi-location leisure and education operator runs classes across towns and activity categories, term by term. Office staff need an accurate picture of who is booked, which operational status applies, and which marketing segment each account should sit in for email campaigns.
That picture previously lived largely in spreadsheets: an internal marketing master, free-form statuses and manual CSV preparation for an email marketing platform. An external booking website was not integrated. Staff re-entered information. Term-end cleanup depended on people remembering rules that were easy to apply inconsistently.
Why it was complex
- Two audiences for the same facts: day-to-day operations vs marketing segmentation
- Many operational statuses mapping to fewer marketing statuses (not one-to-one)
- Context rules: sibling bookings in the same business grain change outcomes
- Open vs closed terms change which bookings drive “current” marketing state
- Term close must settle cohorts safely, block invalid states and leave an audit trail
- Customer identity must survive email corrections without splitting accounts
- Exports must stay aligned with the rules engine, not become another uncontrolled sheet
Our approach
We designed an operational system with an explicit rules engine:
- Lock the marketing grain (customer + category + town) under change control
- Separate layers: staff edit operational status; marketing status is always derived
- Encode precedence and context in SQL procedures with scenario tests
- Make terms first-class, with cohort settlement on close
- Build the staff UI around controlled lookups, saved views and server-side exports
- Host on Azure with Entra authentication and automated deploy across DEV / TEST / PROD
The solution
Operational application
React SPA for records (filterable, sortable, paginated grid) and admin (terms, saved views). Node/Express API on Azure App Service. Microsoft Entra ID via App Service Authentication.
Data model
Azure SQL dimensional model for customers, locations, categories, terms, bookings, marketing master, mapping tables and term-close audit.
Business rules
Stored procedures recalculate marketing status on booking changes. Precedence tiers, context statuses and open-term filtering are encoded and covered by SQL scenario packs plus CI scenario tests.
Term lifecycle
Closing a term runs cohort settlement with guards, transactional updates and before/after audit detail, then marks the term closed.
Automation and exports
Recalculation on save paths; operational CSV export; server-built marketing MasterView CSV shaped for email-platform import; historical import tooling for legacy CSV onboarding.
Identity, security and delivery
Entra sign-in for all routes; admin-gated maintenance; CI/CD build, lint, tests and deploy across DEV / TEST / PROD App Services.
Architecture
Platform flow
- Staff
- Microsoft Entra ID
- Azure application (SPA + API)
- API and business logic
- Azure SQL
- Resolution engine + term-close procedures
- Operational workflows and controlled exports
Platform at a glance
- 14→6Operational to marketing statuses
- 15SQL resolution scenarios
- ~32API routes
- 3Environments (DEV / TEST / PROD)
Key engineering decisions
Two-layer status model with locked grain
Problem
One free-text status cannot serve both operations and marketing.
Decision
Fourteen operational statuses mapped to six derived marketing statuses at customer + category + town.
Why
Matches how the business actually segments.
Result
Exports stop being a manual reinterpretation of the bookings sheet.
Derived marketing state in the database
Problem
Browser-side “last row wins” logic is opaque and untestable.
Decision
SQL resolution with precedence, context rules and term awareness, plus a scenario suite.
Why
Rules must be durable, reviewable and CI-testable.
Result
Same outcome whether a user edits one booking or closes a term.
Term close as a controlled batch
Problem
End-of-term cleanup in spreadsheets skips edge cases.
Decision
Cohort procedures with guards, one-time close protection and audit tables.
Why
Term boundaries change marketing truth; the system must prove what it did.
Result
Repeatable term operations with accountability.
Strict reference data on write
Problem
Auto-created statuses and missing locations poisoned the marketing layer.
Decision
Saves resolve names to existing dimension IDs only.
Why
Dirty dimensions break every downstream rule.
Result
Data quality at the point of entry.
Server-side marketing export
Problem
Spreadsheet pivots drift from the rules engine.
Decision
API builds the marketing CSV from the derived master table.
Why
One definition of marketing truth.
Result
Marketing prep becomes an export, not a rebuild.
Outcomes
Before
Spreadsheets as marketing system of record. Free-text or inconsistent statuses. Manual term-end reinterpretation. Error-prone MasterView assembly.
After
Azure SQL marketing master derived from rules. Controlled operational statuses. Audited term-close settlement. Server-generated marketing and operational exports.
This case study does not claim hours saved, error-rate percentages, or record volumes that were not confirmed for public use. Microsoft Fabric and Power BI were not part of this delivery.
Technology
Frontend
React, TypeScript, Vite
Backend
Node.js, Express, REST APIs
Cloud
Azure App Service, Microsoft Entra ID
Data & delivery
Azure SQL, T-SQL, GitHub Actions CI/CD, DEV / TEST / PROD
Our role
We owned domain modelling, business-rule design, Azure application architecture, API and UI implementation, SQL resolution, term-close design, export tooling, test scenarios, documentation and the deployment path.