Database Selection — The $200k MongoDB Transaction Failure

# DATABASE CATEGORY DECISION TREE
# Use this mental model before writing a single line of schema

function selectDatabaseCategory(requirements):

    # STEP 1: Is the data primarily time-stamped metrics or events?
    if requirements.isTimeSeriesData:
        # e.g. CPU usage every 5s, stock prices, IoT sensor readings
        return "Time-Series DB (InfluxDB, TimescaleDB)"
        # WHY: These DBs compress sequential numeric data up to 90%
        # and run time-window aggregations (avg over last 1hr) natively

    # STEP 2: Are RELATIONSHIPS between entities the core query?
    if requirements.queriesAreRelationshipTraversals:
        # e.g. 'find all users connected within 3 hops who like jazz'
        return "Graph DB (Neo4j, Amazon Neptune)"
        # WHY: SQL JOINs across many-to-many relationships become
        # exponentially slower. Graph traversal stays O(depth), not O(rows)

    # STEP 3: Is sub-millisecond lookup by a single key the priority?
    if requirements.primaryAccessPattern == "KEY_LOOKUP" and
       requirements.latencyBudgetMs < 5:
        # e.g. session tokens, rate limit counters, feature flags
        return "Key-Value Store (Redis, DynamoDB)"
        # WHY: Hash-map style storage eliminates query planning entirely.
        # Redis keeps data in RAM — it's literally as fast as memory access

    # STEP 4: Does the schema change frequently or vary per record?
    if requirements.schemaIsFlexible or requirements.nestedObjectsAreCommon:
        # e.g. product catalog where 'laptop' and 'shirt' have totally
        # different attributes, or a CMS where articles have custom fields
        return "Document DB (MongoDB, Firestore, CouchDB)"
        # WHY: Embedding related data in one document avoids JOINs
        # entirely. One read = complete object. Perfect for read-heavy,
        # object-oriented access patterns

    # STEP 5: Default — do you need ACID transactions and complex queries?
    if requirements.requiresACIDTransactions or
       requirements.complexMultiTableQueries:
        # e.g. banking, e-commerce orders, healthcare records
        return "Relational DB (PostgreSQL, MySQL)"
        # WHY: Decades of optimization, rock-solid consistency guarantees,
        # powerful query planner, and JOIN performance that beats
        # application-level joins for complex analytical queries

    # If nothing is extreme, default to relational — it's the safest bet
    return "Relational DB (PostgreSQL) — until a specific pain point emerges"

-- ============================================================
-- SCENARIO: E-commerce order system
-- We'll model the same data in SQL and discuss the trade-offs
-- ============================================================

-- ── SQL APPROACH (PostgreSQL) ─────────────────────────────────
-- Three normalized tables. Every order_item links back to a product.
-- Changing a product name updates ONE row and every order reflects it.

CREATE TABLE customers (
    customer_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT UNIQUE NOT NULL,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    order_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id   UUID REFERENCES customers(customer_id),  -- foreign key enforced by DB
    total_cents   INTEGER NOT NULL,                        -- store money as cents, never float
    status        TEXT CHECK (status IN ('pending','paid','shipped','cancelled')),
    placed_at     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id      UUID REFERENCES orders(order_id) ON DELETE CASCADE,
    product_name  TEXT NOT NULL,
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_cents    INTEGER NOT NULL
);

-- Fetch a complete order with one JOIN query
-- PostgreSQL's query planner will use indexes automatically
SELECT
    o.order_id,
    c.email            AS customer_email,
    oi.product_name,
    oi.quantity,
    (oi.unit_cents * oi.quantity) / 100.0 AS line_total_dollars
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.placed_at > NOW() - INTERVAL '7 days';

-- WHY SQL WINS HERE:
-- 1. The CHECK constraint on status means invalid states are impossible
-- 2. The FOREIGN KEY means you can never have an orphaned order_item
-- 3. One JOIN query answers complex business questions ad-hoc
-- 4. Changing a product's name is one UPDATE, not finding every document

