claude-opus-5 × research

{
  "executive_recommendation": "Use PostgreSQL as the single primary database, with JSONB columns for the genuinely schema-fluid parts (Discord bot state, webhook payloads, feature flags). Add a second store only when a measured need appears — most likely Redis for ephemeral bot/session state and rate limiting, and later a dedicated log/analytics sink if audit volume outgrows the OLTP box. Rationale (general engineering principle, not a vendor claim): your riskiest workload is money-adjacent auction transactions, which need multi-row atomicity, uniqueness/foreign-key enforcement, and explicit row-level locking; Postgres gives those by default while still handling document-shaped data adequately. A bootstrapped team also benefits from one system to back up, monitor, patch, and reason about. Note: this answer is from general knowledge of both systems, not from live sources or benchmarks; no statistics are cited because I cannot verify them here.",
  "comparison": {
    "postgresql": [
      "FACT: ACID transactions spanning many rows/tables are the default execution model; isolation levels up to SERIALIZABLE are available, and SELECT ... FOR UPDATE / advisory locks give explicit pessimistic concurrency control.",
      "FACT: Declarative integrity — foreign keys, UNIQUE, CHECK, NOT NULL, generated columns — enforced by the engine rather than by application code.",
      "FACT: Full SQL: joins, window functions, CTEs, aggregation. Good fit for billing reconciliation, cohort/revenue reporting, and audit queries that cross entities.",
      "FACT: JSONB columns store semi-structured documents and can be indexed (GIN, expression indexes), so 'we need flexible fields' does not by itself require a document database.",
      "FACT: Operational primitives: streaming + logical replication, WAL-based point-in-time recovery, declarative table partitioning (useful for time-series audit logs), LISTEN/NOTIFY for lightweight pub/sub.",
      "TRADE-OFF: Horizontal write scale-out is not built in. Beyond one primary you use read replicas, app-level sharding, or an extension (e.g. Citus) — real work, and usually a later problem.",
      "TRADE-OFF: Connection-per-backend model means high-concurrency or serverless clients typically need a pooler (PgBouncer or a managed equivalent).",
      "TRADE-OFF: Schema changes are explicit migrations. Most are fast with modern Postgres, but some (certain rewrites, index builds without CONCURRENTLY) can lock or take time if done carelessly."
    ],
    "mongodb": [
      "FACT: Document model stores nested/heterogeneous objects natively; a single document update is atomic, which fits 'the whole aggregate lives in one record' designs.",
      "FACT: Multi-document ACID transactions exist (added for replica sets in 4.0 and for sharded clusters in 4.2), so transactions are possible — but they are an opt-in construct rather than the ambient default, and are best kept short.",
      "FACT: Sharding and replica-set failover are first-class, built-in features; horizontal scaling is a configuration/design exercise rather than an add-on.",
      "FACT: Useful built-ins for this domain: TTL indexes (auto-expiring ephemeral state), change streams (event-driven reactions), time-series collections, aggregation pipeline.",
      "FACT: Schema validation is optional (JSON Schema validators); by default the database will accept differently-shaped documents.",
      "TRADE-OFF: No foreign keys or cross-collection constraints. Referential integrity and most invariants become application responsibilities and are easy to violate under concurrency or partial failure.",
      "TRADE-OFF: Cross-entity querying via $lookup is available but generally less ergonomic and less optimizable than SQL joins; heavy relational reporting tends to get pushed into application code or a separate analytics store.",
      "TRADE-OFF: Flexible schemas defer rather than remove migration cost — old document shapes persist and must be handled by readers or backfilled."
    ]
  },
  "workload_analysis": {
    "user_accounts": "Small, highly relational, invariant-heavy (unique email/handle, one identity linked to many OAuth/Discord identities, cascading deletes for GDPR-style erasure). Both databases can do it; Postgres enforces the uniqueness and referential rules in the engine, which matters because auth bugs are security bugs. Context-dependent: if you outsource auth to an external identity provider, this table becomes a thin profile record and the choice barely matters.",
    "subscriptions": "Money and state mach