aboutsummaryrefslogtreecommitdiffstats
path: root/db/migrations/referrals.sql
diff options
context:
space:
mode:
authorRan Guin <[email protected]>2021-05-13 14:51:38 -0700
committerAdam Soltys <[email protected]>2021-06-04 22:38:18 -0700
commit8b0d41cb8f2296047611f9fc9745ce8c5deefdd2 (patch)
treecb923bf69ea71ecebe28bf022fb5c0968d17109f /db/migrations/referrals.sql
parent8bac6764dceac97e09c66c99a2503fa41c60e5b1 (diff)
downloadcoinos-server-8b0d41cb8f2296047611f9fc9745ce8c5deefdd2.tar.gz
coinos-server-8b0d41cb8f2296047611f9fc9745ce8c5deefdd2.zip
add migrations for referrals & waiting list
Diffstat (limited to 'db/migrations/referrals.sql')
-rw-r--r--db/migrations/referrals.sql30
1 files changed, 30 insertions, 0 deletions
diff --git a/db/migrations/referrals.sql b/db/migrations/referrals.sql
new file mode 100644
index 00000000..b755000a
--- /dev/null
+++ b/db/migrations/referrals.sql
@@ -0,0 +1,30 @@
+-- Note: Adding FK contraints may cause issues, so I have commented them out for now, but these need to be established
+
+CREATE TABLE `referrals` (
+ `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `token` varchar(255) DEFAULT NULL UNIQUE,
+ `expiry` date DEFAULT NULL,
+ `status` enum('pending','active','expired','cancelled') DEFAULT NULL,
+ `sponsor_id` int(11) NOT NULL,
+ `user_id` int(11) DEFAULT NULL,
+ `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
+ `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ KEY `sponsor_id` (`sponsor_id`),
+ KEY `user_id` (`user_id`)
+ -- FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
+ -- FOREIGN KEY (`sponsor_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+
+CREATE TABLE `waiting_list` (
+ `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `email` varchar(255) DEFAULT NULL UNIQUE,
+ `sms` varchar(255) DEFAULT NULL,
+ `user_id` int(11) DEFAULT NULL,
+ `status` enum('pending','contacted','registered','cancelled') DEFAULT 'pending',
+ `notes` text DEFAULT NULL,
+ `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
+ `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`)
+ -- CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 \ No newline at end of file
generated by cgit v1.2.3 (git 2.51.0) at 2026-10-26 14:56:33 +0000
/* Literal.Number */ .highlight .s { color: #D20; background-color: #FFF0F0 } /* Literal.String */ .highlight .na { color: #369 } /* Name.Attribute */ .highlight .nb { color: #038 } /* Name.Builtin */ .highlight .nc { color: #B06; font-weight: bold } /* Name.Class */ .highlight .no { color: #036; font-weight: bold } /* Name.Constant */ .highlight .nd { color: #555 } /* Name.Decorator */ .highlight .ne { color: #B06; font-weight: bold } /* Name.Exception */ .highlight .nf { color: #06B; font-weight: bold } /* Name.Function */ .highlight .nl { color: #369; font-style: italic } /* Name.Label */ .highlight .nn { color: #B06; font-weight: bold } /* Name.Namespace */ .highlight .py { color: #369; font-weight: bold } /* Name.Property */ .highlight .nt { color: #B06; font-weight: bold } /* Name.Tag */ .highlight .nv { color: #369 } /* Name.Variable */ .highlight .ow { color: #080 } /* Operator.Word */ .highlight .w { color: #BBB } /* Text.Whitespace */ .highlight .mb { color: #00D; font-weight: bold } /* Literal.Number.Bin */ .highlight .mf { color: #00D; font-weight: bold } /* Literal.Number.Float */ .highlight .mh { color: #00D; font-weight: bold } /* Literal.Number.Hex */ .highlight .mi { color: #00D; font-weight: bold } /* Literal.Number.Integer */ .highlight .mo { color: #00D; font-weight: bold } /* Literal.Number.Oct */ .highlight .sa { color: #D20; background-color: #FFF0F0 } /* Literal.String.Affix */ .highlight .sb { color: #D20; background-color: #FFF0F0 } /* Literal.String.Backtick */ .highlight .sc { color: #D20; background-color: #FFF0F0 } /* Literal.String.Char */ .highlight .dl { color: #D20; background-color: #FFF0F0 } /* Literal.String.Delimiter */ .highlight .sd { color: #D20; background-color: #FFF0F0 } /* Literal.String.Doc */ .highlight .s2 { color: #D20; background-color: #FFF0F0 } /* Literal.String.Double */ .highlight .se { color: #04D; background-color: #FFF0F0 } /* Literal.String.Escape */ .highlight .sh { color: #D20; background-color: #FFF0F0 } /* Literal.String.Heredoc */ .highlight .si { color: #33B; background-color: #FFF0F0 } /* Literal.String.Interpol */ .highlight .sx { color: #2B2; background-color: #F0FFF0 } /* Literal.String.Other */ .highlight .sr { color: #080; background-color: #FFF0FF } /* Literal.String.Regex */ .highlight .s1 { color: #D20; background-color: #FFF0F0 } /* Literal.String.Single */ .highlight .ss { color: #A60; background-color: #FFF0F0 } /* Literal.String.Symbol */ .highlight .bp { color: #038 } /* Name.Builtin.Pseudo */ .highlight .fm { color: #06B; font-weight: bold } /* Name.Function.Magic */ .highlight .vc { color: #369 } /* Name.Variable.Class */ .highlight .vg { color: #D70 } /* Name.Variable.Global */ .highlight .vi { color: #33B } /* Name.Variable.Instance */ .highlight .vm { color: #369 } /* Name.Variable.Magic */ .highlight .il { color: #00D; font-weight: bold } /* Literal.Number.Integer.Long */
// Background NWC spending state — what the service worker answers (and pays)
// from while the app is closed.
//
// The record mirrors the wallet's REAL ark state: its vtxos (with the signed
// bytes), the chain-3 private keys for exactly those coins plus a window of
// upcoming indices for change, the NWC connections' service keys, and the
// worker's spend log. The seed itself never leaves localStorage.
//
// THREAT MODEL, stated plainly. Anything with same-origin script access, or
// a stolen unlocked device, can read IndexedDB and therefore spend the ark
// balance. That is the accepted cost of a wallet that answers while closed —
// the ark balance is treated as walking-around money, and the on-chain wallet
// (whose keys are never mirrored) stays out of reach. Per-connection NWC
// budgets are enforced by the worker before it pays. A user who does not
// enable background answering has no record here and loses nothing.

const DB = 'hal-nwc-pouch'; // name kept from the pouch era so upgrades are seamless
const STORE = 'pouch';
const VERSION = 1;

function openDb() {
  return new Promise((resolve, reject) => {
    if (typeof indexedDB === 'undefined') return reject(new Error('IndexedDB unavailable'));
    const req = indexedDB.open(DB, VERSION);
    req.onupgradeneeded = () => {
      const db = req.result;
      if (!db.objectStoreNames.contains(STORE)) db.createObjectStore(STORE);
    };
    req.onsuccess = () => resolve(req.result);
    req.onerror = () => reject(req.error);
  });
}

async function tx(mode, fn) {
  const db = await openDb();
  try {
    return await new Promise((resolve, reject) => {
      const t = db.transaction(STORE, mode);
      const s = t.objectStore(STORE);
      let out;
      Promise.resolve(fn(s)).then((v) => { out = v; }).catch(reject);
      t.oncomplete = () => resolve(out);
      t.onerror = () => reject(t.error);
      t.onabort = () => reject(t.error);
    });
  } finally { db.close(); }
}
const get = (s, k) => new Promise((res, rej) => { const r = s.get(k); r.onsuccess = () => res(r.result); r.onerror = () => rej(r.error); });

// One record per wallet, keyed by the wallet's cache key.
export async function loadBg(walletKey) {
  try { return (await tx('readonly', (s) => get(s, walletKey))) || null; } catch { return null; }
}
export async function saveBg(walletKey, rec) {
  return tx('readwrite', (s) => { s.put(rec, walletKey); });
}
export async function clearBg(walletKey) {
  return tx('readwrite', (s) => { s.delete(walletKey); });
}

// The worker's diary: what it did while the app was closed — each push, the
// steps of a payment, every notification shown or refused. A phone stops a
// worker without a trace, and the diary's last line says where it got to.
// Settings → Notifications → Copy hands it to whoever is looking into it.
// Appended line by line (a worker that dies mid-push keeps what it wrote),
// one writer at a time, a few hundred lines kept.
const LOG_KEY = '__log';
const LOG_MAX = 400;
let logChain = Promise.resolve();
export function appendBgLog(line) {
  logChain = logChain.then(() => tx('readwrite', async (s) => {
    const cur = (await get(s, LOG_KEY)) || [];
    cur.push(line);
    s.put(cur.length > LOG_MAX ? cur.slice(-LOG_MAX) : cur, LOG_KEY);
  })).catch(() => {});
  return logChain;
}
export async function readBgLog() {
  try { return (await tx('readonly', (s) => get(s, LOG_KEY))) || []; } catch { return []; }
}
export async function clearBgLog() {
  return tx('readwrite', (s) => { s.delete(LOG_KEY); });
}
// The service worker doesn't know wallet cache keys — it finds the right
// record by the connection's service pubkey across all of them.
export async function allBgs() {
  try {
    return await tx('readonly', (s) => new Promise((res, rej) => {
      const out = [];
      const cur = s.openCursor();
      cur.onsuccess = () => {
        const c = cur.result;
        if (!c) return res(out);
        // the worker's diary shares the store under a reserved key
        if (!(typeof c.key === 'string' && c.key.startsWith('__'))) out.push({ walletKey: c.key, rec: c.value });
        c.continue();
      };
      cur.onerror = () => rej(cur.error);
    }));
  } catch { return []; }
}

// How many upcoming change/refund indices travel with the record: the worker
// cannot derive keys, so its allocations must land inside this window.
export const BG_KEY_LOOKAHEAD = 25;

// { ark } endpoints, the manager state to mirror, and a keyFor(chain, index)
// derive function (the caller owns the account node — it never enters this
// module). Chain 3 covers coins + change, chain 4/0 the mailbox, chain 5 a
// window of receive preimages — everything the worker needs to pay AND to
// mint invoices while the app is closed.
export function buildBg({ ark, mgrState, keyFor, connections, offer, autowithdraw, custody }) {
  const keys = {};
  const idx = new Set((mgrState.vtxos || []).filter((v) => v.state !== 'spent').map((v) => v.keyIndex));
  for (let i = 0; i < BG_KEY_LOOKAHEAD; i++) idx.add((mgrState.nextKeyIndex || 1) + i);
  // A standing renewal order (and a renewal in flight) burned its output key
  // when it was registered, below the counter by now: the worker needs that
  // key to claim the renewed coin while the app is closed.
  for (const e of mgrState.scheduled || []) if (e.outKeyIndex != null) idx.add(e.outKeyIndex);
  for (const a of mgrState.actions || []) if (a.type === 'refresh' && a.step !== 'done' && a.outKeyIndex != null) idx.add(a.outKeyIndex);
  idx.add(0); // the receive-address key: mailbox/refund defaults land on it
  for (const i of idx) keys[String(i)] = keyFor(3, i);
  const keys5 = {};
  for (let i = 0; i < 15; i++) {
    const n = (mgrState.nextLnRecvIndex || 0) + i;
    keys5[String(n)] = keyFor(5, n);
  }
  return {
    v: 3,
    updated: Date.now(),
    ark,
    mgr: mgrState,
    keys,
    keys5,
    key4: keyFor(4, 0),
    offer: offer || null, // { sk, pk } — the CLINK offer service keypair
    // { on, threshold, keep, target: { kind, address, url?, spkHex? } } — the
    // destination is resolved by the app, so the worker never needs a relay
    autowithdraw: autowithdraw || null,
    // the custody tier ('custodial' | 'self' | 'early'): whether the worker
    // registers renewal orders for bare coins, and how far ahead
    custody: custody || null,
    // The budget's spent counter rides along (stamped with its window) so the
    // worker counts what the app spent; a revoked one rides along flagged, to
    // be refused rather than ignored.
    connections: (connections || []).map((c) => ({
      id: c.id, name: c.name || '', servicePk: c.servicePk, serviceSk: c.serviceSk, clientPk: c.clientPk,
      maxSat: c.maxSat, dailySat: c.dailySat, period: c.period || 'day',
      spentDate: c.spentDate || null, spentToday: c.spentToday || 0,
      revoked: !!c.revoked,
    })),
  };
}

export const bgSpendableSat = (rec) =>
  ((rec && rec.mgr && rec.mgr.vtxos) || []).filter((v) => v.state === 'spendable')
    .reduce((n, v) => n + (v.amountSat || 0), 0);

// After (re)writing a wallet's record: strip its connections/offer from any
// OTHER record that still carries them — an old network's mirror, say — so
// exactly one record ever answers for a connection. The sibling's coin state
// stays put for a later merge; only its answering role is removed. A stale
// copy that races the live one answers from the wrong wallet (and a wrong-
// network mirror fails instantly, beating the real payer's reply).
export async function disarmSiblingRecords(walletKey, rec, { loadAll = allBgs, save = saveBg } = {}) {
  const pks = new Set((rec.connections || []).map((c) => c.servicePk));
  if (rec.offer?.pk) pks.add(rec.offer.pk);
  if (!pks.size) return 0;
  let disarmed = 0;
  for (const r of await loadAll()) {
    if (r.walletKey === walletKey || !r.rec || r.rec.v < 3) continue;
    const conns = r.rec.connections || [];
    const keep = conns.filter((c) => !pks.has(c.servicePk));
    const offerHit = !!(r.rec.offer?.pk && pks.has(r.rec.offer.pk));
    if (keep.length === conns.length && !offerHit) continue;
    r.rec.connections = keep;
    if (offerHit) r.rec.offer = null;
    await save(r.walletKey, r.rec);
    disarmed++;
  }
  return disarmed;
}

// The worker records what it spent so the app can absorb it into budgets and
// history on next open.
export async function noteBgSpend(walletKey, spend) {
  const rec = await loadBg(walletKey);
  if (!rec) return null;
  rec.spends = rec.spends || [];
  rec.spends.push({ ...spend, ts: Date.now() });
  if (rec.spends.length > 200) rec.spends = rec.spends.slice(-200);
  await saveBg(walletKey, rec);
  return rec;
}