Overview
Replace all in-memory Maps with a production-grade PostgreSQL persistence layer. This is the single largest backend task and a hard prerequisite for every other feature.
Scope (~2500 lines)
Schema & Migrations (backend/src/db/)
connection.js — pg Pool setup, graceful shutdown, health probemigrations/001_create_assets.sql — assets table with JSONB tags, GIN index for full-textmigrations/002_create_agents.sql — agents table, capabilities as text[]migrations/003_create_licenses.sql — licenses table with FK to assetsmigrations/004_create_streams.sql — streams tablemigrations/005_create_reports.sql — moderation reports tablemigrations/006_create_events_log.sql — raw on-chain event audit logmigrate.js — migration runner that reads SQL files in order and tracks applied migrations in a schema_migrations table
Repository Layer (backend/src/repositories/)
assetRepository.js — findById, findAll(filters, pagination), create, update, softDelete, search(query) using to_tsvector / plainto_tsqueryagentRepository.js — findById, findAll(filters), create, updateReputation, deactivatelicenseRepository.js — create, findByBuyerAndAsset, findAllByBuyer, updateCallsRemaining, expirestreamRepository.js — create, findById, findBySender, findByRecipient, updateStatus, recordWithdrawaleventLogRepository.js — append, findSince(ledger), findByContractAndTopic
Service Migration
- Migrate
assetService.js, agentService.js, streamService.js to call repositories instead of Maps
- Keep the same public interface so routes require zero changes
- Add transaction wrappers for multi-table writes (e.g. purchase_license updates asset.usage_count AND creates a license row atomically)
Connection Pooling
- Configure
pg.Pool with max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000
- Add pool metrics endpoint at
GET /api/v1/internal/db-stats (admin-only)
Tests
- Spin up a real PostgreSQL instance in CI using
@testcontainers/postgresql
- Repository-level integration tests for every method with real SQL
- Test transaction rollback on failure
- Test migration idempotency (running twice does not fail)
Acceptance Criteria
- All data survives process restart
npm test passes with a live PG container- Migration runner is idempotent
- No raw SQL strings outside the repository layer
- Pool never leaks connections under test load
Overview
Replace all in-memory Maps with a production-grade PostgreSQL persistence layer. This is the single largest backend task and a hard prerequisite for every other feature.
Scope (~2500 lines)
Schema & Migrations (
backend/src/db/)connection.js— pg Pool setup, graceful shutdown, health probemigrations/001_create_assets.sql— assets table with JSONB tags, GIN index for full-textmigrations/002_create_agents.sql— agents table, capabilities as text[]migrations/003_create_licenses.sql— licenses table with FK to assetsmigrations/004_create_streams.sql— streams tablemigrations/005_create_reports.sql— moderation reports tablemigrations/006_create_events_log.sql— raw on-chain event audit logmigrate.js— migration runner that reads SQL files in order and tracks applied migrations in aschema_migrationstableRepository Layer (
backend/src/repositories/)assetRepository.js—findById,findAll(filters, pagination),create,update,softDelete,search(query)usingto_tsvector/plainto_tsqueryagentRepository.js—findById,findAll(filters),create,updateReputation,deactivatelicenseRepository.js—create,findByBuyerAndAsset,findAllByBuyer,updateCallsRemaining,expirestreamRepository.js—create,findById,findBySender,findByRecipient,updateStatus,recordWithdrawaleventLogRepository.js—append,findSince(ledger),findByContractAndTopicService Migration
assetService.js,agentService.js,streamService.jsto call repositories instead of MapsConnection Pooling
pg.Poolwithmax: 20,idleTimeoutMillis: 30000,connectionTimeoutMillis: 2000GET /api/v1/internal/db-stats(admin-only)Tests
@testcontainers/postgresqlAcceptance Criteria
npm testpasses with a live PG container