> ## Documentation Index
> Fetch the complete documentation index at: https://village-docs.villagelabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Layer

> Immutable, versioned database architecture

## The System of Record

The Data Layer is a **versioned, relational database** that serves as the immutable system of record for all modeling activities. Every input, execution, and output is permanently archived with full audit trails.

<Info>
  **Core Principle:** Nothing is ever overwritten. All data is append-only, creating perfect reproducibility and temporal analysis capabilities.
</Info>

## Database Architecture

<Frame>
  ```mermaid theme={null}
  erDiagram
      SCENARIOS ||--o{ SIMULATION_RUNS : generates
      CENSUSES ||--o{ SIMULATION_RUNS : uses
      SIMULATION_RUNS ||--o{ ANNUAL_COMPANY_STATES : produces
      SIMULATION_RUNS ||--o{ ANNUAL_TRUST_STATES : produces
      SIMULATION_RUNS ||--o{ ANNUAL_PARTICIPANT_SNAPSHOTS : produces
      SIMULATION_RUNS ||--o{ PROCESSING_LOGS : records
      SCENARIOS ||--|| PLAN_RULES : contains
      SCENARIOS ||--|| OPERATING_ASSUMPTIONS : contains
  ```
</Frame>

## Core Tables

### Input Tables

<Tabs>
  <Tab title="Scenarios">
    Immutable records of all versioned scenario configurations.

    ```sql theme={null}
    CREATE TABLE scenarios (
        id VARCHAR PRIMARY KEY,
        created_at TIMESTAMP,
        created_by VARCHAR,
        name VARCHAR,
        description TEXT,
        plan_rules_id VARCHAR,
        operating_assumptions_id VARCHAR,
        version INTEGER,
        parent_scenario_id VARCHAR  -- For scenario variants
    );
    ```

    **Key Features:**

    * Every scenario is versioned
    * Scenarios can fork from parent scenarios
    * Immutable once created
  </Tab>

  <Tab title="Censuses">
    Versioned participant data snapshots.

    ```sql theme={null}
    CREATE TABLE censuses (
        id VARCHAR PRIMARY KEY,
        created_at TIMESTAMP,
        census_year INTEGER,
        participant_count INTEGER,
        version INTEGER,
        data_source VARCHAR  -- 'actual' or 'projected'
    );

    CREATE TABLE census_participants (
        census_id VARCHAR,
        participant_id VARCHAR,
        age INTEGER,
        service_years DECIMAL,
        compensation DECIMAL,
        allocated_shares DECIMAL,
        vested_percentage DECIMAL,
        PRIMARY KEY (census_id, participant_id)
    );
    ```
  </Tab>

  <Tab title="Plan Rules">
    Legal framework configurations.

    ```sql theme={null}
    CREATE TABLE plan_rules (
        id VARCHAR PRIMARY KEY,
        plan_name VARCHAR,
        plan_year_end VARCHAR,
        vesting_schedule JSONB,
        distribution_policy JSONB,
        diversification_rules JSONB,
        cash_usage_policy JSONB,
        created_at TIMESTAMP
    );
    ```
  </Tab>

  <Tab title="Operating Assumptions">
    Annual strategy settings.

    ```sql theme={null}
    CREATE TABLE operating_assumptions (
        id VARCHAR PRIMARY KEY,
        contribution_policy JSONB,
        share_valuation JSONB,
        repurchase_strategy JSONB,
        financial_projections JSONB,
        turnover_assumptions JSONB,
        created_at TIMESTAMP
    );
    ```
  </Tab>
</Tabs>

### Processing Tables

<Tabs>
  <Tab title="Simulation Runs">
    Execution metadata connecting inputs to outputs.

    ```sql theme={null}
    CREATE TABLE simulation_runs (
        id VARCHAR PRIMARY KEY,
        scenario_id VARCHAR,
        census_id VARCHAR,
        system_config JSONB,
        
        started_at TIMESTAMP,
        completed_at TIMESTAMP,
        status VARCHAR,  -- 'running', 'completed', 'failed'
        
        projection_start_year INTEGER,
        projection_end_year INTEGER,
        
        error_message TEXT,
        execution_metrics JSONB  -- performance stats
    );
    ```

    **The Logbook:** This table is the permanent record connecting specific inputs to specific outputs.
  </Tab>

  <Tab title="Processing Logs">
    Step-by-step audit trail.

    ```sql theme={null}
    CREATE TABLE processing_logs (
        id SERIAL PRIMARY KEY,
        simulation_run_id VARCHAR,
        year INTEGER,
        step_number INTEGER,
        step_name VARCHAR,
        
        timestamp TIMESTAMP,
        duration_ms INTEGER,
        
        inputs JSONB,
        outputs JSONB,
        decisions JSONB,
        warnings TEXT[]
    );
    ```

    **Complete Transparency:** Every decision the engine makes is logged.
  </Tab>

  <Tab title="Events">
    All modeled transactions.

    ```sql theme={null}
    CREATE TABLE events (
        id SERIAL PRIMARY KEY,
        simulation_run_id VARCHAR,
        year INTEGER,
        event_type VARCHAR,  -- 'termination', 'repurchase', etc.
        participant_id VARCHAR,
        
        event_date DATE,
        shares_affected DECIMAL,
        cash_amount DECIMAL,
        
        details JSONB,
        created_at TIMESTAMP
    );
    ```
  </Tab>
