Database Schema

D1 — mit-blincr-db

Binding: AUDIT_DB. Database ID: 16ef1537-f3d7-4b8b-a11d-c2e7c2b24abd.

Tables

users

Column	Type	Notes
id	TEXT PK	UUID
email	TEXT	UNIQUE, stored lowercase
name	TEXT	Display name
password_hash	TEXT	PBKDF2-SHA256 (100k iterations)
email_verified	INTEGER	0 or 1
verification_code	TEXT	6-digit code, nullable
verification_expires	TEXT	ISO datetime
terms_accepted_at	TEXT	ISO datetime
totp_secret	TEXT	Reserved, not yet implemented
totp_enabled	INTEGER	Reserved, default 0
created_at	TEXT	Auto datetime('now')

user_sessions

Column	Type	Notes
token	TEXT PK	64-char hex
user_id	TEXT FK	References users(id)
expires_at	TEXT	30 days from creation
created_at	TEXT	Auto datetime('now')

Indexes: idx_sessions_user, idx_sessions_expires.

cloud_trails

Column	Type	Notes
id	TEXT PK	UUID
user_id	TEXT FK	References users(id)
trail_id	TEXT	Local trail ID from extension
title	TEXT
metadata_json	TEXT	JSON blob
folder_path	TEXT	Default /
is_public	INTEGER	Default 0
category	TEXT	Optional publish category
visibility	TEXT	Tier: t1/t2/t3_self/t3 (default t3_self)
is_agent	INTEGER	Default 0 — agent-session trail (E13.4), excluded from feed/explore/search
share_token	TEXT	UNIQUE, nullable
created_at	TEXT	Auto datetime('now')
updated_at	TEXT	Auto datetime('now')

Indexes: idx_cloud_trails_user, idx_cloud_trails_share.

user_credentials (WebAuthn)

Column	Type	Notes
id	TEXT PK	UUID
user_id	TEXT FK	References users(id)
credential_id	TEXT	UNIQUE, base64url from WebAuthn
public_key	TEXT	Base64url encoded
counter	INTEGER	Signature counter
transports	TEXT	JSON array, nullable
device_name	TEXT	Default 'Passkey'
created_at	TEXT	Auto datetime('now')

Indexes: idx_credentials_user, idx_credentials_cred_id.

webauthn_challenges

Column	Type	Notes
id	TEXT PK	UUID
challenge	TEXT	Base64url challenge string
user_id	TEXT	Nullable (auth challenges omit)
type	TEXT	'registration' or 'authentication'
expires_at	TEXT	5 minutes from creation
created_at	TEXT	Auto datetime('now')

Index: idx_challenges_expires.

audit_log

Column	Type	Notes
id	INTEGER PK	AUTOINCREMENT
session_id	TEXT	DO session ID
action	TEXT	Event type or lifecycle
participant_id	TEXT	Who triggered it
detail	TEXT	JSON, nullable
created_at	TEXT	ISO datetime

Indexes: idx_audit_session, idx_audit_participant, idx_audit_created.

user_profiles

Column	Type	Notes
user_id	TEXT PK	References users(id)
username	TEXT	UNIQUE, URL-safe public handle
display_name	TEXT	Public display name
bio	TEXT	Nullable, 280-char UI limit
avatar_key	TEXT	Nullable R2 key under avatars/
cover_color	TEXT	Hex color, default #2563eb
is_business	INTEGER	0 or 1, business profile flag
business_verified	INTEGER	0 or 1, verified badge state
brand_name	TEXT	Nullable public brand name
support_url	TEXT	Nullable public support link
knowledge_base_url	TEXT	Nullable public documentation link
created_at	TEXT	Auto datetime('now')
updated_at	TEXT	Auto datetime('now')

Index: idx_user_profiles_username.

pinned_trails

