Billing Reports, Commissions & Revenue Share Implementation Plan

Purpose: Comprehensive plan for implementing reports, commissions, and revenue share functionality in the new billing schema Based on: Legacy implementation in parts/billing and parts/clinic Target Schema: billing.* (with cross-domain dependencies on clinical.* and identity.*) Status: ✅ COMPLETED (Phases 1-5) | ⏳ PENDING (Phase 6: Specs)

Related: For how a facility's stakeholder payout is actually calculated — the P&L, the recognition bases, the carry-forward and the settlement gate — see stakeholder-payout-calculation.md. The end-user version of the same subject is ../../user/revenue-sharing-payouts.html. That model replaces the legacy parts/clinic stakeholder flow documented in 03-api-reference/stakeholders-revenue-apis.md.

Related: For who earns an invoice line (the line's doctor, else the encounter's participants), how rule and assignment dates are judged at the bill's issue date, and what Run now refuses, see commission-attribution.md.

Related: For vendor marketplace payments (multi-processor split-payment + commission collection on the Billing::Marketplace::* namespace), see ../marketplace/marketplace-multi-processor.md.

Related: For how a vendor sets up payouts, how a patient pays a vendor, every path that refunds a vendor order, refund requests, and the support runbooks, see vendor-payments-and-refunds.md.

Implementation Status
Executive Summary
Current System Analysis
Proposed Schema Design
Visual Schema Diagram
Key Changes Explained
Domain Structure
API Endpoints
Data Migration Strategy

Implementation Status

✅ Completed Phases (1-5)

Phase	Status	Files Created	Notes
Phase 1: Database Migration	✅ Complete	2 files	Main migration + commission assignments
Phase 2: Domain Foundation	✅ Complete	12 files	11 models + routes updated
Phase 3: Commission API	✅ Complete	29 files	Controllers, services, policies, blueprints
Phase 4: Revenue Share API	✅ Complete	15 files	Controllers, services, policies, blueprints
Phase 5: Reports & Exports API	✅ Complete	18 files	Controllers, services, policies, blueprints
Phase 6: Specs	⏳ Pending	0 files	Skipped per requirements

Migration Files Created

File	Description	Tables
`20260127100000_create_billing_reports_commissions_tables.rb`	Main migration with 9 tables + soft delete + CANCELLED status	All billing reports/commissions tables
`20260127110000_create_commission_assignments_table.rb`	Commission practitioner/staff assignments	billing.commission_assignments

Models Created (11 files)

Model	Table	Status
`Billing::CommissionRule`	`billing.commission_rules`	✅ with soft delete columns
`Billing::CommissionCalculation`	`billing.commission_calculations`	✅ with CANCELLED status
`Billing::CommissionSettlement`	`billing.commission_settlements`	✅
`Billing::CommissionWorkflow`	`billing.commission_workflows`	✅
`Billing::CommissionAssignment`	`billing.commission_assignments`	✅ BONUS - not in original plan
`Billing::RevenueShareConfig`	`billing.revenue_share_configs`	✅ with inline stakeholders
`Billing::RevenueShareStakeholder`	`billing.revenue_share_stakeholders`	✅ hierarchical support
`Billing::RevenueShareCalculation`	`billing.revenue_share_calculations`	✅
`Billing::RevenueShareDistribution`	`billing.revenue_share_distributions`	✅
`Billing::RevenueDistributionQueue`	`billing.revenue_distribution_queues`	✅ BONUS - separate migration
`Billing::ReportExport`	`billing.report_exports`	✅

Controllers Created (9 files)

Controller	Endpoints	Status
`Billing::CommissionRulesController`	CRUD + activate/deactivate	✅
`Billing::CommissionCalculationsController`	Index, show, update, bulk_update, export, approve	✅
`Billing::CommissionSettlementsController`	Index, show, generate, approve, pay, cancel	✅
`Billing::CommissionWorkflowsController`	Index, create, update, execute	✅
`Billing::CommissionDashboardController`	Index, analytics	✅
`Billing::RevenueShareConfigsController`	CRUD with inline stakeholders	✅
`Billing::RevenueShareCalculationsController`	Index, show, calculate, dashboard, approve	✅
`Billing::RevenueShareDistributionsController`	Index, show, approve, pay	✅
`Billing::PremiumReportsController`	All reports endpoints	✅

Services Created (31 files)

Commission Services (13 files):

CommissionRulesService - Rule CRUD with assignee management
CommissionCalculationService - Percentage/fixed calculations
TieredCommissionCalculationService - Tiered calculations (REVENUE_GENERATED/SERVICES)
IndexService - Paginated listing with filters
UpdateStatusService - Individual status updates
BulkUpdateService - Bulk status operations
ApproveService - Approval workflow
SettlementService - Settlement generation
WorkflowExecuteService - Workflow execution
OverviewService - Dashboard metrics
AnalyticsService - Grouped analytics
CommissionWorkflowsService - Workflow management
~~CalculateService~~ - Removed in PR #2511; it had no caller. CommissionCalculationService, run by a workflow, is the per-invoice path

Revenue Share Services (4 files):

RevenueShareConfigsService - Config CRUD with stakeholders
RevenueShare::CalculationService - Revenue share calculations
RevenueShare::DashboardService - Dashboard metrics

Report Services (8 files):

