PostgreSQL Schema
PostgreSQL is the write database for all invoice data. Connection via pgx/v5 with pool, port 5433, 5-second query timeout.
Entity Relationship
Tables
invoices
Primary invoice records with status, amounts, and metadata.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK, DEFAULT gen_random_uuid() | Primary key |
invoice_number | SERIAL | Auto-increment number | |
display_number | VARCHAR(50) | NOT NULL | Display format (e.g., INV-1) |
application_key | VARCHAR(20) | NOT NULL | Client application key |
customer_key | VARCHAR(20) | NOT NULL | Customer key |
period_start | DATE | NOT NULL | Billing period start |
period_end | DATE | NOT NULL | Billing period end |
invoice_date | DATE | NOT NULL, DEFAULT CURRENT_DATE | Invoice issue date |
deal_type | VARCHAR(5) | NOT NULL, CHECK (SUP/HR/FP) | Deal type |
currency | VARCHAR(5) | NOT NULL, DEFAULT 'UZS' | Currency code |
base_amount | DECIMAL(15,2) | NOT NULL | Base contract amount |
overtime_hours | DECIMAL(8,2) | DEFAULT 0 | Hours beyond limit |
overtime_amount | DECIMAL(15,2) | DEFAULT 0 | Overtime charge |
total_amount | DECIMAL(15,2) | NOT NULL | Final invoice total |
total_hours | DECIMAL(8,2) | DEFAULT 0 | Total billable hours |
is_overtime | BOOLEAN | DEFAULT FALSE | Has overtime flag |
monthly_limit_hours | DECIMAL(8,2) | Monthly hour limit from rate rules (stored at generation) | |
status | VARCHAR(20) | NOT NULL, DEFAULT 'draft' | Current status |
approved_at | TIMESTAMPTZ | Approval timestamp | |
approved_by | VARCHAR(100) | Approver identity | |
declined_at | TIMESTAMPTZ | Decline timestamp | |
declined_by | VARCHAR(100) | Decliner identity | |
decline_reason | TEXT | Reason for declining | |
rejected_at | TIMESTAMPTZ | Rejection timestamp | |
rejected_by | VARCHAR(100) | Rejector identity | |
reject_reason | TEXT | Reason for rejecting | |
sent_at | TIMESTAMPTZ | Sent to client timestamp | |
paid_at | TIMESTAMPTZ | Payment received timestamp | |
r2_storage_key | VARCHAR(500) | Cloudflare R2 object key | |
r2_public_url | VARCHAR(500) | Public PDF URL | |
planfact_shipment_id | VARCHAR(100) | PlanFact shipment ID | |
planfact_deal_id | VARCHAR(100) | PlanFact deal ID | |
didox_document_id | VARCHAR(100) | Didox document ID | |
didox_status | VARCHAR(50) | Didox status | |
pdf_storage_path | VARCHAR(500) | PDF file path | |
html_template_hash | VARCHAR(64) | SHA-256 of rendered HTML | |
pdf_data | BYTEA | PDF binary data | |
show_task_list | BOOLEAN | NOT NULL, DEFAULT TRUE | Show individual task rows vs summary |
show_tracked_time | BOOLEAN | NOT NULL, DEFAULT TRUE | Show time column in table |
show_task_pricing | BOOLEAN | NOT NULL, DEFAULT FALSE | Show price column per line item |
show_overtime | BOOLEAN | NOT NULL, DEFAULT TRUE | Show overtime row |
legal_text | TEXT | Per-invoice legal text (overrides template default) | |
version | INT | NOT NULL, DEFAULT 1 | Invoice version |
created_at | TIMESTAMPTZ | NOT NULL, DEFAULT NOW() | Created timestamp |
updated_at | TIMESTAMPTZ | NOT NULL, DEFAULT NOW() | Last update timestamp |
created_by | VARCHAR(100) | Creator identity |
Indexes:
idx_invoices_status— Filter by statusidx_invoices_application— Filter by applicationidx_invoices_period— Filter by date rangeidx_invoices_customer— Filter by customer
Unique constraint: (application_key, period_start, version)
Status check: status IN ('draft', 'needs_review', 'approved', 'declined', 'sent', 'accepted', 'rejected', 'paid')
invoice_line_items
Individual billable items on an invoice.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Primary key |
invoice_id | UUID | FK → invoices(id) ON DELETE CASCADE | Parent invoice |
line_type | VARCHAR(20) | NOT NULL, CHECK | Item category |
description | TEXT | NOT NULL | Display description |
jira_key | VARCHAR(20) | Jira issue key | |
jira_desc | TEXT | Jira issue description | |
time_seconds | INT | DEFAULT 0 | Raw time logged |
billable_seconds | INT | DEFAULT 0 | After min billable rounding |
unit_price | DECIMAL(15,2) | Price per unit | |
quantity | DECIMAL(8,2) | DEFAULT 1 | Quantity (hours) |
line_total | DECIMAL(15,2) | Line item total | |
rate_tier | VARCHAR(30) | DEFAULT 'standard' | Rate tier applied |
rate_multiplier | DECIMAL(5,2) | DEFAULT 1.0 | Rate multiplier |
source | VARCHAR(10) | NOT NULL, DEFAULT 'auto' | auto or manual |
is_auto_generated | BOOLEAN | NOT NULL, DEFAULT TRUE | Auto-generated flag |
original_description | TEXT | Pre-edit description | |
original_time_seconds | INT | Pre-edit time | |
original_line_total | DECIMAL(15,2) | Pre-edit total | |
linked_issue_key | VARCHAR(20) | Linked issue key (e.g. SUP-* from Jira "duplicates" relation) | |
contributors | JSONB | Per-person worklog breakdown | |
sort_order | INT | NOT NULL, DEFAULT 0 | Display order |
created_at | TIMESTAMPTZ | NOT NULL, DEFAULT NOW() | Created timestamp |
Line types: support, monitoring, meeting, incident, task, overtime, incident_critical, off_hours, critical_off_hours
invoice_events
Audit log for all invoice lifecycle events.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key |
invoice_id | UUID | FK → invoices(id) |
event_type | VARCHAR(30) | Event type |
from_status | VARCHAR(20) | Previous status |
to_status | VARCHAR(20) | New status |
actor | VARCHAR(100) | Who performed the action |
metadata | JSONB | Additional event data |
created_at | TIMESTAMPTZ | Event timestamp |
Event types: created, status_changed, regenerated, line_items_updated, invoice_updated, planfact_synced, planfact_sync_failed
invoice_edit_history
Tracks all user edits with before/after values.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key |
invoice_id | UUID | FK → invoices(id) ON DELETE CASCADE |
line_item_id | UUID | FK → invoice_line_items(id) ON DELETE SET NULL |
edit_type | VARCHAR(20) | Type of edit |
field_name | VARCHAR(50) | Which field was changed |
old_value | TEXT | Value before edit |
new_value | TEXT | Value after edit |
edited_by | VARCHAR(100) | Who made the edit |
edited_at | TIMESTAMPTZ | Edit timestamp |
Edit types: line_modified, line_added, line_removed, total_override, deal_amount_override
contract_rate_rules
Per-application billing rates and SLA multipliers.
| Column | Type | Default | Description |
|---|---|---|---|
id | UUID | Primary key | |
application_key | VARCHAR(50) | Application identifier | |
base_hourly_rate | DECIMAL(15,2) | Rate per hour | |
monthly_limit_hours | DECIMAL(10,2) | SUP monthly limit | |
sla_tier | VARCHAR(20) | 'standard' | SLA tier name |
coverage_type | VARCHAR(20) | 'business_hours' | Coverage type |
overtime_multiplier | DECIMAL(5,2) | 1.0 | Overtime rate multiplier |
p1_p3_multiplier | DECIMAL(5,2) | 1.0 | Critical incident multiplier |
off_hours_multiplier | DECIMAL(5,2) | 1.0 | Off-hours multiplier |
p1_p3_off_hours_multiplier | DECIMAL(5,2) | 1.5 | Critical + off-hours |
business_hours_start | TIME | 09:00 | Business hours start |
business_hours_end | TIME | 18:00 | Business hours end |
business_hours_tz | VARCHAR(50) | Asia/Tashkent | Timezone |
weekend_days | INTEGER[] | 7 | Weekend day numbers |
effective_from | DATE | CURRENT_DATE | Rule start date |
effective_to | DATE | Rule end date (null = current) |
Unique constraint: (application_key, effective_from)
app_settings
Key-value configuration store with JSONB values.
| Column | Type | Description |
|---|---|---|
key | VARCHAR(100) | PK — Setting name |
value | JSONB | Setting value |
updated_at | TIMESTAMPTZ | Last update |
updated_by | VARCHAR(100) | Who updated |
Seed keys: company, bank, planfact, didox, invoice_defaults, telegram, r2, calculator
client_invoice_config
Per-client invoice defaults, legal text template, and invoice numbering counter.
| Column | Type | Constraints | Description |
|---|---|---|---|
application_key | VARCHAR(20) | PK | Client application key |
default_show_task_list | BOOLEAN | NOT NULL, DEFAULT TRUE | Default: show task rows |
default_show_tracked_time | BOOLEAN | NOT NULL, DEFAULT TRUE | Default: show time column |
default_show_task_pricing | BOOLEAN | NOT NULL, DEFAULT FALSE | Default: show pricing column |
default_show_overtime | BOOLEAN | NOT NULL, DEFAULT TRUE | Default: show overtime row |
default_legal_text | TEXT | Legal text template for this client | |
last_invoice_number | INT | Atomic counter for per-client numbering | |
created_at | TIMESTAMPTZ | NOT NULL, DEFAULT NOW() | Created timestamp |
updated_at | TIMESTAMPTZ | NOT NULL, DEFAULT NOW() | Last update timestamp |
When an invoice is generated, the display toggle defaults and legal text are copied from this table. The last_invoice_number is atomically incremented using SELECT ... FOR UPDATE within the generation transaction to prevent concurrent collisions.
project_classifications
Classifies projects as billable, internal, or overhead.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key |
project_label | VARCHAR(100) | UNIQUE — Project label |
classification | VARCHAR(20) | billable / internal / overhead / excluded |
notes | VARCHAR(500) | Optional notes |
employee_exclusions
Employees excluded from billing.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key |
account_id | VARCHAR(100) | UNIQUE — Jira account ID |
exclusion_type | VARCHAR(20) | overhead_only / excluded |
reason | VARCHAR(500) | Reason for exclusion |
calculator_snapshots
Saved pricing calculator results.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key |
application_key | VARCHAR(20) | Application |
snapshot_type | VARCHAR(20) | client_margin / quote / what_if |
input_params | JSONB | Calculator inputs |
results | JSONB | Calculated results |
created_by | VARCHAR(100) | Creator |
created_at | TIMESTAMPTZ | Timestamp |