The Starting Point
A brief overview of my journey — from writing my first SQL query to building AI-powered products.
I started my career working with databases. My first job at Mesasix Inc. was all about building the "behind the scenes" parts of web applications — designing how data is stored, writing code that retrieves it efficiently, and making systems run faster.
From there, I moved to Jarvis Analytics (part of Henry Schein One), where I spent nearly 5 years working on analytics for dental practices across North America. This is where things got interesting — and where I learned to work at serious scale.
Most recently, at CliniqApps, I made the jump to AI. I designed and built a complete AI-powered newsletter system from scratch — one that uses Amazon's AI services to create professional email templates through a chat conversation.
This e-book walks through everything I built, why it matters, and how it works — in plain English.
Making Sense of Dental Data
At Jarvis Analytics, I reverse-engineered 14 different dental software systems and built the reporting engine that powered financial insights for thousands of practices.
The Problem
Dental practices use software to manage everything: appointments, patient records, billing, insurance claims. But here's the catch — there are many different software systems out there, and they all store data differently.
One system might call a dental cleaning PROPHY, another calls it D1110, and a third might store it as Prophylaxis - Adult. Same service, three completely different names in three different databases.
Jarvis Analytics needed to pull financial data from ALL of these systems and show it in a single, unified dashboard. My job? Figure out how each one works and write the code that extracts the data correctly.
14 Systems, One Standard
Over three years, I reverse-engineered and built query sets for 14 dental practice management systems:
| System | Queries Built | Notable Challenge |
|---|---|---|
| Dentrix | 3,266 | Most widely used; complex fee schedule logic |
| Eaglesoft | 3,310 | Non-standard date handling, custom transaction types |
| Open Dental | 3,322 | Open-source; highly customizable schemas |
| Denticon | 3,228 | Multi-location cloud system |
| Dentrix Ascend | 3,190 | API-based, not direct database access |
| Cloud 9 | 3,126 | Orthodontic-focused data model |
| AbelDent | 3,126 | Canadian system, different insurance rules |
| Power Practice | 3,128 | Unique billing workflow |
| PBS Endo | 3,230 | Endodontics specialty, procedure-heavy |
| OMS Vision | 3,136 | Oral surgery specialty system |
| Ortho2 | 3,128 | Orthodontic contract and payment plans |
| Focus Ortho | 3,130 | Orthodontic treatment tracking |
| Denterprise | 3,250 | Enterprise multi-practice chains |
| Tracker | 3,130 | Minimal documentation available |
What the Reports Covered
Each dental practice needed to see the same set of financial and operational reports, regardless of which software they used. I built queries for 46 report modules including:
- Revenue & Financial: Production, collection, accounts receivable, aging, cash flow, deposit slips, end-of-day reports
- Insurance: Payor analysis, claims tracking, receivables by insurance carrier
- Operations: Appointments, huddle boards, front office metrics, hygiene recall rates
- Growth: New patient tracking, marketing ROI, practice potential analysis
- Staffing & Payroll: Provider productivity, payroll calculations, staffing metrics
- KPIs & Dashboards: Key performance indicators, benchmark comparisons, provider dashboards
Matchback Lead Tracking
One of the most impactful things I built was the Matchback system. Here's the business problem: a dental practice runs a marketing campaign (postcards, Google Ads, etc.) and new patients come in. But how do you know which campaign brought which patient?
I wrote stored procedures that match marketing campaign data against new patient records — looking at timing, zip codes, and referral sources to connect the dots. This let practices see exactly which campaigns were worth the money and which weren't.
Unified Database & ODS Schema Mapping
Because every dental system stores data differently, we needed a way to combine them all into one standardized structure. I contributed to the unified reporting database — a single schema that could hold data from any of the 14 systems in the same format.
I also owned the ODS (Operational Data Store) schema mapping — translating the MySQL-based queries into Amazon Redshift format for cloud-scale analytics. Redshift is a data warehouse designed to handle massive amounts of data, and it uses slightly different SQL syntax, so each query had to be adapted.
Building the Sync Pipeline
I built a data synchronization system that keeps dental practice data flowing from Dentrix Ascend into our analytics database — in real time.
Why a Sync Pipeline?
Most of the dental systems I worked with gave us direct access to their database — we could connect and run our queries against their data. But Dentrix Ascend was different. It's a cloud-based system, and the only way to get data out was through their API.
The challenge: Dentrix Ascend has 40+ different types of data (patients, appointments, transactions, insurance claims, etc.), and we needed all of it synced into our own database so we could run our 3,000+ analytics queries against it.
How It Works
I built the sync pipeline in Python, running on Amazon Web Services (AWS). It supports three modes:
1. Full Sync
Downloads everything from scratch. Used when setting up a new dental practice or when data needs a complete refresh. This can take a while because it pages through every record.
2. Incremental Sync
Only downloads what changed since the last sync. Runs automatically on a schedule. Much faster than a full sync because it only asks for records modified in the last few days.
3. Real-Time Streaming
Listens for changes as they happen. When a receptionist books an appointment or a payment comes in, the change flows into our database within seconds.
The Architecture
- Step Functions — An orchestrator. It says "first sync patients, then appointments, then transactions" and manages the whole workflow.
- Lambda — Runs code without needing a server. Each sync task runs as a Lambda function — AWS handles starting it, running it, and shutting it down automatically.
- SQS (Simple Queue Service) — A message queue. When there's more data to process (next page of results), it puts a message in the queue, and another Lambda picks it up. This prevents data loss if something crashes mid-sync.
- DynamoDB — Tracks which syncs are running, when they last succeeded, and prevents two syncs from running at the same time.
Scale
The system handles complex data relationships too. A single "appointment" from the API might contain linked procedures, visit records, and practice procedures — all of which need to be split into separate tables. The most complex endpoint (fee schedules) fans out into 5 different tables with patient, location, provider, and insurance carrier associations.
The AI Newsletter Generator
At CliniqApps, I designed and built a complete AI-powered system that creates professional email newsletters through a chat conversation. This chapter covers the full journey.
The Vision
Healthcare businesses need to send newsletters to their patients — appointment reminders, health tips, seasonal updates. But creating a professional email newsletter is hard: you need design skills, copywriting, and technical knowledge of how emails render across different email clients (Gmail, Outlook, Apple Mail, etc.).
The idea: what if a user could just paste their website URL and have AI create a complete, branded newsletter for them?
That's what I built.
How It Works (The User's View)
- Paste a website URL — The system scrapes the website, extracts the company's logo, brand colors, services, images, and writing style.
- Pick a topic — The AI suggests newsletter topics based on the company's services (e.g. "Winter Dental Care Tips" for a dentist) with pre-generated subject lines.
- Set sender details — Name and email address for the newsletter.
- Generate — The AI creates 3 different newsletter layouts: Magazine, Digest, and Featured Story. Each is a fully formatted email ready to send.
- Refine through chat — Users can say things like "make the header blue", "change the font to Roboto", or click directly on text and images to edit them.
- Send a test email — Preview how it looks in a real inbox before sending to patients.
Under the Hood
The system is built across three separate codebases that work together:
| Component | Technology | What It Does |
|---|---|---|
| AI API Backend | TypeScript, Node.js, LoopBack 4 | Handles AI conversations, template generation, image processing, file storage |
| Web Frontend | PHP, Laravel, Blade, JavaScript | The page users see and interact with — chat interface, template previews, buttons |
| Email Builder | React, MJML | A widget for manually editing email templates after AI generates them |
The AI Brain: AWS Bedrock
The AI powering the newsletter generator is Claude (made by Anthropic), accessed through AWS Bedrock. But it's not just a simple chatbot — it's an agentic AI system with tools.
The AI has access to these tools:
validate_url— Check if a website URL is valid and reachableconduct_research— Scrape and analyze a company's websiteset_sender_settings— Save the sender name and emailgenerate_newsletter— Create 3 template variantsreplace_text— Swap text in a templatereplace_image— Swap an image in a templaterefine_template— Make structural changes to layoutsend_test_email— Send a preview to a real inbox- ...and several more for undo, redo, version history, image browsing, etc.
The 5-State Gating System
One big challenge with agentic AI: the AI might try to call a tool at the wrong time. For example, it shouldn't try to generate templates before the user has picked a topic, or change an image before any templates exist.
I solved this with a 5-state gating system. Depending on where the user is in the conversation, only certain tools are available:
| State | What's Happened | Tools Available |
|---|---|---|
| S0 | Nothing yet | URL validation, research |
| S1 | Research done | Set sender settings |
| S2 | Sender configured | Generate templates, load existing |
| S3 | Templates loaded (no sender) | Refine, replace, undo/redo |
| S4 | Full state | Everything — all tools available |
This prevents the AI from doing things out of order and keeps the conversation flowing naturally.
Real-Time Streaming (SSE)
When the AI generates a response, we don't wait for it to finish and then show everything at once. Instead, text streams to the user word by word, just like ChatGPT. This is done with Server-Sent Events (SSE).
The system uses 20+ different SSE event types:
content— AI text streaming word-by-wordbrand_analysis_ready— Research results are in, show the brand paneltemplates_ready— Templates generated, render the previewstool_use— AI is using a tool, show a spinnergeneration_started— Background generation kicked off, start pollingtext_click_mode/image_click_mode— Enable click-to-edit on previewsdone— Conversation turn complete
The Fast Refinement Engine
When a user says "change the header color to blue", we have two choices: ask the AI to regenerate the template (slow, ~8 seconds) or programmatically change it ourselves (fast, under 200 milliseconds).
I built a fast refinement engine with 8 specialized handlers that detect common changes and apply them directly to the template's data structure without involving the AI at all:
- Color swap — "Change background to #2C7A51"
- Font swap — "Change heading font to Playfair Display"
- Text replace — "Change the headline to Winter Wellness Tips"
- Font size — "Make the heading bigger"
- Button style — "Make buttons rounded"
- Spacing — "Add more padding around sections"
- Borders — "Add a border to the header"
- Color adjustment — "Make it darker" / "lighter"
Only complex structural changes (like "reorganize the layout" or "add a new section") fall back to the AI. This made the editing experience feel instant.
Click-to-Edit
Instead of describing what to change in text, users can click directly on elements in the template preview:
- Click on text — The AI asks what you'd like to change it to
- Click on an image — The AI shows replacement options from the brand's image library
- Click on a button — Edit the button text and link URL
Each click mode is mutually exclusive (you can't be in text-edit and image-edit mode at the same time), and the system carefully attaches and removes click handlers from the template previews, which are rendered inside iframes.
Background Generation & Polling
Generating 3 full newsletter templates takes 30-60 seconds. If we kept the connection open that long, the web server (Nginx) would time out with a 504 error.
The solution: background generation with polling.
The API kicks off template generation as a background task, immediately returns a job ID, and the frontend checks back every 3 seconds asking "are they ready yet?" When they're done, the templates appear. This keeps the connection short and avoids timeouts.
Undo, Redo & Version History
Every change to a template is saved to a history stack. Users can:
- Undo — "Go back" or "Undo that"
- Multi-undo — "Go back 3 steps"
- Redo — "Actually, put it back"
- Reset — "Start over" (jumps to the original version)
- Version jump — "Go to version 2" (jumps to any point in history)
The undo/redo system is smart enough to intercept these requests before they reach the AI, making them instant. It also labels each version with what changed ("Refined: changed header color to blue") so users can browse their edit history.
Security Hardening
Since this system handles file uploads, external URLs, and user data, security was critical. I addressed 12 categories of vulnerabilities:
| Vulnerability | What It Means | How I Prevented It |
|---|---|---|
| SSRF | An attacker tricks the server into making requests to internal systems (like AWS metadata) | DNS resolution + IP validation before every external fetch. Blocks private IPs. DNS pinning prevents rebinding attacks. |
| Path Traversal | An attacker uses ../../ in input to access files outside the allowed directory | Company IDs sanitized to alphanumeric only. No path separators allowed. |
| File Upload Abuse | Uploading a disguised malicious file (e.g. a script renamed to .jpg) | Content-type check + magic byte verification. A file must actually be an image, not just claim to be one. |
| HTML Injection | Inserting malicious HTML/JavaScript into email templates | All dynamic content escaped before insertion into templates. |
| Token Exposure | API authentication tokens visible in browser code | Server-side proxy architecture. Browser never sees the API token — Laravel handles auth server-side. |
| Rate Limiting | An attacker flooding the generation endpoint | Per-company rate limits on expensive operations. |
| Error Leakage | Stack traces or internal paths shown to users | All errors sanitized. Users see "Something went wrong" — developers see the full trace in server logs. |
89 50 4E 47, a JPEG with FF D8 FF. By checking these first few bytes, we can verify a file is actually what it claims to be — even if someone renamed a harmful file to photo.jpg.
Testing
The system has 790+ automated tests that run before every deployment. These aren't just "does the button work" tests — they cover:
- Tool gating (can the AI only use tools appropriate for the current state?)
- Fast refinement engine (does each of the 8 handlers produce correct output?)
- Security validators (does the SSRF check block
169.254.169.254? Does the file validator reject a renamed .exe?) - Background generation (does polling work? Are race conditions handled?)
- Undo/redo (does multi-undo work? Does redo clear when new changes are made?)
- Source code verification (do certain patterns still exist in the codebase? This catches accidental deletions.)
On top of that, I built a 49-check simulation script that runs through an entire AI conversation flow — from URL input to template generation to refinement — to catch any end-to-end issues before manual testing.
790+ automated tests · 49-check simulation script · 29-item security checklist · 20+ SSE event types · 8 fast-refinement handlers · 5-state tool gating · 3 template variants · 3 repos maintained in sync
The Full Picture
A look at the arc of my engineering career so far, and the skills that tie it all together.
What Changed, What Stayed
Looking back across these projects, the tools changed dramatically — from SQL stored procedures to Python pipelines to TypeScript AI systems. But the core skill stayed the same: understanding how data flows through a system and making it work reliably at scale.
At Jarvis, "data" meant dental practice records flowing from 14 different software systems into a unified reporting layer. At CliniqApps, "data" meant AI-generated content flowing through real-time streams, getting refined through user interactions, and being stored securely in the cloud.
The problems look different on the surface, but underneath they require the same engineering discipline: understand the system, build it carefully, test it thoroughly, and make it secure.
Skills Timeline
| Period | Role | Key Skills Gained |
|---|---|---|
| 2018–2021 | Web Developer, Mesasix | Database design, SQL, backend fundamentals |
| 2021–2024 | Lead Query Dev, Jarvis | Reverse engineering, large-scale SQL, AWS (Lambda, Step Functions, SQS), Python, data pipelines |
| 2024–2026 | Team Lead, Jarvis/HSOne | Leadership, system design, Redshift, schema standardization, Matchback analytics |
| 2026–Present | Full-Stack AI Dev, CliniqApps | TypeScript/Node.js, AI/LLM integration, prompt engineering, real-time systems, security hardening, React |
What I Bring to the Table
- I build complete systems. Not just a feature or a fix — I design and deliver full products from architecture to deployment.
- I work across the full stack. Backend APIs, frontend interfaces, cloud infrastructure, AI integration, database design — whatever the project needs.
- I take security seriously. Not as an afterthought, but as a first-class requirement with checklists and automated tests.
- I test obsessively. 790+ tests isn't a vanity metric — it's what lets you ship with confidence and refactor without fear.
- I document everything. Every system I've built has clear documentation so the next person (or future me) can understand it.
— Catherine Maglasang, 2026