-- ── WHAT THE SAME DATA LOOKS LIKE IN A DOCUMENT DB ───────────
-- (MongoDB-style JSON document — shown here as a SQL comment for comparison)

/*
{
  "_id": "ord_7f3a9c",
  "customer": {
    "email": "alice@example.com"      // DENORMALIZED — copied at order time
  },
  "items": [
    { "product_name": "Mechanical Keyboard", "quantity": 1, "unit_cents": 12999 },
    { "product_name": "USB-C Cable",         "quantity": 2, "unit_cents": 1499 }
  ],
  "status": "paid",
  "placed_at": "2024-06-01T14:23:00Z"
}

  WHY DOCUMENT WINS HERE:
  - One read fetches the entire order. Zero JOINs. Faster for this specific query.
  - If your UI always shows 'a single order', this shape matches perfectly.
  - Schema can vary: a digital order might have 'download_url', a physical one
    might have 'shipping_address' — no ALTER TABLE needed.

  WHY DOCUMENT LOSES HERE:
  - 'Show me all orders containing Mechanical Keyboards placed in June'
    requires scanning every document unless you build a specific index.
  - The customer's email is copied into every order. If they change it,
    you can't easily update historical orders — is that right or wrong?
    In SQL, that question doesn't arise.
*/

# ============================================================
# SCENARIO: Social media platform — showing how one feature
# (loading a user's home feed) touches THREE different databases
# ============================================================

import redis          # Key-value store — for cached feed
import psycopg2       # PostgreSQL — for user auth and post metadata
# from neo4j import GraphDatabase  # Graph DB — for follow graph

# In a real app these would come from environment variables / secrets manager
REDIS_HOST      = "redis.internal"
POSTGRES_DSN    = "postgresql://app_user:secret@pg.internal/social_db"
NEO4J_URI       = "bolt://neo4j.internal:7687"

FEED_CACHE_TTL_SECONDS = 300   # Cache each user's feed for 5 minutes
FEED_MAX_POSTS         = 20    # Return at most 20 posts per feed load


def get_home_feed(requesting_user_id: str) -> list[dict]:
    """
    Load the home feed for a user.

    Access pattern breakdown:
    1. Redis (Key-Value)   — Check if we already computed this feed recently.
                            If yes, return it instantly. (~0.1ms)
    2. Neo4j (Graph)       — Find which users this person follows.
                            Traversal is native, not a JOIN. (~5ms)
    3. PostgreSQL (SQL)    — Fetch the actual post content + metadata
                            for posts from those followed users. (~20ms)
    4. Redis again         — Cache the assembled feed for next time.
    """

    # ── STEP 1: Check the feed cache first ─────────────────────
    cache_client = redis.Redis(host=REDIS_HOST, decode_responses=True)
    cache_key    = f"feed:v2:{requesting_user_id}"   # versioned key lets us bust cache on schema change

    cached_feed = cache_client.get(cache_key)
    if cached_feed:
        print(f"[Cache HIT]  Feed for {requesting_user_id} served from Redis")
        return eval(cached_feed)   # In production: use json.loads()

    print(f"[Cache MISS] Building feed for {requesting_user_id} from scratch")

    # ── STEP 2: Get followed user IDs from the graph database ─
    # In Neo4j, this is a single traversal — no JOIN across a 500M row follows table
    # Cypher query would look like:
    #   MATCH (me:User {id: $userId})-[:FOLLOWS]->(friend:User)
    #   RETURN friend.id LIMIT 500
    #
    # For this example, we simulate the result:
    followed_user_ids = _get_followed_users_from_graph(requesting_user_id)
    print(f"[Graph DB]   {requesting_user_id} follows {len(followed_user_ids)} users")

    if not followed_user_ids:
        return []   # New user with no follows — return empty feed

    # ── STEP 3: Fetch post content from PostgreSQL ────────────
    # SQL is great here: we need sorting, filtering by time, and joining
    # post content with author display names — exactly what SQL is built for
    pg_connection = psycopg2.connect(POSTGRES_DSN)
    cursor        = pg_connection.cursor()

    # %s placeholders are psycopg2's safe parameterization — never use f-strings for SQL
    placeholders = ','.join(['%s'] * len(followed_user_ids))
    cursor.execute(f"""
        SELECT
            posts.post_id,
            posts.author_id,
            users.display_name,
            posts.content_text,
            posts.created_at,
            posts.like_count
        FROM posts
        JOIN users ON users.user_id = posts.author_id
        WHERE posts.author_id IN ({placeholders})
          AND posts.created_at > NOW() - INTERVAL '48 hours'   -- recency window
        ORDER BY posts.created_at DESC
        LIMIT %s
    """, (*followed_user_ids, FEED_MAX_POSTS))

    raw_rows = cursor.fetchall()
    cursor.close()
    pg_connection.close()

    # Convert raw tuples to readable dicts
    feed_posts = [
        {
            "post_id":      row[0],
            "author_id":    row[1],
            "author_name":  row[2],
            "text":         row[3],
            "posted_at":    str(row[4]),
            "likes":        row[5]
        }
        for row in raw_rows
    ]
    print(f"[PostgreSQL] Fetched {len(feed_posts)} posts from followed users")

    # ── STEP 4: Cache the assembled feed in Redis ─────────────
    # Next request for this user returns instantly without touching PG or Neo4j
    cache_client.setex(cache_key, FEE_CACHE_TTL_SECONDS, str(feed_posts))
    print(f"[Cache SET]  Feed cached for {FEE_CACHE_TTL_SECONDS}s")

    return feed_posts


