← back to AbramsOS
auto-save: 2026-07-08T14:36:14 (5 files) — lib/ids.js db/migrations/0009_prescription_fills.sql routes/prescriptions.js scripts/load-abrams-rx.js views/prescriptions.ejs
658964747a3e63050583027e25feeea9c8bccc7c · 2026-07-08 14:36:18 -0700 · Steve Abrams
Files touched
A db/migrations/0009_prescription_fills.sqlM lib/ids.jsA routes/prescriptions.jsA scripts/load-abrams-rx.jsA views/prescriptions.ejs
Diff
commit 658964747a3e63050583027e25feeea9c8bccc7c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 8 14:36:18 2026 -0700
auto-save: 2026-07-08T14:36:14 (5 files) — lib/ids.js db/migrations/0009_prescription_fills.sql routes/prescriptions.js scripts/load-abrams-rx.js views/prescriptions.ejs
---
db/migrations/0009_prescription_fills.sql | 35 +++++++++
lib/ids.js | 1 +
routes/prescriptions.js | 34 +++++++++
scripts/load-abrams-rx.js | 117 ++++++++++++++++++++++++++++++
views/prescriptions.ejs | 87 ++++++++++++++++++++++
5 files changed, 274 insertions(+)
diff --git a/db/migrations/0009_prescription_fills.sql b/db/migrations/0009_prescription_fills.sql
new file mode 100644
index 0000000..08d632b
--- /dev/null
+++ b/db/migrations/0009_prescription_fills.sql
@@ -0,0 +1,35 @@
+-- 0009_prescription_fills.sql
+-- prescription_fill — pharmacy FILL HISTORY (one row per fill), distinct from `medication`
+-- (one row per current med). Imported from a pharmacy "PAT PROFILE FOR TAX" summary.
+-- Health data: user-supplied record, audit-logged with a medical-consent marker.
+-- Idempotent. Safe to re-run.
+
+BEGIN;
+
+CREATE TABLE IF NOT EXISTS prescription_fill (
+ id text PRIMARY KEY,
+ user_id text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
+ person_id text REFERENCES person(id) ON DELETE SET NULL, -- whose fill (null = account owner)
+ fill_date date,
+ drug_name text NOT NULL, -- as printed, e.g. "FLUOXETINE HCL 20 MG CAPS"
+ rx_number text,
+ refill int,
+ quantity numeric,
+ days_supply int,
+ doctor text, -- prescriber "LAST, FIRST"
+ pharmacy text,
+ ndc text,
+ dea text,
+ plan_name text, -- CARE33 / TRIO ACO HMO
+ plan_paid numeric(12,2), -- what the insurance PLAN paid
+ patient_paid numeric(12,2), -- out-of-pocket (0.00 on this profile)
+ source text, -- provenance tag (idempotent re-load key)
+ raw_text text, -- original OCR line pair (audit trail)
+ metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
+ created_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS prescription_fill_user_idx ON prescription_fill (user_id, fill_date DESC);
+CREATE INDEX IF NOT EXISTS prescription_fill_person_idx ON prescription_fill (person_id);
+CREATE INDEX IF NOT EXISTS prescription_fill_source_idx ON prescription_fill (source);
+
+COMMIT;
diff --git a/lib/ids.js b/lib/ids.js
index 4f414f3..d1d8af1 100644
--- a/lib/ids.js
+++ b/lib/ids.js
@@ -15,6 +15,7 @@ const PREFIX = {
person: 'prsn',
medication: 'med',
medrecall: 'mrcl',
+ prescription_fill: 'rxfl',
};
function id(kind) {
diff --git a/routes/prescriptions.js b/routes/prescriptions.js
new file mode 100644
index 0000000..393e7db
--- /dev/null
+++ b/routes/prescriptions.js
@@ -0,0 +1,34 @@
+// Prescriptions — pharmacy FILL HISTORY viewer (read-only). Rows come from prescription_fill,
+// imported from a pharmacy "PAT PROFILE FOR TAX" summary. Health data: display only.
+const express = require('express');
+const db = require('../lib/db');
+
+const router = express.Router();
+const DEV_USER_ID = 'user_steve';
+
+async function load(userId) {
+ const fills = (await db.query(
+ `SELECT pf.*, p.full_name AS person_name
+ FROM prescription_fill pf LEFT JOIN person p ON p.id = pf.person_id
+ WHERE pf.user_id = $1
+ ORDER BY pf.fill_date DESC NULLS LAST, pf.drug_name ASC`, [userId])).rows;
+ const totals = (await db.query(
+ `SELECT coalesce(p.full_name,'—') AS person, count(*)::int AS fills,
+ coalesce(sum(pf.plan_paid),0)::numeric(12,2) AS plan_paid,
+ coalesce(sum(pf.patient_paid),0)::numeric(12,2) AS patient_paid,
+ min(pf.fill_date) AS first_fill, max(pf.fill_date) AS last_fill
+ FROM prescription_fill pf LEFT JOIN person p ON p.id = pf.person_id
+ WHERE pf.user_id = $1
+ GROUP BY p.full_name ORDER BY 1`, [userId])).rows;
+ return { fills, totals };
+}
+
+router.get('/prescriptions', async (_req, res) => {
+ res.render('prescriptions', await load(DEV_USER_ID));
+});
+
+router.get('/api/prescriptions', async (_req, res) => {
+ res.json(await load(DEV_USER_ID));
+});
+
+module.exports = router;
diff --git a/scripts/load-abrams-rx.js b/scripts/load-abrams-rx.js
new file mode 100644
index 0000000..df1a3c7
--- /dev/null
+++ b/scripts/load-abrams-rx.js
@@ -0,0 +1,117 @@
+#!/usr/bin/env node
+// One-off importer: Shangoo Pharmacy "PAT PROFILE FOR TAX" (2023-07-08 → 2026-07-08)
+// → person (Steve, Natalia) + prescription_fill (every dated fill) + medication (deduped list).
+//
+// Source data: /tmp/abrams_fills.json (OCR-parsed from ~/Downloads/abrams.pdf, reconciled
+// to the document totals: Natalia 220 fills / plan-paid $136,328.74). Idempotent — re-running
+// replaces this source's fills (SOURCE tag) and only adds medications that don't already exist.
+//
+// Usage: node scripts/load-abrams-rx.js [path-to-fills.json]
+
+const fs = require('fs');
+const db = require('../lib/db');
+const audit = require('../lib/audit');
+const { id } = require('../lib/ids');
+
+const USER_ID = 'user_steve';
+const SOURCE = 'shangoo-pharmacy-tax-2023-2026';
+const PHARMACY = 'Shangoo Pharmacy';
+const FILE = process.argv[2] || '/tmp/abrams_fills.json';
+
+// PDF header identity → person records
+const PEOPLE = {
+ 'Steve Abrams': { relation: 'self', dob: '1971-01-03' },
+ 'Natalia Abrams': { relation: 'spouse', dob: '1980-04-25' },
+};
+const ADDR = '18406 Bessemer St, Reseda, CA 91335';
+
+const num = (v) => (v == null || v === '' ? null : Number(v));
+
+async function findOrCreatePerson(fullName) {
+ const found = await db.query(
+ `SELECT id FROM person WHERE user_id=$1 AND lower(full_name)=lower($2) LIMIT 1`, [USER_ID, fullName]);
+ if (found.rows[0]) return found.rows[0].id;
+ const meta = PEOPLE[fullName] || { relation: 'other', dob: null };
+ const pid = id('person');
+ await db.query(
+ `INSERT INTO person (id, user_id, relation, full_name, dob, notes)
+ VALUES ($1,$2,$3,$4,$5,$6)`,
+ [pid, USER_ID, meta.relation, fullName, meta.dob, `Imported from ${PHARMACY} tax profile. ${ADDR}`]);
+ console.log(` + person: ${fullName} (${meta.relation})`);
+ return pid;
+}
+
+// strip a strength/dose out of the printed drug string for the medication list
+const doseOf = (d) => {
+ const m = String(d || '').match(/\b(\d[\d.]*\s?(?:MG|MCG|UNIT|%|ML)[\/\-\d.A-Z ]*?)(?:\s+(?:TABS?|CAPS?|CPEP|TAB|CAP|SPR|GEL|SUS|STRP|ODT|CPDR|SUSP)\b|$)/i);
+ return m ? m[1].trim() : null;
+};
+// dedup key for the medication list — uppercase, collapse ws, drop stray punctuation
+const medKey = (d) => String(d || '').toUpperCase().replace(/[.,;:]/g, '').replace(/\s+/g, ' ').trim();
+
+async function main() {
+ const fills = JSON.parse(fs.readFileSync(FILE, 'utf8'));
+ console.log(`loading ${fills.length} fills from ${FILE}`);
+
+ // 1. people
+ const personId = {};
+ for (const name of new Set(fills.map((f) => f.person).filter(Boolean))) {
+ personId[name] = await findOrCreatePerson(name);
+ }
+
+ // 2. fills — idempotent: clear this source first, then insert all
+ await db.query(`DELETE FROM prescription_fill WHERE user_id=$1 AND source=$2`, [USER_ID, SOURCE]);
+ let n = 0;
+ for (const f of fills) {
+ await db.query(
+ `INSERT INTO prescription_fill
+ (id,user_id,person_id,fill_date,drug_name,rx_number,refill,quantity,days_supply,doctor,pharmacy,ndc,dea,plan_name,plan_paid,patient_paid,source,raw_text)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)`,
+ [id('prescription_fill'), USER_ID, personId[f.person] || null, f.date || null, f.drug || '(unknown)',
+ f.rx_number || null, num(f.refill), num(f.qty), num(f.days_supply), f.doctor || null, PHARMACY,
+ f.ndc || null, f.dea || null, f.plan || null, num(f.cost), 0.00, SOURCE, f.raw || null]);
+ n++;
+ }
+ console.log(` loaded ${n} prescription_fill rows`);
+
+ // 3. deduped medication list (skip the DELIVERY FEE service line-items)
+ const seenMedKeys = new Set(
+ (await db.query(`SELECT person_id, upper(name) u FROM medication WHERE user_id=$1`, [USER_ID]))
+ .rows.map((r) => `${r.person_id}|${r.u}`));
+ const groups = new Map(); // `${personId}|${medKey}` → {person_id, name, latest, earliest, doctor, rx}
+ for (const f of fills) {
+ if (!f.drug || /DELIVERY FEE/i.test(f.drug)) continue;
+ const pid = personId[f.person] || null;
+ const k = `${pid}|${medKey(f.drug)}`;
+ const g = groups.get(k) || { person_id: pid, name: f.drug, earliest: f.date, latest: f.date, doctor: f.doctor, rx: f.rx_number };
+ if (f.date && (!g.latest || f.date > g.latest)) { g.latest = f.date; g.doctor = f.doctor || g.doctor; g.rx = f.rx_number || g.rx; }
+ if (f.date && (!g.earliest || f.date < g.earliest)) g.earliest = f.date;
+ groups.set(k, g);
+ }
+ let med = 0;
+ for (const [k, g] of groups) {
+ if (seenMedKeys.has(`${g.person_id}|${g.name.toUpperCase()}`)) continue;
+ await db.query(
+ `INSERT INTO medication (id,user_id,person_id,name,dosage,prescriber,pharmacy,rx_number,start_date,is_active,notes)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
+ [id('medication'), USER_ID, g.person_id, g.name, doseOf(g.name), g.doctor || null, PHARMACY,
+ g.rx || null, g.earliest || null, true, `Auto-derived from ${PHARMACY} fill history (last fill ${g.latest}).`]);
+ med++;
+ }
+ console.log(` added ${med} deduped medication rows (of ${groups.size} unique drugs)`);
+
+ // 4. audit
+ await audit.log({ actorType: 'user', actorId: USER_ID, objectType: 'prescription_fill', objectId: SOURCE,
+ eventType: 'prescription_import', metadata: { medical: true, consent: 'user-supplied', source: SOURCE, fills: n, medications: med } });
+
+ // 5. reconciliation summary
+ const sum = await db.query(
+ `SELECT p.full_name, count(*)::int fills, coalesce(sum(pf.plan_paid),0)::numeric(12,2) plan_paid
+ FROM prescription_fill pf LEFT JOIN person p ON p.id=pf.person_id
+ WHERE pf.user_id=$1 AND pf.source=$2 GROUP BY p.full_name ORDER BY 1`, [USER_ID, SOURCE]);
+ console.log('\nReconciliation:');
+ for (const r of sum.rows) console.log(` ${r.full_name}: ${r.fills} fills · plan-paid $${Number(r.plan_paid).toLocaleString()}`);
+ await db.pool?.end?.();
+}
+
+main().then(() => process.exit(0)).catch((e) => { console.error('LOAD FAILED:', e.message); process.exit(1); });
diff --git a/views/prescriptions.ejs b/views/prescriptions.ejs
new file mode 100644
index 0000000..38a7633
--- /dev/null
+++ b/views/prescriptions.ejs
@@ -0,0 +1,87 @@
+<%- include('partials/header', { title: 'Prescriptions' }) %>
+
+<section class="page-head">
+ <div>
+ <h1>Prescriptions</h1>
+ <p class="subtle">
+ <%= fills.length %> fills imported from the <strong>Shangoo Pharmacy</strong> tax profile
+ (Jul 2023 → Jul 2026). The <strong>$</strong> column is what the <em>insurance plan</em> paid —
+ your out-of-pocket on this profile is <strong>$0.00</strong>.
+ </p>
+ </div>
+ <div class="grid-controls">
+ <label>Person
+ <select id="pfPerson">
+ <option value="">All</option>
+ <% totals.forEach(function(t){ %><option value="<%= t.person %>"><%= t.person %></option><% }) %>
+ </select>
+ </label>
+ <label>Find <input id="pfFind" type="search" placeholder="drug, doctor, rx#…"></label>
+ </div>
+</section>
+
+<section class="cards">
+ <% totals.forEach(function(t){ %>
+ <div class="glass card">
+ <h3><%= t.person %></h3>
+ <p class="big"><%= t.fills %> <span class="subtle">fills</span></p>
+ <p class="subtle">Plan paid <strong>$<%= Number(t.plan_paid).toLocaleString(undefined,{minimumFractionDigits:2,maximumFractionDigits:2}) %></strong>
+ · Out-of-pocket $<%= Number(t.patient_paid).toLocaleString(undefined,{minimumFractionDigits:2,maximumFractionDigits:2}) %></p>
+ <p class="subtle"><%= t.first_fill ? String(t.first_fill).slice(0,10) : '' %> → <%= t.last_fill ? String(t.last_fill).slice(0,10) : '' %></p>
+ </div>
+ <% }) %>
+</section>
+
+<section class="glass" style="overflow:auto">
+ <table class="rx-table" id="pfTable">
+ <thead>
+ <tr>
+ <th>Date</th><th>Person</th><th>Drug</th><th>Rx #</th><th>Qty</th><th>Days</th>
+ <th>Doctor</th><th>Plan</th><th class="num">Plan paid</th>
+ </tr>
+ </thead>
+ <tbody>
+ <% fills.forEach(function(f){ %>
+ <tr data-person="<%= f.person_name || '' %>"
+ data-find="<%= ((f.drug_name||'')+' '+(f.doctor||'')+' '+(f.rx_number||'')).toLowerCase() %>">
+ <td class="mono"><%= f.fill_date ? String(f.fill_date).slice(0,10) : '' %></td>
+ <td><%= f.person_name || '' %></td>
+ <td><%= f.drug_name %></td>
+ <td class="mono"><%= f.rx_number || '' %><% if (f.refill) { %><span class="subtle"> · rf<%= f.refill %></span><% } %></td>
+ <td class="num"><%= f.quantity != null ? f.quantity : '' %></td>
+ <td class="num"><%= f.days_supply != null ? f.days_supply : '' %></td>
+ <td><%= f.doctor || '' %></td>
+ <td class="subtle"><%= f.plan_name || '' %></td>
+ <td class="num"><% if (f.plan_paid != null) { %>$<%= Number(f.plan_paid).toLocaleString(undefined,{minimumFractionDigits:2,maximumFractionDigits:2}) %><% } %></td>
+ </tr>
+ <% }) %>
+ </tbody>
+ </table>
+</section>
+
+<style>
+ .cards { display:flex; gap:12px; flex-wrap:wrap; margin:14px 0; }
+ .cards .card { padding:14px 16px; min-width:240px; }
+ .cards .card h3 { margin:0 0 6px; font-size:14px; }
+ .cards .big { font-size:22px; font-weight:700; margin:2px 0; }
+ .rx-table { width:100%; border-collapse:collapse; font-size:13px; }
+ .rx-table th, .rx-table td { text-align:left; padding:6px 10px; border-bottom:1px solid rgba(255,255,255,.07); white-space:nowrap; }
+ .rx-table th { position:sticky; top:0; background:rgba(20,20,26,.96); font-size:11px; letter-spacing:.04em; text-transform:uppercase; color:var(--muted,#9a9aa2); }
+ .rx-table td.num, .rx-table th.num { text-align:right; }
+ .rx-table td.mono { font-variant-numeric:tabular-nums; }
+ .rx-table tbody tr:hover { background:rgba(255,255,255,.03); }
+</style>
+<script>
+ (function(){
+ var per = document.getElementById('pfPerson'), find = document.getElementById('pfFind');
+ var rows = Array.prototype.slice.call(document.querySelectorAll('#pfTable tbody tr'));
+ function apply(){
+ var p = per.value, q = (find.value||'').toLowerCase().trim();
+ rows.forEach(function(r){
+ var ok = (!p || r.getAttribute('data-person') === p) && (!q || r.getAttribute('data-find').indexOf(q) >= 0);
+ r.style.display = ok ? '' : 'none';
+ });
+ }
+ per.addEventListener('change', apply); find.addEventListener('input', apply);
+ })();
+</script>
← 63d53f9 feat(health): Household (people/spouse) + Medications with F
·
back to AbramsOS
·
Import Shangoo Pharmacy tax profile: prescription_fill table 542a632 →