Skip to main content
The Reports API provides endpoints to generate various business intelligence reports including financial summaries, service statistics, and patient analytics.

​
Authentication

All report endpoints require:
  • Authentication with a valid session
  • Admin role (role === "admin")
Reports contain sensitive financial and business data. Only administrators have access to these endpoints.

​
Endpoints

​
Get Main Reports

curl -X GET "https://your-domain.com/api/reports" \
  -H "Cookie: session=your-session-token"
Retrieve comprehensive reports including monthly income, income by service, and patient distribution by branch.
​
success
boolean
Indicates if the request was successful
​
monthlyIncome
array
Monthly income summary
​
incomeByService
array
Income breakdown by service type
​
patientsByBranch
array
Patient distribution across branches
{
  "success": true,
  "monthlyIncome": [
    {
      "month": "2024-01",
      "total_income": 45000.00,
      "transaction_count": 123
    },
    {
      "month": "2024-02",
      "total_income": 52000.00,
      "transaction_count": 145
    },
    {
      "month": "2024-03",
      "total_income": 48500.00,
      "transaction_count": 138
    }
  ],
  "incomeByService": [
    {
      "service_name": "Cleaning",
      "total_income": 25000.00,
      "service_count": 250
    },
    {
      "service_name": "Root Canal",
      "total_income": 45000.00,
      "service_count": 45
    },
    {
      "service_name": "Orthodontics",
      "total_income": 75500.00,
      "service_count": 35
    }
  ],
  "patientsByBranch": [
    {
      "branch_id": 1,
      "branch_name": "Main Office",
      "patient_count": 450
    },
    {
      "branch_id": 2,
      "branch_name": "West Side Clinic",
      "patient_count": 320
    },
    {
      "branch_id": 3,
      "branch_name": "Downtown Branch",
      "patient_count": 280
    }
  ]
}

​
Get Debts Report

curl -X GET "https://your-domain.com/api/reports/debts" \
  -H "Cookie: session=your-session-token"
Retrieve a report of outstanding patient debts.
​
success
boolean
Indicates if the request was successful
​
debts
array
Array of debt records
{
  "success": true,
  "debts": [
    {
      "patient_id": 15,
      "patient_name": "John Doe",
      "medrecno": "P-2024-0015",
      "phone": "+1234567890",
      "email": "[email protected]",
      "total_debt": 1500.00,
      "oldest_debt_date": "2024-01-15",
      "days_overdue": 45,
      "branch_name": "Main Office"
    },
    {
      "patient_id": 23,
      "patient_name": "Jane Smith",
      "medrecno": "P-2024-0023",
      "phone": "+1987654321",
      "email": "[email protected]",
      "total_debt": 850.00,
      "oldest_debt_date": "2024-02-01",
      "days_overdue": 28,
      "branch_name": "West Side Clinic"
    },
    {
      "patient_id": 42,
      "patient_name": "Robert Johnson",
      "medrecno": "P-2024-0042",
      "phone": "+1555123456",
      "email": "[email protected]",
      "total_debt": 2250.00,
      "oldest_debt_date": "2023-12-20",
      "days_overdue": 70,
      "branch_name": "Main Office"
    }
  ]
}

​
Report Types

​
Monthly Income Report

Tracks revenue over time to identify trends and seasonal patterns. Use Cases:
  • Financial planning and forecasting
  • Identifying high and low revenue periods
  • Year-over-year comparisons
  • Budget vs. actual analysis

​
Income by Service Report

Breaks down revenue by treatment or service type. Use Cases:
  • Identify most profitable services
  • Resource allocation decisions
  • Marketing focus for high-value services
  • Service mix optimization

​
Patients by Branch Report

Shows patient distribution across different clinic locations. Use Cases:
  • Capacity planning
  • Staff allocation
  • Marketing targeting by location
  • Branch performance comparison

​
Debts Report

Lists all outstanding patient balances. Use Cases:
  • Accounts receivable management
  • Collection follow-up
  • Cash flow forecasting
  • Aging analysis
  • Credit risk assessment

​
Common Use Cases

​
Dashboard Overview

Fetch all main reports for an admin dashboard: 
curl -X GET "https://your-domain.com/api/reports" \
  -H "Cookie: session=your-session-token"

​
Collections Management

Get list of patients with outstanding debts: 
curl -X GET "https://your-domain.com/api/reports/debts" \
  -H "Cookie: session=your-session-token"
Filter debts over 30 days client-side: 
const overdueDebts = debts.filter(d => d.days_overdue > 30);

​
High-Priority Collections

Identify debts over a certain threshold: 
const highValueDebts = debts.filter(d => d.total_debt > 1000);

​
Revenue Analysis

Analyze monthly income trends: 
const recentMonths = monthlyIncome.slice(-6); // Last 6 months
const avgMonthlyIncome = recentMonths.reduce((sum, m) => sum + m.total_income, 0) / 6;

​
Data Aggregation

All reports are generated using stored procedures that aggregate data from multiple tables:
  • sp_get_main_reports() - Generates monthly income, service income, and patient distribution
  • sp_get_debts_report() - Calculates outstanding balances and aging
These stored procedures ensure:
  • Performance - Optimized queries with proper indexing
  • Consistency - Single source of truth for calculations
  • Security - Business logic encapsulated at database level

​
Error Responses

{
  "message": "No autorizado"
}

​
Performance Considerations

​
Caching Recommendations

Reports can be expensive to generate. Consider:
  • Caching report data for 5-15 minutes
  • Using a separate caching layer (Redis, Memcached)
  • Implementing incremental updates for frequently accessed reports

​
Pagination

For large datasets (especially debts report), consider:
  • Client-side pagination for small datasets (less than 1000 records)
  • Server-side pagination for larger datasets
  • Filtering options to reduce payload size

​
Source Reference

API implementation can be found in:
  • src/routes/api/reports/+server.js - Main reports endpoint
  • src/routes/api/reports/debts/+server.js - Debts report endpoint
Stored procedures:
  • sp_get_main_reports() - Generates main reports
  • sp_get_debts_report() - Calculates outstanding debts

Build docs developers (and LLMs) love

Get started for freeTalk to us