breedr/docs/MICROCHIP_FIX.md

# Microchip Field Fix

## Problem

The microchip field in the dogs table had a `UNIQUE` constraint defined directly on the column:

```sql
microchip TEXT UNIQUE
```

In SQLite, when a `UNIQUE` constraint is applied to a nullable column, **only one row can have a NULL value**. This caused the error:

```
UNIQUE constraint failed: dogs.microchip
```

When trying to add a second dog without a microchip number.

---

## Solution

Removed the inline `UNIQUE` constraint and replaced it with a **partial unique index** that only applies to non-NULL values:

```sql
-- Column definition (no UNIQUE constraint)
microchip TEXT

-- Partial unique index (only for non-NULL values)
CREATE UNIQUE INDEX idx_dogs_microchip 
ON dogs(microchip) 
WHERE microchip IS NOT NULL;
```

### Result:
- Multiple dogs can have no microchip (NULL values allowed)
- Dogs with microchips still cannot have duplicates
- Field is now truly optional

---

## Migration Required

If you have an existing database with the old schema, you **must run the migration** before the fix will work.

### Option 1: Docker Container (Recommended)

```bash
# Enter the running container
docker exec -it breedr sh

# Run the migration script
node server/db/migrate_microchip.js

# Exit container
exit

# Restart the container to apply changes
docker restart breedr
```

### Option 2: Direct Node Execution

```bash
cd /path/to/breedr
node server/db/migrate_microchip.js
```

### Option 3: Rebuild from Scratch (Data Loss)

```bash
# Stop container
docker stop breedr

# Remove old database
rm /mnt/user/appdata/breedr/breedr.db

# Start container (will create fresh database)
docker start breedr
```

**Warning:** Option 3 deletes all data. Only use if you have no important data or have a backup.

---

## What the Migration Does

### Step-by-Step Process

1. **Check Database Exists** - Skips if no database found
2. **Create New Table** - With corrected schema (no UNIQUE on microchip)
3. **Copy All Data** - Transfers all dogs from old table to new
4. **Drop Old Table** - Removes the table with bad constraint
5. **Rename New Table** - Makes new table the primary dogs table
6. **Create Partial Index** - Adds unique index only for non-NULL microchips
7. **Recreate Indexes** - Restores name and registration indexes
8. **Recreate Triggers** - Restores updated_at timestamp trigger

### Safety Features

- **Idempotent** - Can be run multiple times safely
- **Data Preservation** - All data is copied before old table is dropped
- **Foreign Keys** - Temporarily disabled during migration
- **Error Handling** - Clear error messages if something fails

---

## Verification

After migration, you should be able to:

### Test 1: Add Dog Without Microchip

```bash
curl -X POST http://localhost:3000/api/dogs \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test Dog 1",
    "breed": "Golden Retriever",
    "sex": "male"
  }'
```

**Expected:** Success (no microchip error)

### Test 2: Add Another Dog Without Microchip

```bash
curl -X POST http://localhost:3000/api/dogs \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test Dog 2",
    "breed": "Labrador",
    "sex": "female"
  }'
```

**Expected:** Success (multiple NULL microchips allowed)

### Test 3: Add Dog With Microchip

```bash
curl -X POST http://localhost:3000/api/dogs \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test Dog 3",
    "breed": "Beagle",
    "sex": "male",
    "microchip": "985112345678901"
  }'
```

**Expected:** Success

### Test 4: Try Duplicate Microchip

```bash
curl -X POST http://localhost:3000/api/dogs \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test Dog 4",
    "breed": "Poodle",
    "sex": "female",
    "microchip": "985112345678901"
  }'
```

**Expected:** Error (duplicate microchip not allowed)

---

## Database Schema Comparison

### Before (Broken)

```sql
CREATE TABLE dogs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  breed TEXT NOT NULL,
  sex TEXT NOT NULL,
  microchip TEXT UNIQUE,  -- ❌ Only one NULL allowed
  ...
);
```

### After (Fixed)

```sql
CREATE TABLE dogs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  breed TEXT NOT NULL,
  sex TEXT NOT NULL,
  microchip TEXT,  -- ✓ Multiple NULLs allowed
  ...
);

-- Partial unique index
CREATE UNIQUE INDEX idx_dogs_microchip 
ON dogs(microchip) 
WHERE microchip IS NOT NULL;  -- ✓ Only enforces uniqueness on non-NULL
```

---

## Technical Details

### Why SQLite Behaves This Way

From SQLite documentation:

> For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. However, when a UNIQUE constraint is defined on a column, SQLite treats NULL as a single value.

This is a quirk of SQLite's implementation. PostgreSQL and MySQL allow multiple NULLs in UNIQUE columns by default.

### Partial Index Solution

Partial indexes (with WHERE clause) were introduced in SQLite 3.8.0 (2013). They allow us to:

1. Create an index that only includes certain rows
2. In this case, only rows where `microchip IS NOT NULL`
3. This means the uniqueness constraint doesn't apply to NULL values
4. Multiple NULL values are now allowed

---

## Rollback (If Needed)

If something goes wrong during migration:

### Manual Rollback Steps

1. **Stop the application**
   ```bash
   docker stop breedr
   ```

2. **Restore from backup** (if you made one)
   ```bash
   cp /mnt/user/appdata/breedr/breedr.db.backup \
      /mnt/user/appdata/breedr/breedr.db
   ```

3. **Start the application**
   ```bash
   docker start breedr
   ```

### Create Backup Before Migration

```bash
# Stop container
docker stop breedr

# Create backup
cp /mnt/user/appdata/breedr/breedr.db \
   /mnt/user/appdata/breedr/breedr.db.backup

# Start container
docker start breedr

# Run migration
docker exec -it breedr node server/db/migrate_microchip.js
```

---

## Future Prevention

All new databases created with the updated schema will have the correct constraint from the start. No migration needed for:

- Fresh installations
- Deleted and recreated databases
- Databases created after this fix

---

## Related Files

- **Schema Definition:** `server/db/init.js`
- **Migration Script:** `server/db/migrate_microchip.js`
- **This Document:** `docs/MICROCHIP_FIX.md`

---

## Changelog

### March 8, 2026
- Identified UNIQUE constraint issue with microchip field
- Created migration script to fix existing databases
- Updated schema for new databases
- Added partial unique index solution
- Documented problem and solution

---

*If you encounter any issues with the migration, check the container logs:*

```bash
docker logs breedr
```

*Or open a GitHub issue with the error details.*