Music Reports Processing System Administrator Documentation

Overview

This document explains the business logic and process flow of the Music Distribution Reports Processing System. It is designed for administrators who need to understand what happens during the report processing workflow without requiring technical knowledge of the codebase.

System Purpose

The Music Distribution Reports Processing System automates the ingestion of royalty reports from various music distribution providers. The system:

1. Processes reports in CSV or Excel format
2. Matches reported tracks to our database records
3. Calculates user earnings and administrative commissions
4. Updates user and admin balances accordingly
5. Creates batch records for organizing payments

Report Processing Workflow

1. File Upload & Initial Validation

When an admin uploads a distribution report:

• The system validates that the file is either CSV or Excel format
• The system checks that a valid provider is specified
• The system verifies the provided sheet ID and actual GBP amount received
• The report record status is updated to “processing” in the database

2. Provider Mapping Configuration

• Each provider has a specific mapping configuration stored in the database
• This mapping defines which columns in the report correspond to our required fields:
  • ISRC (track identifier)
  • Barcode
  • Catalog Number
  • Sales dates
  • Country of sale
  • Store information
  • Quantity
  • Total amount
• The mapping also specifies which row contains the header information

3. Data Extraction & Validation

The system performs extensive validation before processing any data:

1. File Parsing: The file is parsed based on its format (CSV or Excel)
2. Header Identification: Headers are identified based on the provider’s configuration
3. Data Validation: Each row is validated to ensure:
   • ISRCs are in the correct format (12 characters)
   • Tracks with the ISRCs exist in our database
   • Associated releases exist for each track
   • Users associated with the tracks exist
   • Commission rates can be determined for each user

If validation fails, the system will:

• Organize errors by type
• Indicate which lines contain each error type
• Update the report status to “failed” with detailed error messages
• No partial processing occurs - either all rows pass validation or none are processed

4. Currency Conversion

• The system applies a conversion process for all monetary values
• When uploading reports, admins provide the actual GBP amount received
• The system calculates an effective conversion factor: effective_conversion_factor = actual_GBP_received / total_original_amount
• This ensures the sum of all converted amounts exactly matches the GBP amount actually received

5. Revenue Allocation & Commission Calculation

For each valid row in the report:

1. Track Identification: The system locates the track record based on ISRC
2. Ownership Determination: The system identifies:
   • The release associated with the track
   • The user who owns the track
   • Whether it’s an artist release or label release
3. Commission Calculation:
   • The commission rate is determined from:
     • Custom user metadata (if present)
     • User’s subscription plan (if no custom rate)
   • For positive amounts:
     • Admin commission = amount × commission_rate
     • User payout = amount - admin_commission
   • For negative amounts (refunds):
     • User amount is reduced by the full value
     • No commission is taken by admin for refunds

6. Batch Creation

• Tracks are organized into “batches” for payment processing
• Batches are created separately for:
  • Artist releases (grouped by artist)
  • Label releases (grouped by label)
• If a batch already exists for an artist/label, the system will use it
• Otherwise, a new batch with a unique ID is created

7. Record Creation

For each processed row, the system creates:

1. Distribution Report Record containing:
   • Track information (ISRC, barcode, etc.)
   • Sales information (dates, store, country)
   • Amount details (original amount, converted amount, admin commission)
   • Relationships to user, release, track, and batch
   • Metadata (original currency, effective conversion factor)

8. Balance Updates

After all rows are processed:

1. User Balance Updates:

   • For each affected user, calculates their total earnings from the report
   • Retrieves their current balance (if any)
   • Creates a new balance record with:
     • The earnings as “in” amount
     • Updated current balance
     • Reference to the report sheet
     • “music_distribution” as the source

2. Admin Balance Update:

   • Only processed if there are positive amounts (generating admin commission)
   • Retrieves current admin balance
   • Creates a new admin balance record with:
     • Total commissions as “in” amount
     • Updated current balance
     • Reference to the report sheet
     • “music_distribution” as the source

9. Final Status Update

• The system updates the report sheet status to “completed” if all processing succeeds
• If any errors occur during processing (after validation), the status is set to “failed” with error details

Error Handling & Reporting

The system provides detailed error reporting:

• Validation Errors: Grouped by error type with specific line numbers
• Processing Errors: Detailed information about where and why processing failed
• All errors are saved to the report record for admin review

Optimization Techniques

The system employs several optimization techniques:

1. Caching (Memoization):

   • Tracks, releases, users, and commission rates are cached after first lookup
   • Batches are reused when possible
   • Exchange rates are cached to avoid repeated API calls

2. Batch Processing:

   • All validation is done before any processing to avoid partial updates
   • User and admin balance updates are consolidated to minimize database operations

Administrative Controls

As an administrator, you can:

1. Monitor Report Status:

   • View reports in “queued”, “processing”, “completed”, or “failed” states
   • Review detailed error messages for failed imports

2. Manage Provider Mappings:

   • Configure how each provider’s report format maps to our system
   • Specify header rows and column mappings

3. View Processing Results:

   • See summary information about processed reports
   • Review total amounts, conversion factors, and record counts