-- Notification Engine CREATE TABLE notifications ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL, user_id UUID NOT NULL, type VARCHAR(50), title VARCHAR(200), message TEXT, is_read BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT NOW() ); -- Analytics Snapshots (precomputed) CREATE TABLE analytics_snapshots ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL, month INT, year INT, headcount INT, attendance_rate DECIMAL(5,2), total_payroll DECIMAL(14,2), attrition_rate DECIMAL(5,2), updated_at TIMESTAMP );

-- Multi-Office GPS CREATE TABLE offices ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL, name VARCHAR(100), lat DECIMAL(10,8), lng DECIMAL(11,8), radius INT DEFAULT 100 ); -- Add to employees: ALTER TABLE employees ADD COLUMN office_id UUID REFERENCES offices(id); -- Performance Management CREATE TABLE performance_reviews ( id UUID PRIMARY KEY, employee_id UUID NOT NULL, reviewer_id UUID NOT NULL, period VARCHAR(20), okr_score DECIMAL(4,2), kpi_score DECIMAL(4,2), rating INT CHECK(rating BETWEEN 1 AND 5), notes TEXT, created_at TIMESTAMP );

-- Timesheet CREATE TABLE timesheets ( id UUID PRIMARY KEY, employee_id UUID NOT NULL, tenant_id UUID NOT NULL, project VARCHAR(100), task VARCHAR(200), hours DECIMAL(4,2), date DATE, status VARCHAR(20) DEFAULT 'draft' ); -- HR Tickets CREATE TABLE hr_tickets ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL, employee_id UUID NOT NULL, category VARCHAR(50), subject VARCHAR(200), description TEXT, status VARCHAR(20) DEFAULT 'open', assigned_to UUID, resolved_at TIMESTAMP, created_at TIMESTAMP );

// NEW ROUTES GET /api/notifications → list for current user PATCH /api/notifications/:id/read → mark read GET /api/search?q=&type= → global search GET /api/org-chart → hierarchy tree data GET /api/offices → multi-office list POST /api/offices → create office GET /api/timesheets → list timesheets POST /api/timesheets → submit timesheet GET /api/performance → review list POST /api/performance → create review GET /api/tickets → HR ticket list POST /api/tickets → raise ticket PUT /api/tickets/:id/resolve → resolve ticket GET /api/documents → document vault list POST /api/documents → upload document GET /api/leave-calendar → team calendar data

// ENHANCED ROUTES GET /api/analytics/overview → add snapshots table POST /api/expenses/ocr-scan → tesseract.js extract GET /api/ats/:id/offer-letter → PDF offer letter gen POST /api/ats/:id/ai-score → AI CV scoring GET /api/audit-logs → add old_data/new_data GET /api/leaves/calendar → team leave calendar // WEBSOCKET EVENTS ws: attendance.marked ws: leave.approved ws: expense.submitted ws: payroll.generated ws: notification.new // BACKGROUND JOBS (BullMQ) queue: payslip-pdf-gen queue: email-dispatch queue: analytics-rebuild queue: compliance-report