purchases: collect 38 real Amazon orders from info@ (via George) into purchase table

4a02cce6941ecc4fe840f5f28e02e0b3e7a32747 · 2026-07-13 00:56:16 -0700 · Steve

- scripts/collect-amazon-orders.js: parsed order confirmations 2022-2026, dedup on order#, total NULL (not fabricated — needs body enrichment)
- validates savings seeds: Nespresso Vertuo = top recurring buy (4x), shipping labels (2x)
- feeds home purchase count + /purchases + savings advisor with REAL data

Files touched

A scripts/collect-amazon-orders.js

Diff

commit 4a02cce6941ecc4fe840f5f28e02e0b3e7a32747
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 13 00:56:16 2026 -0700

    purchases: collect 38 real Amazon orders from info@ (via George) into purchase table
    
    - scripts/collect-amazon-orders.js: parsed order confirmations 2022-2026, dedup on order#, total NULL (not fabricated — needs body enrichment)
    - validates savings seeds: Nespresso Vertuo = top recurring buy (4x), shipping labels (2x)
    - feeds home purchase count + /purchases + savings advisor with REAL data
---
 scripts/collect-amazon-orders.js | 73 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 73 insertions(+)

diff --git a/scripts/collect-amazon-orders.js b/scripts/collect-amazon-orders.js
new file mode 100644
index 0000000..6ea2592
--- /dev/null
+++ b/scripts/collect-amazon-orders.js
@@ -0,0 +1,73 @@
+// One-off: collect parsed Amazon order confirmations (from info@ via George) into
+// AbramsOS purchase. Honest: total_amount stays NULL (not in metadata); dedup on order#.
+
+const db = require('../lib/db');
+const { id } = require('../lib/ids');
+const USER = 'user_steve';
+
+// [internalDate ms, raw subject, order# or null, gmail msg id]
+const O = [
+ [1783713200000,'Ordered: "Nespresso Capsules Vertuo,..." and 1 more item',null,'19f4d977b22ca555'],
+ [1782836677000,'Ordered: "Plantronics Voyager Focus..."',null,'19f1958caa9e4860'],
+ [1782336230000,'Ordered: "Plantronics CS520 Binaural..." and 6 more items',null,'19efb8497c2a7049'],
+ [1777400488000,'Your Amazon.com order of "4 Rolls 450 Labels 4x6...".',null,'19dd55322cfe38b3'],
+ [1777400271000,'Your Amazon.com order of "Nespresso Capsules Vertuo..." and 2 more items.',null,'19dd54c45ae813be'],
+ [1777062036000,'Your Amazon.com order of "JCLEAL Pool Float Lounger..." and 8 more items.',null,'19dc126557900b67'],
+ [1777061709000,'Your Amazon.com order of "GoldFin Mens Wetsuit Top..." and 3 more items.',null,'19dc12188ee4a51d'],
+ [1776976244000,'Your Amazon.com order of "GoldFin Mens Wetsuit Top..." and 3 more items.',null,'19dbc09b1ed10be5'],
+ [1776899063000,'Your Amazon.com order of "2x JCLEAL Pool Float Lounger..." and 7 more items.',null,'19db76ffed03007e'],
+ [1776898645000,'Your Amazon.com order of "2x Upholstered Stylish Pool..." and 7 more items.',null,'19db769a0272155c'],
+ [1776897636000,'Your Amazon.com order of "2x Upholstered Stylish Pool..." and 15 more items.',null,'19db75a3903871ff'],
+ [1774988145000,'Your Amazon.com order of "TN-730 TN730 MFC-L2710DW...".',null,'19d4589b6defcddd'],
+ [1773162522000,'Your Amazon.com order of "Nespresso Capsules Vertuo..." and 1 more item.',null,'19cd8b8e92e71c2c'],
+ [1772650098000,'Ordered: "Nespresso Capsules Vertuo,..." and 4 more items',null,'19cba2dffd291135'],
+ [1772647844000,'Your Amazon.com order of "Samsung T9 Portable SSD...".',null,'19cba0b8cd428a30'],
+ [1769552625000,'Ordered: "4 Rolls 450 Labels 4x6..."',null,'19c018e4fd40c4d8'],
+ [1769121939000,'Your Amazon Fresh Receipt',null,'19be7e280c331bc3'],
+ [1742344709000,'Your Amazon.com order of "CXEZSIK Garden Hose..." and 10 more items.',null,'195abd669c697871'],
+ [1734645608000,'Your Amazon.com order of "Premium Super Trimmed..." and 3 more items.',null,'193e0ef72f669674'],
+ [1726445765000,'Your Amazon.com order of "Hoover PowerScrub XL Pet..." and 10 more items.',null,'191f82fc78a61c00'],
+ [1713892191000,'Your Amazon.com order of "FANHAO Upgrade Garden Hose...".',null,'18f0bef70eeaa68f'],
+ [1713390964000,'Your Amazon.com order',null,'18eee0f50ca80373'],
+ [1710480506000,'Your Amazon.com order of "SpaGuard Brominating..." and 19 more items.',null,'18e409540ead49bd'],
+ [1704324428000,'Your Amazon.com order of "Nespresso Capsules...".','112-4338583-8108250','18cd1a6f5d4a3c31'],
+ [1702930000000,'Your Amazon.com order of "Abbott Collection Ceramic...".','114-2436527-3315420','18c7e89abb40e864'],
+ [1702922319000,'Your Amazon.com order of "4 Ramen Bowls, 12 pieces..." and 3 more items.','113-0020876-1931461','18c7e147a2b9b31b'],
+ [1702744238000,'Your Amazon.com order of "Inside the White House..." and 9 more items.','113-9055697-2465069','18c73772b79592d1'],
+ [1702581106000,'Your Amazon.com order of "7x Amazon.com Gift Card...".','114-3273948-2369002','18c69bdfd8aeeddd'],
+ [1701973028000,'Your Amazon.com order of "International Delight...".','114-2702963-4247441','18c457f715c85af9'],
+ [1696091563000,'Your Amazon.com order of "4x Maume Dog Bell for Door...".','114-3775698-0042618','18ae6ef666311007'],
+ [1694974687000,'Your Amazon.com order of "USB C to HDMI Adapter...".','114-6961835-5017059','18aa45d3af3bf82a'],
+ [1694035211000,'Your Amazon.com order of "Board2by 2-Pack Cork Board..." and 8 more items.','112-5833883-4990623','18a6c5df6429a906'],
+ [1692670754000,'Your Amazon.com order of "Led Desk Lamp for Office..." and 5 more items.','112-6653619-1561035','18a1b0a02fb0ecee'],
+ [1666330044000,'Your Amazon.com order of "Ring Stick Up Cam Battery..." and 13 more items.',null,'183f902a907018e3'],
+ [1655832050000,'Your Amazon.com order of "King Kooker 5500 Stainless..." and 5 more items.','112-8815900-8787430','1818747ff88f1478'],
+ [1654800585000,'Your Amazon.com order of "Ksunun Double Head LED Desk..." and 5 more items.','113-9761492-5829028','18149cd144c02ac8'],
+ [1654530601000,'Your Amazon.com order of "John Timberland Three Tier..." and 9 more items.',null,'18139b57277ef2c0'],
+ [1650824986000,'Your Amazon.com order of "Harssidanzar Women\'s..." and 15 more items.','112-7447440-9868261','1805cd642b1ddf64'],
+];
+
+function cleanItem(subj) {
+  let s = subj.replace(/^Ordered:\s*/i, '').replace(/^Your Amazon\.com order of\s*/i, '').replace(/^Your\s+/i, '');
+  s = s.replace(/^"|"\.?$/g, '').replace(/\.\.\.?"?\.?$/, '').replace(/"/g, '').trim();
+  return s || 'Amazon order';
+}
+
+(async () => {
+  let inserted = 0, dupe = 0;
+  for (const [ms, subj, oid, mid] of O) {
+    const item = cleanItem(subj);
+    const orderNo = oid ? `amzn:${oid}` : `amzn-msg:${mid}`;
+    const raw = { source: 'george-info-amazon', subject: subj, item, order_id: oid, gmail_id: mid };
+    const ins = await db.query(
+      `INSERT INTO purchase (id, user_id, source_message_id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, confidence, raw_extract)
+       VALUES ($1,$2,NULL,'Amazon','amazon.com',$3,$4,NULL,'USD',0.9,$5)
+       ON CONFLICT (user_id, order_number) WHERE order_number IS NOT NULL DO NOTHING
+       RETURNING id`,
+      [id('purchase'), USER, orderNo, new Date(ms), raw]
+    );
+    if (ins.rows.length) inserted++; else dupe++;
+  }
+  console.log(`inserted=${inserted} dupe=${dupe} of ${O.length}`);
+  process.exit(0);
+})().catch(e => { console.error('FAIL', e.message); process.exit(1); });

← ded692c connectors: no-signup bank import — Wells Fargo CSV upload ( · back to AbramsOS · health: vitals trends + insights (weight/RHR sparklines, loc 8f58fa2 →