</Tabs>

### Output Tables

<Tabs>
  <Tab title="Annual Company States">
    Year-by-year company financial snapshots.

    ```sql theme={null}
    CREATE TABLE annual_company_states (
        simulation_run_id VARCHAR,
        year INTEGER,
        source_type VARCHAR,  -- 'actual' or 'simulated'
        
        revenue DECIMAL,
        ebitda DECIMAL,
        total_payroll DECIMAL,
        esop_contribution DECIMAL,
        
        financial_metrics JSONB,
        
        PRIMARY KEY (simulation_run_id, year)
    );
    ```

    **Source Type:** Distinguishes historical actuals from future projections.
  </Tab>

  <Tab title="Annual Trust States">
    Year-by-year ESOP trust snapshots.

    ```sql theme={null}
    CREATE TABLE annual_trust_states (
        simulation_run_id VARCHAR,
        year INTEGER,
        source_type VARCHAR,
        
        total_shares_outstanding DECIMAL,
        allocated_shares DECIMAL,
        suspense_shares DECIMAL,
        unallocated_shares DECIMAL,
        
        cash_ledger JSONB,
        loans JSONB,
        share_price DECIMAL,
        
        PRIMARY KEY (simulation_run_id, year)
    );
    ```
  </Tab>

  <Tab title="Annual Participant Snapshots">
    Individual participant account states per year.

    ```sql theme={null}
    CREATE TABLE annual_participant_snapshots (
        simulation_run_id VARCHAR,
        year INTEGER,
        participant_id VARCHAR,
        source_type VARCHAR,
        
        allocated_shares DECIMAL,
        vested_percentage DECIMAL,
        vested_shares DECIMAL,
        account_value DECIMAL,
        cash_balance DECIMAL,
        
        status VARCHAR,  -- 'active', 'terminated', etc.
        age INTEGER,
        service_years DECIMAL,
        
        diversification_eligible BOOLEAN,
        
        PRIMARY KEY (simulation_run_id, year, participant_id)
    );
    ```
  </Tab>
</Tabs>

## Versioning Strategy

### Scenario Versioning

Scenarios can be versioned to track changes over time:

```python theme={null}
# Original scenario (June 2024)
scenario_v1 = Scenario(
    id="acme_base",
    version=1,
    contribution_amount=500_000
)

# Updated scenario (September 2024)
scenario_v2 = Scenario(
    id="acme_base",
    version=2,
    parent_scenario_id="acme_base_v1",
    contribution_amount=450_000,  # Reduced
    change_log="Reduced contribution due to market conditions"
)
```

### Source Type Classification

All data is tagged with `source_type`:

* **`'actual'`**: Historical, verified data
* **`'simulated'`**: Forecasted data from model runs

This enables powerful queries:

```sql theme={null}
-- Compare actual vs. projected for year 2024
SELECT 
    actual.repurchase_amount as actual_repurchases,
    simulated.repurchase_amount as projected_repurchases,
    (actual.repurchase_amount - simulated.repurchase_amount) as variance
FROM annual_trust_states actual
JOIN annual_trust_states simulated
    ON actual.year = simulated.year
WHERE actual.source_type = 'actual'
    AND simulated.source_type = 'simulated'
    AND actual.year = 2024;
```

## Temporal Analysis Capabilities

The immutable architecture enables sophisticated temporal queries:

<AccordionGroup>
  <Accordion title="Forecast Evolution" icon="chart-line">
    Track how your forecasts changed over time:

    ```sql theme={null}
    -- How did our 2028 repurchase forecast evolve?
    SELECT 
        sr.completed_at as forecast_date,
        ats.repurchase_amount as projected_2028_repurchases
    FROM simulation_runs sr
    JOIN annual_trust_states ats 
        ON sr.id = ats.simulation_run_id
    WHERE ats.year = 2028
        AND ats.source_type = 'simulated'
    ORDER BY sr.completed_at;
    ```
  </Accordion>

  <Accordion title="Assumption Impact" icon="code-compare">
    Identify which assumption changes drove result differences:

    ```sql theme={null}
    -- What changed between runs?
    SELECT 
        jsonb_diff(
            old_run.operating_assumptions,
            new_run.operating_assumptions
        ) as assumption_changes
    FROM scenarios old_run, scenarios new_run
    WHERE old_run.id = 'acme_v1'
        AND new_run.id = 'acme_v2';
    ```
  </Accordion>

  <Accordion title="Accuracy Analysis" icon="bullseye">
    Compare forecasts to actuals to measure model accuracy:

    ```sql theme={null}
    -- How accurate was our 2024 forecast made in 2023?
    SELECT 
        projected.repurchase_amount as forecast,
        actual.repurchase_amount as actual,
        ABS(projected.repurchase_amount - actual.repurchase_amount) / 
            actual.repurchase_amount as error_percentage
    FROM annual_trust_states projected
    JOIN annual_trust_states actual
        ON projected.year = actual.year
    WHERE projected.source_type = 'simulated'
        AND projected.simulation_run_id = 'run_2023_forecast'
        AND actual.source_type = 'actual'
        AND actual.year = 2024;
    ```
  </Accordion>
