Database Schema & Migrations

The Database Schema & Migrations system manages the durable persistence of financial assets and their complex inter-relationships. This system provides a structured storage layer that replaces ephemeral in-memory graphs, enabling system restarts, disaster recovery, and multi-instance synchronization. The architecture enforces SQLite compatibility for local development while supporting the expansion of the Financial Asset Relationship Database into production environments.

Sources: GEMINI.md:37-41, AUDIT_REPORT.md:144-147, ARCHITECTURE.md:12-15

Data Architecture

The system utilizes a relational model to represent graph structures, where assets serve as nodes and relationships serve as weighted edges. The architecture prioritizes resource safety through the use of context managers for session management and connection pooling.

This diagram illustrates the core entity relationships within the persistence layer.
Sources: src/data/db_models.py, migrations/001_initial.sql

Core Entities

The schema defines three primary tables to support financial modeling:

Sources: migrations/001_initial.sql, src/data/db_models.py

Migration Framework

The migration framework ensures that database schema versions remain consistent across different environments. It utilizes SQL-based migration files to apply incremental changes to the underlying storage.

Migration Execution Flow

When the system initializes, the migration engine checks the current schema version and applies pending SQL scripts in sequential order.

This flow represents the initialization and update sequence for the database schema.
Sources: src/data/migrations.py, src/data/database.py

Invariant Constraints

The migration system and database operations must adhere to specific engineering mandates:

• Idempotency: All migration operations must be safe against partial failure and process termination.
• Resource Management: All sessions and file handles use context managers for absolute disposal pathways.
• SQLite Compatibility: Migrations MUST maintain SQLite compatibility for local development workflows.

Sources: GEMINI.md:15-18, GEMINI.md:37-39

Implementation Details

Session Management

The database layer handles connections through a centralized manager that enforces timeout bounds.

# src/data/database.py
class DatabaseManager:
    def __init__(self, connection_string: str):
        self.engine = create_engine(connection_string)
        self.SessionLocal = sessionmaker(bind=self.engine)

    @contextmanager
    def get_session(self):
        """Provide a transactional scope around a series of operations."""
        session = self.SessionLocal()
        try:
            yield session
            session.commit()
        except Exception:
            session.rollback()
            raise
        finally:
            session.close()

Sources: src/data/database.py:10-25, GEMINI.md:19-21

Schema Definition

The initial schema setup provides the foundational tables required for the graph persistence.

-- migrations/001_initial.sql
CREATE TABLE assets (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    asset_class VARCHAR(20) NOT NULL,
    market_cap FLOAT,
    currency VARCHAR(3),
    details TEXT -- Stores JSON blob for class-specific attributes
);

CREATE TABLE relationships (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id VARCHAR(50),
    target_id VARCHAR(50),
    type VARCHAR(30),
    strength FLOAT CHECK (strength >= 0 AND strength <= 1),
    FOREIGN KEY (source_id) REFERENCES assets(id),
    FOREIGN KEY (target_id) REFERENCES assets(id)
);

Sources: migrations/001_initial.sql:1-20

Conclusion

The Database Schema & Migrations system provides the necessary durability layer for the financial graph. By enforcing SQLite compatibility and strict resource management, the system ensures reliable persistence for asset relationships across restarts and multi-instance deployments. This structure facilitates accurate impact modeling and interactive visualizations by providing a consistent source of truth for the AssetRelationshipGraph.

Sources: GEMINI.md:37-41, AUDIT_REPORT.md:204-208