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.