Quant Trading for Programmers 06: Make The Database Schema Explicit First

The previous article argued that data matters more than strategy. But data does not become real just because we have a download script. The real landing point is the database.

Starting from this article, the ZiQuant mainline project enters the second group of articles. Article 6 does not rush to download more market data. It first makes the PostgreSQL tables, SQLAlchemy metadata, and Alembic migrations explicit. Without a stable schema, the later stock universe, market-data cleaning, factors, and backtests will all become temporary code.

Why Start With Schema

Many quant demos can run with only CSV files and DataFrames. That is fast to write, but hard to trace.

A platform must answer questions like:

• Which stock universe did a strategy use?
• Which market-data snapshot was used on the backtest date?
• Which data source produced the bars?
• Were factor values recalculated later?
• Does a paper-trading order have a corresponding strategy and portfolio?

File names and comments cannot carry these answers for long. If the system is expected to evolve, users, stocks, stock pools, data sources, market bars, financial reports, factors, strategies, backtests, paper portfolios, and task records all need explicit tables.

Current Core Tables In ZiQuant

The project already has a group of core ORM models in app/models.py.

The most important groups are:

• zi_quant_stocks: stock master data.
• zi_quant_stock_pools, zi_quant_stock_pool_members: stock pools and members.
• zi_quant_data_source_configs: data-source configuration.
• zi_quant_market_bars: daily market bars.
• zi_quant_financial_reports: financial-report data.
• zi_quant_factor_definitions, zi_quant_factor_values: factor definitions and values.
• zi_quant_strategies: strategy configuration.
• zi_quant_backtest_runs, zi_quant_backtest_trades: backtest results and trades.
• zi_quant_paper_portfolios, zi_quant_paper_positions, zi_quant_paper_orders: paper portfolios, positions, and orders.

These tables are not here to look complete. Each one maps to capabilities that later chapters will build.

Write Schema Readiness As Code

Chapter 6 adds app/schema_checks.py. It starts with a small but useful task: read tables, columns, primary keys, foreign keys, and unique constraints from SQLAlchemy metadata, then decide whether the core tables are complete.

The key data structure is:

@dataclass(frozen=True)
class TableSummary:
    name: str
    columns: tuple[str, ...]
    primary_key: tuple[str, ...]
    foreign_keys: tuple[str, ...]
    unique_constraints: tuple[str, ...]

This does not connect to the database. It checks whether the schema declared in code satisfies the platform’s minimum requirements. That makes it suitable for unit tests: fast, deterministic, and not dependent on a local PostgreSQL instance.

The core function is:

def schema_readiness(required_tables: Iterable[str] = CORE_TABLES, metadata: MetaData | None = None) -> dict[str, object]:
    summaries = summarize_metadata(metadata)
    present = {summary.name for summary in summaries}
    required = set(required_tables)
    missing = sorted(required - present)
    empty_primary_key = sorted(summary.name for summary in summaries if not summary.primary_key)
    return {
        "status": "ready" if not missing and not empty_primary_key else "degraded",
        "table_count": len(summaries),
        "required_table_count": len(required),
        "missing_tables": missing,
        "tables_without_primary_key": empty_primary_key,
        "tables": [summary.__dict__ for summary in summaries],
    }

The point is not complexity. The point is turning “can the database structure still support the platform?” from a manual opinion into an automated check.

Alembic Also Needs A Check

Looking only at ORM models is not enough. Production should not depend on application startup creating tables automatically. It needs explicit migration files.

Chapter 6 adds migration_readiness():

def migration_readiness(project_root: Path | str = ".") -> dict[str, object]:
    root = Path(project_root)
    alembic_ini = root / "alembic.ini"
    versions_dir = root / "migrations" / "versions"
    versions = sorted(path.name for path in versions_dir.glob("*.py")) if versions_dir.exists() else []
    missing: list[str] = []
    if not alembic_ini.exists():
        missing.append("alembic.ini")
    if not versions_dir.exists():
        missing.append("migrations/versions")
    if not versions:
        missing.append("migration_versions")
    return {
        "status": "ready" if not missing else "degraded",
        "missing": missing,
        "revision_count": len(versions),
        "latest_revision_file": versions[-1] if versions else None,
    }

This is still lightweight, but it catches common mistakes: forgetting to commit alembic.ini, losing the migrations directory, or having no version files.

How The Tests Work

The corresponding tests live in tests/test_schema_checks.py.

def test_schema_readiness_checks_required_tables_and_primary_keys():
    ready = schema_readiness()
    assert ready["status"] == "ready"
    assert ready["missing_tables"] == []
    assert ready["tables_without_primary_key"] == []

    degraded = schema_readiness(required_tables={*CORE_TABLES, "zi_quant_missing_table"})
    assert degraded["status"] == "degraded"
    assert degraded["missing_tables"] == ["zi_quant_missing_table"]

The test intentionally covers a failure branch. Only testing the ready state would not prove that the check can actually warn.

Runnable Foundation Check

Article 6 is about schema readiness. The current project uses this command to reproduce the foundation capabilities from articles 01-08:

uv run python -m scripts.chapter_examples foundation-check

The output for this chapter is:

schema_status=ready means the core tables are complete in SQLAlchemy metadata. migration_status=ready means Alembic configuration and version files exist. It does not connect to production, but it quickly catches low-level problems such as missing models or migration files.

Hands-On Task

Clone the chapter 6 code:

git clone https://github.com/ax2/zi-quant-platform.git
cd zi-quant-platform
git checkout chapter-06
uv sync --extra dev
uv run pytest

Or run only the chapter test:

uv run pytest tests/test_schema_checks.py

The full test result for the chapter 6 tag is 150 passed. There are still two FastAPI on_event deprecation warnings from the existing lifecycle style; they do not affect this chapter.

Chapter Update And Repository

This chapter adds:

• app/schema_checks.py, summarizing tables, primary keys, foreign keys, and unique constraints from SQLAlchemy metadata.
• A lightweight Alembic migration-file readiness check.
• tests/test_schema_checks.py, turning schema readiness into a regression test.

Repository:

https://github.com/ax2/zi-quant-platform

Code for this chapter:

git clone https://github.com/ax2/zi-quant-platform.git
cd zi-quant-platform
git checkout chapter-06
uv sync --extra dev
uv run pytest tests/test_schema_checks.py

Summary

Before a quant platform touches real data, it needs to know its database boundary.

Article 6 is not about database performance tuning or complex modeling. It turns core tables, primary keys, foreign keys, unique constraints, and migration files into testable objects. The next article builds on this foundation and turns the raw A-share list into a reusable public stock universe.

WeChat

Follow ZiCode on WeChat

If this post was useful, you can follow later updates on WeChat as well.

X / Twitter

Follow @ax2_zicode

Faster technical notes, short thoughts, and new-post alerts are posted on X.

X Open X Profile @ax2_zicode