Entities

1. Company (Tenant)

Represents the company/tenant in the system.

Attributes:

• company_id: Numeric, primary key.
• company_name: Text, required.
• company_street: Text, optional.
• company_city: Text, optional.
• company_state: Text, optional.
• company_zip: Text, optional.
• company_vat: Text, optional.
• company_email: Text, required.
• created_at: Date, required.
• updated_at: Date, required.

2. User

Represents system users with specific roles.

Attributes:

• user_id: Numeric, primary key.
• user_name: Text, required.
• user_email: Text, unique, required.
• password: Text, required.
• user_role: Enum (Admin, HR, Finance Manager), required.
• company_id: Foreign key to Company.
• created_at: Date, required.
• updated_at: Date, required.

3. Employee

Represents employees managed by the company.

Attributes:

• employee_id: Numeric, primary key.
• employee_name: Text, required.
• employee_email: Text, unique, required.
• employee_role: Text, optional.
• employee_status: Enum (Active, Inactive, Terminated), required.
• employee_street: Text, optional.
• employee_city: Text, optional.
• employee_state: Text, optional.
• employee_zip: Text, optional.
• employee_iban: Text, optional.
• employee_bank_name: Text, optional.
• employee_swift: Text, optional.
• employee_cv_ref: Text, optional (file path or URL).
• employee_photo_ref: Text, optional (file path or URL).
• company_id: Foreign key to Company.
• created_at: Date, required.
• updated_at: Date, required.

4. Candidate

Represents candidates applying for roles in the company.

Attributes:

• candidate_id: Numeric, primary key.
• candidate_name: Text, required.
• candidate_email: Text, unique, required.
• candidate_status: Enum (Applied, Interview, Offered, Rejected), required.
• candidate_cv_ref: Text, optional (file path or URL).
• candidate_stage: Text, optional.
• candidate_notes: Text, optional.
• company_id: Foreign key to Company.
• created_at: Date, required.
• updated_at: Date, required.

5. Client

Represents the clients of the company.

Attributes:

• client_id: Numeric, primary key.
• client_name: Text, required.
• client_representative: Text, required.
• client_email: Text, unique, required.
• client_street: Text, optional.
• client_city: Text, optional.
• client_state: Text, optional.
• client_zip: Text, optional.
• client_vat: Text, optional.
• client_logo_url: Text, optional (file path or URL).
• company_id: Foreign key to Company.
• created_at: Date, required.
• updated_at: Date, required.

6. Invoice

Represents financial transactions between the company and its clients.

Attributes:

• invoice_id: Numeric, primary key.
• invoice_no: Numeric, required.
• invoice_issue_date: Date, required.
• invoice_due_date: Date, required.
• invoice_notes: Text, optional.
• invoice_status: Enum (Draft, Pending, Paid, Overdue), required.
• company_id: Foreign key to Company.
• client_id: Foreign key to Client.
• created_at: Date, required.
• updated_at: Date, required.

7. LineItem

Represents individual items in an invoice.

Attributes:

• item_id: Numeric, primary key.
• description: Text, required.
• quantity: Integer, required.
• unit_price: Decimal, required.
• currency: Text, required.
• total: Decimal, required.
• invoice_id: Foreign key to Invoice.

8. EmailTemplate

Represents customizable email templates.

Attributes:

• template_id: Numeric, primary key.
• type: Enum (New Invoice, Invoice Overdue, Welcome Employee, etc.), required.
• subject: Text, required.
• body: Text, required.
• company_id: Foreign key to Company.
• created_at: Date, required.
• updated_at: Date, required.

Relations

• Company ↔ User (company_manages_user): A company has many users; each user belongs to one company.
• Company ↔ Employee (company_employs_employee): A company has many employees; each employee belongs to one company.
• Company ↔ Candidate (company_screens_candidate): A company has many candidates; each candidate belongs to one company.
• Company ↔ Client (company_owns_client): A company has many clients; each client belongs to one company.
• Company ↔ Invoice (company_issues_invoice): A company generates many invoices; each invoice belongs to one company.
• Client ↔ Invoice (client_receives_invoice): A client can have many invoices; each invoice is addressed to one client.
• Invoice ↔ LineItem (invoice_contains_item): An invoice has multiple line items; each line item belongs to one invoice.
• Company ↔ EmailTemplate (company_creates_template): A company has multiple email templates; each template belongs to one company.