Data Model

Reqcore uses PostgreSQL 16 with Drizzle ORM for type-safe database access. All domain tables are defined in server/database/schema/app.ts.

Entity Relationship

organization (Better Auth)
├── job (draft → open → closed → archived)
│   ├── jobQuestion (custom application form questions)
│   └── application (new → screening → interview → offer → hired/rejected)
│       ├── questionResponse (answers to custom questions)
│       └── candidate (deduplicated by email per org)
│           └── document (resume, cover_letter — stored in S3)
└── member (user ↔ organization with role)

Tables

`job`

The core hiring entity. Each job belongs to an organization.

Column	Type	Description
`id`	`uuid`	Primary key
`organizationId`	`string`	FK → organization
`title`	`string`	Job title
`description`	`text`	Full description (Markdown)
`status`	`enum`	`draft`, `open`, `closed`, `archived`
`location`	`string`	Office location
`employmentType`	`string`	Full-time, Part-time, etc.
`slug`	`string`	URL slug for public page
`salaryMin` / `salaryMax`	`integer`	Salary range
`salaryCurrency`	`string`	Currency code (USD, EUR, etc.)
`salaryUnit`	`string`	HOUR, DAY, WEEK, MONTH, YEAR
`remoteStatus`	`string`	On-site, Remote, Hybrid
`validThrough`	`timestamp`	Application deadline
`createdAt` / `updatedAt`	`timestamp`	Timestamps

`candidate`

A person who has applied or been added to the talent pool.

Column	Type	Description
`id`	`uuid`	Primary key
`organizationId`	`string`	FK → organization
`firstName` / `lastName`	`string`	Full name
`email`	`string`	Email (unique per org)
`phone`	`string`	Phone number
`notes`	`text`	Recruiter notes
`createdAt` / `updatedAt`	`timestamp`	Timestamps

Unique constraint: (organizationId, email) — prevents duplicate candidates within an org.

`application`

Links a candidate to a job with a pipeline status.

Column	Type	Description
`id`	`uuid`	Primary key
`organizationId`	`string`	FK → organization
`jobId`	`uuid`	FK → job
`candidateId`	`uuid`	FK → candidate
`status`	`enum`	`new`, `screening`, `interview`, `offer`, `hired`, `rejected`
`notes`	`text`	Application-specific notes
`score`	`integer`	Recruiter-assigned score
`createdAt` / `updatedAt`	`timestamp`	Timestamps

Unique constraint: (organizationId, candidateId, jobId) — one application per candidate per job.

`document`

A file (resume, cover letter, work sample) stored in S3.

Column	Type	Description
`id`	`uuid`	Primary key
`organizationId`	`string`	FK → organization
`candidateId`	`uuid`	FK → candidate
`fileName`	`string`	Original filename (sanitized)
`mimeType`	`string`	File MIME type
`fileSize`	`integer`	File size in bytes
`storageKey`	`string`	S3 object key (never exposed to clients)
`documentType`	`enum`	`resume`, `cover_letter`, `other`
`createdAt`	`timestamp`	Upload timestamp

`jobQuestion`

Custom application form questions per job.

Column	Type	Description
`id`	`uuid`	Primary key
`jobId`	`uuid`	FK → job
`questionText`	`string`	Question text
`questionType`	`enum`	`short_text`, `long_text`, `single_select`, `multi_select`, `number`, `date`, `url`, `email`, `file_upload`
`required`	`boolean`	Whether answer is required
`options`	`json`	Options for select types
`sortOrder`	`integer`	Display order

`questionResponse`

Answers to custom questions per application.

Column	Type	Description
`id`	`uuid`	Primary key
`applicationId`	`uuid`	FK → application
`questionId`	`uuid`	FK → jobQuestion
`responseValue`	`text`	The answer

Multi-Tenant Isolation

Every domain table includes an organizationId column. All database queries must include an organizationId filter derived from the authenticated session — never from user input.

Example query pattern:

const jobs = await db
  .select()
  .from(job)
  .where(eq(job.organizationId, orgId))

Migrations

Database migrations are managed by Drizzle Kit:

# Generate a migration after schema changes
npm run db:generate

# Apply migrations manually
npm run db:migrate

Migrations also run automatically on server startup via the server/plugins/migrations.ts plugin, using a PostgreSQL advisory lock for safety in multi-instance deployments.

Next Steps

Security — Security boundaries and enforcement
Architecture Overview — High-level system design
Directory Structure — Project file organization