Data Model

The authoritative logical schema for the Sindh IT Portal — Facilitation Desk (SITP): every table, its columns, constraints, relationships, indexing, archival, migration, and data-classification rules, targeting MariaDB 10.11 with Prisma as the ORM.

Field	Value
Doc ID	05
Status	Draft
Owner	S&ITD / MAAHIR
Languages	EN (master) · UR · SD
Database	MariaDB 10.11.14 (InnoDB, `utf8mb4`) — not PostgreSQL
ORM	Prisma (MySQL/MariaDB driver)
Search	Meilisearch (multilingual full-text)
Related docs	`/specs/en/04-roles-permissions/` · `/specs/en/06-ticket-workflow/` · `09-ai-ocr-spec/en.md` · `/specs/en/11-security-compliance/` · `/specs/en/12-api-contract/` · `/specs/en/15-tech-architecture/` · `/specs/en/21-mom-meetings/`

1. Scope & How to Read This Document

This document defines the logical data model of SITP. It is the single source of truth for table names, columns, types, constraints, and relationships. It is consumed by:

Engineering — to author schema.prisma and Prisma migrations.
Integrations — every adapter (NADRA/SECP/FBR/SRB/PSEB/e-Office) reads/writes through these tables.
Analytics — Metabase models and the public transparency dashboard aggregate these tables.
Security review — §9 classifies PII and cross-references /specs/en/11-security-compliance/.

The model is grouped into ten domains. Each domain has (a) a Mermaid erDiagram, (b) a written description, and (c) one subsection per table. Section §4 holds all table definitions; §5 explains the most important cross-table relationships; §6–§9 cover indexing, archival, migration, and data classification.

Table count: 74 tables across 10 domains. Diagram count: 10 Mermaid erDiagram blocks (one per domain).

2. Conventions

These conventions apply uniformly. They are not repeated on every column.

2.1 Storage engine & character set

Aspect	Rule
Engine	`InnoDB` on every table (transactions, FK constraints, row-level locking). `MyISAM` is never used.
Character set	`utf8mb4` on the database, every table, every text column, and the connection (Prisma datasource URL + MariaDB user default). Legacy 3-byte `utf8` is forbidden — it cannot store all Sindhi/Urdu Arabic-script code points.
Collation	`utf8mb4_unicode_ci` is the documented default. In production the UCA-based `utf8mb4_unicode_520_ci` is recommended (per `/specs/en/15-tech-architecture/` §5.4) for more correct Urdu/Sindhi ordering; both are acceptable, picked once and applied consistently.
Server settings	`character_set_server=utf8mb4`, `collation_server=utf8mb4_unicode_ci` (or `_520_ci`).

2.2 Identifiers & surrogate keys

Every table has a surrogate id column: BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY (compact, join-friendly, sortable).
Meaningful business identifiers (ticket tracking_id = SITP-YYYY-<DEPT>-<NNNNNN>, CNIC, NTN, SECP registration number) are secondary unique columns, never the primary key, so merge/split/renumber and future sharding remain possible.
UUIDs (public tokens, webhook secrets, QR verification codes) are stored as CHAR(36) and generated via MariaDB's UUID() function or application-side UUIDv7; MariaDB has no native UUID type.

2.3 Standard audit columns

Every table carries these columns without exception. In the per-table column lists below they are abbreviated as a single row referencing this section to keep the lists readable.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`NOT NULL AUTO_INCREMENT PRIMARY KEY`	Surrogate PK.
`created_at`	`DATETIME(6)`	`NOT NULL DEFAULT CURRENT_TIMESTAMP(6)`	UTC insert time, microsecond precision.
`updated_at`	`DATETIME(6)`	`NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)`	UTC last-write time.
`created_by`	`BIGINT UNSIGNED`	`NULL`, FK → `users.id`	Actor that created the row. NULL for system/seed rows.
`updated_by`	`BIGINT UNSIGNED`	`NULL`, FK → `users.id`	Actor that last wrote the row.
`deleted_at`	`DATETIME(6)`	`NULL`	Soft-delete marker. Present on user-facing, auditable, and PII-bearing tables; absent on append-only or pure lookup tables (noted explicitly where so).

2.4 Naming

All identifiers are snake_case. Tables are plural nouns (tickets, representatives). Columns are singular (status, due_at).
Timestamps end in _at (created_at, resolved_at, sla_due_at); booleans are prefixed is_, has_, or requires_ (is_confidential, requires_approval).
Foreign keys are <singular_entity>_id (org_id, dept_id, rep_id). Composite FKs name both roles (source_ticket_id, target_ticket_id).
Enums are stored as MariaDB ENUM(...) (not free VARCHAR) for status/state/type/category columns whose domain is closed and small. New values ship as migrations.
Money is stored as integer minor units (PKR paisa) in BIGINT, never floating point, with explicit scale noted in the column comment (per /specs/en/15-tech-architecture/ §5.3). SITP is largely fee-free, so money columns are rare.
JSON is modeled as LONGTEXT and validated with MariaDB JSON_* functions via Prisma $queryRaw when needed (Prisma does not auto-detect MariaDB JSON quirks; see tech-arch §5.2 caveat).

2.5 Module prefixes (optional, applied in `schema.prisma`)

Per /specs/en/15-tech-architecture/ §5.3, the deployed schema MAY prefix tables by module (org_, usr_, tkt_, sla_, fil_, ai_, com_, not_, mtg_, ofc_, kb_, trn_, sys_, aud_, int_) so module ownership is visible without splitting databases. This document uses the un-prefixed logical names (as enumerated in the brief) for readability; the prefix is an implementation detail applied consistently in schema.prisma and does not change relationships.

2.6 Foreign keys & indexing

All FK relationships are declared as FOREIGN KEY ... REFERENCES ... ON DELETE RESTRICT ON UPDATE CASCADE unless noted otherwise (cascade deletes are rare and always explicit).
Every FK column carries an explicit secondary index (MariaDB auto-indexes only the FK child side; many-to-many join tables index both sides, often as a composite UNIQUE).
Soft-deleted rows (deleted_at IS NOT NULL) are excluded by application queries and by Prisma middleware (MariaDB filtered indexes are limited).

2.7 ORM & search

ORM = Prisma over the MariaDB driver. schema.prisma is derived from this document; one datasource block with provider = "mysql". Migrations are forward-only and CI-gated (see §8).
Full-text search is delegated to Meilisearch, not MariaDB FULLTEXT. MariaDB FULLTEXT is used only as a Latin-script fallback for exact-phrase admin queries. Multilingual (Urdu/Sindhi) tokenization, ranking, and similarity all run in Meilisearch (per tech-arch §5.5 and §8). A search-indexer worker listens to domain events and pushes redacted, locale-tagged documents to Meilisearch; confidential/VIP tickets are excluded or masked before indexing.

3. High-Level ERD (by Domain)

The model is split into ten Mermaid erDiagram blocks so each stays readable. Cross-domain foreign keys are mentioned in the written descriptions and shown where they are central to the domain.

3.1 Identity & Organization (companies, reps, verification)