RevenueAnalysisService - Time-series revenue data
DashboardService - High-level KPIs
PeriodComparisonService - Period comparison
LocationComparisonService - Facility comparison
ConsolidatedService - Multi-facility overview
RevenueAnalysisExportService - Export revenue analysis
ComparativeExportService - Export comparative reports
ConsolidatedExportService - Export consolidated reports

Export Services (1 file):

Exports::CreateService - Async export creation

Blueprints Created (10 files)

Blueprint	Views	Status
`CommissionRuleBlueprint`	normal, detailed, fhir	✅
`CommissionCalculationBlueprint`	normal, detailed	✅
`CommissionSettlementBlueprint`	normal, detailed	✅
`CommissionWorkflowBlueprint`	normal, detailed	✅
`CommissionAssignmentBlueprint`	normal, detailed	✅
`RevenueShareConfigBlueprint`	normal, detailed, with_stakeholders, fhir	✅
`RevenueShareCalculationBlueprint`	normal, detailed, fhir	✅
`RevenueShareDistributionBlueprint`	normal, detailed, fhir	✅
`ReportExportBlueprint`	normal, detailed	✅

Policies Created (10 files)

Policy	Permissions	Status
`CommissionRulePolicy`	view_practice_finance, manage_practice_finance	✅
`CommissionCalculationPolicy`	view_practice_finance, manage_practice_finance	✅
`CommissionSettlementPolicy`	view_practice_finance, manage_practice_finance	✅
`CommissionWorkflowPolicy`	view_practice_finance, manage_practice_finance	✅
`RevenueShareConfigPolicy`	view_practice_finance, manage_practice_finance	✅
`RevenueShareCalculationPolicy`	view_practice_finance, manage_practice_finance	✅
`RevenueShareDistributionPolicy`	view_practice_finance, manage_practice_finance	✅
`ReportExportPolicy`	view_practice_finance, manage_practice_finance	✅

Background Jobs Created (2 files)

Job	Purpose	Status
`CommissionWorkflowJob`	Execute scheduled commission calculations	✅
`ReportExportJob`	Async PDF/Excel/CSV generation	✅

API Collections (.bru files) Created (74 files)

All API endpoints have corresponding Bruno HTTP client collections in kulcare-apis/Billing/:

Commission Rules (7 files)
Commission Calculations (5 files)
Commission Settlements (6 files)
Commission Workflows (8 files)
Commission Dashboard (2 files)
Revenue Share Configs (7 files)
Revenue Share Calculations (5 files)
Revenue Share Distributions (4 files)
Revenue Share Dashboard (1 file)
Reports (8 files)
Report Exports (4 files)

Executive Summary

What We Implemented

This plan covers the complete migration of three major functional areas to the new billing schema:

Area	Description	Files Implemented
Financial Reports	Revenue analysis, dashboard, comparative & consolidated reports	18 files
Commission System	Rules, calculations, workflows, settlements for practitioner compensation	29 files
Revenue Share System	Configuration, calculations, distributions for stakeholder revenue sharing	15 files
Report Exports	Async report generation with PDF/Excel/CSV export	6 files

Key Design Principles

Principle	Description
Domain-Driven	All billing logic lives in `billing.*` schema
Tenant Scoped	All tables have `tenant_id` with RLS policies
UUIDv7 Keys	Primary keys use UUIDv7 for distributed compatibility
FHIR-Aligned	Reports follow FHIR financial resource patterns
Audit Trail	Full audit trail via `created_by`, `approved_by` fields
Multi-Currency	Support INR, USD, AED with proper exchange rates

Effort Estimate: COMPLETED

Phase	Planned	Actual	Status
Phase 1: Database Migration	2-3 days	2 days	✅ Complete
Phase 2: Domain Foundation	3-4 days	3 days	✅ Complete
Phase 3: Commission API	5-7 days	6 days	✅ Complete
Phase 4: Revenue Share API	4-5 days	4 days	✅ Complete
Phase 5: Reports API	5-7 days	5 days	✅ Complete
Phase 6: Specs	5-7 days	-	⏳ Skipped per requirements

Total: ~20 days of development (115+ files created)

Business Value

Benefit	Impact
Unified Schema	All billing-related logic consolidated in `billing.*` domain - no more scattered code across `parts/billing` and `parts/clinic`
Better Data Model	UUIDv7 primary keys provide sortable, time-ordered IDs ideal for distributed systems and audit trails
Explicit Naming	Self-documenting foreign keys (`revenue_share_stakeholder_id` vs generic `stakeholder_id`) improve code readability and reduce onboarding time
Enhanced Maintainability	Clear separation of concerns with domain-driven architecture - easier to locate, test, and modify billing logic
Multi-Tenant Scalability	Built-in tenant isolation with RLS policies supports SaaS multi-tenancy from day one
FHIR Alignment	Reports follow FHIR financial resource patterns for future healthcare interoperability
Comprehensive Audit Trail	Full audit capability via `created_by`, `approved_by`, `cancelled_by` fields across all entities
Multi-Currency Ready	Native support for INR, USD, AED with proper exchange rate handling for international expansion
Soft Delete Support	Commission rules support soft delete with `deleted`, `deleted_at`, `deleted_by` columns
Enhanced Status Workflows	Commission calculations now support CANCELLED status

What's Included

✅ All Business Logic Preserved from Legacy System:

Commission Types (all three):

Percentage commissions (% of gross amount)
Fixed commissions (fixed amount per service)
Tiered commissions (sliding scale with base + percentage)

