Dual Database Architecture

Proxima Invoice uses two databases with clearly separated responsibilities: PostgreSQL for writes and ClickHouse for reads.

Why Two Databases?

ClickHouse already contains all Jira, Tempo, and CDM data — synced by existing n8n workflows every ~15 minutes. Duplicating this data into PostgreSQL would create sync complexity and data inconsistency.

PostgreSQL provides ACID transactions needed for invoice state management, where concurrent edits, status transitions, and financial updates must be atomic.

Rules

Rule	Rationale
Never write to ClickHouse	It's a shared analytics database managed by n8n
Never read large datasets from PostgreSQL	ClickHouse has the worklog/CDM data optimized for analytical queries
Use PostgreSQL for all invoice state	ACID transactions for financial data integrity
Join across databases in application code	No cross-database queries; fetch from each and merge in Go

PostgreSQL (Write Database)

Connection: pgx/v5 with connection pooling Port: 5433 (Docker Compose) Timeout: 5 seconds per query Retry: Max 2 for transient errors

Tables

Table	Purpose
invoices	Invoice headers with status, amounts, metadata
invoice_line_items	Individual billable items
invoice_events	Audit log of all status changes
invoice_edit_history	Track all user edits with before/after values
contract_rate_rules	Per-application billing rates and multipliers
app_settings	Application configuration (company, bank, defaults)
calculator_snapshots	Pricing calculator results
project_classifications	Billable/internal/overhead project labels
employee_exclusions	Employees excluded from billing

See PostgreSQL Data Model for full schemas.

ClickHouse (Read Database)

Connection: clickhouse-go/v2 with HTTP protocol Endpoint: clickhouse-api.prxm.uz:443 (HTTPS) Timeout: 15 seconds per query TLS: Required (custom parseHTTPDSN() for HTTPS support)

Tables

Database.Table	Purpose
cdm.jira_applications	Client deals/contracts (PK: application_key)
cdm.jira_customers	Client company details (PK: customer_key)
cdm.jira_contacts	Client contact persons
tempo.worklogs_flat	Time tracking entries from Tempo
jira.jira_issues	Jira issue metadata

See ClickHouse Data Model for full schemas.

ClickHouse Connection Gotchas

The ClickHouse HTTPS connection requires special handling:

// Standard ParseDSN rejects https:// URLs
// Custom parseHTTPDSN() extracts components and configures TLS
opts := &clickhouse.Options{
    Protocol: clickhouse.HTTP,
    Addr:     []string{"clickhouse-api.prxm.uz:443"},
    Auth: clickhouse.Auth{
        Database: "default",
        Username: "default",
        Password: password,
    },
    TLS: &tls.Config{},
}

Type scanning in HTTP mode is strict:

• UInt32 columns → must use Go uint32 (not int)
• Nullable(UInt32) → use ifNull(col, default) or toFloat64() in queries
• Decimal(18,2) → use toFloat64() cast for scanning into Go float64

Cross-Database Queries

When data from both databases is needed (e.g., invoice with client details), the application:

1. Fetches invoice data from PostgreSQL
2. Fetches client data from ClickHouse
3. Merges in Go code

// Example: Get invoice with client info
invoice, err := invoiceRepo.GetByID(ctx, id)       // PostgreSQL
customer, err := chClient.GetCustomer(ctx, invoice.CustomerKey) // ClickHouse
// Merge in response

There are no cross-database joins or foreign keys between PostgreSQL and ClickHouse.