1
CREATE TABLE IF NOT EXISTS customers (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(200) NOT NULL, contact VARCHAR(200) NOT NULL DEFAULT '', email VARCHAR(320) NOT NULL DEFAULT '', phone VARCHAR(80) NOT NULL DEFAULT '', address TEXT NOT NULL, row_version INT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL, INDEX customers_tenant_name (tenant_id, name), CONSTRAINT customers_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id));
2
CREATE TABLE IF NOT EXISTS sites (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, customer_id CHAR(36) NOT NULL, name VARCHAR(200) NOT NULL, address TEXT NOT NULL, row_version INT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL, INDEX sites_tenant_customer (tenant_id, customer_id), CONSTRAINT sites_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id), CONSTRAINT sites_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id));
3
CREATE TABLE IF NOT EXISTS jobs (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, customer_id CHAR(36) NOT NULL, site_id CHAR(36) NULL, number VARCHAR(100) NOT NULL, title VARCHAR(200) NOT NULL, description TEXT NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'open', row_version INT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL, UNIQUE KEY jobs_tenant_number (tenant_id, number), INDEX jobs_tenant_customer (tenant_id, customer_id), CONSTRAINT jobs_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id), CONSTRAINT jobs_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT jobs_site_fk FOREIGN KEY (site_id) REFERENCES sites(id));
4
CREATE TABLE IF NOT EXISTS job_assignments (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, job_id CHAR(36) NOT NULL, employee_id CHAR(36) NOT NULL, assigned_from DATETIME NULL, assigned_until DATETIME NULL, row_version INT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, deleted_at DATETIME NULL, UNIQUE KEY assignments_job_employee (job_id, employee_id), CONSTRAINT assignments_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id), CONSTRAINT assignments_job_fk FOREIGN KEY (job_id) REFERENCES jobs(id));
5