---
url: https://docs.nemo12.com/ops/data-audit.md
description: >-
  SRC-1423 (17.10.2026): which D1 tables hold the data, which data nothing
  reads, which SQL files are rebuildable, and which API routes have no caller.
---

# Database, SQL and API audit (17.10.2026)

Owner 17.10.2026 (SRC-1423): audit the database, the SQL scripts and the API; which tables hold the most
data, and which data exists but is never used. Companion of the repo size audit
(`docs/ops/repo-size.md`, in the nightly of 17.10.2026).

**How it was measured.** Production D1 `nemo12-platform`, read-only, through `npm run seed --
scripts/noop-readonly.sql "<SELECT>"` (the owner's rule: production only through GitHub Actions). D1 has
no `dbstat`, so size is the sum of `LENGTH(CAST(col AS BLOB))` over every column (data only, no
indexes). "Read at runtime" means the table name appears in a worker's source (`workers/*/src`, the www
worker in nemo12-www), tests and migrations excluded.

## Summary

| | Count | Note |
| --- | --- | --- |
| Tables | 371 | 281 with rows, 90 empty |
| Rows | 158,728 | |
| Data | 98.0 MB | top 5 tables hold 52 MB |
| Tables never read at runtime | 9 | 6 of them hold data (2.8 MB) |
| API paths | 671 (787 routes) | 61 have no caller in either repo |

## Biggest tables

| Table | MB | Rows | Finding |
| --- | --- | --- | --- |
| `content_item_drafts` | 19.91 | 4,892 | All from one item-generation run in 08.2026. 3,127 failed the quality gate; none of the 4,892 prompts exists in `items`. 13.2 MB of it is `quality_json` (evaluator output) |
| `items` | 11.20 | 17,310 | 17,224 published. 12,028 are in subjects the English-only direction removes: math 4,180, vietnamese 2,296, physics 1,994, chemistry 1,946, informatics 1,812, biology 800. `quality_json` 3.8 MB |
| `articles` | 8.02 | 400 | www articles. `body_md` 2.82 MB is never read: the worker serves `body_html` |
| `learner_model_versions` | 6.57 | 2,022 | 872 current versions; the 1,150 superseded ones hold 5.12 MB |
| `seo_pages` | 6.01 | 191 | `html` 4.61 MB and `data_json` 0.74 MB are both served |
| `ielts_micro_skill_items` | 3.68 | 5,008 | Read |
| `lab_content` | 2.77 | 1,587 | Read |
| `ai_teen_content_versions` + `_docs` | 3.79 | 297 | AI Teen leaves nemo12 (English-only direction) |
| `learner_models` | 2.05 | 328 | Read |
| `b21_indicators`, `b21_levels` | about 2.3 | 7,670 | b21 moved to conan1.com (redirect in flight) |
| `illustrations` | 1.08 | 825 | Never read at runtime (see below) |

## Data that exists but nothing uses

| What | Size | Evidence | Proposal |
| --- | --- | --- | --- |
| `content_item_drafts` | 19.9 MB | One-off run, none promoted to `items` | Export to R2 (`nemo12-content/archive/`), then delete |
| `items` of the removed subjects | about 8 MB | Subjects leaving nemo12 | Delete with the subject removal (`toan12-split`, `english-only-cleanup`) |
| Superseded `learner_model_versions` | 5.1 MB | 1,150 of 2,022 rows | Keep the last 5 per learner and model kind in D1, older ones to R2 (the model-engine standard keeps every version; this moves them, it does not drop them) |
| `articles.body_md` | 2.8 MB | Only `body_html` is read | Stop writing it (nemo12-www seed), then drop the column (expand/contract) |
| `illustrations` | 1.1 MB | Seeded by www (773 rows), learn (33), marlins (19); read by nothing | Stop seeding, drop the table |
| `curriculum_lesson_gate_choices` + `_gate_quiz` | 1.35 MB | 15,552 rows; marlins renders gates from `apps/marlins/src/parentLessonGates/*.ts` | Drop the tables and `scripts/seed-parent-gates-*.sql` (4.5 MB in git), or make D1 the one source |
| `curriculum_unit_specs`, `_unit_prereq_notes`, `_course_prereqs` | 0.37 MB | 971 rows, never read | Drop, or wire up the feature that wanted them |
| 3 empty tables with no code | 0 | `ai_teen_content_import`, `speak_recording_comments`, `speak_recording_feedback` | Drop |
| 87 empty tables that code does read | 0 | Features built, never used: essay 10, speak 10, live 7, partner 6, school 5, event 4, walk 4 ... | Owner decision per feature; no size cost |
| Proxy email opens | small | 1,098 of 1,706 `email_opens` are mail-proxy fetches, not people | Count only `direct` opens in open-rate figures |

