Data Sync Architecture

The service desk relies on a multi-system data pipeline to keep Console, Jira, and ClickHouse in sync. This page documents the complete flow for ticket creation, updates, and the ETL process.

System Overview

Design: Option D — Console-Side Merge

The system uses a single ClickHouse writer (Dagster) combined with PostgreSQL pending writes for instant visibility. This avoids schema duplication across systems while keeping the user experience instant.

Why this design?

Concern	How it's handled
Instant visibility after creation	Pending write in PostgreSQL, merged into list results
Single ClickHouse writer	Dagster only — no schema duplication
Reliable SUP→PXD creation	Temporal with 3-layer idempotency
Full ticket data	Dagster syncs all 23 columns within ~1 min
Jira webhook delays (1-40 min)	Console calls Temporal gateway directly — no dependency on Jira webhooks

What the user sees

Time	Source	What's displayed
0s	Pending write (PostgreSQL)	SUP key, summary, priority, reporter, status "New"
~5s	Temporal creates PXD	(invisible — happens in background)
~1 min	Dagster syncs to ClickHouse	Full data: PXD key, assignee, team, all 23 columns
30 min	Pending write expires	ClickHouse data takes over (already there from ~1 min)

Ticket Creation Flow

Pending Writes (Option D Detail)

The service_desk_pending_writes table in PostgreSQL bridges the gap between ticket creation and Dagster sync:

Schema

Column	Type	Description
id	UUID	Primary key
client_id	UUID	FK to clients — for access scoping
issue_key	VARCHAR(50)	SUP-xxxx from JSM response
action	VARCHAR(20)	create_ticket or transition
payload	JSONB	Original create request (summary, priority, type)
jira_response	JSONB	Full JSM API response (reporter, status, dates)
created_at	TIMESTAMPTZ	When the write was recorded
expires_at	TIMESTAMPTZ	now() + 30 minutes — auto-cleanup

How the merge works

The ListTickets handler in service_desk_handler.go calls mergePendingWrites():

1. Query ClickHouse for tickets (normal flow)
2. Query PostgreSQL for non-expired pending writes
3. For each create_ticket pending write:
   • Extract fields from payload: summary, priority, issue_type
   • Extract fields from jira_response: SUP key, reporter name/email, status, created date
   • Create a synthetic ticketRow with these fields
   • Prepend to the ClickHouse results
4. For transition pending writes, update the status on the matching ClickHouse row
5. Return merged results

Fields available from pending write

Field	Source	Available?
SUP key	jira_response.issueKey	✅
Summary	payload.summary or jira_response.summary	✅
Priority	payload.priority	✅
Issue type	payload.issue_type	✅
Status	jira_response.currentStatus.status	✅
Status category	jira_response.currentStatus.statusCategory	✅
Reporter name	jira_response.reporter.displayName	✅
Reporter email	jira_response.reporter.emailAddress	✅
Created date	jira_response.createdDate.iso8601	✅
PXD key	—	❌ (not created yet)
Assignee	—	❌
Team	—	❌
Project label	—	❌
Due date	—	❌
Resolved	—	❌
SLA data	—	❌

After Dagster syncs (~1 min), all 23 columns become available from ClickHouse.

Temporal: SUP → PXD Workflow

Architecture

• Gateway: Stateless Go HTTP service. Receives webhook from Console backend (and Jira as backup). Calls start_workflow with id = sup-to-pxd-{SUP-key}.
• Worker: Go binary polling task queue jira-sup-pxd. Runs the SupToPxdWorkflow.
• Config DB: PostgreSQL tables for team→component mappings, idempotency keys, feature flags.

Workflow steps

