# Scaling to 3,000,000 animals & 3,000 users

This document explains how the system is structured to handle a herd database
of ~3 million animals with ~3,000 user accounts (hundreds active at once), and
what you need to do to run it at that scale.

## The core change: database, not flat files

The original prototype stored everything in JSON files (`data/*.json`) and
loaded **all** animals into the browser. That is fine for a few thousand
animals but collapses at millions. The scalable version replaces that with a
**MySQL / MariaDB** database and a paginated API.

| Concern | Old (JSON files) | New (MySQL) |
|---|---|---|
| Storage | One giant `animals.json` rewritten on every save | Indexed `animals` table, row-level locking |
| Concurrency | One file lock → all writes queue | InnoDB row locks → hundreds of writers in parallel |
| Search | Scan every record in the browser | `FULLTEXT` + indexed `WHERE` in SQL |
| Browser load | Loads all 3M records (crashes) | Loads **one page** (e.g. 50 rows) at a time |
| Photos | base64 inside each row (huge) | Files on disk (`uploads/animals/<id>.jpg`), path in the row |

## Files in the scalable backend

| File | Purpose |
|---|---|
| `schema.sql` | The database structure — tables, columns, **indexes**, foreign keys |
| `db.php` | PDO connection (persistent, prepared statements, utf8mb4) |
| `api_db.php` | The API — paginated animals list, search, CRUD, auth, per-owner scoping |
| `config.sample.php` | Template for `config.php` (DB credentials) — copy & fill on server |
| `migrate_import.php` | One-time import of existing `data/*.json` into MySQL (then delete) |

## Why it scales

1. **Indexes.** Every query the app runs hits an index: `owner_id` (per-user
   scoping), `reg_id`, `ear_tag`, `sire_id`/`dam_id` (pedigree & offspring),
   `(sex, role)`, `(province, district)`, and a `FULLTEXT(name, reg_id, ear_tag)`
   index for search. Finding one cow among 3M is a millisecond index lookup,
   not a 3M-row scan.

2. **Pagination.** `?action=animals&page=1&per_page=50` returns 50 rows +
   the total count. The browser holds 50 rows, never 3 million. Scrolling /
   paging fetches the next page on demand.

3. **Server-side search & filter.** Typing in the search box calls the API
   with `&q=...`; SQL does the work against indexes. The browser receives only
   matches.

4. **Per-owner scoping in SQL.** A herd owner's every query is constrained to
   `WHERE owner_id = <their id>` server-side — they can never load or see other
   herds, and their queries only ever touch their own (indexed) slice.

5. **InnoDB row-level locking.** Hundreds of users can insert/update different
   animals simultaneously without blocking each other (unlike the old single
   file lock).

6. **Photos as files.** Images live on disk and are served directly by the web
   server; rows stay small so list queries are fast.

## Running at 3,000,000 cows — step by step

Server-side tools (run on the host, **not** the browser):

| File | Purpose |
|---|---|
| `seed.php` | Generate millions of synthetic animals (with pedigree links) to load-test |
| `recompute.php` | Compute & store every animal's inbreeding in SQL (cron-able, resumable) |
| `?action=herd_stats` | Server-computed avg F, generation interval, ΔF, Ne (used by the Herd Genetics page in DB mode) |

**Runbook:**

1. Create the database + import `schema.sql` (see "Setup steps" below). Fill in
   `config.php` (`db` credentials + a `migrate_key`).
2. **Load test data** (or import your real data via `migrate_import.php`):
   ```bash
   php seed.php 3000000          # ~ a few minutes; uses bulk inserts
   ```
   Then open the app — the herd register pages, searches, opens animals, draws
   pedigrees and plans matings against 3M rows, all from indexed queries.
3. **Populate inbreeding for the herd report** (do this once after seeding/import,
   and after big pedigree changes):
   ```bash
   php recompute.php             # resumable; re-run or cron it until done
   # or as a cron every 5 min:
   #   */5 * * * * php /home/you/public_html/recompute.php >> recompute.log
   ```
   The **📊 Herd Genetics** page then shows herd-wide average inbreeding, ΔF and
   Ne computed in SQL (instant), and the herd report sorts millions by F instantly.
4. **Delete `seed.php` and `recompute.php`** when finished (they are guarded by
   `migrate_key`, but remove them anyway).

**Why these are server-side:** computing inbreeding for 3M animals from the
browser would be millions of requests. `recompute.php` does it in the database
with a recursive-CTE shortest-path Wright's coefficient, and the herd metrics are
plain indexed aggregates — so the UI never has to load the whole herd.

