Skip to main content

Finance Data Ingest Operations

Overview​

Two ingest pipelines import NDIS financial CSV data into the database:

  1. Billing History -- NDIA payment approval records (what was paid)
  2. Payment Request History -- outbound bulk claim submissions (what was requested)

Both follow the same workflow: drop CSVs in the ingest folder, trigger ingest from the admin UI, files get archived after processing.

Folder Structure​

admin_drive/
├── Ingest/Finance/
│   ├── Billing/              ← Drop billing CSVs here
│   └── Payment Requests/     ← Drop payment request CSVs here
│
└── Finance/Ingested/
    ├── Billing Archive/      ← Processed billing files moved here
    │   ├── 2026-04-03/
    │   └── 2026-04-03(1)/    ← Multiple runs same day
    └── Payment Request Archive/
        └── 2026-04-03/       ← Preserves subfolder structure

How to Ingest​

Billing History​

  1. Place NDIS billing export CSVs (from the NDIA portal) into admin_drive/Ingest/Finance/Billing/
  2. Navigate to Finance > Billing in the admin app
  3. Go to the Import section and click "Ingest from Server"
  4. Wait for completion (can take a few minutes for large batches)
  5. Files are automatically moved to Finance/Ingested/Billing Archive/YYYY-MM-DD/

Payment Requests​

  1. Place payment request CSVs into admin_drive/Ingest/Finance/Payment Requests/
    • Can include nested folders (by year, category, etc.)
    • Files named *2021_12-002K* are automatically skipped (legacy templates)
    • Error/response CSVs from the NDIA portal go in Errors/ subfolders
  2. Navigate to Finance > Payment Requests in the admin app
  3. Go to the Import tab and click "Ingest from Server"
  4. Wait for completion (5 minute timeout for large batches)
  5. Files are archived to Finance/Ingested/Payment Request Archive/YYYY-MM-DD/ preserving folder structure

CSV Formats​

Billing History (NDIA Bulk Payment Export)​

19 columns: ProviderBPNumber, ProviderABN, PaymentRequestNum, DateApproved, ClaimRef, ItemID, ItemQty, UnitPrice, AmountClaimed, GSTAmount, Amount, ParticipantBPNumber, ParticipantName, SuppSDate, SuppEDate, ServiceBookingID, GSTCode, BulkClaimRefID, ClearingNum

Payment Requests​

Two formats:

  • 13-column (2017-2021): RegistrationNumber, NDISNumber, SupportsDeliveredFrom, SupportsDeliveredTo, SupportNumber, ClaimReference, Quantity, Hours, UnitPrice, GSTCode, AuthorisedBy, ParticipantApproved, InKindFundingProgram
  • 16-column (2022+): adds ClaimType, CancellationReason, ABN of Support Provider

Error/Response CSVs (18-column): adds PaidTotalAmount, Payment Request Number, Participant Name, Capped Price, Payment Request Status, Error Message

Deduplication​

Both pipelines use data-level deduplication (not file-based):

  • Billing: composite key of PaymentRequestNum + ItemID + ServiceStartDate + ServiceEndDate
  • Payment Requests: composite key of NDISNumber + SupportNumber + SupportsDeliveredFrom + SupportsDeliveredTo + ClaimReference

This means:

  • Moving files between folders does NOT create duplicates
  • Re-ingesting the same data is safe (duplicates are skipped)
  • Files from different folders with the same data are correctly deduplicated
  • New, genuinely different data is always imported

Data Handling​

ScenarioBillingPayment Requests
Empty/padding rowsSilently skippedSilently skipped
Inverted dates (from > to)Auto-swappedAuto-swapped
Impossible dates (e.g. Apr 31)Skipped (null)Skipped (null)
Missing claim referenceUses PaymentRequestNum as fallbackGenerates synthetic ref from filename
Template files (2021_12-002K)N/AAutomatically skipped
Dollar signs in pricesN/AStripped automatically
Date format DD/MM/YYYYHandledHandled
Quoted CSV fields with commasHandledHandled

Database Schema​

Billing History (finance schema)​

  • billing_import_batches -- batch tracking
  • billing_raw_lines -- verbatim CSV rows (audit trail)
  • billing_lines -- normalised, typed, with derived fields
  • billing_item_catalog -- distinct item codes with stats
  • billing_participant_summary -- per-participant aggregates

Payment Requests (finance schema)​

  • pr_import_batches -- batch tracking
  • payment_request_lines -- normalised request data
  • payment_request_responses -- NDIA error/response data
  • v_pr_monthly -- monthly summary view
  • v_pr_by_participant -- participant summary view

File Naming Convention​

All system-generated files (reports, statements) follow:

YYYY-MM-DD Title [period].ext

Examples:

  • 2026-04-03 Billing Statement 2026-01-01_2026-03-31.pdf
  • 2026-04-03 Participant Revenue Report.pdf

Troubleshooting​

IssueCauseFix
"No CSV files found"Ingest folder is emptyPlace CSVs in the correct ingest folder
Timeout on frontendLarge batch exceeding timeoutIngest still runs server-side; check batch history
High error countData quality issues in source CSVsCheck server console for specific row errors
Duplicate data after re-ingestExpected for billing (soft dedup)Totals exclude duplicates via is_duplicate = false
"Billing directory not found"Ingest folder doesn't existCreate admin_drive/Ingest/Finance/Billing/

API Endpoints​

Billing History (/api/v1/billing-history/)​

  • POST /ingest -- trigger CSV ingest
  • GET /lines -- query billing lines (paginated, filterable)
  • GET /summary -- monthly summaries
  • GET /dashboard -- dashboard widget data
  • GET /batches -- import batch history
  • GET /participant/:id -- participant billing detail
  • GET /participant/:id/statement -- download PDF statement
  • POST /participant/:id/statement/save -- save statement to admin drive
  • GET /report/:type -- download PDF report
  • POST /report/:type/save -- save report to admin drive

Payment Requests (/api/v1/payment-requests/)​

  • POST /ingest -- trigger CSV ingest
  • GET /lines -- query request lines (paginated, filterable)
  • GET /summary -- monthly summaries
  • GET /dashboard -- dashboard stats
  • GET /batches -- import batch history
  • GET /responses -- NDIA error/response lines