Database

Series links: Part 1/5 - Introduction Part 2/5 - Database Planning (you are here) Part 3/5 - AWS Setup Part 4/5 - Backend APIs Part 5/5 - Frontend What A minimal PostgreSQL structure with just two tables: one for the transaction entry and one for the attached documents (bills/receipts). Why Keep it simple so you can start quickly. Easy to query, filter by date/text, and export. How Tables at a glance accounting_entry: one row per transaction (expense or income). entry_date: when it happened. amount: the value; CR/DR tells direction. cr_dr: ‘CR’ for money in, ‘DR’ for money out. category: a simple label you choose (e.g., Groceries, Fuel). description: short note to help search later. human_code: short human-friendly code you can cite (shown on the listing). id: internal unique id. accounting_document: zero or more documents for each entry (your bills/receipts). accounting_entry_id: links the document to its entry. file_path: where the file is stored (S3 key or path). doc_code: short code for the document (helps reference a specific file). id: internal unique id. ...

What Active-Active means two or more PostgreSQL databases can accept writes and stay in sync in near real-time. Unlike standard streaming replication (primary → replicas), this setup allows bi-directional writes. We’ll use AWS DMS to achieve this: Full Load: copy existing schema + data Change Data Capture (CDC): replicate ongoing changes from WAL logs Databases can be RDS, EC2 PostgreSQL, or on-premises. Latency is usually seconds. Why Multi-region, hybrid infrastructure, disaster recovery True bi-directional sync is complex; DMS simplifies it Avoid downtime and manual syncing Most failures happen during setup, not concept How 1. Decide DMS Deployment Provisioned: recommended for CDC. Runs 24/7, predictable performance. Serverless: flexible scaling, but dynamic cost and unsuitable for constant CDC. Rule: Always use Provisioned for bi-directional replication. ...