Column	Type	Notes
user_id	TEXT	References users(id)
trail_id	TEXT	References cloud_trails(id)
position	INTEGER	0-based display position, max 5 in API
created_at	TEXT	Auto datetime('now')

Primary key: (user_id, trail_id). Unique index: idx_pinned_trails_position on (user_id, position).

follows

Column	Type	Notes
follower_id	TEXT	References users(id)
following_id	TEXT	References users(id)
created_at	TEXT	Auto datetime('now')

Primary key: (follower_id, following_id). Indexes: idx_follows_follower on (follower_id, created_at DESC) and idx_follows_following on (following_id, created_at DESC).

trail_reactions

Column	Type	Notes
id	TEXT PK	UUID
trail_token	TEXT	Share token of the public trail
user_id	TEXT	References users(id)
reaction_type	TEXT	useful, interesting, saved
page_url	TEXT	Empty for trail-level reactions
created_at	TEXT	Auto datetime('now')

Unique constraint: (trail_token, user_id, reaction_type, page_url).

trail_comments

Column	Type	Notes
id	TEXT PK	UUID
trail_token	TEXT	Share token of the public trail
user_id	TEXT	References users(id)
body	TEXT	Comment text
page_url	TEXT	Optional page context
created_at	TEXT	Auto datetime('now')

trail_reviews

Column	Type	Notes
id	TEXT PK	UUID
trail_token	TEXT	Share token of the public trail
user_id	TEXT	References users(id)
page_url	TEXT	Rated page URL
rating	INTEGER	1 through 5
created_at	TEXT	Auto datetime('now')
updated_at	TEXT	Auto datetime('now')

Unique constraint: (trail_token, user_id, page_url).

trail_views

Column	Type	Notes
id	TEXT PK	UUID per visit
trail_token	TEXT	Share token of the public trail
viewer_key	TEXT	Pseudonymous viewer identifier
viewer_user_id	TEXT	Nullable, when the viewer is signed in
referrer	TEXT	Referrer URL or host
country_code	TEXT	Aggregated country code
language	TEXT	Aggregated language preference
completion_ratio	REAL	0 to 1 view completion estimate
max_scroll_depth	REAL	0 to 1 max scroll depth
created_at	TEXT	Auto datetime('now')
updated_at	TEXT	Auto datetime('now')

Unique key is the visit id; unique viewers are derived from viewer_key and viewer_user_id.

collections

Column	Type	Notes
id	TEXT PK	UUID
user_id	TEXT	References users(id)
name	TEXT	Collection name
description	TEXT	Nullable
is_public	INTEGER	0 or 1
created_at	TEXT	Auto datetime('now')
updated_at	TEXT	Auto datetime('now')

collection_trails

Column	Type	Notes
collection_id	TEXT	References collections(id)
trail_token	TEXT	Share token of a public trail
added_by_user_id	TEXT	References users(id)
created_at	TEXT	Auto datetime('now')

Primary key: (collection_id, trail_token).

collection_members

Column	Type	Notes
collection_id	TEXT	References collections(id)
user_id	TEXT	References users(id)
role	TEXT	member or owner-style role
added_by_user_id	TEXT	References users(id)
created_at	TEXT	Auto datetime('now')

Primary key: (collection_id, user_id).

challenges

Column	Type	Notes
id	TEXT PK	UUID
creator_user_id	TEXT FK	References users(id)
title	TEXT	Challenge prompt
description	TEXT	Nullable
category	TEXT	Optional trail category
deadline	TEXT	Nullable ISO date
created_at	TEXT	Auto datetime('now')
updated_at	TEXT	Auto datetime('now')

Indexes: idx_challenges_updated, idx_challenges_deadline.

challenge_entries

Column	Type	Notes
id	TEXT PK	UUID
challenge_id	TEXT FK	References challenges(id)
trail_token	TEXT	Share token of a public trail
user_id	TEXT FK	References users(id)
created_at	TEXT	Auto datetime('now')

