Database Write/Read Separation

[salmad3]

Current State:

api-main is the only service with a direct Postgres connection today. The reader streams blocks and writes chain-derived state by calling the secured reader endpoints in api-main, which then touch the same tables that user-facing API code uses. Feed sync and future services either consume those API endpoints or would need read-only access. We need a clear separation before the service surface grows.

Problem:

• No enforced boundaries between reader-owned data and API-owned data
• Feed sync and future services hit the writer database directly
• Risk of data corruption if non-reader services accidentally mutate chain state
• Load and lock contention as more services read from the primary

Proposed Solution

Phase 1: Role separation

• Create reader_writer role for chain-derived tables
• Create chain_readonly role for downstream consumers
• Move chain tables to dedicated schema with strict permissions

Phase 2: Read replicas

• Stand up read replica for feed sync and analytics
• Route read-only queries away from primary
• Add connection pooling per service type

Phase 3: Domain separation

• Consider splitting into chain DB vs API DB
• Evaluate event streaming for cross-domain data flow
• Design for independent scaling and failure isolation