Simulation Database Schema

This document describes the database schema used to store simulation data. The database uses SQLite with SQLAlchemy ORM and consists of multiple tables tracking different aspects of the simulation.

Database Overview

Hierarchy: Experiment → Simulation → Agent

The data model is structured hierarchically to support complex research workflows:

• Experiment: The top-level container that groups multiple related simulations. Represented by ExperimentModel in the database.
  • Contains metadata like name, description, hypothesis, status, variables, and results summary.
  • Relationships: One-to-many with Simulations.
• Simulation: A single run of the simulation environment. Represented by Simulation model.
  • Tagged with a unique simulation_id.
  • Contains configuration parameters, start/end times, and summary metrics.
  • Relationships: Many-to-one with Experiment; One-to-many with Agents, Actions, States, etc.
• Agent: Individual entities within a simulation. Represented by AgentModel.
  • Attributes: agent_id, birth/death time, type, position, resources, health, genome, generation.
  • Relationships: One-to-many with States, Actions, Health Incidents, Learning Experiences.

This hierarchy allows storing multiple simulations (with their agents and data) in a single database file via ExperimentDatabase, using simulation_id to differentiate data.

Multi-Simulation Support

All tables include a simulation_id column to support storing multiple simulations in a single database:

• simulation_id: String(64) foreign key to simulations.simulation_id
• Enables filtering and querying across multiple simulations
• Supports experiment-level analysis and comparison

Data Flow: From Logging to Analysis

Data moves through the system in a structured flow to ensure efficiency and reliability:

1. Logging During Simulation:
   • Simulations use DataLogger (or ExperimentDataLogger for multi-simulation) to record data.
   • Data is buffered in memory (e.g., action_buffer, learning_exp_buffer) to reduce database writes.
   • Buffers flush based on size (default 1000) or time interval (default 30s).
   • Transactions ensure atomicity; bulk inserts are used for performance.
2. Storage:
   • Core storage in SQLite via SimulationDatabase or ExperimentDatabase.
   • For multi-simulation: Data tagged with simulation_id.
   • Optional in-memory mode for faster operations, with persistence options.
3. Retrieval and Analysis:
   • Repositories (e.g., AgentRepository) provide query interfaces.
   • Services (e.g., ActionsService) add business logic.
   • Analyzers (e.g., in farm/database/analyzers/) process data into metrics like dominance, advantage.
   • DataRetriever handles population statistics and advanced queries.

Performance Optimizations:

• Buffered logging to minimize I/O.
• Indexes on key columns (e.g., agent_type, birth_time).
• Bulk operations and transaction management.
• In-memory database support for high-throughput scenarios.
• Query optimization in repositories.

ExperimentDatabase Support

ExperimentDatabase extends SimulationDatabase to support storing multiple simulations in a single SQLite file.

• Multi-Simulation Storage:
  • Each simulation is assigned a unique simulation_id.
  • All data tables include simulation_id column for filtering.
  • Consistent schema across simulations.
• Context Management:
  • Use create_simulation_context(simulation_id) to get a SimulationContext object.
  • This context ensures all logging operations tag data with the correct simulation_id.
  • Acts as a simulation-specific view into the shared database.
• Experiment vs. Simulation Data:
  • Experiment-level: Metadata in experiments table.
  • Simulation-level: Per-simulation records in simulations table, plus tagged data in other tables.
  • Query across simulations by filtering on simulation_id.

Architecture Diagrams

Data Flow Diagram

graph TD
    A[Simulation] -->|log data| B[DataLogger]
    B -->|buffered writes| C[ExperimentDatabase]
    C -->|queries| D[Repositories/Services]
    D -->|processed data| E[Analyzers]
    E -->|metrics| F[Results/Visualizations]

Repository Relationships

