A standardized catalog of payment methods, classified by type (e.g., Card, Cash, Bank Transfer). Simplifies financial reporting and analysis of payment trends across all clinics

Key Features:

Standardized naming
Type categorization
Active status tracking

Detailed Column Reference

Core Identifiers

Column	Type	Description	Examples	Business Logic
provet_id	STRING	Your unique practice identifier	`"pcus_1"``
payment_method_id	BIGINT	Unique payment method identifier	1, 15, 42	System-generated, immutable once created. Primary key with provet_id

Payment Method Information

Column	Type	Description	Examples	Business Logic
payment_method_name	STRING	Original payment method name	"Visa Credit Card", "Cash", "CareCredit Financing"	Raw name as configured in practice management system
payment_method_type	STRING	Standardized payment category	"Card", "Cash or Check", "Financing", "Digital Wallet"	Auto-categorization logic: Uses is_card flag + pattern matching on name
payment_method_name_standardized	STRING	Standardized payment name	"Visa", "Cash", "CareCredit"	Standardization logic: Pattern matching (e.g., '%visa%' → 'Visa', '%carecredit%' → 'CareCredit')

Classification Flags

Column	Type	Description	Examples	Business Logic
is_active	BOOLEAN	Payment method active status	true, false	Controls whether method appears in payment selection. false = deprecated/disabled
is_card	BOOLEAN	Card payment indicator	true, false	Primary classifier:true for all card-based payments (credit, debit, gift cards)
is_custom	BOOLEAN	Custom payment method flag	true, false	true for practice-specific payment methods not in standard catalog
is_cashdesk_payments	BOOLEAN	Cash desk payment flag	true, false	true for methods available at physical cash desk/reception

Usage Control Flags

Column	Type	Description	Examples	Business Logic
in_invoices	BOOLEAN	Available for invoice payments	true, false	Controls if method can be used for regular invoice payments
in_prepayments	BOOLEAN	Available for prepayments	true, false	Controls if method can be used for advance payments/deposits
in_reports	BOOLEAN	Include in financial reports	true, false	Controls if transactions appear in standard financial reports
in_end_of_day	BOOLEAN	Include in end-of-day reports	true, false	Controls if method appears in daily cash reconciliation reports

Audit Information

Column	Type	Description	Examples	Business Logic
created	TIMESTAMP	Payment method creation timestamp	2024-01-15 10:30:00.000	When method was first configured in system
created_date	DATE	Payment method creation date	2024-01-15	Date portion of creation timestamp

Business Logic Deep Dive

Payment Method Type Categorization

The system uses a hierarchical approach to categorize payment methods:

1. Primary Classification (is_card flag):

WHEN is_card = true THEN 'Card'  -- Most reliable identifier

2. Pattern-Based Classification:

CASE
    -- Digital payment apps
    WHEN name ILIKE ANY ('%cashapp%', '%zelle%', '%venmo%', '%paypal%') THEN 'Digital Wallet'
    
    -- Physical payment types  
    WHEN name ILIKE ANY ('%cash%', '%check%', '%cheque%') THEN 'Cash or Check'
    
    -- Third-party financing
    WHEN name ILIKE ANY ('%carecredit%', '%scratchpay%', '%cherry%') THEN 'Financing'
    
    -- Bank transfers
    WHEN name ILIKE ANY ('%bank%', '%ach%', '%wire%', '%e-transfer%') THEN 'Bank Transfer'
    
    -- Insurance payments
    WHEN name ILIKE ANY ('%trupanion%', '%insurance%') THEN 'Insurance'
    
    -- Credits and write-offs
    WHEN name ILIKE ANY ('%coupon%', '%rebate%', '%credit%', '%write off%') THEN 'Credit / Write-Off'
    
    -- Internal processes
    WHEN name ILIKE ANY ('%payroll%', '%employee%', '%internal%') THEN 'Internal'
    
    -- Fallback categories
    WHEN is_custom = true THEN 'Custom'
    ELSE 'Other'
END

production.payment_methods