Unique constraint: (challenge_id, trail_token). Indexes: idx_challenge_entries_challenge, idx_challenge_entries_trail.

challenge_votes

Column	Type	Notes
id	TEXT PK	UUID
entry_id	TEXT FK	References challenge_entries(id)
user_id	TEXT FK	References users(id)
vote	INTEGER	Currently stored as 1
created_at	TEXT	Auto datetime('now')

Unique constraint: (entry_id, user_id).

notifications

Column	Type	Notes
id	TEXT PK	UUID
user_id	TEXT	Notification recipient
actor_user_id	TEXT	Nullable actor
kind	TEXT	follow/reaction/comment/collection
target_type	TEXT	user/trail
target_ref	TEXT	User ID or share token
title	TEXT	Short headline
body	TEXT	Detail text
is_read	INTEGER	0 or 1
created_at	TEXT	Auto datetime('now')

notification_preferences

Column	Type	Notes
user_id	TEXT PK	References users(id)
weekly_digest	INTEGER	0 or 1, defaults to enabled
push_enabled	INTEGER	0 or 1
updated_at	TEXT	Auto datetime('now')

Migrations

All schema is in migrations/ (apply with npm run db:migrate:remote, i.e. wrangler d1 migrations apply mit-blincr-db --remote).

File	Contents
migrations/0001_users.sql	users, user_sessions, cloud_trails tables
migrations/0002_email_verification.sql	Add verification/terms/TOTP columns to users
migrations/0003_passkeys.sql	user_credentials, webauthn_challenges
migrations/0004_mup_integration.sql	Add users.mup_account_id (+ index)
migrations/0005_entitlement_cache.sql	entitlement_cache table
migrations/0006_audit_log.sql	audit_log table (was schema/001)
migrations/0007_user_profiles.sql	user_profiles, pinned_trails tables
migrations/0008_trail_categories.sql	cloud_trails.category column and indexes
migrations/0009_follows.sql	follows table and lookup indexes
migrations/0010_social_engagement.sql	reactions, comments, collections, notifications
migrations/0011_page_reactions_and_collab_collections.sql	Adds trail_reactions.page_url, collection_members, and notification_preferences
migrations/0012_reaction_scope_and_notification_digest.sql	Rebuilds trail_reactions with non-null page_url and updated uniqueness
migrations/0013_trail_challenges.sql	challenges, challenge_entries, challenge_votes tables
migrations/0014_trail_reviews.sql	trail_reviews page-rating table
migrations/0015_trail_views.sql	trail_views creator analytics table
migrations/0016_business_profiles.sql	Business metadata + verified badge on profiles
migrations/0017_user_webhooks.sql	user_webhooks outbound webhook config
migrations/0018_user_slack_integrations.sql	user_slack_integrations
migrations/0019_user_discord_integrations.sql	user_discord_integrations
migrations/0020_blincrs.sql	blincrs, blincr_checks, blincr_alerts — standing page watchers
migrations/0021_trail_visibility.sql	visibility tier column on cloud_trails (t1/t2/t3_self/t3) — t1/t2 sat unused until E12.3 (2026-07-18) gave them a real upload/share path, no new migration needed
migrations/0022_agent_trails.sql	is_agent flag on cloud_trails (E13.4 agent audit trails)
migrations/0023_trail_safety_scans.sql	trail_safety_scans — Web Risk scan audit log via marloc-google-webrisk-worker (E14.2 share safety gate)
migrations/0024_trail_safety_scans_type.sql	scan_type column + extended verdict CHECK (table rebuild) — E12.4 PII publish gate shares the audit table
migrations/0025_account_deletion.sql	users.deletion_requested_at / deletion_scheduled_at — E12.5 erasure orchestration grace window
migrations/0026_trail_reports.sql	trail_reports — E12.6.1 abuse & safety report/takedown queue
migrations/0027_trail_report_reveal.sql	trail_reports.revealed_content — E12.6.2 T2 reporter-side reveal

