The following documentation outlines the

production.invoices table, a key component of the Veterinary Practice Data Platform. This table provides direct access to standardized, tested, and enriched invoice data, helping you to accelerate analytics and financial reporting.

Detailed Column Reference

Core Identifiers

Column	Type	Description	Examples	Business Logic
provet_id	STRING	Your unique practice identifier	"pcus_1"	Tenant isolation key - always filter by this for performance
invoice_id	BIGINT	Unique invoice identifier within practice	12345, 67890	System-generated, immutable once created
invoice_number	STRING	Business invoice number shown to clients	"1001-2024-00123", "DEPT2-240915-456"	Format: {department_id}-{date}-{sequence}. NULL until invoice is finalized (status=3)

Invoice Classification

Column	Type	Description	Examples	Business Logic
invoice_type	STRING	Derived invoice category	"consultation", "countersale", "credit_note"	Logic:CASE WHEN countersale=1 THEN 'Countersale' WHEN credit_note=0 THEN 'Consultation' WHEN credit_note=1 THEN 'Credit Note' ELSE 'Unknown' END
consultation_id	BIGINT	Related consultation reference	98765, NULL	Links to consultation record. NULL for countersales and credit notes
invoice_status	STRING	Current invoice state	"pending", "paid", "cancelled"	Mapping: 0=Draft, 1=Sent, 2=Open, 3=Finalized, 4=Invoicing, 99=Voided. Note: "pending"/"paid"/"cancelled" are simplified business terms

Financial Information

Column	Type	Description	Examples	Business Logic
total_incl_vat	DECIMAL	Total amount including VAT/tax	125.50, 89.99	Final billable amount including all taxes
total_excl_vat	DECIMAL	Total amount excluding VAT/tax	100.40, 74.99	Base amount before tax calculations
amount_due	DECIMAL	Outstanding balance remaining	125.50, 0.00, 25.00	Reduces as payments are applied. 0.00 = fully paid
currency_code	STRING	ISO 4217 currency code	"EUR", "USD", "GBP", "CAD"	Auto-mapped from department country
currency_name	STRING	Full currency name	"Euro", "US Dollar", "British Pound Sterling"	Human-readable currency description

Client & Veterinarian Information

