Section IV Schema · Bitemporality · Migrations Figures 6–7

FutureMarket

An autonomous equity-research loop — ingestion, scoring, execution, and self-grading — described from the source

The Ledger

One table holds the system together, and it has no foreign keys

Thirteen tables in one SQLite file. Twelve of them are ordinary. The thirteenth — the prediction log — is the pivot on which every self-evaluation mechanism turns, and it is deliberately decoupled from the rest of the schema by a string.

Engine: SQLite, single file 13 ORM models · 3 Alembic revisions Bitemporal columns on 3 tables

Every table that models the market proper hangs off stocks by integer foreign key: prices, predictions, agent analyses, backtest results. That is the original schema, and it is conventional. Then there is prediction_log, which stores its symbol as a plain string with an index and no relationship at all.

The decoupling is almost certainly deliberate and is the right call. The prediction log is an append-only accountability record, and it must survive a symbol being removed from the watchlist — which the delete route does with a cascade. If the log were joined to stocks, removing a ticker would erase the evidence of every call ever made about it, including the ones that were wrong. Storing the symbol as text means the record outlives the subject.

The cost is that nothing enforces referential integrity on the most important table in the system, and a typo in a symbol produces an orphan row that will never reconcile and never be noticed.

Two of the log's columns carry the whole feedback apparatus. entry_price is captured at the moment the call is made; without it, no return can ever be computed. strategy_id is a free-text label identifying which component made the call. Every downstream mechanism — the Elo tournament, the calibration tracker, the backtester, the adaptive weighting — groups by that string. There is no enumeration constraining it, and no registry of valid values.

Three columns are written later by a scheduled job rather than at insert time: the realised return at one, five and twenty days. A fourth, whether the direction was correct, is filled only for the one-day horizon. Until that job runs, a row is a claim; afterwards, it is evidence.

A fifth column, a hash of the prompt that generated the prediction, exists to join calls back to prompt variants for A/B testing. The hashing code is written and correct. No caller in the repository passes a prompt to it, so the column is universally null and the A/B system that depends on it cannot function.

The prediction log stores its symbol as text so that the record outlives the subject. Delete a ticker and the evidence of every wrong call about it survives.

On the one table with no foreign key

Figure 6 · Schema

Thirteen tables in three clusters

Column names below are as declared in the ORM. Not every column is shown — the wider tables are abbreviated — but every table is present, and no table has been invented.

