Blob Wholesale, Database Sliver

Four containers, one rule about what belongs in each, and the accounting of what this design gets wrong

WHERE DATA LIVES

rendering diagram…

flowchart TB
    subgraph WRITE[Producers]
      W1[Macro engine]
      W2[Report generator]
      W3[SEO page builder]
      W4[Download builder]
    end
    subgraph BLOB[Azure Blob - the wholesale content]
      B1[(macro<br/>snapshot, forecasts,<br/>history archive)]
      B2[(reports-dev<br/>generated workbooks)]
      B3[(seo-pages<br/>pre-generated pages)]
      B4[(gmi-downloads<br/>ZIPs and exports)]
    end
    subgraph SQL[MySQL - only the queryable sliver]
      S1[Run records<br/>regime history]
      S2[File ownership<br/>size, lifecycle]
    end
    W1 --> B1
    W2 --> B2
    W3 --> B3
    W4 --> B4
    W1 --> S1
    W2 --> S2
    B1 -. read back to score<br/>past forecasts .-> W1
    B2 -. SAS URL .-> R[Readers]
    B4 -. SAS URL, 1-24h .-> R
    B1 --> R

The split that governs every storage decision on the platform: bulk content in blob, only the slice that must be queried in MySQL.

One Rule, Applied Everywhere

Blob holds the wholesale content. MySQL holds only the sliver that has to be queried. A macro snapshot is a 288 KB JSON document; the database stores the run record and the regime history that the dashboard filters and sorts on, and nothing else. A generated report is a multi-megabyte Excel workbook; the database stores its ownership, size and lifecycle, not its bytes.

Two consequences follow, and they are the reason the rule is worth stating rather than assuming. First, blob is never the authority on who owns what — a report blob's path contains no account and no folder, so ownership lives exclusively in the database and a blob alone cannot tell you whose it is. Second, the database never grows with content, only with facts about content, which is why a platform generating multi-megabyte workbooks runs on a burstable MySQL instance using well under a gigabyte.

The Containers

Four, on one storage account. Not a taxonomy — each exists because something needed it.

Container	Holds	Access & lifecycle
`macro`	The macro snapshot, commodities, sector grades, the playbook, the ML forecast artefacts, the scorecard archive	Read by every app on every dashboard load. Written by one engine run. Overwritten via an atomic copy-and-swap so no reader ever sees a half-written snapshot.
`seo-pages`	Pre-generated public ticker pages, shared-report shims, social cards	Merged into the client deployment at build time. Pre-generation is why a viral moment costs bandwidth rather than compute.
`gmi-downloads`	Folder ZIPs and account-export archives	Short-lived by intent. Delivered by time-limited SAS URL — 1 hour for folder ZIPs, 24 for exports — so the container is never public.
`reports-dev`	Generated report workbooks	Read via SAS on download. The name is wrong — see below.

There is no hot/cool/archive tiering, no lifecycle policy, no CDN in front of these, and no versioning or soft delete. That is worth saying plainly, because an architecture page that lists capabilities the system does not have is worse than one that admits the gap.

The Atomic Snapshot Swap

The macro dashboard reads one blob. The engine rewrites that blob every run. A naive overwrite means that for the duration of the upload, every reader — three apps, plus every shared link — is racing a partially written JSON document.

So the engine never writes the live blob. It writes a temporary blob, and when that write is complete it issues a server-side StartCopyFromUriAsync onto the live path. The copy is atomic from a reader's perspective: the old snapshot is served in full until the instant the new one is, and no byte of a half-built document is ever visible. The cost is one extra write; the alternative is a class of intermittent parse failure that would be almost impossible to reproduce.

The same instinct governs the failure mode: when an engine run fails, nothing is written at all. The dashboard keeps serving the previous snapshot and says how old it is. Stale-but-honest beats empty, and both beat wrong.

Blob as Input, Not Just Output

Most systems treat object storage as a write-once destination. Here several features read it back as their data source, which is what makes it architecture rather than a filing cabinet:

The forecast scorecard walks the snapshot archive under macro/history/, finds every projection whose target month has since printed, and scores it against what actually happened. The platform's own track record is computed from its own back-catalogue.
Shared macro links pin a copy of the snapshot at the moment of sharing, so a link sent last week still renders exactly what the sender saw rather than silently updating underneath them.
The grade-by-regime study reads historical artefacts to ask whether company quality holds up when conditions turn.

What This Design Gets Wrong

A design document that only describes the parts that worked is marketing. These were found by auditing the storage layer against the code rather than against the intent, and they are listed because the useful output of an audit is the list of things it found.

Gap	Detail	Status
Deleted downloads were never deleted	The deletion path stamped a purge time and a deleted flag in the same write, but the purge worker selects on deleted with no purge time — so the row it had just been handed was already invisible to it. Every ZIP a user deleted stayed in storage. Account exports were worse: they create no file record at all, so nothing ever looked for them.	Fixed
A container named for the wrong environment	Production report workbooks are written to a container called `reports-dev`, because the batch consoles have no production container configuration and fall back to a checked-in default. Harmless until somebody tidying up deletes "the dev container".	Open
Silent fallback to local emulator	The storage service falls back to a local development emulator when no connection string is present, so a production misconfiguration degrades quietly instead of failing loudly. Failing fast would be better.	Open
No age-based download retention	Deletion is only ever user-initiated, yet a download's SAS URL expires after 1–24 hours. An unclicked archive becomes unreachable long before it becomes unreferenced, and is then stored indefinitely.	Open
No lifecycle policy, versioning, or soft delete	Nothing tiers, expires, or can be recovered after deletion on either storage account. Acceptable at current volume; a deliberate choice rather than an oversight only now that it is written down.	Open
This page described a system that did not exist	The previous version of this page documented hot/cool/archive tiering, CDN integration, chunked transfers and six containers. None of it was real. It was written from intent rather than from the code.	Fixed

The rule that would have prevented most of these is now the last item on the checklist for any feature that stores something: say how it gets deleted, in the design, before the code. Every gap above except the naming one is a deletion story nobody wrote down.