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.

Conventions baked into the schema

• 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.

The tables, by area

Users, sessions, auth

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.

Groups and membership

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

Photos, circles, faces

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 }; 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; split-key wrapped_half_a / wrapped_half_b; CHECK group_a_id < group_b_id enforces canonical pair ordering.

Recognition

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.

Keys

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.

Social, moderation, ops

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).
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.

How the entities relate

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.

The D1 client and access patterns

• 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.

Migrations

• Location: apps/web/drizzle/. Files are NNNN_slug.sql, strictly sequential; the latest is 0052. (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.

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