The complete history of all payment events associated with an invoice. Track payment amounts and payment timelines with granular detail

Key Features:

All payments recorded
Currency information

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
payment_id	BIGINT	Unique payment transaction identifier	78901, 234567	System-generated, immutable once created. Primary key with provet_id
invoice_id	BIGINT	Related invoice identifier	12345, 67890	Links to the invoice being paid. Multiple payments can reference same invoice
invoice_number	STRING	Business invoice number	"1001-2024-00123", "DEPT2-240915-456"	Human-readable invoice reference for reconciliation

Financial Information

Column	Type	Description	Examples	Business Logic
paid_amount	DECIMAL	Payment amount received	125.50, -45.00, 25.00	Positive for payments, negative for refunds. Can be partial payment
currency_code	STRING	ISO 4217 currency code	"EUR", "USD", "GBP", "CAD"	Inherited from invoice's department location
currency_name	STRING	Full currency name	"Euro", "US Dollar", "British Pound Sterling"	Human-readable currency description
payment_info	STRING	Payment notes/reference	"Check #1234", "Card ending 5678", "Insurance claim ABC123"	Free-text field for payment details, references, or notes

Payment Cancellation Logic

Column	Type	Description	Examples	Business Logic
cancels_payment_id	BIGINT	ID of cancelled payment (if reversal)	78900, NULL	Logic: When payment is reversed, new payment record created with negative amount and this field points to original payment_id. NULL for regular payments

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: Same as invoices table - Card flag → "Card", keywords → categories
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')

Snapshot & Tracking

Column	Type	Description	Examples	Business Logic
snapshot_date	DATE	Data snapshot date	2024-09-15, 2024-08-22	Shows when payment data was captured

UTC Timestamps (System Times)

Column	Type	Description	Examples	Business Logic
created_ts_utc	TIMESTAMP	System entry timestamp (UTC)	2024-09-18 09:15:30.456	When payment was entered into system - e.g., Monday when staff processes Friday's payment
date_added_ts_utc	TIMESTAMP	Transaction date (UTC)	2024-09-15 16:30:00.000	When payment actually occurred - e.g., Friday when customer paid

Local Timestamps (Department Timezone)

Column	Type	Description	Examples	Business Logic
created_ts_local	TIMESTAMP	System entry timestamp (local)	2024-09-18 05:15:30.456
date_added_ts_local	TIMESTAMP	Transaction timestamp (local)	2024-09-15 12:30:00.000

Common Query Patterns

Payment Collection Analysis

-- Daily payment collections with cancellation tracking
SELECT 
    snapshot_date,
    COUNT(DISTINCT payment_id) as total_payments,
    COUNT(DISTINCT CASE WHEN cancels_payment_id IS NOT NULL THEN payment_id END) as cancellations,
    SUM(CASE WHEN cancels_payment_id IS NULL THEN paid_amount ELSE 0 END) as net_collections,
    currency_code
FROM production.invoices_payments 
WHERE provet_id = 'your_practice_id'
    AND snapshot_date >= DATE_SUB(CURRENT_DATE(), 30)
GROUP BY 1, 5
ORDER BY 1 DESC;

Payment Method Performance

-- Payment method analysis with reversal rates
SELECT 
    payment_method_type,
    payment_method_name_standardized,
    COUNT(DISTINCT payment_id) as total_transactions,
    SUM(paid_amount) as gross_amount,
    SUM(CASE WHEN cancels_payment_id IS NULL THEN paid_amount ELSE 0 END) as net_amount,
    COUNT(DISTINCT CASE WHEN cancels_payment_id IS NOT NULL THEN payment_id END) as reversals,
    ROUND(COUNT(DISTINCT CASE WHEN cancels_payment_id IS NOT NULL THEN payment_id END) * 100.0 / 
          COUNT(DISTINCT payment_id), 2) as reversal_rate_pct
FROM production.invoices_payments
WHERE provet_id = 'your_practice_id'
    AND snapshot_date >= DATE_SUB(CURRENT_DATE(), 90)
GROUP BY 1, 2
ORDER BY net_amount DESC;