The parent course content lives twice: as TypeScript in `apps/marlins/src/parentLessons/` (about 5.8 MB in
git) and in the `curriculum_lesson_*` tables (filled by `scripts/seed-parent-bodies-*.sql`, 7.3 MB in git,
pulled back with `pull-parent-bodies.mjs`). One source should win; D1 edited in Coral is the one the
project's content rule points to.

Logs grow slowly: `engine_runs` and `workflow_run_steps` add about 2,000 rows a month each, under 1 MB in
total. A 90-day retention is enough when they start to matter.

## SQL files in nemo12.com `scripts/`

| Kind | Files | MB | What to do |
| --- | --- | --- | --- |
| Generated (header says so, a generator exists) | 30 | 19.5 | Generate right before loading, do not commit (same change as in nemo12-www) |
| One-off, already loaded (dated, backfill, fix, rebalance) | 15 | 0.8 | Archive to R2 with the date they ran |
| Hand-written | 70 | 1.1 | Review: most are also one-offs |

Largest generated: `seed-b21.sql` 3.0 MB (goes with b21), `seed-parent-bodies-1..4` 7.3 MB,
`seed-parent-gates-1..4` 4.5 MB (feeds tables nothing reads), `seed-grammar.sql` 1.3 MB,
`seed-videos.sql` 0.7 MB (keep generating: nemo12-videos writes it).

## API routes with no caller

671 paths in `workers/api/openapi.snapshot.json`; every `/v1` and `/e` literal in both repos' apps,
workers and packages matched against them. 61 have no caller:

* **External by design (keep):** email links `/e/o/{file}`, `/e/s/{token}`, `/e/u/{token}`,
  `/e/one-click/{token}`, `/email/preferences/{token}`; `/v1/health`; `/v1/auth/access-session`,
  `/v1/auth/mentor-grant`.
* **Admin and editor screens (verify):** 22 under `/v1/admin` (audit-log, dead-letters, engine-runs,
  foundry runs, learners, parents, workflow-runs ...), 9 under `/v1/content/items/...`, 5 under
  `/v1/coral`. Some may be called through a base-path constant the text match cannot follow.
* **Built, no screen:** `/v1/privacy/*` (4: data usage, deletion requests, retention policies),
  `/v1/consents` (2), `/v1/whale/*` (3), `/v1/public/schools`, `/v1/public/markmaps`,
  `/v1/public/chat-testimonials`, `/v1/public/ielts/micro-skills`, `/v1/parent/course-progress`,
  `/v1/parent/ebook-progress`, `/v1/partners/my-discount`, `/v1/event-templates`.

A text match is a candidate list, not a verdict. Before removing a route, confirm zero hits in 30 days of
production requests (Workers analytics per path). Privacy routes stay even with no screen if they back a
legal promise; they need a screen instead.

## Order

1. Free, no product decision: drop `illustrations` and the 3 code-less empty tables; stop writing
   `articles.body_md`; count only direct email opens.
2. Archive then delete `content_item_drafts` (19.9 MB, a fifth of the database).
3. With the subject removals already in flight: `items` of removed subjects, AI Teen and b21 tables.
4. One source for parent course content (Coral D1 or TypeScript), then drop the other copy and its seeds.
5. Superseded learner model versions to R2.
6. API: route hit counts for 30 days, then remove what stays at zero.

Result: about 98 MB of data down to about 50-55 MB, and about 25 MB fewer SQL files in git.