The Migration Finished. The Dashboard Still Said Zero.

The migration finished. I opened the executive dashboard. Every chart said zero.

There were 13,914 submissions in the new Neon database, but the overview still showed 0 removed posts and a 0.0% removal rate.

My first guess was a stale materialized view.

It was not.

The real issue was deeper: the data had moved from Azure Postgres to Neon, but some of the old system's business meaning had not moved cleanly with it.

This turned from a routine database migration into a Technical Discovery mission. The goal was not just moving rows; it was architecting a system where the data actually matched the business reality—and where the new FastAPI backend and BI Studio layer were reliable enough that admins and executives could trust the dashboard.

The broken chain looked like this:

The executive UI mirrored that failure mode: the overview looked populated, but the headline KPIs said nothing was being removed.

The old and new systems did not store the same business event in the same place.

Some takedown data lived on the submission. Some newer flows expected it on the report. The BI materialized view only trusted the report field, so real removals were ignored.

The numbers matched at a row-count level. Foreign keys mapped. Sample checks passed. And the executive overview was still wrong, because none of those checks asked the question that actually mattered: what does this number mean?

Migration validation is structural. BI validation is semantic. They are different jobs, and the second one is harder to automate.

This was not a pg_dump migration.

I treated the move as an ETL problem: extract from Azure Postgres, transform into a normalized target shape, load into Neon, then validate before the BI layer read the new world.

Neon was the right target for managed Postgres with branching-friendly workflows and a clear path for production cutover. The API layer was FastAPI: explicit service boundaries, good ergonomics for long-running batch jobs and admin tooling, and straightforward integration with SQLAlchemy and Alembic for schema evolution.

The important design choice was not which logo sat on the database host. It was that a single legacy post did not become one new row. It became a normalized bundle across submissions, post data, metadata, hashtags, analysis, categories, and raw legacy JSON. That made the new system easier to query and easier to use for BI—but it also meant the migration had to survive real production conditions: long runs, new rows appearing mid-window, and foreign keys depending on IDs created earlier in the same pipeline.

So the pipeline used persisted legacy-to-new ID mappings, validation checks, and delta runs instead of relying on one giant fragile script.

The migration pipeline looked like this:

Azure Postgres → Readers → Transformers → Neon Writers → Validation → BI Layer

The main BI read model was a materialized view, mv_submission_facts. It flattened what analytics needed: submissions, reports, categories, hashtags, platform fields, and takedown status. That gave the dashboard a fast, stable layer to query instead of rebuilding complex analytics from raw tables on every request.

For custom metrics in BI Studio, I kept the SQL surface locked down. User-defined metric JSON should not become raw SQL. The service used whitelisted columns, whitelisted transforms, parameterized values, statement timeouts, and versioned metric definitions.

The overview dashboard also respected role scope: superadmins could see broad data while community managers only saw the communities they were allowed to access.

The interesting part was how quietly wrong the system could look.

A materialized view is a credibility machine: if it encodes the wrong invariant, every downstream chart inherits that mistake at scale. The COALESCE-style rule sounds trivial once you say it out loud—COALESCE(report.taken_down_at, submission.removed_at)—but nobody gets there without tracing meaning across tables and migration stages.

The production view was wider than this sketch, but the core idea is the same: one effective takedown timestamp and an explicit lineage for where it came from.

-- Illustrative: resolving the "zero data" logic gap in the read model
CREATE MATERIALIZED VIEW mv_submission_facts AS
SELECT
    s.id,
    COALESCE(r.taken_down_at, s.removed_at) AS effective_takedown_at,
    CASE
        WHEN r.taken_down_at IS NOT NULL THEN 'report_flow'
        ELSE 'legacy_fallback'
    END AS data_source
FROM submissions s
LEFT JOIN reports r ON s.id = r.submission_id;

The platform_detail bug was the same class of problem in a different costume: schema that looks like a labeling tweak can hide an active classification bug in production data. Splitting Alembic revisions kept blast radius and review clarity under control when the fix necessarily touched both DDL and historical rows.

I owned the end-to-end path from legacy Azure Postgres through the ETL migration into Neon, the FastAPI-facing data model, Alembic-driven schema changes, the analytics read model (including mv_submission_facts), and the BI Studio safeguards (metric definitions, timeouts, and role-scoped access).

That included the semantic investigation when row counts were fine but executive metrics were not, and the corrective work across SQL, migration builders, and validation so the same mistake could not silently reappear.

The migration delivered a normalized Postgres model on Neon, a repeatable ETL and delta-run story, and BI metrics that aligned with operational reality—not just with whichever column happened to be populated in the new schema.

Executives and admins got removal counts, rates, and platform splits that matched what moderators were actually doing—for example 309 removals in the 30-day window once semantics were fixed—with enough provenance to answer why a takedown landed where it did. The dashboard was impressive because we could explain every number on it, not because the charts were flashy.

The broader takeaway I keep using on similar engagements: ship structural checks early, but budget real time for semantic checks on the numbers people stake decisions on.

Most of the code in this project was written with AI assistance. That made the work faster, but it did not remove the important engineering decisions. AI could help write migrations, update views, and generate tests. It could not decide what a takedown means, whether 274 mis-tagged rows were harmless old data or an active bug, or whether a dashboard number would be trusted by the people using it. The workflow I trust is simple: use AI to move faster, investigate before changing data, make the business rule explicit, validate the result.