def _get_followed_users_from_graph(user_id: str) -> list[str]:
    """Simulates a Neo4j graph traversal result for this example."""
    # In production this would run a Cypher query via the neo4j-driver
    simulated_follow_graph = {
        "user_alice": ["user_bob", "user_carol", "user_dave"],
        "user_bob":   ["user_alice", "user_eve"],
    }
    return simulated_follow_graph.get(user_id, [])


# ── DEMO RUN ─────────────────────────────────────
if __name__ == "__main__":
    # First call — cache miss, full pipeline
    feed = get_home_feed("user_alice")
    print(f"\nFeed contains {len(feed)} posts\n")

    # Second call — cache hit, instant response
    feed_cached = get_home_feed("user_alice")

# ============================================================
# CAP THEOREM — Practical database mapping
# This is a system design reference, not runnable code.
# Use this when justifying database choices in design interviews
# or architecture review meetings.
# ============================================================

cap_theorem_database_guide:

  # ── CP: Consistency + Partition Tolerance ────────────────────
  # Behavior during network partition: REJECT requests rather than serve stale data
  # Real-world use: Any domain where stale reads cause real harm
  cp_databases:
    - name: PostgreSQL (with synchronous replication)
      use_when:
        - Financial transactions — double-spend is catastrophic
        - Inventory management — overselling is a business problem
        - Healthcare records — an outdated allergy list could hurt someone
      trade_off: >
        During a network split, your app will get errors.
        Your ops team needs alerting. Users see error pages.
        That is the CORRECT behavior when data integrity > availability.

    - name: HBase
      use_when:
        - Large-scale consistent writes (Hadoop ecosystem)
        - Time-series that cannot tolerate duplicate or out-of-order writes
      trade_off: Operationally complex. Only worth it at massive write scale.

  # ── AP: Availability + Partition Tolerance ────────────────────
  # Behavior during network partition: SERVE potentially stale data
  # Real-world use: Any domain where uptime > perfect accuracy
  ap_databases:
    - name: Cassandra
      use_when:
        - Shopping carts — stale cart is fine, empty cart is not
        - User activity logs — a missed click event is acceptable
        - Messaging (last-seen timestamps) — approximate is good enough
      trade_off: >
        You WILL get eventual consistency, not immediate.
        Your application code must handle 'read your own writes' edge cases.
        Cassandra makes this explicit — you choose consistency level per query.
      cassandra_consistency_levels:
        ONE:    "Fastest. Read from 1 replica. May be stale. Use for analytics."
        QUORUM: "Read from majority of replicas. Balance of speed + safety."
        ALL:    "Read from all replicas. Slowest. Use for critical reads."

    - name: DynamoDB (default mode)
      use_when:
        - Session state — slightly stale session data is usually fine
        - Product catalog reads — showing yesterday's price for a split second is ok
        - Gaming leaderboards — approximate rankings are acceptable
      strongly_consistent_read_flag: >
        DynamoDB lets you opt into consistency PER OPERATION with
        ConsistentRead=True. This doubles read cost but guarantees
        the latest data. Powerful escape hatch.

    - name: CouchDB
      use_when:
        - Offline-first mobile apps — device syncs when back online
        - Distributed edge deployments where partition is the default state

  # ── The 'CA' Myth ──────────────────────────────
  # CA (Consistent + Available, no partition tolerance) only exists
  # on a single-node system. The moment you add a second machine,
  # you MUST handle partition tolerance. CA is a theoretical category
  # for single-node databases like SQLite.
  ca_note: >
    Single-node PostgreSQL is CA — consistent and available, but if
    the single machine goes down, you have neither. This is fine for
    development and small applications. Just don't call it 'distributed'.

  # ── Decision Rule ──────────────────────────────
  decision_rule: >
    Ask yourself: 'During a 30-second network outage between my database
    nodes, is it better to (A) return an error, or (B) return potentially
    stale data?' If A: choose CP. If B: choose AP.
    The answer reveals more about your system than any benchmark.

