claude-opus-5 × research

{
  "executive_recommendation": "Use PostgreSQL as the single primary database, and add specialized stores only when a measured need appears (e.g. Redis for hot/ephemeral Discord bot state and rate limiting; object storage or a log pipeline for long-term audit archives). FACTS/PRINCIPLES: PostgreSQL provides multi-row/multi-table ACID transactions, foreign keys, CHECK/UNIQUE constraints, MVCC, row-level locking (SELECT ... FOR UPDATE), SERIALIZABLE isolation, declarative partitioning, and a native jsonb type with GIN indexing — so schemaless documents and relational integrity can coexist in one engine. MongoDB provides atomic single-document updates, flexible per-document schemas, an aggregation framework, change streams, TTL indexes, built-in sharding, and (since v4.0 on replica sets, v4.2 on sharded clusters) multi-document ACID transactions, though multi-document transactions are generally treated as an exception path rather than the normal design pattern. CONTEXT-DEPENDENT JUDGMENT: your workload mix is dominated by money, entitlements, and contention (subscriptions + auctions), which are exactly the cases where constraints and cross-row transactions reduce bug classes; and a bootstrapped team benefits most from one engine to operate, back up, and reason about. The parts of your workload that suit documents (bot state, event payloads) are well served by jsonb, whereas the parts that suit relations are painful to retrofit into documents. If your team already has deep MongoDB operational experience and none with SQL, that reverses part of the calculus — see risks.",
  "comparison": {
    "postgresql": [
      "FACT: Full ACID transactions across many rows/tables, with SERIALIZABLE available; enforced foreign keys, unique, and check constraints prevent invalid states at the storage layer rather than only in application code.",
      "FACT: Rich declarative querying — joins, window functions, CTEs, aggregates — plus partial, expression, composite, GIN and BRIN indexes; jsonb allows semi-structured columns with indexing, so 'flexible schema' does not require a different database.",
      "FACT: Concurrency primitives useful for auctions: row locks, advisory locks, exclusion/unique constraints, and SKIP LOCKED for queue-style consumption.",
      "FACT: Native declarative partitioning suits time-series/append-only tables such as audit logs; logical and streaming replication support read replicas and low-downtime upgrades.",
      "OPERATIONS TRADE-OFF: scaling is primarily vertical plus read replicas; horizontal write sharding is not built in and typically requires extensions or application-level sharding. Connection handling is process-based, so high-concurrency or serverless deployments usually need a pooler (e.g. PgBouncer). Schema migrations are explicit and must be planned to avoid long locks.",
      "OPERATIONS BENEFIT: widely available as a low-cost managed service; large ecosystem of ORMs, migration tools, and backup/PITR tooling; SQL knowledge is broadly transferable when hiring."
    ],
    "mongodb": [
      "FACT: Documents map naturally to nested, heterogeneous, or rapidly changing objects; fields can differ per document, and optional JSON Schema validation can be added later.",
      "FACT: Single-document updates are atomic, which cleanly handles patterns like 'increment current bid only if my price beats it' via a conditional findAndModify on one auction document.",
      "FACT: Built-in replica sets and sharding give a well-trodden horizontal write-scaling path; tunable read/write concerns let you choose durability vs latency per operation (defaults have become safer over time, e.g. majority write concern as the modern default).",
      "FACT: Change streams provide a first-class, resumable event feed — convenient for pushing real-time auction updates to clients or a Discord bot; TTL indexes make expiring ephemeral state trivial.",
      "TRADE-OFF: no enforced cross-collection referential integrity; correctness of relational invariants (a subscription must point to a real user, a payment must not be double-applied) moves into application code and is easier to get wrong under concurrency.",
      "TRADE-OFF: multi-document transactions exist but add latency, conflict-retry handling, and operational limits; denormalization speeds reads but creates update fan-out and drift risk, and ad-hoc analytical questions across collections are harder than SQL joins."
    ]
  },
  "workload_analysis