jason/breedr
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

fix: Remove old record_type CHECK constraint from health_records (Migration 003) #40

Merged
jason merged 1 commits from fix/health-record-type-constraint into master 2026-03-10 13:08:08 -05:00
Conversation 0 Commits 1 Files Changed 1 +104
jason commented 2026-03-10 13:07:00 -05:00
Owner
Copy Link

Problem

The live breedr.db was originally created with a restrictive CHECK constraint on health_records.record_type:

record_type IN ('test', 'vaccination', 'exam', 'treatment', 'certification')

This blocks newer record types like ofa_clearance from being inserted, producing the error:

CHECK constraint failed: record_type IN ('test', 'vaccination', 'exam', 'treatment', 'certification')

The init.js was already updated to not include this constraint, but SQLite does not modify existing tables on restart — so the constraint persists in existing databases.

Fix

Added Migration 003 to migrations.js which:

  1. Detects if the old constraint is present by inspecting sqlite_master
  2. Backs up existing health_records data
  3. Drops and recreates the table without the CHECK constraint
  4. Restores all existing records
  5. Adds a validation check to confirm the constraint is gone

The migration is idempotent and skips safely if the constraint is already absent (fresh installs).

Testing

  • Fresh install: migration skips, no impact
  • Existing DB with old constraint: table is rebuilt, all existing records preserved, new ofa_clearance records can be inserted
## Problem The live `breedr.db` was originally created with a restrictive `CHECK` constraint on `health_records.record_type`: ```sql record_type IN ('test', 'vaccination', 'exam', 'treatment', 'certification') ``` This blocks newer record types like `ofa_clearance` from being inserted, producing the error: > `CHECK constraint failed: record_type IN ('test', 'vaccination', 'exam', 'treatment', 'certification')` The `init.js` was already updated to not include this constraint, but SQLite does **not** modify existing tables on restart — so the constraint persists in existing databases. ## Fix Added **Migration 003** to `migrations.js` which: 1. Detects if the old constraint is present by inspecting `sqlite_master` 2. Backs up existing `health_records` data 3. Drops and recreates the table **without** the CHECK constraint 4. Restores all existing records 5. Adds a validation check to confirm the constraint is gone The migration is idempotent and skips safely if the constraint is already absent (fresh installs). ## Testing - Fresh install: migration skips, no impact - Existing DB with old constraint: table is rebuilt, all existing records preserved, new `ofa_clearance` records can be inserted
jason added 1 commit 2026-03-10 13:07:00 -05:00
fix: Migration 003 - remove old record_type CHECK constraint from health_records 031e344fcb
jason merged commit 7d498962c8 into master 2026-03-10 13:08:08 -05:00
Sign in to join this conversation.
Reviewers
No Reviewers
Labels
No items
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
jason
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: jason/breedr#40
Delete Branch "fix/health-record-type-constraint"

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?