# Backend Phase 1 — Database Schema + Seed

> **Run this in Claude Code. Do NOT wire any screens or build endpoints yet.**
> Before running: fill in your Sasahost plan in step 3 (VPS or shared cPanel). That one fact decides the ORM.

---

## Instructions for Claude Code

**1. Detect local DB tooling.**
Run and report the output of:
```
docker --version
mysql --version
mariadb --version
```
Then recommend the simplest dev-DB option (prefer a Docker MySQL container if Docker exists, else tell me exactly what to install).

**2. Design the schema.**
Use the design brief in this project (`boda_delivery_design_brief.md`) as the source of truth for what each screen needs. If `web/src/data.js` exists, cross-check against it.

Design a **normalized MySQL schema** covering:

- `orders` — include: 4-digit delivery code, out-of-zone flag, manual-quote amount, vendor accept/reject status
- `order_events` — audit trail (status changes, who, when, free-text resolution notes)
- `riders`
- `vendors` — include credit tier + current balance
- `dispatchers`
- `bikes`
- `bike_daily_logs` — date, bike, rider, opening odometer, closing odometer, computed km, computed fuel cost
- `zones` — include polygon / geofence geometry, not just names
- `zone_pricing_matrix` — zone-pair base prices
- `settlements` / `payments`
- `sms_messages`
- `app_settings` — key/value config: fuel KES-per-km rate, service-alert thresholds, vendor credit tier thresholds, operating hours (daytime cutoff)
- `users` — for auth (role: super_admin / dispatcher / rider / vendor)

**3. ORM decision.**
I am on Sasahost: **[ VPS / shared cPanel — FILL THIS IN ]**

- If **VPS** → use **Prisma** (schema.prisma + `prisma migrate`).
- If **shared cPanel** → use **raw mysql2** with hand-written migration SQL in `api/migrations/`.

Use whichever I specified above. If I left it blank, stop and ask me before proceeding.

**4. Migrations + seed.**
Write the migrations, and a seed script in `api/seed` that loads the design brief's sample data so it matches what the screens expect (the 10 Nairobi zones, sample riders, sample vendors, sample orders across statuses).

**5. Environment.**
Add `.env.example` for the DB connection. Have `api/` read from `.env`. No real credentials.

---

## Stop after this. Report back:

1. DB tooling found on my machine
2. Recommended dev-DB setup
3. The full schema (all tables + columns + relationships)
4. The exact commands to: create the DB → run migrations → run seed