Phase 1: Standardize Payments as the Manual Bill Payment Model + Surface on Bills Detail #43

New Issue

Closed

opened 2026-05-16 18:17:10 -05:00 by null · 0 comments

null commented

2026-05-16 18:17:10 -05:00

Owner

Goal

Standardize and expose the existing payments table as the canonical manual bill payment model. Add payment_source and transaction_id columns to the existing payments table (not a new table). Then surface payment history on the Bills detail/edit page — the only real UI gap.

Depends on: Nothing (first phase)
Blocks: Phase 2 (#44), Phase 4 (#46)
Parent: #42

What Already Exists

The repo has a full payment system already:

payments table: id, bill_id, amount (REAL dollars), paid_date, method, notes, balance_delta, deleted_at, timestamps — db/schema.sql
CRUD endpoints: POST/GET/PUT/DELETE /api/payments, GET /api/bills/:id/payments — routes/payments.js
Soft delete/restore: DELETE /api/payments/:id sets deleted_at, POST /api/payments/:id/restore clears it
Payment validation: services/paymentValidation.js — paid_date, REAL dollar amounts
Status derivation: services/statusService.js — partial payments handled, paid when totalPaid >= expectedAmount
Tracker payment ledger UI: TrackerPage.jsx line 544 (history), line 1075 (edit/delete modal)

Do NOT create a new bill_payments table. The existing payments table is the payment ledger.

Migration: Add Columns to Existing `payments` Table

ALTER TABLE payments ADD COLUMN payment_source TEXT NOT NULL DEFAULT 'manual';
ALTER TABLE payments ADD COLUMN transaction_id INTEGER;

Add to db/database.js migrations:

Check: payment_source column not in PRAGMA table_info(payments)
Run: ALTER TABLE payments ADD COLUMN payment_source TEXT NOT NULL DEFAULT 'manual'
Check: transaction_id column not in PRAGMA table_info(payments)
Run: ALTER TABLE payments ADD COLUMN transaction_id INTEGER

Column purposes

payment_source — tracks origin: manual, file_import, provider_sync. Existing rows default to manual.
transaction_id — nullable FK to transactions table (added in Phase 2). For now, column exists but is NULL until Phase 2 creates the transactions table. Do NOT add a FK constraint yet — add it in Phase 2 when the referenced table exists.

Backend Changes

Update payment creation to accept `payment_source`

Default payment_source to manual in routes/payments.js create handler. Frontend can omit it.

Update payment response shapes

Include payment_source and transaction_id (null for now) in payment responses.

No new endpoints needed

The existing CRUD endpoints already handle everything. This phase does not add routes.

Frontend

Add to the Bills detail/edit page (BillModal.jsx):

Payment history list (paid_date, amount, method, notes, payment_source indicator)
Add payment button/form
Edit payment
Soft-delete payment (with undo/restore)
Source indicator showing manual

Use existing GET /api/bills/:id/payments and POST /api/payments endpoints.

Request shape matches existing validation:

{
  "bill_id": 123,
  "paid_date": "2026-05-16",
  "amount": 85.00,
  "method": "manual",
  "notes": "Paid from checking"
}

Note: amount is a REAL dollar value (not integer cents). paid_date is the field name (not paidAt).

Status Logic

Bill status is already derived from payments correctly:

Partial payments: totalPaid < expectedAmount → partial status
Paid: totalPaid >= expectedAmount → paid
Soft-deleted payments excluded from calculation
No changes to status logic needed.

Do NOT regress partial payment support. A single payment does not mean "paid" unless the total covers the expected amount.

What This Phase Does NOT Do

No new database tables
No new backend endpoints
No CSV import
No SimpleFIN
No transaction matching
No FK constraint on transaction_id yet (added in Phase 2)
No changes to Tracker payment ledger (it already works)

Acceptance Criteria

Migration adds payment_source and transaction_id columns to payments table
Existing payment rows have `payment_source = 'manual' by default
Payment responses include payment_source and transaction_id (null)
Bills detail/edit page shows payment history (BillModal.jsx)
User can add/edit/delete/restore a payment from Bills detail page
Bill status reflects payment changes correctly (already works)
Tracker payment ledger still works unchanged
Partial payment behavior preserved
No new tables or endpoints added

Next → Phase 2 (#44)

## Goal Standardize and expose the existing `payments` table as the canonical manual bill payment model. Add `payment_source` and `transaction_id` columns to the existing `payments` table (not a new table). Then surface payment history on the Bills detail/edit page — the only real UI gap. **Depends on:** Nothing (first phase) **Blocks:** Phase 2 (#44), Phase 4 (#46) **Parent:** #42 --- ## What Already Exists The repo has a full payment system already: - `payments` table: `id`, `bill_id`, `amount` (REAL dollars), `paid_date`, `method`, `notes`, `balance_delta`, `deleted_at`, timestamps — `db/schema.sql` - CRUD endpoints: `POST/GET/PUT/DELETE /api/payments`, `GET /api/bills/:id/payments` — `routes/payments.js` - Soft delete/restore: `DELETE /api/payments/:id` sets `deleted_at`, `POST /api/payments/:id/restore` clears it - Payment validation: `services/paymentValidation.js` — `paid_date`, REAL dollar amounts - Status derivation: `services/statusService.js` — partial payments handled, paid when `totalPaid >= expectedAmount` - Tracker payment ledger UI: `TrackerPage.jsx` line 544 (history), line 1075 (edit/delete modal) **Do NOT create a new `bill_payments` table.** The existing `payments` table is the payment ledger. --- ## Migration: Add Columns to Existing `payments` Table ```sql ALTER TABLE payments ADD COLUMN payment_source TEXT NOT NULL DEFAULT 'manual'; ALTER TABLE payments ADD COLUMN transaction_id INTEGER; ``` Add to `db/database.js` migrations: - Check: `payment_source` column not in `PRAGMA table_info(payments)` - Run: `ALTER TABLE payments ADD COLUMN payment_source TEXT NOT NULL DEFAULT 'manual'` - Check: `transaction_id` column not in `PRAGMA table_info(payments)` - Run: `ALTER TABLE payments ADD COLUMN transaction_id INTEGER` ### Column purposes - `payment_source` — tracks origin: `manual`, `file_import`, `provider_sync`. Existing rows default to `manual`. - `transaction_id` — nullable FK to `transactions` table (added in Phase 2). For now, column exists but is NULL until Phase 2 creates the transactions table. Do NOT add a FK constraint yet — add it in Phase 2 when the referenced table exists. --- ## Backend Changes ### Update payment creation to accept `payment_source` Default `payment_source` to `manual` in `routes/payments.js` create handler. Frontend can omit it. ### Update payment response shapes Include `payment_source` and `transaction_id` (null for now) in payment responses. ### No new endpoints needed The existing CRUD endpoints already handle everything. This phase does not add routes. --- ## Frontend Add to the **Bills detail/edit page** (`BillModal.jsx`): - Payment history list (paid_date, amount, method, notes, payment_source indicator) - Add payment button/form - Edit payment - Soft-delete payment (with undo/restore) - Source indicator showing `manual` Use existing `GET /api/bills/:id/payments` and `POST /api/payments` endpoints. Request shape matches existing validation: ```json { "bill_id": 123, "paid_date": "2026-05-16", "amount": 85.00, "method": "manual", "notes": "Paid from checking" } ``` **Note:** `amount` is a REAL dollar value (not integer cents). `paid_date` is the field name (not `paidAt`). --- ## Status Logic Bill status is already derived from payments correctly: - Partial payments: `totalPaid < expectedAmount` → partial status - Paid: `totalPaid >= expectedAmount` → paid - Soft-deleted payments excluded from calculation - **No changes to status logic needed.** Do NOT regress partial payment support. A single payment does not mean "paid" unless the total covers the expected amount. --- ## What This Phase Does NOT Do - No new database tables - No new backend endpoints - No CSV import - No SimpleFIN - No transaction matching - No FK constraint on `transaction_id` yet (added in Phase 2) - No changes to Tracker payment ledger (it already works) --- ## Acceptance Criteria - [x] Migration adds `payment_source` and `transaction_id` columns to `payments` table - [x] Existing payment rows have `payment_source = 'manual' by default - [x] Payment responses include `payment_source` and `transaction_id` (null) - [x] Bills detail/edit page shows payment history (`BillModal.jsx`) - [x] User can add/edit/delete/restore a payment from Bills detail page - [x] Bill status reflects payment changes correctly (already works) - [x] Tracker payment ledger still works unchanged - [x] Partial payment behavior preserved - [x] No new tables or endpoints added --- **Next → Phase 2 (#44)**

null referenced this issue

2026-05-16 18:17:20 -05:00

Phase 2: Data Sources & Shared Transactions #44

null referenced this issue

2026-05-16 18:17:37 -05:00

Phase 4: Transaction Matching #46

null referenced this issue

2026-05-16 18:17:54 -05:00

Phase 6: Background Sync & Polish #48

null added the

backend

feature

priority:high

labels 2026-05-16 18:18:01 -05:00

null referenced this issue

2026-05-16 18:18:19 -05:00

Project-wide Data Input & Sync — Manual + CSV Import + Provider Abstraction #42

null changed title from ~~Phase 1: Manual Bill Payments~~ to Phase 1: Payment History on Bills Detail Page

2026-05-16 18:33:27 -05:00

null changed title from ~~Phase 1: Payment History on Bills Detail Page~~ to Phase 1: Standardize Payments as the Manual Bill Payment Model + Surface on Bills Detail

2026-05-16 18:34:08 -05:00

null closed this issue

2026-05-16 19:42:07 -05:00