Query

billing.db — installation_jobs

SQL

CREATE TABLE installation_jobs  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  job_number VARCHAR(50) UNIQUE NOT NULL,
  customer_name VARCHAR(255) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  customer_address TEXT NOT NULL,
  package_id INTEGER NOT NULL,
  installation_date DATE NOT NULL,
  installation_time VARCHAR(20),
  assigned_technician_id INTEGER,
  status VARCHAR(50) DEFAULT 'scheduled',
  priority VARCHAR(20) DEFAULT 'normal',
  notes TEXT,
  equipment_needed TEXT,
  estimated_duration INTEGER DEFAULT 120,
  created_by_admin_id INTEGER,
  completed_at DATETIME,
  completion_notes TEXT,
  customer_latitude DECIMAL(10, 8),
  customer_longitude DECIMAL(11, 8),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES packages(id),
  FOREIGN KEY (assigned_technician_id) REFERENCES technicians(id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
job_number VARCHAR(50) Rename | Drop
customer_name VARCHAR(255) Rename | Drop
customer_phone VARCHAR(20) Rename | Drop
customer_address TEXT Rename | Drop
package_id INTEGER Rename | Drop
installation_date DATE Rename | Drop
installation_time VARCHAR(20) ✓ Rename | Drop
assigned_technician_id INTEGER ✓ Rename | Drop
status VARCHAR(50) ✓ Rename | Drop
priority VARCHAR(20) ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
equipment_needed TEXT ✓ Rename | Drop
estimated_duration INTEGER ✓ Rename | Drop
created_by_admin_id INTEGER ✓ Rename | Drop
completed_at DATETIME ✓ Rename | Drop
completion_notes TEXT ✓ Rename | Drop
customer_latitude DECIMAL(10, 8) ✓ Rename | Drop
customer_longitude DECIMAL(11, 8) ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
assigned_technician_id technicians.id
package_id packages.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_installation_jobs_created created_at SQL 
CREATE INDEX idx_installation_jobs_created
ON installation_jobs(created_at)
Drop
idx_installation_jobs_date installation_date SQL 
CREATE INDEX idx_installation_jobs_date
ON installation_jobs(installation_date)
Drop
idx_installation_jobs_number job_number SQL 
CREATE INDEX idx_installation_jobs_number
ON installation_jobs(job_number)
Drop
idx_installation_jobs_status status SQL 
CREATE INDEX idx_installation_jobs_status
ON installation_jobs(status)
Drop
idx_installation_jobs_technician assigned_technician_id SQL 
CREATE INDEX idx_installation_jobs_technician
ON installation_jobs(assigned_technician_id)
Drop
sqlite_autoindex_installation_jobs_1 job_number ✓ SQL
-- no sql found --
 Drop

Triggers

Name SQL Drop?
update_installation_jobs_updated_at SQL 
CREATE TRIGGER update_installation_jobs_updated_at 
    AFTER UPDATE ON installation_jobs
    FOR EACH ROW
BEGIN
    UPDATE installation_jobs SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END
Drop