Postgresql

Part of the MediaBridge series. Why Cache S3 at All S3 ListObjectsV2 is not free. Each call costs money, takes time, and returns at most 1,000 objects per page. A bucket with 10,000 files in a single prefix requires 10 paginated S3 calls just to render one folder. Do that on every page load and you burn money, slow the UI, and hit S3 rate limits under concurrent users. Presigned URL generation is also not free. It is CPU work on the server. A folder with 50 files requires 50 presigned PUT or GET URL generations per load if nothing is cached. ...

Part of the PurelyManage series. The Naive Approach Breaks at Scale The first version of the Domains page rechecked every domain on every page load. The logic was simple: call updateDomainSettings with recheckDns: true for each domain, then call listDomains to get the fresh DNS status. That is N+2 API calls per request where N is the number of domains. With the dashboard set to auto-refresh every 60 seconds: 50 domains × 52 API calls × 60 requests/hour = ~3,000 API calls/hour This is both wasteful and fragile. Most domains are healthy most of the time. Rechecking 50 domains every minute to confirm they are still green is unnecessary. And if PurelyMail ever introduces rate limits, this approach is the first to break. ...

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

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