Database Schema

VeriWorkly uses PostgreSQL through Prisma 7. The source of truth is apps/server/prisma/schema.prisma; this page summarises the model groups and the design decisions worth knowing.

Identity and authentication

Better-Auth's standard tables, extended with VeriWorkly-specific fields on User.

User

model User {
  id                    String                 @id @default(cuid())
  email                 String                 @unique
  name                  String?
  username              String?                @unique
  emailVerified         Boolean                @default(false)
  image                 String?

  autoSyncEnabled       Boolean                @default(true)

  // Affiliate program
  affiliateStatus       AffiliateStatus        @default(NOT_ENROLLED)
  affiliateTier         AffiliateTier          @default(TIER_1)
  affiliateCode         String?                @unique
  affiliateEnrolledAt   DateTime?

  // Roles and ambassador program
  role                  Role                   @default(USER)
  ambassadorStatus      String                 @default("NONE")
  ambassadorApplication AmbassadorApplication?

  // Free-tier import cooldowns
  lastLinkedinImportAt  DateTime?
  lastGithubImportAt    DateTime?

  createdAt             DateTime               @default(now())
  updatedAt             DateTime               @updatedAt
  // …plus relations to sessions, documents, credits, billing, portfolios, and more
}

enum Role            { USER  AMBASSADOR  ADMIN }
enum AffiliateStatus { NOT_ENROLLED  PENDING  ACTIVE  SUSPENDED }
enum AffiliateTier   { TIER_1  TIER_2  TIER_3 }

username is required before a document can be shared publicly — share URLs are username-scoped. lastLinkedinImportAt and lastGithubImportAt are what enforce the free-tier import cooldowns described in Importing Your Profile.

Session, Account, Verification

Standard Better-Auth tables for sessions, OAuth/credential links, and email verification. Session carries expiresAt, a unique token, and the originating ipAddress / userAgent used for new-device login alerts.

Documents

Document — the unified content model

Resumes, cover letters, portfolios, and link-in-bio pages are all one table, distinguished by type.

enum DocumentType { RESUME  COVER_LETTER  PORTFOLIO  LINK_IN_BIO }
enum Visibility   { PRIVATE  UNLISTED  PUBLIC }

model Document {
  id            String       @id @default(cuid())
  userId        String
  type          DocumentType @default(RESUME)
  title         String       @default("Untitled Document")
  slug          String
  tags          String[]     @default([])

  content       Json         // Document body, JSON-Resume-shaped for resumes
  metadata      Json?        // UI state, favourites, and other client hints

  templateId    String       @default("modern")
  schemaVersion Int          @default(1)
  revision      Int          @default(1)   // Optimistic concurrency control

  visibility    Visibility   @default(PRIVATE)
  lastSyncedAt  DateTime?

  createdAt     DateTime     @default(now())
  updatedAt     DateTime     @updatedAt
  deletedAt     DateTime?    // Soft delete

  @@unique([userId, slug])
}

• revision is the optimistic-concurrency guard. A sync write that carries a stale revision is rejected rather than applied, which is what surfaces as a conflicted sync state in Studio.
• templateId's "modern" default is a historical artefact — no template ships with that id. Studio's loadTemplateComponentById falls back to the first registry entry for any unrecognised id, so such a document renders as Executive Clarity. Every document created through the UI carries a real id.
• deletedAt is a soft delete. Restore and hard-delete methods exist in the service layer, but no route currently exposes them — only soft delete is reachable through the API today.
• Payload cap — document content is limited to roughly 1 MB per document, enforced server-side.
• Free-tier cap — one active document per type for users without the ai_credits or portfolio_publish entitlement.

MasterProfile

One JSON blob per user, with no other structural fields and no entitlement gate.

