You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
76 lines
5.1 KiB
Markdown
76 lines
5.1 KiB
Markdown
# HQ Console — APEX Parity Slice (Ticket Desk, Service Data, Branches, Full Importer) — D20
|
|
|
|
> **STATUS: DELIVERED 2026-07-17.** Driven by the real APEX export in `C:\SiMS\hq\Apex`
|
|
> (25 tables surveyed; a 46-agent profiling+audit swarm hardened the importer against the
|
|
> real data). Founder calls locked via review: full ticket desk YES; client_module
|
|
> service-data extension YES; client_branch YES; the five modules are **SMS, RTGS, WhatsApp,
|
|
> Mobile App, ATM**.
|
|
>
|
|
> **Trial import into local Postgres verified (2026-07-17):** 273 clients (110 encrypted DB
|
|
> passwords), 167 client↔module links (SMS 105 · RTGS 53 · ATM 6 · MobileApp 2 · WhatsApp 1;
|
|
> 76 encrypted portal passwords), 244 branches, 150 documents = ₹33,16,800.24 (INV 74 · PI 75
|
|
> · QT 1), 72 payments = ₹14,29,297 (allocations balanced), 1,826 tickets (1,591 closed /
|
|
> 121 open / 73 in-progress / 40 dropped / 1 waiting), 669 interactions; INVOICE series
|
|
> seeded to 10252 (matches APEX `next_val`). Full suite 358 green.
|
|
|
|
## 1. What the APEX data taught us (survey verdicts)
|
|
|
|
| APEX table (rows) | Verdict | Lands as |
|
|
|---|---|---|
|
|
| `sims_clients_list` (273) | carry | `client` (all support fields exist); YES/NO service flags → `client_module` rows |
|
|
| `sims_client_sector` (30) | carry-lite | sector text + UI suggestions |
|
|
| `sims_ho_branch_list` (251) | **new table** | `client_branch` |
|
|
| `sims_bill_filed` (269) | carry | `document` history + **payment + allocation rows** from PAYMENT_DATE/AMOUNT |
|
|
| `sims_invoices` / `_items` (0) | skip | APEX's unfinished redesign — our schema already is it |
|
|
| `sims_invoice_type` / `_billing_head` / `payment_status` / `lov_group` / `app_control` | skip | our CHECK enums / roles cover them |
|
|
| `sims_new_ticket` (4,567) | **new feature** | `ticket` table + Tickets page + history import |
|
|
| `sims_ticket_trigger_log` (15k) / `ticket_report` (42) | skip | audit_log covers; report is a snapshot |
|
|
| `sims_sms_project_list` (636) / `rtgs_project_list` (146) | carry | `client_module` status + descriptions → `interaction` rows |
|
|
| `sims_sms_clients_list` (107) | carry | `client_module` service columns + details |
|
|
| `sims_sms_client_modules` (255) | carry | active alert types → `client_module.details` |
|
|
| `sims_sms_features` (6) / `sms_provider` (4) | carry-lite | settings (`sms.provider.<name>.url`) + details vocabulary |
|
|
| `sims` (1) / `next_val` (1) | carry-lite | company settings check; INVOICE series seed |
|
|
| `sims_calander_event` (13) / `temp_report_data` (1) | skip | demo/scratch rows |
|
|
|
|
APEX stores the client DB password AND SMS portal passwords in plaintext; import encrypts both
|
|
(AES-256-GCM, HQ_SECRET_KEY) — cutover closes a real hole.
|
|
|
|
## 2. Schema (additive, both engines)
|
|
|
|
| Change | Notes |
|
|
|---|---|
|
|
| `ticket` table | `id, client_id, branch_id NULL, module_code NULL ('SMS','RTGS',…), kind, description, status CHECK ('open','in_progress','waiting','closed','dropped'), assigned_to NULL → staff_user, online_offline NULL, opened_on, closed_on NULL, created_by, created_at, source` |
|
|
| `client_branch` table | `id, client_id, name, code NULL, active 0/1` |
|
|
| `client_module` + | `provider NULL, username NULL, password_enc NULL, details TEXT '[]' (JSON [{label,value}]), remark NULL` |
|
|
| sqlite `migrate()` guards + pg migration `002-apex-parity` | same DDL discipline as D19 |
|
|
|
|
## 3. Behavior
|
|
|
|
- **Tickets page** (WORK nav): chips `Open · In progress · Waiting · Closed`, Mine/All
|
|
(managerial), assign dropdown, status actions, new-ticket dialog (client type-ahead, branch
|
|
select, module, kind, description). Client 360 gains a Tickets tab. All mutations audited;
|
|
honest pagination. Kinds are free text with suggestions from history (config over code).
|
|
- **Module card / roster**: service columns (provider/username/•••• password with audited
|
|
reveal, gated managerial like the DB password) + editable labeled details.
|
|
- **Importer v2** (`import-apex.ts`, staged like v1, owner-only, same UI): reads the xlsx-derived
|
|
CSVs — clients (+flags→modules, sector, secretary contact), branches, bill_filed (documents,
|
|
QT/PI/INV mapping, GST split by client state, payments+allocations, `SMS/2024/…` legacy numbers,
|
|
series seed from numeric tail), tickets (status mapping CLOSED→closed etc.), sms/rtgs service
|
|
data → client_module columns+details, project lists → module status + interaction rows.
|
|
Every row staged with per-row problems; commit refuses while any problem exists.
|
|
- **Numbers**: rupee amounts in APEX (incl. fractional per-unit rates like ₹0.16) convert via
|
|
`fromRupees` to integer paise at the staging edge.
|
|
|
|
## 4. Non-goals
|
|
|
|
TA/leave (HR — permanently out per D16), APEX page-ACL parity, trigger-log import, SMS *sending*.
|
|
|
|
## 5. Build order (suite green each phase)
|
|
|
|
1. Schema + repos (ticket, branch, module extensions) with tests
|
|
2. API routes + gating (tickets CRUD/assign/status; branches; module service data + reveal)
|
|
3. Web: Tickets page + nav + Client 360 tab; module card service data; branch list
|
|
4. Importer v2 + tests (fixture CSVs cut from the real export shapes)
|
|
5. Trial import of the real export into local Postgres; reconciliation report (counts + ₹ totals vs APEX)
|
|
6. Docs + D-record
|