**MySQL tuning for big herds:** set `innodb_buffer_pool_size` to hold the indexes
in RAM (e.g. 2–4 GB on a VPS), keep the `animals` indexes from `schema.sql`, and
run `recompute.php` off-peak.

## Hosting reality (important)

- **MySQL/MariaDB is included on every Bluehost plan**, so the *code* runs
  anywhere. But **Bluehost *shared* hosting will not comfortably serve hundreds
  of concurrent users on 3M rows** — shared plans cap CPU, processes and
  simultaneous DB connections.
- For "hundreds active at once," use at least **Bluehost VPS or Dedicated**, or
  a proper VPS (Hetzner, DigitalOcean, Contabo) with MariaDB 10.4+ / MySQL 8.
- Because the code is standard PHP + PDO + MySQL, you can **start on a higher
  Bluehost tier and move to a VPS later without changing any code** — just point
  `config.php` at the new database.
- Recommended for heavy load (later): a caching layer (Redis) for sessions and
  hot lookups, and `innodb_buffer_pool_size` tuned to hold the indexes in RAM.

## Setup steps (on the server)

1. cPanel → **MySQL Databases**: create a database and a DB user, give the user
   all privileges on that database.
2. cPanel → **phpMyAdmin** → select the database → **Import** → upload
   `schema.sql`.
3. Copy `config.sample.php` to `config.php` and fill in the `db` credentials +
   a random `migrate_key`.
4. (If you have existing data) visit
   `https://yourdomain.com/migrate_import.php?key=YOUR_SECRET` once, then
   **delete `migrate_import.php`**.
5. Log in: `admin` / `larspeenghar2024` → change the password immediately.

## What's done vs. next

- **Phase 1 — backend (done):** database schema + indexes, PDO layer, scalable
  API (auth, paginated/searchable animals, owners, users, settings, stats,
  offspring), JSON→MySQL migration script, photo-to-file handling.
- **Phase 2 — frontend wiring (done):** `dbapi.js` is the data-access layer.
  When `api_db.php` answers, the app runs in **DB mode**: the herd register
  loads **one page at a time** (`?action=animals` with server-side search,
  filters and per-owner scoping), the global `animals` array is just a cache of
  the visible page + the open animal, and add/edit/delete go through the API.
  A "Load more" footer pages through results ("Showing 50 of 3,000,000"). If
  `api_db.php` is absent the app keeps its original local/localStorage behaviour
  unchanged. Login and session-resume also prefer the DB backend.
  - Files: `dbapi.js` (new), plus DB-mode branches in `renderSB`/`selAnimal`/
    `saveAnimal`/`doDelete`/`doLogin`/`startApp` in `app.js`.
  - Upload `dbapi.js` alongside the other files (it's loaded in `index.html`).
- **Phase 3 — whole-herd analytics (done):** server-side endpoints so the big
  analytics work at 3M scale without loading the herd into the browser:
  - `?action=pedigree&id=#&depth=N` — recursive-CTE ancestor subgraph. The Deep
    Pedigree page fetches it, caches it, and the existing tree / inbreeding /
    diversity panels run unchanged. The computed F is posted back via
    `?action=set_f` so the herd report can sort at scale.
  - `?action=ancestors_batch&ids=…` — merged ancestor subgraphs for the
    selected animals; the Kinship matrix preloads these then computes pairwise F.
  - `?action=herd_report` — animals sorted by **cached** `inbreeding_f` (new
    column) + herd average, paginated → instant at 3M rows.
  - `?action=founders` and `?action=top_ancestors` — founders (no parents) and
    most-influential ancestors (offspring-count `GROUP BY` on indexed columns).
  - Schema gained `inbreeding_f` / `avk` columns (+ index). The frontend uses
    these in DB mode; local mode keeps the original in-browser computation.

  **Recommended for very large herds:** run a background batch that walks every
  animal's pedigree once and stores `inbreeding_f` (the app fills it lazily as
  animals are viewed in Deep Pedigree, which is enough for most cases).

## DB mode vs. flat-file mode (deployment note)

A DB deployment should use **`api_db.php`** as the backend. If both `api.php`
(old flat-file) and `api_db.php` are present, `api_db.php` takes precedence in
the frontend. For a clean scalable install you do not need `api.php`,
`sync.js`'s flat-file path, or the `data/*.json` files (except as the *source*
for the one-time `migrate_import.php`).
