A guided tour through production systems I've built — from AI-powered newsletter generation to cloud data pipelines and 48,000+ SQL queries.
Before we dive into the code, here's how all the pieces fit together. The AI Newsletter Generator is a full-stack system spanning 3 repositories, each with a distinct role.
Browser (User) │ │ HTTPS POST (SSE stream) ▼ Laravel (PHP) ────────────────── Auth / CSRF / Session │ Blade template serves frontend JS │ All API calls proxied (no tokens in browser) │ │ HTTP Proxy ▼ LoopBack 4 API (TypeScript) ──── The brain │ ├── Chat Service → agentic loop (up to 10 turns) │ ├── Tool System → 5-state gating │ ├── Refinement → fast programmatic engine │ ├── Security → SSRF, path traversal, auth │ └── Job Store → background generation │ │ AWS SDK ▼ AWS Bedrock (Claude) ────────── AI generation │ Sonnet: template generation │ Haiku: refinements & subject lines │ AWS S3 (Encrypted) ───────────── Storage research / templates / images / style-memory
This is the heart of the entire system. When a user sends a message, the chat service orchestrates a multi-turn conversation with Claude (via AWS Bedrock). The AI can call "tools" — functions that do things like research a website, generate templates, or replace text. After each tool runs, the result goes back to the AI for the next step. This loop can run up to 10 turns in a single user message.
The key optimization: fast-path exits. When the AI calls a tool that produces a visual result (like replacing text), we skip the second AI call entirely. Why ask the AI to say "Done!" when the user can already see the change? This cuts response time from ~4 seconds to under 200ms.
// ── Fast-path: skip second LLM call for simple template edits ──
// When replace_text or replace_image succeeds, the preview already updated via
// onTemplatesReady. No need for another 3s Bedrock round-trip just to say "Done!".
if (toolCalls.length === 1) {
const tc = toolCalls[0];
const tr = toolResults[0] && (toolResults[0] as any).toolResult?.content?.[0]?.text;
let parsedResult: any = null;
try { parsedResult = tr ? JSON.parse(tr) : null; } catch { /* ignore */ }
// Fast-path: skip LLM round-trip for tools where the result speaks for itself.
// - replace_text / replace_image: preview already updated via onTemplatesReady
// - generate_newsletter (background): frontend polls independently
let directResponse: string | null = null;
if (parsedResult?.success && (tc.name === 'replace_text' || tc.name === 'replace_image')) {
if (tc.name === 'replace_text' &&
(parsedResult.cascadedSiblings > 0 || parsedResult.cascadedButtons > 0)) {
directResponse = parsedResult.message
|| 'Done! The heading and related content have been updated.';
} else {
directResponse = 'Done!';
}
} else if (parsedResult?.success && parsedResult?.status === 'generating'
&& tc.name === 'generate_newsletter') {
directResponse = parsedResult.message
|| 'Your newsletters are being created. This usually takes about a minute.';
}
if (directResponse) {
if (callbacks.onTextChunk) callbacks.onTextChunk(directResponse);
messages.push({role: 'assistant', content: [{text: directResponse}]});
rawText = directResponse;
logger.info(`Fast-path response for ${tc.name} — skipped LLM round-trip, ` +
`wall-clock: ${Date.now() - chatStartTime}ms`);
await sessionPromise;
if (callbacks.onComplete) {
callbacks.onComplete({
stopReason: 'end_turn', rawText, sessionId: s3SessionId,
usage: {
inputTokens: totalInputTokens, outputTokens: totalOutputTokens,
totalTokens: totalInputTokens + totalOutputTokens,
iterations: i + 1, durationMs: Date.now() - chatStartTime,
},
});
}
return rawText;
}
}
Instead of waiting for the AI to finish its entire response before showing anything, we stream every word as it's generated — character by character, in real time. This uses a web technology called Server-Sent Events (SSE). The AI's response flows through 20+ event types: text chunks, tool calls, template updates, research results, session IDs, and more.
@post('/api/newsletter/chat', { responses: { '200': STREAM_RESPONSE } })
async chat(
@requestBody() req: { companyId: string; message: {role: string; content: string}; ... },
@inject(RestBindings.Http.RESPONSE) response: Response,
): Promise<void> {
const logger = new CustomLogger(req.companyId || 'newsletter-chat');
// Security: Sanitize companyId (prevent path traversal)
const companyIdValidation = sanitizeCompanyId(req.companyId);
if (!companyIdValidation.valid) {
response.status(400).send({error: 'Invalid company identifier'});
return;
}
const safeCompanyId = companyIdValidation.sanitized!;
// Security: Rate limiting
const rateLimitResult = checkRateLimit(safeCompanyId, {
maxRequestsPerMinute: 10, maxGenerationsPerSession: 50,
});
if (!rateLimitResult.allowed) {
response.status(429).send({
error: `Rate limit exceeded. Try again in ${rateLimitResult.retryAfter} seconds.`
});
return;
}
// Set SSE headers — X-Accel-Buffering: no prevents nginx from buffering the stream
response.setHeader('Content-Type', 'text/event-stream; charset=utf-8');
response.setHeader('Cache-Control', 'no-cache, no-transform');
response.setHeader('Connection', 'keep-alive');
response.setHeader('X-Accel-Buffering', 'no');
// Security: Redact message content from logs (may contain PII)
logger.info(`chat, companyId: ${safeCompanyId}, message length: ${req.message.content.length}`);
// Stream events to the browser as they happen
await this.chatService.chat({...req, companyId: safeCompanyId}, {
onTextChunk: (chunk) => response.write(`data: ${JSON.stringify({type: 'content', text: chunk})}\n\n`),
onToolUse: (tool) => response.write(`data: ${JSON.stringify({type: 'tool_use', tool: {...}})}\n\n`),
onTemplatesReady:(data) => response.write(`data: ${JSON.stringify({type: 'templates_ready', data})}\n\n`),
onSessionReady: (sid) => response.write(`data: ${JSON.stringify({type: 'session_ready', sessionId: sid})}\n\n`),
onGenerationStarted: (d) => response.write(`data: ${JSON.stringify({type: 'generation_started', ...d})}\n\n`),
onResearchReady: (data) => response.write(`data: ${JSON.stringify({type: 'research_ready', data})}\n\n`),
onBrandAnalysisReady: (d)=> response.write(`data: ${JSON.stringify({type: 'brand_analysis_ready', data: d})}\n\n`),
onError: (error) => response.write(`data: ${JSON.stringify({
type: 'error', message: 'Something went wrong. Please try again.' // Never expose raw errors
})}\n\n`),
onComplete: (result)=> response.write(`data: ${JSON.stringify({type: 'done', ...result})}\n\n`),
});
response.end();
}
When a user says "change the background to blue" or "use Roboto font," why call an expensive AI model? This engine handles color swaps, font changes, text replacements, and spacing adjustments entirely with code — no AI needed. It walks the MJML JSON tree, finds the right elements, and makes the change programmatically.
/**
* Attempt a fast programmatic refinement. Returns {mjmlJson, html, label} or null.
* Handles: color swaps, font swaps, text replacements, text color changes.
*/
private tryFastRefinement(
mjmlJson: any, instruction: string
): {mjmlJson: any; html: string; label: string} | null {
const lower = instruction.toLowerCase();
// 1) Color swap — hex codes + color keywords
const colorResult = this.tryColorSwap(mjmlJson, instruction, lower);
if (colorResult) return colorResult;
// 2) Font swap — "change font to X", "use X font"
const fontResult = this.tryFontSwap(mjmlJson, instruction, lower);
if (fontResult) return fontResult;
// 3) Text replacement — "replace X with Y", "change header to Y"
const textResult = this.tryTextReplace(mjmlJson, instruction, lower);
if (textResult) return textResult;
return null; // Not a simple change — fall back to AI
}
/** Font swap: "change font to Roboto", "use Georgia font", "switch to Arial" */
private tryFontSwap(mjmlJson: any, instruction: string, lower: string):
{mjmlJson: any; html: string; label: string} | null {
const fontKeywords = ['font', 'typeface', 'typography'];
if (!fontKeywords.some(k => lower.includes(k))) return null;
// Bail on structural changes — these need the AI
const structuralKeywords = ['layout', 'section', 'column', 'add', 'remove', 'image'];
if (structuralKeywords.some(k => lower.includes(k))) return null;
// Extract target font name with multiple regex patterns
const fontMatch = instruction.match(
/(?:to|use|switch\s+to)\s+([A-Z][a-zA-Z\s]+?)(?:\s+(?:font|with|as|and|for)\b|\s*$|\s*,)/i
) || instruction.match(
/([A-Z][a-zA-Z\s]+?)\s+font\b/i
);
if (!fontMatch) return null;
const newFont = fontMatch[1].trim();
if (newFont.split(/\s+/).length > 3 || newFont.length > 30) return null;
// Detect scope: "change header font" vs "change all fonts"
const scope = this.detectFontScope(lower); // 'all' | 'header' | 'paragraph' | 'button'
this.logger.info(`Fast font swap: "${newFont}" (scope: ${scope})`);
const updatedJson = JSON.parse(JSON.stringify(mjmlJson));
this.swapFontInJson(updatedJson, newFont, scope);
const html = this.templateGenerator.renderJsonToHtml(updatedJson);
return {mjmlJson: updatedJson, html, label: `font swap (${scope})`};
}
This centralized module handles 12 categories of web vulnerabilities in one file. Every external input passes through these validators before reaching any business logic.
http://169.254.169.254/ — a special address that only works from inside AWS. That address returns secret credentials. SSRF protection blocks requests to internal/private IP addresses, even if an attacker uses DNS tricks to disguise them.
/**
* Sanitize companyId to prevent path traversal and S3 key injection.
* Only alphanumeric chars, hyphens, underscores allowed (no path separators).
*/
export function sanitizeCompanyId(companyId: string):
{valid: boolean; sanitized?: string; error?: string} {
if (!companyId || typeof companyId !== 'string') {
return {valid: false, error: 'Company ID is required'};
}
const trimmed = companyId.trim();
// Check for path traversal attempts: ../../etc/passwd
if (trimmed.includes('..') || trimmed.includes('/') || trimmed.includes('\\')) {
logger.warn(`Path traversal attempt detected in companyId: ${companyId}`);
return {valid: false, error: 'Invalid company ID format'};
}
// Strict allowlist: only safe characters
const sanitized = trimmed.replace(/[^a-zA-Z0-9_-]/g, '');
if (sanitized.length === 0 || sanitized.length > 100) {
return {valid: false, error: 'Invalid company ID format'};
}
return {valid: true, sanitized};
}
/**
* Validate URL to prevent SSRF attacks.
* Blocks: AWS metadata, localhost, private IPs (RFC 1918).
* Resolves DNS BEFORE making the request to prevent DNS rebinding.
*/
export async function validateUrlForSSRF(url: string):
Promise<{valid: boolean; error?: string; resolvedIps?: string[]}> {
try {
const parsed = new URL(url);
// Only HTTP/HTTPS
if (!['http:', 'https:'].includes(parsed.protocol)) {
return {valid: false, error: 'Only HTTP/HTTPS URLs are allowed'};
}
// Block localhost variants
const hostname = parsed.hostname.toLowerCase();
if (['localhost', '127.0.0.1', '0.0.0.0', '[::1]'].includes(hostname)) {
logger.warn(`SSRF attempt blocked: localhost URL ${url}`);
return {valid: false, error: 'Internal URLs are not allowed'};
}
// Resolve DNS to actual IPs BEFORE making the request
let ips: string[];
try {
ips = await resolveDns(hostname);
} catch {
return {valid: true}; // DNS failed — let HTTP client handle it
}
// Block any resolved IP that's private/internal
for (const ip of ips) {
if (isPrivateIP(ip)) {
logger.warn(`SSRF attempt blocked: ${url} resolves to internal IP ${ip}`);
return {valid: false, error: 'URLs resolving to internal addresses are not allowed'};
}
}
return {valid: true, resolvedIps: ips};
} catch (err: any) {
return {valid: false, error: 'Invalid URL format'};
}
}
Generating 3 newsletter templates takes 40–90 seconds. If we kept the HTTP connection open that long, nginx would kill it with a 504 timeout. The solution: start the job in the background, give the browser a job ID, and let it poll for results.
// ─── In-Memory Generation Job Store ─────────────────────────────────────
// Module-level Map that survives across HTTP requests (lives in Node module scope).
// Tracks background newsletter generation jobs so the frontend can poll for results.
export interface GenerationJob {
jobId: string; // "gen-{companyId}-{timestamp}"
companyId: string;
sessionId: string;
status: 'generating' | 'complete' | 'failed';
startedAt: number;
completedAt?: number;
templates?: any[]; // Full template array on completion
error?: string;
}
const jobs = new Map<string, GenerationJob>();
const MAX_JOBS = 100;
const JOB_TTL_MS = 10 * 60 * 1000; // 10 minutes
/** Create a new generation job. */
export function createJob(jobId: string, companyId: string, sessionId: string): GenerationJob {
if (jobs.size >= MAX_JOBS) evictOldestCompleted(); // Prevent memory leak
const job: GenerationJob = {
jobId, companyId, sessionId, status: 'generating', startedAt: Date.now(),
};
jobs.set(jobId, job);
return job;
}
/** Mark a job as complete with template results. */
export function completeJob(jobId: string, templates: any[]): void {
const job = jobs.get(jobId);
if (!job) return;
job.status = 'complete';
job.completedAt = Date.now();
job.templates = templates;
}
/** Find any active job for a company (prevents double-generation). */
export function getActiveJobForCompany(companyId: string): GenerationJob | undefined {
for (const job of jobs.values()) {
if (job.companyId === companyId && job.status === 'generating') return job;
}
return undefined;
}
And here's the frontend polling that matches — robust error handling with consecutive error tracking, auth expiry detection, and graceful timeouts.
function startGenerationPolling(jobId) {
stopGenerationPolling();
let pollCount = 0;
let consecutiveErrors = 0;
const MAX_POLLS = 60; // 3 minutes at 3s interval
const MAX_CONSECUTIVE_ERRORS = 10; // stop after 10 straight failures
generationPollTimer = setInterval(async () => {
pollCount++;
if (pollCount > MAX_POLLS) {
stopGenerationPolling();
hideGeneratingOverlay();
addMessage('assistant', 'Generation is taking longer than expected. Please try again.');
return;
}
try {
const resp = await fetch(GENERATION_STATUS_URL + '?jobId=' + encodeURIComponent(jobId), {
headers: {'X-CSRF-TOKEN': CSRF_TOKEN, 'Accept': 'application/json'},
credentials: 'same-origin',
});
if (!resp.ok) {
consecutiveErrors++;
if (resp.status === 401 || resp.status === 419) {
// Auth or CSRF expired — stop immediately
stopGenerationPolling();
addMessage('assistant', 'Session expired. Please refresh the page.');
return;
}
if (consecutiveErrors >= MAX_CONSECUTIVE_ERRORS) {
stopGenerationPolling();
addMessage('assistant', 'Unable to check progress. Please refresh.');
return;
}
return; // Keep polling — might recover
}
consecutiveErrors = 0; // Reset on success
const json = await resp.json();
if (json.status === 'complete') {
stopGenerationPolling();
hideGeneratingOverlay();
if (json.templates && Array.isArray(json.templates)) {
renderTemplates(json.templates);
}
}
} catch (err) { consecutiveErrors++; }
}, 3000);
}
getActiveJobForCompany() guard prevents a critical bug: if the AI accidentally calls generate_newsletter twice, the second call finds the existing job and returns it instead of starting a duplicate. The frontend polling has 3 layers of protection: max poll limit (timeout), consecutive error tracking (bad server), and auth status detection (expired session). The in-memory store auto-evicts old jobs so the server never runs out of memory.
The frontend reads the SSE stream and routes each event to the right handler. Over 20 event types trigger different UI behaviors — showing tool progress, rendering templates, updating brand analysis panels, starting polls, enabling click modes, and more.
function handleSSEEvent(data) {
switch (data.type) {
case 'tool_use':
// Mark previous tools as done when the next step starts
if (data.tool.name === 'conduct_research') {
markToolDone('validate_url');
} else if (data.tool.name === 'generate_newsletter') {
markToolDone('set_sender_settings');
}
// Start timing for this tool (displayed in UI as "Researching... 3.2s")
timeStart(data.tool.name);
// Show progress in preview panel
if (data.tool.name === 'validate_url') {
showResearchProgress('validate');
} else if (data.tool.name === 'conduct_research') {
showResearchProgress('research');
} else if (data.tool.name === 'generate_newsletter') {
showGeneratingProgress();
} else if (data.tool.name === 'refine_template') {
showRefiningOverlay('Refining template', {
variantId: data.tool.variantId, applyToAll: data.tool.applyToAll
});
} else if (data.tool.name === 'replace_text') {
showRefiningOverlay('Updating text', {
variantId: data.tool.variantId,
oldText: data.tool.oldText, newText: data.tool.newText
});
}
break;
case 'session_ready':
if (data.sessionId) sessionId = data.sessionId;
break;
case 'brand_analysis_ready':
renderBrandAnalysisPanel(data.data);
break;
case 'templates_ready':
hideRefiningOverlay();
hideGeneratingOverlay();
renderTemplates(data.data);
break;
case 'generation_started':
showGeneratingOverlay();
startGenerationPolling(data.jobId);
break;
case 'image_click_mode':
enableImageClickMode();
break;
case 'text_click_mode':
enableTextClickMode(data.data);
break;
case 'done':
isSending = false;
break;
}
}
markToolDone() chain ensures the progress indicators transition smoothly (e.g., "Validating URL..." → "Researching..." → "Generating...").
Users can click on any image or text in the live email preview to edit it. This required a click-event system that works inside iframes — the email preview lives in an iframe to isolate its styles from the main page. Each mode (image, text, button) is mutually exclusive and uses named event handlers for clean attach/detach.
// ─── Image Click-to-Edit ──────────────────────────────────
function attachImageClickHandlers(iframe) {
if (!imageClickModeActive) return;
if (!iframe || !iframe.contentDocument) return;
const imgs = iframe.contentDocument.querySelectorAll('img');
let visibleIndex = 0;
imgs.forEach(function(img) {
// Skip tiny images (icons, spacers) — only make real images clickable
if (img.naturalWidth < 50 && img.width < 50) return;
const currentIndex = visibleIndex;
img.style.cursor = 'pointer';
img.style.transition = 'outline 0.2s';
// Named handlers for clean detach later
img._imgEditEnter = function() { img.style.outline = '3px dashed #3BA9C2'; };
img._imgEditLeave = function() { img.style.outline = ''; };
img._imgEditClick = function() { onImageClicked(currentIndex, img, iframe); };
img.addEventListener('mouseenter', img._imgEditEnter);
img.addEventListener('mouseleave', img._imgEditLeave);
img.addEventListener('click', img._imgEditClick);
visibleIndex++;
});
}
// ─── Text Click-to-Edit ──────────────────────────────────
function attachTextClickHandlers(iframe) {
if (!textClickModeActive) return;
if (!iframe || !iframe.contentDocument) return;
// Find elements by CSS class identifier (e.g. "identifier-mj-text-5")
// This ID maps directly to the backend's MJML JSON tree position
var textEls = iframe.contentDocument.querySelectorAll('[class*="identifier-mj-text-"]');
textEls.forEach(function(el) {
var match = el.className.match(/identifier-mj-text-(\d+)/);
if (!match) return;
var textIndex = parseInt(match[1], 10);
el.style.cursor = 'pointer';
el._textEditEnter = function() { el.style.outline = '2px dashed #3BA9C2'; };
el._textEditLeave = function() { el.style.outline = ''; };
el._textEditClick = function() { onTextClicked(textIndex, el, iframe); };
el.addEventListener('mouseenter', el._textEditEnter);
el.addEventListener('mouseleave', el._textEditLeave);
el.addEventListener('click', el._textEditClick);
});
}
img._imgEditEnter) solves a common JavaScript bug: if you use anonymous functions with addEventListener, you can never remove them later with removeEventListener. By storing each handler as a property on the element itself, we can cleanly detach all handlers when switching modes — no memory leaks, no ghost event handlers. The CSS class identifier system (identifier-mj-text-5) bridges the visual DOM and the backend's JSON tree, making it possible to map a click in the preview directly to the right node in the MJML data structure.
At Jarvis Analytics, I built a data sync pipeline that pulls dental practice data from the Dentrix Ascend cloud platform into our analytics database. The system syncs 40+ API endpoints into 51 database tables, handling pagination, retries, rate limiting, and incremental updates.
def get_api_call(endpoint, org_id, last_id, last_modified_minutes,
retry_count, page_size=None, processDelete=False,
apiFilter=None, apiResponseFields=None):
"""
API call handler for 40+ dental practice endpoints.
Endpoints: appointments, clinicalnotes, patients, transactions,
patientprocedures, insuranceclaims, feeschedules, providers,
locations, operatories, visits, perioexams, and 28 more.
"""
base_url = 'https://prod.hs1api.com/ascend-gateway/api/v1/'
url = base_url + endpoint
access_token = get_access_token()
headers = {
'Content-type': 'application/json',
'Authorization': 'Bearer ' + access_token,
'Organization-ID': org_id
}
default_page_size = 300 if page_size is None else page_size
params = {'pageSize': default_page_size}
filter = ""
# Cursor-based pagination: resume from where we left off
if last_id is not None:
params['lastId'] = int(last_id)
elif last_id is None and last_modified_minutes == 0:
params['bypassParams'] = '1' # Full sync — no filters
# Incremental sync: only records modified since last run
unsupported = ['feescheduleranges', 'smsnumbers']
if endpoint not in unsupported:
if last_modified_minutes != 0:
last_modified_date = datetime.strftime(
datetime.now() - timedelta(minutes=last_modified_minutes),
'%Y-%m-%d'
)
filter += f'lastModified>={last_modified_date}'
if filter:
params['filter'] = filter
response = requests.get(url, headers=headers, params=params)
response.raise_for_status()
return response.json()
The adaptive page sizing is particularly clever — when the API starts failing (timeouts, rate limits), the system automatically reduces page size to reduce load, then gradually recovers:
def get_page_size(default_pagesize=500, previous_pagesize=None,
retry_count=0, recovery_count=0):
"""
Adaptive page sizing: shrinks on failure, grows on recovery.
- No retries: use default (500)
- Recovering: slowly increase (50% increments back to default)
- Retrying: halve each time (exponential backoff on page size)
- 5+ retries: drop to 1 record at a time (last resort)
"""
if previous_pagesize is None:
return default_pagesize
elif retry_count == 0 and recovery_count == 0 and previous_pagesize < default_pagesize:
# Recovering — increase by 50% each success, capped at default
return min(int(previous_pagesize + max(previous_pagesize * 0.5, 1)), default_pagesize)
elif retry_count == 0:
return previous_pagesize
elif retry_count >= 5:
return 1 # Last resort: one record at a time
else:
# Exponential backoff: halve page size on each retry
return max(int(previous_pagesize / (2 * retry_count)), 1)
if __name__ == "__main__":
print("\n\n********** RUNNING DX ASCEND SCRIPT **********\n\n")
# Create database schema and tables if not yet exist.
db_create_schema()
start_time = time.time()
# Run all 40+ endpoint syncs in parallel (6 workers)
with Pool(processes=6) as pool:
for name, val in data_insertion.__dict__.items():
if callable(val) and 'api_insert' in name:
pool.apply_async(val)
pool.close()
pool.join()
print("\n\n--- %s seconds ---" % (time.time() - start_time))
lastId) allows the system to resume from exactly where it left off after any failure.
Beyond the batch sync, the pipeline also supports real-time streaming. When a dental practice creates a new appointment or records a payment in Dentrix Ascend, that change flows through RabbitMQ (a message queue) to our consumer, which routes it to AWS SQS for processing.
async def on_message(message: AbstractIncomingMessage) -> None:
async with message.process():
# Parse routing key: "orgId.locationId.domainType.operation"
[organization_id, location_id, domain_type, operation] = \
message.routing_key.split('.')
message_body = json.loads(str(message.body, 'utf-8'))
payload = message_body['payload']
record_id = message_body['id']
# Route to correct SQS queue (production vs staging)
test_orgs = ["5fa5bce5471d910c1b33e320"]
sync_env = config('SYNC_ENVIRONMENT', default='prod')
sqs_queue = config('SQS_QUEUE_STAGE' if organization_id in test_orgs
or sync_env == 'stage' else 'SQS_QUEUE')
# Clean oversized nested data (e.g. PatientV1.referredPatients)
if domain_type == "PatientV1" and 'referredPatients' in payload:
del payload['referredPatients']
# Build SQS event and send with deduplication
sqs = boto3.client('sqs')
event = {
'producer': False,
'organization_id': organization_id,
'payload': payload,
'id': record_id,
'operation': operation, # CREATE, UPDATE, or DELETE
'domain_type': domain_type, # PatientV1, Transaction, etc.
}
# Deduplication: hash the content to prevent duplicate processing
if operation == 'DELETE':
dedup_id = f"{organization_id}-{domain_type}-DELETE-{record_id}"
else:
dedup_id = hashlib.md5(
(operation + json.dumps(payload)).encode('utf-8')
).hexdigest()
sqs.send_message(
QueueUrl=sqs_queue,
MessageBody=json.dumps(event),
MessageDeduplicationId=dedup_id,
# Group by org + domain + ID modulo (preserves ordering within entity)
MessageGroupId=f"{organization_id}{domain_type}{int(record_id) % 9}"
)
MessageGroupId with modulo 9 is a clever balancing act: it keeps related records ordered (same entity type from same org) while spreading load across 9 SQS partitions for parallelism.
Marketing agencies spend thousands on Google Ads, Facebook campaigns, and SEO to get new patients to dental practices. But did those campaigns actually work? Matchback answers that question: it links marketing leads (form submissions, phone calls) to actual patient visits in the dental practice's system — proving ROI.
/*
Matchback v5 — Link marketing leads to completed dental appointments.
Matching strategy:
FORMS (website submissions):
1. Email → 100% confidence match
2. Last name + Zip code → 100% (if location matches)
3. Phone → 100% confidence match
CALLS (phone leads):
1. Phone → 100% confidence match
Works across 4 PMS platforms:
EagleSoft, OpenDental, Dentrix Ascend, Denticon
Returns: all patients with completed appointments within 60 days
of the lead date (first appointment only per patient).
*/
CREATE PROCEDURE stored_procs.`matchback_v5`(
command TEXT, -- 'forms' or 'calls'
lname TEXT, -- lead's last name
fname TEXT, -- lead's first name
email TEXT, -- lead's email (blank for calls)
phone TEXT, -- lead's phone
zip TEXT, -- lead's zip code (blank for calls)
dbs TEXT, -- practice database name
`lead_date` TEXT, -- when the lead came in
aggregatedb TEXT -- aggregate database name
)
BEGIN
SET SESSION group_concat_max_len = 1000000;
-- Build the dynamic matching query across all PMS platforms
SET @sql_string = stored_procs.`MATCH_BACK_V5`(
command, lname, fname, email, phone, zip, dbs, `lead_date`, aggregatedb
);
PREPARE stmt FROM @sql_string;
EXECUTE stmt;
END$$
The lead aggregation stored procedure shows the scale of the matching logic — deduplicating leads by email, phone, and Google click IDs across multiple marketing sources, then grouping by UTM campaign:
-- Deduplicate leads: group by email first (email = strongest identifier)
SET @sql_string1 = CONCAT('
SELECT
a.utm_source AS `group`,
GROUP_CONCAT(DISTINCT(name_sanitized)) AS name_sanitized,
GROUP_CONCAT(DISTINCT(location_id)) AS location_id,
GROUP_CONCAT(DISTINCT(`type`)) AS types,
GROUP_CONCAT(DISTINCT(IF(`type` = "forms", "Website|Coupon", "Calls"))) AS lead_type,
COUNT(lead_id) AS lead_count,
GROUP_CONCAT(lead_id) AS lead_ids,
GROUP_CONCAT(DISTINCT(click_id)) AS gclids,
GROUP_CONCAT(DISTINCT(phone_sanitized)) AS phones,
GROUP_CONCAT(DISTINCT(email)) AS emails,
GROUP_CONCAT(DISTINCT(utm_source)) AS utm_sources,
GROUP_CONCAT(DISTINCT(utm_campaign)) AS utm_campaigns
FROM ', `db`, '.lead_aggregates a
WHERE DATE(a.lead_date) BETWEEN \'', `start`, '\' AND \'', `end`, '\'
AND a.email IS NOT NULL
AND a.location_id IN (', `location_ids`, ')
GROUP BY email
');
-- Then union with phone-only leads (no email)
-- Then merge by Google click ID (gclid) to connect ad clicks to conversions
-- Final: GROUP BY gclids for attributed leads, separate unattributed leads
Every dental practice uses different software to manage their business. I reverse-engineered 14 different Practice Management Systems — each with completely different database schemas — and wrote the same 46 report categories for all of them. That's 3,000+ queries per PMS, totaling 48,000+ SQL files.
current_bal field. OpenDental makes you calculate it from 7 separate transaction types. Denterprise stores amounts in cents (not dollars) and uses multi-database ID tracking. Same business question, completely different SQL every time.
Here's the same report — "Accounts Receivable by Insurance" — written for 3 different PMS systems. Watch how the SQL is completely different for the same business question: "How much money does each insurance company owe us, broken down by how old the debt is?"
OpenDental doesn't pre-calculate aging buckets. I had to build a waterfall algorithm in pure SQL: credits are applied oldest-first, and each bucket only gets its share after all older buckets are satisfied. This cascading CASE logic spans 8 aging periods.
-- Waterfall credit allocation: apply credits oldest-first
-- Each bucket = charges minus credits already consumed by older buckets
ROUND(
CASE
WHEN tSums.TotalCredits >= tSums.ChargesOver365 THEN 0
ELSE tSums.ChargesOver365 - tSums.TotalCredits
END, 3) BalOver365,
ROUND(
CASE
WHEN tSums.TotalCredits <= tSums.ChargesOver365 THEN tSums.Charges_241_365
WHEN tSums.ChargesOver365 + tSums.Charges_241_365 <= tSums.TotalCredits THEN 0
ELSE tSums.ChargesOver365 + tSums.Charges_241_365 - tSums.TotalCredits
END, 3) Bal_241_365,
ROUND(
CASE
WHEN tSums.TotalCredits < tSums.ChargesOver365 + tSums.Charges_241_365
THEN tSums.Charges_181_240
WHEN tSums.ChargesOver365 + tSums.Charges_241_365 + tSums.Charges_181_240
<= tSums.TotalCredits THEN 0
ELSE tSums.ChargesOver365 + tSums.Charges_241_365
+ tSums.Charges_181_240 - tSums.TotalCredits
END, 3) Bal_181_240,
-- ... continues for 121-180, 91-120, 61-90, 31-60, 0-30
-- Each level adds one more bucket to the running total
-- Credits "fill up" from oldest to newest — like pouring water into stacked cups
Eaglesoft already calculates aging buckets for you. Much simpler SQL — but the insurance relationship goes through the employer table (employer → insurance company), not through claims.
SELECT
pat.`patient_id`,
pat.`responsible_party`,
CONCAT(
IF(resp.`last_name` IS NULL OR resp.`last_name` = "", "No Last Name", resp.`last_name`),
", ", resp.`first_name`
) AS responsible_party_name,
pat.`current_bal`, -- Pre-calculated by Eaglesoft
pat.`thirty_day`, -- No waterfall logic needed
pat.`sixty_day`,
pat.`ninety_day`,
pat.`estimated_insurance`,
IF(ins.`name` IS NOT NULL, ins.`name`, '') AS insurance_company
FROM
patient pat
STRAIGHT_JOIN patient resp ON pat.`responsible_party` = resp.`patient_id`
-- Insurance linked through EMPLOYER, not claims
LEFT JOIN employer emp ON pat.prim_employer_id = emp.employer_id
LEFT JOIN insurance_company ins ON emp.insurance_company_id = ins.insurance_company_id
Denterprise stores everything in cents (multiply by 0.01 for dollars) and uses a multi-database ID system (PATID + PATDB, GUARID + GUARDB) because it tracks records across multiple office databases. Uses TIMESTAMPDIFF for aging instead of waterfall.
SELECT
pl.proc_logid, pl.proc_logdb,
pl.PATID, pl.PATDB, -- Multi-database ID tracking
pl.GUARID, pl.GUARDB, -- Guarantor in separate DB
pl.PROVID, pl.PROVDB, -- Provider in separate DB
-- Aging via TIMESTAMPDIFF (simpler than waterfall, less accurate)
IF(TIMESTAMPDIFF(DAY, DATE(pl.pldate), DATE(NOW())) < 31,
pl.amount + IFNULL(SUM(chrg.amount), 0), 0) AS current,
IF(TIMESTAMPDIFF(DAY, DATE(pl.pldate), DATE(NOW())) BETWEEN 31 AND 60,
pl.amount + IFNULL(SUM(chrg.amount), 0), 0) AS over30,
IF(TIMESTAMPDIFF(DAY, DATE(pl.pldate), DATE(NOW())) BETWEEN 61 AND 90,
pl.amount + IFNULL(SUM(chrg.amount), 0), 0) AS over60,
-- ... continues for 91-120, 121-180, 180+
-- Amounts stored in CENTS — multiply by 0.01 in outer query
pl.amount + IFNULL(SUM(chrg.amount), 0) total_ar,
-- Determine patient vs insurance responsibility via claim status
IF(clm.CLAIMID IS NULL OR clm.CLAIMID = 0
OR (clm.CLAIMID > 0 AND clm.DATERECEIVED != "1753-03-02"),
1, 2) AS is_pat_ins -- 1=patient owes, 2=insurance owes
FROM DDB_PROC_LOG_BASE pl
LEFT JOIN DDB_CREDIT_TO_CHARGE_BASE chrg
ON pl.proc_logid = chrg.chargeid AND pl.proc_logdb = chrg.chargedb
LEFT JOIN DDB_CLAIM_BASE clm
ON clm.status = 1
AND pl.CLAIMID = clm.CLAIMID AND pl.CLAIMDB = clm.CLAIMDB
GROUP BY pl.proc_logid, pl.proc_logdb, pl.patid, pl.patdb, pl.pldate
HAVING pl.amount + IFNULL(SUM(chrg.amount), 0) > 0 AND is_pat_ins = 2
PATID+PATDB) means every JOIN needs two conditions instead of one. Eaglesoft's employer-based insurance linkage is unlike any other PMS. Understanding each schema deeply enough to produce identical business output required reverse-engineering the database design of every PMS from scratch — there are no public docs.
With 14 different PMS schemas, we needed a unified data model — a single schema where "procedure" means the same thing regardless of which PMS the data came from. This is the ODS (Operational Data Store) layer: Jinja2-templated SQL that dynamically generates the right query for each PMS while outputting identical column names.
{% if software == 'eaglesoft' %} blocks. At runtime, the system fills in the PMS name and generates the correct SQL. One template, 14 outputs — all producing the same columns.
SELECT
proc.procedureid AS procedure_id,
-- Eaglesoft sometimes stores procedure IDs instead of ADA codes
{% if software == 'eaglesoft' %}
COALESCE(proc.adacode, proc.procedureid) AS ada_code,
{% else %}
proc.adacode AS ada_code,
{% endif %}
-- Dentrix/Denterprise/Denticon: no category field — map via ADA code ranges
-- OpenDental/Eaglesoft/others: have a built-in category field
{% if software in ['dentrix', 'denterprise', 'denticon'] %}
{% if proceduresCategory and proceduresCategory[0]|length > 2 %}
CASE
{% for i in range(0, proceduresCategory|length-1) %}
WHEN (proc.adacode BETWEEN '{{ proceduresCategory[i][0] }}'
AND '{{ proceduresCategory[i][1] }}')
THEN '{{ proceduresCategory[i][2] }}'
{% endfor %}
ELSE 'Undefined'
END AS type,
{% else %}
'Undefined' AS type,
{% endif %}
{% else %}
proc.category AS type, -- OpenDental, Eaglesoft etc. have this natively
{% endif %}
proc.description AS description
FROM procedurecodes proc
ORDER BY proc.adacode ASC
-- OUTPUT (identical for all 14 PMS):
-- procedure_id | ada_code | type | description
-- 12345 | D0120 | Preventive | Periodic oral evaluation
-- 12346 | D2750 | Restorative | Crown - porcelain/ceramic