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, which has a type discriminator:

• circle — a sharing audience. Photos are linked to circles to grant access.
• person — a recognised individual. Holds that person’s face slices, embeddings, and recognition pool. Every user has a self_person_group_id pointing at their own person-group.

Both types share groups and group_members.

Table	Purpose	Notes
groups	Circles and persons.	type ∈ `circle
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	An encrypted crop of one face, owned by one person-group.	bounding_box JSON, encrypted_slice blob, epoch_generation.
photo_face_intersections	Joint-access slices needing two groups to decrypt.	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.
person_centroids	Legacy centroid seed.	Still present for the migration path; pools superseded it.
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. Defined in migration 0013, not in schema.ts — managed via raw SQL in rate-limiter.ts.

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 / person_centroids
                 └─ 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/, with the journal in drizzle/meta/_journal.json (drizzle-kit, sqlite dialect). Files are NNNN_slug.sql, strictly sequential. The latest at time of writing is 0042.
• Generate: pnpm db:generate (drizzle-kit) after editing schema.ts.
• 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.

Interim production caveat. GitHub Actions minutes are exhausted, so the migrate-prod CI job does not run, but Cloudflare still deploys code. Merging a migration therefore deploys code against a stale production schema until someone runs cd apps/web && pnpm db:migrate:remote by hand. Until Actions return, treat every merged migration as needing that manual step immediately after deploy, and verify with wrangler d1 migrations list brightblur --remote --env production.