Database Overview

The second-opinion worker stores data in Cloudflare D1 (SQLite). Access pattern: raw SQL via src/db/*/repo.ts and sql.ts — no ORM.

Binding: DB → database second-opinion-dev (UAT)

Migrations: second-opinion/migrations/ (52 SQL files)

Entity relationship diagram

Links to sop_doctors

sop_doctors.user_id is the shared doctor identifier. Two columns reference it:

From table	Column	→ sop_doctors	Role
review_cases	case_coordinator_id	user_id	Assigned case coordinator (optional until admin assigns)
second_opinions	doctor_id	user_id	Assigned specialists (is_coordinator = 1 marks the coordinator’s row)

erDiagram
  review_types ||--o{ review_cases : "rt_id to review_type_id"
  disease_categories ||--o{ review_cases : "dc_id"
  review_cases ||--o{ second_opinions : "case_id"
  review_cases ||--o{ case_status_history : "case_id"
  review_cases ||--o{ sop_activity : "case_id"
  review_cases ||--o| sticky_header_cards : "key is case_id"
  sop_doctors ||--o{ review_cases : "coordinator_id to user_id"
  sop_doctors ||--o{ second_opinions : "doctor_id to user_id"
  review_cases {
    text case_id PK
    text user_id
    text rt_id FK
    text dc_id FK
    text status
    text case_coordinator_id FK
  }
  second_opinions {
    text sop_id PK
    text case_id FK
    text doctor_id FK
    int is_coordinator
  }
  review_types {
    text review_type_id PK
    text review_type
    int active
  }
  disease_categories {
    text dc_id PK
    text specialty_en
    text doctor_ids
  }
  sop_doctors {
    text user_id PK
    text doctor_name_en
  }
  case_coordinators {
    text cc_id PK
    text speciality_id
    text doctor_ids
  }
  case_status_history {
    text case_id PK
    text status PK
  }
  sticky_header_cards {
    text type PK
    text key PK
    text user_id
  }
  sop_activity {
    text case_id PK
    text updated_by PK
    text created_date PK
  }

Tables

Table	Description
review_cases	Central case entity
review_types	Review type catalog
disease_categories	Specialty/disease mapping
second_opinions	Doctor assignments per case
sop_doctors	Doctor profile cache
case_coordinators	Coordinator mapping by speciality
case_status_history	Status milestone timeline
sticky_header_cards	In-app notification banners
sop_activity	Admin audit log

Notes

• Foreign keys are logical — most relationships are not enforced with SQLite FOREIGN KEY constraints
• case_coordinators is a configuration table (coordinators eligible per speciality). It does not link directly to review_cases. On assignment, review_cases.case_coordinator_id is set to the coordinator’s sop_doctors.user_id (see table above)
• sticky_header_cards uses composite primary key (type, key) where key is the case_id and type is typically SOP
• JSON columns: medical_documents, doctor_ids, requested_documents, report
• Boolean flags stored as INTEGER 0 / 1
• Diagrams show core columns only — see each table’s page for the full column list