Implement Tax Tracking and Stripe Tax Integration

[perigrin]

Problem Description

Registry currently lacks comprehensive tax handling capabilities needed for compliance with various jurisdictions. Organizations using Registry need to collect and remit appropriate taxes on their program fees, membership dues, and other charges.

ARCHITECTURAL DECISION: This feature should be integrated into the existing PriceOps infrastructure rather than implemented as a separate system.

Business Requirements

Tax Collection Needs

• Sales Tax: For program fees, materials, equipment rentals
• Use Tax: For digital services, online content
• Local Taxes: Municipal, county, and state-specific rates
• Exemptions: Non-profit status, educational exemptions, student discounts
• International: VAT, GST for global organizations

Compliance Requirements

• Accurate Calculation: Real-time tax rate lookup based on location
• Proper Reporting: Generate tax reports for filing
• Record Keeping: Maintain detailed tax collection records
• Exemption Management: Handle tax-exempt customers and transactions
• Multi-Jurisdiction: Support organizations operating across multiple tax jurisdictions

Technical Implementation

Integration with PriceOps Infrastructure

Tax handling should be built into the existing pricing system components:

Existing PriceOps Components to Extend:

• pricing_plans - Add tax behavior configuration
• pricing_relationships - Extend to handle tax calculations
• payment_intents - Include tax amounts in payment processing
• installment_schedules - Apply tax to installment payments

Stripe Tax Integration within PriceOps

• Automatic Calculation: Use Stripe Tax for real-time tax calculation during pricing
• Rate Updates: Leverage Stripe's automated tax rate maintenance
• Filing Support: Optionally use Stripe Tax for automated filing
• Global Coverage: Support international tax requirements

Data Model Changes (Extending PriceOps)

-- Extend pricing plans with tax configuration
ALTER TABLE pricing_plans ADD COLUMN tax_behavior TEXT DEFAULT 'exclusive'; -- 'exclusive', 'inclusive', 'exempt'
ALTER TABLE pricing_plans ADD COLUMN tax_code TEXT; -- Stripe tax code for product classification

-- Tax configuration per tenant (integrates with pricing system)
CREATE TABLE tax_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    stripe_tax_enabled BOOLEAN DEFAULT false,
    tax_collection_enabled BOOLEAN DEFAULT false,
    default_tax_behavior TEXT DEFAULT 'exclusive', -- 'exclusive' or 'inclusive'
    auto_calculate_tax BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tax exemption management (integrates with customer/family system)
CREATE TABLE tax_exemptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    family_id UUID REFERENCES families(id), -- Links to existing family system
    exemption_type TEXT NOT NULL, -- 'non_profit', 'educational', 'government', 'resale'
    certificate_number TEXT,
    valid_from DATE,
    valid_until DATE,
    jurisdiction TEXT, -- 'federal', 'state', 'local'
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Extend payment_intents with tax information
ALTER TABLE payment_intents ADD COLUMN tax_amount_cents INTEGER DEFAULT 0;
ALTER TABLE payment_intents ADD COLUMN taxable_amount_cents INTEGER;
ALTER TABLE payment_intents ADD COLUMN stripe_tax_transaction_id TEXT;

-- Tax line items (detailed breakdown)
CREATE TABLE tax_line_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    payment_intent_id UUID REFERENCES payment_intents(id),
    installment_id UUID REFERENCES installments(id), -- For installment payments
    tax_amount_cents INTEGER NOT NULL,
    taxable_amount_cents INTEGER NOT NULL,
    tax_rate DECIMAL(5,4), -- e.g., 0.0875 for 8.75%
    jurisdiction TEXT,
    tax_type TEXT, -- 'sales_tax', 'vat', 'gst', 'local_tax'
    stripe_tax_rate_id TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Implementation Phases

Phase 1: Foundation (Week 1-2)

• Research and document tax requirements for target markets
• Set up Stripe Tax in test environment
• Extend pricing_plans table with tax behavior fields
• Create tax_settings table and admin interface
• Update PriceOps calculations to include tax-aware pricing

Phase 2: Core Functionality (Week 3-4)

• Implement customer address collection for tax calculation
• Add tax exemption management system integrated with families
• Update payment workflow to calculate tax during pricing resolution
• Extend installment system to handle tax on recurring payments
• Update price display logic for tax-inclusive/exclusive presentation

Phase 3: Advanced Features (Week 5-6)

• Integration with existing discount and pricing relationship systems
• Historical tax transaction reporting through existing admin dashboards
• Tax exemption validation and certificate management
• Automated tax filing setup (if using Stripe Tax filing)

Phase 4: Testing & Rollout (Week 7-8)

• Comprehensive testing across different tax scenarios with existing pricing plans
• Documentation for organizations on tax setup within PriceOps
• Gradual rollout to existing tenants
• Monitor and optimize tax calculation performance within pricing workflows

Technical Considerations

PriceOps Integration Points

Pricing Calculation Flow:

1. Base price from pricing_plan
2. Apply discounts via pricing_relationships
3. Calculate tax on final amount (respecting exemptions)
4. Present final price to customer

Payment Processing Integration:

• Tax amounts included in payment_intent creation
• Installment schedules include tax calculations
• Webhook handling for tax transaction recording

Address Collection:

• Billing Address: Required for accurate tax calculation (extend existing address fields)
• Service Address: May differ from billing for location-based programs
• Validation: Ensure addresses are valid for tax lookup

Performance Considerations

• Caching: Cache tax rates for common locations within pricing cache
• Async Processing: Handle tax calculation failures gracefully in pricing workflow
• Rate Limits: Manage Stripe Tax API rate limits alongside existing Stripe operations

Error Handling within PriceOps

• Fallback Rates: Use estimated rates if real-time lookup fails during pricing
• Manual Override: Allow admins to manually set tax amounts in pricing plans
• Audit Trail: Log all tax calculation decisions in existing audit system

Success Criteria

• Tax calculation seamlessly integrated into existing pricing workflows
• Organizations can configure tax collection through existing admin interface
• Tax is accurately calculated for all payment types (one-time, recurring, installments)
• Tax exemptions work with existing family/customer management
• Comprehensive tax reporting integrated with existing admin dashboards
• No performance degradation in existing pricing operations
• Backward compatibility with existing pricing plans

Dependencies

• Existing PriceOps System: Must understand current pricing architecture
• Stripe Tax Account: Organizations need Stripe Tax enabled
• Address Validation: Extend existing address handling
• Legal Review: Tax implementation should be reviewed by tax professionals
• Documentation: Update existing PriceOps documentation

Priority

High - Tax compliance is critical for many organizations and a blocker for some potential customers

Integration Notes

This feature extends the existing PriceOps infrastructure:

• Builds on proven pricing calculation patterns
• Leverages existing payment processing workflows
• Integrates with current admin interfaces
• Maintains consistency with existing data models
• Preserves performance characteristics of pricing system

Estimated Effort

6-8 weeks for full implementation including testing and documentation, with focus on PriceOps integration patterns and backward compatibility.