Skip to content
Teros Platform Docs

Database & Prisma

All schema, migrations, and the generated client live in packages/database. The database is the canonical source of truth for the platform — every app reads its types from @repo/database and never re-declares them.

Conventions

Section titled “Conventions”
  • Schema source: packages/database/prisma/schema.prisma.
  • Migrations: packages/database/prisma/migrations/<YYYYMMDDHHmmss>_<snake_case>/.
  • Generated client is the Prisma client — apps import it via @repo/database.
  • New models follow the existing style: id String @id @default(cuid()), createdAt DateTime @default(now()), updatedAt DateTime @updatedAt, and indexes on every column commonly used for filtering or scoping (especially organizationId).
  • Soft / “legacy” columns are kept around with a comment when a structured replacement supersedes them; the rename is sequenced over multiple migrations.

Models grouped by domain

Section titled “Models grouped by domain”

Organizations & identity

Section titled “Organizations & identity”
  • Organization — a tenant. Owns candidate profiles (when staffed), job postings, applications, and audit logs.
  • WorkOsUser — local mirror of WorkOS user metadata, populated by user.created / user.updated webhooks. Used for relational integrity when local rows need a foreign key to a user.

Candidates

Section titled “Candidates”
  • CandidateProfile — the canonical candidate record. Owns resume, skills, work prefs, links, and discoverability state.
  • CandidateFileObject — S3-backed file references for profile picture and resume.
  • DocumentType / DocumentVariant — configurable required document definitions and selectable form variants.
  • DocumentTemplateVersion — S3-backed blank form revisions for each document variant.
  • CandidateDocument — S3-backed signed candidate uploads and Teros Ops review state.
  • ResumeConversion — async resume → markdown extraction state and result.
  • PublicResumeLink — token-gated shareable URL for a candidate’s resume.
  • CandidateInvitation — token-gated invite for a candidate to claim or onboard.

Job postings

Section titled “Job postings”
  • JobPosting — an open role at an organization.
  • JobPostingPublicLink — token-gated public URL to a job posting.
  • JobPostingRequest — the company intake form (a request that produces a JobPosting once submitted).

Applications & pipeline

Section titled “Applications & pipeline”
  • Application — a candidate’s application to a posting; unique per (jobPostingId, candidateProfileId).
  • ApplicationStageEvent — every advance/reject/withdraw event, with actor and timestamp.
  • CandidateNote — internal / application-scoped notes left on candidates.

Skills

Section titled “Skills”
  • NormalizedSkill — canonical skill registry with parent/child grouping and approval state.
  • NormalizedSkillSource — link table between a CandidateProfile’s raw skill string and a NormalizedSkill.

Audit

Section titled “Audit”
  • AuditLog — append-only record of privileged actions, indexed by organizationId, actorSub, action, targetType, and createdAt.

Adding a model

Section titled “Adding a model”
  1. Edit packages/database/prisma/schema.prisma.
  2. Run pnpm run db:migrate:dev from the repo root (or npx prisma migrate dev from packages/database).
  3. Commit the generated migration in packages/database/prisma/migrations/.
  4. Regenerate clients via pnpm run db:generate.
  5. If the change renames or removes a field, update every consuming app in the same PR.

Field-level read/write permissions

Section titled “Field-level read/write permissions”

For the most permission-sensitive models, the matrix below documents who can read and who can write each field. Until field-level extraction is automated, this is hand-maintained and should be updated when a service changes its field filtering.

CandidateProfile

Section titled “CandidateProfile”
Field Read Write
firstName, lastName, headline, bio, location* owner member, company actors w/ app context, teros-ops* owner member, teros-ops*
skills, workPreferences, portfolioLinks, yearsOfExperience same as above same as above
linkedinUrl, githubUrl same as above same as above
phoneNumber, phoneCountryCode owner member, teros-ops* owner member, teros-ops*
profileImage*, resume* owner member, company actors w/ app context, teros-ops* owner member, teros-ops*
isDiscoverable owner member, teros-ops* owner member, teros-ops*
approvalStatus, approvedAt, approvedByWorkosUserSub teros-ops* teros-ops*
salaryExpectation*, currentSalary owner member, teros-ops* owner member, teros-ops*
lastActivityAt owner member, company actors w/ app context, teros-ops* system (Postgres triggers)

