Modularizing a Multi-Client Commission-Setting Service
Standardizing domain models and generalizing data-access behavior across heterogeneous client schemas in PostgreSQL and Apache Cassandra using configuration-driven metadata.
Confidentiality & Attribution Note: This case study reflects hands-on backend engineering contributions within a collaborative team environment. All employer names, client identities, internal endpoints, and proprietary schemas are anonymized. Code listings below are synthetic illustrations created specifically to explain the architectural pattern.
1. Context
In a fintech environment, a commission-setting service is responsible for storing, validating, retrieving, and publishing tiered commission rules used across transaction workflows. The service exposes Node.js and Express APIs, utilizes Redis for low-latency caching, coordinates asynchronous events through Google Cloud Pub/Sub, and integrates with BigQuery for downstream reporting workflows where applicable.
2. Problem: Schema Divergence & Duplication Risk
As different institutional clients onboarded or migrated onto the platform, their underlying storage requirements differed significantly:
- Some client environments stored rule definitions in relational PostgreSQL tables, while others relied on Apache Cassandra wide-column tables.
- Physical table names, selected column identifiers, and filter attributes varied across client configurations.
- Duplicating controllers, model logic, and data-access modules for each client variant made maintenance brittle—any update to validation or calculation rules risked drifting out of sync across implementations.
3. Constraints
- Backward Compatibility: Existing client API contracts and underlying database tables could not be forcibly renamed without breaking external integrations.
- Shared Domain Logic: Core commission calculations and model consumers needed a single, predictable data structure regardless of whether the record originated from PostgreSQL or Cassandra.
- Safe Configuration: Dynamic table and column selection had to rely on strictly controlled internal metadata—never raw user input—to prevent injection vulnerabilities.
4. Approach: Configuration-Driven Metadata & Standardized Output
Rather than branching business logic per client, the data-access layer was refactored around declarative client metadata and standardized output mappers:
// Synthetic demonstration code (not proprietary source)
export interface TenantDataConfig {
tenantId: string;
dbEngine: 'postgres' | 'cassandra';
logicalEntities: {
commissionRules: {
tableName: string;
selectColumns: Record<string, string>; // canonicalField -> physicalColumn
filterKeys: string[];
};
};
}
// Adapter normalizes database-specific rows into a single domain shape
export interface NormalizedCommissionRule {
ruleId: string;
tierCode: string;
rateBasisPoints: number;
effectiveTimestamp: string;
}
export function normalizeRow(
rawRow: Record<string, unknown>,
columnMap: Record<string, string>
): NormalizedCommissionRule {
return {
ruleId: String(rawRow[columnMap.ruleId] ?? ''),
tierCode: String(rawRow[columnMap.tierCode] ?? 'STANDARD'),
rateBasisPoints: Number(rawRow[columnMap.rateBasisPoints] ?? 0),
effectiveTimestamp: new Date(
String(rawRow[columnMap.effectiveTimestamp])
).toISOString(),
};
} 5. Illustrative Architecture
The diagram below illustrates how incoming API requests interact with the service layer, Redis cache, configuration-driven data adapters, and asynchronous Google Cloud Pub/Sub event flows:
Synchronous Request Path: Client → Node.js API → Service Layer → Configuration-Driven Data Access → PostgreSQL or Cassandra (with a parallel Redis Cache read/write path).
Asynchronous Event Path: Service Publisher → Message Broker (Google Cloud Pub/Sub or Redis Pub/Sub) → Subscriber Worker → Downstream Consumer (e.g., analytical sync or notifications).
6. Engineering Trade-Offs
What the Abstraction Simplified
- Consuming model functions received an identical JavaScript object shape regardless of storage engine.
- Onboarding a schema variant with renamed columns required updating validated metadata rather than cloning service files.
What Remained Engine-Specific
- Cassandra partition-key rules and PostgreSQL relational joins cannot be treated identically; low-level query builders remained tailored per database driver.
- Cache invalidation and Pub/Sub payload publishing required explicit lifecycle hooks after successful writes.
7. Quality, Testing & Operations
Refactoring shared data-access paths requires strong regression safety. Unit and integration tests written with Jest verified that each metadata configuration produced valid parameterized queries, handled empty or malformed driver responses gracefully, and preserved expected behavior across deployment pipelines.
8. Outcome
Consolidating the divergent data-access paths reduced copy-pasted query logic, simplified unit test maintenance, and made downstream model functions agnostic to physical column naming differences.
9. Lessons Learned
Working on configuration-driven backend services reinforced the value of compile-time schema validation (such as pairing TypeScript interfaces with runtime config validators at service startup) so misconfigured column mappings fail fast during CI checks rather than during runtime queries.