uttrflow / uttrflow/uttrflow-swift

Rows deleted from the AI suggestions store stay readable in predict.v1.sqlite-wal for as long as the app keeps running

Open
#642 0 comments 0 reactions 0 assignees View on GitHub
area:ai-suggestions bug good first issue P0 security
Dominant language
Swift
Stars
4
Forks
17
Avg merge
3h 32m
Merged PRs (30d)
277

Description

## What happens

The suggestions store opens in WAL mode (`Sources/UttrflowPredictStore/Schema.swift:8`, `PRAGMA journal_mode = WAL`), and the database stays open for the life of the process. Forgetting is a plain `DELETE` (`Sources/UttrflowPredictStore/PredictStore.swift:295-311`, plus eviction at `:407`), followed by no checkpoint.

Measured with the system SQLite (3.51.0) and the same pragmas and open flags as `Database.init` (`Sources/UttrflowPredictStore/SQLite.swift:30-31`):

- `PRAGMA secure_delete` reports `2` (fast). Deleted cells are zeroed in the pages written after the delete.
- The frames written *before* the delete stay in the `-wal` file. With 50 inserted lines and then `DELETE FROM e`, the `-wal` file was 218,392 bytes, and a marker string from the deleted rows appeared 1,275 times in it, while `SELECT count(*)` returned 0.
- Only closing the last connection checkpoints and removes the WAL, which gets the marker count to 0. The app closes it only at quit.

Automatic checkpoints (every 1,000 pages) rewind the WAL but do not truncate or overwrite the tail, so old frames can survive past a checkpoint too.

## Why it matters

After "forget" (once it is wired up, #640), the forgotten lines are still in a file beside the database until the next quit. A Time Machine backup taken in that window, or a crash, keeps them. The person asked for them to be gone.

## How to measure

A throwaway Swift script that opens a file with the flags above, runs the three pragmas from `Schema.statements`, inserts rows containing a marker, deletes them, and counts the marker in `-wal` before closing.

## Acceptance criteria

- After `forget(bundleIdentifier:)`, `forget(_:in:)` and `forgetEverything()`, the store runs `PRAGMA wal_checkpoint(TRUNCATE)`. Consider `PRAGMA secure_delete = ON` in `Schema.statements`, so the setting does not depend on how the system library was built.
- A test in `Tests/UttrflowPredictStoreTests` records a line containing a unique marker, forgets it, and asserts the marker is absent from the bytes of both the database file and its `-wal`, without closing the store.

## Where to start

- `Sources/UttrflowPredictStore/PredictStore.swift` (the three `forget` methods) and `Schema.swift`.
- Extend the existing store tests in `Tests/UttrflowPredictStoreTests`. They already open a store in a temporary directory.
- Run `make verify`. See `CONTRIBUTING.md`.
- Size: small, about 20 lines plus one test.

Contributor guide

Open the contributing guide

Research direction

Start in Sources/UttrflowPredictStore/PredictStore.swift at the three forget methods and review Sources/UttrflowPredictStore/Schema.swift for the existing pragmas. Extend the tests in Tests/UttrflowPredictStoreTests to insert a unique marker, forget it without closing the store, and verify it is absent from both database files. Run make verify; done means all forget paths checkpoint and the marker is absent from the database and -wal bytes.

Written by the indexing model from the issue text.

Assessment

Tech stack
sqlite, swift
Domain
databases, security
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
78/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.