model MasterProfile {
  id        String   @id @default(cuid())
  userId    String   @unique
  content   Json
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Sharing

ShareLink and ShareView

model ShareLink {
  id           String    @id @default(cuid())
  userId       String
  documentId   String

  slug         String    // Stable public URL slug
  snapshot     Json      // Static copy of the document at share time

  passwordHash String?   // scrypt, verified with a timing-safe comparison
  expiresAt    DateTime?
  viewCount    Int       @default(0)
  lastViewedAt DateTime?

  @@unique([userId, documentId])
  @@unique([userId, slug])
}

Two things follow directly from this shape:

• The public page serves snapshot, not the live document. Editing a document after sharing does not change what a recipient sees until the link is refreshed.
• One share link per document per user (@@unique([userId, documentId])). Re-sharing updates the existing link rather than minting a second one.

ShareView records individual views, feeding the buffered view-count flush job. Repeat views from the same IP within a 30-minute window are deduplicated before any counter is incremented.

Public share URLs take the form /share/{username}/{slug}.

Billing, entitlements, and credits

Subscription

enum SubscriptionStatus { INACTIVE  TRIALING  ACTIVE  PAST_DUE  CANCELED }
enum BillingInterval    { ONE_DAY  SEVEN_DAY  MONTHLY  ANNUAL }

Holds provider/customer/price/subscription IDs, a productKey, status, interval, a grace-period end date, a cancel-at-period-end flag, and a last-webhook timestamp used to keep out-of-order webhook delivery from corrupting state.

EntitlementGrant

A named capability (ai_credits, portfolio_publish, custom_subdomain, seo_controls, analytics, watermark_removal), its source (SUBSCRIPTION, MANUAL, PROMOTION, SYSTEM), and a start/end/revoked window. Access checks read entitlements, never plan names — which is why a manual admin grant behaves identically to a paid subscription.

The credit ledger

Five tables work together:

CreditUsageAllocation is what makes multi-grant balances correct: a user holding a subscription grant plus a leftover top-up is debited from the soonest-to-expire grant first.

BillingWebhookEvent

Every Dodo Payments webhook, keyed by the provider's event ID for idempotency, with a processing status and retry count. This table also powers the user-facing billing history list.

Portfolio

Abandoned PENDING uploads older than 24 hours — and their R2 objects — are garbage-collected by the hourly portfolio access job, which also suspends publications whose billing grace period has expired.

Growth programs

Affiliate

AffiliateReferral (signed up / converted / rejected), AffiliateClick, AffiliateCommission (pending / available / reversed / paid, with a basis-points rate and a cent amount), AffiliateWallet (pending / available / paid cent totals), and AffiliateWithdrawal (requested / approved / rejected / paid).

Ambassador

enum AmbassadorApplicationStatus { PENDING  APPROVED  REJECTED }

model AmbassadorApplication {
  id             String                      @id @default(cuid())
  userId         String                      @unique
  collegeName    String
  graduationYear String
  whyJoin        String
  superpower     String
  funFact        String
  vibeCheck      String?
  socialHandle   String?
  status         AmbassadorApplicationStatus @default(PENDING)
  reviewedBy     String?
  reviewedAt     DateTime?
  reviewNote     String?
}

Approval flips both the application's status and the user's role to AMBASSADOR inside a single transaction. See Ambassador Program.

AdminAuditEntry

The admin action log actually written to by the monetization console — credit and entitlement grants, affiliate moderation, and withdrawal decisions.

Public content

RoadmapFeature and RoadmapInteraction

RoadmapFeature holds public roadmap items: a free-text status ("todo" / "in-progress" / "done" — a string, not a database enum), an ETA, tags, and rich detail fields (fullDescription, whyItMatters, timeline, details).

RoadmapInteraction models per-user votes, bookmarks, and comments.

ChangelogEntry

One row per shipped release: version (unique), title, summary, a type (major / minor / patch), publish date, GitHub URL, and five categorised string arrays — added, improved, fixed, breaking, security — plus tags and PR references. A daily sync job can import missing entries from GitHub Releases.

Observability

Usage metrics are aggregate-only product telemetry — counts of events like resumes created, exports, and logins — buffered in Redis and flushed daily. They are not per-user tracking, and no third-party analytics is present.

API keys

model ApiKey {
  id        String    @id @default(cuid())
  keyHash   String    @unique   // HMAC-SHA256; the key itself is never stored
  keyPrefix String
  keySuffix String
  name      String
  scopes    String[]  @default(["user:read"])
  userId    String
  isActive  Boolean   @default(true)
  rateLimit Int       @default(20)
  expiresAt DateTime?
  revokedAt DateTime?
  lastUsed  DateTime?
}

See API Keys for scope semantics and rotation behaviour.