← back to AbramsOS
feat(schema): bill + reorder_item tables, recurrence lib, reminder-engine wiring
5693d23dc4c0081a0107084bca7ecd8fd1665ccf · 2026-07-07 07:32:16 -0700 · Steve
- 0007 migration: bill (Bills to Pay incl. tax/government/CRA via category) + reorder_item (frequently-ordered + best-price savings)
- lib/recurrence.js: pure cadence date math (+ 5 passing unit tests)
- reminder-engine emits calendar_reminder rows for upcoming bill due-dates and reorder points (unified Deadlines)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/0007_bills_reorders.sqlM lib/ids.jsA lib/recurrence.jsM lib/reminder-engine.jsA tests/recurrence.test.js
Diff
commit 5693d23dc4c0081a0107084bca7ecd8fd1665ccf
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 7 07:32:16 2026 -0700
feat(schema): bill + reorder_item tables, recurrence lib, reminder-engine wiring
- 0007 migration: bill (Bills to Pay incl. tax/government/CRA via category) + reorder_item (frequently-ordered + best-price savings)
- lib/recurrence.js: pure cadence date math (+ 5 passing unit tests)
- reminder-engine emits calendar_reminder rows for upcoming bill due-dates and reorder points (unified Deadlines)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
db/migrations/0007_bills_reorders.sql | 56 +++++++++++++++++++++++++++++++
lib/ids.js | 3 ++
lib/recurrence.js | 63 +++++++++++++++++++++++++++++++++++
lib/reminder-engine.js | 42 +++++++++++++++++++++++
tests/recurrence.test.js | 35 +++++++++++++++++++
5 files changed, 199 insertions(+)
diff --git a/db/migrations/0007_bills_reorders.sql b/db/migrations/0007_bills_reorders.sql
new file mode 100644
index 0000000..2bf2f77
--- /dev/null
+++ b/db/migrations/0007_bills_reorders.sql
@@ -0,0 +1,56 @@
+-- 0007_bills_reorders.sql
+-- Adds two household-admin owner tables that also feed the calendar_reminder engine:
+-- bill — recurring/one-off money owed (utilities, rent, insurance, subscriptions,
+-- loans, cards, AND tax/government payments such as CRA — category='tax'|'government')
+-- reorder_item — things bought repeatedly ("what we order a lot") + best-price tracking ("save money")
+-- Idempotent. Safe to re-run.
+
+BEGIN;
+
+CREATE TABLE IF NOT EXISTS bill (
+ id text PRIMARY KEY,
+ user_id text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
+ name text NOT NULL,
+ payee text,
+ category text NOT NULL DEFAULT 'other', -- utility|rent|mortgage|insurance|subscription|loan|card|phone|internet|tax|government|other
+ amount numeric(12,2),
+ currency text NOT NULL DEFAULT 'USD',
+ cadence text NOT NULL DEFAULT 'monthly', -- once|weekly|biweekly|monthly|quarterly|semiannual|annual
+ due_date date, -- NEXT date this bill is due
+ autopay boolean NOT NULL DEFAULT false,
+ account_hint text, -- redacted only, e.g. "Visa ••1234" — never a full PAN/account number
+ url text, -- pay-online / account link
+ status text NOT NULL DEFAULT 'active', -- active|paused|closed
+ last_paid_at date,
+ notes text,
+ metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS bill_user_due_idx ON bill (user_id, status, due_date);
+CREATE INDEX IF NOT EXISTS bill_category_idx ON bill (user_id, category);
+
+CREATE TABLE IF NOT EXISTS reorder_item (
+ id text PRIMARY KEY,
+ user_id text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
+ name text NOT NULL,
+ merchant text, -- where we usually buy it
+ category text, -- grocery|household|pet|office|health|other
+ unit text, -- e.g. "12-pack", "1 gal", "each"
+ typical_price numeric(12,2), -- what we usually pay
+ best_price numeric(12,2), -- cheapest seen (drives "save money")
+ best_price_source text, -- where the cheapest price was
+ currency text NOT NULL DEFAULT 'USD',
+ reorder_cadence_days integer, -- how often we repurchase
+ last_ordered_at date,
+ next_due_date date, -- computed: last_ordered_at + cadence
+ url text,
+ status text NOT NULL DEFAULT 'active', -- active|paused|archived
+ notes text,
+ metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS reorder_user_due_idx ON reorder_item (user_id, status, next_due_date);
+
+COMMIT;
diff --git a/lib/ids.js b/lib/ids.js
index c6a9835..9360880 100644
--- a/lib/ids.js
+++ b/lib/ids.js
@@ -8,6 +8,9 @@ const PREFIX = {
document: 'doc',
purchase: 'pur',
item: 'item',
+ bill: 'bill',
+ reorder: 'reord',
+ reminder: 'rem',
};
function id(kind) {
diff --git a/lib/recurrence.js b/lib/recurrence.js
new file mode 100644
index 0000000..4cac882
--- /dev/null
+++ b/lib/recurrence.js
@@ -0,0 +1,63 @@
+// recurrence.js — pure date helpers for bills (cadence) and reorders (cadence_days).
+// No DB, no side effects → unit-testable in isolation.
+
+const CADENCE_DAYS = {
+ weekly: 7,
+ biweekly: 14,
+};
+const CADENCE_MONTHS = {
+ monthly: 1,
+ quarterly: 3,
+ semiannual: 6,
+ annual: 12,
+};
+
+// Parse a YYYY-MM-DD (or Date) into a UTC-noon Date so DST / timezone never shifts the day.
+function toDate(d) {
+ if (d instanceof Date) return d;
+ const [y, m, day] = String(d).slice(0, 10).split('-').map(Number);
+ return new Date(Date.UTC(y, m - 1, day, 12, 0, 0));
+}
+
+function toISODate(d) {
+ return toDate(d).toISOString().slice(0, 10);
+}
+
+function addDays(d, n) {
+ const x = toDate(d);
+ x.setUTCDate(x.getUTCDate() + n);
+ return x;
+}
+
+function addMonths(d, n) {
+ const x = toDate(d);
+ const targetMonth = x.getUTCMonth() + n;
+ x.setUTCMonth(targetMonth);
+ return x;
+}
+
+// Given a due date and a cadence, return the NEXT due date (YYYY-MM-DD string).
+// 'once' returns null (no recurrence). Unknown cadence → null.
+function nextDue(fromDate, cadence) {
+ if (!fromDate || !cadence || cadence === 'once') return null;
+ if (CADENCE_DAYS[cadence]) return toISODate(addDays(fromDate, CADENCE_DAYS[cadence]));
+ if (CADENCE_MONTHS[cadence]) return toISODate(addMonths(fromDate, CADENCE_MONTHS[cadence]));
+ return null;
+}
+
+// For reorders: next due = last ordered + N days.
+function nextDueDays(fromDate, days) {
+ if (!fromDate || !days || days <= 0) return null;
+ return toISODate(addDays(fromDate, days));
+}
+
+// Days until a date from now (integer, can be negative when overdue).
+function daysUntil(d, now = new Date()) {
+ if (!d) return null;
+ const ms = toDate(d).getTime() - toDate(toISODate(now)).getTime();
+ return Math.round(ms / 86400000);
+}
+
+const CADENCES = ['once', 'weekly', 'biweekly', 'monthly', 'quarterly', 'semiannual', 'annual'];
+
+module.exports = { nextDue, nextDueDays, daysUntil, addDays, addMonths, toISODate, CADENCES };
diff --git a/lib/reminder-engine.js b/lib/reminder-engine.js
index 7356991..e39a29e 100644
--- a/lib/reminder-engine.js
+++ b/lib/reminder-engine.js
@@ -75,6 +75,48 @@ async function generateForUser(userId) {
if (r) inserted += 1;
}
+ // 4) Bills due — fire when due within the next 14 days (and while overdue)
+ const bills = await db.query(
+ `SELECT id, name, payee, amount, currency, due_date, category
+ FROM bill WHERE user_id = $1 AND status = 'active' AND due_date IS NOT NULL`,
+ [userId]
+ );
+ for (const b of bills.rows) {
+ const daysUntil = (new Date(b.due_date).getTime() - Date.now()) / 86400e3;
+ if (daysUntil <= 14) {
+ const dueAt = new Date(new Date(b.due_date).getTime() + 12 * 3600e3);
+ const amt = b.amount ? `${b.currency} ${Number(b.amount).toFixed(2)}` : '';
+ const r = await tryInsert({
+ userId, ownerTable: 'bill', ownerId: b.id, dueAt,
+ reasonCode: 'bill_due',
+ title: `Bill due: ${b.name}${amt ? ' — ' + amt : ''}`,
+ body: `${b.payee ? b.payee + '. ' : ''}Due ${new Date(b.due_date).toDateString()}.`,
+ metadata: { category: b.category },
+ });
+ if (r) inserted += 1;
+ }
+ }
+
+ // 5) Reorders due — fire when we're at/near the repurchase date
+ const reorders = await db.query(
+ `SELECT id, name, merchant, next_due_date FROM reorder_item
+ WHERE user_id = $1 AND status = 'active' AND next_due_date IS NOT NULL`,
+ [userId]
+ );
+ for (const it of reorders.rows) {
+ const daysUntil = (new Date(it.next_due_date).getTime() - Date.now()) / 86400e3;
+ if (daysUntil <= 7) {
+ const dueAt = new Date(new Date(it.next_due_date).getTime() + 12 * 3600e3);
+ const r = await tryInsert({
+ userId, ownerTable: 'reorder_item', ownerId: it.id, dueAt,
+ reasonCode: 'reorder_due',
+ title: `Time to reorder: ${it.name}`,
+ body: `${it.merchant ? 'Usually from ' + it.merchant + '. ' : ''}Due ${new Date(it.next_due_date).toDateString()}.`,
+ });
+ if (r) inserted += 1;
+ }
+ }
+
return inserted;
}
diff --git a/tests/recurrence.test.js b/tests/recurrence.test.js
new file mode 100644
index 0000000..54fd28c
--- /dev/null
+++ b/tests/recurrence.test.js
@@ -0,0 +1,35 @@
+const { test } = require('node:test');
+const assert = require('node:assert');
+const { nextDue, nextDueDays, daysUntil } = require('../lib/recurrence');
+
+test('nextDue advances by cadence', () => {
+ assert.equal(nextDue('2026-01-15', 'weekly'), '2026-01-22');
+ assert.equal(nextDue('2026-01-15', 'biweekly'), '2026-01-29');
+ assert.equal(nextDue('2026-01-15', 'monthly'), '2026-02-15');
+ assert.equal(nextDue('2026-01-15', 'quarterly'), '2026-04-15');
+ assert.equal(nextDue('2026-01-15', 'semiannual'), '2026-07-15');
+ assert.equal(nextDue('2026-01-15', 'annual'), '2027-01-15');
+});
+
+test('nextDue returns null for one-off / unknown / missing', () => {
+ assert.equal(nextDue('2026-01-15', 'once'), null);
+ assert.equal(nextDue('2026-01-15', 'bogus'), null);
+ assert.equal(nextDue(null, 'monthly'), null);
+});
+
+test('nextDue rolls year boundary', () => {
+ assert.equal(nextDue('2026-12-20', 'monthly'), '2027-01-20');
+});
+
+test('nextDueDays adds N days', () => {
+ assert.equal(nextDueDays('2026-01-15', 30), '2026-02-14');
+ assert.equal(nextDueDays('2026-01-15', 0), null);
+ assert.equal(nextDueDays(null, 30), null);
+});
+
+test('daysUntil sign', () => {
+ const now = new Date('2026-01-15T12:00:00Z');
+ assert.equal(daysUntil('2026-01-20', now), 5);
+ assert.equal(daysUntil('2026-01-10', now), -5);
+ assert.equal(daysUntil('2026-01-15', now), 0);
+});
← 7db85d7 chore: move dev port 9931 -> 9774 (9931 held by japan-enrich
·
back to AbramsOS
·
feat(ui): Bills, Reorders, and unified Deadlines pages 43aea4b →