Tiered Commission Approaches:

REVENUE_GENERATED: Based on ALL clinic billed services (management-level)
SERVICES: Based only on ASSIGNED practitioner services (individual/team performance)

Commission Assignments (BONUS FEATURE):

Practitioner and staff assignment to commission rules
Override percentages and fixed amounts per assignee
Active/inactive assignment tracking with effective dates
Support for hierarchical assignment structures

Revenue Sharing:

Hierarchical stakeholder support (parent → child relationships)
Multiple stakeholder types: Platform, Clinic Owner, Investor, Partner
Percentage-based or fixed-amount distribution methods
Configurable distribution rules via JSONB
Inline stakeholder creation/update with configs

Workflow Automation:

Scheduled execution (daily, weekly, monthly)
Automatic commission calculations based on invoice items
Configurable execution times per facility timezone
Background job execution via Sidekiq

Approval Workflows:

Calculations: CALCULATED → APPROVED → PAID → CANCELLED
Settlements: pending → approved → paid → cancelled
Revenue Share: calculated → approved → processed

Financial Reports:

Revenue Analysis (time-series data with trends)
Dashboard (high-level KPIs: monthly revenue, pending items)
Period Comparison (compare two time periods with growth %)
Location Comparison (compare facilities performance)
Consolidated (multi-facility overview with revenue, appointments, patients)

Report Exports:

Async PDF/Excel/CSV generation
Export types: COMMISSION_CALCULATIONS, REVENUE_ANALYSIS, REVENUE_SHARE, CONSOLIDATED_REPORT
Status tracking: PENDING → PROCESSING → COMPLETED/FAILED

✅ Enhanced Features:

Tenant isolation with Row Level Security (RLS) policies
Multi-currency support (INR, USD, AED) with exchange rate handling
Visual schema diagram for easy reference
Explicit foreign key naming convention
Comprehensive API documentation (40+ endpoints)
BONUS: Commission assignment tracking (not in original plan)
BONUS: Revenue distribution queue for post-commission revenue tracking
BONUS: Soft delete support for commission rules
BONUS: Cancelled status for commission calculations

Current System Analysis

1. Financial Reports

Current Location: parts/billing/controllers/premium_reports_controller.rb

Report Type	Description	Key Metrics	Old Data Source
Revenue Analysis	Time-series revenue data	Total, avg/day, patients, trends	`clinic_patient_services.price`
Dashboard	High-level KPIs	Monthly revenue, pending items	Multiple tables
Period Comparison	Compare two time periods	Growth %, category breakdown	`clinic_patient_services`
Location Comparison	Compare facilities	Per-facility revenue	`clinic_patient_services`
Consolidated	Multi-facility overview	Revenue, appointments, patients	Multiple tables

2. Commission System

Current Locations:

Controllers: parts/billing/controllers/commission_*.rb, parts/clinic/controllers/commission_*.rb
Models: parts/billing/models/commission_*.rb, parts/clinic/models/commission_*.rb

Component	Description	Tables
Rules	Commission rule definitions	`clinic_commission_rules`, `commission_tiers`
Assignments	Practitioner/staff assignments	`commission_assignments`
Calculations	Per-service calculations	`commission_calculations`
Workflows	Scheduled calculation jobs	`commission_workflows`
Settlements	Batch payment settlements	`commission_settlements`
Dashboard	Overview & analytics	Aggregated from above

Commission Types:

Percentage: % of gross amount
Fixed: Fixed amount per service
Tiered: Sliding scale based on revenue bands

Current Locations:

Controllers: parts/billing/controllers/revenue_share_*.rb, parts/clinic/controllers/revenue_share_*.rb
Models: parts/billing/models/revenue_share_*.rb, parts/clinic/models/revenue_share_*.rb

Component	Description	Tables
Configs	Revenue share configurations	`clinic_revenue_share_configs`
Stakeholders	Stakeholder definitions	`clinic_revenue_stakeholders`
Calculations	Calculation batches	`revenue_share_calculations`
Distributions	Individual distributions	`revenue_share_distributions`

Stakeholder Types: Platform, Clinic Owner, Investor, Partner

4. Report Exports

Current Location: parts/billing/models/report_export.rb

Field	Type	Description
`clinic_id`	FK	Reference to clinics
`export_type`	String	COMMISSION_CALCULATIONS, COMMISSION_PAYOUT, REVENUE_ANALYSIS, etc.
`file_format`	String	PDF, EXCEL, CSV
`status`	String	PENDING, PROCESSING, COMPLETED, FAILED
`parameters`	JSONB	Filters and report parameters
`file_url`	String	S3 file location
`error_message`	Text	Error details if failed
`created_by_id`	FK	User who triggered export
`started_at`	DateTime	When processing started
`completed_at`	DateTime	When processing completed

Proposed Schema Design

New Tables Overview

#	Table	Purpose	Old Table
1	`billing.commission_rules`	Commission rule definitions	`clinic_commission_rules`
2	`billing.commission_assignments`	Practitioner/staff assignments	`commission_assignments` (BONUS)
3	`billing.commission_calculations`	Individual calculations	`commission_calculations`
4	`billing.commission_settlements`	Batch settlements	`commission_settlements`
5	`billing.commission_workflows`	Scheduled jobs	`commission_workflows`
6	`billing.revenue_share_configs`	Revenue share configs	`clinic_revenue_share_configs`
7	`billing.revenue_share_stakeholders`	Stakeholder definitions	`clinic_revenue_stakeholders`
8	`billing.revenue_share_calculations`	Calculation batches	`revenue_share_calculations`
9	`billing.revenue_share_distributions`	Individual distributions	`revenue_share_distributions`
10	`billing.revenue_distribution_queues`	Post-commission revenue	NEW (BONUS)
11	`billing.report_exports`	Async report exports	`report_exports`

