# Data Stores & Logging Strategy

🟢 **Decided 2026-06-13.** How we store functional data, audit trails, and the
high-volume behavioral-analytics firehose — without the analytics load ever
slowing the live system.

---

## Why this matters (grounded in the current data)

In the current courses DB (`laff_data`), **logs already dwarf the functional
data**. Measured on-disk sizes:

| Table | Type | Rows | Size |
|---|---|---|---|
| `wp_thrive_reporting_logs` | **analytics/events** | 2,098,964 | **501 MB** ← biggest object in the DB |
| `wp_aryo_activity_log` | audit | 323,738 | 37 MB |
| `wp_tva_access_history` | audit | 312,063 | 70 MB |
| `wp_uap_*` (Automator logs+meta) | audit/automation | ~400k+ | ~400 MB combined |
| **`wp_tva_orders`** | **functional** | 167,310 | **31 MB** |
| **`wp_users`** | **functional** | 41,113 | **10 MB** |

The single biggest object in the database is a **reporting log**, ~16× the size
of the orders table. This proves the instinct: high-volume logging must be kept
off the functional data path.

---

## The three workloads (they are NOT the same thing)

| Workload | Examples | Volume | Read pattern | Lose-tolerant? | Home |
|---|---|---|---|---|---|
| **Functional** | users, orders, enrollments, courses, payments | small | row lookups + joins, hot path | **No** | **Postgres (main)** |
| **Audit / business events** | access granted/revoked, order status changes, admin actions, automation runs, dispute trail | moderate–large | occasional, joined to user/order | No (compliance) | **Postgres (separate DB, same server)** |
| **Behavioral analytics** | navigation/clickstream, A/B-test exposure, funnel/quiz interaction, lesson-watch events | **huge, fastest-growing** | bulk aggregation, never row-by-row | Yes (resampleable) | **Custom pipeline → columnar store** |

> The mistake to avoid: dumping all three into one "logs" database. Each has a
> different shape, durability need, and query pattern.

---

## Decision 1 — Database engine: **PostgreSQL**

Chosen over MySQL for stricter types, better JSON, partitioning, and stronger
analytical SQL. Migration from the WP MySQL data is a one-time ETL, so MySQL's
"same engine" advantage doesn't outweigh Postgres for a from-scratch schema.

## Decision 2 — **Two databases on one Postgres server (split-aware)** 🟢 revised 2026-06-13

Audit is kept in its **own database** (`laff_audit`), *separate from* the
functional database (`laff`) — but **both live on the same Postgres server** for
now. The app talks to audit through its **own connection config**, so promoting
`laff_audit` to its own server later is an infrastructure change with **zero
application code change**.

```
┌─────────────────────────────────────────────────────────────────────┐
│ ONE PG SERVER (e.g. t3.medium)                                        │
│  ┌──────────────────────────────┐   ┌──────────────────────────────┐ │
│  │ DB "laff" (functional, sacred)│   │ DB "laff_audit" (audit/events)│ │
│  │ users·orders·courses·payments │   │ access_history·activity_log·  │ │
│  │ → transactional, every q fast │   │ order_status_events·auto_runs │ │
│  └──────────────────────────────┘   └──────────────────────────────┘ │
│  app uses a SEPARATE connection for laff_audit (split-aware)          │
└─────────────────────────────────────────────────────────────────────┘
        │  later, on a measured trigger, lift laff_audit to its own box:
        ▼
   pg_dump laff_audit → restore on new instance → repoint that connection
```

### Why this (not two separate servers)
We originally specced two physical servers. Re-checked against **real load**:
current audit-style writes are **~88 orders/day, ~20 signups, a few hundred
access/activity events** — well under 1 write/sec. That is **not** a firehose; the
contention a second server would protect against doesn't exist at our scale, and a
single box already serves ~40–60k sessions/day. Two servers now = paying for and
operating hardware to isolate a trickle.

### What we keep / give up
- **Keep:** clean separation (audit clearly delineated in its own DB), one server
  to run/back up now, the **same no-JOIN discipline** we'd already accepted, and a
  **clean lift-and-shift seam** to split later.
