# Architecture

## Stack

| Layer | Technology |
|---|---|
| Framework | Next.js 15 (App Router) |
| Language | TypeScript |
| Database | PostgreSQL (via `pg` / node-postgres, no ORM) |
| Auth | Better Auth |
| Payments | Stripe |
| Hosting | Cloudflare Workers (via OpenNext) |

---

## Environment variables

| Variable | Description |
|---|---|
| `DATABASE_URL` | PostgreSQL connection string, e.g. `postgresql://postgres:postgres@127.0.0.1:5432/carntrack` |
| `BETTER_AUTH_SECRET` | Long random string used to sign sessions |
| `BETTER_AUTH_URL` | App base URL, e.g. `http://localhost:3000` |
| `STRIPE_SECRET_KEY` | Stripe secret key (`sk_test_...` for dev) |
| `STRIPE_WEBHOOK_SECRET` | Written automatically by `npm run setup:stripe-webhook` |

---

## Database

No ORM. All queries use `src/lib/db.ts` — a shared `pg.Pool` imported by API routes.

### Migrations

Files in `db/migrations/` run in filename order. The `_migrations` table tracks which have run.

```
db/
├── migrations/
│   ├── 001_initial_schema.sql      — container_shipment, shipment_document
│   └── 002_better_auth_schema.sql  — user, session, account, verification
├── seed/
│   ├── 001_sample_data.sql         — 6 sample containers + 3 documents
│   └── run.ts                      — seed runner (also creates admin user)
└── migrate.ts                      — migration runner
```

### Schema

#### `container_shipment`

Main cargo tracking table.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `order_number` | TEXT | |
| `po_number` | TEXT | |
| `container_number` | TEXT | UNIQUE |
| `vessel_name` | TEXT | |
| `voyage_number` | TEXT | |
| `carrier` | TEXT | |
| `port_of_origin` | TEXT | |
| `port_of_destination` | TEXT | |
| `etd` | DATE | Estimated departure |
| `eta` | DATE | Estimated arrival |
| `actual_departure` | DATE | |
| `actual_arrival` | DATE | |
| `status` | TEXT | `en_transito` · `en_puerto_destino` · `en_produccion` |
| `sale_office` | TEXT | |
| `product_type` | TEXT | |
| `weight_kg` | NUMERIC | |
| `temperature_c` | TEXT | |
| `seal_number` | TEXT | |
| `booking_number` | TEXT | |
| `bill_of_lading` | TEXT | |
| `dhl_tracking` | TEXT | |
| `vessel_tracking_url` | TEXT | |
| `notes` | TEXT | |
| `last_sync_date` | TIMESTAMPTZ | |
| `created_at` | TIMESTAMPTZ | |
| `updated_at` | TIMESTAMPTZ | |

#### `shipment_document`

Documents attached to a shipment.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `container_shipment_id` | UUID | FK → `container_shipment` |
| `document_name` | TEXT | |
| `document_type` | TEXT | `invoice` · `packing_list` · `picking_report` · `seguro` · `bill_of_lading` · `certificate_of_origin` · `health_certificate` |
| `file_url` | TEXT | |
| `file_name` | TEXT | |
| `file_size` | TEXT | |
| `uploaded_by` | TEXT | |
| `notified` | BOOLEAN | |
| `created_at` | TIMESTAMPTZ | |

#### Better Auth tables

`user`, `session`, `account`, `verification` — managed by Better Auth. Created by `002_better_auth_schema.sql`.

The `user` table has an extra `role` column: `matadero` (admin) or `cliente`.

---

## Auth & roles

Better Auth handles sign-in/sign-up at `/api/auth/[...all]`.

| Role | Access |
|---|---|
| `matadero` | Full access — sees all containers, can manage data |
| `cliente` | Restricted — sees only their assigned containers |

The seed creates a default admin: `admin@matadero.local` / `123456`.

**Known gap:** the register page currently allows self-selecting any role. New registrations should be hardcoded to `cliente`.

---

## API routes

| Route | Method | Description |
|---|---|---|
| `/api/containers` | GET | List containers (filters: `order`, `po`, `container`, `status`, `sale_office`) |
| `/api/containers/sync-status` | GET | Returns `MAX(last_sync_date)` from `container_shipment` |
| `/api/containers/export` | GET | Excel export of filtered containers |
| `/api/orders/[id]` | GET | Single shipment + its documents |
| `/api/alerts` | GET | Alerts (stub) |
| `/api/auth/[...all]` | ANY | Better Auth handler — do not touch |
| `/api/stripe/webhook` | POST | Stripe webhook receiver (handlers are stubs — not yet implemented) |
| `/api/stripe/create-checkout-session` | POST | |
| `/api/stripe/customer-portal` | POST | |
| `/api/stripe/products` | GET | |

---

## Known gaps / pending work

- Stripe webhook handlers are stubs (no DB writes yet)
- `cliente` role filtering on `/api/containers` is not implemented
- Register page allows self-assigning any role
- No admin panel for user management