Database schema

D1 database: mit-blincr-db

Database ID: 16ef1537-f3d7-4b8b-a11d-c2e7c2b24abd

Tables

audit_log (migration: schema/001_audit_log.sql)

id            INTEGER PRIMARY KEY AUTOINCREMENT
session_id    TEXT NOT NULL
action        TEXT NOT NULL
participant_id TEXT
detail        TEXT
created_at    TEXT NOT NULL DEFAULT datetime('now')

users (migration: 0001_users.sql + 0002_email_verification.sql)

id                    TEXT PRIMARY KEY (UUID)
email                 TEXT UNIQUE NOT NULL
name                  TEXT NOT NULL
password_hash         TEXT NOT NULL (PBKDF2 salt:hash hex)
email_verified        INTEGER DEFAULT 0
verification_code     TEXT (6-digit, nullable after verification)
verification_expires  TEXT (ISO datetime)
terms_accepted_at     TEXT (ISO datetime)
totp_secret           TEXT (reserved, not yet used)
totp_enabled          INTEGER DEFAULT 0 (reserved)
created_at            TEXT NOT NULL DEFAULT datetime('now')

user_sessions (migration: 0001_users.sql)

token       TEXT PRIMARY KEY (64-char hex)
user_id     TEXT NOT NULL REFERENCES users(id)
expires_at  TEXT NOT NULL (ISO datetime, 30 days from creation)
created_at  TEXT NOT NULL DEFAULT datetime('now')

Indexes: idx_sessions_user(user_id), idx_sessions_expires(expires_at)

cloud_trails (migration: 0001_users.sql)

id            TEXT PRIMARY KEY (UUID)
user_id       TEXT NOT NULL REFERENCES users(id)
trail_id      TEXT NOT NULL
title         TEXT NOT NULL
metadata_json TEXT
folder_path   TEXT DEFAULT '/'
is_public     INTEGER DEFAULT 0
share_token   TEXT UNIQUE
created_at    TEXT NOT NULL DEFAULT datetime('now')
updated_at    TEXT NOT NULL DEFAULT datetime('now')

Indexes: idx_cloud_trails_user(user_id), idx_cloud_trails_share(share_token)

user_credentials (migration: 0003_passkeys.sql)

id            TEXT PRIMARY KEY (UUID)
user_id       TEXT NOT NULL REFERENCES users(id)
credential_id TEXT UNIQUE NOT NULL (base64url WebAuthn credential ID)
public_key    TEXT NOT NULL (base64url-encoded COSE public key)
counter       INTEGER NOT NULL DEFAULT 0 (signature counter for replay prevention)
transports    TEXT (JSON array: ["internal","hybrid","usb","ble","nfc"])
device_name   TEXT DEFAULT 'Passkey'
created_at    TEXT NOT NULL DEFAULT datetime('now')

Indexes: idx_credentials_user(user_id), idx_credentials_cred_id(credential_id)

webauthn_challenges (migration: 0003_passkeys.sql)

id          TEXT PRIMARY KEY (UUID)
challenge   TEXT NOT NULL (base64url challenge string)
user_id     TEXT (nullable — null for authentication challenges)
type        TEXT NOT NULL ('registration' or 'authentication')
expires_at  TEXT NOT NULL (5 minutes from creation)
created_at  TEXT NOT NULL DEFAULT datetime('now')

Index: idx_challenges_expires(expires_at)

Expired challenges are cleaned up opportunistically on each registration/login options request.

Migration sequence

1. schema/001_audit_log.sql — Audit log table
2. migrations/0001_users.sql — Users, sessions, cloud trails tables + indexes
3. migrations/0002_email_verification.sql — Adds email verification columns, terms timestamp, and TOTP placeholders to users
4. migrations/0003_passkeys.sql — WebAuthn credentials and challenge tables

Run locally: wrangler d1 execute mit-blincr-db --local --file=migrations/XXXX.sql Run remote: wrangler d1 execute mit-blincr-db --remote --file=migrations/XXXX.sql

IndexedDB: blincr-trails (extension)

Browser-local database in the Chrome extension. Version 1, two object stores:

trails — Key path: trailId (UUID)

trailId           string (UUID)
title             string (auto-generated from domains)
folderId          string | null
createdAt         number (epoch ms)
updatedAt         number (epoch ms)
duration          number (ms)
siteCount         number
pageCount         number
eventCount        number
domains           string[] (up to 8)
events            BrowseEvent[]
participants      Participant[]
extensionVersion  string
installId         string
syncStatus        "local" | "synced" | "modified"
remoteSessionId   string | null
tags              string[]
description       string

Indexes: createdAt, folderId, title, syncStatus

folders — Key path: folderId (UUID)

folderId   string (UUID)
name       string
parentId   string | null
createdAt  number (epoch ms)
color      string | null

Indexes: parentId, name

R2 bucket: blincr-session-snapshots

Object storage for trail JSON blobs.

• Session trails: trails/{sessionId}.json — Raw session trail data
• User trails: users/{userId}/trails/{trailId}.json — User-uploaded trail data

Each object is stored as application/json with the full trail including events, participants, and metadata.