# Enterprise ERP Specification - Relational Database Architecture

This document defines the core relational database schema, indexing strategies, field validations, and entity relationships (ER) modeled in PostgreSQL for transaction safety.

---

## 1. Entity Relationship (ER) Diagram

```mermaid
erDiagram
    TENANTS ||--o{ USERS : owns
    USERS ||--o1 EMPLOYEE_PROFILES : has
    DEPARTMENTS ||--o{ EMPLOYEE_PROFILES : groups
    EMPLOYEE_PROFILES ||--o{ ATTENDANCE : records
    EMPLOYEE_PROFILES ||--o{ LEAVES : requests
    EMPLOYEE_PROFILES ||--o{ TASKS : executes
    CLIENTS ||--o{ PROJECTS : commissions
    PROJECTS ||--o{ SPRINTS : structures
    SPRINTS ||--o{ TASKS : contains
    CLIENTS ||--o{ LEADS : queries
    PROJECTS ||--o{ INVOICES : charges
    EMPLOYEE_PROFILES ||--o{ EXPENSES : incurs
    PROJECTS ||--o{ EXPENSES : associated_with
```

---

## 2. Table Specifications & Schema Definitions

### A. Core Directory & Profile Tables

```sql
-- Multi-Tenant Segregation Table
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_name VARCHAR(150) NOT NULL,
    license_tier VARCHAR(50) DEFAULT 'Enterprise',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Authentication User Accounts
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    username VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL, -- e.g., CEO, Employee, CFO
    is_mfa_enabled BOOLEAN DEFAULT FALSE,
    mfa_secret VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Detailed Employee Profiles (HRMS Domain)
CREATE TABLE employee_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20),
    department_id UUID,
    designation VARCHAR(100) NOT NULL,
    joined_date DATE NOT NULL,
    ctc_annual NUMERIC(15, 2) NOT NULL,
    base_salary_monthly NUMERIC(15, 2) NOT NULL,
    bank_account_no VARCHAR(50) NOT NULL,
    tax_identifier VARCHAR(50) NOT NULL, -- PAN/SSN
    status VARCHAR(20) DEFAULT 'Active', -- Active, Terminated, OnLeave
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### B. Attendance & Leave Records

```sql
-- Biometric / Geofenced Attendance Log
CREATE TABLE attendance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID REFERENCES employee_profiles(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    check_in_time TIMESTAMP WITH TIME ZONE NOT NULL,
    check_out_time TIMESTAMP WITH TIME ZONE,
    check_in_ip VARCHAR(45) NOT NULL,
    check_out_ip VARCHAR(45),
    latitude NUMERIC(9, 6), -- For Geofenced checkins
    longitude NUMERIC(9, 6),
    status VARCHAR(20) DEFAULT 'Present', -- Present, Late, Absent, HalfDay
    CONSTRAINT unique_employee_daily_attendance UNIQUE (employee_id, date)
);

-- Leave Application Register
CREATE TABLE leaves (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID REFERENCES employee_profiles(id) ON DELETE CASCADE,
    leave_type VARCHAR(50) NOT NULL, -- Sick, Casual, Earned, Maternity
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_days INT NOT NULL,
    reason TEXT,
    approval_status VARCHAR(20) DEFAULT 'Pending', -- Pending, Approved, Rejected
    approved_by UUID REFERENCES employee_profiles(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### C. Client & CRM Sales Pipeline

```sql
-- Client Accounts List
CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    company_name VARCHAR(150) NOT NULL,
    industry VARCHAR(100),
    primary_contact_name VARCHAR(100) NOT NULL,
    primary_contact_email VARCHAR(150) NOT NULL,
    billing_address TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- CRM Pipeline Tracking
CREATE TABLE leads (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
    source VARCHAR(100), -- LinkedIn, Referral, Inbound
    opportunity_value NUMERIC(15, 2) NOT NULL,
    stage VARCHAR(50) DEFAULT 'Prospecting', -- Qualification, Proposal, Negotiation, ClosedWon, ClosedLost
    assigned_to UUID REFERENCES employee_profiles(id),
    next_action_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### D. PMO, Sprints, & Activity Timers

```sql
-- Project Metadata (PMO Domain)
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE RESTRICT,
    name VARCHAR(150) NOT NULL,
    billing_model VARCHAR(50) NOT NULL, -- T&M, FixedPrice, Retainer
    budget_revenue NUMERIC(15, 2) NOT NULL, -- Total project value
    allocated_hours_budget INT,
    start_date DATE,
    end_date DATE,
    status VARCHAR(20) DEFAULT 'In Progress', -- Proposed, Active, Completed, Paused
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Agile Sprints Table
CREATE TABLE sprints (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    goal TEXT,
    status VARCHAR(20) DEFAULT 'Planning' -- Active, Closed, Planning
);

-- Sprint Tasks Checklist
CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sprint_id UUID REFERENCES sprints(id) ON DELETE CASCADE,
    assignee_id UUID REFERENCES employee_profiles(id),
    title VARCHAR(200) NOT NULL,
    description TEXT,
    priority VARCHAR(20) DEFAULT 'Medium', -- Low, Medium, High, Critical
    status VARCHAR(50) DEFAULT 'Todo', -- Todo, InProgress, Review, Done
    estimated_minutes INT DEFAULT 0,
    time_spent_seconds INT DEFAULT 0,
    last_started_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### E. Financial Ledgers

```sql
-- Client Invoices Records
CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID REFERENCES projects(id) ON DELETE RESTRICT,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    amount NUMERIC(15, 2) NOT NULL,
    tax_amount NUMERIC(15, 2) NOT NULL,
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'Unpaid', -- Draft, Unpaid, Paid, Overdue, Cancelled
    payment_reference VARCHAR(150),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Expense Claims Register
CREATE TABLE expenses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID REFERENCES employee_profiles(id) ON DELETE CASCADE,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    category VARCHAR(100) NOT NULL, -- Travel, SaaS, Hardware, Entertainment
    amount NUMERIC(15, 2) NOT NULL,
    description TEXT,
    claim_date DATE NOT NULL,
    approval_status VARCHAR(20) DEFAULT 'Pending', -- Pending, Approved, Rejected, Disbursed
    receipt_url VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

---

## 3. Indexing & Optimization Strategy

To sustain high throughput during active task timing and geofenced checkpoints across 10,000+ staff:

1. **Partitioning**: Partition the `attendance` and `tasks` tables by range of `date` (monthly partitions) to optimize query performance for reports.
2. **B-Tree Indexes**:
   ```sql
   CREATE INDEX idx_attendance_emp_date ON attendance(employee_id, date);
   CREATE INDEX idx_tasks_assignee_status ON tasks(assignee_id, status);
   CREATE INDEX idx_invoices_project_status ON invoices(project_id, status);
   ```
3. **Gin Indexes**: Used on unstructured JSON/text fields (like logs, resume data, and description searches):
   ```sql
   CREATE INDEX idx_tasks_desc_gin ON tasks USING gin(to_tsvector('english', description));
   ```