Table Definitions

1. Commission Rules

CREATE TABLE billing.commission_rules (
  -- Primary Key & Tenant
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  tenant_id UUID NOT NULL REFERENCES identity.tenants(id),
  facility_id UUID REFERENCES identity.facilities(id),  -- NULL = global rule

  -- Identification
  name VARCHAR(255) NOT NULL,
  description TEXT,
  rule_type VARCHAR(50) NOT NULL,  -- 'percentage', 'fixed', 'tiered'

  -- Commission Values
  commission_percentage DECIMAL(5,2),
  fixed_amount DECIMAL(12,2),
  tier_config JSONB,

  -- Applicability
  applies_to_all BOOLEAN DEFAULT false,
  practitioner_ids UUID[],
  service_type_ids UUID[],
  effective_from DATE NOT NULL,
  effective_to DATE,

  -- Status
  is_active BOOLEAN DEFAULT true,

  -- Soft Delete
  deleted BOOLEAN DEFAULT false NOT NULL,
  deleted_at TIMESTAMPTZ(6),
  deleted_by UUID REFERENCES identity.users(id),

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID REFERENCES identity.users(id),

  CONSTRAINT chk_commission_values CHECK (
    (rule_type = 'percentage' AND commission_percentage IS NOT NULL)
    OR (rule_type = 'fixed' AND fixed_amount IS NOT NULL)
    OR (rule_type = 'tiered' AND tier_config IS NOT NULL)
  )
);

-- Indexes
CREATE INDEX idx_comm_rules_tenant_facility
  ON billing.commission_rules(tenant_id, facility_id);
CREATE INDEX idx_comm_rules_tenant_active
  ON billing.commission_rules(tenant_id, is_active)
  WHERE is_active = true;
CREATE INDEX idx_comm_rules_deleted
  ON billing.commission_rules(deleted);
CREATE INDEX idx_comm_rules_deleted_at
  ON billing.commission_rules(deleted_at);

Key Changes:

clinic_id → facility_id (references identity.facilities)
clinic_doctor_ids → practitioner_ids (references identity.practitioners)
basic_service_type_ids → service_type_ids (references invoice item types)
Added tenant_id for multi-tenancy
Added soft delete columns: deleted, deleted_at, deleted_by

2. Commission Calculations

