← back to AbramsOS
Add California unclaimed-property tracker to claims dashboard
2f7118568df90b584abbeb7eaffebbec56b4fceb · 2026-07-31 14:47:49 -0700 · Steve Abrams
- New unclaimed_property table (migration 0015) + seed of 5 CA properties
found for Steve on claimit.ca.gov ($131.55, status=staged). Public data
only, no SSN.
- /claims now renders a Tracked Properties table (amount/holder/address/
property-id drill hrefs, found date+time chip, per-row status select).
- API: GET /api/unclaimed, POST /api/unclaimed/:id/status (session-scoped
write, audited, CSRF-exempt like other /api routes).
- server.js: claims.abramsos.agentabrams.com/ -> /claims host redirect.
- DNS + live deploy left gated (memo in pending-approval).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/0015_unclaimed_property.sqlM routes/claims.jsA scripts/seed-unclaimed-ca.jsM server.jsM views/claims.ejs
Diff
commit 2f7118568df90b584abbeb7eaffebbec56b4fceb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jul 31 14:47:49 2026 -0700
Add California unclaimed-property tracker to claims dashboard
- New unclaimed_property table (migration 0015) + seed of 5 CA properties
found for Steve on claimit.ca.gov ($131.55, status=staged). Public data
only, no SSN.
- /claims now renders a Tracked Properties table (amount/holder/address/
property-id drill hrefs, found date+time chip, per-row status select).
- API: GET /api/unclaimed, POST /api/unclaimed/:id/status (session-scoped
write, audited, CSRF-exempt like other /api routes).
- server.js: claims.abramsos.agentabrams.com/ -> /claims host redirect.
- DNS + live deploy left gated (memo in pending-approval).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
db/migrations/0015_unclaimed_property.sql | 32 ++++++++
routes/claims.js | 68 +++++++++++++++-
scripts/seed-unclaimed-ca.js | 60 ++++++++++++++
server.js | 9 +++
views/claims.ejs | 126 ++++++++++++++++++++++++++++++
5 files changed, 294 insertions(+), 1 deletion(-)
diff --git a/db/migrations/0015_unclaimed_property.sql b/db/migrations/0015_unclaimed_property.sql
new file mode 100644
index 0000000..d4a46f0
--- /dev/null
+++ b/db/migrations/0015_unclaimed_property.sql
@@ -0,0 +1,32 @@
+-- 0015_unclaimed_property.sql
+-- Tracks state-held unclaimed property found for the user (e.g. California SCO
+-- claimit.ca.gov). Holds ONLY public property data + a status the user drives.
+-- Deliberately stores NO SSN / identity data — filing happens on the state
+-- site, never here (AGENTS.md: drafts only, no send).
+
+CREATE TABLE IF NOT EXISTS unclaimed_property (
+ id text PRIMARY KEY,
+ user_id text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
+ jurisdiction text NOT NULL DEFAULT 'US-CA', -- state program
+ property_id text NOT NULL, -- the state's property id
+ holder_name text, -- who reported it (bank, PayPal, etc.)
+ owner_name text, -- name as listed by the holder
+ co_owner text,
+ address text,
+ city text,
+ state text DEFAULT 'CA',
+ zip text,
+ amount_cents integer, -- null when undisclosed ("OVER $100")
+ amount_display text, -- raw label, e.g. '$66.50' or 'OVER $100'
+ property_type text, -- NAUPA type, e.g. 'PREMIUM REFUNDS'
+ source text DEFAULT 'claimit.ca.gov',
+ status text NOT NULL DEFAULT 'found', -- found | staged | filed | paid | not_mine
+ claim_id text, -- the state's Claim ID, once filed
+ notes text,
+ found_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now(),
+ UNIQUE (user_id, property_id)
+);
+
+CREATE INDEX IF NOT EXISTS unclaimed_property_user_status_idx
+ ON unclaimed_property (user_id, status);
diff --git a/routes/claims.js b/routes/claims.js
index fe86d0e..aae5cf6 100644
--- a/routes/claims.js
+++ b/routes/claims.js
@@ -23,7 +23,30 @@ router.get('/claims', async (_req, res) => {
);
household = p.rows.map(x => x.full_name).filter(Boolean);
} catch (_e) { household = []; }
- res.render('claims', { claims: r.rows, household });
+ // Unclaimed property — defensive: a missing/renamed table must never break the page.
+ let properties = [];
+ let ucpTotals = { count: 0, staged: 0, filed: 0, paid: 0, found: 0, totalCents: 0, totalDisplay: '$0.00' };
+ try {
+ const up = await db.query(
+ `SELECT * FROM unclaimed_property WHERE user_id = $1 ORDER BY (amount_cents IS NULL), amount_cents DESC`,
+ [DEV_USER_ID]
+ );
+ properties = up.rows;
+ const totalCents = properties.reduce((sum, p) => sum + (p.amount_cents || 0), 0);
+ ucpTotals = {
+ count: properties.length,
+ staged: properties.filter(p => p.status === 'staged').length,
+ filed: properties.filter(p => p.status === 'filed').length,
+ paid: properties.filter(p => p.status === 'paid').length,
+ found: properties.filter(p => p.status === 'found').length,
+ totalCents,
+ totalDisplay: '$' + (totalCents / 100).toFixed(2),
+ };
+ } catch (_e) {
+ properties = [];
+ ucpTotals = { count: 0, staged: 0, filed: 0, paid: 0, found: 0, totalCents: 0, totalDisplay: '$0.00' };
+ }
+ res.render('claims', { claims: r.rows, household, properties, ucpTotals });
});
// HTML — detail view
@@ -78,4 +101,47 @@ router.post('/api/claims/:caseId/actions/:actionId/approve', async (req, res) =>
res.json({ ok: true });
});
+// JSON — unclaimed property list
+router.get('/api/unclaimed', async (req, res) => {
+ try {
+ const userId = req.userId || DEV_USER_ID;
+ const r = await db.query(
+ `SELECT * FROM unclaimed_property WHERE user_id = $1 ORDER BY (amount_cents IS NULL), amount_cents DESC`,
+ [userId]
+ );
+ res.json(r.rows);
+ } catch (err) {
+ res.status(500).json({ error: 'db error' });
+ }
+});
+
+// Update an unclaimed-property record's status (and optionally link a claim_id).
+router.post('/api/unclaimed/:id/status', async (req, res) => {
+ try {
+ const userId = req.userId || DEV_USER_ID; // authorize the write against the session user, not a constant
+ const { id } = req.params;
+ const { status, claim_id } = req.body || {};
+ const allowed = ['found', 'staged', 'filed', 'paid', 'not_mine'];
+ if (!allowed.includes(status)) {
+ return res.status(400).json({ error: 'invalid status' });
+ }
+ await db.query(
+ `UPDATE unclaimed_property SET status = $1, claim_id = COALESCE($2, claim_id), updated_at = now()
+ WHERE id = $3 AND user_id = $4`,
+ [status, claim_id || null, id, userId]
+ );
+ await audit.log({
+ actorType: 'user',
+ actorId: userId,
+ objectType: 'unclaimed_property',
+ objectId: id,
+ eventType: 'ucp_status_changed',
+ metadata: { status, claim_id },
+ });
+ res.json({ ok: true });
+ } catch (err) {
+ res.status(400).json({ error: err.message });
+ }
+});
+
module.exports = router;
diff --git a/scripts/seed-unclaimed-ca.js b/scripts/seed-unclaimed-ca.js
new file mode 100644
index 0000000..314b2ab
--- /dev/null
+++ b/scripts/seed-unclaimed-ca.js
@@ -0,0 +1,60 @@
+#!/usr/bin/env node
+// Seed the California unclaimed-property records found for Steve on 2026-07-31
+// via claimit.ca.gov. Idempotent: upserts on (user_id, property_id).
+// Public property data only — no SSN / identity. Status defaults to 'staged'
+// because these are already selected in the claimit.ca.gov claim cart
+// (relationship = "Myself"), awaiting Steve's FILE CLAIM + signature.
+
+require('dotenv').config();
+const db = require('../lib/db');
+
+const USER_ID = process.env.UCP_USER_ID || 'user_steve';
+
+const PROPERTIES = [
+ { property_id: '1024857116', holder_name: 'Grace Class Action', owner_name: 'ABRAMS STEVE', address: '1501 S Durango Ave', city: 'Los Angeles', zip: '90035', amount_display: '$30.43', property_type: 'Misc Outstanding Checks' },
+ { property_id: '972437075', holder_name: 'PayPal Inc', owner_name: 'ABRAMS STEVE', address: '1482 Shenandoah Ave #203', city: 'Los Angeles', zip: '90035', amount_display: '$66.50', property_type: 'Misc Intangible Prop' },
+ { property_id: '964119998', holder_name: 'New Hampshire Indemnity Co', owner_name: 'STEVE ABRAMS', address: '1501 S Durango Ave', city: 'Los Angeles', zip: '90035', amount_display: '$5.00', property_type: 'Premium Refunds' },
+ { property_id: '1041807865', holder_name: 'Google Payment Corporation', owner_name: 'ABRAMS STEVE', address: '15442 Ventura Blvd #201', city: 'Sherman Oaks', zip: '91403', amount_display: '$9.15', property_type: 'Credit Bal - Accts Receivable' },
+ { property_id: '961308880', holder_name: 'UBS Financial Services', owner_name: 'ABRAMS STEVE', address: '914 20th St Apt A', city: 'Santa Monica', zip: '90403', amount_display: '$20.47', property_type: 'Credit Balances' },
+];
+
+function centsFrom(display) {
+ const m = String(display).match(/\$?([\d,]+)\.(\d{2})/);
+ if (!m) return null;
+ return parseInt(m[1].replace(/,/g, ''), 10) * 100 + parseInt(m[2], 10);
+}
+
+async function main() {
+ let n = 0;
+ for (const p of PROPERTIES) {
+ await db.query(
+ `INSERT INTO unclaimed_property
+ (id, user_id, jurisdiction, property_id, holder_name, owner_name, address, city, state, zip,
+ amount_cents, amount_display, property_type, source, status)
+ VALUES ($1,$2,'US-CA',$3,$4,$5,$6,$7,'CA',$8,$9,$10,$11,'claimit.ca.gov','staged')
+ ON CONFLICT (user_id, property_id) DO UPDATE SET
+ holder_name = EXCLUDED.holder_name,
+ owner_name = EXCLUDED.owner_name,
+ address = EXCLUDED.address,
+ city = EXCLUDED.city,
+ zip = EXCLUDED.zip,
+ amount_cents = EXCLUDED.amount_cents,
+ amount_display = EXCLUDED.amount_display,
+ property_type = EXCLUDED.property_type,
+ updated_at = now()`,
+ [
+ `ucp_${p.property_id}`, USER_ID, p.property_id, p.holder_name, p.owner_name,
+ p.address, p.city, p.zip, centsFrom(p.amount_display), p.amount_display, p.property_type,
+ ]
+ );
+ n++;
+ }
+ const tot = await db.query(
+ `SELECT count(*)::int AS c, COALESCE(sum(amount_cents),0)::int AS cents
+ FROM unclaimed_property WHERE user_id = $1`, [USER_ID]);
+ console.log(`seeded/updated ${n} CA unclaimed-property rows`);
+ console.log(`total tracked: ${tot.rows[0].c} properties, $${(tot.rows[0].cents / 100).toFixed(2)}`);
+ process.exit(0);
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
diff --git a/server.js b/server.js
index dac508a..1b0e16c 100644
--- a/server.js
+++ b/server.js
@@ -48,6 +48,15 @@ app.use(express.json({ limit: '2mb' }));
app.use(express.urlencoded({ extended: true }));
app.use(cookieParser(process.env.SESSION_SECRET || 'dev-only-rotate-me'));
+// claims.abramsos.agentabrams.com → the claims dashboard (unclaimed property + claims)
+app.use((req, res, next) => {
+ const host = (req.hostname || '').toLowerCase();
+ if (host === 'claims.abramsos.agentabrams.com' && (req.path === '/' || req.path === '')) {
+ return res.redirect(302, '/claims');
+ }
+ next();
+});
+
// Load DB-backed session for every request (no-op if cookie missing)
app.use(loadSessionMiddleware);
diff --git a/views/claims.ejs b/views/claims.ejs
index 19b4835..3219924 100644
--- a/views/claims.ejs
+++ b/views/claims.ejs
@@ -41,6 +41,132 @@
<% } %>
</section>
+<%
+ var properties = (typeof properties !== 'undefined' && properties) ? properties : [];
+ var ucpTotals = (typeof ucpTotals !== 'undefined' && ucpTotals) ? ucpTotals : { count: 0, staged: 0, filed: 0, paid: 0, found: 0, totalCents: 0, totalDisplay: '$0.00' };
+ var statusOptions = ['found', 'staged', 'filed', 'paid', 'not_mine'];
+%>
+<% if (properties.length) { %>
+ <section class="glass" style="padding:16px 18px;margin-bottom:20px;display:flex;flex-direction:column;gap:12px">
+ <header style="display:flex;align-items:center;justify-content:space-between;gap:12px;flex-wrap:wrap">
+ <span class="name" style="display:flex;align-items:center;gap:10px">
+ <span class="icon">💰</span>
+ <strong>Tracked Properties</strong>
+ </span>
+ <span class="subtle" style="font-size:12px;color:var(--text-dim);display:flex;align-items:center;gap:10px;flex-wrap:wrap">
+ <strong style="color:inherit"><%= ucpTotals.count %></strong> propert<%= ucpTotals.count === 1 ? 'y' : 'ies' %>
+ · <strong style="color:#7dd88a"><%= ucpTotals.totalDisplay %></strong> total
+ <span style="opacity:.7">·</span>
+ <span title="staged">staged <strong><%= ucpTotals.staged %></strong></span>
+ <span style="opacity:.7">·</span>
+ <span title="filed">filed <strong><%= ucpTotals.filed %></strong></span>
+ <span style="opacity:.7">·</span>
+ <span title="paid">paid <strong><%= ucpTotals.paid %></strong></span>
+ </span>
+ </header>
+ <div style="overflow-x:auto">
+ <table style="width:100%;border-collapse:collapse;font-size:12px">
+ <thead>
+ <tr style="text-align:left;color:var(--text-dim);text-transform:uppercase;letter-spacing:.05em;font-size:11px">
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Amount</th>
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Holder</th>
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Address</th>
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Type</th>
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Property ID</th>
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Status</th>
+ <th style="padding:8px 10px;border-bottom:1px solid var(--border,#3a3a3a)">Found</th>
+ </tr>
+ </thead>
+ <tbody>
+ <% properties.forEach(function (p) {
+ var st = p.status || 'found';
+ var stColor = st === 'paid' ? '#7dd88a' : st === 'staged' ? '#e0b24a' : st === 'not_mine' ? 'var(--text-dim,#8a8a8a)' : 'inherit';
+ var addrParts = [];
+ if (p.address) addrParts.push(p.address);
+ var cityLine = [p.city, [p.state, p.zip].filter(Boolean).join(' ')].filter(Boolean).join(', ');
+ if (cityLine) addrParts.push(cityLine);
+ var foundIso = p.found_at ? String(p.found_at) : '';
+ var foundLabel = '';
+ if (p.found_at) {
+ try { foundLabel = new Date(p.found_at).toLocaleString(undefined, { year:'numeric', month:'short', day:'numeric', hour:'numeric', minute:'2-digit' }); }
+ catch (e) { foundLabel = foundIso; }
+ }
+ %>
+ <tr style="border-bottom:1px solid var(--border,#2a2a2a)">
+ <td style="padding:8px 10px;white-space:nowrap">
+ <a href="<%= claimitUrl %>" target="_blank" rel="noopener noreferrer" style="color:inherit;text-decoration:none;font-weight:600" title="Search this on claimit.ca.gov">
+ <%= p.amount_display || (p.amount_cents != null ? ('$' + (p.amount_cents / 100).toFixed(2)) : '—') %> ↗
+ </a>
+ </td>
+ <td style="padding:8px 10px">
+ <span title="<%= p.owner_name || '' %>"><%= p.holder_name || '—' %></span>
+ <% if (p.owner_name) { %><div class="subtle" style="font-size:11px;color:var(--text-dim)"><%= p.owner_name %><% if (p.co_owner) { %> · <%= p.co_owner %><% } %></div><% } %>
+ </td>
+ <td style="padding:8px 10px;color:var(--text-dim)">
+ <% if (addrParts.length) { %><%= addrParts.join(' · ') %><% } else { %>—<% } %>
+ </td>
+ <td style="padding:8px 10px"><%= p.property_type || '—' %></td>
+ <td style="padding:8px 10px;white-space:nowrap">
+ <a href="<%= claimitUrl %>" target="_blank" rel="noopener noreferrer" style="color:inherit;text-decoration:none" title="Search Property ID on claimit.ca.gov">
+ <%= p.property_id || '—' %> ↗
+ </a>
+ </td>
+ <td style="padding:8px 10px;white-space:nowrap">
+ <select class="ucp-status" data-id="<%= p.id %>" style="background:transparent;color:<%= stColor %>;border:1px solid var(--border,#3a3a3a);border-radius:6px;padding:4px 6px;font-size:12px">
+ <% statusOptions.forEach(function (opt) { %>
+ <option value="<%= opt %>"<%= st === opt ? ' selected' : '' %>><%= opt %></option>
+ <% }); %>
+ </select>
+ <span class="ucp-saved" style="display:none;font-size:11px;color:#7dd88a;margin-left:6px">saved ✓</span>
+ </td>
+ <td style="padding:8px 10px;white-space:nowrap;color:var(--text-dim)">
+ <% if (foundLabel) { %><span title="<%= foundIso %>">🕓 <%= foundLabel %></span><% } else { %>—<% } %>
+ </td>
+ </tr>
+ <% }); %>
+ </tbody>
+ </table>
+ </div>
+ </section>
+ <script>
+ // Inline per-row status update for tracked unclaimed-property records.
+ document.querySelectorAll('select.ucp-status').forEach(function (sel) {
+ sel.addEventListener('change', function () {
+ var id = sel.getAttribute('data-id');
+ var status = sel.value;
+ var saved = sel.parentNode.querySelector('.ucp-saved');
+ var colors = { paid: '#7dd88a', staged: '#e0b24a', not_mine: '#8a8a8a', filed: '', found: '' };
+ sel.disabled = true;
+ fetch('/api/unclaimed/' + encodeURIComponent(id) + '/status', {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify({ status: status })
+ }).then(function (res) {
+ if (!res.ok) throw new Error('HTTP ' + res.status);
+ return res.json().catch(function () { return {}; });
+ }).then(function () {
+ sel.style.color = (colors[status] || 'inherit');
+ if (saved) {
+ saved.style.display = 'inline';
+ setTimeout(function () { saved.style.display = 'none'; }, 1400);
+ }
+ }).catch(function () {
+ if (saved) {
+ saved.textContent = 'failed';
+ saved.style.color = '#e07a7a';
+ saved.style.display = 'inline';
+ setTimeout(function () { saved.style.display = 'none'; saved.textContent = 'saved ✓'; saved.style.color = '#7dd88a'; }, 1800);
+ }
+ }).finally(function () {
+ sel.disabled = false;
+ });
+ });
+ });
+ </script>
+<% } else if (typeof properties !== 'undefined') { %>
+ <p class="subtle" style="font-size:12px;color:var(--text-dim);margin:0 0 20px">No tracked properties yet.</p>
+<% } %>
+
<% if (!claims.length) { %>
<section class="empty glass">
<p>No claims yet. Claims are created automatically when a reminder fires (returns window closing, warranty expiry, recall match).</p>
← fc3c624 chore: lint (node --check 21/21) + version bump v0.3.0 (sess
·
back to AbramsOS
·
auto-save: 2026-07-31T14:58:42 (1 files) — deploy/claims.abr 69b613c →