Backend — Phase I

This folder is the Backend bucket of the LBZF computer-vision deployment design docs. It owns: the SQLite data model on the Jetson, the Excel export Ronald uses, the FastAPI dashboard API, the user/org/Auth0 integration, the lbzfai.com ↔ Jetson integration, and the deployment / CI/CD plumbing.

It does not own: the ML/CV inference loop, the frontend UI, hardware/network, or business/privacy posture. Cross-bucket dependencies are flagged in each doc.

Reading order

data-model.md — start here. Everything else queries against this schema.
excel-export.md — Ronald’s drop-in adoption surface; pins the locale rules used everywhere else.
dashboard-api.md — the FastAPI REST endpoints on :5000 (live push channel is parked; Phase I polls REST) and the auth middleware.
user-org-and-auth.md — how Auth0 identities become app roles, where the user/org table lives, the JWT claim shape.
lbzfai-jetson-integration.md — how authenticated browsers at lbzfai.com reach Jetson data (Tailscale-direct per ADR-005; Worker-proxy is parked).
deployment-and-cicd.md — agent-safe Cloudflare deploys + SSH-push Jetson updates (R2 release manifest auto-pull is parked).

Architecture at a glance

                                                Auth0
                                                  ▲
                                                  │ login
                                                  │
   Mariana / Ronald / Sophia / Andrew / Armando / ITBA  ─►  lbzfai.com (Cloudflare Workers, static)
                                          │                       │
                                          │                       │ JWT custom claims:
                                          │                       │   https://lbzfai.com/role          (singular string)
                                          │                       │   https://lbzfai.com/org_id        (integer)
                                          │                       │   https://lbzfai.com/module_scope  (null in Phase I)
                                          │                       │
                                          │                       ▼
                                          │                Astro role-aware /app/* shell
                                          │                ─ /app/ingeniero (Ronald) — "Open dashboard via Tailscale"
                                          │                ─ /app/ejecutivo (Mariana) — mocked aggregates in Phase I
                                          │                ─ /app/admin    (Sophia, Andrew)
                                          │
                                          │  (all real-data access is Tailscale-direct per ADR-005)
                                          ▼
   Tailscale tailnet  ─►  Jetson :5000 (FastAPI REST dashboard)
                                          ▲
                                          │  (Tailscale-User-Login header → identity)
                                          │
                                          CV writer  (direct SQLite, no HTTP)
                                          ─ YOLOv8n + TensorRT
                                          ─ writes cycle_events + cycle_events_rejected + manual_observations
                                          ─ /data/frames/, /data/video/ on NVMe
                                          ─ SQLite (WAL) at /data/lbzf.db

Big decisions made in this bucket

SQLite stays. WAL mode, single-writer (CV writer writes directly to SQLite — no internal HTTP endpoint). Schema expanded from v1.9’s 3-table sketch to ~11 tables to handle multi-module from day 1 and paper-grade metadata. New tables in Phase B: rois, cycle_events_rejected, manual_observations. audit_log is parked.
FastAPI over Flask. Pydantic + auto-OpenAPI win the small per-developer-day tradeoff even without the live-push channel (which is parked to Phase II).
Tailscale-only for Phase I per ADR-005. The Worker-proxy + outbound-tunnel design is preserved in 60-parking/cloudflare-tunnel-from-jetson.md for Phase II.
Hybrid user/org store. Auth0 holds identity; a users.json bundled into the Worker (graduating to Cloudflare D1 later) holds role (singular string) + org_id + module_scope; the Jetson reads identity from Tailscale-injected headers.
SSH-push Jetson updates for Phase I. R2 release-manifest auto-pull is preserved in 60-parking/r2-release-manifest.md for Phase II (when fleet size justifies).
cycle_events retained forever; frames roll at 120 days. Paper dataset survives; forensic playback degrades gracefully.

Open questions to escalate (cross-bucket)

Question	Owner	Affects
Operator-ID capture mechanism (manual / badge / face-rec)	Agent E + Mariana	every export + cycle_events schema axis
Exact column set in `INDICADORES ABRIL.xlsx`	Ronald via Armando	Excel drop-in fidelity
Shift count and end-of-shift hour	Ronald	exporter cadence
ITBA emails for Auth0 invites	Andrew	user table

See each doc’s “Open questions” section for the full list with named owners.

Status

All docs in this folder are reviewed as of 2026-05-12. Phase B reconciliation (Sophia + Phase B agent) applied the round-2 seams: role names canonicalized (admin / engineer / executive); Cloudflare Tunnel parked per ADR-005; ML schema fields added; audit_log, R2 release manifest, multi-tenant Auth0, supervisor + research roles, and the live push channel all moved to 60-parking/. The docs remain action-ready and still need Ronald / Mariana review before deploy.

Phase I implementation outline

The sketch below was migrated from the pre-design technical/software.md split. It remains a quick reference for the Phase I shape; the canonical specs live in data-model.md, dashboard-api.md, and excel-export.md.

Data model (SQLite)

Three tables:

CREATE TABLE workstations (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,            -- e.g. "puesto 1", "perrillas"
  module TEXT NOT NULL,          -- "Angela", "Carmenza", ...
  camera_id INTEGER,             -- FK to cameras config
  operation TEXT                 -- e.g. "PESPUNTAR CUELLO"
);

CREATE TABLE cycle_events (
  id INTEGER PRIMARY KEY,
  workstation_id INTEGER REFERENCES workstations(id),
  start_ts TIMESTAMP NOT NULL,
  end_ts TIMESTAMP,              -- NULL while cycle is in progress
  duration_seconds REAL,
  notes TEXT
);

CREATE TABLE standard_times (
  id INTEGER PRIMARY KEY,
  reference TEXT NOT NULL,       -- e.g. "Ref22 Slim"
  operation TEXT NOT NULL,       -- matches workstations.operation
  sam_minutes REAL NOT NULL,
  machine_code TEXT              -- MPP, MREV, MOJA, MBOT, PLCH
);

CREATE INDEX idx_events_workstation_start ON cycle_events(workstation_id, start_ts);

Seed standard_times from Ref22 Slim - Angela.xlsx, “Balanceo” tab.

Dashboard

Flask or FastAPI (decision deferred; Flask easier for Phase I scope)
Bound to 0.0.0.0:5000, accessed via Tailscale from anywhere with the Tailscale client
Live cycle times per workstation vs SAM benchmark
Color-coded by efficiency band (thresholds TBD per module — umbrales a definir)

Excel export

pandas + openpyxl
Output format matches INDICADORES ABRIL.xlsx so the plant uses the export in their existing workflow without changing habits
Triggered manually in Phase I; cron’d daily in Phase II

Recording & storage

Cameras record their sub-stream to disk in 10-minute segments
Naming: cam{id}_YYYYMMDD_HHMMSS.mp4
Rolling deletion when free space drops below a threshold

Capacity math

6 cameras × 24 hr × ~24 GB/cam-day ≈ ~33 GB/day total → ~120 days rolling buffer on 4TB at Phase I scale, ~36 days at Phase III’s 20-camera scale.