backend/migrations/005_master_data_sync.sqlSQL
CREATE TABLE IF NOT EXISTS sync_changes (
change_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
entity_type VARCHAR(40) NOT NULL,
entity_id CHAR(36) NOT NULL,
operation VARCHAR(20) NOT NULL,
row_version INT NOT NULL,
data_json LONGTEXT NOT NULL,
changed_at DATETIME NOT NULL,
INDEX sync_changes_tenant_cursor (tenant_id, change_id),
CONSTRAINT sync_changes_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);
CREATE TABLE IF NOT EXISTS sync_operations (
id CHAR(36) PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
operation_id VARCHAR(100) NOT NULL,
idempotency_key VARCHAR(200) NOT NULL,
request_hash CHAR(64) NOT NULL,
result_json LONGTEXT NULL,
http_status SMALLINT NULL,
created_at DATETIME NOT NULL,
completed_at DATETIME NULL,
UNIQUE KEY sync_operations_tenant_operation (tenant_id, operation_id),
UNIQUE KEY sync_operations_tenant_idempotency (tenant_id, idempotency_key),
CONSTRAINT sync_operations_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);
-- Seed the cursor with the current server state. NOT EXISTS keeps a partially
-- applied migration safely repeatable without duplicating the baseline.
INSERT INTO sync_changes (tenant_id, entity_type, entity_id, operation, row_version, data_json, changed_at)
SELECT c.tenant_id, 'customers', c.id, IF(c.deleted_at IS NULL, 'upsert', 'delete'), c.row_version,
JSON_OBJECT('id', c.id, 'tenant_id', c.tenant_id, 'name', c.name, 'contact', c.contact, 'email', c.email, 'phone', c.phone, 'address', c.address, 'row_version', c.row_version, 'created_at', c.created_at, 'updated_at', c.updated_at, 'deleted_at', c.deleted_at),
UTC_TIMESTAMP()
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM sync_changes sc WHERE sc.tenant_id = c.tenant_id AND sc.entity_type = 'customers' AND sc.entity_id = c.id);
INSERT INTO sync_changes (tenant_id, entity_type, entity_id, operation, row_version, data_json, changed_at)
SELECT s.tenant_id, 'sites', s.id, IF(s.deleted_at IS NULL, 'upsert', 'delete'), s.row_version,
JSON_OBJECT('id', s.id, 'tenant_id', s.tenant_id, 'customer_id', s.customer_id, 'name', s.name, 'address', s.address, 'row_version', s.row_version, 'created_at', s.created_at, 'updated_at', s.updated_at, 'deleted_at', s.deleted_at),
UTC_TIMESTAMP()
FROM sites s
WHERE NOT EXISTS (SELECT 1 FROM sync_changes sc WHERE sc.tenant_id = s.tenant_id AND sc.entity_type = 'sites' AND sc.entity_id = s.id);
INSERT INTO sync_changes (tenant_id, entity_type, entity_id, operation, row_version, data_json, changed_at)
SELECT j.tenant_id, 'jobs', j.id, IF(j.deleted_at IS NULL, 'upsert', 'delete'), j.row_version,
JSON_OBJECT('id', j.id, 'tenant_id', j.tenant_id, 'customer_id', j.customer_id, 'site_id', j.site_id, 'number', j.number, 'title', j.title, 'description', j.description, 'status', j.status, 'row_version', j.row_version, 'created_at', j.created_at, 'updated_at', j.updated_at, 'deleted_at', j.deleted_at),
UTC_TIMESTAMP()
FROM jobs j
WHERE NOT EXISTS (SELECT 1 FROM sync_changes sc WHERE sc.tenant_id = j.tenant_id AND sc.entity_type = 'jobs' AND sc.entity_id = j.id);
INSERT INTO sync_changes (tenant_id, entity_type, entity_id, operation, row_version, data_json, changed_at)
SELECT e.tenant_id, 'employee_profiles', e.id, IF(e.deleted_at IS NULL, 'upsert', 'delete'), e.row_version,
JSON_OBJECT('id', e.id, 'tenant_id', e.tenant_id, 'user_id', e.user_id, 'employee_number', e.employee_number, 'first_name', e.first_name, 'last_name', e.last_name, 'email', e.email, 'phone', e.phone, 'is_active', e.is_active, 'row_version', e.row_version, 'created_at', e.created_at, 'updated_at', e.updated_at, 'deleted_at', e.deleted_at),
UTC_TIMESTAMP()
FROM employee_profiles e
WHERE NOT EXISTS (SELECT 1 FROM sync_changes sc WHERE sc.tenant_id = e.tenant_id AND sc.entity_type = 'employee_profiles' AND sc.entity_id = e.id);
INSERT INTO sync_changes (tenant_id, entity_type, entity_id, operation, row_version, data_json, changed_at)
SELECT a.tenant_id, 'job_assignments', a.id, IF(a.deleted_at IS NULL, 'upsert', 'delete'), a.row_version,
JSON_OBJECT('id', a.id, 'tenant_id', a.tenant_id, 'job_id', a.job_id, 'employee_id', a.employee_id, 'assigned_from', a.assigned_from, 'assigned_until', a.assigned_until, 'row_version', a.row_version, 'created_at', a.created_at, 'updated_at', a.updated_at, 'deleted_at', a.deleted_at),
UTC_TIMESTAMP()
FROM job_assignments a
WHERE NOT EXISTS (SELECT 1 FROM sync_changes sc WHERE sc.tenant_id = a.tenant_id AND sc.entity_type = 'job_assignments' AND sc.entity_id = a.id);