ClickHouse Schema

ClickHouse is the read-only analytics database. Data is synced by n8n workflows from Jira, Tempo, and the CDM pipeline. Never write to ClickHouse from the invoice application.

Actual Table Names

The table names below are the actual names in the ClickHouse cluster, which may differ from names in early specification documents. Always use these names in queries.

Connection

Endpoint: clickhouse-api.prxm.uz:443
Protocol: HTTPS (clickhouse.HTTP)
Database: default
TLS: Required

Tables

cdm.jira_applications

Client deals/contracts. Each application represents a service agreement with a client.

Column	Type	Description
application_key	String	PK — Unique application identifier
customer_key	String	Foreign key to jira_customers
status	String	Active, Closed, etc.
application_name	String	Human-readable name
description	String	Application description
start_date	Date	Contract start
finish_date	Nullable(Date)	Contract end
deal_amount	Decimal(18,2)	Contract value (usually UZS)
deal_type	String	SUP, HR, or FP
monthly_limit	Nullable(UInt32)	Hours included in SUP base
hourly_rate	Decimal(18,2)	Rate per hour
currency	String	UZS, USD, EUR
invoice_amount	Decimal(18,2)	International billing amount
agreement_date	Nullable(Date)	Agreement signing date
agreement_number	Nullable(String)	Agreement reference number
labels	String	Project labels (maps to worklogs)

Key gotchas:

• monthly_limit is Nullable(UInt32) — use ifNull(monthly_limit, 0) or toFloat64() in queries
• labels field may be empty for newer applications
• labels maps to project_label in worklogs and issues

---

cdm.jira_customers

Client company details for invoice templates and international detection.

Column	Type	Description
customer_key	String	PK — Unique customer identifier
company_name	String	Legal company name
company_type	String	LLC, JSC, etc.
address	String	Company address
tin	String	Tax ID (INN)
ccea	String	ОКОНХ code
swift_bic	String	SWIFT/BIC code (international detection)
account_number	String	Bank account number
bank_name	String	Bank name
signer_full_name	String	Authorized signer name
signer_title	String	Signer's position
vat	String	VAT number
bin	String	BIN number
registration_number	String	Registration number
country	String	Country code
email	String	Contact email
phone	String	Contact phone
telegram_link	String	Telegram contact
website	String	Company website
source	String	Data source
customer_type	String	Customer classification
industry	String	Industry sector

International detection: swift_bic.length > 2 or currency != 'UZS'

---

cdm.jira_contacts

Client contact persons (for email delivery in Phase 4).

Column	Type	Description
customer_key	String	FK to jira_customers
name	String	Contact name
email	String	Contact email
phone	String	Contact phone

---

tempo.worklogs_flat

Time tracking entries from Tempo, synced every ~15 minutes.

Column	Type	Description
duration_seconds	UInt32	Time logged in seconds
account_id	String	Jira user account ID
user_name	String	Display name
start_date	Date	Date work was performed
project_label	String	Project label (maps to application)
issue_key	String	Jira issue key

Key gotchas:

• Table is tempo.worklogs_flat (NOT tempo.worklogs)
• duration_seconds is UInt32 — must scan into Go uint32
• project_label maps to labels in cdm.jira_applications

---

jira.jira_issues

Jira issue metadata, used for categorization (issue type, priority).

Column	Type	Description
issue_key	String	Jira issue key (e.g., PROJ-123)
issue_type	String	Bug, Task, Story, Incident, Meeting, etc.
priority	String	P1, P2, P3, P4, P5
project_label	String	Project label
summary	String	Issue summary/title
customfield_10150	String	Custom description field

Key gotchas:

• Table is jira.jira_issues (NOT jira.issues)
• Issue type and priority are used for rate tier assignment
• Critical incident = issue_type = 'Incident' AND priority IN ('P1', 'P2', 'P3')

Application → Worklog Mapping

The connection between applications and worklogs goes through project labels:

One application can have multiple comma-separated labels, mapping to multiple projects in Jira.

Type Scanning in HTTP Mode

ClickHouse's HTTP protocol has strict type scanning:

ClickHouse Type	Go Type	Notes
UInt32	uint32	int will fail
Nullable(UInt32)	*uint32 or use ifNull()	Wrap in query
Decimal(18,2)	float64 via toFloat64()	Cast in query
String	string	Direct scan
Date	time.Time	Direct scan