Grade My Investments Technology Observatory
GMI · TECHNOLOGY OBSERVATORY // ALL SYSTEMS NOMINAL
ENGINEERED BY LEOPARD DATA

Database Design & Why MySQL

A deliberately relational core: one battle-tested engine, integrity enforced by construction, and a schema that evolves through reviewed SQL — not guesswork.

PERSISTENCE STACK
rendering diagram…
flowchart TB
    subgraph APP[Application Tier - C# and .NET 10]
        WEB[Blazor Server<br/>Azure Functions]:::app
        WORK[11 Console Workers<br/>Report - Billing - Email - SMS]:::app
    end
    WEB --> EF[Entity Framework Core 9<br/>Pomelo MySQL Provider]:::svc
    WORK --> EF
    EF --> RES[Connection Resilience<br/>ServerVersion.AutoDetect<br/>EnableRetryOnFailure]:::svc
    RES --> DB[(MySQL - InnoDB<br/>utf8mb4_bin - 69 tables)]:::data
    DB --> INT[Integrity Layer<br/>70 Foreign Keys<br/>9 CHECK Constraints<br/>FOR UPDATE Row Locks]:::sec
    classDef app fill:#1f2630,stroke:#C0894E,color:#F3EFE8;
    classDef svc fill:#241c14,stroke:#E2B583,color:#F3EFE8;
    classDef data fill:#22201a,stroke:#C0894E,color:#EBD3AE;
    classDef sec fill:#1d2733,stroke:#5cc8e0,color:#eafaff;
Every read and write in GMI — from the web tier and the eleven background workers — funnels through Entity Framework Core and the Pomelo MySQL provider into a single InnoDB database, guarded by an integrity layer of foreign keys, CHECK constraints, and row locks.

Why MySQL

Grade My Investments is built on a Microsoft-first stack — C#, .NET 10, Blazor, Azure, Azure Functions — and complements that core with best-of-breed services where it matters. For the system of record we chose MySQL: a relational, ACID-compliant engine that has run production workloads at internet scale for two decades.

The data at the heart of GMI is intrinsically relational — accounts own credit cards, symbol lists, folders and files; report jobs generate charges; charges roll up into invoices; invoices are settled by payments. Those relationships are the business rules, and we wanted the database itself to enforce them rather than trusting application code to remember. A mature SQL engine with strong foreign-key and constraint support was the natural fit, and MySQL pairs cleanly with .NET through the open-source Pomelo Entity Framework Core provider — giving us LINQ, migrations, and change tracking with zero friction against the Microsoft toolchain.

MySQL is also operationally boring in the best way: predictable, well-understood, inexpensive to host on Azure, and backed by an enormous body of tooling and expertise. For a lean SaaS that needs to move fast without babysitting its datastore, that reliability is a feature.

MySQL vs. the Alternatives

Option Why It Was Considered Why MySQL Won
MySQL Chosen Relational, ACID, InnoDB, first-class Pomelo EF Core provider, cheap Azure hosting, ubiquitous expertise. Best balance of integrity, cost, .NET integration, and operational simplicity for our workload.
Azure SQL / SQL Server The "native" Microsoft choice with excellent EF Core support. Materially higher hosting cost for a lean SaaS, with no capability we actually needed over InnoDB.
PostgreSQL Superb standards compliance and advanced feature set (JSONB, extensions). Our model is classic relational — the advanced features were surplus, and team fluency in MySQL was deeper.
NoSQL (Cosmos DB / Mongo) Elastic scale and schema flexibility. We want a fixed schema and cross-entity transactions (billing, allowances). Denormalizing would cost integrity, not buy it.

Where document-shaped data is genuinely useful — like the cached grade summary for public ticker pages — we keep it inside MySQL as a JSON column (publictickers.SummaryJson) rather than reaching for a second datastore.

Design Principles

Normalized Relational Core

A ~67-table normalized model — one fact in one place. Accounts, cards, symbol lists, folders, files, jobs, charges, invoices and payments are distinct entities linked by keys, not duplicated blobs.

Integrity by Construction

Business invariants live in the schema: ~70 foreign keys, ~9 CHECK constraints (e.g. payments must be strictly positive; charges may go negative for credits and refunds), and unique keys — not just application checks.

Money Is Exact

Every monetary column is decimal(19,4) — never floating point. Rounding is explicit and auditable, so a customer's balance always reconciles to the cent.

Unicode Everywhere

Every table is InnoDB with utf8mb4 / utf8mb4_bin — full Unicode (including emoji and every ticker's native characters) and transactional storage as the default, not an afterthought.

Concurrency & Correctness

A pay-as-you-go platform cannot afford a double-charge or a blown free-tier cap under a race. The money and allowance paths lean on InnoDB's transactional guarantees directly:

  • Pessimistic row locks: report submission takes a SELECT … FOR UPDATE on the account row and re-checks the monthly allowance inside the transaction, so two concurrent submits can't both slip past the free-tier limit.
  • Atomic claims: background senders claim a pending email/SMS row transactionally before dispatch, so no message is ever sent twice — even if two worker instances run at once.
  • Idempotent billing: an invoice already marked paid can never be charged again; a re-run of a billing period is a safe no-op.
  • Resilient connections: every context is configured with EnableRetryOnFailure, and multi-statement transactions run inside an EF Core execution strategy that retries transient faults from a clean change-tracker.

The result: correctness is enforced at the layer that can actually guarantee it — the database transaction — not hoped for in application code.

Schema Evolution

GMI does not let a framework silently mutate the production schema. Structure changes are hand-authored, reviewed SQL:

Reviewed Migrations
  • Each change ships as a dated file in migrations/
  • Applied deliberately to production — never auto-run by a deploy
  • Migrations land before the code that depends on them
  • Backfills are explicit and idempotent
Single Source of Truth
  • gmi.sql is the comprehensive fresh-deploy schema
  • Kept in lock-step with every migration
  • Entity model (Gmi.ClassLibrary) maps 1:1 to the tables
  • EF Core auto-detects the server version at startup

This discipline is exactly what let the free-tier launch and four hardening rounds add columns like jobs.IsFreeTierJob, jobs.CreatedDateTime and whole tables like generationratelimitevents and failedpaymentreconciliations without a single unplanned schema surprise in production.

Schema by the Numbers
  • ~67 normalized tables
  • ~70 foreign-key constraints
  • ~9 CHECK constraints
  • InnoDB engine, transactional
  • utf8mb4_bin full-Unicode collation
  • decimal(19,4) for all money
The Access Layer
  • EF Core + Pomelo 9.0
    MySQL provider for .NET
  • Request-scoped context
    One unit-of-work per request/job
  • ServerVersion.AutoDetect
    No hard-coded engine version
  • EnableRetryOnFailure
    Transient-fault resilience
  • LINQ → SQL
    Type-safe queries, no string SQL
See the Data Access Layer →
Quick Answers
  • Why not SQL Server?
    No feature gap for us, materially higher hosting cost.
  • Why not NoSQL?
    Our model is relational and transactional — a fixed schema is a feature.
  • Why not EF auto-migrations?
    Production schema changes are reviewed SQL, applied on purpose.
The Relational Core
rendering diagram…
flowchart TB
    ACC[accounts]:::core
    ACC --> CC[creditcards]:::rel
    ACC --> SL[symbollists]:::rel
    ACC --> JOB[jobs]:::rel
    ACC --> CHG[charges]:::rel
    ACC --> INV[invoices]:::rel
    SL --> SLI[symbollistitems]:::leaf
    JOB --> CAJ[claudeanalysisjobs]:::leaf
    CHG --> IC[invoicecharges]:::leaf
    INV --> IC
    INV --> PAY[payments]:::leaf
    classDef core fill:#241c14,stroke:#E2B583,color:#F3EFE8;
    classDef rel fill:#1f2630,stroke:#C0894E,color:#F3EFE8;
    classDef leaf fill:#22201a,stroke:#5A616B,color:#C4C0B8;
The account sits at the center of the model; billing and content entities hang off it through foreign keys.
Explore the full Database Schema →