CREATE TABLE billing.commission_calculations (
  -- Primary Key & Tenant
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  tenant_id UUID NOT NULL REFERENCES identity.tenants(id),
  facility_id UUID NOT NULL REFERENCES identity.facilities(id),

  -- Source References
  invoice_id UUID NOT NULL REFERENCES billing.invoices(id),
  invoice_item_id UUID REFERENCES billing.invoice_items(id),
  practitioner_id UUID NOT NULL REFERENCES identity.practitioners(id),
  commission_rule_id UUID REFERENCES billing.commission_rules(id),

  -- Calculation Details
  gross_amount DECIMAL(12,2) NOT NULL,
  commission_amount DECIMAL(12,2) NOT NULL,
  calculation_method VARCHAR(20) NOT NULL,

  -- Status Workflow
  status VARCHAR(20) NOT NULL DEFAULT 'CALCULATED',
  calculated_at TIMESTAMPTZ NOT NULL,
  approved_at TIMESTAMPTZ,
  approved_by UUID REFERENCES identity.users(id),
  paid_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,
  cancelled_by UUID REFERENCES identity.users(id),

  -- Settlement Reference
  commission_settlement_id UUID REFERENCES billing.commission_settlements(id),

  -- Metadata
  notes TEXT,
  metadata JSONB DEFAULT '{}',

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Key Changes:

clinic_id → facility_id
clinic_patient_service_id → invoice_item_id
clinic_doctor_id → practitioner_id
Added CANCELLED status: cancelled_at, cancelled_by
Added tenant_id

3. Commission Settlements

CREATE TABLE billing.commission_settlements (
  -- Primary Key & Tenant
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  tenant_id UUID NOT NULL REFERENCES identity.tenants(id),
  facility_id UUID NOT NULL REFERENCES identity.facilities(id),
  practitioner_id UUID NOT NULL REFERENCES identity.practitioners(id),

  -- Settlement Period
  settlement_period VARCHAR(7) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,

  -- Financial Summary
  total_commission DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_amount DECIMAL(12,2) NOT NULL,

  -- Status Workflow
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  approved_at TIMESTAMPTZ,
  approved_by UUID REFERENCES identity.users(id),
  paid_at TIMESTAMPTZ,
  payment_reference VARCHAR(255),
  cancelled_at TIMESTAMPTZ,
  cancelled_by UUID REFERENCES identity.users(id),
  cancellation_reason TEXT,

  -- Payment Transfer
  payment_transfer_id UUID,

  -- Metadata
  notes TEXT,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT chk_settlement_amounts CHECK (
    net_amount >= 0 AND net_amount <= (total_commission - total_deductions)
  )
);

Key Changes:

clinic_id → facility_id
clinic_doctor_id → practitioner_id
Added tenant_id
Added cancellation support: cancelled_at, cancelled_by, cancellation_reason

Visual Schema Diagram

┌─────────────────────────────────────────────────────────────────────────────────────┐
│                              BILLING SCHEMA - Reports & Commissions                    │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                      │
│  ┌─────────────────────────── COMMISSION SYSTEM ───────────────────────────┐        │
│  │                                                                           │        │
│  │  ┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐      │        │
│  │  │ commission_rules │───>│commission_assignments│                    │      │        │
│  │  └────────┬────────┘    └─────────────────┘                            │      │        │
│  │           │                                                             │      │        │
│  │           │                                                            ▼      │        │
│  │           │                    ┌─────────────────────────────────┐    │      │        │
│  │           └───────────────────<│ commission_calculations          │    │      │        │
│  │                                │ • invoice_item_id ────────┐     │    │      │        │
│  │                                │ • practitioner_id           │     │    │      │        │
│  │                                │ • commission_rule_id        │     │    │      │        │
│  │                                └────────────────────────────┼─────┘    │      │        │
│  │                                                         │            │      │        │
│  │                                                         ▼            │      │        │
│  │                                ┌─────────────────────────────────┐  │      │        │
│  │                                │ commission_settlements           │  │      │        │
│  │                                │ • practitioner_id               │  │      │        │
│  │                                └─────────────────────────────────┘  │      │        │
│  │                                                                           │        │
│  └───────────────────────────────────────────────────────────────────────────┘        │
│                                                                                      │
│  ┌────────────────────────── REVENUE SHARE SYSTEM ──────────────────────────┐       │
│  │                                                                           │       │
│  │  ┌──────────────────────────┐                                            │       │
│  │  │ revenue_share_configs    │                                            │       │
│  │  └────────────┬─────────────┘                                            │       │
│  │               │                                                           │       │
│  │               ▼                                                           │       │
│  │  ┌────────────────────────────────────┐                                  │       │
│  │  │ revenue_share_stakeholders          │                                  │       │
│  │  │ • parent_revenue_share_stakeholder_id (self-ref)                      │       │
│  │  │   └── allows hierarchical stakeholder trees                           │       │
│  │  └────────────┬─────────────────────────┘                                  │       │
│  │               │                                                            │       │
│  │               ▼                                                            │       │
│  │  ┌────────────────────────────────────┐    ┌────────────────────────────┐  │       │
│  │  │ revenue_share_calculations          │───>│ revenue_share_distributions  │  │       │
│  │  └────────────────────────────────────┘    │ • revenue_share_stakeholder_id│  │       │
│  │                                              └────────────────────────────┘  │       │
│  │                                                                           │       │
│  └───────────────────────────────────────────────────────────────────────────┘       │
│                                                                                      │
│  ┌──────────────────────────   REPORTS & EXPORTS   ──────────────────────────┐       │
│  │                                                                           │       │
│  │  ┌─────────────────┐                                                       │       │
│  │  │ report_exports  │───> Generated Files (PDF/Excel/CSV)                   │       │
│  │  │                 │       • REVENUE_ANALYSIS                               │       │
│  │  │                 │       • COMMISSION_CALCULATIONS                        │       │
│  │  │                 │       • REVENUE_SHARE                                  │       │
│  │  │                 │       • CONSOLIDATED_REPORT                            │       │
│  │  └─────────────────┘                                                       │       │
│  │                                                                           │       │
│  └───────────────────────────────────────────────────────────────────────────┘       │
│                                                                                      │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                              CROSS-DOMAIN REFERENCES                                   │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                      │
│  IDENTITY FACILITIES ◄─────────────────────┐                                       │
│  └── facility_id (all tables)               │                                       │
│                                              │                                       │
│  IDENTITY PRACTITIONERS ◄───────────────────┤                                       │
│  └── practitioner_id (commission tables)     │                                       │
│                                              │                                       │
│  IDENTITY USERS ─────────────────────────────┤                                       │
│  └── created_by, approved_by, cancelled_by, deleted_by (audit fields)             │
│                                              │                                       │
│  BILLING INVOICES ───────────────────────────┤                                       │
│  └── invoice_id (commission_calculations)    │                                       │
│                                              │                                       │
│  BILLING INVOICE ITEMS ──────────────────────┤                                       │
│  └── invoice_item_id (commission_calculations)                                      │
│                                                                                      │
└─────────────────────────────────────────────────────────────────────────────────────┘

COLUMN NAME CONVENTION:
───────────────────────────
Foreign keys follow: {referenced_table_singular}_id pattern

Examples:
• practitioner_id → REFERENCES identity.practitioners(id)
• facility_id → REFERENCES identity.facilities(id)
• invoice_item_id → REFERENCES billing.invoice_items(id)
• commission_settlement_id → REFERENCES billing.commission_settlements(id)
• revenue_share_stakeholder_id → REFERENCES billing.revenue_share_stakeholders(id)
• parent_revenue_share_stakeholder_id → REFERENCES billing.revenue_share_stakeholders(id) (self-ref)

Key Changes Explained

1. Association Updates

The primary change is updating foreign key references to point to the new schema tables:

Old Reference	New Reference	Domain
`clinic_id` → `clinics`	`facility_id` → `identity.facilities`	Identity
`clinic_doctor_id` → `clinic_doctors`	`practitioner_id` → `identity.practitioners`	Identity
`clinic_patient_service_id` → `clinic_patient_services`	`invoice_item_id` → `billing.invoice_items`	Billing
`billing_invoice_id` → `billing_invoices`	`invoice_id` → `billing.invoices`	Billing

2. Tenant Isolation

All tables now include:

tenant_id UUID NOT NULL REFERENCES identity.tenants(id)
Row Level Security (RLS) policies for automatic tenant filtering
Queries automatically scoped to current tenant

3. UUIDv7 Primary Keys

All tables use:

id UUID PRIMARY KEY DEFAULT uuid_generate_v7()
Provides sortable, time-ordered IDs
Better for distributed systems than UUIDv4

4. Column Preservation

All business logic columns remain identical:

Commission amounts, percentages, tiers
Status workflows (CALCULATED → APPROVED → PAID → CANCELLED)
Settlement periods, distributions
Export types, formats, statuses
All JSONB configuration structures

5. Soft Delete Support (NEW)

Commission rules table now includes:

deleted BOOLEAN DEFAULT false
deleted_at TIMESTAMPTZ(6)
deleted_by UUID REFERENCES identity.users(id)
Allows safe deletion without losing historical data

6. Enhanced Status Workflows (NEW)

Commission calculations now support:

CALCULATED → APPROVED → PAID
CALCULATED → CANCELLED (new)
Settlements support cancellation with cancelled_at, cancelled_by, cancellation_reason

Domain Structure

backend/domains/billing/
├── models/
│   ├── base_record.rb (existing)
│   ├── invoice.rb (existing)
│   ├── invoice_item.rb (existing)
│   ├── payment.rb (existing)
│   ├── payment_allocation.rb (existing)
│   ├── commission_rule.rb ✅
│   ├── commission_assignment.rb ✅ (BONUS - not in original plan)
│   ├── commission_calculation.rb ✅
│   ├── commission_settlement.rb ✅
│   ├── commission_workflow.rb ✅
│   ├── revenue_share_config.rb ✅
│   ├── revenue_share_stakeholder.rb ✅
│   ├── revenue_share_calculation.rb ✅
│   ├── revenue_share_distribution.rb ✅
│   ├── revenue_distribution_queue.rb ✅ (BONUS - separate migration)
│   └── report_export.rb ✅
│
├── blueprints/
│   ├── invoice_blueprint.rb (existing)
│   ├── invoice_item_blueprint.rb (existing)
│   ├── payment_blueprint.rb (existing)
│   ├── payment_allocation_blueprint.rb (existing)
│   ├── commission_rule_blueprint.rb ✅
│   ├── commission_calculation_blueprint.rb ✅
│   ├── commission_settlement_blueprint.rb ✅
│   ├── commission_workflow_blueprint.rb ✅
│   ├── commission_assignment_blueprint.rb ✅
│   ├── revenue_share_config_blueprint.rb ✅
│   ├── revenue_share_calculation_blueprint.rb ✅
│   ├── revenue_share_distribution_blueprint.rb ✅
│   └── report_export_blueprint.rb ✅
│
├── controllers/
│   ├── base_controller.rb (existing)
│   ├── invoices_controller.rb (existing)
│   ├── invoice_items_controller.rb (existing)
│   ├── payments_controller.rb (existing)
│   ├── payment_allocations_controller.rb (existing)
│   ├── commission_rules_controller.rb ✅
│   ├── commission_calculations_controller.rb ✅
│   ├── commission_settlements_controller.rb ✅
│   ├── commission_workflows_controller.rb ✅
│   ├── commission_dashboard_controller.rb ✅
│   ├── revenue_share_configs_controller.rb ✅
│   ├── revenue_share_calculations_controller.rb ✅
│   ├── revenue_share_distributions_controller.rb ✅
│   └── premium_reports_controller.rb ✅ (handles all reports)
│
├── services/
│   ├── base_service.rb (existing)
│   ├── invoices/ (existing)
│   ├── payments/ (existing)
│   ├── commissions/
│   │   ├── commission_rules_service.rb ✅
│   │   ├── commission_calculation_service.rb ✅
│   │   ├── tiered_commission_calculation_service.rb ✅
│   │   ├── approve_service.rb ✅
│   │   ├── bulk_update_service.rb ✅
│   │   ├── update_status_service.rb ✅
│   │   ├── settlement_service.rb ✅
│   │   ├── index_service.rb ✅
│   │   ├── workflow_execute_service.rb ✅
│   │   ├── commission_workflows_service.rb ✅
│   │   ├── overview_service.rb ✅
│   │   └── analytics_service.rb ✅
│   ├── revenue_share/
│   │   ├── revenue_share_configs_service.rb ✅
│   │   ├── calculation_service.rb ✅
│   │   └── dashboard_service.rb ✅
│   ├── reports/
│   │   ├── revenue_analysis_service.rb ✅
│   │   ├── revenue_analysis_export_service.rb ✅
│   │   ├── dashboard_service.rb ✅
│   │   ├── period_comparison_service.rb ✅
│   │   ├── location_comparison_service.rb ✅
│   │   ├── consolidated_service.rb ✅
│   │   ├── comparative_export_service.rb ✅
│   │   └── consolidated_export_service.rb ✅
│   └── exports/
│       └── create_service.rb ✅
│
├── policies/
│   ├── invoice_policy.rb (existing)
│   ├── invoice_item_policy.rb (existing)
│   ├── payment_policy.rb (existing)
│   ├── payment_allocation_policy.rb (existing)
│   ├── commission_rule_policy.rb ✅
│   ├── commission_calculation_policy.rb ✅
│   ├── commission_settlement_policy.rb ✅
│   ├── commission_workflow_policy.rb ✅
│   ├── revenue_share_config_policy.rb ✅
│   ├── revenue_share_calculation_policy.rb ✅
│   ├── revenue_share_distribution_policy.rb ✅
│   └── report_export_policy.rb ✅
│
├── routes.rb (existing - extended)
├── jobs/ (NEW - background jobs)
│   ├── commission_workflow_job.rb ✅
│   └── report_export_job.rb ✅
│
└── spec/ (existing - extended)

API Endpoints

Commission Rules

GET    /billing/commission_rules
POST   /billing/commission_rules
GET    /billing/commission_rules/:id
PATCH  /billing/commission_rules/:id
DELETE /billing/commission_rules/:id
POST   /billing/commission_rules/:id/activate
POST   /billing/commission_rules/:id/deactivate

Commission Calculations

GET    /billing/commission_calculations
GET    /billing/commission_calculations/:id
PATCH  /billing/commission_calculations/:id
POST   /billing/commission_calculations/bulk_update
POST   /billing/commission_calculations/export

Commission Settlements

GET    /billing/commission_settlements
POST   /billing/commission_settlements/generate
GET    /billing/commission_settlements/:id
PATCH  /billing/commission_settlements/:id/approve
POST   /billing/commission_settlements/:id/pay
POST   /billing/commission_settlements/:id/cancel

Commission Dashboard

GET    /billing/commission_dashboard
GET    /billing/commission_dashboard/analytics

Commission Workflows

GET    /billing/commission_workflows
POST   /billing/commission_workflows
GET    /billing/commission_workflows/:id
PATCH  /billing/commission_workflows/:id
POST   /billing/commission_workflows/:id/execute

GET    /billing/revenue_share_configs
POST   /billing/revenue_share_configs
GET    /billing/revenue_share_configs/:id
PATCH  /billing/revenue_share_configs/:id
DELETE /billing/revenue_share_configs/:id

GET    /billing/revenue_share_calculations
POST   /billing/revenue_share_calculations/calculate
GET    /billing/revenue_share_calculations/dashboard
GET    /billing/revenue_share_calculations/:id
PATCH  /billing/revenue_share_calculations/:id/approve

GET    /billing/revenue_share_distributions
GET    /billing/revenue_share_distributions/:id
PATCH  /billing/revenue_share_distributions/:id/approve
POST   /billing/revenue_share_distributions/:id/pay

Reports

GET    /billing/premium_reports/revenue_analysis
POST   /billing/premium_reports/export_revenue_analysis
GET    /billing/premium_reports/dashboard
GET    /billing/premium_reports/period_comparison
GET    /billing/premium_reports/location_comparison
POST   /billing/premium_reports/export_comparative
GET    /billing/premium_reports/consolidated
POST   /billing/premium_reports/export_consolidated

Report Exports

GET    /billing/report_exports
POST   /billing/report_exports
GET    /billing/report_exports/:id
GET    /billing/report_exports/:id/download

Data Migration Strategy

Mapping Table

Old Table	New Table	Primary Changes
`clinic_commission_rules`	`billing.commission_rules`	`clinic_id` → `facility_id`, `clinic_doctor_ids` → `practitioner_ids`, added soft delete
`commission_assignments`	`billing.commission_assignments`	`clinic_id` → `facility_id`, `clinic_doctor_id` → `practitioner_id`
`commission_calculations`	`billing.commission_calculations`	`clinic_id` → `facility_id`, `clinic_patient_service_id` → `invoice_item_id`, `clinic_doctor_id` → `practitioner_id`, added cancellation support
`commission_settlements`	`billing.commission_settlements`	`clinic_id` → `facility_id`, `clinic_doctor_id` → `practitioner_id`, added cancellation support
`commission_workflows`	`billing.commission_workflows`	`clinic_id` → `facility_id`
`clinic_revenue_share_configs`	`billing.revenue_share_configs`	`clinic_id` → `facility_id`
`clinic_revenue_stakeholders`	`billing.revenue_share_stakeholders`	`clinic_id` → `facility_id`, `parent_stakeholder_id` → `parent_revenue_share_stakeholder_id`
`revenue_share_calculations`	`billing.revenue_share_calculations`	`clinic_id` → `facility_id`, `config_id` → `revenue_share_config_id`
`revenue_share_distributions`	`billing.revenue_share_distributions`	`clinic_id` → `facility_id`, `calculation_id` → `revenue_share_calculation_id`, `stakeholder_id` → `revenue_share_stakeholder_id`
`report_exports`	`billing.report_exports`	`clinic_id` → `facility_id`
`clinics`	`identity.facilities`	Migrated separately
`clinic_doctors`	`identity.practitioners`	Migrated separately

Migration Approach

Prerequisites: Ensure identity.facilities and identity.practitioners are migrated
Create New Tables: Run the new migration
Migrate Data: Copy data from old tables to new tables with updated references
Verify: Compare counts and sums between old and new
Deploy Cutover: Switch application to use new tables

Summary

Files Created: 115+ files

Category	Planned	Actual	Status
Migration	1	2	✅ Complete
Models	9	11	✅ Complete (2 bonus)
Blueprints	12	10	✅ Complete
Controllers	10	9	✅ Complete
Policies	9	10	✅ Complete
Services	40	31	✅ Complete
Background Jobs	0	2	✅ Complete (bonus)
API Collections (.bru)	0	74	✅ Complete
Specs	44	0	⏳ Skipped per requirements

Effort Estimate: COMPLETED

Dependencies

Clinical schema (patients, encounters) ✓ (already implemented)
Identity schema (facilities, practitioners) ✓ (already implemented)
Base billing tables (invoices, invoice_items, payments) ✓ (already implemented)

Bonus Features Delivered

Beyond the original plan, we also implemented:

Commission Assignment Tracking - Separate table with many-to-many relationship between rules and practitioners/staff
Revenue Distribution Queue - Tracks remaining revenue after commission deductions
Soft Delete Support - Commission rules can be soft-deleted
Cancellation Workflows - Both calculations and settlements can be cancelled
Inline Stakeholder Management - Revenue share configs handle stakeholders inline (no separate endpoint needed)
Background Job Support - Dedicated jobs for workflows and export generation
Enhanced Validations - 100% stakeholder percentage checks, uniqueness validations
Hierarchical Stakeholders - Parent-child relationships for complex revenue sharing
Comprehensive API Documentation - 74 .bru files for Bruno HTTP client

Billing Reports, Commissions & Revenue Share Implementation Plan

Table of ContentsCopy Anchor Link

Implementation StatusCopy Anchor Link

✅ Completed Phases (1-5)Copy Anchor Link

Migration Files CreatedCopy Anchor Link

Models Created (11 files)Copy Anchor Link

Controllers Created (9 files)Copy Anchor Link

Services Created (31 files)Copy Anchor Link

Blueprints Created (10 files)Copy Anchor Link

Policies Created (10 files)Copy Anchor Link

Background Jobs Created (2 files)Copy Anchor Link

API Collections (.bru files) Created (74 files)Copy Anchor Link

Executive SummaryCopy Anchor Link

What We ImplementedCopy Anchor Link

Key Design PrinciplesCopy Anchor Link

Effort Estimate: COMPLETEDCopy Anchor Link

Business ValueCopy Anchor Link

What's IncludedCopy Anchor Link

Current System AnalysisCopy Anchor Link

1. Financial ReportsCopy Anchor Link

2. Commission SystemCopy Anchor Link

3. Revenue Share SystemCopy Anchor Link

4. Report ExportsCopy Anchor Link

Proposed Schema DesignCopy Anchor Link

New Tables OverviewCopy Anchor Link

Table DefinitionsCopy Anchor Link

1. Commission RulesCopy Anchor Link

2. Commission CalculationsCopy Anchor Link

3. Commission SettlementsCopy Anchor Link

Visual Schema DiagramCopy Anchor Link

Key Changes ExplainedCopy Anchor Link

1. Association UpdatesCopy Anchor Link

2. Tenant IsolationCopy Anchor Link

3. UUIDv7 Primary KeysCopy Anchor Link

4. Column PreservationCopy Anchor Link

5. Soft Delete Support (NEW)Copy Anchor Link

6. Enhanced Status Workflows (NEW)Copy Anchor Link

Domain StructureCopy Anchor Link

API EndpointsCopy Anchor Link

Commission RulesCopy Anchor Link

Commission CalculationsCopy Anchor Link

Commission SettlementsCopy Anchor Link

Commission DashboardCopy Anchor Link

Commission WorkflowsCopy Anchor Link

Revenue ShareCopy Anchor Link

ReportsCopy Anchor Link

Report ExportsCopy Anchor Link

Data Migration StrategyCopy Anchor Link

Mapping TableCopy Anchor Link

Migration ApproachCopy Anchor Link

SummaryCopy Anchor Link

Files Created: 115+ filesCopy Anchor Link

Effort Estimate: COMPLETEDCopy Anchor Link

DependenciesCopy Anchor Link

Bonus Features DeliveredCopy Anchor Link

Approval ChecklistCopy Anchor Link

On this page

Table of Contents

Implementation Status

✅ Completed Phases (1-5)

Migration Files Created

Models Created (11 files)

Controllers Created (9 files)

Services Created (31 files)

Blueprints Created (10 files)

Policies Created (10 files)

Background Jobs Created (2 files)

API Collections (.bru files) Created (74 files)

Executive Summary

What We Implemented

Key Design Principles

Effort Estimate: COMPLETED

Business Value

What's Included

Current System Analysis

1. Financial Reports

2. Commission System

3. Revenue Share System

4. Report Exports

Proposed Schema Design

New Tables Overview

Table Definitions

1. Commission Rules

2. Commission Calculations

3. Commission Settlements

Visual Schema Diagram

Key Changes Explained

1. Association Updates

2. Tenant Isolation

3. UUIDv7 Primary Keys

4. Column Preservation

5. Soft Delete Support (NEW)

6. Enhanced Status Workflows (NEW)

Domain Structure

API Endpoints

Commission Rules

Commission Calculations

Commission Settlements

Commission Dashboard

Commission Workflows

Revenue Share

Reports

Report Exports

Data Migration Strategy

Mapping Table

Migration Approach

Summary

Files Created: 115+ files

Effort Estimate: COMPLETED

Dependencies

Bonus Features Delivered

Approval Checklist