Skip to content

Data Model

BrightBlur’s database is Cloudflare D1 (SQLite) accessed through Drizzle ORM. The schema is a single file — apps/web/src/lib/server/schema.ts — and everything in this chapter is derived from it. If the schema and this page ever disagree, the schema wins; tell whoever owns the docs.

  • Timestamps are Unix seconds (integer), not milliseconds, unless a column comment says otherwise.
  • Binary columns use the uint8Blob custom type (top of schema.ts). Its TypeScript type is Uint8Array; toDriver copies into a fresh Uint8Array to avoid shared-buffer aliasing, and fromDriver tolerates Uint8Array | ArrayBuffer | number[] because D1’s binding layer is inconsistent about ArrayBuffer across contexts. Every ciphertext, public key, and wrapped key is a uint8Blob.
  • Booleans are integer 0/1.
  • Many “foreign” columns deliberately have no FK constraint (owner_did, photo_id on embeddings/comments/reports, created_by, …). Some point at users who may not be registered; some are deletion keys whose cascade behaviour is a product decision made in code rather than by the database. When you see a *_did or *_id without a references(), that is intentional — don’t “fix” it by adding an FK without reading why.
Table Purpose Notes
users One row per account. PK did. email unique; self_person_group_id points at the person-group that represents this user’s own face.
sessions Active sessions. FK → users cascade; family_id groups sessions for bulk revocation; revoked_at nullable.
user_credentials Password hash (1:0–1 with users).
passkey_credentials WebAuthn credentials. public_key blob, counter, optional label (declared last to avoid a column-shift hazard).
passkey_login_challenges / passkey_register_challenges One-shot WebAuthn challenges. Consumed via CAS (see Encryption & Keys); pruned by expires_at.
email_verification_tokens / password_reset_tokens Email/PW flows.
user_encryption_keys The user’s personal keypair + recovery material. public_key, prf_wrapped_seed (FIDO2-PRF-wrapped master seed), prf_salt_input.

Everything shareable hangs off groups. There is no type column — since migration 0049 every group is a person-group: an access boundary around one individual’s faces, holding their face slices, embeddings, and recognition pool. Every user has a self_person_group_id pointing at their own. Photos are shared by linking them to person-groups through photo_circles (the column is still named circle_group_id, so “circle” survives only as the audience-link, not a group type).

