Skip to content

NEXORA — Known issues & technical debt

This file lists inconsistencies / risks that were identified but deliberately not fixed yet, to keep a trace and not lose them. High priority = to handle soon.

🔴 High — SQL built by interpolation

Several tools execute Postgres by building SQL through string concatenation (no ORM / no parameterized queries):

  • scripts/import_march.py → psql() / psql_query() (ensure_classes_subjects, ensure_students, ensure_sessions, ensure_parent_links…)
  • processing/pipelines/reports.py → _write_to_db() (only escapes apostrophes of the content)
  • ai/pipeline/db.py → query() / execute() / set_status() / get_reference_photos()

Today all values are internal constants (demo: S001..S031, CLASSES…) or escaped, so no exploit path. But it is fragile:

  • Table / column names are interpolated (no %s).
  • Values go through single quotes (escaped for LLM content only).

Suggested fix: migrate these three modules to psycopg or postgres (the driver already used on the web side) with real parameterized queries (%s / $1), or expose a small shared SQL client.

🟠 Medium — Other points to watch

  1. Lake purge on every import. import_march.run_pipeline() and the standalone mode delete bronze/silver/gold before re-running. Correct for the single-batch demo, but incompatible with incremental multi-lesson accumulation. Eventually: ingest without purge + hook the worker so new lessons are appended.

  2. processing/.venv corrupted on this machine. It belongs to root with a symlink to a nonexistent python → uv run in processing/ fails locally (Permission denied). The code has a “current interpreter” fallback (ai/pipeline/runner.py::_run_polars and scripts/import_march.py), so it works if the calling interpreter has polars. Definitive fix: sudo rm -rf processing/.venv then uv sync in processing/ (the venv must exist for uv run in Docker).

  3. Two demo data paths. scripts/import_march.py (importer) is the source of truth; web/scripts/seed_fake_data.ts is a shortcut that delegates to it. ai/pipeline/steps/classify.py keeps a legacy PROFILES: the “random” logic stays deliberately simplistic until a real behavior model exists.

  4. ORIGIN / BETTER_AUTH_SECRET hardcoded in docker-compose.yml — dev values; inject them properly in prod (same as the .env).

  5. web/.env contains GITHUB_CLIENT_ID/SECRET never used (no GitHub plugin in auth.ts) => dead variables to remove or wire up.

  6. Root .env.example vs web/.env.example diverge (two sources of truth). web/.env.example is the most complete.

  7. No versioned Drizzle migrations: drizzle-kit push is used (fine for dev, but no reproducible migrations for prod).

  8. Scoring tables duplicated in ai/pipeline/steps/score.py (inline _InlineEngine fallback in case the canonical engine processing/pipelines/scoring.py fails to import). The canonical engine must stay the reference; the fallback is only a safety net.

  9. Photos and storage paths: photo.filename stores the URL returned by storage.put (with /). Verify LocalStorage vs S3 consistency for photo DELETE.

  10. merge_segments removed from import_march.py (the old 2-min chunk splitting imposed a magic 123.147 s offset). The new format is direct multi-lesson, so it is no longer needed.