Column	Type	Description	Examples	Business Logic
client_name	STRING	Client/payer name	"John Smith", "Pet Insurance Co", "Sarah Johnson"	From payer_name field in source data
supervising_veterinarian_name	STRING	Full name of supervising vet	"Dr. Emily Watson", "Michael Chen DVM", NULL	Logic: `first_name

Payment Method Details

Column	Type	Description	Examples	Business Logic
payment_method_type	STRING	Standardized payment category	"Card", "Cash or Check", "Digital Wallet", "Financing"	Auto-categorization logic: Card flag → "Card", Cash/Check keywords → "Cash or Check", CareCredit/Scratchpay → "Financing", etc.
payment_method_name	STRING	Original payment method name	"Visa Credit Card", "Cash", "CareCredit"	Raw name from practice management system
payment_method_name_standardized	STRING	Standardized payment name	"Visa", "Cash", "CareCredit"	Standardization logic: Pattern matching (e.g., '%visa%' → 'Visa', '%mastercard%' → 'Mastercard')

Department & Location Information

Column	Type	Description	Examples	Business Logic
department_id	BIGINT	Department/clinic identifier	1001, 2005	Links to specific clinic location
department_name	STRING	Department/clinic name	"Main Street Veterinary", "Emergency Clinic Downtown"	Business name for the location
department_timezone	STRING	IANA timezone identifier	"America/New_York", "Europe/London", "Australia/Sydney"	Used for local time conversions

Date Fields (Business Dates)

Column	Type	Description	Examples	Business Logic
created_date	DATE	Invoice creation date	2024-09-15, 2024-08-22	Date portion of creation timestamp
invoice_date	DATE	Official invoice date	2024-09-15, 2024-08-22	Business date shown on invoice
invoice_due_date	DATE	Payment due date	2024-10-15, 2024-09-22	When payment is expected
invoice_paid_date	DATE	Date payment was received	2024-09-20, NULL	NULL for unpaid invoices

UTC Timestamps (System Times)

Column	Type	Description	Examples	Business Logic
created_ts_utc	TIMESTAMP	Creation timestamp (UTC)	2024-09-15 14:30:25.123	System creation time in UTC
invoice_date_ts_utc	TIMESTAMP	Invoice timestamp (UTC)	2024-09-15 12:00:00.000	Invoice date converted to UTC
invoice_due_date_ts_utc	TIMESTAMP	Due timestamp (UTC)	2024-10-15 23:59:59.000	Due date converted to UTC
invoice_paid_date_ts_utc	TIMESTAMP	Payment timestamp (UTC)	2024-09-20 16:45:30.456, NULL	Payment received time in UTC

Local Timestamps (Department Timezone)

Column	Type	Description	Examples	Business Logic
created_ts_local	TIMESTAMP	Creation timestamp (local)	2024-09-15 10:30:25.123
invoice_date_ts_local	TIMESTAMP	Invoice timestamp (local)	2024-09-15 08:00:00.000
invoice_due_date_ts_local	TIMESTAMP	Due timestamp (local)	2024-10-15 19:59:59.000
invoice_paid_date_ts_local	TIMESTAMP	Payment timestamp (local)	2024-09-20 12:45:30.456, NULL

Common Query Patterns

Revenue Analysis

-- Monthly revenue by payment method
SELECT 
    DATE_TRUNC('month', invoice_date) as month,
    payment_method_type,
    COUNT(*) as invoice_count,
    SUM(total_incl_vat) as revenue,
    currency_code
FROM production.invoices 
WHERE provet_id = 'your_practice_id'
    AND invoice_status IN ('paid', 'finalized')
    AND invoice_date >= '2024-01-01'
GROUP BY 1, 2, 5
ORDER BY 1 DESC, 4 DESC;

Outstanding Receivables

-- Aging analysis of unpaid invoices
SELECT 
    CASE 
        WHEN DATEDIFF(CURRENT_DATE(), invoice_due_date) <= 0 THEN 'Current'
        WHEN DATEDIFF(CURRENT_DATE(), invoice_due_date) <= 30 THEN '1-30 days'
        WHEN DATEDIFF(CURRENT_DATE(), invoice_due_date) <= 60 THEN '31-60 days'
        ELSE 'Over 60 days'
    END as aging_bucket,
    COUNT(*) as invoice_count,
    SUM(amount_due) as total_outstanding,
    currency_code
FROM production.invoices
WHERE provet_id = 'your_practice_id'
    AND amount_due > 0
GROUP BY 1, 4
ORDER BY 1;

Veterinarian Performance

-- Top performing veterinarians by consultation revenue
SELECT 
    supervising_veterinarian_name,
    COUNT(DISTINCT invoice_id) as consultations,
    SUM(total_incl_vat) as total_revenue,
    AVG(total_incl_vat) as avg_consultation_value,
    currency_code
FROM production.invoices
WHERE provet_id = 'your_practice_id'
    AND invoice_type = 'consultation'
    AND supervising_veterinarian_name IS NOT NULL
    AND invoice_date >= DATE_SUB(CURRENT_DATE(), 30)
GROUP BY 1, 5
ORDER BY 3 DESC;

Important Usage Notes

Multi-Currency Handling

Always include currency_code when aggregating financial data across departments:

-- CORRECT: Group by currency
SELECT currency_code, SUM(total_incl_vat) as revenue
FROM production.invoices 
GROUP BY currency_code;-- INCORRECT: Mixing currencies
SELECT SUM(total_incl_vat) as revenue  -- May mix EUR + USD + GBP
FROM production.invoices;

Timezone Best Practices

Use *_ts_local fields for business reporting and user interfaces
Use *_ts_utc fields for system integration and data processing
Always consider the department timezone when interpreting local timestamps