erDiagram
    Experiment ||--o{ Simulation : contains
    Simulation ||--o{ Agent : has
    Agent ||--o{ AgentState : has
    Agent ||--o{ Action : performs
    Agent ||--o{ HealthIncident : experiences
    Agent ||--o{ ReproductionEvent : attempts

Usage Patterns

Running Experiments with Multiple Simulations

from farm.database.experiment_database import ExperimentDatabase
from farm.runners.experiment_runner import ExperimentRunner

db = ExperimentDatabase('experiment.db', 'exp_001')
context1 = db.create_simulation_context('sim_001')
# Run simulation 1 using context1.logger

context2 = db.create_simulation_context('sim_002')
# Run simulation 2 using context2.logger

Querying Cross-Simulation Data

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from farm.database.models import AgentModel

engine = create_engine('sqlite:///experiment.db')
Session = sessionmaker(bind=engine)
with Session() as session:
    agents = session.query(AgentModel).filter(
        AgentModel.simulation_id.in_(['sim_001', 'sim_002']),
        AgentModel.agent_type == 'control'
    ).all()

Tables Overview

Experiments Table

Stores experiment-level metadata and groups related simulations.

Simulations Table

Stores metadata about simulation runs.

Agents Table

Stores metadata and lifecycle information for each agent.

AgentStates Table

Tracks the state of each agent at each simulation step.

ResourceStates Table

Tracks the state of resources at each simulation step.

SimulationSteps Table

Stores aggregate metrics for each simulation step.

Note: Combat encounters, successful attacks, and resources shared metrics are no longer stored in simulation_steps. These can be derived from the agent_actions table:

• Combat encounters: Count of actions where action_type = 'attack'
• Successful attacks: Count of attack actions where reward > 0
• Resources shared: Sum of resources from actions where action_type = 'share' (extract from details JSON field)

AgentActions Table

Records actions taken by agents during simulation.

Note: For actions generated by learning modules, module_type and module_id identify the learning module. The details JSON field contains action_taken (integer) and action_taken_mapped (string) when available.

HealthIncidents Table

Tracks changes in agent health status.

SimulationConfig Table

Stores simulation configuration data.

ReproductionEvents Table

Records reproduction attempts and outcomes in the simulation.

Note: The social_interactions table has been removed. Social interactions can be derived from the agent_actions table by filtering for actions that have a target agent (where action_target_id is not null). For example:

• Cooperation interactions: actions where action_type = 'share' and action_target_id is not null
• Competition interactions: actions where action_type = 'attack' and action_target_id is not null

Relationships

• Simulations.experiment_id → Experiments.experiment_id: Links simulations to their experiment
• AgentStates.agent_id → Agents.agent_id: Links agent states to their agent records
• AgentActions.agent_id → Agents.agent_id: Links actions to agents
• AgentActions.action_target_id → Agents.agent_id: Links actions to target agents
• AgentActions.state_before_id → AgentStates.id: Links actions to prior state
• AgentActions.state_after_id → AgentStates.id: Links actions to resulting state
• HealthIncidents.agent_id → Agents.agent_id: Links health incidents to agents
• LearningExperiences.agent_id → Agents.agent_id: Links learning experiences to agents
• ReproductionEvents.parent_id → Agents.agent_id: Links reproduction events to parent agents
• ReproductionEvents.offspring_id → Agents.agent_id: Links reproduction events to offspring agents

Indexes

The schema includes optimized indexes for common queries:

• Agents: agent_type, birth_time, death_time, simulation_id
• AgentStates: agent_id, step_number, simulation_id
• ResourceStates: step_number, resource_id, simulation_id
• AgentActions: step_number, agent_id, action_type, simulation_id
• LearningExperiences: step_number, agent_id, module_type, simulation_id
• HealthIncidents: step_number, agent_id, simulation_id
• ReproductionEvents: step_number, parent_id, success, simulation_id

Notes

• Uses SQLite as the backend database
• Implements foreign key constraints
• Includes indexes for performance optimization
• Supports concurrent access through session management
• Uses transaction safety with automatic rollback
• All tables include simulation_id for multi-simulation support
• Primary keys use appropriate data types (String for agent_id, Integer for auto-incrementing IDs)