File size: 4,616 Bytes
0a515cd |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
-- PostgreSQL schema for KIllaDesign data model
-- Safe re-runnable DDL
-- Recommended: run inside a dedicated schema
CREATE SCHEMA IF NOT EXISTS kd;
SET search_path TO kd, public;
-- Domain definitions (optionally used for clarity)
CREATE DOMAIN IF NOT EXISTS money_aed AS NUMERIC(16,2)
CHECK (VALUE >= 0);
-- Lookup tables (optional, minimal to keep example concise)
CREATE TABLE IF NOT EXISTS project_types (
type TEXT PRIMARY KEY
);
INSERT INTO project_types(type) VALUES
('Mixed-Use'), ('Hospitality'), ('Office'), ('Residential'), ('Cultural')
ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS work_categories (
category TEXT PRIMARY KEY
);
INSERT INTO work_categories(category) VALUES
('Architecture'), ('Project Management'), ('Subcontractor')
ON CONFLICT DO NOTHING;
-- Core tables
CREATE TABLE IF NOT EXISTS projects (
project_id TEXT PRIMARY KEY,
project_name TEXT NOT NULL,
client_name TEXT NOT NULL,
contract_value_aed money_aed NOT NULL,
planned_cost_aed money_aed NOT NULL,
project_start_date DATE NOT NULL,
project_end_date DATE,
project_type TEXT REFERENCES project_types(type),
project_status TEXT NOT NULL,
current_phase TEXT
);
CREATE TABLE IF NOT EXISTS employees (
employee_id TEXT PRIMARY KEY,
employee_name TEXT NOT NULL,
department TEXT,
role TEXT,
hourly_rate_aed money_aed NOT NULL,
employment_type TEXT,
start_date DATE,
cost_category TEXT REFERENCES work_categories(category)
);
CREATE TABLE IF NOT EXISTS milestones (
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
milestone_name TEXT NOT NULL,
milestone_order INT NOT NULL,
planned_date DATE,
actual_date DATE,
status TEXT,
completion_percentage INT CHECK (completion_percentage BETWEEN 0 AND 100),
PRIMARY KEY (project_id, milestone_order)
);
CREATE TABLE IF NOT EXISTS invoices (
invoice_id TEXT PRIMARY KEY,
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
invoice_date DATE NOT NULL,
invoice_amount_aed money_aed NOT NULL,
due_date DATE,
payment_date DATE,
payment_status TEXT,
days_outstanding INT,
milestone_reference TEXT
);
CREATE TABLE IF NOT EXISTS subcontractors (
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
subcontractor_name TEXT NOT NULL,
service_type TEXT NOT NULL,
contract_amount_aed money_aed NOT NULL,
amount_invoiced_aed money_aed NOT NULL,
payment_status TEXT,
work_category TEXT REFERENCES work_categories(category),
PRIMARY KEY (project_id, subcontractor_name, service_type)
);
CREATE TABLE IF NOT EXISTS timesheets (
record_id TEXT PRIMARY KEY,
date DATE NOT NULL,
employee_id TEXT NOT NULL REFERENCES employees(employee_id) ON DELETE RESTRICT,
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
hours_worked NUMERIC(6,2) NOT NULL CHECK (hours_worked >= 0),
work_category TEXT REFERENCES work_categories(category),
task_description TEXT,
billable_hours NUMERIC(6,2) CHECK (billable_hours >= 0)
);
CREATE TABLE IF NOT EXISTS milestone_budgets (
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
milestone_name TEXT NOT NULL,
milestone_order INT NOT NULL,
planned_date DATE,
actual_date DATE,
status TEXT,
budgeted_hours NUMERIC(10,2),
budgeted_cost_aed money_aed,
actual_hours NUMERIC(10,2),
actual_cost_aed money_aed,
cumulative_budgeted_hours NUMERIC(10,2),
cumulative_budgeted_cost_aed money_aed,
cumulative_actual_hours NUMERIC(10,2),
cumulative_actual_cost_aed money_aed,
hours_variance NUMERIC(10,2),
cost_variance_aed money_aed,
schedule_variance_days INT,
budget_utilization_percent NUMERIC(5,2),
architecture_hours NUMERIC(10,2),
architecture_cost_aed money_aed,
pm_hours NUMERIC(10,2),
pm_cost_aed money_aed,
subcontractor_hours NUMERIC(10,2),
subcontractor_cost_aed money_aed,
notes TEXT,
PRIMARY KEY (project_id, milestone_order)
);
CREATE TABLE IF NOT EXISTS staff_allocation (
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
milestone_id TEXT,
milestone_name TEXT,
employee_id TEXT NOT NULL REFERENCES employees(employee_id) ON DELETE RESTRICT,
employee_name TEXT,
role TEXT,
category TEXT,
hours_allocated NUMERIC(10,2),
hours_worked NUMERIC(10,2),
hourly_rate_aed money_aed,
cost_aed money_aed,
utilization_percent NUMERIC(6,2),
skill_match_score NUMERIC(6,2),
availability_status TEXT,
start_date DATE,
end_date DATE,
performance_rating TEXT,
notes TEXT,
PRIMARY KEY (project_id, milestone_name, employee_id)
);
|