Bronze to Gold

The client runs a national network of branch offices helping students and skilled migrants through visa lodgement, education admissions, and compliance. Every one of those journeys generates data in three disconnected places: a SQL Server CRM behind an operational RDS instance, a mail server handling thousands of counsellor↔applicant threads a day, and a 3CX VoIP system recording every call.

None of the three could see the others. A counsellor could tell you a lead's visa status. They could not tell you, in the same screen, whether that lead had emailed twice this week asking about a delayed document, or called the office sounding frustrated. Reporting meant a one-off SQL export; there was no analytics surface on top of the operational database at all.

Each bundle deploys on its own timeline through GitHub Actions and owns its own job schedule, so a change to the audio pipeline never waits on a CRM release.

Every slowly-changing dimension — leads, companies, products, users, people — carries a five-column SCD Type 2 pattern: a SHA-2 hash of the business key detects a real change, the old row closes with _effective_to, and a new current row opens. Re-running the pipeline with no upstream changes writes nothing, which makes the whole system safely idempotent.

The 360° bridge fails in both dev and prod if its upstream gold layer is missing. Below that, a quarantine pattern catches rows that would otherwise violate a NOT NULL contract downstream: they're routed to a paired _rejects table instead of being dropped or crashing the run, so the pipeline stays green while nothing gets lost.

Because the platform is serverless end to end, every job run is a metered line item in system.billing.usage — which turned the cost-dashboard bundle into a genuine optimization tool rather than just a reporting exercise.

86% of CRM-lead sync runs wrote zero rows — pure cold-start overhead on an over-frequent schedule. Right-sizing sync frequency and retiring an idle SQL warehouse identified $2,424/mo; merging four sequential email-silver tasks into one, with no added latency, identified another $801/mo. Every job runs as a Unity Catalog service principal.

The same billing-driven analysis is now standing engineering practice: before proposing a schedule or a warehouse, look at what the last N runs actually cost against what they actually changed.

Every visa lodgement and every education admission is backed by paperwork: passports, bank statements, academic transcripts, offer letters, compliance forms. Counsellors upload it all through the CRM into a shared S3 bucket, in whatever format it arrives — a scanned PDF, a phone photo, a Word document, an Excel bank statement export. Formats in scope: PDF, JPG / PNG, TIFF / HEIC / WebP, DOC / DOCX, XLS / XLSX.

The prior design — a Lambda/SQS/DynamoDB pipeline — worked, but every new document type meant new glue code, and there was no visibility into what a failed extraction actually cost or why it failed. The brief was to replace it without losing anything on cost, while gaining structured, per-document-type warehouse tables and a real audit trail.

Every non-image format is normalized first — Word documents render to PDF via headless LibreOffice, then every page becomes a PNG — so a single vision-capable model interface handles the entire format matrix.

A document that fails at any stage — normalization, the LLM call, a rate limit, a confidence score that never clears 70% even after the Sonnet pass — lands in document_audit_log with enough detail for a staff member to act on it: the failure stage, the error type, the model that was tried, and a working link to the original file. A manual reprocessing job lets that person key in the correct fields directly against the target bronze table.

Multi-page documents are handled the same way a person would read them: batches of up to 20 pages go to the model together with context (“page batch 2 of 3 of the same document”), fields are merged across batches, and the reported confidence is the average across all of them — so a 40-page bank statement doesn't get judged on its first page alone.