Figure 6 Entity–relationship map, backend/models/
FutureMarket database schema, thirteen tables Three vertical clusters. The market cluster on the left contains stocks, stock_prices, predictions, agent_analyses and backtest_results, joined by integer foreign keys. The accountability cluster in the centre contains prediction_log, which is joined to nothing, plus strategy_ratings, backtest_runs and signal_weight_log, which are linked to it only by a shared strategy_id string. The research cluster on the right contains alpha_factors, stock_suggestions, forecast_cache and users, all standalone. MARKET CLUSTER — FK-joined stocks PK id UQ symbol · name · sector last_updated delete cascades to children stock_prices PK id FK stock_id → stocks.id timestamp « as_of » open · high · low · close volume available_at « indexed » predictions PK id FK stock_id → stocks.id target_date · timeframe predicted_price confidence_score · analysis_mode available_at agent_analyses FK prediction_id → predictions.id agent_type · analysis_text sentiment_score · key_factors JSON 12 rows per deep analysis; 4 identical backtest_results FK prediction_id · actual_price accuracy_percentage · evaluated_at legacy — superseded, no writer ACCOUNTABILITY CLUSTER — joined by string only prediction_log THE PIVOT PK id date « indexed » symbol — STRING, NO FK strategy_id « the group-by key » written at call time — predicted_direction UP|DOWN|FLAT predicted_magnitude confidence 0.0–1.0, clamped composite_score 0–100 entry_price « required for any return » model_version · available_at prompt_hash « always NULL » written days later by reconciliation — actual_return_1d / 5d / 20d · correct_direction grouped by strategy_id — no constraint, no registry strategy_ratings UQ strategy_id elo_rating default 1500.0 wins · losses · draws · total_comparisons win_rate · avg_confidence · last_updated one row per strategy, updated in place backtest_runs strategy_id « indexed » · symbol start_date · end_date train_window_days · test_window_days win_rate · sharpe_ratio · max_drawdown profit_factor · annualized_return graduated BOOL · window_results JSON calibration_score, elo_rating_post — declared, never written signal_weight_log timestamp « indexed » weights JSON · source_accuracies JSON reason · window_days written nightly — never read back at startup RESEARCH & CACHE — standalone alpha_factors name · category « indexed » expression « LLM-authored formula » description ic_mean · ic_std · last_ic icir « indexed — the ranking key » sharpe · turnover · returns_1d status « indexed » candidate → active → decayed → retired eval_count · symbols_tested JSON generation, parent_id, returns_5d, max_drawdown — declared, never written stock_suggestions symbol « indexed » · name · sector score 0–100 · signal brief_thesis · detailed_thesis key_factors JSON · predicted_upside dismissed BOOL · expires_at 24-hour expiry; screener output forecast_cache symbol « indexed » · days technical_score last_actual_price · last_actual_date forecast_data JSON · expires_at a cache table, not a record of belief users UQ email · hashed_password display_name · is_active · created_at no endpoint depends on auth; the frontend never calls the auth routes Migrations — three Alembic revisions, applied to head on every startup; a create-all fallback runs if the upgrade raises. Concurrency — the engine is constructed with same-thread checking disabled, unconditionally. Journal mode and busy timeout are left at defaults. Portability — the SQLite-specific connection argument is passed regardless of dialect, so pointing the URL at another engine raises on connect.
Figure 6. The schema, by cluster. Dashed borders mark tables with no active writer or no dependent code path. The vermilion block is prediction_log; note that the three tables beneath it relate to it only through a shared strategy_id string, with no database-level linkage — the entire self-evaluation apparatus is joined by convention rather than constraint. backend/models/*.py — thirteen declarative classes · backend/database.py:10–20 · backend/alembic/versions/ (3 revisions)

Figure 7 · Bitemporality

When it happened, and when you could have known

A daily bar for a Tuesday describes Tuesday. But you could not have acted on it during Tuesday's session — it did not exist until the close. Any backtest that treats the bar's own date as the moment it became usable will systematically buy at prices it could not have seen, and will report returns that cannot be reproduced.

The countermeasure is two timestamps per row instead of one. timestamp records what period the row describes. available_at records when the row became knowable. A point-in-time query filters on the second, never the first.

FutureMarket implements this on three tables, and provides three query helpers that apply the filter. Each helper permits a legacy fallback for rows written before the column existed: if available_at is null, treat the row as knowable four hours after its own timestamp.

Two honest qualifications. The docstring on that fallback describes a one-day offset — daily bars are typically available the next morning — while the implemented constant is four hours. And the helper that stamps the column on insert promises, in its docstring, to apply a market-close offset for price rows; it does not. It stamps wall-clock time.

The deeper limitation is scope. The guards exist and are correct, but they are only applied where they are called. Alpha-factor evaluation reads price history directly rather than through the point-in-time helper, so factor fitting is not protected. The walk-forward backtester's docstring claims to respect the column; the string available_at does not appear anywhere in that file.

Where the guard is applied

Price history
Guarded. Default lookback one trading year.
Prediction log lookups
Guarded, capped at 100 rows.
Latest prediction
Guarded.
Alpha-factor fitting
Not guarded — reads all price rows directly.
Walk-forward backtest
Not guarded — relies on rows having been written live.

A one-shot migration backfills the column on all three tables, loading every null row into memory without batching. It is covered by fifteen test functions.

Figure 7 Two timelines: as-of versus available-at, bitemporal.py
Bitemporal filtering of price bars against a decision time Two parallel timelines. The upper as-of axis shows five daily bars at their nominal dates. The lower available-at axis shows the same bars shifted right by the delay before they became knowable. A vertical decision-time line cuts both axes. Under a naive filter on the as-of date, four bars are admitted, one of which was not yet knowable. Under the bitemporal filter on available-at, only three bars are admitted. The difference is labelled as look-ahead leakage. decision_time AXIS 1 — timestamp « as_of » what period the row describes Mon Tue Wed Thu Fri Naive filter timestamp < decision_time admits four bars — Mon, Tue, Wed and Thu. shift right by publication delay AXIS 2 — available_at when the row became knowable Mon Tue Wed Thu Fri Bitemporal filter available_at ≤ decision_time admits three — Thursday's bar was not yet published. THE DIFFERENCE one bar of look-ahead leakage THE IMPLEMENTED PREDICATE — a disjunction, to tolerate legacy rows available_at <= decision_time OR ( available_at IS NULL AND timestamp < decision_time − 4 hours ) The docstring beside this predicate describes a one-day offset. The constant in the code is four hours. The migration that backfills the column uses four hours, so the code is at least self-consistent.
Figure 7. The two axes. The dashed Thursday box on the lower axis is the bar a naive filter would have admitted and the bitemporal filter correctly rejects. In a single query this looks like a rounding error; compounded across a backtest it is the difference between a strategy that works on paper and one that works. backend/services/bitemporal.py:31–72 (price query), :75–105 (prediction log), :108–136 (latest prediction), :139–150 (insert stamp), :153–181 (backfill) · columns at models/stock_price.py:15,21 · models/prediction_log.py:27 · models/prediction.py:21