Notification Routing — Data Model & Onboarding

Console is the single source of truth for client notification routing: which Telegram group a Jira/alert event reaches, whether it's sent, and under what policy. This page documents the consolidated data model, the runtime routing chain, how a client is onboarded, and how to automate that onboarding (e.g. from a Temporal workflow).

Why this exists — consolidation

Historically a single client's "who / where / whether" was smeared across four systems:

System	Held	Status
n8n jira_bot_db	project → Telegram chat routing	being retired → Console
temporal jira_sync_config_kv	notify modes + review/autocomplete policy	being retired → Console
JSM (Jira)	org ↔ service-desk structure	upstream master (read)
CDM (ClickHouse)	whether the customer is active	upstream master (read)
ad-hoc /bind Telegram command	per-group routing	replaced by the admin UI

Console consolidates the operational layer (routing + policy) into a real client record with an admin UI, an audit trail, and one internal API the temporal-workers read. It does not absorb the upstream masters — it reads the facts it needs from JSM and CDM and owns the routing/policy that results.

The consolidated model

Console tables (what Console owns)

Table	Purpose	Key columns	Populated from
clients	the customer entity	id, name, slug	created (CDM-active gated)
service_desk_org_mappings	label → client, + the JSM org link	client_id, jira_organization_id, jira_organization_name, project_label (UNIQUE), service_desk_id, UNIQUE(client_id,jira_organization_id)	JSM org + the n8n project_label
telegram_group_binding	client → chat (audience client)	client_id, chat_id, thread_id, audience, notify_jira, language, topic_name	the n8n chat_id (via backfill / discovery+bind)
telegram_team_binding	internal team → chat	team_id, chat_id, mention_handle, …	team routing
telegram_org_override	per-SUP-org chat override	sup_org_id, client_id, chat_id	n8n org_client_overrides
client_notification_settings	per-client review/autocomplete policy	client_id, review_notify_mode, autocomplete_mode, autocomplete_inactive_days, …	admin UI
notification_global_settings	global pipeline mode switches	key (pxd_*_mode), value	super-admin UI

service_desk_id

There is currently one JSM service desk (id = 1, "Support"/SUP); every org lives under it, so service_desk_id = '1' for every mapping. If a second service desk is ever added, the onboarding must capture it per-org.

Upstream sources (read, never owned)

• CDM (ClickHouse) — cdm.jira_customers (company_name, status). The active gate: only customers with status = 'Active' are onboarded. CDM uses its own customer_key (CDM-NN); there is no key join to JSM org ids, so CDM is matched by company name (or a human-confirmed mapping for renamed brands). CDM data is never stored in Console — it only decides who gets a client.
• JSM (Jira) — GET {site}/rest/servicedeskapi/organization (id, name) and /servicedesk (the service desks). Source of jira_organization_id, jira_organization_name, service_desk_id.
• n8n jira_bot_db (project_mappings, team_settings, org_client_overrides) — the project_label → chat_id routing, imported by the backfill tool. Being retired once routing is fully on Console.

Runtime routing chain

When a Jira/PXD event fires, the temporal-workers ask the Console resolver (POST /api/v1/internal/telegram/notify-targets) for the targets:

The resolver matches the ticket's labels against service_desk_org_mappings.project_label, finds the client, and returns that client's chat from telegram_group_binding (dropping bindings with notify_jira = false, applying any telegram_org_override). The worker then sends via @proximaopsbot. See Ops Bot for the send path.

Onboarding a client (manual flow)

This is the flow used to populate the active customers. Each step has a clear source:

1. Gate on CDM — only status = 'Active' customers (skip Passive / In analysis / not-in-CDM). Renamed brands (the routing/JSM name differs from the CDM legal name) need a human-confirmed mapping.
2. Resolve the JSM org — jira_organization_id, jira_organization_name, service_desk_id (=1).
3. Create the client (name, slug) — idempotent on slug.
4. Create the service_desk_org_mappings row — (client_id, jira_organization_id, jira_organization_name, project_label, service_desk_id). One client can own several orgs (e.g. a SUP + an FP org), but only one project_label per (client, org) (the UNIQUE(client_id, jira_organization_id) constraint). Extra labels for the same (client, org) keep routing via the Console-first / n8n fallback.
5. Create the telegram_group_binding — client → chat_id (read from n8n), audience = 'client'. The backfill-telegram-routing tool does this in bulk once the mappings exist.

The fallback for un-onboarded / collision routes

CONSOLE_ROUTING_SOURCE=console resolves via Console only; a label with no service_desk_org_mappings row (inactive customer, or a (client, org) collision) returns no target. The worker's empty-result fallback routes those via jira_bot_db instead of dropping them — parity until they're onboarded or n8n is retired.

Automating onboarding via Temporal

The manual flow above maps cleanly onto a Temporal workflow. Recommended shape:

Activities & APIs (all idempotent — check-then-create or ON CONFLICT):

Activity	Source / call	Idempotency key
CdmActiveCheck	ClickHouse cdm.jira_customers	— (read)
ResolveJsmOrg	GET /rest/servicedeskapi/organization	— (read)
EnsureConsoleClient	POST /api/v1/clients (super-admin)	slug
EnsureOrgMapping	POST /api/v1/service-desk/admin/organizations	project_label (UNIQUE) + (client, org)
EnsureTelegramBinding	POST /api/v1/admin/telegram/groups/{chat_id}/bind (see Telegram Groups admin)	chat_id

Design notes for the automation:

• Idempotent end-to-end — every write is upsert/ON CONFLICT DO NOTHING, so re-runs and retries are safe. This is what lets a Temporal workflow own it.
• CDM gate is mandatory — never create a client for a non-Active customer; that's the rule that keeps the consolidated view clean.
• Renamed brands — the JSM/routing name (Royal Taxi) often differs from the CDM legal name (You Cloud), and there's no key join. Keep a small override map (project/org → CDM customer) the workflow consults; surface unmatched-but-active candidates for human confirmation rather than guessing.
• The (client, org) constraint — if a client has several projects under one JSM org, only the first maps; the rest are intentionally left to the fallback (or relax UNIQUE(client_id, jira_organization_id) in a migration after confirming the JSM ticket-sync tolerates multiple labels per client-org).
• Auth — Console admin endpoints are super-admin / clients:write; the internal resolver + GetConfig are service-account-pinned (telegram_ops:read). A Temporal worker SA needs the right scopes per call.
• Bot identity — sending only needs the bot token; binding a chat requires @proximaopsbot to already be in that group (it is, for any chat n8n already routes to). Do not move the bot webhook as part of onboarding — see the cutover runbook.

Reference — key facts

• Resolver: POST /api/v1/internal/telegram/notify-targets → {targets:[{chat_id, thread_id, audience, mention, team_name, language, topic_name}]}.
• Config: GET /api/v1/internal/notification-config?key= (modes + autocomplete_org_thresholds), X-API-Key, telegram_ops:read.
• service_desk_id is always 1 (single SUP service desk today).
• CDM is a gate, not storage — matched by company name; renamed brands need a confirmed override.
• Backfill tool: backend/cmd/backfill-telegram-routing imports n8n routing into Console bindings (dry-run by default; -apply, -create-teams).
• The flip + cutover sequence lives in the Console-flip cutover runbook (temporal-workers repo).