Trail reports (0026/0027, E12.6.1/.2)

trail_reports has no FK to cloud_trails — deliberately, so a report survives the trail being unpublished/revoked or deleted and the admin queue can still show what was actioned. resolveReport() in src/moderation.ts flips status to actioned (unpublish a T3 trail, or revoke a T2 share token) or dismissed, and eraseAccount() (E12.5) auto-dismisses any open report on a trail it deletes. Admin access to the queue (/api/admin/trail-reports*) is gated by isAdmin(), which checks the signed-in user's email against the ADMIN_EMAILS worker secret (comma-separated allowlist, empty by default).

revealed_content (nullable TEXT, 0027): a T2 (E2EE) report can optionally carry a digest the reporter's own browser decrypted and chose to include — blincr never decrypts anything itself, so this is the only way T2 content ever reaches the report queue, and only with the reporter's consent (app/routes/p.$token.tsx's reveal checkbox — unchecked, no field is sent at all). Truncated to 20,000 chars on write (MAX_REVEALED_CONTENT_CHARS in src/moderation.ts).

Account deletion (0025, E12.5)

users.deletion_requested_at / deletion_scheduled_at back the grace-window account-delete flow in src/erasure.ts: requesting deletion sets both to now / now+14 days, cancelling clears both. A daily cron (wrangler.jsonc triggers.crons, workers/handler.ts scheduled) finds accounts whose deletion_scheduled_at has passed and runs the full ledger-driven erasure walk (eraseAccount) — an explicit, ordered sequence of D1 deletes/updates and R2 blob deletes across every table in src/data-ledger.ts, since most FKs here don't declare ON DELETE CASCADE and D1 does enforce FKs.

Blincr tables (0020)

• blincrs — one row per watcher: page_url, label, extractor_kind (css/regex/jsonld), extractor_value, comparator (changed/lt/gt/appeared/disappeared), threshold, interval_bucket (5m/30m/6h/daily), status (active/paused/sight_lost), last_value, next_check_at, consecutive_failures, cooldown_until. Optional trail_token anchor. Index idx_blincrs_due(status, next_check_at) drives the watch worker's due-selection.
• blincr_checks — append-only check history (ok, value, error); pruned/rolled up after 30 days (lifecycle per MASTER-PLAN §3.3).
• blincr_alerts — raised alerts (old_value, new_value, message), mirrored into notifications (kind='blincr_alert').

trail_safety_scans (0023 + 0024, E14.2 + E12.4)

One row per scan attempt on POST /api/user/trails/:id/share — run before the share token is minted, whether the scan passes or blocks. scan_type (webrisk | pii, added 0024) distinguishes the two checks that run in the same publish flow. verdict (clean / blocked / acknowledged / error — acknowledged added 0024 for a PII finding the user confirmed and published anyway), checked_url_count (URL count for webrisk, match count for pii), flagged_json (webrisk: array of {url, threatTypes}; pii: array of {category, context} — populated whenever something was found, not only on blocked), provider (google_safe_browsing for webrisk rows; unused for pii rows). Index idx_trail_safety_scans_trail. This is the only record of a blocked or PII-flagged share — the row survives even though no share_token was ever set (or was set later, after acknowledgment).

Migration 0024 also extends the verdict CHECK constraint (clean/blocked/acknowledged/error) — SQLite can't ALTER a CHECK in place, so this migration rebuilds the table (create-new, copy, drop, rename) rather than a simple ADD COLUMN.

Durable Object — SessionCoordinator

Uses DO SQLite storage (new_sqlite_classes migration tag v1).

Stores session state under key "session":

• sessionId, ownerId, isLive, currentUrl, createdAt
• participants — Map of id → { id, name, role, joinedAt }
• trail — Array of BrowseEvent
• extensionVersion, installId

IndexedDB — blincr-trails (extension)

Database version 3. Runs in the extension's background context. v2 (E17) added the four crawl stores; v3 (E15.11) adds overlays. Every step is purely additive and guarded by a contains check, so an existing database keeps everything already in it.

Object Stores

trails (keyPath: trailId)

Fields: trailId, title, folderId, createdAt, updatedAt, duration, siteCount, pageCount, eventCount, domains[], events[], participants[], extensionVersion, installId, syncStatus (local|synced|modified), remoteSessionId, tags[], description.

Indexes: createdAt, folderId, title, syncStatus.

folders (keyPath: folderId)

Fields: folderId, name, parentId, createdAt, color.

Indexes: parentId, name.

crawlRecipes (keyPath: recipeId) — v2

Fields: recipeId, label, listUrl (filters included — the filters are the recipe), origin, pagination, paginationSelector, pageBudget, rowBudget, politeness{minDelayMs,maxDelayMs}, authorisedSite, rowSelector, rowCountAtAuthoring, fields[], detailFields[], imageMode, createdAt, updatedAt.

A field is {machineName, friendlyName, languageCode, selector, xpath?, attribute, attributeName?, transform, required, isKey, isDetailLink?, isImage?}. machineName is the JSON key, friendlyName the column header, languageCode tags the captured value's language. xpath is a fallback consulted only when the CSS selector matches nothing at run time.

Indexes: listUrl, updatedAt.

crawlRuns (keyPath: runId) — v2

Fields: runId, recipeId, startedAt, updatedAt, finishedAt?, phase, pagesExhausted, rowsDiscovered, rowsDetailed, rowsFailed, rowsAdded?, rowsChanged?, rowsGone?, resume{phase,pagesExhausted,lastCompletedIdentity, discoveredIdentities[]}, trailId, basedOnRunId?, error?, errorId?.

Indexes: recipeId, startedAt.

crawlRows (keyPath: [runId, identity]) — v2

Fields: runId, identity, identitySource, ordinal, revisitUrl, list{}, detail{}|null, images[], state (listed|detailed|failed|gone), error?, errorId?, listedAt, detailedAt?, goneAt?.

The compound key is what makes "every row of this run" a range query, and what lets one row be rewritten in place after every single detail fetch — the write that makes resume possible.

Index: runId.

crawlImages (keyPath: hash) — v2

Fields: hash (FNV-1a of the bytes plus length), url, mime, bytes, blob.

Content-hash keyed so the same brand shot reused across hundreds of rows is stored once. Written only when the recipe's imageMode is download and the build carries unlimitedStorage.

overlays (keyPath: overlayId) — v3

Fields: overlayId, pluginId, url, position (the §4.5 anchor model), data (plugin-specific), participantId, authorName?, createdAt, updatedAt.

Written on every overlay create/update before the session gate in sendBrowseEvent, which is what makes an anchored item survive a reload outside a co-browse session. createdAt is preserved across updates so an edited annotation does not sort as new. Indexes: url, updatedAt.

R2 — blincr-session-snapshots

Binding: SESSION_SNAPSHOTS.

Key Layout

Prefix	Content
trails/{sessionId}.json	Full trail snapshot (from DO save)
users/{userId}/trails/{id}.json	User-uploaded trail data — shape depends on the row's cloud_trails.visibility (below)
avatars/{userId}	Public profile avatar image

users/{userId}/trails/{id}.json shape by tier (E12.3): for t3/t3_self this is a plaintext TrailData object (events, participants, ...). For t1/t2 it is instead an encrypted envelope {v: 1, iv, ciphertext} (both base64) — the server only ever writes/reads this opaque shape and cannot parse it as a trail; cloud_trails.visibility is what tells every reader which shape to expect, so the two never get cross-parsed. Written by src/backup.ts (upsertBackup), encrypted client-side by extension/src/crypto.ts before it ever reaches the server.