erDiagram ORGANIZATIONS ||--o{ ORGANIZATION_LOCATIONS : "has" ORGANIZATIONS ||--o{ REPRESENTATIVES : "has" ORGANIZATIONS ||--o{ VERIFICATION_JOBS : "undergoes" ORGANIZATIONS ||--o{ CONSENTS : "records" REPRESENTATIVES ||--o{ REP_ROLE_ASSIGNMENTS : "holds" REPRESENTATIVES ||--o{ REP_PERMISSION_OVERRIDES : "has" REP_ROLE_ASSIGNMENTS }o--|| ROLES : "template" REPRESENTATIVES ||--o| USERS : "login identity 1:1" VERIFICATION_JOBS ||--o{ VERIFICATION_DOCUMENTS : "consumes" VERIFICATION_DOCUMENTS }o--|| ORGANIZATIONS : "belongs to" ORGANIZATIONS { bigint id PK varchar entity_type "ENUM" varchar legal_name varchar secp_registration_no varchar verification_status "ENUM" tinyint kyc_level char locale "en/ur/sd" } REPRESENTATIVES { bigint id PK bigint org_id FK char cnic varchar designation varchar role_at_company varchar email varchar mobile_e164 varchar whatsapp_e164 char locale varchar two_fa_method "ENUM" varchar status "ENUM" } VERIFICATION_JOBS { bigint id PK bigint org_id FK varchar provider "ENUM SECP/FBR/SRB/PSEB/NADRA/DOMAIN" varchar status "ENUM" longtext result_json longtext raw_payload }

Written description. An organization (one of five entity types — SECP company, sole proprietor/partnership, freelancer, foreign branch, early startup) owns one or more representatives and one or more organization_locations. Exactly one representative holds the Primary Authorized Representative role (enforced at the application layer plus a partial unique index on rep_role_assignments where role = Primary). Each representative maps 1:1 to a users row for login identity. Registration is file-first: an organization is created with verification_status = provisional, kyc_level = 0, and can file tickets immediately; background checks run as verification_jobs (one per provider: SECP, FBR/NTN, SRB, PSEB, NADRA, domain-email) each consuming verification_documents. On all-checks-pass the organization moves to verified and kyc_level rises; on failure it moves to on_hold and an appeal may be raised. consents records the organization's consent to data processing and to each integration lookup.

3.2 Departments (government org tree, hours, holidays)

erDiagram DEPARTMENTS ||--o{ DEPARTMENTS : "parent_id self-ref tree" DEPARTMENTS ||--o{ DEPARTMENT_SECTIONS : "has" DEPARTMENTS ||--o{ BUSINESS_HOURS : "has" DEPARTMENTS ||--o{ DEPARTMENT_HOLIDAYS : "observes" HOLIDAY_CALENDAR ||--o{ DEPARTMENT_HOLIDAYS : "specialized by" DEPARTMENTS { bigint id PK bigint parent_id FK "NULL = top-level" varchar code "LBR, SITD" varchar name_en varchar name_ur varchar name_sd tinyint is_owner_dept varchar status "ENUM active/inactive" } DEPARTMENT_SECTIONS { bigint id PK bigint dept_id FK bigint parent_section_id FK "NULL" varchar code varchar name_en } DEPARTMENT_HOLIDAYS { bigint id PK bigint dept_id FK bigint holiday_id FK tinyint observes } HOLIDAY_CALENDAR { bigint id PK date holiday_date varchar name_en varchar name_ur varchar name_sd varchar region "Sindh" } BUSINESS_HOURS { bigint id PK bigint dept_id FK tinyint weekday "0..6" time opens_at time closes_at tinyint is_working_day }

Written description. departments is a self-referencing tree via parent_id: a top-level node is a Department (Labour, Finance, S&ITD, …); any node below it is a Section / Sub-department and can nest freely at any depth. department_sections is a convenience projection for leaf units that are independently assignable. The owning department S&ITD is flagged is_owner_dept = 1. holiday_calendar holds Sindh public holidays (used to pause SLA — see _context.md §5); department_holidays lets a department override (observe extra or skip a listed holiday). business_hours defines per-department working hours per weekday; SLA timers run only within working hours on working days.

3.3 Users, Roles & Officials CMS

erDiagram USERS ||--o{ USER_ROLE_ASSIGNMENTS : "has" USERS ||--o{ USER_PERMISSION_OVERRIDES : "has" USERS ||--o| REPRESENTATIVES : "company login 1:1" ROLES ||--o{ USER_ROLE_ASSIGNMENTS : "template" ROLES ||--o{ ROLE_TEMPLATE_PERMISSIONS : "grants" ROLES ||--o{ REP_ROLE_ASSIGNMENTS : "company-side templates" OFFICIALS ||--o{ OFFICIAL_TERMS : "date-scoped tenure" OFFICIALS }o--|| DEPARTMENTS : "affiliated" MEDIA_LIBRARY ||--o{ OFFICIALS : "portrait" USERS }o--|| DEPARTMENTS : "staff belong to" USERS { bigint id PK char keycloak_sub "OIDC UUID UNIQUE" varchar display_name varchar email "UNIQUE" char locale varchar two_fa_method varchar status "ENUM" tinyint is_certified bigint dept_id FK "staff" bigint rep_id FK "company" } ROLES { bigint id PK varchar code "SUPER_ADMIN, OFFICER, PRIMARY_REP" varchar side "ENUM gov/company/oversight/other" varchar name_en } OFFICIALS { bigint id PK varchar title "ENUM Minister/Secretary/DG/SACM" varchar full_name_en varchar full_name_ur varchar full_name_sd bigint portrait_media_id FK longtext message_en tinyint is_current } OFFICIAL_TERMS { bigint id PK bigint official_id FK date effective_from date effective_to "NULL = open" varchar designation }

Written description. users is the unified login identity. Government staff carry a dept_id; company representatives carry a rep_id (1:1 to representatives). Every user has one or more user_role_assignments referencing a roles template (SUPER_ADMIN, SITD_FACILITATION_OFFICER, DEPT_ADMIN, OFFICER, DG, SECRETARY, MINISTER, READONLY_AUDITOR, PRIMARY_REP, ADMIN_REP, FILER, VIEWER, NOTIFY_ONLY, CITIZEN, SERVICE_ACCOUNT). The role template grants a baseline via role_template_permissions; per-user grants or revocations are stored in user_permission_overrides (the granular-override layer, see /specs/en/04-roles-permissions/ §6–§7). The Officials CMS (officials, official_terms, media_library) holds date-scoped records of the Minister/SACM, Secretary, and DG so that official letters and dashboards render the correct name for the date the letter was issued — historical accuracy is enforced by joining on official_terms.effective_from/effective_to.

3.4 Tickets & Workflow

erDiagram TICKETS ||--o{ TICKET_THREADS : "has" TICKETS ||--o{ TICKET_SUBTASKS : "decomposes into" TICKETS ||--o{ TICKET_LINKS : "source" TICKETS ||--o{ TICKET_LINKS : "target" TICKETS ||--o{ TICKET_WATCHERS : "watched by" TICKETS ||--o{ TICKET_ATTACHMENTS : "carries" TICKETS ||--o{ TICKET_HISTORY : "audited in" TICKETS ||--o{ RESOLUTION_EVIDENCE : "proven by" TICKETS ||--o{ APPEALS : "may be appealed" TICKETS ||--o{ CSAT_RESPONSES : "rated by" TICKETS }o--|| ORGANIZATIONS : "filed by company" TICKETS }o--|| DEPARTMENTS : "routed to" TICKETS }o--|| DEPARTMENT_SECTIONS : "assigned to section" TICKETS }o--|| TICKET_CATEGORIES : "classified as" TICKETS }o--|| REPRESENTATIVES : "filer_rep_id" TICKET_THREADS ||--o{ TICKET_MESSAGES : "contains" TICKET_MESSAGES ||--o{ TICKET_ATTACHMENTS : "may attach" TICKETS { bigint id PK varchar tracking_id "SITP-YYYY-DEPT-NNNNNN UNIQUE" varchar status "ENUM" varchar priority "ENUM" bigint category_id FK bigint dept_id FK bigint section_id FK bigint org_id FK bigint filer_rep_id FK char locale tinyint is_confidential tinyint is_vip datetime sla_due_at datetime sla_paused_until datetime resolved_at datetime closed_at } TICKET_THREADS { bigint id PK bigint ticket_id FK varchar visibility "ENUM public/internal" } TICKET_MESSAGES { bigint id PK bigint thread_id FK bigint author_user_id FK longtext body varchar source "ENUM web/email/sms/whatsapp/ivr" } TICKET_LINKS { bigint id PK bigint source_ticket_id FK bigint target_ticket_id FK varchar link_type "ENUM merge/split/relate/blocks/duplicate" }

Written description. A ticket is the central artifact. Its tracking_id (SITP-2026-LBR-000123) is the user-facing identifier; the surrogate id is the join key. A ticket belongs to one organization (the filer), is routed to one department and optionally a section, is classified by a ticket_category, and is filed by a representative (filer_rep_id). A ticket has two ticket_threads: one public (visible to the company) and one internal (government-only); each thread holds ticket_messages whose source records the intake channel (web, email, SMS, WhatsApp, IVR). ticket_subtasks model split tickets; ticket_links model merge/split/relate/duplicate/blocking relationships (a row references both source_ticket_id and target_ticket_id with a link_type). ticket_watchers records the CC/escalation-added audience. Every state-changing event is appended to ticket_history (the per-ticket audit; the global audit is audit_logs). Resolution requires resolution_evidence (the proof-of-resolution gate); appeals and CSAT close the lifecycle.

3.5 SLA, Escalation & Resolution

erDiagram SLA_DEFINITIONS ||--o{ TICKETS : "governs" ESCALATION_RULES ||--o{ ESCALATION_EVENTS : "fires" TICKETS ||--o{ ESCALATION_EVENTS : "escalated by" TICKETS ||--o{ SLA_PAUSE_EVENTS : "paused by" SLA_DEFINITIONS }o--|| DEPARTMENTS : "per dept" SLA_DEFINITIONS }o--|| TICKET_CATEGORIES : "per category" ESCALATION_RULES }o--|| ROLES : "target oversight role" SLA_DEFINITIONS { bigint id PK bigint dept_id FK bigint category_id FK "NULL = dept default" varchar priority "ENUM" int first_response_hours int resolution_hours tinyint pauses_on_await tinyint pauses_on_holiday } ESCALATION_RULES { bigint id PK bigint dept_id FK tinyint tier "1=DG 2=Sec 3=Min" int days_after_open bigint target_role_id FK varchar mode "ENUM notify/notify+action" } ESCALATION_EVENTS { bigint id PK bigint ticket_id FK bigint rule_id FK bigint target_user_id FK datetime fired_at varchar outcome } SLA_PAUSE_EVENTS { bigint id PK bigint ticket_id FK varchar reason "ENUM await/weekend/holiday/manual" datetime paused_at datetime resumed_at int paused_seconds }

Written description. sla_definitions defines first-response and resolution targets per (department, category, priority); the platform defaults are 2/5/10 days (see _context.md §5), fully overridable here. escalation_rules defines the tier ladder per department — by default tier 1 → DG at day 2, tier 2 → Secretary at day 7 (2+5), tier 3 → Minister/SACM at day 17 (2+5+10), each pointing at a target_role and a mode (notify or notify+action, mirroring the configurable oversight powers in /specs/en/04-roles-permissions/ §9). When a tier fires, an escalation_events row is written and the target user is added as a watcher. sla_pause_events records every SLA clock pause/resume (await-company, weekend, Sindh public holiday, manual) with the duration so the effective SLA can be reconstructed forensically. resolution_evidence enforces the proof gate; appeals and csat_responses are linked from the ticket (shown in §3.4).

3.6 AI

erDiagram AI_ENGINE_CONFIGS ||--o{ AI_RUNS : "executed by" AI_RUNS ||--o{ EXTRACTED_ACTION_ITEMS : "produces" TICKETS ||--o{ AI_RUNS : "routing/summary/redaction" MOM ||--o{ AI_RUNS : "action-item extraction" EXTRACTED_ACTION_ITEMS ||--o{ TICKET_SUBTASKS : "confirmed into" AI_ENGINE_CONFIGS { bigint id PK varchar feature "ocr/summary/routing/.../mom_extract" varchar provider "ENUM azure/google/aws/ollama/tesseract/whisper" varchar model varchar api_endpoint tinyint is_cloud tinyint enabled varchar sensitivity_class "ENUM public/internal/confidential/restricted" } AI_RUNS { bigint id PK bigint engine_config_id FK varchar feature varchar input_ref "ticket:123 / mom:45" longtext input_summary "redacted" longtext output_json int prompt_tokens int completion_tokens decimal cost_usd varchar status "ENUM ok/fallback/failed/redacted" tinyint was_redacted int latency_ms } EXTRACTED_ACTION_ITEMS { bigint id PK bigint ai_run_id FK bigint mom_id FK varchar owner_text bigint owner_user_id FK date due_date varchar action_text varchar status "ENUM proposed/confirmed/converted/rejected" bigint subtask_id FK }

Written description. ai_engine_configs is the pluggable engine registry: for each of the 11 AI capabilities (+ transcription), one or more (provider, model, is_cloud) rows are configured, each tagged with the data-sensitivity class it is allowed to serve (cloud engines never serve restricted/confidential raw PII — see /specs/en/15-tech-architecture/ §6). ai_runs records every AI invocation: the feature, the resolved engine, a redacted input snapshot, the structured output, token counts, cost, latency, status (ok/fallback/failed/redacted), and a was_redacted flag. extracted_action_items holds the structured output of MoM action-item extraction (owner text + resolved user, due date, action text, status); when an officer confirms an item, it is converted into a ticket_subtasks row and status flips to converted.

3.7 Comms & Notifications

erDiagram USERS ||--o{ MESSAGES : "DM author" USERS ||--o{ CHANNEL_MEMBERSHIPS : "joins" CHANNELS ||--o{ CHANNEL_MEMBERSHIPS : "has" CHANNELS ||--o{ CHANNEL_MESSAGES : "contains" CHANNEL_MESSAGES ||--o{ THREADED_REPLIES : "replies" CHANNEL_MESSAGES ||--o{ MESSAGE_READS : "read receipts" USERS ||--o{ NOTIFICATIONS : "receives" NOTIFICATION_TEMPLATES ||--o{ NOTIFICATIONS : "rendered from" USERS ||--o{ NOTIFICATION_PREFERENCES : "configures" INBOUND_REPLIES }o--|| TICKETS : "appended to" CHANNELS { bigint id PK varchar code varchar type "ENUM dm/group/channel" bigint dept_id FK varchar name_en } CHANNEL_MESSAGES { bigint id PK bigint channel_id FK bigint author_user_id FK bigint parent_message_id FK longtext body } NOTIFICATIONS { bigint id PK bigint user_id FK bigint template_id FK varchar channel "ENUM email/sms/whatsapp/in_app" varchar event_key longtext payload_json char locale varchar status "ENUM queued/sent/delivered/failed" datetime sent_at } NOTIFICATION_TEMPLATES { bigint id PK varchar event_key char locale varchar channel varchar subject longtext body } INBOUND_REPLIES { bigint id PK varchar source "ENUM email/whatsapp/sms" varchar from_address varchar external_message_id bigint ticket_id FK longtext body bigint created_message_id FK }

Written description. The 3-tier internal comms model maps to two table families: DMs and group DMs use messages (org-wide inbox, simplified) and the full channel-based chat (channels, channel_memberships, channel_messages) with threaded replies (threaded_replies, modeled as channel_messages self-referencing parent_message_id) and read receipts (message_reads). The notifications pipeline is separate: notification_templates holds multilingual templates keyed by event_key + locale + channel; notifications records every outbound notification per user/channel with delivery status; notification_preferences is the per-user preference center (channel opt-ins, digest cadence, quiet hours). inbound_replies captures two-way replies (email via Mailjet inbound parse, WhatsApp via webhook, SMS) parsed back into the originating ticket and surfaced as a ticket_message.

3.8 Meetings, TRI & MoM

erDiagram TICKETS ||--o{ MEETINGS : "stalled ticket to TRI" MEETINGS ||--o{ MEETING_ATTENDEES : "attended by" MEETINGS ||--|| MOM : "produces 1:1" MEETINGS ||--o{ MEETING_RECORDINGS : "recorded as" MOM ||--o{ MOM_APPROVALS : "approved by" MOM ||--o{ MOM_DISTRIBUTIONS : "shared to" MOM ||--o{ MOM_ACKNOWLEDGMENTS : "acked by" MOM ||--o{ AI_RUNS : "OCR + extract" MOM ||--o{ EXTRACTED_ACTION_ITEMS : "yields" MEETINGS { bigint id PK bigint ticket_id FK varchar type "ENUM tri/hearing/internal" varchar modality "ENUM virtual/physical/hybrid" varchar video_provider "ENUM zoom/meet/teams/null" varchar join_url datetime scheduled_at datetime started_at datetime ended_at bigint dept_id FK } MEETING_ATTENDEES { bigint id PK bigint meeting_id FK bigint user_id FK varchar name varchar party "ENUM company/sitd/department/external" varchar attendance_status } MOM { bigint id PK bigint meeting_id FK int version varchar status "ENUM draft/approved/published" tinyint sensitive_requires_approval longtext body_en longtext body_ur longtext body_sd bigint uploaded_by FK datetime published_at } MOM_APPROVALS { bigint id PK bigint mom_id FK bigint approver_user_id FK varchar decision "ENUM approved/rejected/changes_requested" longtext note } MEETING_RECORDINGS { bigint id PK bigint meeting_id FK bigint media_id FK int duration_seconds tinyint transcribed }

Written description. A meeting is triggered from a stalled ticket (TRI = Company + S&ITD facilitator + concerned Department), or scheduled as a hearing or internal meeting. Modality is virtual/physical/hybrid; for virtual/hybrid the video_provider (Zoom/Meet/Teams) and a time-limited join_url are stored. Each meeting produces exactly one mom (1:1), which is versioned (version increments on edit) and moves draft → approved → published. For sensitive/VIP tickets the sensitive_requires_approval flag forces a mom_approvals row from the Chair/DG before publish; for normal tickets the uploader publishes directly. On publish, mom_distributions records every channel share (email + in-app + SMS/WhatsApp) and mom_acknowledgments tracks who acknowledged. meeting_recordings links the recording blob and whether it was transcribed. MoM is upload-first (PDF/Word/images); on upload, OCR + AI extraction (ai_runs, feature = mom_extract) produce extracted_action_items that the officer confirms into ticket_subtasks (see §3.6).

3.9 Officials & Media, Knowledge, Content & Training

erDiagram MEDIA_LIBRARY ||--o{ KB_ARTICLES : "inline images" MEDIA_LIBRARY ||--o{ OFFICIALS : "portrait" DEPARTMENTS ||--o{ KB_ARTICLES : "owned by" DEPARTMENTS ||--o{ SERVICE_CATALOG_ENTRIES : "offered by" KB_ARTICLES ||--o{ DOCUMENTS : "attached forms" TRAINING_COURSES ||--o{ COURSE_ENROLLMENTS : "taken via" TRAINING_COURSES ||--o{ TRAINING_EXAMS : "assessed by" TRAINING_EXAMS ||--o{ EXAM_ATTEMPTS : "attempted" COURSE_ENROLLMENTS ||--o{ CERTIFICATIONS : "yields" USERS ||--o{ COURSE_ENROLLMENTS : "enrollee" USERS ||--o{ EXAM_ATTEMPTS : "examinee" KB_ARTICLES { bigint id PK varchar slug int version varchar status "ENUM draft/review/published/archived" varchar title_en longtext body_en longtext body_ur longtext body_sd bigint dept_id FK tinyint is_featured } SERVICE_CATALOG_ENTRIES { bigint id PK bigint dept_id FK varchar code varchar name_en bigint default_sla_definition_id FK } SOP_DOCUMENTS { bigint id PK bigint dept_id FK int version varchar status bigint doc_id FK } CIRCULARS { bigint id PK varchar title_en datetime published_at tinyint is_pinned } DOCUMENTS { bigint id PK varchar kind "ENUM form/sop/circular/repository/letter" varchar title bigint media_id FK char checksum_sha256 } TRAINING_COURSES { bigint id PK varchar code varchar title_en int duration_minutes tinyint required_for_role } TRAINING_EXAMS { bigint id PK bigint course_id FK int pass_threshold_pct int time_limit_minutes } EXAM_ATTEMPTS { bigint id PK bigint exam_id FK bigint user_id FK int score_pct varchar result "ENUM pass/fail/incomplete" datetime started_at datetime finished_at } CERTIFICATIONS { bigint id PK bigint user_id FK bigint course_id FK bigint attempt_id FK varchar certificate_code "UNIQUE" date issued_on date expires_on tinyint is_valid }

Written description. The Knowledge Base (kb_articles) is versioned and trilingual (title_en/body_en/.../_sd), owned per department, and flows through draft → review → published → archived. sop_documents, service_catalog_entries, circulars, and the general documents repository (forms, SOPs, circulars, downloadable repository items) cover Module J (KB + SOPs) and Module N (Suggestion & Content Portal). The Training LMS-lite (training_courses, course_enrollments, training_exams, exam_attempts, certifications) implements the exam-gated certification (Module O) that all government staff must pass before the users.is_certified flag is set and live-ticket access is unlocked (see /specs/en/04-roles-permissions/ §14). Certifications carry issued_on/expires_on for recurring re-certification (default annual). media_library is the shared asset store (uploads to MinIO with metadata) referenced by officials, KB, and inline article images.

3.10 System & Configuration

erDiagram FEATURE_FLAGS ||--o{ FEATURE_FLAGS : "scope override chain" USERS ||--o{ AUDIT_LOGS : "actor" INTEGRATIONS_CONFIGS ||--o{ WEBHOOKS : "fires" QR_VERIFIABLE_DOCUMENTS }o--|| DOCUMENTS : "proves" SYSTEM_SETTINGS }o--|| NOTIFICATION_TEMPLATES : "renders via" FEATURE_FLAGS { bigint id PK varchar flag_key "UNIQUE" tinyint enabled varchar scope "ENUM platform/dept/env/user_segment" bigint dept_id FK varchar env "ENUM dev/staging/prod" tinyint default_value varchar description } AUDIT_LOGS { bigint id PK bigint actor_user_id FK varchar action varchar entity_type bigint entity_id longtext before_json longtext after_json varchar ip_address char request_id datetime occurred_at } INTEGRATIONS_CONFIGS { bigint id PK varchar provider "ENUM nadra/secp/fbr/srb/pseb/eoffice/oidc/mailjet/sms/whatsapp" varchar base_url varchar vault_ref "secret path never the secret" tinyint enabled int timeout_ms varchar env } WEBHOOKS { bigint id PK varchar event_key varchar target_url char secret_hash tinyint enabled varchar last_status datetime last_fired_at } OPEN_DATA_EXPORTS { bigint id PK varchar dataset varchar format "ENUM csv/xlsx/json/pdf" bigint media_id FK datetime generated_at char hash_sha256 } QR_VERIFIABLE_DOCUMENTS { bigint id PK bigint document_id FK char qr_token "UNIQUE" char payload_hash datetime issued_at datetime expires_at tinyint revoked } SYSTEM_SETTINGS { bigint id PK varchar setting_key "UNIQUE" longtext value varchar category "ENUM smtp/mailjet/sms/whatsapp/general/branding" tinyint is_secret }

Written description. feature_flags is the runtime-toggle table; a flag's effective value is resolved by the chain platform-default → department-override → env-override → user-segment-override → off (first match wins), cached in Redis and audit-logged on change (see /specs/en/15-tech-architecture/ §11). audit_logs is the append-only global audit (never UPDATE or DELETE; partitioned by month — see §7); every state-changing privileged action writes a row with before/after JSON, actor, IP, and request id for correlation. integrations_configs stores per-provider settings for NADRA/SECP/FBR/SRB/PSEB/NITB e-Office/OIDC/Mailjet/SMS/WhatsApp, with secrets held only as a vault_ref path into the secrets vault — never the secret itself. webhooks defines outbound webhook subscriptions (HMAC-signed). open_data_exports records each transparency-dashboard / open-data export blob. qr_verifiable_documents binds a generated official letter (documents) to a QR token + content hash so the public can verify letter authenticity and detect forgery. system_settings is the general key-value store (SMTP, Mailjet, SMS gateway, WhatsApp, branding) with is_secret marking rows whose value lives only in the vault.

4. Table Definitions

Notation: every table includes the standard audit columns (§2.3). The row _(standard audit)_ in each table references that block to keep the lists readable. FK = foreign key; UQ = unique; NN = not null; PK = primary key.

4.1 Identity & Organization

`organizations`

Purpose: A registered entity (company/individual) that files tickets. Five entity types drive a conditional form; file-first, verify-in-parallel lifecycle.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`	Surrogate.
`entity_type`	`ENUM('secp_company','sole_proprietor','freelancer','foreign_branch','early_startup')`	`NN`	Drives conditional fields.
`legal_name`	`VARCHAR(255)`	`NN`	Registered name.
`trading_name`	`VARCHAR(255)`	`NULL`	Optional DBA.
`secp_registration_no`	`VARCHAR(32)`	`NULL, UQ`	SECP companies only.
`ntn`	`VARCHAR(20)`	`NULL, UQ`	FBR National Tax Number.
`srb_tax_id`	`VARCHAR(32)`	`NULL`	Sindh Revenue Board.
`pseb_membership_no`	`VARCHAR(32)`	`NULL`	PSEB membership.
`domain_email_domain`	`VARCHAR(255)`	`NULL`	Verified domain for rep email proof.
`verification_status`	`ENUM('provisional','verified','on_hold','suspended','dissolved')`	`NN DEFAULT 'provisional'`	Lifecycle.
`kyc_level`	`TINYINT UNSIGNED`	`NN DEFAULT 0`	Depth of checks passed.
`primary_rep_id`	`BIGINT UNSIGNED`	`NULL, FK → representatives.id`	Denormalized exactly-one pointer; maintained on transfer.
`locale`	`CHAR(3)`	`NN DEFAULT 'en'`	`en`/`ur`/`sd`.
`registered_at`	`DATETIME(6)`	`NN`	When the org account was created.
`re_atted_due_at`	`DATE`	`NULL`	Next periodic re-attestation.
(standard audit)	—	see §2.3	`+deleted_at` (soft-delete).

`organization_locations`

Purpose: One or more physical addresses per organization.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`org_id`	`BIGINT UNSIGNED`	`NN, FK → organizations.id`, indexed
`label`	`VARCHAR(64)`	`NULL`	e.g. "Head office", "Karachi branch".
`address_line1`	`VARCHAR(255)`	`NN`
`address_line2`	`VARCHAR(255)`	`NULL`
`city`	`VARCHAR(64)`	`NN`
`district`	`VARCHAR(64)`	`NULL`	For GIS heatmap.
`province`	`VARCHAR(64)`	`NN DEFAULT 'Sindh'`
`postal_code`	`VARCHAR(16)`	`NULL`
`country`	`VARCHAR(64)`	`NN DEFAULT 'Pakistan'`
`geo_lat`	`DECIMAL(10,7)`	`NULL`	Optional pin.
`geo_lng`	`DECIMAL(10,7)`	`NULL`	Optional pin.
`is_primary`	`TINYINT(1)`	`NN DEFAULT 0`	One primary per org.
(standard audit)	—	see §2.3	`+deleted_at`.

`representatives`

Purpose: Multiple authorized reps per org; one Primary mandatory. Each maps 1:1 to a login users row.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`org_id`	`BIGINT UNSIGNED`	`NN, FK → organizations.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NULL, UQ, FK → users.id`	Login identity (1:1).
`cnic`	`CHAR(15)`	`NULL`	13-digit + format; PII — encrypted (see §9).
`full_name`	`VARCHAR(255)`	`NN`
`designation`	`VARCHAR(128)`	`NULL`	Title.
`role_at_company`	`VARCHAR(128)`	`NULL`	Functional role, free text.
`email`	`VARCHAR(255)`	`NN, UQ`	Domain verified where applicable.
`mobile_e164`	`VARCHAR(16)`	`NN`	E.164.
`whatsapp_e164`	`VARCHAR(16)`	`NULL`	Optional, for WhatsApp channel.
`locale`	`CHAR(3)`	`NN DEFAULT 'en'`
`two_fa_method`	`ENUM('totp','sms','none')`	`NN DEFAULT 'totp'`
`status`	`ENUM('invited','active','revoked','transferred')`	`NN DEFAULT 'invited'`
`is_primary`	`TINYINT(1)`	`NN DEFAULT 0`	Mirror of role assignment for fast checks.
`accepted_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`rep_role_assignments`

Purpose: Binds a representative to a company-side role template (Primary/Admin/Filer/Viewer/Notify).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`rep_id`	`BIGINT UNSIGNED`	`NN, FK → representatives.id`, indexed
`role_id`	`BIGINT UNSIGNED`	`NN, FK → roles.id`	Company-side template.
`assigned_by`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`assigned_at`	`DATETIME(6)`	`NN`
`revoked_at`	`DATETIME(6)`	`NULL`
`reason_code`	`VARCHAR(64)`	`NULL`	Audit reason.
(standard audit)	—	see §2.3

`rep_permission_overrides`

Purpose: Granular per-rep grant/revoke on individual capabilities (see roles doc §7).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`rep_id`	`BIGINT UNSIGNED`	`NN, FK → representatives.id`, indexed
`capability_key`	`VARCHAR(64)`	`NN`	e.g. `ticket.file`, `company.export`.
`effect`	`ENUM('grant','revoke')`	`NN`
`scope_json`	`LONGTEXT`	`NULL`	Optional scope refinement.
`reason`	`VARCHAR(255)`	`NULL`	Audit rationale.
(standard audit)	—	see §2.3

`verification_jobs`

Purpose: A background verification lookup (SECP/FBR/SRB/PSEB/NADRA/domain-email) against an org or rep.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`org_id`	`BIGINT UNSIGNED`	`NN, FK → organizations.id`, indexed
`rep_id`	`BIGINT UNSIGNED`	`NULL, FK → representatives.id`	For NADRA CNIC checks on a rep.
`provider`	`ENUM('secp','fbr','srb','pseb','nadra','domain_email')`	`NN`
`status`	`ENUM('queued','running','passed','failed','error')`	`NN DEFAULT 'queued'`
`result_json`	`LONGTEXT`	`NULL`	Normalized result (redacted PII).
`raw_payload`	`LONGTEXT`	`NULL`	Raw response (encrypted; PII — see §9).
`provider_reference`	`VARCHAR(128)`	`NULL`	External transaction id.
`started_at`	`DATETIME(6)`	`NULL`
`finished_at`	`DATETIME(6)`	`NULL`
`error_message`	`TEXT`	`NULL`
(standard audit)	—	see §2.3

`verification_documents`

Purpose: Documents submitted as proof during registration / re-attestation (e.g. SECP certificate, bank letter).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`org_id`	`BIGINT UNSIGNED`	`NN, FK → organizations.id`, indexed
`verification_job_id`	`BIGINT UNSIGNED`	`NULL, FK → verification_jobs.id`	If linked to a check.
`media_id`	`BIGINT UNSIGNED`	`NN, FK → media_library.id`	The uploaded blob ref.
`doc_type`	`VARCHAR(64)`	`NN`	e.g. `secp_certificate`, `bank_proof`.
`status`	`ENUM('pending','verified','rejected')`	`NN DEFAULT 'pending'`
`notes`	`TEXT`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`consents`

Purpose: Records consent (data processing, integration lookups, marketing) per org/rep, with timestamp and version.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`org_id`	`BIGINT UNSIGNED`	`NULL, FK → organizations.id`
`rep_id`	`BIGINT UNSIGNED`	`NULL, FK → representatives.id`
`consent_type`	`VARCHAR(64)`	`NN`	e.g. `data_processing`, `nadra_lookup`, `marketing`.
`granted`	`TINYINT(1)`	`NN`	1=granted, 0=withdrawn.
`policy_version`	`VARCHAR(32)`	`NN`	Privacy policy version.
`consented_at`	`DATETIME(6)`	`NN`
`withdrawn_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

4.2 Departments

`departments`

Purpose: The government org tree. Top-level = Department; nested = Section/Sub-department.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`parent_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`, indexed	NULL = top-level Department.
`code`	`VARCHAR(8)`	`NN, UQ`	Short code e.g. `LBR`, `SITD`, `FIN`.
`name_en`	`VARCHAR(255)`	`NN`
`name_ur`	`VARCHAR(255)`	`NULL`
`name_sd`	`VARCHAR(255)`	`NULL`
`description`	`TEXT`	`NULL`
`is_owner_dept`	`TINYINT(1)`	`NN DEFAULT 0`	1 for S&ITD.
`depth`	`TINYINT UNSIGNED`	`NN DEFAULT 0`	Tree depth cache.
`path`	`VARCHAR(512)`	`NULL`	Materialized path `/1/4/9/` for subtree queries.
`status`	`ENUM('active','inactive')`	`NN DEFAULT 'active'`
`sort_order`	`INT`	`NN DEFAULT 0`
(standard audit)	—	see §2.3	`+deleted_at`.

`department_sections`

Purpose: Convenience projection / explicit metadata for leaf assignable sections under a department.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`, indexed	Parent department.
`section_dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`	The sub-node row itself.
`code`	`VARCHAR(16)`	`NULL`	Section code.
`name_en`	`VARCHAR(255)`	`NN`
`parent_section_id`	`BIGINT UNSIGNED`	`NULL, FK → department_sections.id`
`is_assignable`	`TINYINT(1)`	`NN DEFAULT 1`	Can receive tickets.
(standard audit)	—	see §2.3

`holiday_calendar`

Purpose: Sindh public holidays (and national) used to pause SLA.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`holiday_date`	`DATE`	`NN`
`name_en`	`VARCHAR(128)`	`NN`
`name_ur`	`VARCHAR(128)`	`NULL`
`name_sd`	`VARCHAR(128)`	`NULL`
`region`	`VARCHAR(64)`	`NN DEFAULT 'Sindh'`
`holiday_type`	`ENUM('public','bank','optional')`	`NN DEFAULT 'public'`
(standard audit)	—	see §2.3

`department_holidays`

Purpose: Per-department override of the shared holiday calendar.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`, indexed
`holiday_id`	`BIGINT UNSIGNED`	`NULL, FK → holiday_calendar.id`	Reference to shared calendar.
`override_date`	`DATE`	`NULL`	Department-specific date.
`observes`	`TINYINT(1)`	`NN DEFAULT 1`	1=observes, 0=explicitly skips.
(standard audit)	—	see §2.3

`business_hours`

Purpose: Working hours per department per weekday; SLA clock runs only within these windows.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`, indexed
`weekday`	`TINYINT UNSIGNED`	`NN`	0=Sun … 6=Sat.
`opens_at`	`TIME`	`NULL`
`closes_at`	`TIME`	`NULL`
`is_working_day`	`TINYINT(1)`	`NN DEFAULT 1`
`timezone`	`VARCHAR(32)`	`NN DEFAULT 'Asia/Karachi'`
(standard audit)	—	see §2.3

4.3 Users, Roles & Officials

`users`

Purpose: Unified login identity for all actors (government staff, company reps, citizens, service accounts).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`keycloak_sub`	`CHAR(36)`	`NN, UQ`	OIDC subject UUID.
`display_name`	`VARCHAR(255)`	`NN`
`email`	`VARCHAR(255)`	`NULL, UQ`	NULL for service accounts.
`mobile_e164`	`VARCHAR(16)`	`NULL`
`locale`	`CHAR(3)`	`NN DEFAULT 'en'`
`two_fa_method`	`ENUM('totp','sms','none')`	`NN DEFAULT 'totp'`
`status`	`ENUM('active','suspended','training','deactivated')`	`NN DEFAULT 'active'`
`is_certified`	`TINYINT(1)`	`NN DEFAULT 0`	Live-ticket gate (Module O).
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`, indexed	Government staff only.
`rep_id`	`BIGINT UNSIGNED`	`NULL, UQ, FK → representatives.id`	Company side only (1:1).
`is_service_account`	`TINYINT(1)`	`NN DEFAULT 0`	Integrations/jobs.
`last_login_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`roles`

Purpose: Role templates (government, company, oversight, other).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`code`	`VARCHAR(64)`	`NN, UQ`	e.g. `SUPER_ADMIN`, `OFFICER`, `PRIMARY_REP`.
`side`	`ENUM('gov','company','oversight','other')`	`NN`
`name_en`	`VARCHAR(128)`	`NN`
`name_ur`	`VARCHAR(128)`	`NULL`
`name_sd`	`VARCHAR(128)`	`NULL`
`description`	`TEXT`	`NULL`
`is_system`	`TINYINT(1)`	`NN DEFAULT 0`	Seeded, non-deletable.
(standard audit)	—	see §2.3

`role_template_permissions`

Purpose: Baseline capabilities granted by a role template (the matrix in roles doc §8).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`role_id`	`BIGINT UNSIGNED`	`NN, FK → roles.id`, indexed
`capability_key`	`VARCHAR(64)`	`NN`	e.g. `ticket.file`, `sla.override`.
`effect`	`ENUM('allow','conditional')`	`NN DEFAULT 'allow'`	`conditional` = ◐ in the matrix.
(standard audit)	—	see §2.3
Unique	`(role_id, capability_key)`	`UNIQUE`

`user_role_assignments`

Purpose: Binds a user to one or more role templates (with scope/department).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`role_id`	`BIGINT UNSIGNED`	`NN, FK → roles.id`
`scope_dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`	Department scope for DA/Officer/DG/Sec.
`assigned_at`	`DATETIME(6)`	`NN`
`revoked_at`	`DATETIME(6)`	`NULL`
`assigned_by`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
(standard audit)	—	see §2.3

`user_permission_overrides`

Purpose: Per-user granular grant/revoke on individual capabilities.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`capability_key`	`VARCHAR(64)`	`NN`
`effect`	`ENUM('grant','revoke')`	`NN`
`scope_dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`	Optional department-scoped override.
`reason`	`VARCHAR(255)`	`NULL`
(standard audit)	—	see §2.3
Unique	`(user_id, capability_key, scope_dept_id)`	`UNIQUE`

`officials`

Purpose: Brand & Officials CMS records (Minister/SACM, Secretary, DG/Director) shown on site, letters, dashboards.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`title`	`ENUM('minister','sacm','secretary','dg','director')`	`NN`
`full_name_en`	`VARCHAR(255)`	`NN`
`full_name_ur`	`VARCHAR(255)`	`NULL`
`full_name_sd`	`VARCHAR(255)`	`NULL`
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`	Affiliated department.
`portrait_media_id`	`BIGINT UNSIGNED`	`NULL, FK → media_library.id`
`message_en`	`LONGTEXT`	`NULL`	Public message.
`message_ur`	`LONGTEXT`	`NULL`
`message_sd`	`LONGTEXT`	`NULL`
`is_current`	`TINYINT(1)`	`NN DEFAULT 1`	Convenience flag.
(standard audit)	—	see §2.3	`+deleted_at`.

`official_terms`

Purpose: Date-scoped tenure for historical accuracy (letters render the correct official for the issue date).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`official_id`	`BIGINT UNSIGNED`	`NN, FK → officials.id`, indexed
`designation`	`VARCHAR(128)`	`NN`	e.g. "Secretary S&ITD".
`effective_from`	`DATE`	`NN`
`effective_to`	`DATE`	`NULL`	NULL = open-ended / incumbent.
`metadata_json`	`LONGTEXT`	`NULL`	Additional facts (notification ref).
(standard audit)	—	see §2.3

`media_library`

Purpose: Shared asset registry (MinIO blobs: portraits, KB images, attachments, generated docs, exports).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`bucket`	`VARCHAR(64)`	`NN`	`uploads`/`generated-docs`/`moms`/`avatars`/`exports`.
`object_key`	`VARCHAR(255)`	`NN`	MinIO key.
`mime_type`	`VARCHAR(128)`	`NN`
`size_bytes`	`BIGINT UNSIGNED`	`NN`
`checksum_sha256`	`CHAR(64)`	`NULL`	Dedup / integrity.
`av_status`	`ENUM('pending','clean','infected','error')`	`NN DEFAULT 'pending'`	ClamAV scan.
`is_encrypted`	`TINYINT(1)`	`NN DEFAULT 1`	At-rest encryption flag.
`extracted_text`	`LONGTEXT`	`NULL`	OCR output (for indexing).
`owner_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Uploader.
`expires_at`	`DATETIME(6)`	`NULL`	Retention-driven purge.
(standard audit)	—	see §2.3	`+deleted_at`.

4.4 Tickets & Workflow

`tickets`

Purpose: The central artifact; full lifecycle with SLA, escalation, confidentiality, and resolution gate.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`	Surrogate join key.
`tracking_id`	`VARCHAR(32)`	`NN, UQ`	`SITP-YYYY-DEPT-NNNNNN`.
`title`	`VARCHAR(255)`	`NN`
`description`	`LONGTEXT`	`NN`
`status`	`ENUM('new','triaged','assigned','in_progress','resolved','closed','reopened','appealed','withdrawn')`	`NN DEFAULT 'new'`
`priority`	`ENUM('low','normal','high','urgent','vip')`	`NN DEFAULT 'normal'`
`category_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_categories.id`, indexed
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`, indexed
`section_id`	`BIGINT UNSIGNED`	`NULL, FK → department_sections.id`, indexed
`assigned_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Resolving officer.
`org_id`	`BIGINT UNSIGNED`	`NN, FK → organizations.id`, indexed	Filing company.
`filer_rep_id`	`BIGINT UNSIGNED`	`NN, FK → representatives.id`	Filing rep.
`filer_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Login user who filed.
`locale`	`CHAR(3)`	`NN DEFAULT 'en'`
`is_confidential`	`TINYINT(1)`	`NN DEFAULT 0`	ABAC gate.
`is_vip`	`TINYINT(1)`	`NN DEFAULT 0`	VIP closure gate.
`is_anonymous`	`TINYINT(1)`	`NN DEFAULT 0`	Whistleblower channel.
`is_rti`	`TINYINT(1)`	`NN DEFAULT 0`	RTI statutory deadline.
`sla_due_at`	`DATETIME(6)`	`NULL`, indexed	Effective deadline.
`sla_paused_until`	`DATETIME(6)`	`NULL`	Active pause end.
`first_response_at`	`DATETIME(6)`	`NULL`
`resolved_at`	`DATETIME(6)`	`NULL`
`closed_at`	`DATETIME(6)`	`NULL`
`resolution_note`	`TEXT`	`NULL`	Mandatory at resolve.
`merged_into_ticket_id`	`BIGINT UNSIGNED`	`NULL, FK → tickets.id`	If merged.
(standard audit)	—	see §2.3	`+deleted_at` (rare; legal hold).

`ticket_categories`

Purpose: Classification taxonomy (per department, nestable).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`parent_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_categories.id`	Tree.
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`, indexed
`code`	`VARCHAR(32)`	`NN`
`name_en`	`VARCHAR(255)`	`NN`
`name_ur`	`VARCHAR(255)`	`NULL`
`name_sd`	`VARCHAR(255)`	`NULL`
`default_sla_definition_id`	`BIGINT UNSIGNED`	`NULL, FK → sla_definitions.id`
`is_rti_category`	`TINYINT(1)`	`NN DEFAULT 0`
`sort_order`	`INT`	`NN DEFAULT 0`
(standard audit)	—	see §2.3

`ticket_threads`

Purpose: A conversation container on a ticket (public vs internal).

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`visibility`	`ENUM('public','internal')`	`NN`
(standard audit)	—	see §2.3
Unique	`(ticket_id, visibility)`	`UNIQUE`

`ticket_messages`

Purpose: Individual messages in a thread (web, email, SMS, WhatsApp, IVR origins).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`thread_id`	`BIGINT UNSIGNED`	`NN, FK → ticket_threads.id`, indexed
`author_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	NULL for inbound/system.
`author_display`	`VARCHAR(255)`	`NULL`	For external/anonymous.
`body`	`LONGTEXT`	`NN`	Rendered markdown/HTML sanitized.
`body_plain`	`LONGTEXT`	`NULL`	Stripped for SMS/search.
`source`	`ENUM('web','email','sms','whatsapp','ivr','system','api')`	`NN DEFAULT 'web'`
`source_ref`	`VARCHAR(128)`	`NULL`	External message id.
`is_internal_note`	`TINYINT(1)`	`NN DEFAULT 0`
`is_redacted`	`TINYINT(1)`	`NN DEFAULT 0`	PII redaction applied.
`sent_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

`ticket_attachments`

Purpose: Files attached to a ticket message (or directly to the ticket).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`message_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_messages.id`	If attached to a message.
`media_id`	`BIGINT UNSIGNED`	`NN, FK → media_library.id`	The blob.
`display_name`	`VARCHAR(255)`	`NN`
`uploaded_by_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`is_evidence`	`TINYINT(1)`	`NN DEFAULT 0`	Counts toward proof gate.
`visibility`	`ENUM('public','internal')`	`NN DEFAULT 'public'`
(standard audit)	—	see §2.3

`ticket_watchers`

Purpose: CC / escalation-added audience on a ticket.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`added_by_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`reason`	`ENUM('cc','escalation','triage','break_glass','manual')`	`NN DEFAULT 'manual'`
`added_at`	`DATETIME(6)`	`NN`
`removed_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3
Unique	`(ticket_id, user_id)`	`UNIQUE`

`ticket_subtasks`

Purpose: A child ticket derived by splitting a parent (or from a confirmed MoM action item).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`parent_ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`child_ticket_id`	`BIGINT UNSIGNED`	`NN, UQ, FK → tickets.id`	The sub-ticket.
`extracted_action_item_id`	`BIGINT UNSIGNED`	`NULL, FK → extracted_action_items.id`	If from MoM.
`title`	`VARCHAR(255)`	`NN`
`due_date`	`DATE`	`NULL`
`status`	`ENUM('open','in_progress','done','cancelled')`	`NN DEFAULT 'open'`
(standard audit)	—	see §2.3

`ticket_links`

Purpose: Typed relationships between two tickets (merge, split, relate, duplicate, blocks).

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`source_ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`target_ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`link_type`	`ENUM('merge','split','relate','duplicate','blocks')`	`NN`
`note`	`VARCHAR(255)`	`NULL`
(standard audit)	—	see §2.3
Unique	`(source_ticket_id, target_ticket_id, link_type)`	`UNIQUE`

`ticket_history`

Purpose: Append-only per-ticket event log (status, assignment, SLA, priority changes).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`actor_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	NULL for system.
`event_type`	`VARCHAR(64)`	`NN`	e.g. `status.changed`, `sla.paused`.
`from_value`	`VARCHAR(255)`	`NULL`
`to_value`	`VARCHAR(255)`	`NULL`
`metadata_json`	`LONGTEXT`	`NULL`
`occurred_at`	`DATETIME(6)`	`NN`

4.5 SLA, Escalation & Resolution

`sla_definitions`

Purpose: Configurable SLA per (department, category, priority).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`, indexed
`category_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_categories.id`	NULL = dept-wide default.
`priority`	`ENUM('low','normal','high','urgent','vip')`	`NN`
`first_response_hours`	`INT UNSIGNED`	`NN`	Working-hour clock.
`resolution_hours`	`INT UNSIGNED`	`NN`	Working-hour clock.
`pauses_on_await`	`TINYINT(1)`	`NN DEFAULT 1`	Pause when awaiting company.
`pauses_on_weekend`	`TINYINT(1)`	`NN DEFAULT 1`
`pauses_on_holiday`	`TINYINT(1)`	`NN DEFAULT 1`	Sindh calendar.
`is_active`	`TINYINT(1)`	`NN DEFAULT 1`
(standard audit)	—	see §2.3
Unique	`(dept_id, category_id, priority)`	`UNIQUE`

`escalation_rules`

Purpose: The 2/5/10-day tier ladder per department, target role, and mode.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`, indexed
`tier`	`TINYINT UNSIGNED`	`NN`	1=DG(2d), 2=Sec(7d), 3=Min(17d).
`days_after_open`	`INT UNSIGNED`	`NN`	Cumulative days.
`target_role_id`	`BIGINT UNSIGNED`	`NN, FK → roles.id`	Oversight role.
`mode`	`ENUM('notify','notify_plus_action')`	`NN DEFAULT 'notify'`	Mirrors oversight powers.
`is_active`	`TINYINT(1)`	`NN DEFAULT 1`
(standard audit)	—	see §2.3

`escalation_events`

Purpose: Record of each escalation tier firing on a ticket.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`rule_id`	`BIGINT UNSIGNED`	`NN, FK → escalation_rules.id`
`target_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Resolved target.
`fired_at`	`DATETIME(6)`	`NN`
`outcome`	`VARCHAR(64)`	`NULL`	e.g. `notified`, `watcher_added`.
(standard audit)	—	see §2.3

`sla_pause_events`

Purpose: Every SLA clock pause/resume, reconstructing effective SLA forensically.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`reason`	`ENUM('await_company','weekend','holiday','manual','tri_meeting')`	`NN`
`paused_at`	`DATETIME(6)`	`NN`
`resumed_at`	`DATETIME(6)`	`NULL`	NULL = still paused.
`paused_seconds`	`INT UNSIGNED`	`NULL`	Computed on resume.
`note`	`VARCHAR(255)`	`NULL`
`actor_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`

`resolution_evidence`

Purpose: The proof-of-resolution gate — at least one row required to move to resolved.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`attachment_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_attachments.id`	The evidence file.
`note`	`TEXT`	`NN`	Resolution note.
`submitted_by_user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`submitted_at`	`DATETIME(6)`	`NN`
`approval_status`	`ENUM('pending','approved','rejected','auto')`	`NN DEFAULT 'auto'`	`approved` required for sensitive/VIP.
`approved_by_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Chair/DG for VIP.
`approved_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

`appeals`

Purpose: Company or citizen appeal against a resolution or rejection.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed
`appealed_by_user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`reason`	`TEXT`	`NN`
`tier`	`TINYINT UNSIGNED`	`NN DEFAULT 1`	Appeal tier (CPGRAMS-style).
`status`	`ENUM('filed','under_review','upheld','rejected','escalated')`	`NN DEFAULT 'filed'`
`reviewer_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`decision_note`	`TEXT`	`NULL`
`decided_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

`csat_responses`

Purpose: Customer satisfaction rating after resolution.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NN, UQ, FK → tickets.id`	One per ticket.
`submitted_by_user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`rating`	`TINYINT UNSIGNED`	`NN`	1..5.
`comment`	`TEXT`	`NULL`
`submitted_at`	`DATETIME(6)`	`NN`

4.6 AI

`ai_engine_configs`

Purpose: Pluggable engine registry for the 11 AI capabilities + transcription.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`feature`	`VARCHAR(48)`	`NN`	`ocr`,`summary`,`routing`,`urgency`,`draft_reply`,`translation`,`duplicate`,`chatbot`,`redaction`,`trends`,`mom_extract`,`transcribe`.
`provider`	`ENUM('azure','google','aws','ollama_vllm','tesseract','whisper')`	`NN`
`model`	`VARCHAR(64)`	`NULL`	e.g. `gpt-4o`, `llama3-70b`.
`api_endpoint`	`VARCHAR(255)`	`NULL`
`is_cloud`	`TINYINT(1)`	`NN`	Cloud vs on-prem.
`sensitivity_class`	`ENUM('public','internal','confidential','restricted')`	`NN DEFAULT 'internal'`	Max data class it may serve.
`priority`	`TINYINT UNSIGNED`	`NN DEFAULT 100`	Lower = preferred.
`enabled`	`TINYINT(1)`	`NN DEFAULT 1`
`env`	`ENUM('dev','staging','prod')`	`NN DEFAULT 'prod'`
`config_json`	`LONGTEXT`	`NULL`	Extra params (temperature, etc.).
(standard audit)	—	see §2.3

`ai_runs`

Purpose: Audit of every AI invocation (engine, tokens, cost, latency, redaction).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`engine_config_id`	`BIGINT UNSIGNED`	`NN, FK → ai_engine_configs.id`, indexed
`feature`	`VARCHAR(48)`	`NN`	Denormalized for query.
`input_ref_type`	`VARCHAR(32)`	`NULL`	`ticket`,`mom`,`message`,`kb`, etc.
`input_ref_id`	`BIGINT UNSIGNED`	`NULL`
`input_summary`	`LONGTEXT`	`NULL`	Redacted snapshot.
`output_json`	`LONGTEXT`	`NULL`	Structured result.
`prompt_tokens`	`INT UNSIGNED`	`NULL`
`completion_tokens`	`INT UNSIGNED`	`NULL`
`cost_usd`	`DECIMAL(12,4)`	`NULL`	In USD micro-cost.
`latency_ms`	`INT UNSIGNED`	`NULL`
`status`	`ENUM('ok','fallback','failed','redacted')`	`NN`
`was_redacted`	`TINYINT(1)`	`NN DEFAULT 0`
`error_message`	`TEXT`	`NULL`
`started_at`	`DATETIME(6)`	`NN`
`finished_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

`extracted_action_items`

Purpose: Structured output of MoM action-item extraction; confirmed into sub-tasks.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ai_run_id`	`BIGINT UNSIGNED`	`NN, FK → ai_runs.id`, indexed
`mom_id`	`BIGINT UNSIGNED`	`NULL, FK → mom.id`
`action_text`	`TEXT`	`NN`
`owner_text`	`VARCHAR(255)`	`NULL`	As-extracted owner.
`owner_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Resolved officer.
`due_date`	`DATE`	`NULL`
`priority`	`ENUM('low','normal','high','urgent')`	`NULL`
`status`	`ENUM('proposed','confirmed','converted','rejected')`	`NN DEFAULT 'proposed'`
`subtask_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_subtasks.id`	Set when converted.
`confirmed_by_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`confirmed_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

4.7 Comms

`channels`

Purpose: DM/group/channel containers for the 3-tier internal comms.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`code`	`VARCHAR(64)`	`NULL`
`type`	`ENUM('dm','group','channel')`	`NN`
`name_en`	`VARCHAR(255)`	`NULL`
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`	Scoped channel.
`is_private`	`TINYINT(1)`	`NN DEFAULT 0`
`last_message_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`channel_memberships`

Purpose: Membership of users in channels (with role: member/admin/owner).

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`channel_id`	`BIGINT UNSIGNED`	`NN, FK → channels.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`role`	`ENUM('member','admin','owner')`	`NN DEFAULT 'member'`
`joined_at`	`DATETIME(6)`	`NN`
`left_at`	`DATETIME(6)`	`NULL`
`muted_until`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3
Unique	`(channel_id, user_id)`	`UNIQUE`

`channel_messages`

Purpose: Messages in a channel (incl. threaded replies via parent_message_id).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`channel_id`	`BIGINT UNSIGNED`	`NN, FK → channels.id`, indexed
`author_user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`parent_message_id`	`BIGINT UNSIGNED`	`NULL, FK → channel_messages.id`	Thread root.
`body`	`LONGTEXT`	`NN`
`is_pinned`	`TINYINT(1)`	`NN DEFAULT 0`
`edited_at`	`DATETIME(6)`	`NULL`
`sent_at`	`DATETIME(6)`	`NN`
(standard audit)	—	see §2.3	`+deleted_at`.

`messages`

Purpose: DM and group-DM inbox messages (org-wide, simplified path).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`from_user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`to_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	DM recipient.
`channel_id`	`BIGINT UNSIGNED`	`NULL, FK → channels.id`	Group/DM channel.
`body`	`LONGTEXT`	`NN`
`sent_at`	`DATETIME(6)`	`NN`
`read_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`threaded_replies`

Purpose: Explicit thread reply metadata on channel messages (in addition to self-ref).

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`root_message_id`	`BIGINT UNSIGNED`	`NN, FK → channel_messages.id`, indexed
`reply_message_id`	`BIGINT UNSIGNED`	`NN, UQ, FK → channel_messages.id`

`message_reads`

Purpose: Read receipts per user per channel message.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`channel_message_id`	`BIGINT UNSIGNED`	`NN, FK → channel_messages.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`read_at`	`DATETIME(6)`	`NN`
Unique	`(channel_message_id, user_id)`	`UNIQUE`

4.8 Notifications

`notifications`

Purpose: Outbound notification per user/channel with delivery status.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`template_id`	`BIGINT UNSIGNED`	`NULL, FK → notification_templates.id`
`channel`	`ENUM('email','sms','whatsapp','in_app')`	`NN`
`event_key`	`VARCHAR(64)`	`NN`, indexed	e.g. `ticket.escalated.tier2`.
`entity_type`	`VARCHAR(32)`	`NULL`
`entity_id`	`BIGINT UNSIGNED`	`NULL`
`payload_json`	`LONGTEXT`	`NULL`	Render variables.
`locale`	`CHAR(3)`	`NN DEFAULT 'en'`
`subject`	`VARCHAR(255)`	`NULL`	Rendered.
`body`	`LONGTEXT`	`NULL`	Rendered.
`status`	`ENUM('queued','sent','delivered','failed','suppressed')`	`NN DEFAULT 'queued'`
`provider_message_id`	`VARCHAR(128)`	`NULL`
`sent_at`	`DATETIME(6)`	`NULL`
`delivered_at`	`DATETIME(6)`	`NULL`
`error_message`	`TEXT`	`NULL`
(standard audit)	—	see §2.3

`notification_templates`

Purpose: Multilingual templates keyed by event + locale + channel.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`event_key`	`VARCHAR(64)`	`NN`, indexed
`locale`	`CHAR(3)`	`NN`
`channel`	`ENUM('email','sms','whatsapp','in_app')`	`NN`
`subject`	`VARCHAR(255)`	`NULL`	Not for SMS.
`body`	`LONGTEXT`	`NN`	Handlebars/Mustache.
`is_active`	`TINYINT(1)`	`NN DEFAULT 1`
(standard audit)	—	see §2.3
Unique	`(event_key, locale, channel)`	`UNIQUE`

`notification_preferences`

Purpose: Per-user preference center (channel opt-ins, digests, quiet hours).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`user_id`	`BIGINT UNSIGNED`	`NN, UQ, FK → users.id`
`email_enabled`	`TINYINT(1)`	`NN DEFAULT 1`
`sms_enabled`	`TINYINT(1)`	`NN DEFAULT 1`
`whatsapp_enabled`	`TINYINT(1)`	`NN DEFAULT 1`
`in_app_enabled`	`TINYINT(1)`	`NN DEFAULT 1`
`digest_frequency`	`ENUM('immediate','hourly','daily','weekly','off')`	`NN DEFAULT 'immediate'`
`quiet_hours_start`	`TIME`	`NULL`	Local time.
`quiet_hours_end`	`TIME`	`NULL`
`muted_event_keys_json`	`LONGTEXT`	`NULL`	Array of muted events.
(standard audit)	—	see §2.3

`inbound_replies`

Purpose: Two-way inbound replies (email/WA/SMS) parsed back into a ticket.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`source`	`ENUM('email','whatsapp','sms')`	`NN`
`from_address`	`VARCHAR(255)`	`NN`	Sender.
`external_message_id`	`VARCHAR(128)`	`NULL`	Provider id.
`ticket_id`	`BIGINT UNSIGNED`	`NN, FK → tickets.id`, indexed	Resolved target.
`subject`	`VARCHAR(255)`	`NULL`
`body`	`LONGTEXT`	`NN`
`created_message_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_messages.id`	Resulting message.
`received_at`	`DATETIME(6)`	`NN`
(standard audit)	—	see §2.3

4.9 Meetings, TRI & MoM

`meetings`

Purpose: TRI / hearing / internal meetings triggered from a ticket, virtual/physical/hybrid.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`ticket_id`	`BIGINT UNSIGNED`	`NULL, FK → tickets.id`, indexed	Trigger.
`type`	`ENUM('tri','hearing','internal')`	`NN`
`modality`	`ENUM('virtual','physical','hybrid')`	`NN`
`video_provider`	`ENUM('zoom','meet','teams')`	`NULL`
`join_url`	`VARCHAR(512)`	`NULL`	Time-limited presigned.
`location_address`	`VARCHAR(255)`	`NULL`	For physical.
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`	Concerned department.
`scheduled_at`	`DATETIME(6)`	`NN`
`started_at`	`DATETIME(6)`	`NULL`
`ended_at`	`DATETIME(6)`	`NULL`
`status`	`ENUM('scheduled','in_progress','completed','cancelled')`	`NN DEFAULT 'scheduled'`
`agenda_json`	`LONGTEXT`	`NULL`	AI-drafted agenda.
(standard audit)	—	see §2.3	`+deleted_at`.

`meeting_attendees`

Purpose: Attendees per meeting (internal users + external parties).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`meeting_id`	`BIGINT UNSIGNED`	`NN, FK → meetings.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	Internal.
`name`	`VARCHAR(255)`	`NULL`	External attendee.
`party`	`ENUM('company','sitd','department','external')`	`NN`
`role`	`VARCHAR(64)`	`NULL`	e.g. "Chair".
`attendance_status`	`ENUM('invited','accepted','declined','attended','absent')`	`NN DEFAULT 'invited'`
(standard audit)	—	see §2.3

`mom`

Purpose: Minutes of Meeting, versioned, with sensitive-approval gating.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`meeting_id`	`BIGINT UNSIGNED`	`NN, UQ, FK → meetings.id`	1:1.
`version`	`INT UNSIGNED`	`NN DEFAULT 1`
`status`	`ENUM('draft','approved','published','revised')`	`NN DEFAULT 'draft'`
`sensitive_requires_approval`	`TINYINT(1)`	`NN DEFAULT 0`	Forces approval.
`source_media_id`	`BIGINT UNSIGNED`	`NULL, FK → media_library.id`	Uploaded MoM file.
`body_en`	`LONGTEXT`	`NULL`
`body_ur`	`LONGTEXT`	`NULL`
`body_sd`	`LONGTEXT`	`NULL`
`summary_ai`	`TEXT`	`NULL`	AI summary.
`uploaded_by_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`published_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`mom_approvals`

Purpose: Chair/DG approvals required for sensitive/VIP MoM before publish.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`mom_id`	`BIGINT UNSIGNED`	`NN, FK → mom.id`, indexed
`approver_user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`decision`	`ENUM('approved','rejected','changes_requested')`	`NN`
`note`	`TEXT`	`NULL`
`decided_at`	`DATETIME(6)`	`NN`

`mom_distributions`

Purpose: Record of each channel share on MoM publish.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`mom_id`	`BIGINT UNSIGNED`	`NN, FK → mom.id`, indexed
`recipient_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`recipient_address`	`VARCHAR(255)`	`NULL`	External contact.
`channel`	`ENUM('email','sms','whatsapp','in_app')`	`NN`
`notification_id`	`BIGINT UNSIGNED`	`NULL, FK → notifications.id`
`sent_at`	`DATETIME(6)`	`NN`

`mom_acknowledgments`

Purpose: Track who acknowledged the published MoM.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`mom_id`	`BIGINT UNSIGNED`	`NN, FK → mom.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`
`acknowledged_at`	`DATETIME(6)`	`NN`
Unique	`(mom_id, user_id)`	`UNIQUE`

`meeting_recordings`

Purpose: Recording blobs per meeting, with transcription flag.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`meeting_id`	`BIGINT UNSIGNED`	`NN, FK → meetings.id`, indexed
`media_id`	`BIGINT UNSIGNED`	`NN, FK → media_library.id`
`duration_seconds`	`INT UNSIGNED`	`NULL`
`transcribed`	`TINYINT(1)`	`NN DEFAULT 0`
`transcript_media_id`	`BIGINT UNSIGNED`	`NULL, FK → media_library.id`
(standard audit)	—	see §2.3

4.10 Knowledge & Content

`kb_articles`

Purpose: Versioned, trilingual knowledge-base articles.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`slug`	`VARCHAR(255)`	`NN`
`version`	`INT UNSIGNED`	`NN DEFAULT 1`
`status`	`ENUM('draft','review','published','archived')`	`NN DEFAULT 'draft'`
`title_en`	`VARCHAR(255)`	`NN`
`title_ur`	`VARCHAR(255)`	`NULL`
`title_sd`	`VARCHAR(255)`	`NULL`
`body_en`	`LONGTEXT`	`NULL`
`body_ur`	`LONGTEXT`	`NULL`
`body_sd`	`LONGTEXT`	`NULL`
`summary`	`TEXT`	`NULL`
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`
`category`	`VARCHAR(64)`	`NULL`
`is_featured`	`TINYINT(1)`	`NN DEFAULT 0`
`helpful_count`	`INT`	`NN DEFAULT 0`	"Was this helpful".
`not_helpful_count`	`INT`	`NN DEFAULT 0`
`published_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`sop_documents`

Purpose: Versioned Standard Operating Procedure documents per department.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`
`title_en`	`VARCHAR(255)`	`NN`
`version`	`VARCHAR(32)`	`NN`	e.g. "2.1".
`status`	`ENUM('draft','review','published','archived')`	`NN DEFAULT 'draft'`
`doc_id`	`BIGINT UNSIGNED`	`NULL, FK → documents.id`	The file.
`effective_from`	`DATE`	`NULL`
`effective_to`	`DATE`	`NULL`
(standard audit)	—	see §2.3

`service_catalog_entries`

Purpose: Catalog of services offered by each department (with default SLA).

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dept_id`	`BIGINT UNSIGNED`	`NN, FK → departments.id`
`code`	`VARCHAR(32)`	`NN`
`name_en`	`VARCHAR(255)`	`NN`
`name_ur`	`VARCHAR(255)`	`NULL`
`name_sd`	`VARCHAR(255)`	`NULL`
`description`	`TEXT`	`NULL`
`default_sla_definition_id`	`BIGINT UNSIGNED`	`NULL, FK → sla_definitions.id`
`default_category_id`	`BIGINT UNSIGNED`	`NULL, FK → ticket_categories.id`
`form_doc_id`	`BIGINT UNSIGNED`	`NULL, FK → documents.id`
`is_active`	`TINYINT(1)`	`NN DEFAULT 1`
(standard audit)	—	see §2.3

`circulars`

Purpose: Announcements / circulars published to the suggestion & content portal.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`title_en`	`VARCHAR(255)`	`NN`
`title_ur`	`VARCHAR(255)`	`NULL`
`title_sd`	`VARCHAR(255)`	`NULL`
`body_en`	`LONGTEXT`	`NULL`
`body_ur`	`LONGTEXT`	`NULL`
`body_sd`	`LONGTEXT`	`NULL`
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`
`is_pinned`	`TINYINT(1)`	`NN DEFAULT 0`
`published_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`documents`

Purpose: General document repository (forms, SOPs, circulars, repository items, official letters).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`kind`	`ENUM('form','sop','circular','repository','letter','other')`	`NN`
`title`	`VARCHAR(255)`	`NN`
`media_id`	`BIGINT UNSIGNED`	`NN, FK → media_library.id`
`version`	`VARCHAR(32)`	`NULL`
`checksum_sha256`	`CHAR(64)`	`NULL`
`is_public`	`TINYINT(1)`	`NN DEFAULT 0`
`description`	`TEXT`	`NULL`
(standard audit)	—	see §2.3	`+deleted_at`.

`suggestion_submissions`

Purpose: Public suggestion box submissions.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`submitter_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	NULL if anonymous.
`submitter_name`	`VARCHAR(255)`	`NULL`
`submitter_email`	`VARCHAR(255)`	`NULL`
`subject`	`VARCHAR(255)`	`NN`
`body`	`LONGTEXT`	`NN`
`category`	`VARCHAR(64)`	`NULL`
`status`	`ENUM('submitted','under_review','accepted','rejected','implemented')`	`NN DEFAULT 'submitted'`
`is_public`	`TINYINT(1)`	`NN DEFAULT 0`
(standard audit)	—	see §2.3

4.11 Training & Certification

`training_courses`

Purpose: LMS-lite course catalog for government staff.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`code`	`VARCHAR(32)`	`NN, UQ`
`title_en`	`VARCHAR(255)`	`NN`
`title_ur`	`VARCHAR(255)`	`NULL`
`title_sd`	`VARCHAR(255)`	`NULL`
`description`	`TEXT`	`NULL`
`duration_minutes`	`INT UNSIGNED`	`NN`
`target_roles_json`	`LONGTEXT`	`NULL`	Array of role codes.
`is_required`	`TINYINT(1)`	`NN DEFAULT 0`	Certification gate.
(standard audit)	—	see §2.3

`course_enrollments`

Purpose: User enrollment in a course with progress.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`course_id`	`BIGINT UNSIGNED`	`NN, FK → training_courses.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`progress_pct`	`TINYINT UNSIGNED`	`NN DEFAULT 0`
`status`	`ENUM('enrolled','in_progress','completed','dropped')`	`NN DEFAULT 'enrolled'`
`completed_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3
Unique	`(course_id, user_id)`	`UNIQUE`

`training_exams`

Purpose: Exam definition tied to a course (certification gate).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`course_id`	`BIGINT UNSIGNED`	`NN, FK → training_courses.id`
`title_en`	`VARCHAR(255)`	`NN`
`pass_threshold_pct`	`TINYINT UNSIGNED`	`NN DEFAULT 70`
`time_limit_minutes`	`INT UNSIGNED`	`NULL`
`max_attempts`	`TINYINT UNSIGNED`	`NULL`
`questions_json`	`LONGTEXT`	`NULL`	Question bank.
(standard audit)	—	see §2.3

`exam_attempts`

Purpose: A user's attempt at an exam, with score and result.

Column	Type	Constraints
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`exam_id`	`BIGINT UNSIGNED`	`NN, FK → training_exams.id`, indexed
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`score_pct`	`TINYINT UNSIGNED`	`NULL`
`result`	`ENUM('pass','fail','incomplete')`	`NN DEFAULT 'incomplete'`
`answers_json`	`LONGTEXT`	`NULL`
`started_at`	`DATETIME(6)`	`NN`
`finished_at`	`DATETIME(6)`	`NULL`

`certifications`

Purpose: Issued (and expiring) certifications unlocking live-ticket access.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`user_id`	`BIGINT UNSIGNED`	`NN, FK → users.id`, indexed
`course_id`	`BIGINT UNSIGNED`	`NN, FK → training_courses.id`
`attempt_id`	`BIGINT UNSIGNED`	`NN, FK → exam_attempts.id`	Passing attempt.
`certificate_code`	`VARCHAR(64)`	`NN, UQ`	Public code.
`issued_on`	`DATE`	`NN`
`expires_on`	`DATE`	`NULL`	NULL = no expiry.
`is_valid`	`TINYINT(1)`	`NN DEFAULT 1`
(standard audit)	—	see §2.3

4.12 System & Configuration

`feature_flags`

Purpose: Runtime capability toggles, scoped by platform/dept/env/user-segment.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`flag_key`	`VARCHAR(128)`	`NN, UQ`	e.g. `ai.enabled`, `mom.transcription`.
`scope`	`ENUM('platform','dept','env','user_segment')`	`NN DEFAULT 'platform'`
`dept_id`	`BIGINT UNSIGNED`	`NULL, FK → departments.id`	When scope = dept.
`env`	`ENUM('dev','staging','prod')`	`NULL`	When scope = env.
`enabled`	`TINYINT(1)`	`NN DEFAULT 0`	Effective value at this scope.
`default_value`	`TINYINT(1)`	`NN DEFAULT 0`	Platform baseline.
`description`	`VARCHAR(255)`	`NULL`
(standard audit)	—	see §2.3

`audit_logs`

Purpose: Append-only global audit of every state-changing privileged action.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`actor_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`	NULL = system.
`action`	`VARCHAR(64)`	`NN`, indexed	e.g. `role.override.created`.
`entity_type`	`VARCHAR(32)`	`NN`
`entity_id`	`BIGINT UNSIGNED`	`NULL`
`before_json`	`LONGTEXT`	`NULL`
`after_json`	`LONGTEXT`	`NULL`
`ip_address`	`VARCHAR(45)`	`NULL`	IPv4/IPv6.
`user_agent`	`VARCHAR(255)`	`NULL`
`request_id`	`CHAR(36)`	`NULL`	Correlation.
`step_up_auth`	`TINYINT(1)`	`NN DEFAULT 0`	Re-auth at action time.
`occurred_at`	`DATETIME(6)`	`NN`

Never UPDATE or DELETE. Partitioned by month (see §7).

`integrations_configs`

Purpose: Per-provider integration settings; secrets only as vault references.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`provider`	`ENUM('nadra','secp','fbr','srb','pseb','eoffice','oidc','mailjet','sms','whatsapp','video')`	`NN`
`base_url`	`VARCHAR(255)`	`NULL`
`api_version`	`VARCHAR(32)`	`NULL`
`vault_ref`	`VARCHAR(255)`	`NN`	Path in secrets vault — never the secret.
`timeout_ms`	`INT UNSIGNED`	`NN DEFAULT 30000`
`retry_max`	`TINYINT UNSIGNED`	`NN DEFAULT 3`
`enabled`	`TINYINT(1)`	`NN DEFAULT 0`
`env`	`ENUM('dev','staging','prod')`	`NN DEFAULT 'prod'`
`config_json`	`LONGTEXT`	`NULL`	Non-secret params.
(standard audit)	—	see §2.3
Unique	`(provider, env)`	`UNIQUE`

`webhooks`

Purpose: Outbound webhook subscriptions (HMAC-signed).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`event_key`	`VARCHAR(64)`	`NN`, indexed
`target_url`	`VARCHAR(512)`	`NN`
`secret_hash`	`CHAR(64)`	`NN`	sha256 of HMAC secret.
`content_type`	`VARCHAR(64)`	`NN DEFAULT 'application/json'`
`enabled`	`TINYINT(1)`	`NN DEFAULT 1`
`last_status`	`VARCHAR(16)`	`NULL`
`last_fired_at`	`DATETIME(6)`	`NULL`
(standard audit)	—	see §2.3

`open_data_exports`

Purpose: Transparency-dashboard and open-data export artifacts.

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`dataset`	`VARCHAR(64)`	`NN`	e.g. `tickets_public`,`moms_public`,`officials`,`sla_perf`.
`format`	`ENUM('csv','xlsx','json','pdf')`	`NN`
`period_from`	`DATE`	`NULL`
`period_to`	`DATE`	`NULL`
`media_id`	`BIGINT UNSIGNED`	`NN, FK → media_library.id`	The export blob.
`generated_at`	`DATETIME(6)`	`NN`
`row_count`	`INT UNSIGNED`	`NULL`
`hash_sha256`	`CHAR(64)`	`NULL`
(standard audit)	—	see §2.3

`qr_verifiable_documents`

Purpose: QR-token binding for official letters (anti-forgery public verification).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`document_id`	`BIGINT UNSIGNED`	`NN, FK → documents.id`, indexed
`qr_token`	`CHAR(36)`	`NN, UQ`	Public verify token.
`payload_hash`	`CHAR(64)`	`NN`	sha256 of letter content.
`issued_by_user_id`	`BIGINT UNSIGNED`	`NULL, FK → users.id`
`issued_at`	`DATETIME(6)`	`NN`
`expires_at`	`DATETIME(6)`	`NULL`
`revoked`	`TINYINT(1)`	`NN DEFAULT 0`
`revoked_at`	`DATETIME(6)`	`NULL`

`system_settings`

Purpose: General key-value configuration (SMTP, Mailjet, SMS, WhatsApp, branding).

Column	Type	Constraints	Notes
`id`	`BIGINT UNSIGNED`	`PK, NN, AUTO_INCREMENT`
`setting_key`	`VARCHAR(128)`	`NN, UQ`
`value`	`LONGTEXT`	`NULL`
`category`	`ENUM('smtp','mailjet','sms','whatsapp','general','branding','security')`	`NN`
`is_secret`	`TINYINT(1)`	`NN DEFAULT 0`	1 ⇒ value lives in vault only.
`description`	`VARCHAR(255)`	`NULL`
(standard audit)	—	see §2.3

5. Key Relationships

The most important relationships, in plain language:

Ticket ↔ Company / Department / Section / Staff. A tickets row binds the filer (org_id + filer_rep_id) to a resolver path (dept_id → section_id → assigned_user_id). Every queue in the product — "my work", "department queue", "company workspace", "escalation inbox" — is a filtered projection of these four FKs plus status. The tracking_id is the user-facing identifier; the surrogate id is the only join key used internally so merge/split/renumber stay possible.
Escalation chain. escalation_rules defines a per-department tier ladder; a scheduled BullMQ worker scans tickets WHERE status NOT IN (resolved,closed,withdrawn) AND sla_due_at < NOW() and, per escalation_rules.days_after_open, writes escalation_events, adds the target user as a ticket_watchers row, and dispatches notifications. sla_pause_events plus holiday_calendar / business_hours adjust the effective clock so the scan uses working deadlines, not wall-clock.
Official-term date-awareness for letters. When the system renders an official letter, it does not read officials directly; it joins official_terms WHERE effective_from <= :issue_date AND (effective_to IS NULL OR effective_to >= :issue_date) so a letter dated 2024 shows the 2024 Secretary even after a 2025 transfer. This is the historical accuracy requirement from /specs/en/04-roles-permissions/ §3 and _context.md §5. The same date-aware join drives which official's portrait/name appears on dashboards for a given reporting period.
MoM ↔ Meeting ↔ Ticket ↔ Action Items ↔ Sub-tasks. A stalled tickets row spawns a meetings row (type tri). The meeting produces exactly one mom (1:1). On MoM upload, an ai_runs row (feature mom_extract) emits extracted_action_items. The officer confirms each item; on confirm, a ticket_subtasks row is created (linking the child ticket back to the parent), extracted_action_items.subtask_id is set, and status flips to converted. The chain is fully traceable ticket → meeting → mom → ai_run → action_item → subtask.
User ↔ Role ↔ Override ↔ Capability. Authorization resolves through users → user_role_assignments → roles → role_template_permissions for the baseline, then user_permission_overrides (and rep_permission_overrides for the company side) for per-user grants/revocations, then an ABAC gate for tickets.is_confidential / is_vip. The result is cached per request. The mirror tables representatives → rep_role_assignments → roles carry the identical pattern on the company side.
Verification ↔ Organization lifecycle. organizations.verification_status moves provisional → verified only when all required verification_jobs (one per provider per entity type) report passed; any failed moves it to on_hold and an appeals row may be raised. consents gates which providers may be queried. organizations.kyc_level rises with the depth of checks passed; missed re_atted_due_at downgrades verified → provisional → suspended (per roles doc §11.4).
Files ↔ Records ↔ Retention. Every blob is a media_library row (bucket, key, checksum, AV status, expiry). Content records reference it by FK (ticket_attachments.media_id, verification_documents.media_id, mom.source_media_id, documents.media_id, meeting_recordings.media_id, officials.portrait_media_id, open_data_exports.media_id). A scheduled cleanup worker uses media_library.expires_at plus the data-class retention policy (§9) to revoke links and purge the object from MinIO, while preserving the metadata rows for audit continuity.
Feature flags ↔ Every capability. No capability ships un-gated. Code paths consult feature_flags via a thin client (cached in Redis); the resolution chain (platform → dept → env → user_segment → off) lets the Super Admin toggle anything per environment without a redeploy. Changes are audit-logged to audit_logs.

6. Indexing Strategy

MariaDB auto-indexes only the child side of an FK; we declare explicit secondary indexes on every FK and on every hot access path. Indexes are reviewed quarterly via EXPLAIN ANALYZE.

6.1 Unique business identifiers (equality lookups)

Table	Column(s)	Index
`tickets`	`tracking_id`	`UNIQUE`
`users`	`keycloak_sub`, `email`	`UNIQUE` (two)
`organizations`	`secp_registration_no`, `ntn`	`UNIQUE` (partial — NULL allowed)
`representatives`	`email`, `user_id`	`UNIQUE`
`roles`	`code`	`UNIQUE`
`feature_flags`	`flag_key`	`UNIQUE`
`system_settings`	`setting_key`	`UNIQUE`
`certifications`	`certificate_code`	`UNIQUE`
`qr_verifiable_documents`	`qr_token`	`UNIQUE`
`media_library`	`checksum_sha256`	non-unique (dedup scan)

6.2 Ticket queue hot paths (composite)

Table	Composite index	Serves
`tickets`	`(status, dept_id, sla_due_at)`	Department queue + escalation scan.
`tickets`	`(org_id, status, updated_at)`	Company workspace ("my tickets").
`tickets`	`(assigned_user_id, status)`	"My work" queue for an officer.
`tickets`	`(dept_id, priority, status)`	Oversight dashboard (DG/Secretary).
`tickets`	`(is_confidential, dept_id)`	ABAC confidential filtering.
`tickets`	`(sla_due_at)`	Standalone escalation/overdue cron scan.

6.3 Foreign-key secondary indexes

Every FK column listed "indexed" in §4 carries an explicit INDEX. Notable high-cardinality ones: ticket_messages.thread_id, ticket_attachments.ticket_id, notifications.user_id, notifications.event_key, audit_logs.action, ai_runs.engine_config_id, meeting_attendees.meeting_id, channel_messages.channel_id, sla_pause_events.ticket_id, escalation_events.ticket_id.

6.4 Locale / multilingual

tickets.locale, users.locale, representatives.locale, notification_templates.locale are indexed low-cardinality columns used as query filters; they do not replace Meilisearch for full-text Urdu/Sindhi search.
Trilingual text columns (name_en/ur/sd, body_en/ur/sd, title_en/ur/sd) are stored as separate physical columns (not JSON), so the application selects the locale-specific column directly — no per-row JSON parsing on the hot path.

6.5 Full-text search

Primary path = Meilisearch. A search-indexer worker pushes normalized, redacted, locale-tagged documents to Meilisearch on every domain event; confidential/VIP tickets are excluded or masked before indexing.
MariaDB FULLTEXT is created only on kb_articles.body_en, tickets.title + tickets.description, and documents.title, as a Latin-script exact-phrase fallback for admin tools. Urdu/Sindhi full-text is not attempted on MariaDB.

7. Partitioning & Archival

Three tables grow unbounded by design and need an explicit partitioning + archival plan, aligned with the retention policy in §9 and the Sindh Archives rules referenced in _context.md §6.

Table	Growth	Strategy
`ticket_messages`	High (every reply, inbound email/WA/SMS appended)	Partition by RANGE on `created_at` (monthly). Old partitions older than the active window (e.g., 18 months) are moved to an `archive` tablespace on cheaper storage and/or exported to MinIO as Parquet for Metabase history; rows remain queryable but outside the hot partitions. `OPTIMIZE PARTITION` runs quarterly.
`audit_logs`	Append-only, never UPDATE/DELETE	Partition by RANGE on `occurred_at` (monthly). Partitions older than the online retention (e.g., 36 months) are exported to off-host object storage (hash-chained for tamper detection, per tech-arch §16) and then `DROP PARTITION`. The archive is retained per Sindh Archives rules.
`ai_runs`	High (every AI call writes a row)	Partition by RANGE on `started_at` (monthly). Cost/token columns are also rolled into a daily `ai_cost_daily` aggregate (materialized view) for dashboards; raw rows older than 12 months are archived to MinIO and dropped.
`ticket_history`, `sla_pause_events`, `escalation_events`, `notifications`	Medium	Monthly `RANGE` partitioning on their timestamp (`occurred_at` / `paused_at` / `fired_at` / `sent_at`); same archive-and-drop cadence, longer retention for forensic tables (`ticket_history`, `sla_pause_events`).
`channel_messages`, `message_reads`	Medium-high	Monthly `RANGE` partitioning on `sent_at` / `read_at`; soft-delete plus a 24-month online window before archive.

Archival pipeline. A scheduled BullMQ job (per table) moves rows older than the window into MinIO as compressed Parquet (schema included), writes an open_data_exports-style manifest row, then drops the source partition. Restores (RTI / audit / litigation hold) re-load the Parquet into a temporary table. Retention numbers are confirmed against the data-classification policy in §9 and the security doc.

8. Migration Approach

8.1 Prisma migrations

Forward-only, CI-gated. Every schema change is a numbered Prisma migration committed to the repo; migrations run as a pre-deploy, gated step in CI (dev → staging → prod, manual approval before prod). Rollback is a deliberate, reviewed migration — never an automated revert (per tech-arch §19).
schema.prisma is derived from this document. One datasource block (provider = "mysql", url = env("DATABASE_URL")); one generator block (provider = "prisma-client-js"). Tables are organized under // <-- module --> comments (Auth, Org, Tickets, Files, Notifications, AI, Comms, Analytics, Integrations, System). Optional module prefixes (§2.5) are applied consistently via @@map("tkt_tickets") style mappings so SQL stays readable while the Prisma model names stay clean.
Backward-compatible change discipline. Additive changes (new column NULL/default, new table, new index with ALGORITHM=INPLACE LOCK=NONE) ship freely. Destructive changes (rename, drop, type narrow) ship as a multi-step migration: add new → dual-write → backfill → switch reads → drop old, each step its own deployment. ENUM widening is online-safe; narrowing requires care.
Large online migrations (new index on multi-million-row tickets) use pt-online-schema-change or MariaDB's native online DDL, coordinated by the DBA, not Prisma's default ALTER.

8.2 Seed data

The following lookup/bootstrap rows are seeded by migrations and prisma db seed, idempotent and version-controlled:

Seed set	Examples
`roles`	`SUPER_ADMIN`, `SITD_FACILITATION_OFFICER`, `DEPT_ADMIN`, `OFFICER`, `DG`, `SECRETARY`, `MINISTER`, `SACM`, `READONLY_AUDITOR`, `PRIMARY_REP`, `ADMIN_REP`, `FILER`, `VIEWER`, `NOTIFY_ONLY`, `CITIZEN`, `SERVICE_ACCOUNT`.
`role_template_permissions`	The 67-capability matrix from `/specs/en/04-roles-permissions/` §8, encoded as `(role_code, capability_key, effect)`.
`departments`	The owning department S&ITD (`code='SITD'`, `is_owner_dept=1`) plus the major GoS departments (Labour `LBR`, Investment, Finance `FIN`, Excise & Taxation, Revenue, Board of Revenue, etc.), each seeded as a top-level node ready for nesting.
`ticket_categories`	A starter taxonomy per department (RTI, Grievance, Service Request, Information, Facilitation).
`sla_definitions`	Platform defaults per priority: 2-day first response / 5-day / 10-day resolution tiers, mapped to S&ITD and each seeded department.
`escalation_rules`	The 2/5/10-day tier ladder (tier 1 → DG @ 2d, tier 2 → Secretary @ 7d, tier 3 → Minister/SACM @ 17d) per department, `mode='notify'`.
`holiday_calendar`	The Sindh public holidays for the current and next Gregorian/Hijri year (seeded from the GoS notification; editable by Super Admin).
`business_hours`	Default 09:00–17:00 Monday–Friday, Asia/Karachi, per seeded department.
`feature_flags`	Bootstrap flags (e.g. `ai.enabled=true`, `mom.transcription=false`, `inbound.email=true`, `inbound.whatsapp=false`) in a known default state per environment.
`notification_templates`	The core event keys (`ticket.created`, `ticket.assigned`, `ticket.escalated.tier1/2/3`, `ticket.resolved`, `mom.published`, `verification.passed/failed`) in all three locales (`en`/`ur`/`sd`) and all four channels.
`integrations_configs`	All providers seeded `enabled=0` with a placeholder `vault_ref`; enabled + vault path set per environment by the operator.
`system_settings`	Branding defaults (portal name, tagline, footer line) per `_context.md` §1; SMTP/Mailjet/SMS/WhatsApp seeded `is_secret=1` awaiting vault population.
`ai_engine_configs`	A starter set per feature (cloud-preferred + on-prem fallback), all `enabled=0` until the operator turns them on per data-classification policy.

8.3 Environment bootstrapping

A fresh environment runs (1) prisma migrate deploy (all migrations), then (2) prisma db seed (the seed set above), then (3) an operator runbook to populate integrations_configs.vault_ref and the secrets vault, enable the required feature_flags, and create the first SUPER_ADMIN user. No secrets are ever committed; the seed writes only non-secret defaults and vault references.

9. Data Classification

Every table is tagged with a data class (Public / Internal / Confidential / Restricted) per /specs/en/15-tech-architecture/ §16. The class drives encryption, AI-engine routing, Meilisearch indexing, retention, and access logging. Detailed controls live in /specs/en/11-security-compliance/; this section cross-references them.

9.1 PII-bearing tables (highest sensitivity)

Table	PII columns	Class	Treatment
`representatives`	`cnic`, `email`, `mobile_e164`, `whatsapp_e164`, `full_name`	Restricted	Column-level encryption for `cnic` and mobiles; access logged; never indexed by Meilisearch; `SELECT` restricted to self, Primary/Admin Rep of same org, S&ITD facilitation, Super Admin (ABAC). CNIC format-validated; raw value encrypted, last-4 only in logs.
`organizations`	`secp_registration_no`, `ntn`, `srb_tax_id`, `pseb_membership_no`, `domain_email_domain`	Confidential	Tax IDs treated as confidential; visible to org reps, assigned department staff, Super Admin.
`users`	`email`, `mobile_e164`, `keycloak_sub`	Confidential	Email/mobile confidential; `keycloak_sub` is the stable identity (not a secret).
`verification_jobs`	`raw_payload` (NADRA/SECP/FBR responses)	Restricted	`raw_payload` encrypted at rest; redacted `result_json` used for analytics; never sent to cloud AI; on-prem engines only for raw PII.
`verification_documents`	uploaded CNIC/bank proof	Restricted	MinIO SSE-encrypted blob; AV-scanned; presigned URLs only; retention per policy then purged.
`media_library`	uploaded docs (CNIC scans, evidence)	Restricted/Confidential	Encrypted at rest; AV scan before `av_status='clean'`; confidential/VIP ticket attachments excluded from Meilisearch extracted-text indexing.
`inbound_replies`	`from_address`	Confidential	Used to resolve ticket; address never surfaced cross-tenant.
`notification_preferences`, `consents`	per-user settings	Confidential	Owner-only + Super Admin.

9.2 Ticket content (context-dependent)

Table	Sensitivity driver	Class	Treatment
`tickets`	`is_confidential`, `is_vip`, `is_anonymous` flags	Internal by default; Confidential/VIP/Restricted when flagged	ABAC visibility gate; confidential/VIP excluded from default department reads and from cross-department dashboards; anonymous whistleblower tickets store no cleartext reporter identity.
`ticket_messages`, `ticket_attachments`	inherits ticket flags	Internal → Restricted	Body redacted (`is_redacted=1`) before any cloud AI call; attachments on confidential tickets never leave on-prem storage/AI.

9.3 Operational / audit (integrity-sensitive)

Table	Class	Treatment
`audit_logs`	Confidential (platform)	Append-only; hash-chained; partitioned monthly; exported off-host; access by Read-only Auditor + Super Admin only.
`ticket_history`, `sla_pause_events`, `escalation_events`	Internal	Append-only; retention per archival plan (§7).
`integrations_configs`, `system_settings` (`is_secret=1`), `webhooks.secret_hash`	Restricted	No secret value ever stored in the DB — only `vault_ref` or a hash; the secrets vault is the single source.
`ai_runs`	Confidential	`input_summary` is the redacted snapshot only; raw inputs are never persisted here; cost/token data is Internal for analytics.

9.4 Public / low-sensitivity

Table	Class	Treatment
`departments`, `holiday_calendar`, `business_hours`, `ticket_categories`, `service_catalog_entries`, `circulars`, `kb_articles` (published), `officials`, `official_terms`	Public/Internal	Safe to surface on the public site and transparency dashboard; officials terms needed for historical letter accuracy.
`open_data_exports`	Public (aggregated)	Only anonymized aggregates; no PII; hash-published for tamper detection.

9.5 Retention (cross-ref security doc 11)

Retention windows are finalized in /specs/en/11-security-compliance/ and aligned with Sindh Archives rules (_context.md §6). The defaults that drive the §7 archival pipeline:

Active ticket data (tickets, messages, attachments, evidence): online for the active window, then archived; ticket metadata retained long-term (audit/RTI); confidential/VIP ticket content retained under legal hold until release.
PII (CNIC, contact): retained for the statutory period after the last ticket/relationship, then purged from hot storage (encrypted archive retained per law).
Audit logs: 36 months online minimum, indefinite archive.
AI run logs: 12 months online, aggregate cost data retained long-term.
Verification raw payloads: shortest practical retention (re-verification re-fetches); redacted result_json retained for the org lifecycle.

All purge jobs are themselves audit-logged (audit_logs, action data.purged) with the row counts and the retention rule applied, so destruction of records is provable for RTI and audit.

End of document.