Application

Section titled “Application”
Field Read Write
coverNote candidate (own), company actors of posting org, teros-ops* candidate at submission only
status candidate (own), company actors, teros-ops* company-admin, teros-ops*; company-member limited
offerStartDate, offeredSalary candidate (own), company-admin, teros-ops* company-admin, teros-ops*
withdrawnAt candidate (own), company actors, teros-ops* candidate (own withdraw), teros-ops*
terminatedAt company-admin, teros-ops* company-admin, teros-ops*

JobPosting

Section titled “JobPosting”
Field Read Write
title, description, location*, employmentType, isRemote, currency public (via public link or list), all roles company-admin, teros-ops*
minCompensation, maxCompensation public listing, all internal roles company-admin, teros-ops*
status, openedAt, closedAt public listing, all internal roles company-admin, teros-ops*
createdBySub, updatedBySub company-admin, teros-ops* system-managed

AuditLog

Section titled “AuditLog”
Field Read Write
action, actorSub, targetType, targetId, createdAt, metadata company-admin (scoped to own organizationId), teros-ops* (cross-org) system
organizationId, actorOrgId same as above system

CandidateNote

Section titled “CandidateNote”
Field Read Write
content (visibility = INTERNAL) teros-ops* only teros-ops*
content (visibility = APPLICATION) company actors of the posting org, teros-ops* company actors of the posting org, teros-ops*
authorSub same as content for that visibility system-managed

Documents

Section titled “Documents”
Model / Field Read Write
DocumentType, DocumentVariant public config fields owner member, teros-ops* seed / system config
DocumentTemplateVersion bucket/object metadata owner member, teros-ops* seed / system config
CandidateDocument upload metadata, status, expiry, rejection reason owner member, teros-ops* owner member creates uploads
CandidateDocument.reviewedByWorkosUserSub, reviewedAt, status, rejectionReason owner member, teros-ops* teros-ops* review decisions

Organization

Section titled “Organization”
Field Read Write
name, slug, description members of org, teros-ops* company-admin (own org), teros-ops*
workosOrganizationId, ownerWorkosUserId teros-ops* system-managed (WorkOS sync)

Documents module registration

Section titled “Documents module registration”

The Documents module is generic. Platform gating reads active DocumentType rows, resolves their requiredPredicate, and checks whether the candidate has a valid CandidateDocument. The gate does not branch on tax-specific ids.

To add a new required document type:

  1. Add document_types and document_variants seed rows.
  2. Add a document_template_versions row pointing at the blank PDF in S3.
  3. Register a ValidityPolicy or RequiredPredicate key only if the built-in never expiry or LINKED_TO_COMPANY predicate is not enough.
  4. Keep blocksPayment=false unless that document should affect the informational payment eligibility endpoint.

CandidateProfile.lastActivityAt

Section titled “CandidateProfile.lastActivityAt”

lastActivityAt is a denormalized “last activity” timestamp on CandidateProfile, maintained entirely by Postgres triggers — no application code path writes to it. Drives the default ops Pool page ordering.

Activity sources (rows whose INSERT/UPDATE bump lastActivityAt = now() on the related profile):

  • "CandidateProfile" itself: any meaningful column change (excludes lastActivityAt, updatedAt, searchVector).
  • "Application", "ApplicationStageEvent", "CandidateNote".
  • candidate_documents.
  • "PublicResumeLink", "CandidateInvitation", "JobPostingCandidateSuggestionFeedback".

Explicit exclusions (no triggers attached): "CandidateProfileEmbedding", "NormalizedSkillSource", "ResumeConversion".

Adding a new activity source = follow-up migration attaching the shared candidate_profile_bump_last_activity_from_related(<fk-column>) trigger function. Data-fix or backfill scripts that should not count as activity must run with SET session_replication_role = replica for the session so the triggers are skipped.

See also

Section titled “See also”
  • API — how services enforce these access decisions.
  • Migrations — the loop for changing the schema safely.
  • Permissions Matrix — endpoint-level view of the same model.