← back to AbramsOS
tick 5: calendar_reminder table + reminder engine
b6f84e0036578e9fe8408468ce97436e46862bf9 · 2026-05-10 00:35:14 -0700 · Steve
- db/migrations/0003_reminders.sql: calendar_reminder + dedupe unique index on
(user_id, owner_table, owner_id, reason_code) so reruns are no-ops
- lib/reminder-engine.js: 3 deadline types fire today
· returns_window_closing (5d before 30d default returns mark)
· warranty_expiry (30d before 1y default mark)
· recall_action_due (14d after a recall_match lands)
- routes/reminders.js: /api/reminders/upcoming, /:id/dismiss, /regenerate
- 4 new tests (uses fixture user_steve + tracked test purchase ids)
- 28/29 tests green (1 skip — pdf-parse fixture)
Files touched
A db/migrations/0003_reminders.sqlA lib/reminder-engine.jsA routes/reminders.jsM server.jsA tests/reminder-engine.test.js
Diff
commit b6f84e0036578e9fe8408468ce97436e46862bf9
Author: Steve <steve@designerwallcoverings.com>
Date: Sun May 10 00:35:14 2026 -0700
tick 5: calendar_reminder table + reminder engine
- db/migrations/0003_reminders.sql: calendar_reminder + dedupe unique index on
(user_id, owner_table, owner_id, reason_code) so reruns are no-ops
- lib/reminder-engine.js: 3 deadline types fire today
· returns_window_closing (5d before 30d default returns mark)
· warranty_expiry (30d before 1y default mark)
· recall_action_due (14d after a recall_match lands)
- routes/reminders.js: /api/reminders/upcoming, /:id/dismiss, /regenerate
- 4 new tests (uses fixture user_steve + tracked test purchase ids)
- 28/29 tests green (1 skip — pdf-parse fixture)
---
db/migrations/0003_reminders.sql | 24 +++++++++
lib/reminder-engine.js | 114 +++++++++++++++++++++++++++++++++++++++
routes/reminders.js | 40 ++++++++++++++
server.js | 2 +
tests/reminder-engine.test.js | 68 +++++++++++++++++++++++
5 files changed, 248 insertions(+)
diff --git a/db/migrations/0003_reminders.sql b/db/migrations/0003_reminders.sql
new file mode 100644
index 0000000..792bf68
--- /dev/null
+++ b/db/migrations/0003_reminders.sql
@@ -0,0 +1,24 @@
+-- 0003_reminders.sql — calendar_reminder table
+
+BEGIN;
+
+CREATE TABLE IF NOT EXISTS calendar_reminder (
+ id text PRIMARY KEY,
+ user_id text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
+ owner_table text, -- 'purchase', 'recall_match', etc.
+ owner_id text,
+ due_at timestamptz NOT NULL,
+ reason_code text NOT NULL, -- 'returns_window_closing', 'warranty_expiry', 'recall_action_due'
+ title text NOT NULL,
+ body text,
+ calendar_event_id text, -- when synced to Google Calendar (later)
+ state text NOT NULL DEFAULT 'pending', -- pending | dismissed | resolved
+ created_at timestamptz NOT NULL DEFAULT now(),
+ reviewed_at timestamptz,
+ metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb
+);
+CREATE INDEX IF NOT EXISTS calendar_reminder_due_idx ON calendar_reminder (user_id, state, due_at);
+CREATE INDEX IF NOT EXISTS calendar_reminder_owner_idx ON calendar_reminder (owner_table, owner_id);
+CREATE UNIQUE INDEX IF NOT EXISTS calendar_reminder_dedupe ON calendar_reminder (user_id, owner_table, owner_id, reason_code);
+
+COMMIT;
diff --git a/lib/reminder-engine.js b/lib/reminder-engine.js
new file mode 100644
index 0000000..7356991
--- /dev/null
+++ b/lib/reminder-engine.js
@@ -0,0 +1,114 @@
+// Reminder engine. Scans purchases + recall_match rows; emits calendar_reminder rows.
+// Idempotent — the (user_id, owner_table, owner_id, reason_code) UNIQUE index dedupes.
+
+const db = require('./db');
+const audit = require('./audit');
+const { id } = require('./ids');
+
+// Default windows. Real merchant policies live in service_commitment / coverage_policy
+// (Phase 5 in the roadmap); until then we use pessimistic defaults.
+const DEFAULT_RETURNS_DAYS = 30;
+const DEFAULT_WARRANTY_DAYS = 365;
+const RECALL_ACTION_DAYS = 14;
+
+async function generateForUser(userId) {
+ let inserted = 0;
+
+ // 1) Returns-window closing — fire 5 days before window closes
+ const purchases = await db.query(
+ `SELECT id, merchant_name, purchase_date, total_amount, currency
+ FROM purchase WHERE user_id = $1 AND purchase_date IS NOT NULL`,
+ [userId]
+ );
+ for (const p of purchases.rows) {
+ const purchaseDate = new Date(p.purchase_date);
+ const returnsClose = new Date(purchaseDate.getTime() + DEFAULT_RETURNS_DAYS * 86400e3);
+ const daysUntilClose = (returnsClose - Date.now()) / 86400e3;
+ if (daysUntilClose > 0 && daysUntilClose <= 7) {
+ const r = await tryInsert({
+ userId,
+ ownerTable: 'purchase',
+ ownerId: p.id,
+ dueAt: returnsClose,
+ reasonCode: 'returns_window_closing',
+ title: `Return window for ${p.merchant_name} closes in ${Math.ceil(daysUntilClose)}d`,
+ body: `If you intend to return this $${p.total_amount} purchase, do it before ${returnsClose.toDateString()}.`,
+ });
+ if (r) inserted += 1;
+ }
+
+ // 2) Warranty expiry — fire 30 days before
+ const warrantyExp = new Date(purchaseDate.getTime() + DEFAULT_WARRANTY_DAYS * 86400e3);
+ const daysUntilWarranty = (warrantyExp - Date.now()) / 86400e3;
+ if (daysUntilWarranty > 0 && daysUntilWarranty <= 30) {
+ const r = await tryInsert({
+ userId,
+ ownerTable: 'purchase',
+ ownerId: p.id,
+ dueAt: warrantyExp,
+ reasonCode: 'warranty_expiry',
+ title: `${p.merchant_name} warranty expires in ${Math.ceil(daysUntilWarranty)}d`,
+ body: `Default 1-year manufacturer warranty assumption. If you have an issue, file before ${warrantyExp.toDateString()}.`,
+ });
+ if (r) inserted += 1;
+ }
+ }
+
+ // 3) Recall actions — fire 14 days from match creation
+ const matches = await db.query(
+ `SELECT rm.id, rm.recall_id, re.title AS recall_title, re.url, re.remedy, rm.matched_at, rm.asset_id
+ FROM recall_match rm JOIN recall_event re ON re.id = rm.recall_id
+ WHERE rm.user_id = $1 AND rm.status = 'pending_review'`,
+ [userId]
+ );
+ for (const m of matches.rows) {
+ const dueAt = new Date(new Date(m.matched_at).getTime() + RECALL_ACTION_DAYS * 86400e3);
+ const r = await tryInsert({
+ userId,
+ ownerTable: 'recall_match',
+ ownerId: m.id,
+ dueAt,
+ reasonCode: 'recall_action_due',
+ title: `Recall match needs your review: ${m.recall_title || 'CPSC notice'}`,
+ body: `Remedy: ${m.remedy || 'see notice'}. Source: ${m.url || 'CPSC'}.`,
+ });
+ if (r) inserted += 1;
+ }
+
+ return inserted;
+}
+
+async function tryInsert({ userId, ownerTable, ownerId, dueAt, reasonCode, title, body, metadata = {} }) {
+ try {
+ const reminderId = id('document'); // reuse ulid prefix
+ await db.query(
+ `INSERT INTO calendar_reminder (id, user_id, owner_table, owner_id, due_at, reason_code, title, body, metadata_jsonb)
+ VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
+ ON CONFLICT (user_id, owner_table, owner_id, reason_code) DO NOTHING`,
+ [reminderId, userId, ownerTable, ownerId, dueAt, reasonCode, title, body, metadata]
+ );
+ await audit.log({
+ actorType: 'system',
+ objectType: 'calendar_reminder',
+ objectId: reminderId,
+ eventType: 'reminder_generated',
+ metadata: { reason_code: reasonCode, owner_table: ownerTable, owner_id: ownerId, due_at: dueAt },
+ });
+ return true;
+ } catch (err) {
+ return false;
+ }
+}
+
+async function upcomingForUser(userId, { days = 30 } = {}) {
+ const r = await db.query(
+ `SELECT id, owner_table, owner_id, due_at, reason_code, title, body, state
+ FROM calendar_reminder
+ WHERE user_id = $1 AND state = 'pending' AND due_at < (now() + ($2 || ' days')::interval)
+ ORDER BY due_at ASC`,
+ [userId, String(days)]
+ );
+ return r.rows;
+}
+
+module.exports = { generateForUser, upcomingForUser, tryInsert };
diff --git a/routes/reminders.js b/routes/reminders.js
new file mode 100644
index 0000000..b53e5f6
--- /dev/null
+++ b/routes/reminders.js
@@ -0,0 +1,40 @@
+const express = require('express');
+const db = require('../lib/db');
+const audit = require('../lib/audit');
+const engine = require('../lib/reminder-engine');
+
+const router = express.Router();
+const DEV_USER_ID = 'user_steve';
+
+// Manual recompute trigger (cheap; idempotent because of the dedupe unique index)
+router.post('/api/reminders/regenerate', async (_req, res) => {
+ try {
+ const inserted = await engine.generateForUser(DEV_USER_ID);
+ res.json({ ok: true, inserted });
+ } catch (err) {
+ res.status(500).json({ error: err.message });
+ }
+});
+
+router.get('/api/reminders/upcoming', async (req, res) => {
+ const days = parseInt(req.query.days || '30', 10);
+ const rows = await engine.upcomingForUser(DEV_USER_ID, { days });
+ res.json(rows);
+});
+
+router.post('/api/reminders/:id/dismiss', async (req, res) => {
+ await db.query(
+ `UPDATE calendar_reminder SET state = 'dismissed', reviewed_at = now() WHERE id = $1 AND user_id = $2`,
+ [req.params.id, DEV_USER_ID]
+ );
+ await audit.log({
+ actorType: 'user',
+ actorId: DEV_USER_ID,
+ objectType: 'calendar_reminder',
+ objectId: req.params.id,
+ eventType: 'reminder_dismissed',
+ });
+ res.json({ ok: true });
+});
+
+module.exports = router;
diff --git a/server.js b/server.js
index 9325bde..0d39771 100644
--- a/server.js
+++ b/server.js
@@ -16,6 +16,7 @@ const purchases = require('./routes/purchases');
const importRouter = require('./routes/import');
const plaidRouter = require('./routes/plaid');
const documentsRouter = require('./routes/documents');
+const remindersRouter = require('./routes/reminders');
const app = express();
const PORT = parseInt(process.env.PORT || '9931', 10);
@@ -55,6 +56,7 @@ app.use(connectors); // /connectors, /api/connectors, sync
app.use(purchases); // /purchases, /api/purchases
app.use(plaidRouter); // /api/plaid/*
app.use(documentsRouter); // /api/documents, /api/documents/:id/parse
+app.use(remindersRouter); // /api/reminders/upcoming, regenerate, dismiss
// Step-up-required routes (must re-verify TOTP within 60s)
app.use('/import', requireStepUp, importRouter);
diff --git a/tests/reminder-engine.test.js b/tests/reminder-engine.test.js
new file mode 100644
index 0000000..ad56e5f
--- /dev/null
+++ b/tests/reminder-engine.test.js
@@ -0,0 +1,68 @@
+const test = require('node:test');
+const assert = require('node:assert');
+
+require('dotenv').config();
+const db = require('../lib/db');
+const engine = require('../lib/reminder-engine');
+
+const TEST_USER_ID = 'user_steve';
+
+test.before(async () => {
+ // Wipe any reminders the engine might recreate, but keep purchases.
+ await db.query(`DELETE FROM calendar_reminder WHERE user_id = $1`, [TEST_USER_ID]);
+});
+
+test.after(async () => {
+ await db.query(`DELETE FROM calendar_reminder WHERE user_id = $1`, [TEST_USER_ID]);
+ // Drop the synthetic purchase we insert below
+ await db.query(`DELETE FROM purchase WHERE merchant_name = 'TEST_REMINDER_FIXTURE'`);
+ await db.pool.end();
+});
+
+test('warranty_expiry reminder fires for purchase ~340 days old (≈25d before 1y mark)', async () => {
+ const purchaseDate = new Date(Date.now() - 340 * 86400e3); // 340 days ago → warranty expires ~25d from now
+ await db.query(
+ `INSERT INTO purchase (id, user_id, merchant_name, purchase_date, total_amount, currency, confidence)
+ VALUES ('test_pur_warranty', $1, 'TEST_REMINDER_FIXTURE', $2, 100, 'USD', 0.9)
+ ON CONFLICT (id) DO UPDATE SET purchase_date = EXCLUDED.purchase_date`,
+ [TEST_USER_ID, purchaseDate]
+ );
+ const inserted = await engine.generateForUser(TEST_USER_ID);
+ assert.ok(inserted >= 1, `should insert at least one reminder (got ${inserted})`);
+ const r = await db.query(
+ `SELECT reason_code FROM calendar_reminder WHERE owner_id = 'test_pur_warranty'`
+ );
+ const codes = r.rows.map(x => x.reason_code);
+ assert.ok(codes.includes('warranty_expiry'), `expected warranty_expiry in [${codes.join(',')}]`);
+});
+
+test('returns_window_closing fires for purchase ~25d old (≈5d before 30d mark)', async () => {
+ const purchaseDate = new Date(Date.now() - 25 * 86400e3);
+ await db.query(
+ `INSERT INTO purchase (id, user_id, merchant_name, purchase_date, total_amount, currency, confidence)
+ VALUES ('test_pur_returns', $1, 'TEST_REMINDER_FIXTURE', $2, 100, 'USD', 0.9)
+ ON CONFLICT (id) DO UPDATE SET purchase_date = EXCLUDED.purchase_date`,
+ [TEST_USER_ID, purchaseDate]
+ );
+ await engine.generateForUser(TEST_USER_ID);
+ const r = await db.query(
+ `SELECT reason_code FROM calendar_reminder WHERE owner_id = 'test_pur_returns'`
+ );
+ assert.ok(r.rows.some(x => x.reason_code === 'returns_window_closing'));
+});
+
+test('engine is idempotent (re-runs do not duplicate)', async () => {
+ const before = await db.query(`SELECT count(*)::int AS n FROM calendar_reminder WHERE user_id = $1`, [TEST_USER_ID]);
+ await engine.generateForUser(TEST_USER_ID);
+ await engine.generateForUser(TEST_USER_ID);
+ const after = await db.query(`SELECT count(*)::int AS n FROM calendar_reminder WHERE user_id = $1`, [TEST_USER_ID]);
+ assert.strictEqual(after.rows[0].n, before.rows[0].n, 'duplicate runs should be no-ops');
+});
+
+test('upcomingForUser returns pending reminders ordered by due_at', async () => {
+ const rows = await engine.upcomingForUser(TEST_USER_ID, { days: 400 });
+ assert.ok(rows.length >= 2, `expected ≥2 pending (got ${rows.length})`);
+ for (let i = 1; i < rows.length; i++) {
+ assert.ok(new Date(rows[i].due_at) >= new Date(rows[i-1].due_at), 'order ASC');
+ }
+});
← d95d5e5 tick 4: PDF parsing for receipt attachments (tier-3)
·
back to AbramsOS
·
tick 6: claim_case + action_queue + claim strategist (drafts 7294ca3 →