RA Import & Billing Upload Utility
Internal Documentation
This document describes how the RA Import and Billing Upload utility works,
how data flows through the system, and the design decisions behind it.
1. Purpose
This utility is designed to safely ingest financial data into MySQL so it can
later be used for reconciliation, reporting, and invoice generation.
Current scope:
- RA text imports (one doctor per import)
- Billing uploads via CSV or Excel (.xlsx)
- Patient name masking for privacy
- Preview before commit
Out of scope (by design):
- Invoice generation
- Accounting calculations
- Automation or attribution rules
2. Technology Stack
- Web Server: IIS
- Language: PHP
- Database: MySQL
- Excel Support: PhpSpreadsheet
- Session Storage: PHP Sessions
3. Folder Structure
/ra-import/
├── index.php (RA text import UI)
├── parse.php (RA parser)
├── csv_index.php (Billing upload UI)
├── billing_preview.php (Preview before commit)
├── billing_commit.php (Final DB insert)
├── db.php (Database connection)
├── vendor/ (Composer dependencies)
└── docs/
└── index.html (This documentation)
4. Database Tables
4.1 Doctors
Stores individuals (doctors) used to tag RA transactions.
| Column | Description |
|---|
| doctor_id | Primary key |
| doctor_name | Doctor’s name |
| email | Email address |
| oh_percent | Overhead percentage |
| is_active | Active flag |
| created_at | Creation timestamp |
4.2 RA Transactions
Stores raw RA data, tagged to a single doctor per import.
| Column | Description |
|---|
| transaction_type | Advance / Reduction |
| transaction_date | RA posting date |
| amount | Normalized amount |
| message | RA description |
| doctor_id | Foreign key to doctors |
4.3 Billing CSV Extracts
Stores billing data uploaded from CSV or Excel.
Patient names are masked before storage.
Full patient names are never stored in the database.
5. RA Text Import Flow
- User selects a doctor
- User pastes RA text
- System parses each line
- Dates and amounts are normalized
- All rows are saved with the selected doctor
One doctor per import is an intentional design choice to avoid ambiguity.
6. Billing Upload Flow (CSV / Excel)
- User uploads CSV or Excel file
- File is parsed but not saved
- Patient names are masked
- Preview table is shown
- User confirms import
- Data is inserted into the database
Nothing is written to the database until the user confirms.
7. Patient Name Masking
Patient names are converted to initials to reduce PHI exposure.
| Original | Stored |
|---|
| TAM, IRENE CHI WAI | T, I.C.W. |
| JAVIER, FILIPINA | J, F. |
Masking happens:
- Before preview
- Before database insert
8. Excel Support
Excel (.xlsx) is preferred over CSV because it preserves:
- Dates
- Decimal precision
- Column order
Excel support is provided using PhpSpreadsheet.
9. Preview → Commit Safety Model
- Preview data is stored temporarily in session
- Commit uses the same previewed data
- No partial or silent imports
10. Known Limitations
- No duplicate detection
- No provider-to-doctor mapping
- No invoice generation
- No reconciliation logic
11. Recommended Future Enhancements
- Duplicate detection
- Provider → doctor mapping
- RA ↔ Billing reconciliation
- Invoice generation
12. Design Philosophy
Ingestion should be boring, predictable, and auditable.
Intelligence belongs on top of clean data — not inside ingestion.