Skip to content

Stripe Product Enrichment - Implementation Summary

✅ Completed: Minimal Implementation

Successfully implemented product enrichment for Stripe transactions to show actual product names instead of generic descriptions.

🗄️ Database Changes

New Columns Added to transactions Table

  • product_id (TEXT) - Stripe product ID
  • product_name (TEXT) - Human-readable product name
  • invoice_id (TEXT) - Associated invoice ID
  • subscription_id (TEXT) - Associated subscription ID (if recurring)

Migration: Automatic via SQLite ALTER TABLE (runs on first app start)

File Modified: common/stripe_revenue/database.py - Lines 45-68: Added column migrations - Lines 279-310: Updated store_transaction() to include new fields - Lines 1139-1142: Updated get_recent_transactions() to return new fields

🔄 Sync Logic Enhancement

Product Data Enrichment Function

Added _enrich_with_product_data() method to extract product information from Stripe invoices and subscriptions.

How It Works: 1. Checks if transaction has an associated invoice 2. Fetches invoice with expanded line items and products 3. Extracts product name from first line item 4. If subscription exists, fetches subscription details as fallback 5. Returns product_id, product_name, invoice_id, subscription_id

API Efficiency: Uses Stripe's expand parameter to minimize API calls - expand=['lines.data.price.product'] for invoices - expand=['items.data.price.product'] for subscriptions

File Modified: common/stripe_revenue/stripe_sync.py - Lines 162-232: New _enrich_with_product_data() method - Lines 259-260: Call enrichment in _convert_transaction() - Lines 281-284: Include product data in returned transaction dict

🎨 Transactions UI Page

New Features

Built complete transactions page with: - Product Column: Shows product name prominently in blue - Subscription Indicator: Icon for recurring subscriptions - Invoice Reference: Shows truncated invoice ID - Customer Info: Name and email display - Type Badges: Color-coded transaction types (charge, refund, fee) - Amount Formatting: Proper currency display with fees and net

File Modified: apps/public/tools/stripe/templates/stripe/transactions.html - Complete rebuild from placeholder to functional table - Responsive Bootstrap table with 8 columns - Empty state with sync prompt

Template Filter Added: apps/public/app.py - Lines 293-302: timestamp_to_date filter for Unix timestamp formatting

📊 What You'll See

Before Enrichment

Description: "Invoice payment"
Product: -

After Enrichment

Description: "Invoice payment"
              📄 in_1ABC123...
Product: Premium Membership
         🔄 Subscription

🧪 Testing Instructions

1. Start the App

cd apps/public
python app.py

2. Navigate to Transactions

  • Go to http://localhost:5001/stripe/transactions
  • You should see the new transactions table

3. Sync Some Data

  • Go to Dashboard
  • Click "Sync Transactions" for current month
  • Return to Transactions page

4. Verify Enrichment

Look for transactions with: - ✅ Product names displayed (if invoice-based) - ✅ Subscription indicators - ✅ Invoice IDs shown - ✅ Proper date formatting

🔍 What Gets Enriched

✅ Will Have Product Data

  • Invoice payments
  • Subscription charges
  • Recurring billing

❌ Won't Have Product Data

  • One-off card charges (no invoice)
  • Refunds (reference original charge)
  • Stripe fees
  • Adjustments

This is expected and normal!

🚀 Next Steps (Future Enhancements)

Phase 2: Product-Based Categorization

Add new rule types to Income Streams: - "If product name equals 'X'" → Stream Y - "If product name contains 'Course'" → Course Sales

Phase 3: Re-Enrichment Tool

Add button to re-enrich historical transactions: - Useful for backfilling old data - Progress indicator for large datasets

Phase 4: Multiple Products

Handle invoices with multiple line items: - Currently only captures first product - Could store as JSON array or comma-separated

📝 Technical Notes

Performance Considerations

  • Enrichment adds 1-2 API calls per invoice-based transaction
  • Uses expand to minimize round-trips
  • Errors are caught and logged (won't break sync)
  • Empty product data is gracefully handled

Database Compatibility

  • Uses SQLite ALTER TABLE for migrations
  • Backward compatible (new columns are nullable)
  • Existing transactions won't break
  • New syncs will automatically include product data

Multi-Tenant Safe

  • Works with per-tenant databases
  • Client-specific Stripe API keys
  • No cross-tenant data leakage

🐛 Troubleshooting

"Product column shows all dashes"

  • Check if transactions are invoice-based
  • Verify Stripe API key has read permissions
  • Look for errors in console during sync

"Sync is slower than before"

  • Expected: enrichment adds API calls
  • Only affects invoice-based transactions
  • Consider limiting to recent months

"Database migration errors"

  • Columns might already exist (safe to ignore)
  • Check console for "[DB Migration]" messages
  • Restart app to retry migrations

📦 Files Modified

  1. common/stripe_revenue/database.py - Database schema + queries
  2. common/stripe_revenue/stripe_sync.py - Enrichment logic
  3. apps/public/tools/stripe/templates/stripe/transactions.html - UI
  4. apps/public/app.py - Template filter

Total Changes: ~150 lines of code Estimated Time: 1.5 hours Status: ✅ Ready for testing