Monthly Obligations v3 — Canonical Schema Proposal
Generated: 2026-05-06 · Session 26 close
Source data: v1 Monthly Obligations as pasted by Sam (you confirmed it's the latest reality — APR went up, payments up, Barclay→Citi transition real).
Output: MonthlyObligations_v3_canonical.csv (41 rows, 16 columns) — drop-in for a new Sheet.
Why a v3, not just keep using v1
The v1 sheet you have is correct on the data, but its schema and structure make on-the-go editing harder than it should be:
| Pain in current v1 | What v3 fixes |
|---|---|
| 27+ columns including some computed/calculated fields you can't edit cleanly on phone | 16 essential columns, derived fields move to a Dashboard tab |
| Mixed casing / inconsistent naming ("agudah" vs "Agudah", spaces inconsistent) | Stable IDs (OBL_KIDS_AGUDAH) so renames don't break references |
| No "frequency" field — quarterly William Penn looks like monthly | Explicit frequency field |
| No payment-log/audit trail | New PaymentLog tab |
| HookStreet OS Control Panel and Obligations are mixed in different sheets (v1/v2 split) | Consolidated into one |
| No way to mark "paid this cycle" without clobbering autopay status | Status field decoupled from payment_method |
| Closed accounts (Citi 6965, Citi 0157 with $0 balance) clutter active list | Archive tab for closed |
Proposed 6-tab architecture
NEW SHEET: "Hookstreet OS — Obligations & Cashflow v3"
Tab 1: Obligations ← THE core (you edit this on phone)
Tab 2: Accounts ← bank/card metadata (edit rarely)
Tab 3: PaymentLog ← append-only proof artifact (PWA writes here)
Tab 4: Dashboard ← calculated views (don't edit)
Tab 5: Control ← cron + recipient config (lifted from v2)
Tab 6: Archive ← closed/inactive
Tab 1: Obligations — the canonical row-per-obligation table (16 cols)
| # | Column | Type | What it holds | Editable on phone? |
|---|---|---|---|---|
| 1 | id |
string | Stable primary key (OBL_HOME_FIFTH_THIRD) | No — auto |
| 2 | category |
enum | Home · Kids · Auto · Card-Personal · Card-Business · Utility · Hookstreet · Life Ins · Loan · Services | Yes |
| 3 | name |
string | Display name | Yes |
| 4 | vendor |
string | Who you owe | Yes |
| 5 | amount |
money | Current min due / scheduled payment | Yes (most edited field) |
| 6 | frequency |
enum | monthly · quarterly · installment · bi-monthly-installment · one-time · annual | Yes |
| 7 | due_day |
int | Day of month (1-31) | Yes |
| 8 | next_due_date |
date | YYYY-MM-DD | Auto-computed from due_day usually |
| 9 | balance |
money | Current balance owed (cards/loans) | Yes |
| 10 | credit_limit |
money | Limit (cards only) | Rarely |
| 11 | apr |
percent | Current APR | Yes (changes when promos end) |
| 12 | account_paid_from |
string | Which account funds this (Chase 5609, Chase 7792, etc.) | Yes |
| 13 | payment_method |
enum | Autopay · Scheduled · Manual · Pending · Not Scheduled | Yes |
| 14 | strategy |
enum | Snowball · Big Focus - Payoff · Spread Out · Utility · Busn-Snowball · Pending · Fixed | Yes |
| 15 | status |
enum | Active · Autopay · Urgent! · Paid - refresh data · Pending - Inspect · Closed | Yes (most state changes here) |
| 16 | notes |
string | Free-form | Yes |
Why 16 columns: they fit horizontally on iPad mini portrait without horizontal scroll. Phone landscape works too.
Tab 2: Accounts — the funding-source registry (8 cols)
| Column | Holds |
|---|---|
account_id |
ACC_5609, ACC_7792, ACC_1260, ACC_5777, ACC_9312_6651, ACC_9332_2528 etc. |
display_name |
"Chase 5609 Personal Checking" |
type |
checking · credit_card · debit · investment · escrow · loan |
current_balance |
Snapshot (you update on Sundays or when alerts fire) |
credit_limit |
For cards |
funding_priority |
1 = primary, 2 = secondary, etc. |
routing_in |
What deposits into this account |
notes |
"Chanie funds 7792 on the 1st" |
This separates "where the money sits" from "what's owed." When the PWA shows your Cash section, it pulls from here.
Tab 3: PaymentLog — append-only proof of every payment
| Column | Holds |
|---|---|
timestamp |
When recorded |
obligation_id |
FK → Obligations.id |
amount_paid |
Actual amount |
account_used |
Which account (for reconciliation) |
proof |
URL / screenshot path / confirmation # |
recorded_by |
Sam · Mildred · PWA-auto · Bank-import |
notes |
Free |
The PWA writes here when you tap "Mark Paid" on an obligation. Mildred can see this view (transparent ledger) but she can't see the Accounts tab balances (privacy boundary).
Tab 4: Dashboard — calculated views
Only formulas, no manual edits. Generates:
- By Account: sum of obligations per
account_paid_from - By Category: monthly outflow per category
- Due Next 7 / 14 / 30 days
- High-APR cards (>15%) with utilization %
- Total monthly outflow vs monthly income (when income tab populated)
- Snowball priority order (highest APR first, with strategy override)
- Items needing balance (rows where
balanceis blank but should have value) - Status alerts (Urgent! count, Pending - Inspect count)
Tab 5: Control — cron config (lifted from v2)
Identical to current v2 HookStreet OS Control Panel:
- AM_HOUR / AM_MINUTE / AM_ENABLED
- PM, WEEKLY, MONTHLY, BIWEEKLY, WIFE schedules
- Alert thresholds (DUE_DAYS_MAX, URGENT_WINDOW, UTILIZATION_PCT, HIGH_APR_PCT)
- LAST_RUN / LAST_STATUS / LAST_RECIPIENTS auto-fields
This makes the new sheet a drop-in replacement for v2's automation role.
Tab 6: Archive — closed/inactive obligations
When an obligation closes (paid off / cancelled), move row here with closed_date. Keeps the Obligations tab clean for active items.
Two-way sync — what edits go where
When the PWA edits an obligation:
| User action on PWA | Sheet write |
|---|---|
| Long-press obligation card → edit Min Due | Obligations[id].amount updated; row appended to a Edit_Audit_Log tab |
| Triple-tap card → mark "paid this cycle" | Obligations[id].status = "Paid - refresh data" + new row in PaymentLog |
| Add new obligation via "+" button | New row in Obligations with auto-generated id |
| Edit account balance | Accounts[id].current_balance updated |
| Update APR after rate change | Obligations[id].apr updated; comment captured in notes |
All edits are timestamped, audit-logged, and reversible (the audit log preserves before/after).
Migration from v1 → v3 (5 steps, ~10 min)
- Open Google Sheets → New blank Sheet → name it "Hookstreet OS — Obligations & Cashflow v3"
- File → Import → Upload → drag
MonthlyObligations_v3_canonical.csv→ Replace current sheet → Import data - Rename Sheet1 → "Obligations"
- Insert 5 more tabs: Accounts, PaymentLog, Dashboard, Control, Archive
- Get the new Sheet ID (URL between /d/ and /edit), give it to me, I update Code.gs CONFIG to point at it
After migration:
- v1 → add SUPERSEDED header at top → archive in Drive folder
- v2 → control panel lift target (move CONTROL section content into v3 Control tab) → archive
What's filled in vs what needs you
The CSV has 41 rows of clean data. Already filled where I had confidence:
- All amounts that v1 had (your latest values)
- All APRs that v1 had (including the 19.49% on Bus 0049 — confirmed correct)
- All due dates / due days
- All strategy / payment method / status fields
- Stable IDs
Still needs your input (rows where balance or due_day is blank):
- 5 cards NEEDS BALANCE — Chase PV 6377, Amex Busn SPG, Citi 0157, Citi 6965 (formerly Barclay), US Bus 7496 — paste Rocket Money CSV or check statements
- Prudential — reactivation date / amount needed
- PSEG — next due date
- Outstanding Bills / Gardener / Exterminator / Subscriptions — due dates
- Duke Energy 9312/9332 — next due dates
- Toho Water 9312/9332 — next due dates
Recommendation
Migrate to v3 this week. Send me the new Sheet ID once created → I update Code.gs CONFIG → PWA points at the right place. Then v1 + v2 retire.
The CSV is the cleanest representation I can produce from your current data. Tweak/edit/extend as you go.