# DATABASE SELECTION FRAMEWORK
# Use this before writing a single line of code

function chooseDatabaseStack(accessPatterns):
    candidates = {}  # category -> [access patterns]

    for pattern in accessPatterns:
        category = mapToCategory(pattern)
        candidates[category].append(pattern)

    # Rule: If a single relational DB handles >80% of patterns, stop here
    if (candidates.size == 1 and candidates.hasKey("relational")):
        print("Use PostgreSQL. You don't need a second database yet.")
        return

    # Rule: If you need two categories, check operational cost
    for category, count in candidates:
        opsCost = estimateOpsCost(category)  # hours/month
        if opsCost > expectedPerformanceGain:
            print(f"Reconsider {category}: the Ops tax is higher than the latency gain.")
            candidates.remove(category)

    print("Final candidates:", candidates.keys())
    print("Implementation: each service owns its data store.")
    print("Cross-store consistency via event-driven patterns (Saga).")
    print("Prototype with production-scale data before committing.")

# Example usage:
# patterns = ["user_auth (key lookup, high consistency)",
#             "news_feed (key lookup, low consistency, high read)",
#             "friend_graph (relationship traversals)"]
# chooseDatabaseStack(patterns)
# Output: {"relational": [user_auth], "key-value": [news_feed], "graph": [friend_graph]}

-- io.thecodeforge
-- Log actual query patterns before picking a database
-- Run this against your existing application logs
SELECT
    query_type,
    COUNT(*) as frequency,
    AVG(execution_time_ms) as avg_latency
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
GROUP BY query_type
ORDER BY frequency DESC;

# io.thecodeforge
# Verify your database supports required compliance features

def check_compliance_requirements(db_type):
    requirements = {
        'field_level_encryption': ['postgresql', 'oracle', 'sqlserver'],
        'row_level_security': ['postgresql', 'sqlserver'],
        'audit_trail': ['postgresql', 'sqlserver', 'mongodb_enterprise'],
        'data_expiry': ['cassandra', 'dynamodb', 'mongodb'],
        'immutable_logs': ['dynamodb', 'cassandra', 'postgresql_audit']
    }
    
    missing = []
    for req, dbs in requirements.items():
        if db_type not in dbs:
            missing.append(req)
    
    return missing

# This will fail compliance audit
print(check_compliance_requirements('mysql'))
# Output: ['row_level_security', 'audit_trail', 'immutable_logs']