Serialize the entire db:reset chain (DROP → migrate → seed) inside one withSeedAdvisoryLock callback so a concurrent same-PRNG seeder cannot interleave and collide on invoices_pkey. Pool sized max:6 (1 reserved for the lock + work headroom) to avoid the connection-starvation deadlock the CTO caught. Verified with three end-to-end live db:reset runs against a throwaway Postgres.
cc @cpfarhood
The reset-demo-data CronJob in groombook-uat intermittently failed with
FK 23503 on invoice_tip_splits because two pods could run the seed
concurrently: the new pod's TRUNCATE deleted rows the old pod was still
inserting.
Acquire a session-level advisory lock for the full duration of the seed.
CRITICAL: with postgres-js connection pooling, a pg_advisory_lock
acquired on one pooled connection and released on a different one is a
no-op (the lock is bound to the pg-backend that took it). We therefore
reserve a dedicated connection for the lock, take pg_advisory_lock(KEY)
on it, run the seed on the pooled connections, and release the lock +
reserved connection in a try/finally so a thrown seed error cannot leak
the lock or the connection.
Defence-in-depth with the infra PR that switches
concurrencyPolicy: Replace → Forbid on the reset-demo-data CronJob.
- Adds withSeedAdvisoryLock helper and runSeedBody extracted function
- Wraps seed() body in the helper; client.end() runs after the lock
releases so a reserved connection is not returned to a closed pool
- SEED_ADVISORY_LOCK_KEY = 0x47524f4f ("GROO" in ASCII) — arbitrary
stable 32-bit key, referenced in runbooks
- UAT_PLAYBOOK.md §3.29 documents the regression check
cc @cpfarhood
Co-Authored-By: Paperclip <noreply@paperclip.ing>
Prod cumulative promotion 2026.06.01-7667288 (PR #596) revealed that
0034_extend_pet_profile_columns (temperament_score + 3 jsonb cols) and
0036_add_missing_coat_type_values (short/medium/silky) were silently
skipped on the prod database, leaving the seed/reset path with:
Seed failed: PostgresError: column "temperament_score" does not exist
## Root cause: drizzle high-water-mark, same shape as GRO-1999
drizzle-orm@0.38.4 `pg-core/dialect.js#migrate` only applies a journal
entry when its `folderMillis` is strictly greater than the most recent
`__drizzle_migrations.created_at`:
if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis) {
// apply SQL + record hash
}
`packages/db/migrations/meta/_journal.json` has 0033's when at
1779500000000 (2026-05-23) — but 0034 was registered with when
1751140800000 (2025-06-28) and 0036 with 1751480000000 (2025-07-02).
Both are below the 0033 watermark, so on the prod DB (whose newest
applied migration was 0033) drizzle silently skipped 0034 and 0036.
0038 (when 1780000000000) was above the watermark, so it applied — and
the migrate Job exits 0 with 'migrations applied successfully!'. The
schema didn't change. GRO-1999 documented the same bug for 0037 → 0038.
UAT/dev are unaffected because their watermarks were already below the
0034/0036 entries when those originally ran.
## Fix
Add two new idempotent migrations with monotonic 'when':
- 0039_extend_pet_profile_columns_idempotent.sql, when 1780000000001:
ALTER TABLE pets ADD COLUMN IF NOT EXISTS temperament_score integer;
-- + temperament_flags jsonb, medical_alerts jsonb, preferred_cuts jsonb
- 0040_register_missing_coat_type_values.sql, when 1780000000002:
ALTER TYPE coat_type ADD VALUE IF NOT EXISTS 'short';
-- + 'medium', 'silky'
Both are 'IF NOT EXISTS' — safe no-ops on UAT/dev where 0034/0036
applied normally, and effective forward-fix on prod where they were
skipped. Do NOT modify 0034/0036 in place (per the GRO-1999 pattern):
UAT/dev have already applied them and re-running would fail.
## Verification
- packages/db/migrations/meta/_journal.json now has 41 entries with idx
39 and 40 strictly monotonic in 'when'.
- python3 -c 'import json; json.load(open(...))' parses cleanly.
- ALTER TYPE ADD VALUE IF NOT EXISTS is permitted inside a tx on
PostgreSQL 18.3 (prod cluster image confirmed via CNPG status).
## UAT Playbook
No user-visible behaviour change — schema only. Existing TC-API-3.8 / 3.9 /
3.11 / 3.13 (extended pet profile) and 3.19a (profile summary) continue to
pass and now ALSO act as smoke tests after the prod image roll-forward.
## Refs
- Issue: GRO-2033
- Same-shape prior bug: GRO-1999 (0037 → 0038), commit 423d4bf
- Mitigation: groombook/infra PR #597 (suspend prod reset-demo-data
CronJob while this lands)
Co-Authored-By: Paperclip <noreply@paperclip.ing>
GRO-1979 added 0037_add_extra_large_to_pet_size_category with a journal
'when' of 1751500000000 — below the 0033 high-water mark (1779500000000)
on existing UAT/persistent DBs. Drizzle only applies a migration when its
journal.when is strictly greater than max(applied created_at), so 0037
was silently skipped, leaving pet_size_category without 'extra_large'
and crashing the UAT seed-test-data job (22P02 enum error).
This adds 0038 with a monotonic 'when' (1780000000000) so it applies on
both existing UAT/persistent DBs and fresh DBs. Statement is idempotent
(ADD VALUE IF NOT EXISTS) and a single auto-commit DDL (ADD VALUE cannot
run inside a transaction block).
Do not modify 0033/0034/0036/0037 — re-registering extra_large is correct
since the drizzle PetSizeCategory type and seed.ts both use that value.
GRO-2004
Co-Authored-By: Paperclip <noreply@paperclip.ing>
This merge resolves a journal conflict between dev's idx 37 entry (0037_add_extra_large_to_pet_size_category) and the diverged uat branch. Both branches want the idx 37 entry; keeping the dev version which adds the migration.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
GRO-1979: The pet_size_category enum created in 0031_buffer_rules.sql
contained ('small', 'medium', 'large', 'xlarge'), but the drizzle schema
and seed.ts both use 'extra_large'. The mismatch caused the UAT seed job
to fail with:
invalid input value for enum pet_size_category: "extra_large"
This migration adds the 'extra_large' value to pet_size_category and
registers it at idx 37 in the drizzle journal (sequel to 0035/0036
which registered short/medium/silky in coat_type under GRO-1971).
Non-transactional per Postgres restriction on ALTER TYPE ADD VALUE.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
GRO-1850: Adds temperament_score, temperament_flags, medical_alerts,
and preferred_cuts to the pets table.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
feat(GRO-1177): add pet profile summary endpoint (#30)
Adds GET /api/pets/:id/profile-summary with aggregated pet profile,
grooming history, visit count, and upcoming appointment.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
Lint Roller (QA) flagged that buildPet in factories.ts was missing the
4 fields added to the pets table schema, causing TS2739 in the Docker
build job (run 1701, job 3717).
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Returns aggregated pet profile with:
- All pet fields (basic + extended)
- recentGroomingHistory: last 10 entries from groomingVisitLogs with staff name join
- lastVisitDate: most recent groomedAt timestamp
- visitCount: count of completed appointments
- upcomingAppointment: next scheduled/confirmed appointment with service/staff name
Enforces same groomer RBAC as GET /:id. Returns 404 for non-existent pets.
Adds PetProfileSummary, GroomingHistoryEntry, and UpcomingAppointment types.
Adds unit tests covering: 404, 403, aggregated profile, empty history, no upcoming appt.
Updates UAT_PLAYBOOK.md §3 with TC-API-3.8 and TC-API-3.9.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Migration 0031 tries to ALTER the coat_type and pet_size_category columns
on the pets table to use new enum types, but no prior migration adds
these columns. On a fresh DB (after the reset CronJob wiped all tables),
this causes the entire migration chain to fail and roll back.
Added ADD COLUMN IF NOT EXISTS before the ALTER TYPE so the migration
works both on fresh databases and existing ones with the columns.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
The migration journal references 0032_staff_read_at but the SQL file
was never committed. drizzle-kit migrate fails with "No file
./migrations/0032_staff_read_at.sql found" which blocks all subsequent
migrations including the 0033 default_buffer_minutes fix.
Added as a no-op since the staff table schema has no readAt column.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
Adds 0033_add_services_default_buffer_minutes.sql with idempotent
ALTER TABLE to ensure services.default_buffer_minutes exists.
Also fixes _journal.json by adding missing 0031_buffer_rules entry (idx 31)
and 0032_staff_read_at entry (idx 32) that were absent from the journal.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
CI Run 942 Docker build fails on TS2451 (duplicate bufferRules at lines
190 & 653 of schema.ts), TS1117 (duplicate defaultBufferMinutes in
services table, duplicate coatType/petSizeCategory in factories.ts),
and TS2322 (null vs number for defaultBufferMinutes in factories.ts).
Keep the newer, more complete bufferRules declaration (with comments and
index) and the .notNull().default(0) variant of defaultBufferMinutes.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
The schema file had two sets of these enum declarations with different values.
The first (stale) set broke all tests importing @groombook/db via the vitest alias,
causing CI to fail and blocking the docker build job.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
GRO-1325 was marked done but never implemented. This adds the missing
Better-Auth user + account seeding for UAT email+password logins.
For each SEED_UAT_*_PASSWORD env var present, the seed now:
1. Creates (or links to existing) a Better-Auth user record with
emailVerified: true
2. Creates a credential account with providerId: "credential"
and a bcrypt-hashed password (using better-auth/crypto)
3. Links the staff record to the Better-Auth user via userId
Idempotent: skips user/account creation if already seeded.
Updated UAT_PLAYBOOK.md §4.1 — TC-API-1.4 through 1.9 now reference
the new seed provisioning (GRO-1325 was the missing piece).
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Re-implement lost commit from worktree cleanup. PR #12 already has
UAT_PLAYBOOK + factories fix; add all missing core implementation:
- Add petSizeCategoryEnum/coatTypeEnum to schema
- Add bufferRules table with service FK + unique constraint
- Add defaultBufferMinutes column to services table
- Change pets.coatType/petSizeCategory text columns to use enums
- Add routes/buffer-rules.ts: GET/POST/PATCH/DELETE, manager role guard
- Register /api/buffer-rules in index.ts
- Update services.ts PATCH to accept defaultBufferMinutes
- Update pets.ts POST/PATCH to accept sizeCategory/coatType
- Cast coatType/petSizeCategory in book.ts insert to match new enums
- Add 0031_buffer_rules.sql migration
- Fix factories.ts buildService to include defaultBufferMinutes: null
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Address QA review findings on PR #12:
- Add coatType and petSizeCategory to buildPet defaults in packages/db/src/factories.ts
to fix TypeScript typecheck failure
- Restore UAT_PLAYBOOK.md (was deleted during monorepo extraction) and add
§4.15 Buffer Rules test cases
Co-Authored-By: Paperclip <noreply@paperclip.ing>
Sync api packages/types with web workspace — add MedicalAlert, AlertSeverity,
CoatType, preferredCuts, medicalAlerts, temperamentScore, temperamentFlags.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
- Add petSizeCategory and petCoatType to bookingSchema zod validator (optional)
- Save coatType to pets row on booking creation
- Add coatType and petSizeCategory columns to pets DB schema
- Add coatType and petSizeCategory to Pet interface in @groombook/types
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Use pnpm --filter consistently for all three package builds in the
Dockerfile instead of mixing filter and cd approaches. Also set
--project . explicitly on tsc invocations to ensure tsconfig resolution
from the package directory rather than workspace root.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
pnpm --filter runs in the workspace root where tsc finds the root
tsconfig.json instead of packages/db/tsconfig.json. Change into the
package directory so tsc picks up the correct local tsconfig.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
tsc without --project traverses up to workspace root, which has a
different tsconfig.json that lacks package-local paths. Fix both
@groombook/types and @groombook/db scripts consistently.
Co-Authored-By: Paperclip <noreply@paperclip.ing>
PetRow (pets.$inferSelect) now includes these nullable columns after
the GRO-1174 migration, but buildPet's defaults were never updated.
Adding null defaults fixes the typecheck failure in CI.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Flea Flicker
Flea Flicker
The Dogfather
Scrubs McBarkley
Lint Roller
Chris Farhood