ali-ahnaf / ali-ahnaf/pocket_pixel

Encrypt Expense.title/amount and Tag.name at rest (zero-knowledge, backend)

Aperta
#180 3 commenti 0 reazioni 1 assegnatario Rivendicata da @ali-ahnaf Vedi su GitHub
backend blocked
Lingua principale
TypeScript
Stelle
14
Fork
91
Metriche di merge delle PR
Nessuna PR unita negli ultimi 30g

Descrizione

## 🔒 Context / background
The `expenses` table (`packages/api/src/entities/Expense.entity.ts`) stores `title` and `amount` as plaintext, and the `tags` table (`packages/api/src/entities/Tag.entity.ts`) stores `name` as plaintext, in the SQLite DB (`packages/api/pocket_pixel.sqlite`) and in backups. We want the server to become "zero-knowledge" for these three fields — the API and DB should only ever see ciphertext, never the plaintext value or the encryption key. All encryption/decryption happens in the browser.

This is the backend half of the work. A companion frontend issue covers the client-side crypto and UI changes, and is **blocked by this one** (it needs the new API contracts/endpoints first).

## 🎯 Problem / goal
Today anyone with read access to the SQLite file (or a leaked backup) can read every expense title, amount, and tag name in plaintext. We want these three fields stored as ciphertext blobs instead, decryptable only with the owning user's key — a key the server never holds.

## ⚠️ Important — read before starting
This is more than "add two columns." Two things currently only work *because* the server can read `amount` in plaintext, and both need a decision before writing code:

1. **No key-management exists yet.** There is no per-user Data Encryption Key (DEK), no KDF, and no wrapped-key storage anywhere in the codebase today (checked `auth.service.ts` and `User.entity.ts` — only a bcrypt password hash exists). This issue has to stand up that foundation too (a wrapped-DEK column on `User` + an endpoint to fetch/rotate it) — it is not a preexisting piece to plug into.
2. **The AI budgeting feature (`packages/api/src/services/wizard.service.ts`, ~lines 117-157) reads `expense.amount` server-side** to build its prompt to the LLM (spend-vs-budget per vault/tag). If the server never sees plaintext amounts, this feature breaks as currently written. Pick one approach before implementing, and flag the choice to a maintainer rather than silently picking:
- the wizard only receives pre-aggregated numbers the client already computed after decrypting (client sends "spent so far per vault/tag" as a param, not raw expenses), or
- the AI wizard flow is explicitly descoped from the zero-knowledge guarantee.

## 🛠 Suggested approach
1. **Schema** — use `npm run migration:generate` (don't hand-write), add nullable columns, keep old plaintext columns until backfill is verified:
- `expenses`: add `encryptedData TEXT`, `nonce TEXT` (ciphertext of `{ title, amount }`)
- `tags`: add `encryptedName TEXT`, `nonce TEXT`
- `users`: add columns for the user's wrapped DEK (e.g. `wrappedDek TEXT`, `dekNonce TEXT`)
- Entities: `packages/api/src/entities/Expense.entity.ts`, `packages/api/src/entities/Tag.entity.ts`, `packages/api/src/entities/User.entity.ts`
2. **Contracts** — update `packages/shared/src/contracts/transactions.ts` and `packages/shared/src/contracts/tags.ts` so `CreateTransactionInput`/`UpdateTransactionInput`/`TransactionDto` and `CreateTagInput`/`UpdateTagInput`/`TagDto` carry `{ nonce, encryptedData }` instead of raw `title`/`amount`/`name`. Rebuild with `npm run build:shared` after.
3. **Repositories/services**:
- `packages/api/src/repositories/analytics.repository.ts` does `SUM(e.amount)` (~lines 32, 45, 57) and `ORDER BY total DESC` (~line 62) for monthly/yearly/per-tag totals — the server can no longer compute these. Replace with endpoints that return raw (still-encrypted) rows for client-side aggregation, or remove once the frontend issue lands client aggregation.
- `packages/api/src/repositories/tags.repository.ts` (`order: { name: 'ASC' }`) and `packages/api/src/repositories/recurring.repository.ts` (`order: { title: 'ASC' }`) sort by the now-encrypted field — drop the `ORDER BY`, let the client sort post-decrypt.
4. **Migration script for existing rows** — the server never has a DEK, so existing plaintext rows can only be migrated by the client. Add an endpoint the client calls once per user (e.g. on next login) to fetch plaintext rows, and a corresponding write-back endpoint for the encrypted versions. No server-side batch job.
5. **Cleanup migration** — once a user's rows are confirmed migrated, a *separate follow-up* migration drops `title`/`amount` from `expenses` and `name` from `tags`. Don't drop in the same migration as the additive one, so there's a rollback path.
6. Run `npm run migration:run` after generating each migration.

## ✅ Acceptance criteria
- [ ] Decision documented on the AI wizard / plaintext-amount conflict above
- [ ] `users` table stores a wrapped DEK; server never stores/logs the raw DEK
- [ ] `expenses` and `tags` have new nullable encrypted columns; old plaintext columns untouched by new writes
- [ ] `packages/shared` contracts updated and rebuilt (`npm run build:shared`)
- [ ] Create/update endpoints for expenses and tags accept `{ nonce, encryptedData }` and never receive/log plaintext `title`/`amount`/`name`
- [ ] `analytics.repository.ts` SUM/ORDER BY queries on `amount` removed or replaced with raw-row endpoints for client aggregation
- [ ] `tags.repository.ts`/`recurring.repository.ts` no longer `ORDER BY` the encrypted field
- [ ] One-time client-triggered migration endpoint(s) added for backfilling existing rows
- [ ] Jest tests added/updated (see `.agents/rules/testing.md`) — especially asserting no route ever logs/returns a decrypted value
- [ ] No `try/catch` added in routes — errors still flow through the global handler

## Notes
- `Vault.monthlyBudget` stays plaintext per original scope; comparisons against decrypted expense totals happen client-side.
- Related files: `packages/api/src/entities/Expense.entity.ts`, `packages/api/src/entities/Tag.entity.ts`, `packages/api/src/entities/User.entity.ts`, `packages/api/src/repositories/analytics.repository.ts`, `packages/api/src/repositories/tags.repository.ts`, `packages/api/src/repositories/recurring.repository.ts`, `packages/api/src/services/wizard.service.ts`, `packages/shared/src/contracts/transactions.ts`, `packages/shared/src/contracts/tags.ts`.

Guida per i contributori

Nessuna guida per i contributori indicizzata per questo repository

Valutazione

Questa issue non è ancora stata valutata.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.