Data model

Data model

Source of truth for tables, R2 buckets, secrets, and env. architecture.md
only mentions table names inline; this doc carries columns, indexes, and
storage key conventions. If packages/db/src/schema.ts changes, update
this doc in the same PR.

Storage surfaces

Database (libSQL)

• Local SQLite file at /opt/brioso/shared/data/local.db, opened via
  @libsql/client against DATABASE_URL=file:/opt/brioso/shared/data/local.db.
• Single node, single file. Drizzle Gateway is a read-only viewer (second WAL
  reader). No Turso today — the earlier infisical-env-guide.md line
  “prod uses Turso” is inaccurate; correct or remove it. Migrating to Turso
  managed would still be a single DATABASE_URL swap (web keeps the same
  libsql://... URL); not planned.

Object storage — compute path

• Push-only (BRI-439) — two runtime kinds: the home GPU daemon
  (apps/worker/, pm2-supervised, POST /jobs push) and the Modal app
  (apps/modal-worker, FunctionCall generate writes straight to R2 via
  the payload’s presigned URLs). Both upload to R2 (brioso-jobs prod
  bucket) and report completion via the writeback path. See
  apps/worker/AGENTS.md and .agents/docs/gpu-gateway-canary.md.

Object storage — dev stand-in (local-only)

• Garage S3 container at :3900 (brioso-jobs bucket, garage region),
  reached via R2_ENDPOINT/R2_FORCE_PATH_STYLE=1/R2_REGION=garage.
• Drop-in for R2 in dev:fake / dev:preview; never used in production.
  See .agents/docs/home-worker-local-run.md for setup.

Object storage — catalog (separate bucket)

• Catalog has its own R2 bucket: brioso-catalog, deliberately separate
  from brioso-jobs per docs/royalty-free-music-decisions.md’s
  “R2 bucket split”. Uses packages/storage/src/catalog-config-bound.ts.

R2 bucket map

Drizzle schema

39 tables in packages/db/src/schema.ts, grouped by domain. Status is
active unless a vestigial/DROP TABLE ponytail marks it deprecated.

Jobs

jobs (active)

• PK: id (autoincrement integer)
• FKs: kitId → kits.id
• Indexes: idx_jobs_slug (unique), idx_jobs_state, idx_jobs_runner_home
  (partial: runner='home'), idx_jobs_leased (partial: leasedUntil IS NOT NULL),
  idx_jobs_stripe (unique, partial: stripeSessionId != ''), idx_jobs_message
  (partial), idx_jobs_kit (partial), idx_jobs_thread, idx_jobs_parent,
  idx_jobs_email
• Notable columns: slug, email (no user_id FK; orphan-on-delete),
  state (job lifecycle), runner (runtime id: "home" built-in or a
  gateway-config id; insert default via defaultRunner), paramsJson,
  tracks (JSON blob of Track[] —
  preview + 3 masters per take), threadId, parentJobSlug, traceId /
  traceparent (W3C context; bridges web→worker spans), costCents,
  artifactsPurgedAt (BRI-372 tombstone), createdAt/updatedAt (nowIso)

jobEvents (active)

• PK: id (autoincrement integer)
• FKs: none (audit log keyed by slug, not by id)
• Indexes: idx_job_events_slug
• Notable columns: slug, fromState, toState, actor,
  fieldsTouched (JSON list), reason, traceId, createdAt (nowIso)
• Append-only audit log; one row per applied state transition.

jobFeedback (deprecated)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: idx_job_feedback_slug
• Notable columns: slug, trackIdx, text, createdAt (nowIso)
• Vestigial — no longer written; Fillout is the feedback system of record.
  DROP TABLE pending a coordinated export to Quino.

Artifacts

kits (active)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: idx_kits_slug (unique), idx_kits_state, idx_kits_stripe
  (unique, partial: stripeSessionId != '')
• Notable columns: slug, agencyEmail, agencyName, clientBrandName,
  briefJson / analysisJson / strategyJson (3-stage JSON blobs),
  state (analyzing → strategy_ready → approved → generating →
  ready), zipKey (R2 key for deliverable), visionCostCents,
  stripeSessionId, paid (boolean), paidAt, createdAt/updatedAt

kitCheckoutSessions (active)

• PK: id (autoincrement integer)
• FKs: kitId → kits.id
• Indexes: idx_kit_checkout_sessions_session (unique on
  stripeSessionId), idx_kit_checkout_sessions_kit
• Notable columns: stripeSessionId, createdAt (nowIso)
• One row per Stripe Checkout session ever minted for a kit — full
  reconciliation trail, not just the paying session.

Workers

workers (active)

• PK: id (text)
• FKs: none
• Indexes: none
• Notable columns: lastHeartbeat, model, state (default idle)

evalRuns (active)

• PK: id (autoincrement integer)
• FKs: jobId → jobs.id (nullable); promptId → prompts.id (loose ref)
• Indexes: none
• Notable columns: traceId, runpodWorkerId, score (real), notes,
  createdAt (nowIso)

prompts (active)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: none
• Notable columns: version, body, status (default draft),
  createdAt (nowIso)

Ops / alerts

alerts (active)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: idx_alerts_dedupe_key_unresolved (unique partial:
  resolved = 0 AND dedupeKey IS NOT NULL)
• Notable columns: level (default info), message, resolved
  (int 0/1), dedupeKey (nullable upsert key), createdAt (nowIso)
• Stopped the BRI-57 watchdog from inserting a fresh row every tick.

handledMessages (active)

• PK: messageId (text)
• FKs: none
• Indexes: none
• Notable columns: createdAt (nowIso)
• At-most-once dedup ledger for inbound webhook messages.

Users / auth

users (active)

• PK: id (text UUID, $defaultFn(() => crypto.randomUUID()))
• FKs: none
• Indexes: idx_users_email (unique)
• Notable columns: email, name, country, aiExperience,
  useCase, discoveryChannel, discoveryChannelOther,
  onboardingCompletedAt, createdAt/lastSeenAt (timestamp mode,
  $defaultFn(() => new Date()))

emailVerifications (active)

• PK: id (text UUID)
• FKs: none (keyed on email)
• Indexes: idx_email_verifications_email
• Notable columns: email, otpHash (bcrypt; plaintext never stored),
  expiresAt, attempts, consumedAt, createdAt (timestamp mode)

sentEmails (active)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: idx_sent_emails_slug_kind_gen (unique on
  slug,kind,gen)
• Notable columns: slug, kind ("proposal_ready" | "receipt" | …),
  gen, createdAt (nowIso)
• At-most-once send ledger; sendOnce() does INSERT ON CONFLICT DO NOTHING.

shareLinks (active)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: idx_share_links_token (unique)
• Notable columns: token, jobSlug, scope ("kit" | "track"),
  trackIdx, viewCount, createdAt (nowIso)
• Scoped, non-revocable share links. No expires_at by design.

userPreferences (active)

• PK: userId (text)
• FKs: userId → users.id
• Indexes: none (PK is userId)
• Notable columns: preferencesJson (JSON string[] of inspectable
  prefs), summary, source ("summary" | "manual"), createdAt/updatedAt
• Tier-2 memory — explicit, user-visible preference rows.

Billing

subscriptions (active)

• PK: userId (text)
• FKs: userId → users.id
• Indexes: idx_subscriptions_user (unique), idx_subscriptions_stripe_subscription
  (unique on stripeSubscriptionId)
• Notable columns: stripeCustomerId, stripeSubscriptionId,
  plan ("monthly" | "annual"), status (mirrors Stripe lifecycle
  string), priceCentsLocked (early-adopter price lock),
  currentPeriodEnd, cancelAtPeriodEnd (boolean), createdAt/updatedAt
• One row per Pro subscription, upserted on (re)subscribe.

proCreditUsage (active)

• PK: composite (userId, month)
• FKs: userId → users.id
• Indexes: idx_pro_credit_usage_user_month (unique)
• Notable columns: creditsUsed, updatedAt (nowIso)
• Month-bucketed counter (month = "YYYY-MM").

creatorRefinementUsage (active)

• PK: composite (userId, periodKey)
• FKs: userId → users.id
• Indexes: idx_creator_refinement_usage_user_period (unique)
• Notable columns: count, updatedAt (nowIso)
• OpenRouter refinement allowance; period-keyed (typically month).

processedStripeEvents (active)

• PK: eventId (text)
• FKs: none
• Indexes: none
• Notable columns: createdAt (nowIso)
• Webhook idempotency ledger for customer.subscription.* lifecycle events.

promoCodes (active)

• PK: id (autoincrement integer)
• FKs: none
• Indexes: idx_promo_codes_code (unique), idx_promo_codes_active
• Notable columns: code, kind ("pilot" | "topup" | "trial"),
  generations, durationDays (trial only), active (boolean),
  maxRedemptions (null = unlimited), redemptionCount (atomic counter),
  notes, createdAt/updatedAt

grants (active)

• PK: id (autoincrement integer)
• FKs: userId → users.id
• Indexes: idx_grants_user, idx_grants_user_expires,
  idx_grants_user_code_unique (unique on userId,code)
• Notable columns: code (redeemed promo or "trial:cohort-a" synthetic;
  not FK), generations, planOverride ("pro" | null), expiresAt,
  createdAt (nowIso)
• One row per redeemed/direct grant; entitlement resolves by stacking.

freeGenerations (active)

• PK: composite (keyType, keyValue, day)
• FKs: none
• Indexes: idx_free_generations_key_day (unique)
• Notable columns: count, updatedAt (nowIso)
• Daily free-preview counter keyed on normalized email OR client IP
  (keyType discriminates; both caps enforced independently).

Catalog

catalogTracks (active)

• PK: id (text UUID)
• FKs: none
• Indexes: idx_catalog_tracks_slug (unique), idx_catalog_tracks_genre,
  idx_catalog_tracks_mood, idx_catalog_tracks_published,
  idx_catalog_tracks_free_tier, idx_catalog_tracks_seed_axis,
  idx_catalog_tracks_classification_status,
  idx_catalog_tracks_watermark_code (unique)
• Notable columns: slug, title, genreSlug/subgenreSlug/moodSlug
  (BRI-126 taxonomy axes, slugs not display names), instrumentSlugsJson
  (JSON string[]), bpm, key, durationSeconds, peaksJson (BRI-193
  128 uint8 waveform peaks), previewKey, masterFlacKey, masterMp3320Key,
  masterMp3128Key (4-tier keys mirror jobs.tracks), featured /
  published / freeTier (booleans), classificationStatus
  ("pending" | "ok" | "failed"), classifiedArtifactsVersion,
  classifiedMovement, classifiedCharacter, watermarkCode (BRI-129
  AudioSeal), generationSeed / generationHardware / generationPrompt
  (reproducibility), seedAxis ("genre" | "subgenre" | "mood" | "instrument"),
  createdAt/updatedAt
• Public royalty-free catalog track; distinct from jobs.tracks.

catalogPurchases (active)

• PK: id (autoincrement integer)
• FKs: trackId → catalogTracks.id; userId → users.id (nullable)
• Indexes: idx_catalog_purchases_stripe (unique on stripeSessionId),
  idx_catalog_purchases_track, idx_catalog_purchases_user
• Notable columns: email, amountCents, purchasedAt (nowIso)
• One row per PWYW sale (BRI-132). Catalog is non-exclusive (BRI-89).

catalogDownloadUsage (active)

• PK: id (autoincrement integer)
• FKs: userId → users.id; trackId → catalogTracks.id
• Indexes: idx_catalog_download_usage_unique (unique on
  userId,day,trackId,format), idx_catalog_download_usage_user_day
• Notable columns: day, format, createdAt (nowIso)
• BRI-284: one consumed catalog-master download per user/day/track/format.

catalogFavorites (active)

• PK: composite (userId, trackId)
• FKs: userId → users.id; trackId → catalogTracks.id
• Indexes: idx_catalog_favorites_user_track (unique),
  idx_catalog_favorites_track
• Notable columns: createdAt (nowIso)
• Verified-user × track likes (BRI-138); like-twice is a no-op.

catalogQcFailures (active)

• PK: id (autoincrement integer)
• FKs: none (standalone log; failed generations never become catalogTracks)
• Indexes: idx_catalog_qc_failures_created
• Notable columns: leafAxis, leafSlug, title, reason, detail,
  seed, createdAt (nowIso)
• BRI-145 QC review queue log; one row per failed seed attempt.

catalogCopyrightClaims (active)

• PK: id (autoincrement integer)
• FKs: none (trackUrl is free-text, not FK)
• Indexes: idx_catalog_copyright_claims_created,
  idx_catalog_copyright_claims_status
• Notable columns: kind ("takedown" | "counter_notice"),
  claimantName, claimantEmail, trackUrl, description, signature,
  status (default "open"), createdAt (nowIso)
• BRI-147 DMCA intake — single table for both directions, discriminated
  by kind.

catalogTrackEmbeddings (active)

• PK: trackId (text)
• FKs: trackId → catalogTracks.id
• Indexes: none (PK is trackId)
• Notable columns: artifactsVersion, embedding (raw float32 blob,
  512-d CLAP, 2048 bytes/row), createdAt (nowIso)
• BRI-142; upserted on trackId so only the latest embedding survives.

catalogTrackClassificationVerdicts (active)

• PK: id (autoincrement integer)
• FKs: trackId → catalogTracks.id
• Indexes:
  idx_catalog_track_classification_verdicts_track_axis_slug (unique on
  trackId,taxonomyAxis,promptedSlug),
  idx_catalog_track_classification_verdicts_verdict
• Notable columns: taxonomyAxis ("genre" | "mood" | "instrument"),
  promptedSlug, verdict ("match" | "mismatch" | "unexpressible"),
  probability (null iff unexpressible), matchedLabel,
  viaTop1Fallback (boolean), artifactsVersion, createdAt (nowIso)
• BRI-142 classifier QC; one row per (track, axis, prompted slug).
  Never gates publish.

catalogTrackVideos (active)

• PK: id (autoincrement integer)
• FKs: trackId → catalogTracks.id
• Indexes: idx_catalog_track_videos_youtube_video_id (unique),
  idx_catalog_track_videos_track,
  idx_catalog_track_videos_track_current (unique partial: current = 1)
• Notable columns: youtubeVideoId, title, description, current
  (boolean; at most one current row per track — history trail via current = false), publishedAt (nowIso)
• BRI-75; one row per uploaded YouTube video for a catalog track.

catalogPromptReuses (active)

• PK: id (autoincrement integer)
• FKs: userId → users.id; trackId → catalogTracks.id
• Indexes: idx_catalog_prompt_reuses_user,
  idx_catalog_prompt_reuses_track
• Notable columns: promptSnapshot, createdAt (nowIso)
• Tier-1 relational memory: “user reused catalog track X’s prompt” — one
  row per reuse event, history not latest-state.

Inference / kill switch / spend

inferenceRoutes (active)

• PK: provider (text)
• FKs: none
• Indexes: none (PK is provider)
• Notable columns: weight (0–100, normalized at read time),
  enabled (boolean; forced 0 when false), fallbackAfter (optional chain
  hint), updatedBy (audit), updatedAt (nowIso)
• BRI-359 GPU gateway v2 routing; BRI-439 retired weight routing — the
  gateway document’s fallbackChain is the dispatch order and weight /
  fallbackAfter are vestigial columns kept for backfill compatibility.

inferenceProviderKeys (active)

• PK: composite (provider, position)
• FKs: none
• Indexes: idx_inference_provider_keys_position (unique)
• Notable columns: keyEnv (env-var name, never a key value),
  secretEnv (Modal paired secret, else null), label (admin hint),
  updatedBy, updatedAt (nowIso)
• BRI-359 key ring; resolver looks up env by name. Empty table = fixed
  env-name fallbacks, a no-op of pre-gateway behavior.

workerIdleStreak (vestigial — watchdog removed in BRI-439)

• PK: composite (endpointId, streakKind)
• FKs: none
• Indexes: idx_worker_idle_streak_endpoint
• Notable columns: streakKind
  ("idle" | "queued_without_workers"), count, updatedAt (nowIso)
• BRI-338 Phase 2: durable watchdog streak state (replaces in-memory Maps).

killSwitchState (vestigial — kill-switch removed in BRI-439)

• PK: id (always 1 — singleton state row)
• FKs: none
• Indexes: none
• Notable columns: lastFiredAtMs, updatedAt (nowIso)
• BRI-338 Phase 2: durable kill-switch re-fire cooldown.

runpodSpend (vestigial — RunPod runtime removed in BRI-439)

• PK: date (text, "YYYY-MM-DD")
• FKs: none
• Indexes: none
• Notable columns: cents, updatedAt (nowIso)
• Daily RunPod spend counter (UTC date). Cents to avoid float drift.

runpodSpendCache (vestigial — RunPod billing removed in BRI-439)

• PK: id (always 1 — singleton state row)
• FKs: none
• Indexes: none
• Notable columns: cents, fetchedAtMs, updatedAt (nowIso)
• BRI-338 Phase 2: durable 60s cache of RunPod real-billing API read.

Spaces / canvas

spacesCanvasDocuments (active)

• PK: userId (text)
• FKs: userId → users.id (cascade delete)
• Indexes: idx_spaces_canvas_documents_updated
• Notable columns: documentJson (JSON layout/annotations),
  revision, updatedAt (nowIso)
• One owner-scoped Spaces document. Layout + annotation state only; never
  preview credentials.

Cross-cutting fields

• createdAt/updatedAt: written via nowIso() from
  packages/db/src/constants.ts — strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
  evaluated by SQLite. Note: users, emailVerifications use Drizzle
  timestamp mode with $defaultFn(() => new Date()) instead; the
  nowIso() pattern is the rest of the schema.
• paid: boolean; catalog tracks, purchases, downloads gate on this.
• Email-keyed tables: jobs, kits, emailVerifications,
  freeGenerations keyed on the email string (no user_id FK; v1 retains
  rows on account deletion as orphans — see architecture.md §Account
  deletion).
• Opaque branded IDs: JobId, KitId, UserId, etc. — see
  packages/db/src/brands.ts (zod-branded string types, no runtime cost).
  Where the underlying column is an autoincrement integer (e.g. jobs.id),
  the brand wraps the integer; text UUID columns (users.id) use
  crypto.randomUUID() as the default.
• JSON-blob columns: peaks/peaksJson (audio fingerprint),
  traceparent (OpenTelemetry context), paramsJson (job input JSON),
  instrumentSlugsJson/tracks (catalog tag set / job tracks array),
  verdict-typed blobs (classification JSON), documentJson (Spaces
  canvas).

Litestream

• WAL streamed from local SQLite → R2 at brioso-jobs/litestream/.
• Prefix is protected from lifecycle rules (see
  .agents/docs/r2-lifecycle-rules.md §“Litestream prefix”).

Secrets / env

• Infisical: project-wide env per .agents/docs/infisical-env-guide.md.
  Per-environment (dev/preview/prod) scopes.
• nono profile: per-secret disk file at /etc/brioso/ for systemd-
  managed processes that can’t reach Infisical at boot.
• Drizzle Gateway: read-only viewer; reads the same SQLite file as a
  second WAL reader.

Migration policy

• Schema-as-TS in packages/db/src/schema.ts.
• drizzle-kit generate produces SQL under packages/db/src/migrations/.
• Migrations are embedded and applied at boot via migrate() from
  drizzle-orm/libsql/migrator.
• Each migration is one ticket: one PR, one schema change, one
  reversible migration.