</AccordionGroup>

## Data Integrity

### Constraints & Validation

```sql theme={null}
-- Ensure share conservation
ALTER TABLE annual_trust_states
    ADD CONSTRAINT shares_balance_check
    CHECK (
        total_shares_outstanding = 
        allocated_shares + suspense_shares + unallocated_shares
    );

-- Prevent negative cash
ALTER TABLE annual_trust_states
    ADD CONSTRAINT cash_positive_check
    CHECK (
        (cash_ledger->>'total_cash')::DECIMAL >= 0
    );
```

### Referential Integrity

```sql theme={null}
-- All simulation runs must reference valid scenarios
ALTER TABLE simulation_runs
    ADD FOREIGN KEY (scenario_id) 
    REFERENCES scenarios(id);

-- All participant snapshots must reference valid runs
ALTER TABLE annual_participant_snapshots
    ADD FOREIGN KEY (simulation_run_id)
    REFERENCES simulation_runs(id)
    ON DELETE CASCADE;
```

## Query Patterns

### Common Queries

<Tabs>
  <Tab title="Get Latest Run">
    ```sql theme={null}
    SELECT * FROM simulation_runs
    WHERE scenario_id = 'acme_base'
    ORDER BY completed_at DESC
    LIMIT 1;
    ```
  </Tab>

  <Tab title="Compare Scenarios">
    ```sql theme={null}
    SELECT 
        s1.year,
        s1.repurchase_amount as scenario_a,
        s2.repurchase_amount as scenario_b,
        s2.repurchase_amount - s1.repurchase_amount as difference
    FROM annual_trust_states s1
    JOIN annual_trust_states s2 
        ON s1.year = s2.year
    WHERE s1.simulation_run_id = 'run_scenario_a'
        AND s2.simulation_run_id = 'run_scenario_b';
    ```
  </Tab>

  <Tab title="Peak Cash Year">
    ```sql theme={null}
    SELECT year, repurchase_amount
    FROM annual_trust_states
    WHERE simulation_run_id = 'run_123'
    ORDER BY repurchase_amount DESC
    LIMIT 1;
    ```
  </Tab>

  <Tab title="Participant Timeline">
    ```sql theme={null}
    SELECT 
        year,
        allocated_shares,
        vested_shares,
        account_value
    FROM annual_participant_snapshots
    WHERE simulation_run_id = 'run_123'
        AND participant_id = 'EMP001'
    ORDER BY year;
    ```
  </Tab>
</Tabs>

## Backup & Recovery

<CardGroup cols={2}>
  <Card title="Point-in-Time Recovery" icon="clock-rotate-left">
    Restore database to any point in history
  </Card>

  <Card title="Snapshot Exports" icon="download">
    Export specific runs for offline archival
  </Card>

  <Card title="Incremental Backups" icon="floppy-disk">
    Daily backups with 90-day retention
  </Card>

  <Card title="Disaster Recovery" icon="shield">
    Multi-region replication for business continuity
  </Card>
</CardGroup>

## Performance Considerations

<AccordionGroup>
  <Accordion title="Indexing Strategy" icon="magnifying-glass">
    ```sql theme={null}
    -- Fast simulation run lookups
    CREATE INDEX idx_runs_scenario ON simulation_runs(scenario_id, completed_at);

    -- Fast year-based queries
    CREATE INDEX idx_trust_year ON annual_trust_states(simulation_run_id, year);

    -- Fast participant lookups
    CREATE INDEX idx_participant ON annual_participant_snapshots(participant_id, year);
    ```
  </Accordion>

  <Accordion title="Partitioning" icon="table">
    Large tables partitioned by simulation\_run\_id for faster queries and easier archival.
  </Accordion>

  <Accordion title="Materialized Views" icon="eye">
    Pre-computed aggregations for common dashboard queries (e.g., total repurchase obligations by year).
  </Accordion>
</AccordionGroup>

## Next Steps

<CardGroup cols={2}>
  <Card title="Data Models" icon="shapes" href="/models/overview">
    Explore the object models built on this database
  </Card>

  <Card title="API Reference" icon="code" href="/api-reference/introduction">
    Learn how to query and manipulate data via API
  </Card>
</CardGroup>