Database Schema

The whole schema is one file, convex/schema.ts, and Convex enforces it: a field that is not declared cannot be written, and a validator change is applied to the deployment at push time. Six tables cover the product — profiles, jobs, tags, trackedApplications, emailSubscriptions and jobNotificationDeliveries. Convex adds _id and _creationTime to every document, so no table declares its own id or created-at field.

erDiagram
profiles {
  string userId "Clerk user id"
  string fullName "optional"
  string role "user, moderator or admin"
}
jobs {
  string title
  string company
  string location "optional"
  string url "optional"
  string roleLevel "optional"
  string status "pending, approved, rejected, outdated"
  array tags "optional, tag names"
  string submitterUserId "optional, Clerk user id"
}
tags {
  string name
}
trackedApplications {
  string userId "Clerk user id"
  id jobId "references jobs"
  object jobSnapshot "optional"
  string status "saved to accepted"
  string notes "optional"
  array statusHistory "optional"
}
emailSubscriptions {
  string userId "Clerk user id"
  string email
  string unsubscribeToken
  boolean isActive
  array roleFilters
}
jobNotificationDeliveries {
  id jobId "references jobs"
  string userId "Clerk user id"
  number sentAt
}
jobs ||--o{ trackedApplications : "jobId"
jobs ||--o{ jobNotificationDeliveries : "jobId"
profiles ||..o{ trackedApplications : "same userId"
profiles ||..|| emailSubscriptions : "same userId"
profiles ||..o{ jobNotificationDeliveries : "same userId"
jobs }o..o{ tags : "tag names, not ids"

profiles

One row per signed-in user, created by users.ensureProfile on first sign-in.

role drives all moderation

role is the only authorization input in the backend. requireModOrAdmin in convex/admin.ts loads this row and rejects anything that is not moderator or admin, which gates the entire Admin API; dashboard.getDashboardData reads the same field to decide whether to return the pending queue at all. No function writes role, so promotion is a manual edit of this row in the Convex dashboard, and users.ensureProfile hard-codes "user" for new rows.

A user with no profiles row is treated as signed out for authorization purposes — checkUserRole returns all-false flags and requireModOrAdmin throws Unauthorized.

jobs

Every submission, at every stage of its life.

Convex appends _creationTime to every index, which is what lets the retention cron range-scan by_status with q.eq("status", ...).lt("_creationTime", cutoff) instead of reading the table.

The status values are a lifecycle, not flags: pending on submission, approved by a moderator (the only state the public list reads), rejected by a moderator, and outdated either manually or automatically 14 days after creation. See Jobs API for the retention rules.

tags

The tag vocabulary shown in the filter UI.

There is no join table. jobs.tags stores tag names directly, and jobs.postJob inserts any name it has not seen before so the vocabulary grows from real submissions. tags.getAllTags returns the sorted names. Rows are never deleted, so a tag can outlive the last job that used it.

trackedApplications

One row per user per saved job — this single table is both "bookmarks" and "application tracking".

by_userId_jobId is read with .unique(), which throws if two rows ever match. Convex has no unique constraints, so uniqueness is maintained by toggleBookmark always checking that index before inserting.

What jobSnapshot is for

A dashboard must not lose an application because the listing was taken down. jobSnapshot caches title, company, location, url, createdAt (the job's original _creationTime) tags and roleLevel when the bookmark is created, so a tracked application still renders in full after the job document is deleted by a moderator or the retention cron. The card is then marked availability: "closed" with closureReason: "removed" — as opposed to "outdated", which means the job still exists but is closed.

It is optional because rows created before snapshots existed do not have one; bookmarks.backfillTrackedApplicationSnapshots fills those in where the job still exists. A row with neither a live job nor a snapshot is skipped by the dashboard queries.

What statusHistory is for

An append-only audit trail of status changes: { fromStatus?, toStatus, changedAt }. The first entry is written on creation with toStatus: "saved" and no fromStatus; after that updateBookmarkStatus appends one entry per actual change, and notes-only edits (status unchanged) append nothing.

emailSubscriptions

At most one row per user, managed at /profile.

The token is preserved when a user re-subscribes, so unsubscribe links already sitting in inboxes keep working.

jobNotificationDeliveries

A receipt per email sent, and the reason nobody gets told about the same job twice.

Rows are written only after the send succeeds, so a failed send is retried the next time the action runs for that job. They are deleted when the retention cron deletes a job and when a user deletes their account; admin.deleteJob does not delete them.

Working with the schema

• Edit convex/schema.ts; bunx convex dev pushes the change and regenerates convex/_generated/.
• Adding a required field to a table that already has rows will fail the push — add it as v.optional(...), backfill with an internalMutation, then tighten it.
• Dev and prod are separate deployments with separate data. bun run sync:prod-to-dev copies prod data over your dev deployment; see the warning in Deployment.

The functions that read and write each table are documented in the API Reference.