sims-hq/docs/superpowers/specs/2026-07-17-apex-parity-design.md

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