Description
The project has inconsistent database architecture decisions between SQLite and Postgres.
Evidence
- Backend:
backend/src/database/connection.js opens SQLite with WAL mode
- Knex config:
backend/knexfile.js configures SQLite, expects migrations in src/database/migrations/
- Migrations:
backend/migrations/ directory contains Postgres SQL files (V001__create_users_table.up.sql, V002__add_rate_limiting.up.sql, etc.)
- Indexer:
indexer/migrations/postgres/ has Postgres-specific migration SQL
- Root Rust source:
src/postgres.rs and src/sqlite.rs suggest dual-write support
Impact
- Unclear which database system is the "source of truth"
- Contributors might add SQLite-specific or Postgres-specific code that breaks the other
- The
schema.sql file has extensive DDL that is never run through knex migrations
- Postgres migration files exist but knex is configured for SQLite
Required Fix
- Decide on a primary database (recommended: keep SQLite for simplicity, add Postgres as optional)
- Document the database strategy in CONTRIBUTING.md
- Clean up unused migration files
- Ensure knex migrations match the actual schema in
schema.sql
Reference
Identified during full codebase audit of soroban-playground.
Description
The project has inconsistent database architecture decisions between SQLite and Postgres.
Evidence
backend/src/database/connection.jsopens SQLite with WAL modebackend/knexfile.jsconfigures SQLite, expects migrations insrc/database/migrations/backend/migrations/directory contains Postgres SQL files (V001__create_users_table.up.sql,V002__add_rate_limiting.up.sql, etc.)indexer/migrations/postgres/has Postgres-specific migration SQLsrc/postgres.rsandsrc/sqlite.rssuggest dual-write supportImpact
schema.sqlfile has extensive DDL that is never run through knex migrationsRequired Fix
schema.sqlReference
Identified during full codebase audit of soroban-playground.