groombook/api
13
0
Fork 0
Code Issues 2 Pull Requests 1 Actions Packages Projects Releases Wiki Activity

feat(GRO-2152): route optimization schema migration #164

Merged
Flea Flicker merged 1 commits from feat/gro-2152-route-optimization-schema-dev into dev 2026-06-08 07:55:36 +00:00
Conversation 0 Commits 1 Files Changed 4
+159 -1
Flea Flicker commented 2026-06-08 07:48:54 +00:00
Member
Copy Link
Copy Source

GRO-2152 — [Phase 1.1] Route Optimization: Schema migration

Database foundation for mobile groomer route optimization (plan: GRO-1164, "Database Schema Changes" section).

Changes (packages/db)

  • clients: latitude/longitude (double precision, null), geocoded_at (timestamp, null)
  • groomer_routes (new): id, staff_id FK→staff (cascade), route_date (date), status (route_status enum: draft/optimized/in_progress/completed), total_travel_mins, total_distance_km numeric(8,2), optimized_at, timestamps — UNIQUE(staff_id, route_date)
  • route_stops (new): id, route_id FK→groomer_routes (cascade), appointment_id FK→appointments (cascade), stop_order, latitude/longitude (NOT NULL), travel_mins_from_prev, travel_distance_km_from_prev numeric(8,2), buffer_mins default 15, timestamps — UNIQUE(route_id, appointment_id) and UNIQUE(route_id, stop_order)
  • business_settings: default_travel_buffer_mins (int, NOT NULL, default 15), route_optimization_provider (text, default nominatim), google_maps_api_key (text, null — encrypted at rest at the app layer, mirroring auth_provider_configs.client_secret)
  • Migration 0041_route_optimization.sql — hand-authored, idempotent (IF NOT EXISTS / DO $$ … duplicate_object), matching the established 0031_buffer_rules style; journal entry registered with when = max(existing)+1 so drizzle-kit applies it last.
  • factories.ts buildClient updated for the new nullable client columns.

Why packages/db only

The deployed schema + migration source is packages/db (the Dockerfile migrate stage runs pnpm --filter @groombook/db migrate; the image only COPYs packages/ + src/). apps/api/src/db is a legacy CI-only copy frozen at migration 0030 — the last 10 migrations (0031–0040) all landed in packages/db only, and dev is actively deleting the stale apps/api duplicates (GRO-2129). This change follows that precedent.

Verification

  • pnpm --filter @groombook/db typecheck ✅
  • pnpm --filter @groombook/db build ✅
  • Migration not applied locally (no groombook Postgres reachable from the agent pod); it is exercised by the CI migrate-image smoke test and the deploy migrate job against the groombook DB.

Closes GRO-2152.

🤖 Generated with Claude Code

cc @cpfarhood

