Every table the EM Trainers system has, what each column stores, how the tables connect, and how a lesson moves from the database to a student's WhatsApp. Database emtcc on the server's Postgres, plus lesson files in MinIO. Checked read-only.
Every place it could be stored was checked: the number table em_wa_numbers has 0 rows, the "Lessons Bot" chat section has a blank number and Phone number ID, and the old July system has 0 WhatsApp accounts. Only a template name is filled in (em_day_test, language ml), and that was left by the automated test, so it must be replaced with the real approved template.
Where it will live once added (Admin → EM Setup → WhatsApp numbers): one row in em_wa_numbers with role bot. The token is stored encrypted. The engine then copies the number into the "Lessons Bot" chat section automatically.
What the business must provide: the WhatsApp number (91…), its Phone number ID, the WABA ID, a permanent access token, and an approved template with a quick-reply Start button and no variables. In Meta, the webhook must point to /api/engine/meta-webhook (verify token shown on EM Setup), subscribed to messages, plus message echoes for trainer numbers. Each trainer's own WhatsApp Business number is added the same way with role trainer.
emtcc databasePostgres on the shared server (supabase-db). 61 tables in three sections: public (students, trainers, lessons, settings) and two chat sections with EP's generated names: p4boly0zb0p8fp1 = lessons-bot chats, py7a0z2w9agcvv2 = trainer-phone chats. 12 MB.
emtrainers-mediaFile storage. em_DAY_ORDER_TYPE lesson files (338: 328 voice notes, 10 images), chat-media/ for chat photos and voice notes (created on first message), and the old lessons/ originals.
emtrainers databaseThe old July system. Its 736 lesson messages were copied into Lesson_Resources with the same ids; its student and log tables were empty. Not used by the new system.
flowchart TB
subgraph SETUP["Admin → EM Setup"]
N["em_wa_numbers<br/>bot + trainer numbers<br/>(token encrypted)"]
S["app_settings<br/>em_* settings"]
TR["Trainers_info"]
EN["Enrol students"]
end
EN --> SD["Student_Details<br/>name, mobile, class mobile"]
SD --> SPg["Student_Progress<br/>day + state"]
SPg <--> J1["t_ovaj35d9wum<br/>weekday trainers"]
SPg <--> J2["t_71p261vuowi<br/>Sunday trainers"]
J1 --> TR
J2 --> TR
N -. "copied to" .-> CFG["projects.config.json<br/>Lessons Bot section"]
CRON(["Daily 08:00 job"]) --> SPg
CRON -- "template via bot" --> META{{"WhatsApp<br/>Cloud API"}}
META -- "Start tap / replies / statuses" --> WH(["Webhook<br/>/api/engine/meta-webhook"])
WH --> CH1["Chat History<br/>p4boly0zb0p8fp1<br/>(lessons bot)"]
WH --> CH2["Chat History<br/>py7a0z2w9agcvv2<br/>(trainer phones)"]
WH --> SPg
LR["Lesson_Resources<br/>54 days · 736 parts"] --> SEND(["Send day's lesson"])
MINIO[("MinIO emtrainers-media<br/>em_DAY_ORDER_TYPE files")] --> SEND
SEND --> META
SEND --> MST["Media_Status_Trainer<br/>receipt per part"]
SEND --> CH1
CRON --> LOG["Student_Day_logs<br/>events"]
WH --> LOG
H["holidays"] --> CRON
S --> CRON
OV["trainer_support_overrides"] --> SEND
TR --> SEND
CH1 & CH2 & SPg & LOG --> DASH(["TCC dashboards"])Boxes are tables, rounded boxes are running jobs, the hexagon is WhatsApp. Arrows show which way data moves.
erDiagram
Course_Details ||--o{ Student_Details : "course_id"
Course_Details ||--o{ Lesson_Resources : "course_id"
Student_Details ||--o{ Student_Progress : "student_id"
Student_Progress ||--o{ t_ovaj35d9wum : "student_id1"
Trainers_info ||--o{ t_ovaj35d9wum : "student_id2"
Student_Progress ||--o{ t_71p261vuowi : "s_student_id"
Trainers_info ||--o{ t_71p261vuowi : "sunday_trainer_id"
Student_Details ||--o{ Student_Day_logs : "student_id"
Trainers_info ||--o{ Student_Day_logs : "trainer_id"
Lesson_Resources ||--o| lesson_media_sync : "lesson_id"
Student_Progress ||--o{ trainer_followups : "student_progress_id"
Course_Details { bigint id PK
varchar Course_Name
varchar Region }
Student_Details { bigint id PK
varchar Student_Name
varchar Mobile_Number
varchar Class_Mobile_Number
bigint course_id FK
date Batch_Date }
Student_Progress { bigint id PK
bigint student_id FK
varchar current_lesson_day
varchar Progress_state
int Follow_up_count
date Next_Followup_At
bool is_stop
date Next_Start_Date }
Trainers_info { bigint id PK
varchar full_name
varchar Trainer_WA_Number
varchar Shift
jsonb Day
varchar Status }
t_ovaj35d9wum { bigint student_id1 FK
bigint student_id2 FK }
t_71p261vuowi { bigint s_student_id FK
bigint sunday_trainer_id FK }
Lesson_Resources { bigint id PK
bigint course_id FK
int Day
int Send_Order
varchar Resource_type
text Text_Message
text File_Key
text Media_ID
date Media_Expiry_Date }
Student_Day_logs { bigint id PK
bigint student_id FK
bigint trainer_id FK
varchar lesson_day
varchar event_type }| Linked by phone number (last 10 digits) | How |
|---|---|
| Student → lesson-bot chats | Student_Details.Class_Mobile_Number (or Mobile_Number) = p4boly0zb0p8fp1."Chat History" From/To_Phone_Number |
| Trainer → trainer chats | Trainers_info.Trainer_WA_Number = py7a0z2w9agcvv2."Chat History".Phone_Number |
| Number registry → chats | em_wa_numbers.phone_number_id = Chat History.Phone_Number_ID (role bot → bot chats, role trainer → trainer chats) |
| Student → lesson receipts | student's number = Media_Status_Trainer.WA_NUMBER (no student id stored) |
| Trainer → substitutions | Trainers_info.Trainer_WA_Number = trainer_support_overrides.main_wa |
The links are kept by the code, not enforced by the database (EP's design). A student's trainers are not a column on the student: they are rows in the two junction tables, one row per trainer. t_ovaj35d9wum holds the weekday trainers and t_71p261vuowi the Sunday trainers; both point at Student_Progress.id, not Student_Details.id. Chats link to students by phone number (last 10 digits).
sequenceDiagram
autonumber
participant J as Daily job (08:00 IST)
participant DB as Database (emtcc)
participant WA as WhatsApp Cloud API
participant S as Student
participant T as Trainer phone
J->>DB: Read Student_Progress (Ready / Waiting_completed, has weekday trainer)
J->>WA: Day template with Start button (from bot number)
J->>DB: State = Waiting_start · Next_Followup_At = tomorrow · log waiting_start · Chat History row
WA->>S: Template arrives
S->>WA: Taps Start
WA->>DB: Webhook → Chat History (incoming Button)
DB->>DB: Claim: state = Waiting_completed · log waiting_completed
loop each Lesson_Resources part (Send_Order)
DB->>WA: Text / voice note / image (media from MinIO, Media_ID cached 29 days)
WA-->>DB: Status "sent" → next part
end
DB->>WA: Trainer footer (Morning / Evening / Night names + numbers)
WA->>S: Lesson + footer
S->>T: Homework on trainer's own WhatsApp
T-->>DB: Coexistence webhook → trainer Chat History
Note over J,DB: Next morning: Waiting_completed → day + 1 → new templatestateDiagram-v2 [*] --> Ready: Enrolled Ready --> Waiting_start: 8 AM template sent Waiting_start --> Waiting_completed: Start tap, lesson sent Waiting_start --> follow_up_sent: No tap, reminder 1 follow_up_sent --> follow_up_sent: Reminders 2 to 5 follow_up_sent --> Waiting_completed: Start tap, lesson sent Waiting_completed --> Waiting_start: Next morning, next day Waiting_completed --> [*]: Day 54 done Ready --> Paused: Pause date set Paused --> Ready: Pause date reached
| When | What happens |
|---|---|
| Day D | Template sent, state Waiting_start, next reminder date D+1 |
| D+1, D+2, D+3, D+7, D+12 | Reminders 1–5: same template again, state follow_up_sent |
| D+19 | Reminder date cleared; no more reminders until the student replies |
| Rest day / holiday | No sends; a due reminder goes out on the next working day |
Students the dashboard puts into other states (Paused, Stuck, Dropped, Completed…) leave this cycle until set back to Ready. Paused with a date (Next_Start_Date) returns to Ready on that date automatically.
Old system (emtrainers) | New Lesson_Resources |
|---|---|
lesson_courses.id = 1 | course_id = 1 → Course_Details "EM Trainer Basic", KERALA |
lesson_day_content.day_no | Day (1–54) |
lesson_day_messages.send_order | Send_Order |
resource_type text / audio / image | Resource_type Text / Audio / Image |
text_message | Text_Message |
media_path lessons/1/day01/03.ogg | File_Key em_1_3_Audio.ogg (file copied in MinIO, list in em_media_import) |
meta_media_id (empty) | Media_ID + Media_Expiry_Date (filled on first send, reused 29 days) |
| — | Lesson_Type = Grammar |
Open a table to see its columns, what it connects to, and which part of the code writes and reads it. Core the lesson system needs it · Support used by screens or app internals · EP-only came with EP's code and is unused on EM. Row counts are today's; most tables are empty because no students, trainers or numbers are set up yet.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Course id (primary key) |
createdAt | timestamptz | When created |
updatedAt | timestamptz | Last change |
createdById | bigint | NocoBase user (EP leftover) |
updatedById | bigint | NocoBase user (EP leftover) |
Course_Name | varchar | Course name, e.g. 'EM Trainer Basic' |
Course_Price | numeric | Price (empty for EM) |
Region | varchar | Region, 'KERALA' |
Read by: em-engine/admin.js (course list with day count), routes/trainer-dashboard.js, routes/trainer-app.js, routes/lesson-resources.js, routes/messages.js, routes/student-journey.js, routes/bda-app.js, routes/dashboard.js, routes/finished-combined.js, routes/silent-students.js, routes/speaking-mark.js, routes/call-report.js, routes/bulk-message.js, routes/rejoin.js … and 3 more
Primary key: id. Only table in this group with data: 1 row (id 1, 'EM Trainer Basic', price empty, KERALA).
| Column | Type | What it stores |
|---|---|---|
id | bigint | Student id (primary key, auto number) |
createdAt | timestamptz | When the row was created |
updatedAt | timestamptz | When the row last changed |
createdById | bigint | NocoBase user who created it (EP leftover; EM leaves it empty) |
updatedById | bigint | NocoBase user who last changed it (EP leftover) |
Student_Name | varchar | Student's name |
Mobile_Number | varchar | Student's main phone number |
Class_Mobile_Number | varchar | Phone that gets the class messages on WhatsApp. EM enrolment sets it to Mobile_Number when none is given. The engine matches on its last 10 digits. |
Email_ID | varchar | Email (optional, EP sales field) |
Gender | varchar | Gender (EP sales field) |
Age | varchar | Age (EP sales field) |
District | varchar | District (EP sales field) |
Current_Location | varchar | Where the student lives now (EP sales field) |
Profession | varchar | Job (EP sales field) |
Where_did_you_find_English_Partner | varchar | Lead source (the name is from EP's brand English Partner) |
Reason_for_joining_course | text | Why the student joined |
Enquiry_Date | date | Date of first enquiry |
Batch_Date | date | Batch start date. EM enrolment sets it to the enrolment day. |
Admission_Number | varchar | Admission or receipt number (EP sales field) |
course_id | bigint | Which course the student is on. Points to Course_Details.id. EM uses course 1. |
counselor_id | bigint | The salesperson (BDA) who sold the course. Points to Counsellor_Details.id. EM enrolment leaves it empty. |
Read by: em-engine/lesson.js, em-engine/cron.js, em-engine/hooks.js, em-engine/admin.js, auth.js, middleware/verify-student-owner.js, middleware/verify-speaking-owner.js, routes/trainer-dashboard.js, routes/trainer-app.js, routes/messages.js, routes/student-journey.js, routes/silent-students.js, routes/bda-app.js, routes/dashboard.js … and 9 more
Primary key: id. No unique key on phone. Duplicate check is done in code (admin.js enrolOne): same last-10-digit class/main mobile on the same course_id is refused. Indexes cover Mobile_Number, Class_Mobile_Number (plain and normalised '91'+last 10 digits), Batch_Date, course_id, counselor_id. Has an UPDATE trigger trg_log_student_updates -> Sales_Update_Log. Currently empty (0 rows): no EM students enrolled yet. Many of the columns are EP sales-form fields that EM enrolment does not fill.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Progress id (primary key). Trainer links in t_ovaj35d9wum and t_71p261vuowi point to this id. |
createdAt | timestamptz | When the student was enrolled |
updatedAt | timestamptz | Last change (each send, tap or edit) |
createdById | bigint | NocoBase user (EP leftover) |
updatedById | bigint | NocoBase user (EP leftover) |
student_id | bigint | Which student. Points to Student_Details.id. |
trainer_id | bigint | Old single-trainer column from EP. EM never fills it (always empty); trainers come from the junction tables. A few EP screens still read it (owner check, MCP trainer counts), so those show nothing on EM. |
current_lesson_day | varchar | Lesson day number stored as text, e.g. '1'...'54'. Enrolment sets the start day (default 1). The daily job only picks rows where it is all digits. |
Progress_state | varchar | Where the student is today: Ready (gets today's template), Waiting_start (template sent, waiting for the Start tap), follow_up_sent (reminder sent, still no tap), Waiting_completed (tapped, lesson delivered; tomorrow moves to the next day). The dashboard "send-class" editor can also set Active, Completed, Dropped, Paused, Follow Up, On Track, Ahead, Behind or Stuck; any of those takes the student out of the engine's daily cycle until set back to Ready. |
Follow_up_count | integer | How many reminders have gone out for the current day (0-5). Reset to 0 on a new day or a tap. |
Next_Followup_At | date | Date the next reminder is due if there is still no tap. Cleared on tap or after the 5th reminder. |
is_stop | boolean | True = student stopped; the engine sends nothing. |
Next_Start_Date | date | Pause until this date. On that day the daily job clears it and sets the student back to Ready. |
Read by: em-engine/cron.js, em-engine/lesson.js, em-engine/hooks.js, em-engine/admin.js, middleware/verify-student-owner.js, routes/trainer-dashboard.js, routes/trainer-app.js, routes/messages.js, routes/student-journey.js, routes/silent-students.js, routes/student-trainers.js, routes/finished-combined.js, routes/bulk-message.js, routes/ai-analysis.js … and 5 more
Primary key: id. No unique constraint on student_id. Indexes: student_id, trainer_id, Progress_state, Next_Followup_At (partial, not null). A student's day, step by step (settings: em_send_time 08:00 IST, em_rest_days Sunday, em_lesson_type Grammar, em_course_id 1, engine enabled): (1) Enrolment (EM Setup) inserts Student_Details + this row with Progress_state='Ready', current_lesson_day=start day, and links trainers in t_ovaj35d9wum / t_71p261vuowi. (2) Every day at/after 08:00 IST (once per day, claimed via app_settings em_last_dispatch_date), unless Sunday or a holidays row with skip_grammar, cron.js: a) rows whose Next_Start_Date <= today and not stopped -> Next_Start_Date NULL, state Ready; b) rows in Waiting_start/follow_up_sent whose Next_Followup_At <= today -> the day template is sent again, state follow_up_sent, Follow_up_count+1, Next_Followup_At = today + 1/1/4/5/7 days for reminders 1-5; after the 5th, Next_Followup_At is cleared and reminders stop; c) rows in Ready or Waiting_completed -> Waiting_completed first moves current_lesson_day +1 (skipped if already at the last day = finished), the WhatsApp day template with a Start button is sent from the bot number, then state Waiting_start, Follow_up_count 0, Next_Followup_At tomorrow. Only students linked to at least one weekday trainer in t_ovaj35d9wum, not stopped and not paused are picked. (3) When the student taps Start (or sends any message, because em_start_on_any_message=true) on the bot number, webhook.js -> lesson.js handleStartTap matches the last 10 digits of Class_Mobile_Number/Mobile_Number, sets state Waiting_completed, clears Next_Followup_At and Follow_up_count, writes a Student_Day_logs row, then sends that day's Lesson_Resources in Send_Order plus the trainer footer (trainer names and numbers from the junction tables, with trainer_support_overrides applied) and, on the last day, the completion message. (4) The next morning the cycle repeats with day+1. Each state change also writes a Student_Day_logs row (waiting_start, follow_up_sent, waiting_completed). Currently empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Trainer id (primary key) |
createdAt | timestamptz | When added |
updatedAt | timestamptz | Last change |
createdById | bigint | NocoBase user (EP leftover) |
updatedById | bigint | NocoBase user (EP leftover) |
full_name | varchar | Trainer's name (shown to students in the lesson footer) |
Trainer_WA_Number | varchar | Trainer's own WhatsApp number. Students are told to contact this number; it is not the lesson-sending number (lessons go from the bot number in em_wa_numbers). |
Region | varchar | Region; EM always writes 'KERALA' |
Shift | varchar | Morning, Evening or Night. Enrolment picks one trainer per shift; the footer lists them in that order. |
Day | jsonb | List of weekdays. On EM it only decides Sunday-pool membership (contains "Sunday" = can be a Sunday trainer). EM Setup defaults to all 7 days. (In EP it meant the student cohort start day.) |
Lesson_Type | varchar | Course type; EM always writes 'Grammar' |
EMP_ID | varchar | Employee id (optional) |
Status | varchar | Active, or inactive/disabled/left/resigned/blocked. Inactive trainers are not picked for new students. |
Read by: em-engine/lesson.js (footer), em-engine/admin.js (auto-assign least-loaded trainer per shift), auth.js, middleware/verify-student-owner.js, routes/trainer-app.js, routes/trainer-dashboard.js, routes/trainer-auth-admin.js, routes/trainer-substitutes.js, routes/shift.js, routes/messages.js, routes/silent-students.js, routes/app-usage.js, routes/filters.js, routes/student-trainers.js … and 4 more
Primary key: id. No unique key on Trainer_WA_Number (index idx_trainers_info_wa only). Currently empty: no EM trainers added yet, so enrolment would fail with 'No active trainers to assign'.
| Column | Type | What it stores |
|---|---|---|
id | integer | Holiday id (primary key) |
holiday_date | date | The day off (unique) |
reason | text | Why, e.g. festival name |
skip_speaking | boolean | Skip EP Speaking sends (EP only) |
skip_grammar | boolean | Skip Grammar sends: the one EM checks |
skip_kannada | boolean | Skip EP Kannada course (EP only) |
skip_tamil | boolean | Skip EP Tamil course (EP only) |
skip_telugu | boolean | Skip EP Telugu course (EP only) |
created_at | timestamptz | When added |
Read by: em-engine/cron.js, routes/holidays.js, routes/student-journey.js, routes/finished-combined.js, routes/admin-mcp-tools.js, mcp-connector/tools.js, frontend/src/components/trainer-dashboard/buildCombinedReport.js
Primary key: id. Unique: holiday_date (one row per date). All skip flags default true. Empty. Sundays are skipped separately by the setting em_rest_days='Sunday', not by this table.
| Column | Type | What it stores |
|---|---|---|
createdAt | timestamptz | When the link was made |
updatedAt | timestamptz | Last change |
s_student_id | bigint | Student_Progress.id |
sunday_trainer_id | bigint | Trainers_info.id of a Sunday trainer |
Read by: em-engine/lesson.js (Sunday footer), em-engine/admin.js (Sunday student count), routes/trainer-app.js, routes/trainer-dashboard.js, routes/messages.js, routes/silent-students.js, routes/student-trainers.js, routes/shift.js, routes/app-usage.js, routes/trainer-substitutes.js, routes/rejoin.js, mcp-connector/tools.js
Primary key: (s_student_id, sunday_trainer_id). Extra index on sunday_trainer_id. No real FKs. Empty.
| Column | Type | What it stores |
|---|---|---|
createdAt | timestamptz | When the link was made |
updatedAt | timestamptz | Last change |
student_id1 | bigint | Student_Progress.id (despite the name, it is the progress row, not Student_Details) |
student_id2 | bigint | Trainers_info.id (despite the name, it is the trainer) |
Read by: em-engine/cron.js (LINKED guard), em-engine/lesson.js (weekday footer trainers, trainer_id for Student_Day_logs), em-engine/admin.js (student count per trainer), middleware/verify-student-owner.js (trainer may only see own students), routes/trainer-app.js, routes/trainer-dashboard.js, routes/trainer-head-app.js, routes/messages.js, routes/silent-students.js, routes/student-trainers.js, routes/shift.js, routes/app-usage.js, routes/trainer-substitutes.js, routes/bulk-message.js … and 1 more
Primary key: (student_id1, student_id2), so the same trainer cannot be linked twice. Extra index on student_id2. No real FKs. Empty.
| Column | Type | What it stores |
|---|---|---|
id | integer | Follow-up id (primary key) |
student_progress_id | integer | Student_Progress.id of the student |
trainer_id | integer | Trainers_info.id of the trainer who owns the task |
scheduled_date | date | Day to do it |
type | varchar | Kind of follow-up, default 'check_in' |
notes | text | Trainer's note when scheduling |
status | varchar | upcoming / done / other states (default 'upcoming') |
outcome | varchar | Result recorded when completed |
completed_at | timestamptz | When marked done |
completed_notes | text | Notes on completion |
created_at | timestamptz | When created |
updated_at | timestamptz | Last change |
Read by: routes/trainer-app.js
Primary key: id. Indexes on trainer_id, student_progress_id, scheduled_date. No FKs. Empty.
| Column | Type | What it stores |
|---|---|---|
id | integer | Override id (primary key) |
main_wa | text | WhatsApp number of the trainer being covered (matched by last 10 digits) |
main_name | text | Name of the trainer being covered |
override_name | text | Substitute trainer's name shown to students |
override_wa | text | Substitute's WhatsApp number shown to students |
start_date | date | First day the swap applies |
end_date | date | Last day the swap applies |
created_by | text | Dashboard user who set it up |
created_at | timestamptz | When created |
cancelled_at | timestamptz | When cancelled early (NULL = active) |
cancelled_by | text | Who cancelled it |
Read by: em-engine/lesson.js (buildFooter: active rows where cancelled_at IS NULL and today is between start and end), routes/trainer-substitutes.js, n8n-footer-wiring.js
Primary key: id. No unique constraint (overlapping swaps possible; the first match wins in lesson.js). Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Speaking progress id (primary key) |
createdAt | timestamptz | When created |
updatedAt | timestamptz | Last change |
createdById | bigint | NocoBase user (EP leftover) |
updatedById | bigint | NocoBase user (EP leftover) |
student_id | bigint | Student_Details.id |
current_lesson_day | varchar | Speaking lesson day |
Progress_state | varchar | Speaking state (same values as Student_Progress) |
Follow_up_count | integer | Reminders sent |
Next_Followup_At | date | Next reminder date |
is_stop | boolean | Stopped |
Next_Start_Date | date | Pause until |
Read by: routes/trainer-app.js, routes/trainer-dashboard.js, routes/messages.js, routes/student-journey.js, routes/silent-students.js, routes/student-trainers.js, routes/finished-combined.js, middleware/verify-speaking-owner.js, utils/stop-both.js, mcp-connector/tools.js, frontend/src/components/trainer-dashboard/buildCombinedReport.js
Primary key: id. Unique index uq_speaking_progress_student_id on student_id (one speaking row per student). Empty.
| Column | Type | What it stores |
|---|---|---|
createdAt | timestamptz | When the link was made |
updatedAt | timestamptz | Last change |
Student_id | bigint | Speaking_Student_Progress.id |
Trainer_id | bigint | Trainers_info.id (Sunday trainer) |
Read by: routes/rejoin.js, scripts/check-mastery-trainerless.js, scripts/unassign-mastery-trainers.js
Primary key: (Student_id, Trainer_id). Extra index on Trainer_id. Empty.
| Column | Type | What it stores |
|---|---|---|
createdAt | timestamptz | When the link was made |
updatedAt | timestamptz | Last change |
f_gqgr9qm4w4s | bigint | Speaking_Student_Progress.id |
f_21qx09ebh45 | bigint | Trainers_info.id |
Read by: routes/trainer-app.js, routes/speaking-mark.js, routes/messages.js, routes/shift.js, routes/app-usage.js, routes/trainer-substitutes.js, routes/student-trainers.js, routes/rejoin.js, middleware/verify-speaking-owner.js, mcp-connector/tools.js
Primary key: (f_gqgr9qm4w4s, f_21qx09ebh45). Extra index on f_21qx09ebh45. Its ids are Speaking progress ids, a separate number range from Student_Progress ids that can overlap numerically (rejoin.js warns about this). Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint (serial) | Row number of this lesson message. Also used in the chat media link /download/em/lesson/<id>. |
createdAt | timestamptz | When the row was created. |
updatedAt | timestamptz | When the row was last changed (also bumped when a new WhatsApp Media_ID is cached). |
createdById | bigint | NocoBase-era 'created by user' id; not used by EM (empty). |
updatedById | bigint | NocoBase-era 'updated by user' id; not used by EM. |
course_id | bigint | Which course this message belongs to (Course_Details.id). All 736 rows are course 1. |
Day | integer | Lesson day number (1 to 54 today). MAX(Day) per course is treated as the course's last day. |
Send_Order | integer | Position of this message within the day (1 = sent first). |
Lesson_Type | varchar | Kind of lesson: all EM rows are 'Grammar' (EP also had Speaking/Kannada/Tamil/Telugu999). |
Resource_type | varchar | Text, Audio, Image (Video/Document also supported). Today: 398 Text, 328 Audio, 10 Image. |
Text_Message | text | The words to send. For a Text row it is the message; for an Audio row it is sent as a separate text just before the audio; for Image/Video it becomes the caption. |
Drive_Link | text | EP-era Google Drive link to the source file; empty in EM (0 rows). |
File_Key | text | Name of the media file in MinIO storage (bucket emtrainers-media), e.g. em_1_3_Audio.ogg. 338 rows have one. |
Media_ID | text | WhatsApp's id for the uploaded file, cached so the file does not have to be uploaded again each send. Empty in all rows today (nothing uploaded to WhatsApp yet). |
Media_Expiry_Date | date | Date the cached Media_ID stops being trusted (set to today + 29 days on upload). After it the engine re-uploads from MinIO. |
Read by: em-engine/lesson.js (sendDayContent, maxDay), em-engine/admin.js (EM Setup status: row/day counts; course max day), em-engine/hooks.js, routes/messages.js (streams lesson media for chat view), routes/trainer-dashboard.js (max day / speaking start day / reports), routes/bulk-message.js (finished-course filter), routes/finished-combined.js, rejoin.js, speaking-mark.js, trainer-app.js, middleware/verify-student-owner.js, verify-speaking-owner.js, frontend/src/components/LessonResourcesPanel.jsx, TrainerSupportPanel.jsx, ChatWindow.jsx, trainer-dashboard/buildCombinedReport.js
PK: id. Index on course_id. No unique constraint (nothing stops two rows having the same Day+Send_Order). No real foreign keys. Data: course 1, Grammar, days 1-54, 54 distinct days. Lesson-day flow: when a student taps Start, lesson.js selects rows WHERE course_id = student's course AND Lesson_Type = app_settings.em_lesson_type AND Day = current_lesson_day ORDER BY Send_Order, id; for each media row it calls ensureMediaId (uses cached Media_ID if not expired, else downloads File_Key from MinIO, uploads to WhatsApp and UPDATEs Media_ID / Media_Expiry_Date = today+29), sends, and waits for WhatsApp's 'sent' before the next row.
| Column | Type | What it stores |
|---|---|---|
id | bigint (serial) | Row number. |
createdAt | timestamptz | When the lesson message was sent. |
updatedAt | timestamptz | When the latest status arrived. |
createdById | bigint | NocoBase leftover; unused. |
updatedById | bigint | NocoBase leftover; unused. |
WA_NUMBER | varchar | Student's WhatsApp number the lesson went to (with 91 prefix). |
BDA_WA_NUMBER | varchar | EP-era: the trainer/sender number. EM's engine never fills it, so the dashboard's per-trainer media stats would group everything under blank. |
Lesson_Type | varchar | Always 'Grammar' from the EM engine. |
Day | varchar | Lesson day the message belongs to (stored as text). |
course_id | bigint | Course of the student. |
Order | integer | Position of the message within that day's send (1, 2, 3...). |
Message_ID | varchar | WhatsApp's id for the sent message (wamid); used to match status updates. |
Media_Type | varchar | Resource_type of the lesson row: Text, Audio, Image... |
Message_Status | varchar | sent -> delivered -> read, or 'Failed'. |
Message_processed | boolean | Set true at insert by the EM engine (EP used it to chain the next send). |
Phone_Number_ID | varchar | Meta phone-number id of the bot number that sent it (em_wa_numbers.phone_number_id). |
Read by: routes/trainer-dashboard.js (media delivery stats by type/status and by trainer), scripts/drain-lessons.js, em-engine/lesson.js (indirectly; ack waiting uses Chat History status, not this table)
PK: id. Indexes on Message_ID, BDA_WA_NUMBER, and (WA_NUMBER, Lesson_Type, Day, course_id, Order). No FKs. Empty because no lesson has been sent yet. Lesson-day flow: after each successful send in sendDayContent, one row is inserted with Message_Status='sent'; Meta's status webhook later updates it to delivered/read/Failed.
| Column | Type | What it stores |
|---|---|---|
id | bigint (serial) | Row number. |
createdAt | timestamptz | When the event happened (used heavily by reports). |
updatedAt | timestamptz | Last change. |
createdById | bigint | NocoBase leftover; unused. |
updatedById | bigint | NocoBase leftover; unused. |
student_id | bigint | The student (Student_Details.id). |
trainer_id | bigint | The trainer linked to the student (Trainers_info.id). The engine fills the student's first weekday trainer; may be empty on some event types. |
lesson_day | varchar | Lesson day number, stored as text. |
event_type | varchar | What happened: waiting_start (day invite template sent), follow_up_sent (reminder sent), waiting_completed (student tapped Start, lesson delivered), completed (trainer sent/marked the class manually from the dashboard). |
Lesson_Type | varchar | Grammar for the engine; dashboard routes also write Speaking, Kannada, Tamil, Telugu999 (EP features). |
Read by: routes/trainer-dashboard.js (many reports, finish dates, day-1 started counts), routes/bulk-message.js (filter: students sent a given day), routes/finished-combined.js, revision-meet.js, ai-analysis.js, trainer-app.js, app-usage.js, mcp-connector/context.js, tools.js; mcp/tools/trainer.js, frontend/src/components/trainer-dashboard/buildCombinedReport.js
PK: id. Indexes: student_id; trainer_id; (student_id, event_type, createdAt desc); (createdAt desc, event_type). No FKs, no unique constraint (duplicates possible). Empty today. Gotcha: trainer-dashboard.js tries to insert class_type and notes columns, which do NOT exist in EM's table (code says it uses try/catch so the main action still works - those inserts would fail and fall back; not verified at runtime). Lesson-day flow: (1) 08:00 IST dispatch sends day template -> INSERT event 'waiting_start' with the new day; (2) no tap by Next_Followup_At -> reminder template -> INSERT 'follow_up_sent'; (3) student taps Start on the bot -> INSERT 'waiting_completed' then lesson content is sent.
| Column | Type | What it stores |
|---|---|---|
key | text | Setting name, e.g. em_send_time. |
value | text | Setting value (text). Secret values exist for some keys - (hidden). |
updated_at | timestamptz | When last changed. |
updated_by | text | Username of the admin who changed it (empty for seeded defaults). |
Read by: em-engine/store.js getSettings (cron, lesson, hooks), em-engine/admin.js, routes/trainer-dashboard.js (class_link_secret, telugu999_link_secret, class_link_start_date), routes/day4-intimation.js, revision-meet.js, mcp-connector/tools.js, frontend/src/components/ClassNotePanel.jsx (via API)
PK: key. Current 24 keys: em_completion_message, em_course_id=1, em_engine_enabled=true, em_footer_message, em_last_dispatch_date=2026-10-06, em_last_dispatch_result='no bot number registered', em_lesson_type=Grammar, em_rest_days=Sunday, em_send_time=08:00, em_start_on_any_message=true, em_template_lang=ml, em_template_name=em_day_test; plus class_note_message_tn/_telugu, day4_speaking_intimation_tn/_telugu, revision_meet1-3_basic/intermediate_tn, revision_prefix_reminder_tn/today_tn (EP-inherited message texts, Tamil/Telugu). em_last_dispatch_date acts as a once-per-day lock so a restart never sends twice.
| Column | Type | What it stores |
|---|---|---|
id | integer (serial) | Row number. If several active bots exist, the lowest id is used. |
label | text | Friendly name shown in Admin -> EM Setup. |
role | text | 'bot' = lesson sending number; 'trainer' = a trainer's WhatsApp Business (coexistence) number. |
wa_number | text | The phone number, digits only (e.g. 91XXXXXXXXXX). |
phone_number_id | text | Meta's id for that number; used to send and to recognise incoming webhooks. |
waba_id | text | Meta WhatsApp Business Account id that owns the number. |
access_token_enc | text | Meta access token for this number, (encrypted) with AES-256-GCM using EM_ENCRYPTION_KEY. |
active | boolean | Off = ignored for sending and for incoming webhooks. |
created_at | timestamptz | When registered. |
updated_at | timestamptz | Last edit. |
Read by: em-engine/store.js getBot (lesson sender), getNumberByPhoneNumberId (webhook), getNumberByWa, listNumbers, em-engine/cron.js, lesson.js, hooks.js, webhook.js (through store)
PK: id. UNIQUE wa_number, UNIQUE phone_number_id. CHECK role IN ('bot','trainer'). Lesson sending number = SELECT ... WHERE role='bot' AND active ORDER BY id LIMIT 1; currently none, which is why app_settings.em_last_dispatch_result says 'no bot number registered'. Tokens are never returned by the list API.
| Column | Type | What it stores |
|---|---|---|
id | bigint (serial) | Row number. |
phone | varchar | Recipient phone (normalised). |
message | text | Text sent, or the template name/content. |
mode | varchar | 'text' or 'template'. |
sent_by | varchar | Dashboard user who sent it. |
sent_at | timestamptz | When it was sent. |
Read by: routes/bulk-message.js (recipient lists, 'already sent' filter), frontend/src/components/chat/BulkMessageChat.jsx (via API)
PK: id. Indexes on phone and mode. No FKs. Empty. Bulk sends go through the engine's bulk-text / bulk-template hooks, i.e. from the bot number.
| Column | Type | What it stores |
|---|---|---|
file_key | text | The file name used in storage and in Lesson_Resources.File_Key, e.g. em_1_3_Audio.ogg. |
src | text | Where the file came from in the old EM lesson copy, e.g. lessons/1/day01/03.ogg (day folder / send order). |
copied | boolean (default false) | Meant to flag that the file was copied. It is false on all 338 rows, yet the files are present in MinIO, so the flag was simply never updated. |
Read by: Nothing in the code (grep finds no reference)
PK: file_key. Safe to treat as an audit trail of the media import; not needed at runtime. The 'copied' flag is stale (all f).
| Column | Type | What it stores |
|---|---|---|
lesson_id | bigint | Which lesson message this is about (Lesson_Resources.id). |
replaced_at | timestamptz | When an admin last uploaded a replacement file for this lesson row. |
synced_at | timestamptz | When the file was last uploaded to WhatsApp (Meta) and a fresh Media_ID cached. |
Read by: routes/lesson-resources.js (LEFT JOIN to compute Synced / Needs sync status)
PK: lesson_id (one row per lesson message). Created by code at startup (CREATE TABLE IF NOT EXISTS), also in backend/db/em-schema.sql. Empty today because no media has been uploaded to WhatsApp yet. Status rule in Lesson Resources: no File_Key = text; File_Key but no Media_ID = Needs sync; replaced after last sync = Needs sync; otherwise Synced. Today all 338 media rows show Needs sync.
| Column | Type | What it stores |
|---|---|---|
id | bigint (serial) | Row number. |
createdAt | timestamptz | When the rating was given. |
updatedAt | timestamptz | Last change. |
createdById | bigint | NocoBase leftover; unused. |
updatedById | bigint | NocoBase leftover; unused. |
student_id | bigint | Student who rated (Student_Details.id). |
Rating | integer | Star score. |
Class_Attended_on | varchar | Date of the class being rated (text). |
What_did_you_learn_today | text | Student's free-text answer. |
Lesson_Type | varchar | Grammar or Speaking. |
Day | varchar | Lesson day rated. |
Read by: routes/trainer-dashboard.js (trainer rating summaries and reports)
PK: id. No other indexes or constraints. Empty. Rating screens in the dashboard will show nothing for EM until a rating collection flow is built.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number (auto). |
created_at | timestamp with time zone | When the row was inserted (default now()). |
updated_at | timestamp with time zone | Last change, e.g. when the delivery status or media link was updated. |
Phone_Number | text | Our own WhatsApp number (the bot number) this conversation belongs to. The engine always fills it with the registered bot wa_number. |
From_Phone_Number | text | Who sent the message: the student's number for incoming rows, the bot's number for outgoing rows. For incoming rows this is the 'contact' in the chat screen. |
To_Phone_Number | text | Who received it: the student's number (with 91 prefix) for outgoing lesson rows, the bot number for incoming rows. |
Text | text | Message text or caption. Templates are stored as 'Template: <template name>'. Button taps store the button text (e.g. Start). |
Message_Type | text | Kind of message, capitalised: Text, Template, Audio, Image, Video, Document, Button, Interactive, Reaction, Sticker, etc. |
Type | text | Direction: 'messages' = incoming from the student, 'smb_message_echoes' = outgoing from us (names inherited from Meta/EP). |
Timestamp | timestamp with time zone | When the message was sent/received (Meta's timestamp for incoming; now() for outgoing). All screens sort/filter on this. |
Message_Status | text | Delivery status: accepted (just sent) -> sent -> delivered -> read, or failed; 'received' for incoming. Updated by Meta status webhooks. |
Media_ID | text | Meta's media id for audio/image/video/document messages. |
Message_ID | text | Meta's unique message id (wamid). Used to stop duplicates and to match status updates. |
Payload | text | Raw JSON of the webhook / send payload, kept for debugging. |
Media | text | JSON attachment list pointing to the stored copy of the media in MinIO (path download/em/...), so the chat screen can play it. |
Phone_Number_ID | text | Meta's Phone Number ID of our bot number (links to em_wa_numbers.phone_number_id). |
Progress | text | Legacy EP field mapped as 'progress' in projects.config; not written by the EM engine (not verified any EM code fills it). |
Read by: routes/messages.js + frontend/src chat view (EM Lessons Bot chat section), routes/read-state.js (unread counts), routes/nocodb-integration.js (API delivery stats), routes/trainer-dashboard.js (Start-tap attendance, API report), routes/student-journey.js, routes/silent-students.js, routes/rejoin.js, routes/revision-meet.js, routes/finished-combined.js, routes/trainer-app.js, em-engine/chat.js (messageExists/getStatus for dedup and waitForAck), n8n/999-link/monitor-*-link.js and scripts/* (EP 999/Tamil scripts, EP-only)
PK: id. No unique constraint; duplicates are prevented in code by checking Message_ID first (messageExists). Indexes on Message_ID (twice: idx_chat_message_id and idx_p4boly0zb0p8fp1_msgid), (Phone_Number_ID, Message_ID, To_Phone_Number), From_Phone_Number(+Timestamp), To_Phone_Number+Timestamp, Message_Type+Timestamp, Phone_Number+Timestamp, Timestamp. Empty today because no lesson bot number is registered yet: public.em_wa_numbers has 0 rows, projects.config.json 'ep_trainer_api_tn' has singleWaNumber='' and app_settings em_last_dispatch_result = '2026-10-06 ... no bot number registered'. So the LESSON SENDING NUMBER is whatever number is added in Admin -> EM Setup with role 'bot' (first active bot row); none is set now. 918838827955 appears only in an EP code comment (EP Grammar bot), not EM. Student's day: (1) daily dispatch at em_send_time 08:00 IST (rest day Sunday) sends template em_day_test -> one outgoing row Message_Type='Template', Text='Template: em_day_test', status 'accepted'; (2) Meta status webhooks update Message_Status to sent/delivered/read on the same row; (3) student taps Start -> incoming row Type='messages', Message_Type='Button'/'Interactive' (em_start_on_any_message=true so any message counts) and Student_Progress moves to Waiting_completed; (4) engine sends every Lesson_Resources row for that day in Send_Order: one outgoing row per text/media (audio with text gets two rows), waiting for each to be acknowledged before the next; plus a footer row with trainer names and, on the last day, the completion message row; (5) inbound media is copied to MinIO and the Media column filled.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number (auto). |
created_at | timestamp with time zone | When inserted. |
updated_at | timestamp with time zone | Last change (status/media update). |
Phone_Number | text | The trainer's WhatsApp number this chat belongs to. |
From_Phone_Number | text | Sender: student for incoming, trainer for outgoing. |
To_Phone_Number | text | Receiver: student for outgoing, trainer for incoming. |
Text | text | Message text or caption. |
Message_Type | text | Text, Audio, Image, Video, Document, Template, Reaction, etc. |
Type | text | 'messages' = incoming from student, 'smb_message_echoes' = sent by the trainer. |
Timestamp | timestamp with time zone | Message time; main sort/filter column. |
Message_Status | text | accepted/sent/delivered/read/failed or received. |
Media_ID | text | Meta media id. |
Message_ID | text | Meta message id (dedup + status matching). |
Payload | text | Raw webhook JSON. |
Media | text | JSON pointer to the MinIO copy of the media. |
Phone_Number_ID | text | Meta Phone Number ID of the trainer number. |
Progress | text | Legacy EP field; not filled by EM engine (not verified). |
User | text | Legacy EP sales-chat column: the contact number. Written only by the fallback path in routes/messages.js. |
Output_Message__Bot_ | text | Legacy EP sales-chat column: the text sent from the dashboard (fallback path). |
Output_Message_Timing | timestamp with time zone | Legacy EP sales-chat column: when that dashboard message was sent. |
Read by: routes/messages.js + frontend/src/components/chat (EM Trainers chat), routes/read-state.js, routes/trainer-dashboard.js, routes/student-trainers.js, routes/student-journey.js, routes/silent-students.js, routes/sales-dashboard.js (EP sales, ep_only use), routes/bulk-message.js, routes/filters.js, routes/nocodb-integration.js, mcp/tools/messages.js, mcp-connector/*
PK: id; no unique constraint (dedup in code via Message_ID). Many indexes incl. trigram GIN on Text (search), Phone_Number+From_Phone_Number+Timestamp (thread view), partial indexes for echoes/messages, Message_ID. Empty today: no trainer number registered in em_wa_numbers. Possible duplicate (not verified): for ep_trainer_bot (no singleWaNumber) a dashboard send writes a legacy row in routes/messages.js AND the engine reply hook logs a proper outgoing row.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. |
username | character varying | Dashboard login name (e.g. admin). |
project_id | character varying | Chat section: ep_trainer_api_tn (Lessons Bot) or ep_trainer_bot (Trainers). |
wa_number | character varying | Our WhatsApp number of that chat. |
contact_number | character varying | Student/contact number. |
last_read_at | timestamp with time zone | When the user last opened the chat; incoming messages after this count as unread. |
manually_unread | boolean | True if the user clicked 'mark as unread'. |
updated_at | timestamp with time zone | Last change. |
Read by: routes/read-state.js (/api/unread-counts), frontend/src/App.jsx, frontend/src/components/chat/ChatMode.jsx (via API)
PK: id. UNIQUE (username, project_id, wa_number, contact_number) - upserts use ON CONFLICT on it. Index (username, project_id, wa_number). Current 2 rows are test data: admin, ep_trainer_api_tn, 919000000001 with contacts 919000000011/12 (2026-10-06).
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. |
created_at | timestamp with time zone | Inserted at. |
updated_at | timestamp with time zone | Updated at. |
Phone_Number | text | Trainer's WhatsApp number (whose address book). |
Contact_Number | text | Contact's number. |
Contact_Name | text | Display name for that contact. |
Read by: routes/filters.js (contact list/name lookup), mcp/tools/contacts.js
PK: id; index on Phone_Number. No unique constraint, so the same contact could be saved twice.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. |
session_id | uuid | Unique id of the assistant conversation. |
username | text | Dashboard user. |
project_id | text | Chat section the user was in. |
messages | jsonb | Full list of assistant conversation messages. |
tool_calls_count | integer | How many data lookups the assistant made. |
started_at | timestamp with time zone | Session start. |
last_active_at | timestamp with time zone | Last message time. |
is_active | boolean | False after the user clears the session. |
metadata | jsonb | Extra info (free-form). |
Read by: routes/forge-agent.js (/api/agent/session), frontend/src/components/forge-agent/ForgeAgentWidget.jsx (via API)
PK: id. UNIQUE session_id (plus a redundant index idx_agent_sessions_id). Index (username, last_active_at DESC). Uses OpenAI (paid) when used.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. |
analysis_id | integer | Which AI analysis is rated (ai_chat_analyses.id). |
project_id | text | Chat section. |
rating | character varying | thumbs_up or thumbs_down. |
categories | jsonb | Problem tags chosen (list). |
comment | text | Free-text comment. |
submitted_by | text | User who rated. |
submitted_at | timestamp with time zone | When rated. |
updated_at | timestamp with time zone | Last change. |
Read by: routes/ai-analysis.js (feedback stats/list), frontend/src/components/AIChatAnalysis.jsx (via API)
PK: id. FK analysis_id -> ai_chat_analyses(id) ON DELETE CASCADE. CHECK rating IN ('thumbs_up','thumbs_down'). UNIQUE index (analysis_id, submitted_by) = one rating per user per analysis. Index (project_id, submitted_at DESC).
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. |
project_id | text | Chat section analysed. |
wa_number | text | Our number of the conversation. |
contact_number | text | Student/contact of the conversation. |
raw_text | text | The AI's answer as text. |
parsed | jsonb | The AI answer split into structured fields. |
message_count | integer | How many messages were analysed. |
audio_transcribed | integer | How many voice notes were transcribed. |
model | text | OpenAI model used. |
analyzed_at | timestamp with time zone | When run. |
analyzed_by | text | Dashboard user who ran it. |
prompt_tokens | integer | Input tokens (cost). |
completion_tokens | integer | Output tokens (cost). |
total_tokens | integer | Total tokens. |
images_analyzed | integer | Images described. |
source | character varying | 'ai_analysis' or the Forge Agent source label. |
audio_duration_seconds | numeric | Seconds of audio transcribed (cost). |
prompt_version | character varying | Short version tag of the prompt used. |
prompt_text | text | Full prompt used. |
Read by: routes/ai-analysis.js (/api/ai/history), routes/ai-usage.js, frontend/src/components/AIChatAnalysis.jsx, AIUsageDashboard.jsx (via API)
PK: id. Indexes (project_id, wa_number, contact_number, analyzed_at DESC) and (source, project_id, analyzed_at DESC). Calls OpenAI (paid) when used.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. |
created_at | timestamp with time zone | Inserted at. |
updated_at | timestamp with time zone | Updated at. |
Phone_Number | text | Our WhatsApp number. |
Contact_Number | text | The contact's number. |
Contact_Name | text | Name saved for that contact. |
Read by: none found
PK: id. No other indexes. Created only so the schema matches EP.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. Primary key. |
nocobase_user_id | integer | Link to users.id (name is inherited from EP's NocoBase). Optional for new users created with a local password. |
username | character varying | The login name typed on the sign-in page. Unique. |
display_name | character varying | Name shown in the dashboard (e.g. 'EM Admin'). |
role | character varying | What the person is: admin, viewer, content_viewer, or rejoin_agent (CHECK constraint). Default viewer. |
scope | character varying | 'all' = sees every trainer; 'restricted' = only the regions/teams/projects listed below (CHECK constraint). |
allowed_regions | ARRAY (text[]) | Regions the person may see; matched against Trainers_info."Region". |
allowed_teams | ARRAY (text[]) | Teams the person may see; in code teams map to trainer shifts (Trainers_info."Shift"). |
allowed_projects | ARRAY (text[]) | Projects/course groups the person may see (checked by checkProjectAccess). |
created_at | timestamp with time zone | When the login was created. |
updated_at | timestamp with time zone | When permissions were last changed. |
demo_mode | boolean | If true, the person sees masked/demo data. Always false for admins. |
read_only | boolean | If true, the person can look but cannot send lessons or change anything. |
password_hash | text | (hash) The person's password, stored scrambled. Never shown. |
Read by: auth.js POST /api/auth/login — finds the person and checks the password, auth.js GET /api/auth/me — reloads demo_mode/read_only/role/scope each request, auth.js GET /api/auth/users — admin user list, frontend/src/components/AdminPanel.jsx (via the /api/auth/users endpoints)
PK id; UNIQUE username; FK nocobase_user_id -> users(id) ON DELETE CASCADE; CHECK on role and scope. Current row: id 1, 'admin', role admin, scope all, has a password hash. Cryptic name: inherited from EP where it meant 'who may view the WhatsApp dashboard'. Every dashboard sign-in depends on it, hence core.
| Column | Type | What it stores |
|---|---|---|
actor | text | Who: a dashboard username (e.g. 'admin') or a trainer's phone number for the mobile app. |
app | text | Which app: 'web' (dashboard), 'trainer_app', or BDA app. |
day | date | Calendar day in India time (IST). Note: a row dated 2026-10-15 exists because IST is ahead of UTC. |
actor_role | text | Role at the time (admin, viewer...). |
requests | integer | Count of tracked API calls that day. |
actions | integer | Count of meaningful actions (matched in app-usage-actions.js). |
sends | integer | Count of send actions (lessons, classes, messages). |
failures | integer | Count of actions that failed (HTTP error). |
sessions | integer | Count of logins / token refreshes. |
first_seen_at | timestamp with time zone | Earliest activity that day. |
last_seen_at | timestamp with time zone | Latest activity that day. |
Read by: routes/app-usage.js GET /api/admin/app-usage (frontend/src/components/AppUsagePanel.jsx in AdminPanel), routes/trainer-app.js warmTrainerChatMaps() — trainers active in last 14 days
PK (actor, app, day). Indexes (day DESC, app) and (actor, day DESC). Tracking must never affect a request: writes are buffered in memory and swallowed on error. Not part of lesson sending.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Event number. Primary key. |
occurred_at | timestamp with time zone | Exact time of the action. |
day | date | IST calendar day of the action. |
app | text | web / trainer_app / BDA app. |
actor | text | Who did it (username or trainer phone). |
actor_role | text | Their role. |
trainer_id | integer | Trainers_info id when the actor is a trainer. |
action | text | Short action name, e.g. trainer_login, students_export, followup_report_view. |
category | text | Group of action: auth, report, send, etc. |
route | text | API path that was called. |
method | text | GET/POST/... |
course_type | text | Course involved, when known. |
target_type | text | What kind of thing was acted on (student, trainer...). |
target_id | text | Id of that thing. |
target_phone | text | Phone of the student/trainer acted on, when known. |
target_count | integer | How many targets (e.g. bulk send to N students). Default 1. |
status_code | smallint | HTTP result code (200 ok, 403 forbidden...). |
ok | boolean | Whether it succeeded. |
duration_ms | integer | How long the request took. |
client | text | Client type (browser / app). |
client_version | text | App version, when sent. |
source | text | 'live' (real-time) or backfill. |
dedupe_key | text | Used only by the backfill script so the same log line is not inserted twice. |
meta | jsonb | Extra details. |
Read by: routes/app-usage.js GET /api/admin/app-usage and /api/admin/app-usage/events (AppUsagePanel.jsx), mcp-connector/tools.js list_app_usage_events (MCP tool, admin_ops capability)
PK id; partial UNIQUE index on dedupe_key (when not null); indexes on (day,app), (day,action), (actor,day), occurred_at, and failed rows. Samples: two admin report views that returned 403 and one trainer_login by a test number 919000000201.
| Column | Type | What it stores |
|---|---|---|
key | text | Name of the fact. Primary key. Only row: tracking_started_at. |
value | text | The fact's value (here 2026-10-06 10:03:52 UTC). |
updated_at | timestamp with time zone | When it was written. |
Read by: app-usage.js getTrackingSince() used by routes/app-usage.js and scripts/backfill-app-usage-from-logs.js
PK key. No foreign keys.
| Column | Type | What it stores |
|---|---|---|
id | integer | Key number. Primary key. |
label | text | Admin's name for the key (who/what it is for). |
key_prefix | text | First few characters of the key, shown so it can be recognised. |
key_suffix | text | Last few characters of the key, shown so it can be recognised. |
key_hash | text | (hash) Scrambled full key, used to check incoming requests. Unique. |
enabled | boolean | Whether the key currently works. |
created_at | timestamp with time zone | When the key was issued. |
created_by | text | Admin username who issued it. |
last_used_at | timestamp with time zone | Last time the key was used. |
Read by: mcp-connector/state.js — key check for routes/mcp-http.js, key list for the admin panel
PK id; UNIQUE key_hash. 0 rows.
| Column | Type | What it stores |
|---|---|---|
id | integer | Report number. Primary key. |
trainer_wa | text | Trainer's WhatsApp number (who made the call). |
trainer_id | integer | Trainers_info id, for reference. |
student_phone | text | Student called. Readers compare it after removing non-digits. |
student_name | text | Student's name as entered. |
course | text | Course of the student. |
image_url | text | Call screenshot link (file in /app/uploads/reports). |
duration_text | text | Call length as typed by the trainer (free text). |
report_date | date | IST day of the call. |
created_at | timestamp with time zone | When uploaded. |
Read by: routes/call-report.js GET /api/trainer/report/today and GET /api/trainer-dashboard/call-reports (ActivityTab.jsx), routes/student-journey.js — calls per day on the student timeline, routes/student-trainers.js, routes/silent-students.js — a student with a logged call is not counted as silent
PK id; index (trainer_wa, report_date). Many rows per trainer per day are allowed. 0 rows. Not part of lesson sending.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row number. Primary key. |
trainer_wa | text | Trainer's WhatsApp number. This, not trainer_id, identifies the trainer. |
trainer_id | integer | First matching Trainers_info id, kept for reference only. |
shift_date | date | The IST working day. |
start_url | text | Old start-of-shift screenshot link. Kept but unused. |
start_at | timestamp with time zone | Old start time. Unused. |
end_url | text | Old end-of-shift screenshot link. Unused. |
end_at | timestamp with time zone | Old end time. Unused. |
login_url | text | LOGIN screenshot link (file in /app/uploads/shift). |
login_at | timestamp with time zone | When LOGIN was uploaded. |
break_url | text | BREAK screenshot link. |
break_at | timestamp with time zone | When BREAK was uploaded. |
logout_url | text | LOGOUT screenshot link. |
logout_at | timestamp with time zone | When LOGOUT was uploaded. |
created_at | timestamp with time zone | Row created. |
updated_at | timestamp with time zone | Row last changed. |
Read by: routes/shift.js GET /api/trainer/shift/today (mobile app), routes/shift.js GET /api/trainer-dashboard/trainer-activity (frontend/src/components/trainer-dashboard/ActivityTab.jsx)
PK id; UNIQUE (trainer_wa, shift_date). '_gs' suffix is inherited from EP: the GS backend keyed by WA number, the O3 backend had its own table. 0 rows.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Account number (auto-numbered). Primary key. |
createdAt | timestamp with time zone | When the account row was created (NocoBase-style camelCase name). |
updatedAt | timestamp with time zone | When the account row was last changed. |
nickname | character varying | Display name. The bootstrap admin gets the same value as username ('admin'). |
email | character varying | Email. Unique. The bootstrap sets it to the username, not a real email. Login can also match on this email. |
username | character varying | Login name. Unique. |
Read by: auth.js POST /api/auth/login — email fallback (JOIN users on wa_viewer_access.nocobase_user_id), auth.js GET /api/auth/nocobase-users — lists accounts not yet linked to a login (AdminPanel.jsx 'add user' picker)
PK id; UNIQUE email; UNIQUE username. Sample: id 1, nickname/username 'admin'. Holds no password — passwords live in wa_viewer_access.password_hash. Nothing here relates to lessons or the lesson-sending WhatsApp number.
| Column | Type | What it stores |
|---|---|---|
key | character varying | Name of the setting. Primary key. Rows: response_time_thresholds, duration_thresholds, shift_hours. |
value | jsonb | The setting's value, e.g. {green:5, yellow:15, orange:30} minutes for reply time; {green:30, yellow:15} for call duration; {morning:[8,16], evening:[16,24], night:[0,8]} hours. |
updated_at | timestamp with time zone | When the setting was last changed (all three seeded 2026-10-06). |
Read by: routes/trainer-dashboard.js GET /api/trainer-dashboard/settings (no caller found in frontend/src — not verified whether any app calls it)
PK key. No foreign keys. Values are defaults only; nobody has edited them. Classified ep_only because no EM screen reads it (not verified for external clients).
| Column | Type | What it stores |
|---|---|---|
id | bigint | Counsellor id (primary key) |
createdAt | timestamp with time zone | Created time |
updatedAt | timestamp with time zone | Last changed time |
createdById | bigint | NocoBase creator (unused) |
updatedById | bigint | NocoBase last editor (unused) |
Name | character varying | Counsellor's name |
EMP_ID | character varying | Employee id |
Region | character varying | Sales region |
Team | character varying | Sales team |
Part_of | character varying | Parent group or department (not verified) |
Status | character varying | Active or inactive status |
Read by: auth.js, backfill-speaking.js, routes/filters.js, routes/dashboard.js, routes/trainer-dashboard.js, routes/bda-app.js, routes/app-usage.js, routes/ai-analysis.js, routes/sales-dashboard.js, mcp/scope.js, mcp/tools/bda.js, frontend/src/components/AppUsagePanel.jsx
PK id. No unique constraints. Empty in emtcc, so any counsellor name comes back blank.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Row id (primary key) |
createdAt | timestamp with time zone | Created time |
updatedAt | timestamp with time zone | Last changed time |
Phone_number | character varying | A sales/BDA WhatsApp number. The code strips non-digits before using it. |
Read by: routes/trainer-dashboard.js (DRAWER_SALES_NUMBERS_SQL)
PK id. Only Phone_number is read. The schema comment says it may be numeric in EP production (not verified).
| Column | Type | What it stores |
|---|---|---|
id | bigint | Row id (primary key) |
createdAt | timestamp with time zone | Created time |
updatedAt | timestamp with time zone | Last change |
Student_Name | character varying | Buyer's name |
Mobile_Number | character varying | Buyer's phone (matched to Student_Details by digits) |
Course_Name | character varying | Course bought |
Employee_Name | character varying | Counsellor who made the sale |
Employee_ID | character varying | That counsellor's employee id |
Region | character varying | Sales region |
Transaction_Date | date | Date of sale |
Read by: routes/rejoin.js (around line 302)
PK id. Many frontend files match only on the word 'Sales' in page names. Only rejoin.js reads this table. Empty in emtcc.
| Column | Type | What it stores |
|---|---|---|
id | integer | Row id (primary key) |
table_name | text | Table that was edited (normally Transaction_details) |
record_id | integer | id of the edited row |
counsellor_id | integer | Counsellor whose record it is |
column_name | text | Which field changed |
old_value | text | Value before |
new_value | text | Value after |
updated_at | timestamp with time zone | When it changed |
Read by: routes/dashboard.js (sales updates endpoints), frontend/src/components/SalesUpdateDashboard.jsx / sales-dashboard/SalesUpdatesPage.jsx (via API)
PK id. Indexes on (updated_at DESC) and (counsellor_id, updated_at DESC). Empty in emtcc.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Primary key |
student_detail_id | bigint | Student_Details.id |
state | text | 'stopped' or other candidate state |
stopped_by | text | 'auto' or 'manual' |
day1_date | date | Student's Day 1 date |
created_at | timestamp with time zone | Insert time |
Read by: routes/trainer-dashboard.js (~line 11582 LEFT JOIN)
PK id. Index idx_aasc_student (student_detail_id, state). Read-only from TCC. Empty.
| Column | Type | What it stores |
|---|---|---|
phone10 | text | Student's last-10-digit phone (primary key) |
stage | text | Payment stage |
settled | boolean | true = fully paid |
received | numeric | Amount received so far |
course_price | numeric | Course price |
team | text | Sales team |
counsellor | text | Counsellor name |
updated_at | timestamp with time zone | Last refresh |
Read by: routes/trainer-dashboard.js (~2951, ~3980, ~11584), routes/trainer-app.js (~530), frontend/src/components/chat/ChatMode.jsx (via API)
PK phone10. In EP it may be a view; here it is recreated as a table. The code tolerates it being missing. Empty, so no Paid badges show on EM.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Primary key |
student_id | bigint | Student_Details.id (unique, one row per student) |
progress_id | bigint | Student_Progress row created or reset |
created_sd | boolean | true = a new Student_Details row was created |
added_by | character varying | Admin who did it |
added_at | timestamp with time zone | When |
action | character varying | Which rejoin action ran (reset / enrol ...) |
prev_day | character varying | Lesson day before the reset |
Read by: routes/rejoin.js (/api/admin/rejoin/added), frontend/src/components/RejoinPanel.jsx (via API)
PK id. UNIQUE (student_id), so a re-rejoin overwrites the row. Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Progress row id (primary key) |
createdAt | timestamp with time zone | Enrolment time |
updatedAt | timestamp with time zone | Last change |
createdById | bigint | NocoBase creator (unused) |
updatedById | bigint | NocoBase editor (unused) |
student_id | bigint | Student_Details.id |
current_lesson_day | character varying | Lesson day the student is on (text number) |
Progress_state | character varying | State such as Ready / Waiting_start / Waiting_completed / follow_up_sent / Completed |
Follow_up_count | integer | How many reminders were sent |
Next_Followup_At | timestamp with time zone | When the next reminder is due |
is_stop | boolean | true = paused/stopped |
Next_Start_Date | date | Date to resume or start |
Read by: routes/trainer-dashboard.js, routes/messages.js, n8n/999-link/monitor-kannada-link.js
PK id. No unique constraint on student_id. On EP, the n8n schedule moved rows Ready -> Waiting_start -> Waiting_completed and advanced current_lesson_day each day. EM has no such sender, so these rows are never created on EM. Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Row id (primary key) |
createdAt | timestamp with time zone | When the row was created (NocoBase audit column) |
updatedAt | timestamp with time zone | When the row was last changed |
createdById | bigint | NocoBase user who created it (unused here) |
updatedById | bigint | NocoBase user who last changed it (unused here) |
BDA_WA_Number | character varying | WhatsApp number a sales counsellor (BDA) uses to chat with leads |
counsellor_id | bigint | The counsellor who owns this number (points to Counsellor_Details.id) |
Read by: auth.js, routes/filters.js, routes/dashboard.js, routes/sales-dashboard.js, routes/forge-agent.js, routes/ai-analysis.js, routes/app-usage.js, mcp/scope.js, mcp/tools/bda.js, demo-mask.js, frontend/src/components/FilterBar.jsx, frontend/src/components/AppUsagePanel.jsx
PK id. There is no unique constraint on BDA_WA_Number and no real FK. The table is empty in emtcc, so BDA/sales filters return nothing on EM. These are sales numbers, not lesson numbers.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Event id (primary key) |
createdAt | timestamp with time zone | When the stage was reached |
updatedAt | timestamp with time zone | Last change |
createdById | bigint | NocoBase creator (unused) |
updatedById | bigint | NocoBase editor (unused) |
WA_Number | character varying | Lead's WhatsApp number |
BDA_WA_Number | character varying | Sales number involved |
Stage | character varying | Stage name: FIRST_MESSAGE / VOUCHER_SENT / VOUCHER_REDEEMED / PURCHASED ... |
Read by: routes/sales-dashboard.js, routes/ai-analysis.js, routes/forge-agent.js, mcp/tools/leads.js, mcp/tools/funnel.js, mcp/tools/bda.js
PK id. Append-only log. Empty in emtcc.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Lead id (primary key) |
createdAt | timestamp with time zone | When the lead first came in |
updatedAt | timestamp with time zone | Last change |
createdById | bigint | NocoBase creator (unused) |
updatedById | bigint | NocoBase editor (unused) |
Name | character varying | Lead's name |
WA_Number | character varying | Lead's WhatsApp number |
BDA_WA_Number | character varying | Sales number chatting with the lead |
Current_Stage | character varying | Current funnel stage (FIRST_MESSAGE, VOUCHER_SENT, PURCHASED, ...) |
Source | character varying | Where the lead came from (ad, organic, ...) |
City | character varying | City |
Age | character varying | Age as text |
level | character varying | English level (lower-case duplicate column) |
Level | character varying | English level (capitalised duplicate column, inherited quirk) |
Engagement | character varying | Engagement rating |
Batch_Date | date | Planned batch start date |
Profession | character varying | Occupation |
Read by: routes/sales-dashboard.js, routes/ai-analysis.js, routes/forge-agent.js, mcp/scope.js, mcp/tools/funnel.js, mcp/tools/leads.js, mcp/tools/dashboard.js, mcp/tools/bda.js
PK id. There are two level columns ('level' and 'Level'). Empty in emtcc.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Primary key |
createdAt | timestamp with time zone | Enrolment time |
updatedAt | timestamp with time zone | Last change |
createdById | bigint | unused |
updatedById | bigint | unused |
student_id | bigint | Student_Details.id |
current_lesson_day | character varying | Current lesson day |
Progress_state | character varying | Lesson state |
Follow_up_count | integer | Reminders sent |
Next_Followup_At | timestamp with time zone | Next reminder due |
is_stop | boolean | Stopped flag |
Next_Start_Date | date | Resume date |
Read by: routes/trainer-dashboard.js, routes/messages.js, n8n/999-link/monitor-tamil-link.js
PK id only. Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Primary key |
createdAt | timestamp with time zone | Enrolment time |
updatedAt | timestamp with time zone | Last change |
createdById | bigint | unused |
updatedById | bigint | unused |
student_id | bigint | Student_Details.id |
current_lesson_day | character varying | Current lesson day |
Progress_state | character varying | Lesson state |
Follow_up_count | integer | Reminders sent |
Next_Followup_At | timestamp with time zone | Next reminder due |
is_stop | boolean | Stopped flag |
Next_Start_Date | date | Resume date |
Read by: routes/trainer-dashboard.js, routes/messages.js
PK id only. Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Payment row id (primary key) |
createdAt | timestamp with time zone | Created time |
updatedAt | timestamp with time zone | Last change |
createdById | bigint | NocoBase creator (unused) |
updatedById | bigint | NocoBase editor (unused) |
Payment_status | character varying | Full-Paid / Partial etc. (trimmed in queries) |
Mode_of_payment | character varying | UPI, card, bank transfer ... |
Received_Amount | numeric | Amount received (summed for revenue) |
Credited_Account | character varying | Which company account got the money |
Transaction_Date | date | Payment date |
Transaction_ID | character varying | Bank/UPI reference |
Media_URL | text | Link to the payment screenshot |
Student_Type | character varying | New / renewal type of buyer (not verified) |
Execution_ID | character varying | n8n execution that created the row |
BDA_WA_Number | character varying | Sales number that closed it |
student_id | bigint | Student_Details.id |
course_id | bigint | Course_Details.id |
counsellor_id | bigint | Counsellor_Details.id |
Read by: routes/dashboard.js, frontend/src/components/sales-dashboard/SalesRecentPage.jsx (via API)
PK id. Has the trigger trg_log_transaction_updates. Empty in emtcc.
| Column | Type | What it stores |
|---|---|---|
student_id | integer | Student_Details.id |
sent_date | date | IST date the link was sent |
created_at | timestamp with time zone | Insert time |
Read by: routes/trainer-dashboard.js
PK (student_id, sent_date). EM's engine does not use it. EM has no class-link flow ('unsupported' hook). Empty.
| Column | Type | What it stores |
|---|---|---|
phone | text | Student 10-digit phone (primary key) |
device_id | text | Locked device id |
first_day | integer | Day first opened |
first_seen | timestamp with time zone | First open |
last_seen | timestamp with time zone | Latest open |
Read by: routes/trainer-dashboard.js
PK phone. Empty.
| Column | Type | What it stores |
|---|---|---|
phone | text | 10-digit phone (primary key) |
created_at | timestamp with time zone | When added |
Read by: routes/trainer-dashboard.js (isClassLinkEnabled), DB function public.fn_is_link_student (inferred reconstruction in em-schema.sql)
PK phone. The fn_is_link_student body is an inferred rebuild, not EP's original. Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Primary key |
student_id | bigint | Student_Details.id |
lesson_type | text | Grammar or Speaking |
day | integer | Lesson day viewed |
viewed_at | timestamp with time zone | View time |
Read by: routes/trainer-dashboard.js (~lines 3713-3732)
PK id. Index idx_class_link_view_student (student_id, viewed_at DESC). Empty.
| Column | Type | What it stores |
|---|---|---|
id | bigint | Primary key |
wa_number | character varying | Student WhatsApp number |
meet | smallint | Which revision meet (1-4; 4 = finisher meet) |
phase | character varying | 'reminder' or 'meetlink' |
level | character varying | Student level group |
week_start | date | Week the send belongs to |
sent_at | timestamp with time zone | Send time |
Read by: routes/revision-meet.js (report/weeks/history), the same n8n crons (skip already-sent), frontend/src/components/RevisionReportPanel.jsx (via API)
PK id. UNIQUE idx_revsend_uniq (wa_number, meet, phase, week_start). EM has no n8n, so nothing sends these on EM. Empty.
| Column | Type | What it stores |
|---|---|---|
meet | smallint | Meet number (primary key) |
enabled | boolean | On or off |
updated_at | timestamp with time zone | When changed |
updated_by | character varying | Admin who changed it |
Read by: routes/revision-meet.js (GET toggles), EP n8n revision crons (not verified)
PK meet. Empty.
| Column | Type | What it stores |
|---|---|---|
progress_id | bigint | Speaking_Student_Progress.id that was marked |
mark_date | date | IST date of the mark |
student_id | bigint | Student_Details.id |
lesson_day | text | Lesson day at the time of marking |
trainer_wa | text | Trainer's WhatsApp number |
trainer_id | integer | Trainers_info.id |
owned | boolean | Whether the student is that trainer's own student |
advanced | boolean | true = the mark also advanced the day; false = recorded only |
created_at | timestamp with time zone | Insert time |
Read by: routes/speaking-mark.js (speaking-lookup)
PK (progress_id, mark_date), so at most one mark per student per day. When advanced, it also writes a Student_Day_logs speaking entry via utils/stop-both insertSpeakingDayLog. Whether EM uses speaking is not verified. Empty. Inactive on EM: EM has no Speaking course.
| Column | Type | What it stores |
|---|---|---|
phone | text | Student phone (primary key) |
note | text | Why it was added |
created_at | timestamp with time zone | When added |
Read by: routes/speaking-mark.js
PK phone. Empty. Inactive on EM: EM has no Speaking course.
| Column | Type | What it stores |
|---|---|---|
phone | text | Student's 10-digit phone (primary key) |
device_id | text | Id of the device that first opened the link |
first_day | integer | Lesson day on which it was first opened |
first_seen | timestamp with time zone | First open |
last_seen | timestamp with time zone | Most recent open |
Read by: routes/trainer-dashboard.js
PK phone. One device per phone. Empty.
| Issue | Effect |
|---|---|
| Replies typed in the "EM Trainers" chat section go out from the lesson-bot number | That section has no number of its own in the config, so the engine falls back to the bot. Trainers should reply from their own phones until this is fixed. |
Template name and language are test values (em_day_test, ml) | Must be replaced with the real approved template before the first send |
| Any reply from a waiting student starts the lesson | A plain "hi" releases the day's lesson (setting "any reply starts the lesson" is on) |
| A phone with two active enrolments | Matching is on the last 10 digits, so one tap releases both |
| Student linked only to Sunday trainers | Never sent anything (the daily job requires a weekday trainer) |
"Send Today's Class" logs a completed day event | Reports count template-only sends as manual class sends |
| Footer numbers | Shown as 10 digits without 91 |
| Start-tap phone lookup | Not covered by an index; fine now, should get one before thousands of students (server Rule #3) |