Backup entry-restore is broken - INSERT omits NOT NULL project_id and uses invalid certainty #84

New issue

Closed

opened 2026-07-15 19:49:56 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-07-15 19:49:56 +00:00

Contributor

Severity: High · Confidence: High · Effort: S · Category: code

Evidence

api/src/services/backup.ts:407-424 - applyRestoreEntry INSERT omits project_id; defaults certainty to 'possible'.
api/src/db/migrations/026_projects.sql:97 - entries.project_id SET NOT NULL, no default.
api/src/db/migrations/002_entries_tags_audit.sql:10 - certainty CHECK IN ('confirmed','likely','speculative').

Problem
The restore INSERT does not supply project_id (NOT NULL, no default → Postgres 23502), and its certainty fallback 'possible' is not in the CHECK set (? 23514). Every staged-entry apply throws.

Impact
Granular entry restore is completely broken. It fails loudly (400 per entry), but at exactly the moment recovery is needed. Directly violates the spec's first principle ("her data is irreplaceable").

Fix
Source project_id from the staged entry_data (the COPY parser already captures it) or require a target project id; change the certainty fallback to a valid value ('confirmed'). Add a restore integration test.

Acceptance criteria

A staged entry restore reaches status applied with a valid project_id.
Integration test covers restore-apply end to end.

Related: backup can also silently stop (F-09).

Filed from the 2026-07-15 codebase audit. Full report: docs/.internal/report-2026-07-15.md (gitignored).

**Severity:** High · **Confidence:** High · **Effort:** S · Category: code **Evidence** - `api/src/services/backup.ts:407-424` - `applyRestoreEntry` INSERT omits `project_id`; defaults `certainty` to `'possible'`. - `api/src/db/migrations/026_projects.sql:97` - `entries.project_id SET NOT NULL`, no default. - `api/src/db/migrations/002_entries_tags_audit.sql:10` - `certainty CHECK IN ('confirmed','likely','speculative')`. **Problem** The restore INSERT does not supply `project_id` (NOT NULL, no default → Postgres `23502`), and its `certainty` fallback `'possible'` is not in the CHECK set (? `23514`). Every staged-entry apply throws. **Impact** Granular entry restore is completely broken. It fails loudly (400 per entry), but at exactly the moment recovery is needed. Directly violates the spec's first principle ("her data is irreplaceable"). **Fix** Source `project_id` from the staged `entry_data` (the COPY parser already captures it) or require a target project id; change the `certainty` fallback to a valid value (`'confirmed'`). Add a restore integration test. **Acceptance criteria** - [ ] A staged entry restore reaches status `applied` with a valid `project_id`. - [ ] Integration test covers restore-apply end to end. Related: backup can also silently stop (F-09). --- _Filed from the 2026-07-15 codebase audit. Full report: `docs/.internal/report-2026-07-15.md` (gitignored)._

claude-bot added this to the v7.1.0 - Core hardening & security milestone

2026-07-15 19:49:56 +00:00

claude-bot added the

bug

label

2026-07-15 19:49:56 +00:00

rbrooks referenced this issue from a commit

2026-07-16 21:34:18 +00:00

fix(v7.1): wave 3 — backup recovery & process lifecycle (#87, #84, #85, #86)

claude-bot commented

2026-07-16 21:47:32 +00:00

Author

Contributor

Fixed in b640072 (v7.1.0 wave 3).

applyRestoreEntry now:

Supplies project_id (NOT NULL with no default since migration 026 → was throwing 23502 on every apply). Sourced from the staged entry_data, with a guard: if that project no longer exists (or was soft-deleted) it falls back to the user's active/default project via defaultProjectId(userId), and throws a clear error only if the user has no project at all.
Validates certainty against the real CHECK set (confirmed|likely|speculative, migration 002). The old 'possible' fallback wasn't in that set → 23514. Invalid/missing values now default to 'confirmed'.

Acceptance criteria:

A staged entry restore reaches status applied with a valid project_id.
Integration test covers restore-apply end to end — api/src/test/integration/backup.test.ts › restore apply (#84):
1. applies a staged entry and asserts the entry row keeps its original project_id and certainty: 'likely', and the staging row flips to applied;
2. reproduces the exact old failure mode (staged data with no project_id and the invalid 'possible' certainty) and asserts it now applies, falling back to the active project with certainty: 'confirmed'.

Verified on the dev server: 234/234 tests pass. CI green.

Related: #86 (backup can also silently stop) fixed in the same wave.

Fixed in `b640072` (v7.1.0 wave 3). `applyRestoreEntry` now: - **Supplies `project_id`** (NOT NULL with no default since migration 026 → was throwing `23502` on every apply). Sourced from the staged `entry_data`, with a guard: if that project no longer exists (or was soft-deleted) it falls back to the user's active/default project via `defaultProjectId(userId)`, and throws a clear error only if the user has no project at all. - **Validates `certainty`** against the real CHECK set (`confirmed|likely|speculative`, migration 002). The old `'possible'` fallback wasn't in that set → `23514`. Invalid/missing values now default to `'confirmed'`. **Acceptance criteria:** - [x] A staged entry restore reaches status `applied` with a valid `project_id`. - [x] Integration test covers restore-apply end to end — `api/src/test/integration/backup.test.ts` › `restore apply (#84)`: 1. applies a staged entry and asserts the entry row keeps its original `project_id` and `certainty: 'likely'`, and the staging row flips to `applied`; 2. reproduces the exact old failure mode (staged data with **no `project_id`** and the invalid `'possible'` certainty) and asserts it now applies, falling back to the active project with `certainty: 'confirmed'`. Verified on the dev server: 234/234 tests pass. CI green. Related: #86 (backup can also silently stop) fixed in the same wave.

claude-bot closed this issue

2026-07-16 22:04:32 +00:00

claude-bot referenced this issue

2026-07-16 22:05:06 +00:00

Roadmap: sequence a core-hardening pass before v8 and test the v7 federation trust boundary #92