Phase 2: Data Sources & Shared Transactions #44

New Issue

Closed

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

null commented

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

Owner

Goal

Add the shared foundation for imported, synced, and manual transaction data. This phase creates the tables and APIs that CSV import (Phase 3) and SimpleFIN (Phase 5) will feed into.

Depends on: Phase 1 (#43) — needs transaction_id column on payments table
Blocks: Phase 3 (#45), Phase 4 (#46), Phase 5 (#47)
Parent: #42

Scope

Implement

data_sources table
financial_accounts table
transactions table
Backend transaction APIs (list, create manual, ignore/unignore)
Manual data source seed
Source labels in backend responses

Do NOT implement

CSV upload
SimpleFIN
Matching suggestions
Background sync

Database

Three new tables added via v0.60 migration:

data_sources — user_id scoped, with manual seed per user
financial_accounts — linked to data_sources, provider_account_id unique constraint
transactions — user_id scoped, source_type enum (manual/file_import/provider_sync), match_status, matched_bill_id FK to bills

All tables use CREATE TABLE IF NOT EXISTS with proper indexes.

Backend API

Mounted in server.js:

GET /api/data-sources — list data sources with account/transaction counts
GET /api/transactions — list with filters (match_status, ignored, source_type, date range, text search)
POST /api/transactions/manual — create manual transaction, auto-creates manual data source
PUT /api/transactions/:id — update transaction fields
DELETE /api/transactions/:id — delete, nullifies linked payment.transaction_id
POST /api/transactions/:id/ignore — set ignored=1, match_status=ignored
POST /api/transactions/:id/unignore — set ignored=0, match_status=unmatched

Manual Data Source

Seed a manual data source on first run:

type: manual, provider: manual, name: Manual Entry
Auto-created per user via ensureManualDataSource()
Unique partial index on (user_id, type, provider) WHERE type='manual' AND provider='manual'

Acceptance Criteria

New tables migrate cleanly alongside existing schema
Existing app behavior is unchanged
User can create manual transactions via API
Transactions list supports basic filters
Ignore and unignore work
No CSV or SimpleFIN code in this phase

Previous → Phase 1 (#43)
Next → Phase 3 (#45)

## Goal Add the shared foundation for imported, synced, and manual transaction data. This phase creates the tables and APIs that CSV import (Phase 3) and SimpleFIN (Phase 5) will feed into. **Depends on:** Phase 1 (#43) — needs `transaction_id` column on `payments` table **Blocks:** Phase 3 (#45), Phase 4 (#46), Phase 5 (#47) **Parent:** #42 --- ## Scope ### Implement - `data_sources` table - `financial_accounts` table - `transactions` table - Backend transaction APIs (list, create manual, ignore/unignore) - Manual data source seed - Source labels in backend responses ### Do NOT implement - CSV upload - SimpleFIN - Matching suggestions - Background sync --- ## Database Three new tables added via `v0.60` migration: - `data_sources` — user_id scoped, with manual seed per user - `financial_accounts` — linked to data_sources, provider_account_id unique constraint - `transactions` — user_id scoped, source_type enum (manual/file_import/provider_sync), match_status, matched_bill_id FK to bills All tables use `CREATE TABLE IF NOT EXISTS` with proper indexes. --- ## Backend API Mounted in `server.js`: - `GET /api/data-sources` — list data sources with account/transaction counts - `GET /api/transactions` — list with filters (match_status, ignored, source_type, date range, text search) - `POST /api/transactions/manual` — create manual transaction, auto-creates manual data source - `PUT /api/transactions/:id` — update transaction fields - `DELETE /api/transactions/:id` — delete, nullifies linked payment.transaction_id - `POST /api/transactions/:id/ignore` — set ignored=1, match_status=ignored - `POST /api/transactions/:id/unignore` — set ignored=0, match_status=unmatched --- ## Manual Data Source Seed a manual data source on first run: - `type: manual`, `provider: manual`, `name: Manual Entry` - Auto-created per user via `ensureManualDataSource()` - Unique partial index on `(user_id, type, provider)` WHERE type='manual' AND provider='manual' --- ## Acceptance Criteria - [x] New tables migrate cleanly alongside existing schema - [x] Existing app behavior is unchanged - [x] User can create manual transactions via API - [x] Transactions list supports basic filters - [x] Ignore and unignore work - [x] No CSV or SimpleFIN code in this phase --- **Previous → Phase 1 (#43)** **Next → Phase 3 (#45)**