Fix: Populate missing transaction_id and revenue fields in v2_receipts for Lastlink transactions

Problem

The v2_receipts table for Lastlink transactions is missing critical transaction data in the transaction_id and revenue fields, even though this data is available in the RudderStack BigQuery order_completed event.

Example: User 5298448

Status: ✅ FIXED (reference case)

Before:

• v2_receipts.transaction_id: NULL ❌
• v2_receipts.revenue: NULL ❌
• v2_receipts.currency: NULL ❌

After:

• v2_receipts.transaction_id: f1fd47be-11a0-4746-ba83-ddb06fa37baa ✅
• v2_receipts.revenue: 367.59 ✅
• v2_receipts.currency: BRL ✅

BigQuery Source (order_completed event):

timestamp:   2025-11-03T00:45:20.537Z
user_id:     5298448
product_id:  8d2125f6-aa5f-424d-af65-c91c6e9cc892
platform:    lastlink
revenue:     367.59 BRL
order_id:    f1fd47be-11a0-4746-ba83-ddb06fa37baa

Root Cause

The ApprovedStatusHandler in /app/services/lastlink/status_handlers/approved_status_handler.rb creates v2_receipts records but does not populate transaction_id, revenue, and currency fields from the webhook payload.

Affected Service: Lastlink callback handler
Payment Platform: Lastlink
Data Completeness: 77.8% (7/9 fields synced)

Impact

• Missing transaction IDs prevent audit trail correlation with Lastlink webhook events
• Missing revenue prevents billing verification against payment gateway
• Affects financial reconciliation and reporting

Solution

1. Update ApprovedStatusHandler Code

Ensure BillingService receives and stores transaction data:

# In ApprovedStatusHandler.handle()
# When creating v2_receipt, include:
{
  transaction_id: parsed_callback[:transaction_id],  # from Lastlink webhook
  revenue: parsed_callback[:net_value],              # from Lastlink webhook
  currency: parsed_callback[:currency]               # from Lastlink webhook
}

2. Backfill Historical Data (if needed)

For existing NULL records, recover data from BigQuery:

-- Find affected records
SELECT v2r.id, v2r.user_id, v2i.platform, v2r.created_at
FROM v2_receipts v2r
LEFT JOIN v2_invoices v2i ON v2r.invoice_id = v2i.id
WHERE v2i.platform = 'lastlink'
  AND (v2r.transaction_id IS NULL OR v2r.revenue IS NULL OR v2r.currency IS NULL)
ORDER BY v2r.created_at DESC;

3. Data Recovery Query

Correlate v2_receipts with BigQuery order_completed:

-- Query to recover transaction_id and revenue from BigQuery
SELECT
  timestamp,
  user_id,
  order_id as transaction_id,
  product_id,
  revenue,
  currency
FROM `rudderstack.order_completed`
WHERE user_id = 'USER_ID'
  AND DATE(timestamp) = 'DATE'
  AND platform = 'lastlink'
ORDER BY timestamp DESC
LIMIT 1;

Test Case

User: 5298448 (carlos.jlconsult@gmail.com)
Product: 12min Gold (Yearly) - UUID: 8d2125f6-aa5f-424d-af65-c91c6e9cc892
Transaction Date: 2025-11-03
Transaction ID: f1fd47be-11a0-4746-ba83-ddb06fa37baa
Revenue: 367.59 BRL

Verification Query:

SELECT
  v2r.id as receipt_id,
  v2r.invoice_id,
  v2r.user_id,
  v2r.product_id,
  v2r.transaction_id,
  v2r.revenue,
  v2r.currency,
  v2i.platform
FROM v2_receipts v2r
LEFT JOIN v2_invoices v2i ON v2r.invoice_id = v2i.id
WHERE v2r.user_id = 5298448;

Recommendations

1. ✅ Code Fix: Update ApprovedStatusHandler to extract and store transaction_id, revenue, currency from webhook
2. ⚠️ Audit: Query BigQuery to find all affected Lastlink transactions with NULL fields
3. ⚠️ Backfill: Create script to populate historical NULL values from order_completed events
4. ⚠️ Validation: Add test coverage to prevent regression

Related Documentation

• ApprovedStatusHandler: /app/services/lastlink/status_handlers/approved_status_handler.rb
• Lastlink Callback Parser: /app/services/lastlink/callback.rb
• Billing Database Reference: /billing/docs/DATABASE_REFERENCE.md
• RudderStack Integration: /billing/docs/RUDDERSTACK_DOCUMENTATION.md

Links

• RudderStack Dataset: rudderstack.order_completed
• Billing Service API: https://billing.12min.com/api/v2
• BigQuery Project: 12min-analytics