Database Design
The house rules the schema is written to, the review that turned 39 JSON columns into ten, and what a real MySQL 8 caught that reading the file never could.
The house rules
The standards are Allan's, written down so any future session designs the house way without re-deriving it. Their lineage is the leoparddata and Gmi.Ai schemas going back to about 2016. Four are non-negotiable.
Third normal form, everything. Every fact lives in exactly one place. Derived values are computed or live in an explicitly named rollup table, never stored beside their sources.
Exactly one primary key per table, a surrogate bigint AUTO_INCREMENT named <TableName>Key: TradingAccountsKey, StimuliKey. Never composite, never natural, never int. Natural uniqueness is a UNIQUE KEY beside the surrogate.
Foreign-key columns are Fk plus the referenced table's PK name, so the relationship reads straight off the DDL: FkTradingAccountsKey points at TradingAccounts.TradingAccountsKey. When the role matters it goes after Fk: FkEntryStimuliKey, FkKilledAtPipelineStageTypesKey. You should be able to draw the ER diagram from the DDL without looking anything else up.
Every fixed vocabulary is a parametric <Object>Types table with the same shape always: a key, a Code the application matches on, a Name, a Description so the meaning lives in the database, a SortOrder and IsActive. Rows retire with IsActive = 0 and are never deleted, because history references them forever. For code-backed vocabularies the rows are enforced documentation, not an extension point: the daemon asserts code-to-table agreement at startup.
Then the naming: tables are the plural of the object a row is, never a mass noun (NotificationMessages, not NotificationLog); junctions are the two names plus Mappings; string identifiers end in Code, never Id; timestamps are datetime(6) in UTC with a Utc suffix; money is decimal(18,6); flags on high-volume rows are a FlagBits bitmask with the bit meanings in a types table.
The JSON review
It started with a question from Allan: "I don't like having 39 JSON columns." The schema had 39 of them across 31 of its 57 substance tables, and none had a documented shape. A JSON column is a schema with no definition, which is a worse state than a table with a bad one: nothing can validate it, nothing can migrate it, and two writers can disagree without either being wrong.
Three questions were asked of each column, in order. Will anyone ever filter, join or aggregate on a field inside it? Then it is columns, or a child table, or a types table. Is it per-day-times-per-instrument volume, or more than about 1 KB nobody queries? Then it is blob behind a BlobRef. Is its shape genuinely variable, a config that depends on a type code or a model's structured answer? Then JSON is right, with a written shape and a version column beside it. Fail all three and it is a grab-bag: decide it, do not ship it.
| Verdict | Count | Meaning |
|---|---|---|
| KEEP | 9 | Genuinely a document. Stays JSON, gains a written schema and a version column |
| TYPE | 13 | A handful of scalars, or a list of typed facts. Becomes columns or a child table |
| BLOB | 10 | Per-day payload. Becomes a BlobRef plus summary columns |
| DECIDE | 7 | A DetailJson nobody had specified. Each one decided, mostly into TYPE |
Nine kept, thirty changed. One verdict changed during application: Stimuli.SnapshotJson had been marked BLOB on the reading that it snapshotted the world, but the table's own comment said it held the values that crossed, not the world. It was renamed TriggerJson, kept small, and given the link that was missing, a MarketStateBlobRef to the cycle's frozen state. Ten JSON columns survived, not nine, and an eleventh, AllocationModelConfigs, was added when the review noticed the types table named six allocation models and the schema held parameters for two.
The review found one structural defect rather than a style problem. Promotions had no foreign keys at all, because its evidence, the backtest run and the strategy version it promoted, was inside EvidenceJson. An audit trail whose links are in a text blob is not an audit trail. It now carries FkStrategyModulesKey, FkResolversKey and FkBacktestRunsKey, and the three promotion gates are reconstructible from the database.
Two amendments landed in the standards as a result. Every surviving JSON column carries three obligations: a sibling SchemaVersion column, its shape written down, and a C# record that is the shape. And the names DetailJson, DataJson, ExtraJson, MiscJson and InfoJson are banned; a column named for the fact that its contents were not decided is a defect. The eleven shapes that remain.
What loading it into MySQL 8 caught
The reworked schema was loaded into a real MySQL 8.0.46 rather than reviewed again. The load caught two defects the file never could.
Twenty-five foreign-key constraint names were over MySQL's 64-character identifier limit, and the load stopped at table 16. The naming pattern FK_<Table>_<RefTable>_<FkColumn> is descriptive, and a junction table referencing a types table produces a name that is descriptive and too long. The standards now carry a shortening rule applied in order until the name fits: drop the referenced-table segment, since the column already names it; then the Fk and Key decorations; then abbreviate Mappings; then drop Types. Never truncate arbitrarily. And the rule beneath it: validate every schema change by loading it, because a file cannot tell you its names are too long.
The second defect was a seed row with six values in a seven-column insert. The file had survived several reading passes with that in it.
Validate by enumeration, not by reading
The schema reached 89 tables and survived several review passes with a hole in it that blocked 42 of the 122 strategies in the catalog. Nothing was careless. SignalSources named every input and its freshness contract, which felt like the inputs were handled; it stored no values, so the macro series every rotation strategy needed had nowhere to live. A table that names things is not a table that holds them, and the naming table is exactly what made the missing storage invisible.
Two rules came out of it. A registry is not storage: for every input a specification names, ask where its values live. Validate by enumeration: the gap was invisible to abstract review and obvious within an afternoon of listing 122 concrete strategies and asking each whether it could actually run against these tables. A schema looks complete when you read it for elegance; it looks honest when you interrogate it with specific, named use cases.
The corollary worth stating: designing inward from the core workflow reliably produces rich journals and thin inputs. The data that causes the workflow is the part that gets assumed. Check that side twice.
- Composite or natural primary keys
- A column ending in
Keythat is not a PK or FK DetailJson,DataJson,ExtraJson,MiscJson,InfoJson- A JSON array of codes, or a CSV column
- Float or double for money
- DELETE on a vocabulary row