Back to Home
Catherine Maglasang

From SQL to AI

How I built large-scale data systems for 14 dental platforms, a real-time sync pipeline, and an AI-powered newsletter generator — explained so anyone can understand.
by Catherine Maglasang
Full-Stack Engineer
Chapter 1

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.

What's a database? Think of it as a giant, organized spreadsheet. Instead of one file with a few tabs, a database can have hundreds of tables with millions of rows, and software can search through them in milliseconds.

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.


Chapter 2

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.

What's reverse engineering? It means figuring out how something works by studying it — without having the original instructions. I didn't have documentation for most of these dental systems. I had to explore their databases, find the right tables, understand how they connected, and map them to our reporting format.

14 Systems, One Standard

Over three years, I reverse-engineered and built query sets for 14 dental practice management systems:

SystemQueries BuiltNotable Challenge
Dentrix3,266Most widely used; complex fee schedule logic
Eaglesoft3,310Non-standard date handling, custom transaction types
Open Dental3,322Open-source; highly customizable schemas
Denticon3,228Multi-location cloud system
Dentrix Ascend3,190API-based, not direct database access
Cloud 93,126Orthodontic-focused data model
AbelDent3,126Canadian system, different insurance rules
Power Practice3,128Unique billing workflow
PBS Endo3,230Endodontics specialty, procedure-heavy
OMS Vision3,136Oral surgery specialty system
Ortho23,128Orthodontic contract and payment plans
Focus Ortho3,130Orthodontic treatment tracking
Denterprise3,250Enterprise multi-practice chains
Tracker3,130Minimal documentation available
By the numbers: 14 systems × ~3,200 queries each = 48,000+ SQL queries in total, covering 46 report categories.

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:

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.

What's a stored procedure? It's like a saved recipe inside the database. Instead of writing the same complex instructions every time, you save them once and just say "run the recipe." It's faster, reusable, and keeps the logic in one place.

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.

What's Amazon Redshift? It's a cloud database built for analytics. While a regular database handles day-to-day operations (read one patient record, update one appointment), Redshift is designed to crunch millions of records at once — perfect for generating reports across thousands of dental practices.

Chapter 3

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.

What's an API? An API (Application Programming Interface) is like a waiter at a restaurant. You (the client) can't walk into the kitchen (the database) yourself. Instead, you tell the waiter what you want, and they bring it to you. An API works the same way — you send a request, and it sends back data in a structured format.

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

Dentrix Ascend API → AWS Step Functions → Lambda (Python) → SQS Queue → MySQL Database
Breaking down the AWS services:

Scale

40+
API Endpoints
51
Database Tables
3
Sync Modes

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.


Chapter 4

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 URL → AI Researches Brand → Pick Topic → 3 Templates Generated → Refine via Chat → Send
  1. Paste a website URL — The system scrapes the website, extracts the company's logo, brand colors, services, images, and writing style.
  2. 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.
  3. Set sender details — Name and email address for the newsletter.
  4. Generate — The AI creates 3 different newsletter layouts: Magazine, Digest, and Featured Story. Each is a fully formatted email ready to send.
  5. 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.
  6. 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:

ComponentTechnologyWhat It Does
AI API BackendTypeScript, Node.js, LoopBack 4Handles AI conversations, template generation, image processing, file storage
Web FrontendPHP, Laravel, Blade, JavaScriptThe page users see and interact with — chat interface, template previews, buttons
Email BuilderReact, MJMLA widget for manually editing email templates after AI generates them
What's TypeScript? TypeScript is JavaScript with type safety. Regular JavaScript lets you accidentally pass a number where a text string is expected, and the error only shows up when the program runs. TypeScript catches these mistakes while you're writing the code, before it ever runs. It's especially helpful in large projects where many files depend on each other.
What's MJML? Email HTML is notoriously difficult. Code that looks perfect in Chrome might be completely broken in Outlook. MJML is a special language that compiles into email-safe HTML that works across all email clients. You write clean, simple markup, and it outputs the ugly-but-compatible code that emails need.

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.

What's an agentic AI? A regular chatbot just generates text. An agentic AI can take actions. In this system, the AI has access to "tools" — functions it can call to actually do things: generate templates, replace images, change colors, send test emails. The AI decides which tool to use based on what the user asks. It's like the difference between an advisor who gives suggestions and an assistant who actually does the work.

The AI has access to these tools:

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:

StateWhat's HappenedTools Available
S0Nothing yetURL validation, research
S1Research doneSet sender settings
S2Sender configuredGenerate templates, load existing
S3Templates loaded (no sender)Refine, replace, undo/redo
S4Full stateEverything — 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).

What's SSE? Normally, a website sends a request and waits for a response (like ordering food and waiting for the plate). SSE keeps the connection open so the server can keep sending data as it becomes available (like a sushi conveyor belt — pieces arrive one by one). This lets users see the AI "typing" in real time.

The system uses 20+ different SSE event types:

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:

  1. Color swap — "Change background to #2C7A51"
  2. Font swap — "Change heading font to Playfair Display"
  3. Text replace — "Change the headline to Winter Wellness Tips"
  4. Font size — "Make the heading bigger"
  5. Button style — "Make buttons rounded"
  6. Spacing — "Add more padding around sections"
  7. Borders — "Add a border to the header"
  8. 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.

How does it work technically? Email templates are stored as a JSON tree (a structured data format). Each node represents an element — a heading, a paragraph, an image, a button. The fast refinement engine "walks" through this tree, finds the nodes that match what the user wants to change, and modifies their properties directly. No AI needed.

Click-to-Edit

Instead of describing what to change in text, users can click directly on elements in the template preview:

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.

User says "Generate" → API starts background job → Returns job ID immediately → Frontend polls every 3s → Templates arrive

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:

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:

VulnerabilityWhat It MeansHow I Prevented It
SSRFAn 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 TraversalAn attacker uses ../../ in input to access files outside the allowed directoryCompany IDs sanitized to alphanumeric only. No path separators allowed.
File Upload AbuseUploading 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 InjectionInserting malicious HTML/JavaScript into email templatesAll dynamic content escaped before insertion into templates.
Token ExposureAPI authentication tokens visible in browser codeServer-side proxy architecture. Browser never sees the API token — Laravel handles auth server-side.
Rate LimitingAn attacker flooding the generation endpointPer-company rate limits on expensive operations.
Error LeakageStack traces or internal paths shown to usersAll errors sanitized. Users see "Something went wrong" — developers see the full trace in server logs.
What are magic bytes? Every file type starts with a specific sequence of bytes. A PNG image always starts with 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:

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.

Final numbers for the AI Newsletter project:
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

Chapter 5

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

PeriodRoleKey Skills Gained
2018–2021Web Developer, MesasixDatabase design, SQL, backend fundamentals
2021–2024Lead Query Dev, JarvisReverse engineering, large-scale SQL, AWS (Lambda, Step Functions, SQS), Python, data pipelines
2024–2026Team Lead, Jarvis/HSOneLeadership, system design, Redshift, schema standardization, Matchback analytics
2026–PresentFull-Stack AI Dev, CliniqAppsTypeScript/Node.js, AI/LLM integration, prompt engineering, real-time systems, security hardening, React

What I Bring to the Table

— Catherine Maglasang, 2026