Query

billing.db — technicians

SQL

CREATE TABLE technicians  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  phone TEXT UNIQUE NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('technician', 'field_officer', 'collector')),
  email TEXT,
  notes TEXT,
  is_active INTEGER DEFAULT 1 CHECK (is_active IN (0, 1)),
  area_coverage TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_login DATETIME,
  whatsapp_group_id TEXT,
  join_date DATETIME
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
name TEXT Rename | Drop
phone TEXT Rename | Drop
role TEXT Rename | Drop
email TEXT ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
is_active INTEGER ✓ Rename | Drop
area_coverage TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop
last_login DATETIME ✓ Rename | Drop
whatsapp_group_id TEXT ✓ Rename | Drop
join_date DATETIME ✓ Rename | Drop

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_technicians_active is_active SQL 
CREATE INDEX idx_technicians_active
ON technicians(is_active)
Drop
idx_technicians_phone phone SQL 
CREATE INDEX idx_technicians_phone
ON technicians(phone)
Drop
idx_technicians_role role SQL 
CREATE INDEX idx_technicians_role
ON technicians(role)
Drop
idx_technicians_whatsapp_group whatsapp_group_id SQL 
CREATE INDEX idx_technicians_whatsapp_group
ON technicians(whatsapp_group_id)
Drop
sqlite_autoindex_technicians_1 phone ✓ SQL
-- no sql found --
 Drop

Triggers

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