# Smart Clinic ERD / Schema Map

```text
organizations
  ├─ users
  │   ├─ doctors (1:1)
  │   ├─ assistants (1:1)
  │   ├─ patients (optional 1:1 account link)
  │   ├─ device_tokens
  │   └─ audit_logs (actor)
  ├─ locations
  ├─ settings
  ├─ doctor_location_assignments
  │   ├─ doctor_working_hours
  │   └─ doctor_schedule_exceptions
  ├─ assistant_assignments ── assistant + doctor + location + permission flags
  ├─ patients
  │   └─ appointments
  │       ├─ appointment_status_histories
  │       ├─ queue_entries
  │       │   └─ queue_status_histories
  │       ├─ transactions
  │       └─ notification_logs
  ├─ queue_counters
  └─ notification_preferences
```

## Critical keys and indexes

- `users`: unique `(organization_id, email)`.
- `patients`: unique `patient_code`; indexes for organization, normalized phone, name.
- `doctor_location_assignments`: unique `(doctor_id, location_id)`.
- `assistant_assignments`: unique `(assistant_id, doctor_id, location_id)`.
- `appointments`: indexes on doctor/location/start, patient/start and status/start.
- `queue_entries`: unique `(organization_id, queue_scope_key, queue_date, token_number)` and `(appointment_id, queue_date)`.
- `queue_counters`: unique `(organization_id, queue_scope_key, queue_date)`; row is locked during token allocation/call-next.
- `transactions`: unique idempotency key within organization where supplied, plus appointment/patient/location/status/date indexes.
- `notification_logs`: unique `idempotency_key`, plus appointment/channel/type/status/scheduled indexes.

## Queue scope

`queue_scope_key` represents either:

- `doctor:{doctor_id}:location:{location_id}` (default), or
- `location:{location_id}` when the organization's `token_scope` setting is `location`.

This keeps the database uniqueness rule compatible with both token policies without duplicating queue tables.