# Financial Integrity and Production-Ready Refund Remediation Plan ## 1. Objective The objective is to make invoice, discount, payment, refund, reimbursement, expense, and reporting calculations consistent, auditable, concurrency-safe, and suitable for production. The completed system must guarantee that: * The same financial inputs always produce the same balance. * Historical invoices do not change because current configuration changes. * A payment, refund, or reimbursement cannot be processed twice. * Every financial write is atomic. * Every financial event has an immutable audit trail. * Reports use the same accounting rules as operational screens. * Invalid or unauthorized financial operations are rejected before any data is changed. ## 2. Release Policy The financial module must remain blocked from production until all Phase 0 requirements are complete. No refund should be paid from the application until: 1. Refund accounting signs are corrected. 2. Refund eligibility is calculated from the canonical ledger. 3. Refund rows are locked during payout. 4. Duplicate submission protection exists. 5. Broken and unsecured refund routes are repaired. 6. Critical refund tests pass. Manual operational review is not an acceptable substitute for these controls. --- # Phase 0: Stabilize and Prevent Further Damage ## 3. Freeze Risky Financial Operations Temporarily disable or restrict the following operations in production: * Refund payout * Reimbursement payout * Historical invoice recalculation * Paid reimbursement editing * Reimbursed expense editing * Manual invoice status changes * Repeated invoice generation Permit these operations only for an authorized financial administrator until remediation is complete. Add temporary logging around every financial write: * User ID * Request ID * Record ID * Old values * New values * Timestamp * IP address * Operation type ## 4. Back Up and Reconcile Existing Data Before applying schema or calculation changes: 1. Create a full database backup. 2. Export all invoices, payments, refunds, discounts, expenses, reimbursements, and purchase orders. 3. Calculate current invoice balances using both: * Existing production formulas * Corrected canonical formulas 4. Produce a reconciliation report identifying: * Incorrect invoice balances * Negative balances * Duplicate refunds * Refunds exceeding available credit * Discounts exceeding eligible charges * Reimbursements not matching expenses * Paid expenses that were later edited * Duplicate invoices * Invalid status values Do not automatically rewrite disputed historical values. Generate an exception list for financial review. --- # Phase 1: Establish One Canonical Financial Ledger ## 5. Define Canonical Accounting Rules Use the following definitions throughout the application. ### Gross charges ```text gross charges = frozen tuition lines + frozen event lines + signed additional adjustments ``` ### Applied discount ```text applied discount = minimum of: - valid requested discount - remaining eligible discount base ``` ### Net charges ```text net charges = gross charges - applied discount ``` ### Net cash received ```text net cash received = successful payments - completed cash refund payouts - completed payment reversals ``` ### Raw balance ```text raw balance = net charges - net cash received ``` ### Amount due ```text amount due = max(0, raw balance) ``` ### Customer credit ```text customer credit = max(0, -raw balance) ``` A completed cash refund must reduce customer credit. It must not increase it. ## 6. Separate Financial Concepts Create explicit transaction types instead of using one refund concept for unrelated operations. Required concepts: * Payment * Payment reversal * Credit memo * Discount * Cash refund * Refund payout * Reimbursement * Reimbursement reversal * Invoice adjustment Rules: * A credit memo reduces charges. * A cash refund reduces cash retained. * A payment reversal reverses a payment. * A reimbursement pays an expense and does not affect an invoice. * A discount reduces only an eligible charge base. ## 7. Create a Single Ledger Service Replace duplicated balance calculations with one service. Suggested responsibilities: ```text FinancialLedgerService - calculateInvoiceTotals(invoiceId) - calculateRefundableCredit(parentId, invoiceId) - calculatePaymentAllocation(invoiceId) - calculateDiscountEligibility(invoiceId, voucherId) - recalculateInvoiceProjection(invoiceId) - validateLedgerConsistency(invoiceId) ``` Controllers must not calculate balances directly. Controllers should: 1. Validate the request format. 2. Check authorization. 3. Call the domain service. 4. Return the service result. Remove duplicate formulas from: * Invoice controller * Refund controller * Discount controller * Financial reporting controller * Payment controller * Manual payment controller ## 8. Use Integer Minor Units Store and calculate money using integer cents. Examples: ```text $10.25 = 1025 cents $0.01 = 1 cent ``` Do not use floating-point arithmetic for: * Invoice totals * Discounts * Payments * Refunds * Reimbursements * Tax * Purchase-order totals Define one rounding policy for percentage calculations: ```text round half up to the nearest cent ``` Apply it exactly once per line or transaction according to documented rules. --- # Phase 2: Make Invoices Historically Immutable ## 9. Introduce Invoice Line Snapshots Create an immutable `invoice_lines` table. Suggested fields: ```text id invoice_id line_type source_type source_id description quantity unit_amount_cents line_amount_cents discount_eligible calculation_version metadata_json created_at ``` Possible line types: * Tuition * Event fee * Additional charge * Deduction * Credit memo * Manual adjustment When an invoice is issued, copy the calculated values into invoice lines. After issuance, invoice totals must be derived from invoice lines, not from: * Current enrollments * Current tuition settings * Current event configuration * Current student class assignment * Current refund deadlines ## 10. Stop Rewriting Issued Invoices Changes after invoice issuance must produce a new financial event: * Debit adjustment * Credit memo * Replacement invoice * Invoice cancellation and reissue Do not silently rebuild the original invoice. Define invoice lifecycle statuses: ```text draft issued partially_paid paid credited voided ``` Only draft invoices may have their original line items edited directly. ## 11. Enforce Invoice Uniqueness Choose and document the invoice identity rule: ```text one invoice per parent, school year, and semester ``` or another explicitly approved rule. Enforce it with a database unique index. Do not rely on: ```text SELECT existing invoice then INSERT ``` That sequence is race-prone without a unique constraint. --- # Phase 3: Rebuild Refund Logic ## 12. Define Refund Eligibility A refund request must reference a valid source. Supported refund sources should be explicit: * Invoice overpayment * Specific payment * Approved credit memo * Duplicate payment * Administrative correction A refund cannot be created from a free-form amount without a verified credit source. The maximum refundable amount must be: ```text minimum of: - verified available customer credit - remaining refundable amount from the source - requested amount ``` ## 13. Define Valid Payment Statuses Only payments in a final successful state should contribute to refundable credit. Suggested payment statuses: ```text pending successful failed voided reversed partially_refunded refunded chargeback ``` Include only `successful` and the non-refunded portion of `partially_refunded` payments. Exclude: * Pending * Failed * Voided * Reversed * Refunded * Chargeback ## 14. Create Immutable Refund Records Use separate tables for refund intent and refund payouts. ### Refund table ```text refunds - id - parent_id - invoice_id - source_type - source_id - approved_amount_cents - status - reason - requested_by - approved_by - created_at - approved_at ``` ### Refund payout table ```text refund_payouts - id - refund_id - amount_cents - payment_method - external_reference - idempotency_key - status - processed_by - processed_at - reversed_payout_id - created_at ``` Do not repeatedly overwrite a single `refund_paid_amount` field as the source of truth. Refund totals should be calculated from immutable payout rows. ## 15. Implement Transaction-Safe Refund Processing Refund payout flow: 1. Begin database transaction. 2. Lock the refund row using `FOR UPDATE`. 3. Lock the relevant invoice or credit-source rows. 4. Reload completed refund payouts. 5. Recalculate the remaining refundable amount. 6. Reject the request if the remaining amount is insufficient. 7. Validate the payment method. 8. Insert the immutable refund payout. 9. Update the refund aggregate status if needed. 10. Recalculate the invoice ledger projection. 11. Write an audit event. 12. Commit. Do not calculate the remaining refund amount before acquiring the lock. ## 16. Add Idempotency Every payout request must include or generate an idempotency key. Create a unique database constraint on: ```text refund_payouts.idempotency_key ``` Repeated submission with the same key must return the original payout result instead of creating a new payout. Recommended sources for the key: * Payment gateway event ID * Check-processing reference * Application-generated operation UUID Frontend button disabling is not idempotency. Humans double-click and networks retry because apparently distributed systems needed a sense of humor. ## 17. Validate Refund Payment Methods Define an allowlist: ```text cash check bank_transfer card_refund account_credit ``` Validation examples: * Check requires check number and issue date. * Bank transfer requires a transaction reference. * Card refund must reference the original card transaction. * Cash payout may require acknowledgement or receipt. * Account credit must not be treated as a cash payout. Reject unknown payment methods. ## 18. Add Refund Authorization Create explicit permissions: ```text refund.view refund.request refund.approve refund.pay refund.reverse refund.report ``` Recommended segregation of duties: * Requester cannot approve their own refund above a configured threshold. * Approver cannot mark a refund paid without payout evidence. * Refund reversal requires a separate permission. * Large refunds require dual approval. All refund routes must use explicit authentication and permission filters. ## 19. Repair Refund Routes Audit every refund route against actual controller method signatures. For each route: * Verify the method exists. * Verify URL arguments match method arguments. * Verify POST body values are read from the request, not expected as PHP method arguments. * Apply authentication. * Apply authorization. * Apply CSRF protection. * Use POST for state changes. * Remove obsolete routes. Add a route test that fails when a configured controller method does not exist. ## 20. Implement Refund Reversals Paid refund records should not be edited or deleted. A correction must create a reversal payout referencing the original payout. Required rules: * Original payout remains unchanged. * Reversal amount cannot exceed the original payout. * Reversed amount is included in net cash calculations. * Reversal requires reason, user, timestamp, and approval. --- # Phase 4: Repair Discounts ## 21. Centralize Discount Eligibility The ledger service must determine: * Eligible invoice lines * Voucher validity * Remaining voucher uses * Maximum allowed discount * Actual applied discount Voucher validity must include: * Active flag * Start date * End date * School year * Semester * Parent eligibility * Usage limits * Minimum charge conditions ## 22. Store Requested and Applied Amounts Separately Store: ```text requested_discount_cents applied_discount_cents eligible_base_cents ``` Reports must use the applied amount. Never report the raw requested amount as though it reduced the invoice. ## 23. Lock Voucher Usage Voucher application flow: 1. Begin transaction. 2. Lock voucher row. 3. Recheck validity. 4. Recheck remaining uses. 5. Lock invoice discount rows. 6. Calculate the eligible base. 7. Insert usage. 8. Update usage count. 9. Recalculate ledger. 10. Commit. Add a unique constraint preventing the same voucher from being applied twice to the same invoice unless explicitly supported. ## 24. Define Multiple-Discount Ordering Document the application order, for example: 1. Fixed credits 2. Fixed vouchers 3. Percentage discounts 4. Administrative adjustments Apply each discount against the remaining eligible base. Prevent total applied discounts from exceeding eligible charges. --- # Phase 5: Repair Additional Charges ## 25. Validate Invoice Ownership When creating a charge, load the invoice first. Derive from the invoice: * Parent * School year * Semester Do not trust redundant request fields. Reject charges when the supplied parent or accounting period conflicts with the invoice. ## 26. Standardize Charge Sign Storage Choose one representation. Recommended model: ```text amount_cents is always positive charge_type determines add or deduct ``` The ledger converts the type into a signed amount. Add database validation: ```text amount_cents >= 0 charge_type IN ('add', 'deduct') ``` ## 27. Complete the Pending-Charge Workflow Define transitions: ```text pending approved applied rejected voided ``` Provide an explicit command to apply an approved charge to an invoice. Record: * Applied invoice * Applied user * Applied time * Generated invoice line --- # Phase 6: Repair Expenses and Reimbursements ## 28. Make Paid Records Immutable Once an expense is reimbursed: * Amount cannot be edited. * Category cannot be edited. * Purchaser cannot be edited. * Accounting period cannot be edited. * Receipt cannot be replaced silently. Corrections require: 1. Reimbursement reversal 2. Expense correction record 3. New reimbursement The same rule applies to paid reimbursement records. ## 29. Validate Reimbursement Eligibility Before reimbursement: * Expense must exist. * Expense must be approved. * Expense must not be a donation. * Expense must not already be reimbursed. * Expense amount must be positive. * Recipient must match the approved purchaser unless an exception is authorized. * Accounting period must match. * Requested amount must not exceed the remaining reimbursable amount. ## 30. Make Reimbursement Creation Atomic Transaction flow: 1. Begin transaction. 2. Lock expense row. 3. Confirm eligibility. 4. Confirm no active reimbursement exists. 5. Insert reimbursement. 6. Link expense to reimbursement. 7. Write audit record. 8. Commit. Add a database unique constraint preventing multiple active reimbursements for one expense. ## 31. Repair Reimbursement Batches Batch processing must be all-or-nothing. Before closing a batch: * Validate every active item. * Lock the batch. * Lock every expense. * Confirm all items were processed. * Reject the entire batch if any item is invalid. * Confirm every batch item has a reimbursement record. * Close the batch only after successful completion. Never silently skip invalid items. --- # Phase 7: Repair Purchase Orders and Inventory ## 32. Validate Purchase-Order Values Add constraints: ```text quantity > 0 unit_cost_cents >= 0 received_quantity >= 0 received_quantity <= ordered_quantity ``` ## 33. Recalculate Receipt Completion from All Lines After receiving items: 1. Lock the purchase order. 2. Lock all purchase-order lines. 3. Apply receipt quantities. 4. Update inventory atomically. 5. Query every line. 6. Mark the order received only when every line is complete. Do not derive order completion only from posted rows. ## 34. Make Inventory Updates Atomic Inventory receipt must occur in one transaction. For each item: * Confirm the supply record exists before updating received quantity. * Lock the supply row. * Increment inventory atomically. * Insert inventory transaction log. * Fail the entire transaction if any step fails. --- # Phase 8: Normalize Statuses and Database Constraints ## 35. Define Canonical Status Enums Standardize lowercase machine values. Examples: ### Invoice ```text draft issued unpaid partially_paid paid credited voided ``` ### Refund ```text requested approved partially_paid paid rejected cancelled reversed ``` ### Reimbursement ```text pending approved paid rejected reversed ``` Do not store display labels such as `Partially Paid` as database values. ## 36. Add Database Constraints Recommended constraints: * Unique invoice identity * Unique refund payout idempotency key * Unique active reimbursement per expense * Nonnegative money columns where applicable * Valid enum or check-constrained statuses * Foreign keys for invoice, payment, refund, expense, and reimbursement references * Unique batch sequence per year * Unique voucher application where applicable Application validation is useful. Database constraints are the part that still works when application code has a difficult afternoon. --- # Phase 9: Secure Financial Endpoints ## 37. Apply Explicit Route Protection Every financial route must require: * Authentication * Role or permission authorization * CSRF validation for browser writes * Correct HTTP method * Input validation * Audit logging State changes must not use GET. ## 38. Add Ownership and Scope Checks Every operation must verify the relationship between records. Examples: * Invoice belongs to parent. * Payment belongs to invoice. * Refund source belongs to parent and invoice. * Expense belongs to the correct accounting period. * Reimbursement belongs to the expense. * Additional charge belongs to the invoice. * Voucher is valid for the invoice period. --- # Phase 10: Repair Reporting ## 39. Use Canonical Ledger Projections Reports must not independently reconstruct totals. Create reporting views or projections for: * Invoice gross charges * Applied discounts * Net charges * Successful payments * Completed refunds * Amount due * Customer credit * Reimbursed expenses * Outstanding reimbursements All operational screens and reports must call the same ledger logic or use the same persisted ledger projection. ## 40. Add Reconciliation Reports Create administrative reports for: * Ledger total versus stored invoice total * Payment total versus payment allocation total * Refund payouts versus approved refund amount * Expense amount versus reimbursement amount * Invoice credit versus outstanding refund amount * Duplicate external transaction references * Invalid or unknown statuses * Historical record modifications --- # Phase 11: Automated Testing ## 41. Unit Tests Test every canonical formula: * Exact payment * Partial payment * Multiple payments * Overpayment * Zero balance * Partial refund * Full refund * Multiple refund payouts * Reversed refund * Payment reversal * Fixed discount * Percentage discount * Discount cap * Multiple discount ordering * Additional charge * Deduction * Rounding boundaries ## 42. Integration Tests Test database workflows: * Payment creates correct invoice projection. * Refund reduces customer credit. * Refund cannot exceed available credit. * Failed payments do not contribute to refundable credit. * Issued invoice does not change when tuition settings change. * Issued invoice does not change when enrollment changes. * Extra charge cannot target another parent’s invoice. * Reimbursement and expense update commit together. * Failed second write rolls back the first write. * Reports match invoice ledger totals. ## 43. Concurrency Tests Run two simultaneous requests for: * Invoice generation * Voucher application * Refund payout * Reimbursement creation * Reimbursement batch closing * Purchase-order receipt * Inventory increment Expected result: * Only one operation succeeds when the resource is exclusive. * No duplicated payout occurs. * No balance becomes inconsistent. * Database constraints reject duplicate operations safely. ## 44. Route and Authorization Tests Verify: * Every configured route points to an existing method. * Method argument counts match the route. * Unauthorized users receive rejection. * Users cannot modify another parent’s financial records. * State-changing GET requests do not exist. * Invalid statuses are rejected. * Invalid payment methods are rejected. ## 45. Property-Based Financial Tests Generate random combinations of: * Charges * Discounts * Payments * Refunds * Reversals Assert invariants: ```text amount due >= 0 customer credit >= 0 amount due and customer credit cannot both be positive completed refund payouts cannot exceed approved refundable credit applied discounts cannot exceed eligible charge base ``` --- # Phase 12: Data Migration ## 46. Prepare Migration Scripts Migrations should: 1. Add integer-cent columns. 2. Backfill cents from legacy decimal fields. 3. Add invoice line snapshots. 4. Normalize status values. 5. Create refund payout records from existing paid-refund totals. 6. Create ledger projection fields or tables. 7. Add constraints only after invalid data is reconciled. 8. Preserve legacy values for audit purposes. ## 47. Reconcile Existing Refunds For every existing refund: * Identify its source. * Confirm approved amount. * Confirm actual payout amount. * Confirm payout method and reference. * Compare it with customer credit at the payout time. * Mark exceptions for manual review. Do not invent missing transaction references. ## 48. Recalculate Existing Invoices Carefully Use frozen historical source data where available. When source data is incomplete: * Preserve the existing issued total. * Record a reconciliation warning. * Do not rebuild the invoice from current configuration. --- # Phase 13: Observability and Operational Controls ## 49. Add Structured Financial Logging Every financial command should log: * Request ID * Idempotency key * Actor * Action * Entity type * Entity ID * Amount * Currency * Previous status * New status * External reference * Transaction outcome Never log full card data or sensitive bank information. ## 50. Add Alerts Alert on: * Refund greater than configured threshold * Multiple refund attempts using the same source * Failed ledger recalculation * Negative stored amount where prohibited * Duplicate invoice attempt * Refund payout without reference * Reimbursement exceeding expense * Report-to-ledger mismatch * Database transaction rollback ## 51. Add Daily Reconciliation Run a scheduled reconciliation job that verifies: * Invoice projection equals ledger activity. * Payment totals equal allocated payment totals. * Refund payouts do not exceed approved refunds. * Reimbursements do not exceed approved expenses. * No paid record was modified in place. * No unknown status exists. The job should report exceptions, not silently modify financial records. --- # Phase 14: Deployment Strategy ## 52. Deploy Behind Feature Flags Use separate flags for: * New invoice ledger * New refund processing * New discount calculation * New reimbursement processing * New financial reports ## 53. Shadow Calculation Before switching production writes: 1. Continue using the current calculation for operational output. 2. Run the new ledger calculation in parallel. 3. Log differences. 4. Investigate every unexplained difference. 5. Require a zero-unexplained-difference period before cutover. ## 54. Staged Rollout Recommended order: 1. Internal test environment 2. Staging with production-like data 3. Finance-administrator pilot 4. Limited production cohort 5. Full production rollout ## 55. Rollback Plan Rollback must disable new writes without deleting new immutable records. Do not design rollback around reversing database migrations that contain financial transactions. Keep: * Compatibility reads * Feature flags * Migration checkpoints * Database backups * Reconciliation exports --- # Phase 15: Production Acceptance Criteria The refund workflow is production-ready only when all of the following are true: * A completed refund reduces customer credit correctly. * A refund cannot create additional overpayment. * A refund cannot exceed verified refundable credit. * Two concurrent payout requests cannot pay twice. * Repeated requests with the same idempotency key return the same result. * Only successful payment amounts are refundable. * Issued invoice totals remain unchanged when live configuration changes. * Every payout has an immutable record. * Paid refunds cannot be edited or deleted. * Refund reversals preserve the original record. * All refund routes are valid and protected. * All database writes are checked. * Partial failures roll back completely. * Reports match ledger values. * Unit, integration, concurrency, route, and authorization tests pass. * Existing financial data has been reconciled. * Finance staff have approved the reconciliation report. * Monitoring and daily reconciliation are active. --- # Recommended Implementation Order ## Sprint 1: Critical accounting and security * Fix refund sign. * Introduce raw balance and customer credit. * Secure all financial routes. * Repair broken refund routes. * Normalize payment status filtering. * Add immediate refund amount validation. * Block editing paid financial records. ## Sprint 2: Canonical ledger and invoice snapshots * Build the ledger service. * Create invoice line snapshots. * Stop recalculation from live data. * Remove duplicate controller formulas. * Normalize invoice statuses. ## Sprint 3: Refund transaction model * Add refund payout table. * Add row locking. * Add idempotency. * Add approval workflow. * Add payout-method validation. * Add refund reversal support. ## Sprint 4: Discounts and additional charges * Centralize eligibility. * Lock vouchers. * Store applied discount amounts. * Define multiple-discount ordering. * Validate additional-charge ownership. * Repair charge sign handling. ## Sprint 5: Reimbursements, expenses, and purchase orders * Add reimbursement locking and uniqueness. * Make paid records immutable. * Repair batch processing. * Repair PO receiving and inventory transactions. ## Sprint 6: Reporting, migration, and rollout * Rebuild reports from the ledger. * Reconcile historical data. * Add monitoring and alerts. * Run shadow calculations. * Perform staged rollout. --- # Definition of Done A financial feature is not complete merely because the controller returns success. It is complete only when: * Accounting behavior is documented. * Calculation occurs in the canonical service. * Money uses integer minor units. * Authorization is enforced. * Writes are transactional. * Concurrent execution is safe. * Idempotency is implemented where needed. * Database constraints defend invariants. * Audit records are immutable. * Reports use the same ledger. * Automated tests cover success, failure, retry, and concurrency. * Existing data has a defined migration and reconciliation path.