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

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 1. Runs in the extension's background context.

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.

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
avatars/{userId}	Public profile avatar image