- **Give up:** cross-database SQL JOINs between `laff` and `laff_audit` — same
  constraint as the two-server plan, so nothing new lost. Audit reads are rare and
  stitched in the app; never on a hot path.

### Trigger to split `laff_audit` to its own server (don't do it early)
- audit write volume climbing enough to show in the functional DB's CPU/IO, **or**
- `laff_audit` growing large enough that its backups/vacuum affect the main DB's
  performance window, **or**
- you want hard risk-isolation (an audit-logging bug can't fill the payment DB's
  disk) — though a disk/tablespace quota can cover that without splitting.

Same "build lean, split on a measured signal" pattern as the Redis ladder,
PgBouncer, and EC2 sizing.

## Decision 3 — Behavioral analytics: **build in-house, decoupled pipeline**

We build our own event pipeline (not PostHog/SaaS) to keep full ownership,
consistent with the in-house philosophy. The architecture that makes it *safe*:

```
  User action (navigate / click / see A/B variant / watch lesson)
        │
        ▼  fire-and-forget — NEVER blocks the HTTP response
  ┌──────────────────────────┐
  │  Event collector endpoint │  thin; validates + pushes to queue, returns instantly
  └────────────┬─────────────┘
               ▼
  ┌──────────────────────────┐
  │  Redis Stream (buffered)  │  buffer between capture and storage; drained
  │  via Horizon worker pool  │  in batches. Detail: queues-and-redis.md
  └────────────┬─────────────┘
               ▼  async workers, BATCHED bulk inserts
  ┌──────────────────────────┐
  │  Analytics store          │  Phase 1: Postgres (partitioned events table)
  │                           │  Later: ClickHouse when volume demands columnar
  └──────────────────────────┘
```

### THE RULE (inviolable) 🔒
**No analytics write ever happens synchronously in a user's request path.**
Every event is fire-and-forget to the queue; async workers batch-insert them.
This single rule is what keeps the system fast no matter how much we log —
the user's latency is fully insulated from analytics storage performance.

This applies to **server-side** events we emit (Laravel) *and* client-side
events from the Vue/Nuxt apps (the JS SDK batches and sends async, never
blocking navigation).

### Storage: Postgres now → ClickHouse later
- **Phase 1:** land events in a **partitioned Postgres table** (by day/month) on
  its own store. At current scale (~2M rows) Postgres handles it fine.
- **Later (on a measured trigger):** move the firehose to **ClickHouse** (column
  store) for funnel / navigation / A-B-test aggregation over tens of millions of
  rows. Because storage sits behind the queue + a clean interface, **swapping the
  store does not touch business logic.**
- **Trigger to migrate to ClickHouse (pre-agreed):** events table > ~50–100M rows
  *or* aggregation queries for dashboards exceeding a few seconds.

### What we must build (in-house scope)
- [ ] JS tracking SDK (autocapture + manual `track()`), used by Nuxt/Vue apps.
- [ ] Server-side `track()` interface in Laravel (pushes to queue).
- [ ] A/B test framework: variant assignment (sticky per user), exposure logging.
- [ ] Worker(s) that batch-drain the Redis Stream into the analytics store
      (Horizon `analytics` pool — see [queues-and-redis.md](queues-and-redis.md)).
- [ ] Aggregation/reporting queries + dashboards (Reporting module).

### Clean interface (so the store is swappable)
All app code calls one stable method — never the DB directly:
```
analytics.track('lesson_viewed', { user_id, lesson_id, variant, ts })
   → queue → workers → (Postgres today | ClickHouse later)
```

---

## Summary: the full storage picture

```
ONE PG server (now):
  DB laff        → users, orders, courses, payments          (sacred, fast)
  DB laff_audit  → access_history, activity_log, order_events  (separate DB; split-aware)
Analytics pipeline → collector → queue → workers → Postgres→ClickHouse (decoupled)
App/debug logs    → Laravel files + error tracking (NOT in SQL)   [see note]

Later (on a trigger): lift DB laff_audit onto its own server — no app code change.
```

> **App/debug logs note:** Laravel error/request/debug logs are disposable and
> read only during incidents — they belong in files (and optionally an
> error-tracker), **not** in any SQL database. Open question:
> [open-questions.md](../00-index/open-questions.md) — self-host an error tracker
> or use a SaaS free tier.