SQLite Schema Design and Migration System #128

New Issue

2025-12-17T13:58:56Z

siennathesane commented

2025-12-17 13:58:56 +00:00

(Migrated from github.com)

Description

In order to validate persistence capabilities in v0,
as a Marathon developer,
I need to design and implement the SQLite schema for storing entity data and a schema versioning/migration system.

The database will store all entity component data using rkyv binary serialization for high performance and low memory overhead. Assets are NOT stored in the database.

Acceptance Criteria

Scenario: Entity data persists to SQLite
  Given entities exist in the game world
  When the game saves state
  Then all entity component data is stored in SQLite as rkyv binary blobs

Scenario: State rehydrates from SQLite
  Given saved entity data exists in SQLite
  When the game loads
  Then all entities are recreated with correct component data via zero-copy deserialization

Scenario: Schema migrations work
  Given an older schema version exists
  When a schema migration is applied
  Then the database upgrades without data loss

Scenario: Entity queries work on metadata
  Given entities exist in the database
  When querying by network_id or entity_type
  Then entities can be retrieved efficiently via indexed metadata

Technical Approach

Database Schema

CREATE TABLE entities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entity_type TEXT NOT NULL,
  network_id BLOB NOT NULL,        -- UUID as 16 bytes
  created_at INTEGER NOT NULL,     -- Unix timestamp
  updated_at INTEGER NOT NULL,     -- Unix timestamp
  version INTEGER NOT NULL,        -- Serialization format version
  component_data BLOB NOT NULL,    -- rkyv serialized component bundle
  INDEX idx_network_id (network_id),
  INDEX idx_entity_type (entity_type)
);

CREATE TABLE schema_version (
  version INTEGER PRIMARY KEY,
  applied_at INTEGER NOT NULL
);

Serialization Strategy

Use rkyv for component data serialization:
- Zero-copy deserialization (critical for iPad memory constraints)
- Much faster than JSON parsing
- More compact than JSON
- No intermediate allocations
Replace bincode with rkyv throughout serialization layer
Metadata fields (id, entity_type, network_id) remain queryable via SQL indexes
Component data stored as opaque binary blob

Why Binary Blobs Over Normalized Tables

Game engines load entities into ECS anyway - no need for SQL-level component queries
Critical queries (by network_id, by entity_type) are on indexed metadata fields
Zero-copy deserialization is huge win for performance and memory
Simpler schema evolution (version field + migration logic)

Schema Evolution

version field tracks serialization format version
Migration logic deserializes old version, transforms in memory, serializes as new version
Can leverage rkyv's versioning features where applicable

Files Affected

New: crates/libmarathon/src/persistence/schema.rs
New: crates/libmarathon/src/persistence/migrations.rs
Modify: Serialization layer to use rkyv instead of bincode

Implementation Tasks

Add rkyv dependency and configure for entity components
Design entity table schema (as above)
Implement schema versioning system
Create migration framework
Replace bincode with rkyv in serialization layer
Implement save/load operations with rkyv serialization
Add indexes for network_id and entity_type queries
Test migration path with version bumps

Future Enhancements (Out of Scope for v0)

Multi-world support: One database per world (worlds/world_<uuid>.db)
Debug devtool: CLI tool to inspect/dump entity data in human-readable format (see related issue)
Per-component tables: Option to normalize specific high-query components into dedicated tables

Additional Context

Part of v0 Core Tech Validation. Required for persistence validation demo to function. This design prioritizes performance and memory efficiency over SQL queryability, which is the right trade-off for a game engine where entities are loaded into ECS.

## Description In order to validate persistence capabilities in v0, as a Marathon developer, I need to design and implement the SQLite schema for storing entity data and a schema versioning/migration system. The database will store all entity component data using rkyv binary serialization for high performance and low memory overhead. Assets are NOT stored in the database. ## Acceptance Criteria ```gherkin Scenario: Entity data persists to SQLite Given entities exist in the game world When the game saves state Then all entity component data is stored in SQLite as rkyv binary blobs Scenario: State rehydrates from SQLite Given saved entity data exists in SQLite When the game loads Then all entities are recreated with correct component data via zero-copy deserialization Scenario: Schema migrations work Given an older schema version exists When a schema migration is applied Then the database upgrades without data loss Scenario: Entity queries work on metadata Given entities exist in the database When querying by network_id or entity_type Then entities can be retrieved efficiently via indexed metadata ``` ## Technical Approach ### Database Schema ```sql CREATE TABLE entities ( id INTEGER PRIMARY KEY AUTOINCREMENT, entity_type TEXT NOT NULL, network_id BLOB NOT NULL, -- UUID as 16 bytes created_at INTEGER NOT NULL, -- Unix timestamp updated_at INTEGER NOT NULL, -- Unix timestamp version INTEGER NOT NULL, -- Serialization format version component_data BLOB NOT NULL, -- rkyv serialized component bundle INDEX idx_network_id (network_id), INDEX idx_entity_type (entity_type) ); CREATE TABLE schema_version ( version INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL ); ``` ### Serialization Strategy - **Use rkyv** for component data serialization: - Zero-copy deserialization (critical for iPad memory constraints) - Much faster than JSON parsing - More compact than JSON - No intermediate allocations - **Replace bincode** with rkyv throughout serialization layer - **Metadata fields** (id, entity_type, network_id) remain queryable via SQL indexes - **Component data** stored as opaque binary blob ### Why Binary Blobs Over Normalized Tables - Game engines load entities into ECS anyway - no need for SQL-level component queries - Critical queries (by network_id, by entity_type) are on indexed metadata fields - Zero-copy deserialization is huge win for performance and memory - Simpler schema evolution (version field + migration logic) ### Schema Evolution - `version` field tracks serialization format version - Migration logic deserializes old version, transforms in memory, serializes as new version - Can leverage rkyv's versioning features where applicable ### Files Affected - New: `crates/libmarathon/src/persistence/schema.rs` - New: `crates/libmarathon/src/persistence/migrations.rs` - Modify: Serialization layer to use rkyv instead of bincode ## Implementation Tasks 1. Add rkyv dependency and configure for entity components 2. Design entity table schema (as above) 3. Implement schema versioning system 4. Create migration framework 5. Replace bincode with rkyv in serialization layer 6. Implement save/load operations with rkyv serialization 7. Add indexes for network_id and entity_type queries 8. Test migration path with version bumps ## Future Enhancements (Out of Scope for v0) - **Multi-world support**: One database per world (`worlds/world_<uuid>.db`) - **Debug devtool**: CLI tool to inspect/dump entity data in human-readable format (see related issue) - **Per-component tables**: Option to normalize specific high-query components into dedicated tables ## Additional Context Part of v0 Core Tech Validation. Required for persistence validation demo to function. This design prioritizes performance and memory efficiency over SQL queryability, which is the right trade-off for a game engine where entities are loaded into ECS.

siennathesane commented

2025-12-17 20:14:22 +00:00

(Migrated from github.com)

Removed bincode, serde, and reflection requirements in 99e31b1 and b421aaf.

Removed `bincode`, serde, and reflection requirements in [99e31b1](https://github.com/r3t-studios/marathon/commit/99e31b1157b8e9296d889aaeb10d762b40c7e00c) and [b421aaf](https://github.com/r3t-studios/marathon/commit/b421aaf037287fb8aebeea818c265613f2102342).

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: studio/marathon#128