131 lines
4.4 KiB
Markdown
131 lines
4.4 KiB
Markdown
# 🗄️ Database Architecture - GoHorseJobs
|
|
|
|
GoHorseJobs uses a single **PostgreSQL 16+** database shared across all services (Backend API, NestJS Backoffice, Node.js Seeder).
|
|
|
|
---
|
|
|
|
## 🏗️ Core Entity-Relationship Diagram (ERD)
|
|
|
|
The core data model centers around `users` acting in different capacities (`candidate`, `recruiter`, `admin`) interacting with `companies` and `jobs`.
|
|
|
|
```mermaid
|
|
erDiagram
|
|
USERS ||--o{ USER_COMPANIES : "belongs to"
|
|
COMPANIES ||--o{ USER_COMPANIES : "contains"
|
|
|
|
COMPANIES ||--o{ JOBS : "posts"
|
|
USERS ||--o{ JOBS : "creates (author)"
|
|
|
|
USERS ||--o{ APPLICATIONS : "submits"
|
|
JOBS ||--o{ APPLICATIONS : "receives"
|
|
|
|
USERS ||--o{ FAVORITE_JOBS : "saves"
|
|
JOBS ||--o{ FAVORITE_JOBS : "favorited"
|
|
|
|
USERS ||--o{ TICKETS : "opens"
|
|
USERS ||--o{ TICKET_MESSAGES : "sends"
|
|
TICKETS ||--o{ TICKET_MESSAGES : "has"
|
|
|
|
USERS {
|
|
uuid id PK
|
|
string role "superadmin, admin, recruiter, candidate"
|
|
string identifier "username or email"
|
|
string email UK
|
|
string password_hash
|
|
boolean is_active
|
|
timestamp created_at
|
|
}
|
|
|
|
COMPANIES {
|
|
uuid id PK
|
|
string name
|
|
string document "CNPJ"
|
|
string domain UK
|
|
string location
|
|
string industry
|
|
string logo_url
|
|
string status "pending, active, rejected"
|
|
}
|
|
|
|
USER_COMPANIES {
|
|
uuid id PK
|
|
uuid user_id FK
|
|
uuid company_id FK
|
|
string role "owner, admin, member"
|
|
}
|
|
|
|
JOBS {
|
|
uuid id PK
|
|
uuid company_id FK
|
|
uuid author_id FK
|
|
string title
|
|
text description
|
|
string type "full-time, remote, etc"
|
|
decimal max_salary
|
|
decimal min_salary
|
|
string status "draft, published, closed"
|
|
}
|
|
|
|
APPLICATIONS {
|
|
uuid id PK
|
|
uuid job_id FK
|
|
uuid candidate_id FK
|
|
string status "pending, reviewing, interviewed, rejected, accepted"
|
|
text cover_letter
|
|
}
|
|
|
|
FAVORITE_JOBS {
|
|
uuid id PK
|
|
uuid user_id FK
|
|
uuid job_id FK
|
|
timestamp created_at
|
|
}
|
|
|
|
TICKETS {
|
|
uuid id PK
|
|
uuid user_id FK "nullable for guests"
|
|
string subject
|
|
string category "Support, Bug, Billing..."
|
|
string status "open, in_progress, resolved, closed"
|
|
string email "fallback for guests"
|
|
string name "fallback for guests"
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## 🔑 Key Strategies
|
|
|
|
### 1. Primary Keys (UUID v7)
|
|
We aggressively use **UUID v7** for all major business entities (`users`, `companies`, `jobs`).
|
|
* **Why?** UUID v7 contains a timestamp component, making it naturally sortable by insertion time (like SERIAL/auto-increment) while avoiding the predictability and distributed generation bottlenecks of standard integers.
|
|
|
|
### 2. Multi-Role Profiles
|
|
The system handles permissions through the `role` enum in the `users` table:
|
|
* `superadmin`: Global control over GoHorseJobs.
|
|
* `admin`: Can moderate companies/jobs inside the Backoffice.
|
|
* `recruiter`: A user attached to a `company_id` via `user_companies` who can post `jobs`.
|
|
* `candidate`: A standard user seeking jobs and making `applications`.
|
|
|
|
A single email can only correspond to one role globally. If a user needs another role, they must change it or use a different credential.
|
|
|
|
### 3. Migrations
|
|
Migrations are written in pure SQL and stored in `backend/migrations/`.
|
|
They follow a strict sequential numbering index (e.g., `000_schema.sql`, `001_roles.sql`, etc). The backend runtime executes these on startup if the database is out of sync.
|
|
|
|
### 4. Background Seeding
|
|
Seeding (populating test data) is explicitly decoupled from migrations. It lives isolated in the Node.js `seeder-api`.
|
|
To reset your local database to a clean, populated state:
|
|
```bash
|
|
# Deletes everything, remigrates, and inserts base data (except 153k cities)
|
|
cd seeder-api && npm run seed:lite
|
|
```
|
|
|
|
---
|
|
|
|
## ⚠️ Security Notes
|
|
|
|
### The `password_hash` & Pepper
|
|
Bcrypt hashes stored in the standard `users` table require a secret pepper (`PASSWORD_PEPPER`).
|
|
* The raw hash stored in the DB cannot be cracked immediately due to the pepper.
|
|
* The seeder relies on reading the `PASSWORD_PEPPER` from `.env` to forge the initial super-admin and test accounts hashes. **If you change the `.env` pepper, the entire DB of seeded passwords immediately invalidates.** You must re-run `npm run seed`.
|