Living documentation — last reviewed 2026-05-28

Progress Photos — Data Model

Schema: libs/db/src/lib/schema/progress-photos.ts.

`progress_photos`

Column	Type	Notes
`id`	`uuid` PK
`membership_id`	`uuid` NOT NULL	FK CASCADE on membership delete
`organization_id`	`uuid` NOT NULL	FK CASCADE — denormalized for fast org-scoped reads
`r2_key`	`text` NOT NULL	Original
`thumbnail_r2_key`	`text`	sharp-generated thumb
`mime`	`varchar(100)` NOT NULL
`size_bytes`	`int` NOT NULL
`width` / `height`	`int`	Probed by sharp
`recorded_at`	`date` NOT NULL	Day granularity
`notes`	`text`
`bodyweight_kg`	`numeric(6,2)`	Optional inline snapshot; NOT a `body_metrics` row
`uploaded_by`	`uuid` NOT NULL	FK `users(id)`
`created_at` / `updated_at` / `deleted_at`	`timestamptz`	Soft-delete pattern

Indexes:

progress_photos_membership_recorded_idx on (membership_id, recorded_at) — timeline scan.
progress_photos_org_idx on organization_id.

Multi-org isolation

Both membership_id and organization_id carried. Cascade on both ends.
API gates: requireAccess ensures the requester is either the membership’s owner or staff in the same org.

PII handling

The photo binary is itself sensitive PII (body images, identifiable face, possibly EXIF metadata including GPS).
Stored in the default R2 bucket — not the compliance bucket. No retention guarantees.
Reads are always via presigned 1h URLs. No public URLs ever issued.
EXIF is NOT stripped on upload — known gap; GPS coordinates from phone uploads will land in R2.

Soft / hard delete

Soft via deleted_at. Listing endpoints filter IS NULL.
Binary in R2 is never deleted by app code today (gap).
Membership delete cascades — wipes the row but leaves the binary.

Progress Photos — Code Map Progress Photos — QA Plan