## GRO-2152 — [Phase 1.1] Route Optimization: Schema migration Database foundation for mobile groomer route optimization (plan: [GRO-1164](/GRO/issues/GRO-1164#document-plan), "Database Schema Changes" section). ### Changes (`packages/db`) - **`clients`**: `latitude`/`longitude` (`double precision`, null), `geocoded_at` (`timestamp`, null) - **`groomer_routes`** (new): `id`, `staff_id` FK→staff (cascade), `route_date` (`date`), `status` (`route_status` enum: draft/optimized/in_progress/completed), `total_travel_mins`, `total_distance_km` `numeric(8,2)`, `optimized_at`, timestamps — **UNIQUE(staff_id, route_date)** - **`route_stops`** (new): `id`, `route_id` FK→groomer_routes (cascade), `appointment_id` FK→appointments (cascade), `stop_order`, `latitude`/`longitude` (NOT NULL), `travel_mins_from_prev`, `travel_distance_km_from_prev` `numeric(8,2)`, `buffer_mins` default 15, timestamps — **UNIQUE(route_id, appointment_id)** and **UNIQUE(route_id, stop_order)** - **`business_settings`**: `default_travel_buffer_mins` (int, NOT NULL, default 15), `route_optimization_provider` (text, default `nominatim`), `google_maps_api_key` (text, null — encrypted at rest at the app layer, mirroring `auth_provider_configs.client_secret`) - **Migration** `0041_route_optimization.sql` — hand-authored, idempotent (`IF NOT EXISTS` / `DO $$ … duplicate_object`), matching the established `0031_buffer_rules` style; journal entry registered with `when = max(existing)+1` so drizzle-kit applies it last. - `factories.ts` `buildClient` updated for the new nullable client columns. ### Why `packages/db` only The deployed schema + migration source is `packages/db` (the Dockerfile `migrate` stage runs `pnpm --filter @groombook/db migrate`; the image only COPYs `packages/` + `src/`). `apps/api/src/db` is a legacy CI-only copy frozen at migration 0030 — the last 10 migrations (0031–0040) all landed in `packages/db` only, and `dev` is actively deleting the stale `apps/api` duplicates (GRO-2129). This change follows that precedent. ### Verification - `pnpm --filter @groombook/db typecheck` ✅ - `pnpm --filter @groombook/db build` ✅ - Migration not applied locally (no groombook Postgres reachable from the agent pod); it is exercised by the CI migrate-image smoke test and the deploy migrate job against the groombook DB. Closes GRO-2152. 🤖 Generated with [Claude Code](https://claude.com/claude-code) cc @cpfarhood
Flea Flicker added 1 commit 2026-06-08 07:48:54 +00:00
feat(GRO-2152): route optimization schema migration
CI / Test (pull_request) Successful in 53s
Details
CI / Lint & Typecheck (pull_request) Successful in 1m0s
Details
CI / Build & Push Docker Images (pull_request) Successful in 4m13s
Details
4884961c8e 
Add the database foundation for mobile groomer route optimization:

- clients: latitude/longitude (double precision) + geocodedAt
- groomer_routes: per-(staff, date) route with route_status enum,
  totals, optimizedAt; UNIQUE(staff_id, route_date)
- route_stops: ordered stops FK->groomer_routes (cascade) + appointments,
  lat/lng, per-leg travel mins/distance, bufferMins;
  UNIQUE(route_id, appointment_id) and UNIQUE(route_id, stop_order)
- business_settings: defaultTravelBufferMins (default 15),
  routeOptimizationProvider (default nominatim), googleMapsApiKey
  (encrypted at rest at the app layer)
- Idempotent hand-authored migration 0041 + journal entry (when=max+1)

Lands in packages/db (the deployed schema/migration source per the
Dockerfile migrate stage); apps/api is the legacy CI-only copy.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Flea Flicker merged commit 40bd6dcfea into dev 2026-06-08 07:55:36 +00:00
Flea Flicker deleted branch feat/gro-2152-route-optimization-schema-dev 2026-06-08 07:55:36 +00:00
Sign in to join this conversation.
Reviewers
No Reviewers
Labels
Clear labels
bug
Something isn't working
 documentation
Improvements or additions to documentation
 duplicate
This issue or pull request already exists
 enhancement
New feature or request
 good first issue
Good for newcomers
 help wanted
Extra attention is needed
 invalid
This doesn't seem right
 question
Further information is requested
 wontfix
This will not be worked on
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
ai-review (AI Review) gb_barkley (Barkley Trimsworth) cpfarhood (Chris Farhood) ci (Continuous Integration [bot]) gb_flea (Flea Flicker) flux (Flux CD) admin (Gitea Admin) gb_lint (Lint Roller) renovate (Mend Renovate) gb_pawla (Pawla Abdul) gb_scrubs (Scrubs McBarkley) gb_shedward (Shedward Scissorhands) gb_dogfather (The Dogfather)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: groombook/api#164
Delete Branch "feat/gro-2152-route-optimization-schema-dev"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?