1. Load config from Postgres (mode, mappings, excluded actors)
2. Feature flag check: disabled / shadow / label_opt_in / full
3. Load SUP issue from Jira (fresh GET, don't trust webhook payload)
4. Idempotency check (3 layers):
   • Workflow ID collision (Temporal built-in)
   • SUP customfield_10150 already set (Jira-side)
   • Postgres jira_idempotency_keys table (in_flight → committed)
5. Fetch org details from Atlassian CSM API
6. Derive PXD payload (issue type routing, team→component mapping)
7. Reserve idempotency key (Postgres INSERT, state=in_flight)
8. Create PXD issue in Jira (POST /rest/api/3/issue)
9. Commit idempotency key (UPDATE state=committed)
10. Update SUP syncer field (customfield_10150 = PXD-xxxx)

Retry policies

Activity type	Max attempts	Backoff	Non-retryable
Read (Jira/Postgres GET)	10	2× from 1s	404, 401
Postgres write	8	2× from 1s	unique_violation (handled)
Jira POST/PUT	5	2× from 2s	4xx except 429

Cutover phases

Phase	Mode	Behavior
0	disabled	Workflow exits immediately, no side effects
1	shadow	Fetches SUP, derives payload, logs — no PXD created
2	label_opt_in	Only creates PXD for SUP issues with opt-in label
3	full	Creates PXD for all SUP issues

Dagster: Jira Mirror ETL

Dagster is the sole writer to ClickHouse jira.jira_issues. Runs as a cron every 1 minute:

Schedule (every 1 min)
→ JQL: project=PXD AND updated >= 15 minutes ago
→ Transform to 23-column ClickHouse schema
→ INSERT INTO jira.jira_issues (batch, JSONEachRow)
→ OPTIMIZE TABLE jira.jira_issues FINAL
→ Audit log to jira.jira_sync_audit
→ Stale check → Telegram alert if no sync in 15+ min

ClickHouse schema (23 columns)

Column	Jira Source
issue_key	issue.key (PXD-xxxx)
linked_issue_key	customfield_10150 (SUP-xxxx)
summary	fields.summary
status	fields.status.name
status_category	fields.status.statusCategory.name
created	fields.created
updated	fields.updated
resolved	fields.resolutiondate
reporter_name	customfield_10183
reporter_email	customfield_10878
assignee	fields.assignee.displayName
project_key	fields.project.key
project_name	fields.project.name
issue_type	fields.issuetype.name
priority	fields.priority.name
severity	customfield_10049.value
project_label	fields.labels[0]
team_name	Component name
linked_issue_key	customfield_10150
duedate	fields.duedate
flagged	customfield_10021 contains "Impediment" → 1
started	customfield_10015
timeoriginalestimate	customfield_10645

What Dagster catches

• Updates to existing tickets (status changes, field edits)
• Tickets created outside Console (Jira portal, email, API)
• Any tickets missed by Temporal (shouldn't happen, but safety net)
• Comment sync, attachment sync metadata

Console → Temporal Gateway Call

After CreateTicket succeeds in the Console backend:

// Fire-and-forget POST to Temporal gateway
go func() {
    body, _ := json.Marshal(map[string]string{"sup_issue_key": result.IssueKey})
    req, _ := http.NewRequest("POST", cfg.TemporalGatewayURL+"/jira/sup-issue-created", bytes.NewReader(body))
    req.Header.Set("Content-Type", "application/json")
    req.Header.Set("X-Webhook-Secret", cfg.TemporalWebhookSecret)
    resp, err := http.DefaultClient.Do(req)
    if err != nil || resp.StatusCode >= 300 {
        slog.Warn("temporal gateway call failed", "error", err, "issue_key", result.IssueKey)
    }
    if resp != nil { resp.Body.Close() }
}()

This runs in a goroutine — doesn't block the API response. If it fails, the Jira webhook (backup) or manual retry handles it.

Failure Scenarios

Scenario	Impact	Recovery
Temporal gateway down	PXD not created immediately	Jira webhook retries; pending write visible for 30 min
Temporal worker down	Workflow queued, executes when worker recovers	Temporal guarantees execution
Dagster cron fails	ClickHouse stale	Telegram alert; next cycle retries; pending write covers gap
Console backend down	Can't create ticket	User sees error
ClickHouse down	Reads fail (503)	Writes to JSM still work; pending writes visible
Jira down	Can't create SUP	User sees error
Duplicate webhook (Console + Jira)	Two start_workflow calls	Temporal deduplicates by workflow ID

Monitoring

System	What to watch
Console	proxima_servicedesk_pending_writes gauge; proxima_servicedesk_writes_total counter
Temporal	Workflow execution history in Temporal UI; workflow failure count
Dagster	Asset materialization status; jira.jira_sync_audit table; stale data Telegram alerts
ClickHouse	SELECT max(updated) FROM jira.jira_issues — should be within 2 min of now

Migration Status

Component	Old (n8n)	New	Status
SUP → PXD creation	n8n sub-workflow	Temporal workflow	✅ Built, deploying
ClickHouse sync	n8n ETL cron (1 min)	Dagster mirror (1 min)	🔄 In progress
Instant visibility	n8n ClickHouse sub-workflow	Pending writes (Option D)	✅ Live
PXD ↔ SUP sync	n8n PXD↔SUP sync workflow	Temporal (future)	⏳ Planned
Console → gateway call	N/A	Backend HTTP POST	⏳ Pending gateway URL