Table Purpose Notes
groups One person-group per individual. id, name, created_by (no FK), created_at. No type column (dropped in 0049).
group_members Composite PK (group_id, user_did). role ∈ `admin
Table Purpose Notes
photos One row per published photo. base_image_key / thumb_image_key are R2 object keys; idempotency_key + the unique (owner_did, idempotency_key) index backstop concurrent publish retries.
photo_circles Which circles can view a photo. PK (photo_id, circle_group_id). wrapped_content_key = the photo’s content key wrapped to that circle’s epoch key — one row per audience.
face_slices One sealed BBP face entity, selected by one person-group ACL. encrypted_slice contains { bbox, landmarks?, epoch, pixels }, but only on older rows: from migration 0068 new rows hold a zero-length body and a container_entity_index pointing at the entity inside the photo’s R2 container; wrapped_key and uploader_wrapped_key release its DEK to the group or uploader. No plaintext geometry column.
photo_face_intersections A sealed BBP entity needing two groups to decrypt. encrypted_blob contains geometry and pixels on older rows (newer rows reference the entity with container_entity_index, as for face_slices); split-key wrapped_half_a / wrapped_half_b; CHECK group_a_id < group_b_id enforces canonical pair ordering.
Table Purpose Notes
person_embeddings One ArcFace embedding contributed to a person-group. face_slice_id (client-minted UUID, no FK), photo_id (no FK — the untag deletion key), incorporated 0/1, contributed_by_did nullable (so account deletion can anonymise rather than delete). Indexed on (person_group_id, incorporated) and (photo_id, person_group_id).
person_pools The active per-identity template pool. Whole pool sealed as one hybrid envelope (encrypted_pool); template_count, epoch_generation.
personal_negative_stores Per-user hard-negative embeddings, one opaque ciphertext row. No plaintext person_group_id — the association lives inside the ciphertext.

person_embeddings.photo_id has no foreign key on purpose (ticket bri-a1c9). A CASCADE would silently delete a person’s contributed embeddings whenever a photo is removed; a RESTRICT would block photo deletion. Both are wrong, so cascade is handled explicitly in code: untagging (removePersonFromPhoto) and photo deletion both delete embeddings by (person_group_id, photo_id) and reset the affected pool. Any new path that removes photos or tags must replicate that batch — the DB will not do it for you. See Recognition.

Table Purpose Notes
epoch_keys Per-group, per-generation public key. PK (group_id, generation).
wrapped_keys The epoch private key, wrapped per member. PK (group_id, generation, user_did). Each member who can decrypt the group gets one row.
key_rotation_requests Tracks in-flight rotations.

The full key model is its own chapter — see Encryption & Keys.

Table Purpose
notifications Per-recipient; polymorphic type + reference_id.
access_requests A request to be granted a person-group’s face slice.
comments body_ciphertext blob (comments are encrypted); compound index (photo_id, created_at, id) for keyset pagination.
invites / invite_uses Invite links (token, max_uses, revoked) and per-user redemption.
reports Safeguarding reports — carry an encrypted copy of the base image + slices so moderators can act; one per (photo_id, reporter_did). The encrypted image lives in R2 under reports/<id> and the row keeps its media_key; older rows keep the image inline.
blob_access_log / audit_logs / background_task_failures R2 access audit, action audit, and failed background-job records.
push_subscriptions Web Push endpoints.
merge_operations Tracks in-progress person-group merges.
rate_limits Fixed-window counters. Declared in schema.ts for visibility, but read/written with raw atomic SQL in server/rate-limiter.ts (Drizzle has no portable upsert-and-return); the table is created in migration 0013.
users ─┬─ user_credentials / user_encryption_keys / personal_negative_stores (1:0–1)
├─ sessions / passkey_credentials / push_subscriptions / notifications (1:N)
└─ self_person_group_id ┄┄→ groups (pointer, no FK)
groups (circle) ─┬─ group_members (N:M with users)
├─ epoch_keys / wrapped_keys
├─ photo_circles ┄┄→ photos
└─ invites
groups (person) ─┬─ group_members
├─ epoch_keys / wrapped_keys
├─ face_slices
├─ person_embeddings / person_pools
└─ access_requests
photos ─┬─ photo_circles (who can view)
├─ face_slices (detected faces)
├─ photo_face_intersections (joint-access slices)
├┄ person_embeddings.photo_id (linkage, NO FK)
├┄ comments.photo_id (linkage, no FK)
└┄ reports.photo_id (linkage, no FK)

The end-to-end access flow for a photo:

  1. The uploader wraps the photo’s content key to each audience circle’s current epoch_keys.public_key → rows in photo_circles.
  2. A viewer unwraps their wrapped_keys row for that circle/generation (using their PRF-derived personal key), giving the epoch private key.
  3. They use it to unwrap photo_circles.wrapped_content_key, then decrypt the R2 blob.
  4. Face slices follow the same pattern per person-group; intersections split the key into two halves, each wrapped to a different group.
  • Client: apps/web/src/lib/server/db.ts exports db, a Proxy over a per-request Drizzle instance. hooks.server.ts calls setRequestDb(event.platform.env.DB) at the start of every request; accessing db before that throws.
  • Atomicity: D1 has no interactive transactions. The substitute is asD1Db(db).batch(statements) (apps/web/src/lib/server/api/d1.ts) — a list of prepared statements sent as one atomic call. Multi-row writes (create group + epoch key + members + wrapped keys) are assembled into a flat statements[] array and batched. Statement order within a batch is often load-bearing (e.g. deletes before un-incorporation in the untag path).
  • Logic placement: business logic lives in apps/web/src/lib/server/api/*.ts; the src/routes/api/ handlers are thin wrappers that parse/validate, call the API module, and serialise. Binary columns are base64 at the wire boundary (api/encoding.ts), decoded to Uint8Array before they touch Drizzle.
  • Pagination: keyset cursors (api/keyset-cursor.ts) backed by compound indexes co-located with each table.
  • Testing: api/test-helpers.ts (createTestDb) spins up Miniflare’s in-memory D1 — the same .batch() semantics as production, no network — so API tests exercise real SQLite.
  • Location: apps/web/drizzle/. Files are NNNN_slug.sql, strictly sequential; the latest is 0068. (drizzle/meta/ holds drizzle-kit’s snapshot journal, but it is vestigial — frozen at 0009 — because migrations here are hand-authored, not generated. wrangler d1 migrations apply reads the .sql files directly and tracks applied ones in its own table, ignoring meta/.)
  • Authoring: migrations are written by hand. Edit schema.ts (it drives the Drizzle types and db:push for local iteration), then hand-write the matching NNNN_slug.sql. drizzle-kit generate is not used — its snapshots stopped at 0009, so it would diff against an ancient schema and emit a destructive migration; there is no db:generate script.
  • Apply locally: pnpm db:migrate (against the Miniflare SQLite file). Fresh worktrees have an empty local D1 — run this before the e2e suite or global-setup fails with “no such table”.
  • Apply to production: cd apps/web && pnpm db:migrate:remote.
  • Destructive drops are acceptable pre-release — there is no production data to preserve at these migration points, so table rebuilds and wholesale DELETEs are used freely.

Recent migrations worth knowing:

# What it did
0040 Dropped photos.visibility — access is entirely via photo_circles.
0041 Wiped all recognition state (person_pools, person_embeddings, person_centroids, personal_negative_stores) for the embedder swap to uniface-w600k-mbf-1 — old vectors live in an incompatible space.
0042 Rebuilt person_embeddings with photo_id NOT NULL (no FK — the untag deletion key) and cleared person_pools.
0043 Dropped person_centroids — the legacy single-centroid store, retired in favour of person_pools.
0044 Added face_slices.encrypted_landmarks (nullable) — encrypted per-slice landmarks so rebuild aligns without re-detecting.
0045 / 0046 Added then dropped personal_positive_stores (reverted — the store was never read).
0047 Added recovery_kit_wrapped_mnemonic.
0048 pending_invite_member_backfill.
0049 Dropped groups.type — general circles retired; every group is now a person-group.
0050–0052 Blob-key, blob-access-log and notifications keyset indexes (query-performance).
0061–0063 Introduced the BBP entity/container schema and temporary one-time migration flags.
0064 Retired the completed legacy-photo migration: dropped its flags and the old plaintext geometry / standalone landmark columns.
0065–0067 Added recent_auth_grants (single-use fresh-identity grants), share_capability_proof_uses (one-use link proofs) and users.pending_email.
0068 Added container_entity_index to face_slices and photo_face_intersections, and media_key (with an index) to reports. Additive only: nothing is rebuilt, backfilled or deleted. D1 rejects rows over 2,000,000 bytes, so new face and overlap rows reference their entity in the photo’s immutable R2 container instead of copying its body, and report images go to R2. Every reader handles both shapes.

Production migration flow. On push to main, CI’s migrate-prod job applies pending migrations to production D1 and then deploys the worker. For destructive or required-NOT NULL migrations, apply by hand and deploy in the right order before merging — the old worker would otherwise briefly run against the new schema. Full detail on Operational Status & Gates.