CalAssist Mortgage Fund
2026 · OsaaSSLA reporting for state agencies → faster SQL, aging reports, Lambda + RDS patterns.
- MariaDB
- Lambda
- Node
- SQL
- Sequelize
Full story Close
Challenge
State agency case managers needed timely visibility into disaster-relief mortgage assistance: which applications were aging past SLA, how workloads compared across programs, and whether reporting stayed accurate when federal rules and business calendars (including holidays) changed. The stack mixed Angular and Spring Boot with MariaDB and asynchronous work in AWS Lambda, so performance and observability had to hold up under concurrent agency users.
Approach
I focused on the data layer first: query plans, indexing, and refactoring heavy multi-join SQL so dashboards and aging reports could run without table scans. I built and tuned REST APIs in Spring Boot with validation and consistent error shapes, and moved appropriate work to Lambda with buffered logging and Secrets Manager integration so serverless handlers could talk to RDS through proxy-friendly connection patterns. Where reporting needed cross-program aggregates, I used multi-CTE queries and careful normalization so metrics stayed explainable to stakeholders.
Outcome
- Average query time dropped ~40% on the hottest paths after schema and query work.
- Aging and SLA reports gave managers a clear queue of at-risk cases.
- Lambda-based async work reduced load on synchronous APIs with audit-friendly logging.
Impact
~40% faster queries on targeted routes; fewer production errors from API validation; clearer operational story for state partners reviewing relief metrics.
Details
- Collaborated with agency stakeholders to encode business-day SLA and holiday rules in reporting logic.
- Sequelize singleton + secrets patterns aligned with RDS Proxy expectations in Lambda.