File size: 4,885 Bytes
c8f4656 | 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 | """Database schema.
Kept apart from store.py so the SQL is readable in one place.
The `complaints` table started life as a classification log and is now the real
complaint entity. Rather than adding a parallel table, the extra columns are
added by an idempotent migration in store.migrate() -- existing rows (from the
classifier demo page) simply have no user and stay in status 'Submitted'.
"""
BASE_SCHEMA = """
CREATE TABLE IF NOT EXISTS labels (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
description TEXT NOT NULL DEFAULT '',
active INTEGER NOT NULL DEFAULT 1,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS complaints (
id INTEGER PRIMARY KEY AUTOINCREMENT,
original_text TEXT NOT NULL,
translated_text TEXT NOT NULL,
source_lang TEXT NOT NULL DEFAULT 'unknown',
was_translated INTEGER NOT NULL DEFAULT 0,
predicted_label TEXT NOT NULL,
confidence REAL NOT NULL,
scores_json TEXT NOT NULL DEFAULT '[]',
engine TEXT NOT NULL DEFAULT '',
took_ms INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_complaints_created ON complaints(created_at DESC);
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phone TEXT NOT NULL DEFAULT '',
password_hash TEXT NOT NULL,
language TEXT NOT NULL DEFAULT 'en',
is_admin INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS complaint_photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
filename TEXT NOT NULL,
mime TEXT NOT NULL DEFAULT 'image/jpeg',
bytes INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_photos_complaint ON complaint_photos(complaint_id);
-- One row per status change: this is what the tracking timeline renders.
CREATE TABLE IF NOT EXISTS status_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
status TEXT NOT NULL,
note TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_events_complaint ON status_events(complaint_id, id);
CREATE TABLE IF NOT EXISTS notifications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
complaint_id INTEGER REFERENCES complaints(id) ON DELETE CASCADE,
message TEXT NOT NULL,
is_read INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, id DESC);
CREATE TABLE IF NOT EXISTS feedback (
id INTEGER PRIMARY KEY AUTOINCREMENT,
complaint_id INTEGER NOT NULL UNIQUE REFERENCES complaints(id) ON DELETE CASCADE,
rating INTEGER NOT NULL,
comment TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS departments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
created_at TEXT NOT NULL
);
"""
# (column, DDL type/default) added to `complaints` if not already present.
COMPLAINT_COLUMNS = [
("ref", "TEXT"), # UC2026-000123
("user_id", "INTEGER"),
("category", "TEXT NOT NULL DEFAULT ''"), # final, admin may override
("location", "TEXT NOT NULL DEFAULT ''"),
("latitude", "REAL"),
("longitude", "REAL"),
("status", "TEXT NOT NULL DEFAULT 'Submitted'"),
("department", "TEXT NOT NULL DEFAULT ''"),
("extra_details", "TEXT NOT NULL DEFAULT ''"),
("updated_at", "TEXT"),
]
# The lifecycle a complaint moves through, in order.
STATUSES = ["Submitted", "Under Review", "Assigned", "In Progress", "Resolved"]
DEFAULT_DEPARTMENTS = [
"Municipal Engineering Department",
"Water Board",
"Electricity Board",
"Public Health Department",
"Waste Management Unit",
"Roads & Highways Authority",
"General Administration",
]
# Which department a category routes to by default when an admin assigns.
CATEGORY_DEPARTMENT = {
"Water Supply": "Water Board",
"Electricity": "Electricity Board",
"Street Lighting": "Electricity Board",
"Road & Transport": "Roads & Highways Authority",
"Waste & Sanitation": "Waste Management Unit",
"Public Health": "Public Health Department",
"Noise & Pollution": "Public Health Department",
"Building & Property": "Municipal Engineering Department",
}
|