Database Schema & Prisma
Relevant source files
The following files were used as context for generating this wiki page:
The ZINC-Fusion-V15 project utilizes a cloud-hosted Prisma Postgres database as its authoritative single source of truth for all quantitative forecasting operations. The database is structured using an institutional architecture comprising 12 distinct schemas designed to isolate raw data ingestion from derived features, model artifacts, and operational logs.
Prisma is employed exclusively for schema management and migrations, while runtime data access is handled via psycopg2 in Python and a pg Pool in the TypeScript frontend to maintain performance during high-frequency forecasting tasks.
Sources: AGENTS.md:18-24, src/fusion/config.py:11-15, README.md:110-112
Institutional Schema Architecture
The database follows a strict 12-schema hierarchy. This structure ensures that append-only landing tables are separated from mutable analytics tables and model registry artifacts.
Schema Categorization
The project categorizes these schemas into four primary functional groups:
| Category | Schemas | Purpose |
|---|---|---|
| Landing | mkt, econ, alt, pos, supply | Append-only source data from external APIs (FRED, Yahoo, etc.). |
| Derived | features, training | Engineered feature stores and prepared training matrices. |
| Output | model, forecasts, analytics | Model registries, quantile predictions, and dashboard-ready scores. |
| Governance | ops, vegas | System telemetry, ingest logs, and sales intelligence. |
Sources: src/fusion/config.py:74-92, AGENTS.md:26-28, README.md:115-122
Banned Schemas
The system enforces a policy against "legacy" naming conventions. The following schemas are explicitly banned and will trigger failures in preflight or SQL reference checks: raw, gold, silver, bronze, monitoring, specialist, weather, and archive.
Sources: src/fusion/config.py:95-103, scripts/check_sql_table_references.py:33-42, scripts/preflight_check.py:121-130
Data Flow & Table Relationships
The database acts as the central hub for the ensemble ML pipeline. Data moves from external sources through landing schemas into the training matrix before being published as forecasts.
The diagram illustrates the pipeline from raw ingestion to dashboard-ready analytical scores.
Sources: README.md:125-135, src/fusion/api/server.py:220-240
Key Data Models
Training & Out-of-Fold (OOF) Tables
The system tracks the integrity of its 19-model ensemble using specialized training tables.
training.matrix_1d: The primary point-in-time (PIT) correct matrix containing 213+ features used for AutoGluon training.training.oof_core_1d: Stores out-of-fold predictions. Columns includetrade_date,symbol,horizon_days, and the quantile tripletp30,p50,p70.training.specialist_signals_1d: A horizon-agnostic table containing signals from the 11 specialists (e.g., Crush, China, Tariff).
Sources: README.md:125-135, AGENTS.md:68-75
Forecasting & Analytics
Predictions are finalized in the forecasts and analytics schemas for frontend consumption.
forecasts.forecast_quantiles: Published multi-step forecasts includingp10,p50, andp90.analytics.driver_scores: Decomposed model signals showingspecialist,signal,direction, andshap_contribution.analytics.price_15m: Intraday price data for real-time dashboard updates.
Sources: src/fusion/api/server.py:220-225, src/fusion/api/server.py:440-450, src/fusion/api/server.py:530-540
Implementation & Constraints
Connection Configuration
The system requires direct PostgreSQL connection strings (postgres://) for psycopg2 and sqlalchemy paths. The DATABASE_URL is configured in the environment and validated by src/fusion/config.py.
# src/fusion/config.py:28-32
DATABASE_URL = (
os.environ.get("DIRECT_DATABASE_URL")
or os.environ.get("POSTGRES_URL")
or os.environ.get("DATABASE_URL")
)
Sources: src/fusion/config.py:28-32, scripts/preflight_check.py:165-175
Data Integrity Rules
The project architecture imposes several strict constraints on database operations:
- Point-in-Time (PIT) Correctness: Every row in landing tables must represent what was known at that time to prevent lookahead bias in ML training.
- Naming Conventions: Tables must follow the pattern
schema.entity_granularity(e.g.,mkt.futures_1d). - Read-Only API: The FastAPI server implements a
_validate_readonly_sqlhelper to ensure the/api/db/queryendpoint only executesSELECTorWITHstatements.
Sources: src/fusion/api/server.py:55-70, scripts/check_sql_table_references.py:100-115, AGENTS.md:80-90
Conclusion
The database schema in ZINC-Fusion-V15 is a highly structured environment that enforces institutional data standards. By utilizing Prisma for schema definitions and migrations while relying on direct drivers for runtime performance, the system maintains a robust foundation for multi-horizon soybean oil forecasting. The strict 12-schema architecture serves as a critical guardrail, ensuring data purity and operational transparency across the entire ML pipeline.
Sources: AGENTS.md:18-28, src/fusion/config.py:74-92