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
Section titled “Conventions baked into the schema”- Timestamps are Unix seconds (
integer), not milliseconds, unless a column comment says otherwise. - Binary columns use the
uint8Blobcustom type (top ofschema.ts). Its TypeScript type isUint8Array;toDrivercopies into a freshUint8Arrayto avoid shared-buffer aliasing, andfromDrivertoleratesUint8Array | ArrayBuffer | number[]because D1’s binding layer is inconsistent aboutArrayBufferacross contexts. Every ciphertext, public key, and wrapped key is auint8Blob. - Booleans are
integer0/1. - Many “foreign” columns deliberately have no FK constraint (
owner_did,photo_idon 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*_didor*_idwithout areferences(), that is intentional — don’t “fix” it by adding an FK without reading why.
The tables, by area
Section titled “The tables, by area”Users, sessions, auth
Section titled “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
Section titled “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
Section titled “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 }, 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. |
Recognition
Section titled “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_idhas no foreign key on purpose (ticket bri-a1c9). ACASCADEwould silently delete a person’s contributed embeddings whenever a photo is removed; aRESTRICTwould 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.
Social, moderation, ops
Section titled “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). 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. |
How the entities relate
Section titled “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:
- The uploader wraps the photo’s content key to each audience circle’s current
epoch_keys.public_key→ rows inphoto_circles. - A viewer unwraps their
wrapped_keysrow for that circle/generation (using their PRF-derived personal key), giving the epoch private key. - They use it to unwrap
photo_circles.wrapped_content_key, then decrypt the R2 blob. - 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
Section titled “The D1 client and access patterns”- Client:
apps/web/src/lib/server/db.tsexportsdb, aProxyover a per-request Drizzle instance.hooks.server.tscallssetRequestDb(event.platform.env.DB)at the start of every request; accessingdbbefore 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 flatstatements[]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; thesrc/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 toUint8Arraybefore 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
Section titled “Migrations”- Location:
apps/web/drizzle/. Files areNNNN_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 applyreads the.sqlfiles directly and tracks applied ones in its own table, ignoringmeta/.) - Authoring: migrations are written by hand. Edit
schema.ts(it drives the Drizzle types anddb:pushfor local iteration), then hand-write the matchingNNNN_slug.sql.drizzle-kit generateis not used — its snapshots stopped at 0009, so it would diff against an ancient schema and emit a destructive migration; there is nodb:generatescript. - 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’smigrate-prodjob applies pending migrations to production D1 and then deploys the worker. For destructive or required-NOT NULLmigrations, 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.