# FamilyDashboard persistence and admin-data protection

Use this when touching `/home/agent/projects/FamilyDashboard`, especially Docker, backend tests, SQLite, seeds, Admin CRUD, schedule, task templates, or benefits.

## Critical invariants

- Canonical production DB: `/home/agent/projects/FamilyDashboard/data/familydashboard.db`.
- Backend container path: `/data/familydashboard.db`.
- Compose must use bind mount `./data:/data`; do not reintroduce `familydashboard-data` as the productive store.
- Never run `docker compose down -v` for normal work.
- Always back up before DB/persistence work: `scripts/backup-db.sh`.
- Diagnose with `scripts/db-doctor.sh` and container counts before/after changes.
- GitHub token for this repo is in `~/.hermes/secrets/familydashboard_github_token`; never print it.

## Known failure mode found in session

Backend tests executed inside the backend container inherited `FAMILYDASHBOARD_DATABASE_URL=sqlite:////data/familydashboard.db`. The pytest fixture dropped and recreated tables, thereby mutating the production DB. Guard against this class of failure by ensuring tests override the DB URL before importing `app.db.session`:

```python
import os
from pathlib import Path

os.environ["FAMILYDASHBOARD_DATABASE_URL"] = "sqlite:////tmp/familydashboard-test.db"
Path("/tmp/familydashboard-test.db").unlink(missing_ok=True)
```

Then import the app/session modules. After any test run, compare production counts before/after to prove production DB was untouched.

## Required workflow for persistence changes

1. Capture status first:
   - `git status -sb`
   - `docker compose ps`
   - `docker compose config | sed -n '1,140p'`
   - `docker volume ls | grep -i familydashboard || true`
   - container DB path/counts via Python sqlite3.
2. Create backups before edits:
   - `mkdir -p data/backups rescue-db`
   - `scripts/backup-db.sh` if available.
   - `docker cp familydashboard-backend-1:/data/familydashboard.db rescue-db/...db` when container is running.
3. Compare all candidate DBs by size and counts for `child`, `scheduleblock`, `tasktemplate`, `benefit`.
4. If candidates disagree and the correct DB is unclear, stop and report; do not guess or seed over data.
5. Ensure `.gitignore` excludes `data/*.db`, `data/*.db-*`, `data/backups/`, and `rescue-db/`.
6. Ensure `seed_database()` is additive only; it must not disable/overwrite Admin-created schedule blocks, task templates, or benefits.
7. Ensure destructive reference reset endpoints require exact confirmation payload, e.g. `{ "confirm": "RESET_STUNDENPLAN" }`.
8. Add startup logging for DB path/size/counts and fail-fast if a marked production DB suddenly has empty core tables.
9. When changing SQLite uniqueness semantics on production tables and the user has explicitly forbidden data loss, do not casually rebuild/drop existing tables. Prefer an additive migration path (new versioned/slot-specific table, backfill only if clearly safe, old table left intact) unless a vetted migration framework and backup/restore plan are in place.
10. If a live API smoke finds an SQLite `UNIQUE constraint failed` after adding a new dimension (for example a quiz `slot`), suspect an old unique index still exists in the real DB even if the SQLModel model changed. Fix the storage/migration strategy, not the API retry loop.

## Admin task-template pitfalls

- Admin edits to `TaskTemplate` title/kind/coin/penalty must also update open `TaskInstance` rows for today/future; otherwise the Admin portal appears to save while the child dashboard keeps stale tasks (e.g. Duolingo remains bonus after changing the template to Pflicht).
- Use shared/common tasks as `TaskTemplate.child_id = NULL`: dashboard creates one row per child, and task completion/undo should group by `task_template_id + task_date` so a task like “Wuschi füttern” is completed once and checked for both children.

## Verification checklist

- `docker compose down` then `docker compose up -d --build` — never `down -v`.
- `curl -fsS http://localhost:8000/api/health`.
- `curl -I http://localhost:5173`.
- Create a test ScheduleBlock, TaskTemplate, Benefit, and CalendarEvent via Admin API/UI.
- Rebuild again with `docker compose down && docker compose up -d --build`.
- Confirm all four test rows remain; for ScheduleBlock verify both `subject` and `title` when the Admin CRUD path is involved.
- Run backend tests and confirm production DB counts did not change, including `calendarevent`, `dailyphraseassignment`, and `phrasequizattempt` when those features are touched.
- Run frontend build and Playwright when UI is touched.
- Commit and push after green verification.