Commit cf29e8
2026-08-07 11:34:44 admin: init| /dev/null .. 08-data-model-supabase.md | |
| @@ 0,0 1,67 @@ | |
| + | --- |
| + | title: Data Model (Supabase) |
| + | aliases: [Supabase Schema, Database Schema] |
| + | tags: [3netra/wiki, supabase, data-model, diagrams] |
| + | product: 3netra Meta Android App |
| + | source_repository: 3Netra-ai/3n_meta_android |
| + | source_ref: origin/prod |
| + | source_revision: b48aada8fd5427b5db0c02945569963c4ae543c8 |
| + | last_verified: 2026-08-06 |
| + | up: "[[Home]]" |
| + | --- |
| + | |
| + | # Data Model (Supabase) |
| + | |
| + | > [!warning] No DDL is committed |
| + | > No migrations, seed, or `CREATE TABLE` statements are present. The Kotlin payload/data classes and table names below show **what the client expects**, not an authoritative deployed schema. Local config requests Postgres 17, API port 55321, and DB port 55322; it has no schema paths. |
| + | |
| + | ## Entity Diagram |
| + | |
| + | ```mermaid |
| + | erDiagram |
| + | AUTH_USERS ||--|| USER_PROFILES : "id" |
| + | USER_PROFILES ||--o| STUDENTS : "student" |
| + | STUDENTS ||--|| STUDENT_SETTINGS : "student_id" |
| + | STUDENTS ||--o{ SESSIONS : "student_id" |
| + | STUDENTS ||--o{ FACE_EMBEDDINGS : "student_id" |
| + | SESSIONS ||--o{ SESSION_EVENTS : "session_id" |
| + | STUDENTS ||--o{ EMERGENCY_ALERTS : "student_id" |
| + | AUDIT_LOGS }o--|| AUTH_USERS : "actor_id" |
| + | ``` |
| + | |
| + | ## Tables (columns as used in code) |
| + | |
| + | | Table | Columns | Notes | |
| + | |---|---|---| |
| + | | `user_profiles` | `id`, `role`, `first_name`, `last_name` (+ UI reads `avatar_url`) | Client attempts bootstrap/query | |
| + | | `students` | `id`, `date_of_birth`, consent/opt-out fields | Client payload; values/workflow are not validated in repo | |
| + | | `student_settings` | voice, language, capture and battery fields | Client queries/updates and declares a Realtime subscription | |
| + | | `sessions` | `student_id`, `mode`, `session_summary`, `offline_mode`, `created_at` | Client attempts an insert; Today's Story itself is in-memory | |
| + | | `face_embeddings` | `id`, `student_id`, `person_name`, CSV embedding | Active enrollment writes and matching reads this cloud table | |
| + | | `session_events` | event concepts | Mentioned in repository code/docs; table contract unverified | |
| + | | `audit_logs` | actor/action/entity fields | Repository method attempts inserts; not compliance proof | |
| + | | `emergency_alerts` | SOS payload fields | Service attempts insert but GPS/summaries are TODO | |
| + | |
| + | > [!todo] Planned but absent from code |
| + | > `schools`, `caregiver_students`, `iep_goals`, `progress_reports` — designed in Mayukh's schema work, PRD-level only. |
| + | |
| + | ## Compliance Design (FERPA / Higher Ed Privacy) |
| + | |
| + | - **Face data:** FaceNet runs on-device, but active code writes/reads CSV embeddings in `face_embeddings`. The AES/90-day utility is not invoked, including its retention method. |
| + | - **Consent/audit/RLS:** client fields and insert methods exist; no versioned schema, policy, consent flow, audit coverage, or test proves their operation. |
| + | - **Media:** the SAM bucket has no lifecycle rule; only a comment suggests one. |
| + | - **Vendor flow:** scene/photo bytes are sent to the Supabase Edge Function for VLM analysis. Do not claim local-only face or media handling. |
| + | |
| + | See [[11-Security-Privacy-Compliance]] for the audited gap analysis. |
| + | |
| + | ## Auth Model |
| + | |
| + | - Providers: email/password, email OTP, Google (IDToken via Credential Manager) |
| + | - Deep links: `threenetra://` + `http://localhost:3000` for OAuth/email callbacks |
| + | - Roles today: `student` (role field exists for future teacher/parent/caregiver) |
| + | - Teacher gating in-app via local BCrypt PIN (`3netra_secure` prefs), not a backend role — **gap to close** |
| + | |
| + | --- |
| + | |
| + | > [!tip] Navigation |
| + | > ⬅️ [[07-Services-APIs-Vendors|Services, APIs & Vendors]] · 🏠 [[Home]] · ➡️ [[09-Design-System|Design System]] |