# Supabase vs Knex schema audit

The audit covers all 15 supplied Knex migration files. The request calls them “14,” but the supplied paths contain 15 files. Table names are deduplicated; the second `club_chats` CREATE and the two lesson-plan ALTER follow-ups do not add tables.

| Comparison | Tables |
|---|---|
| **Knex SQLite tables** | admission_enquiries, applicants, assignments, attachments, attendance, audit_logs, bank_accounts, bank_reconciliation_lines, bank_reconciliations, chart_of_accounts, chat_group_members, chat_groups, chat_messages, classes, club_announcements, club_attendance, club_chats, club_memberships, clubs, complaints, course_enrolments, course_grades, courses, crossword_clues, crossword_games, crossword_sessions, departments, discounts, education_level_configs, enrollments, events, exam_answers, exam_attempts, exam_groups, exam_review_audit, exam_schedules, expenses, fee_groups, fee_plan_installments, fee_plans, focus_points, grades, homework, homework_submissions, hostel_attendance, hostel_fees, hostels, invoices, item_categories, item_issues, item_stock, items, leave_requests, leave_types, lesson_plans, library_books, library_fines, library_issues, library_reservations, messages, notices, notifications, parent_students, payments, payroll_runs, payslips, phone_call_logs, pickup_points, postal_dispatch, question_banks, room_assignments, room_types, rooms, routes, salary_grades, schemes_of_work, school_settings, settings, staff_attendance, staff_records, staff_roles, stock_transactions, store_locations, student_fee_discounts, student_fees, student_group_members, student_groups, student_medical_history, student_medical_profiles, student_medication_logs, student_social_docs, subjects, subjects_new, submissions, suppliers, syllabuses, sync_config, sync_logs, sync_metadata, system_settings, tenant_licenses, tenants, test_submissions, tests, transport_allocations, transport_attendance, trip_payments, trips, users, vacancies, vehicle_routes, vehicles, visitor_logs |
| **Supabase-only tables (NOT in Knex SQLite)** | assignment_submissions, attendance_new, calendar_events, edutainment_content, edutainment_games, edutainment_sessions, messages_new |
| **Knex SQLite tables MISSING from Supabase** | attachments, chat_messages, education_level_configs, exam_review_audit, homework, homework_submissions, notifications, school_settings, student_medical_history, student_medical_profiles, student_medication_logs, tenant_licenses, test_submissions, tests |

## SHOULD-DROP candidates and exact FK risk

These are candidates only. The migration leaves every DROP commented.

| Table | Classification | FK risk / prerequisite |
|---|---|---|
| `assignment_submissions` | SHOULD-DROP; overlaps canonical Knex `submissions` | No inbound FK appears in the dump. It has outbound FKs to `assignments` and `users`; dropping it removes only its own data/constraints. Confirm active code does not query it. |
| `attendance_new` | SHOULD-DROP; canonical Knex table is `attendance` | No inbound FK appears in the dump. It has outbound FKs to `users` and `subjects_new`; dropping it does not remove those parent tables. |
| `calendar_events` | SHOULD-DROP; canonical Knex table is `events` | No inbound FK appears in the dump. It has an outbound FK to `users`; confirm active calendar code uses `events`. |
| `edutainment_content` | SHOULD-DROP | No inbound FK appears in the dump. It has outbound FKs to `subjects_new` and `users`; confirm the cloud-only edutainment feature is retired. |
| `edutainment_games` | SHOULD-DROP | **Inbound FK from `edutainment_sessions.game_id`.** Drop `edutainment_sessions` first or use `CASCADE`. It also has outbound FKs to `subjects_new` and `users`. |
| `edutainment_sessions` | SHOULD-DROP | No inbound FK appears in the dump. It has outbound FKs to `edutainment_games` and `users`; drop before/with `edutainment_games`. |
| `messages_new` | SHOULD-DROP; canonical Knex table is `messages` | No inbound FK appears in the dump. It has outbound FKs to `users`; confirm active messaging code uses `messages`/`chat_messages`. |

## Load-bearing non-drop finding

`subjects_new` is **not** cloud-only: `2026072000000001_fix_student_fees_and_subjects_new.js` explicitly creates it. It is also load-bearing in the dump: `courses`, `crossword_games`, `schemes_of_work`, `lesson_plans`, `syllabuses`, `edutainment_content`, `edutainment_games`, and `attendance_new` reference it. Do not drop it without migrating those FKs to canonical `subjects`.

## Schema discrepancies

- Knex and cloud `tenants.id` use UUID identifiers and seed `5b39bcb5-506a-456d-b7cb-91730b397cc1`.
- Knex `users.role` permits `dining_staff`; the cloud dump role CHECK omits it. `CREATE TABLE IF NOT EXISTS` cannot repair an existing CHECK, so this needs a separately reviewed constraint migration if the role is required.
- The dump points several active FKs at `subjects_new`, while canonical Knex tables generally point at `subjects`; this is retained rather than destructively rewritten.
