Postgresql

Part of the PurelyManage series. The Problem Any admin panel that manages real data has a deletion problem. The moment you give someone a delete button, you are one misclick away from losing something that took time to set up. In a single-user tool this is manageable: you know who deleted it because it was you. In a multi-admin panel it is more complicated. PurelyManage can have multiple sysadmin accounts. Any of them can delete email users, domains, and routing rules. The operations go directly to PurelyMail via their API, meaning the moment the request is made, the resource is gone. There is no recycle bin, no undo, no recovery path. ...

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. ...