aboutsummaryrefslogtreecommitdiffstats
path: root/scripts
diff options
context:
space:
mode:
Diffstat (limited to 'scripts')
-rw-r--r--scripts/analytics-geo.ts134
-rw-r--r--scripts/analytics.ts309
-rw-r--r--scripts/audit-lightning-backlog.ts76
-rw-r--r--scripts/audit-missing-history.ts106
-rw-r--r--scripts/audit-wiped-history.ts85
-rw-r--r--scripts/boltz-recover-stuck.ts91
-rw-r--r--scripts/cln_schema.sql1783
-rwxr-xr-xscripts/close-dead-channels.sh276
-rw-r--r--scripts/dedup-invoices.ts86
-rw-r--r--scripts/kvrocks-merge-lowmem.conf19
-rw-r--r--scripts/ln-liquid-arb.ts126
-rw-r--r--scripts/merge-kvrocks-archives.ts321
-rw-r--r--scripts/migrate.py74
-rwxr-xr-xscripts/monitor-ln-socket.sh90
-rw-r--r--scripts/pay-hard.ts159
-rwxr-xr-xscripts/payment-monitor.sh210
-rw-r--r--scripts/rebalance-economics.ts96
17 files changed, 4041 insertions, 0 deletions
diff --git a/scripts/analytics-geo.ts b/scripts/analytics-geo.ts
new file mode 100644
index 00000000..ef088b6b
--- /dev/null
+++ b/scripts/analytics-geo.ts
@@ -0,0 +1,134 @@
+#!/usr/bin/env bun
+// Geographic report from the request log. Ties Cloudflare's cf-ipcountry header
+// to the authenticated `user` field so we count DISTINCT USERS per country
+// (not raw request volume, which is dominated by datacenter/bot traffic).
+//
+// Usage: bun scripts/analytics-geo.ts [--days N] [--top N]
+// Streams the request log (pino JSONL, multi-GB) — never loads it whole.
+
+import { createInterface } from "readline";
+import { createReadStream, readdirSync } from "fs";
+import { createGunzip } from "zlib";
+
+const argDays = Number(process.argv[process.argv.indexOf("--days") + 1]) || 30;
+const TOP = Number(process.argv[process.argv.indexOf("--top") + 1]) || 25;
+const cutoff = Date.now() - argDays * 24 * 60 * 60 * 1000;
+
+// Datacenter IPs that flood /login with many usernames (credential-testing
+// bots). Excluded from geo counts. 195.123.220.232 = M247 NL, ~3,971 logins
+// for sequential usernames (bugtraq, bugtraqq, ...) in the last 30d.
+const BOT_IPS = new Set<string>(["195.123.220.232"]);
+
+
+// The live log rotates into 50M dated chunks under reqlogs/ (named for when
+// they were rotated out, gzipped except the newest), so stream every chunk
+// rotated within the window, oldest first, then the live file.
+const REQ = "/home/adam/coinos-server/req";
+const REQLOGS = "/home/adam/coinos-server/reqlogs";
+const chunkTime = (f: string) => {
+ const m = f.match(/^req-(\d{4})(\d{2})(\d{2})-(\d{2})(\d{2})(\d{2})?/);
+ return m ? new Date(+m[1], +m[2] - 1, +m[3], +m[4], +m[5], +(m[6] || 0)).getTime() : 0;
+};
+async function* reqLines(since: number) {
+ const files = readdirSync(REQLOGS)
+ .filter((f) => chunkTime(f) >= since)
+ .sort((a, b) => chunkTime(a) - chunkTime(b) || a.localeCompare(b))
+ .map((f) => `${REQLOGS}/${f}`);
+ for (const path of [...files, REQ]) {
+ const raw = createReadStream(path);
+ const input = path.endsWith(".gz") ? raw.pipe(createGunzip()) : raw;
+ yield* createInterface({ input, crlfDelay: Infinity });
+ }
+}
+
+const rl = reqLines(cutoff);
+
+// We only trust /login and /register events for geography: they are real human
+// auth actions tied to a username in the body, from the user's actual device.
+// Counting ALL authenticated requests pollutes the result massively — e.g.
+// nostr/NWC server traffic from a single M247 datacenter IP (195.123.220.232)
+// carries a stale `user` field and made NL look like 93% of users. Per-IP-per-
+// country dedup on auth events is the honest signal.
+
+// country -> Set<username> (distinct users who logged in / registered there)
+const usersByCountry = new Map<string, Set<string>>();
+// country -> distinct IPs (sanity: real geography has many IPs per country)
+const ipsByCountry = new Map<string, Set<string>>();
+let lines = 0;
+let authEvents = 0;
+
+const extractUsername = (r: any): string | null => {
+ // /login and /register carry username in the body (top-level `user` is empty
+ // on these). Body shape varies: {username} or {user:{username}}.
+ const b = r.body;
+ if (!b) return null;
+ if (typeof b.username === "string") return b.username.toLowerCase();
+ if (b.user && typeof b.user.username === "string") return b.user.username.toLowerCase();
+ return null;
+};
+
+for await (const line of rl) {
+ lines++;
+ // cheap pre-filter: must be an auth event with a country tag
+ if (!line.includes("cf-ipcountry")) continue;
+ if (!line.includes('"/login"') && !line.includes('"/register"')) continue;
+ let r: any;
+ try {
+ r = JSON.parse(line);
+ } catch {
+ continue;
+ }
+ if (!r.time || r.time < cutoff) continue;
+ if (r.url !== "/login" && r.url !== "/register") continue;
+ const cc = r.headers?.["cf-ipcountry"];
+ if (!cc || cc === "XX" || cc.length !== 2) continue;
+ const ip = r.headers?.["cf-connecting-ip"];
+
+ const user = extractUsername(r);
+ if (!user) continue;
+
+ // Drop known bot/proxy egress IPs that fire thousands of logins for many
+ // usernames from a single datacenter address — these are credential-testing
+ // bots, not real users, and otherwise dominate the country ranking. Heuristic
+ // backstop below (users >> ips) catches the rest; this is the worst offender.
+ if (ip && BOT_IPS.has(ip)) continue;
+ authEvents++;
+
+ let s = usersByCountry.get(cc);
+ if (!s) usersByCountry.set(cc, (s = new Set()));
+ s.add(user);
+
+ if (ip) {
+ let is = ipsByCountry.get(cc);
+ if (!is) ipsByCountry.set(cc, (is = new Set()));
+ is.add(ip);
+ }
+}
+const withCountry = authEvents;
+
+const byUsers = [...usersByCountry.entries()]
+ .map(([cc, set]) => [cc, set.size, ipsByCountry.get(cc)?.size || 0] as [string, number, number])
+ .sort((a, b) => b[1] - a[1]);
+
+const totalUsers = byUsers.reduce((s, [, n]) => s + n, 0);
+const max = byUsers[0]?.[1] || 1;
+const bar = (n: number) =>
+ "█".repeat(Math.round((n / max) * 24)) + "·".repeat(24 - Math.round((n / max) * 24));
+
+console.log("═".repeat(64));
+console.log(` COINOS GEO — last ${argDays} days (login/register events only)`);
+console.log(` scanned ${lines.toLocaleString()} log lines, ${withCountry.toLocaleString()} auth events`);
+console.log("═".repeat(64));
+console.log("\n▏TOP COUNTRIES (distinct users who logged in / registered)");
+console.log(" cc distribution users ips share");
+byUsers.slice(0, TOP).forEach(([cc, n, ips], i) => {
+ const pct = ((n / totalUsers) * 100).toFixed(1);
+ console.log(
+ ` ${String(i + 1).padStart(2)}. ${cc} ${bar(n)} ${String(n).padStart(5)} ${String(ips).padStart(4)} ${pct.padStart(5)}%`,
+ );
+});
+console.log(
+ `\n (ips column = distinct source IPs; a country with users >> ips is likely a proxy/VPN egress, not real geography)`,
+);
+console.log("\n" + "═".repeat(64));
+process.exit(0);
diff --git a/scripts/analytics.ts b/scripts/analytics.ts
new file mode 100644
index 00000000..7cefa3d4
--- /dev/null
+++ b/scripts/analytics.ts
@@ -0,0 +1,309 @@
+#!/usr/bin/env bun
+// Coinos analytics report. Streams the live `db` (SCAN, never KEYS) and
+// aggregates payment + balance + user data into an insight report.
+//
+// Usage:
+// bun scripts/analytics.ts [--days N] [--top N] [--json] [--csv PATH]
+//
+// Defaults: --days 30, --top 20. Reads only the live db (redis://127.0.0.1:6379);
+// archived payments (older, in `arc`) are out of scope for the trailing window.
+// --csv appends one metrics row to PATH (writing a header if the file is new),
+// for month-over-month trend tracking; the human report still prints to stdout.
+
+import { createClient } from "redis";
+import { appendFileSync, existsSync } from "fs";
+
+const argDays = Number(process.argv[process.argv.indexOf("--days") + 1]) || 30;
+const TOP = Number(process.argv[process.argv.indexOf("--top") + 1]) || 20;
+const asJson = process.argv.includes("--json");
+const csvIdx = process.argv.indexOf("--csv");
+const csvPath = csvIdx >= 0 ? process.argv[csvIdx + 1] : null;
+
+const now = Date.now();
+const cutoff = now - argDays * 24 * 60 * 60 * 1000;
+
+const db = createClient({
+ url: "redis://127.0.0.1:6379",
+ socket: { reconnectStrategy: () => false },
+});
+await db.connect();
+
+const fmtSats = (n: number) =>
+ n.toLocaleString("en-US") + " sats";
+const fmtBtc = (n: number) => (n / 1e8).toFixed(3) + " BTC";
+
+// ---- helpers ---------------------------------------------------------------
+
+async function* scanGetJSON(match: string, count = 1000) {
+ // Stream keys matching `match`, fetch values in pipelined batches, yield
+ // parsed JSON objects (skips non-JSON / mapping-string keys).
+ let batch: string[] = [];
+ async function* drain() {
+ if (!batch.length) return;
+ const vals = await db.mGet(batch);
+ for (let i = 0; i < vals.length; i++) {
+ const v = vals[i];
+ if (!v || v[0] !== "{") continue; // skip mapping strings / nulls
+ try {
+ yield [batch[i], JSON.parse(v)] as [string, any];
+ } catch {}
+ }
+ batch = [];
+ }
+ for await (const key of db.scanIterator({ MATCH: match, COUNT: count })) {
+ batch.push(key as string);
+ if (batch.length >= 500) yield* drain();
+ }
+ yield* drain();
+}
+
+// ---- pass 1: usernames (uid -> username) -----------------------------------
+// user:<uid> is the full record; user:<username> is a string pointer we skip.
+
+const uname = new Map<string, string>();
+let userRecords = 0;
+let newUsers = 0; // created in window (if `created` present on user)
+for await (const [key, u] of scanGetJSON("user:*")) {
+ if (!u?.id || !u?.username) continue;
+ uname.set(u.id, u.username);
+ userRecords++;
+ if (u.created && u.created >= cutoff) newUsers++;
+}
+
+const nameOf = (uid: string) => uname.get(uid) || uid.slice(0, 8);
+
+// ---- pass 2: balances (holders) --------------------------------------------
+
+type Holder = { uid: string; bal: number };
+const holders: Holder[] = [];
+let totalCustodied = 0;
+for await (const key of db.scanIterator({ MATCH: "balance:*", COUNT: 1000 })) {
+ const uid = (key as string).slice("balance:".length);
+ const v = await db.get(key as string);
+ const bal = Number(v) || 0;
+ if (bal > 0) {
+ holders.push({ uid, bal });
+ totalCustodied += bal;
+ }
+}
+holders.sort((a, b) => b.bal - a.bal);
+
+// ---- pass 3: payments (the heavy walk) -------------------------------------
+
+const TYPES = [
+ "lightning",
+ "liquid",
+ "bitcoin",
+ "internal",
+ "bolt12",
+ "ecash",
+ "fund",
+ "reconcile",
+] as const;
+
+type Agg = { count: number; volume: number; sent: number; received: number };
+const byType: Record<string, Agg> = {};
+for (const t of TYPES) byType[t] = { count: 0, volume: 0, sent: 0, received: 0 };
+
+const activeUids = new Set<string>();
+const weekActive = new Set<string>(); // last 7d
+const perUser = new Map<string, { count: number; volume: number }>();
+const bolt12Recipients = new Map<string, number>(); // uid -> # bolt12 receives
+let feeRevenue = 0;
+let paymentsInWindow = 0;
+let largest: { uid: string; amount: number; type: string } | null = null;
+const dayBuckets = new Map<string, number>(); // YYYY-MM-DD -> count
+
+const weekCutoff = now - 7 * 24 * 60 * 60 * 1000;
+
+let scanned = 0;
+const t0 = Date.now();
+for await (const [key, p] of scanGetJSON("payment:*", 2000)) {
+ scanned++;
+ if (!p?.created || typeof p.amount !== "number" || !p.uid) continue;
+ if (p.created < cutoff) continue;
+
+ paymentsInWindow++;
+ const uid = p.uid;
+ const type = p.type || "unknown";
+ const amt = p.amount;
+ const abs = Math.abs(amt);
+
+ const agg = byType[type] || (byType[type] = { count: 0, volume: 0, sent: 0, received: 0 });
+ agg.count++;
+ agg.volume += abs;
+ if (amt < 0) agg.sent += abs;
+ else agg.received += abs;
+
+ activeUids.add(uid);
+ if (p.created >= weekCutoff) weekActive.add(uid);
+
+ const pu = perUser.get(uid) || { count: 0, volume: 0 };
+ pu.count++;
+ pu.volume += abs;
+ perUser.set(uid, pu);
+
+ if (type === "bolt12" && amt > 0) {
+ bolt12Recipients.set(uid, (bolt12Recipients.get(uid) || 0) + 1);
+ }
+
+ if (typeof p.fee === "number") feeRevenue += p.fee;
+ if (typeof p.ourfee === "number") feeRevenue += p.ourfee;
+
+ if (!largest || abs > largest.amount) largest = { uid, amount: abs, type };
+
+ const day = new Date(p.created).toISOString().slice(0, 10);
+ dayBuckets.set(day, (dayBuckets.get(day) || 0) + 1);
+}
+const walkSecs = ((Date.now() - t0) / 1000).toFixed(1);
+
+// ---- derive ----------------------------------------------------------------
+
+const mostActive = [...perUser.entries()]
+ .sort((a, b) => b[1].count - a[1].count)
+ .slice(0, TOP);
+const highestVolume = [...perUser.entries()]
+ .sort((a, b) => b[1].volume - a[1].volume)
+ .slice(0, TOP);
+
+// "Regular" bolt12 recipients = received via bolt12 on multiple distinct days
+// would be ideal, but receive-count >= 4 in the window is a good proxy for the
+// Ocean mining-reward stream.
+const regularBolt12 = [...bolt12Recipients.entries()].filter(([, n]) => n >= 4);
+regularBolt12.sort((a, b) => b[1] - a[1]);
+
+const totalVolume = Object.values(byType).reduce((s, a) => s + a.volume, 0);
+
+// ---- CSV append (trend tracking) -------------------------------------------
+// One row per run. Stable column set so months line up; per-type volume/count
+// columns let us chart the lightning/liquid/bitcoin/internal/bolt12 mix over
+// time. Written before stdout output so it happens in both text and --json modes.
+if (csvPath) {
+ const typeCols = ["lightning", "liquid", "bitcoin", "internal", "bolt12", "fund"];
+ const header = [
+ "date",
+ "window_days",
+ "users_total",
+ "new_users",
+ "mau",
+ "wau",
+ "payments",
+ "total_volume_sats",
+ "custodied_sats",
+ "holders",
+ "fee_revenue_sats",
+ "bolt12_recipients",
+ "regular_bolt12",
+ ...typeCols.flatMap((t) => [`${t}_vol`, `${t}_txns`]),
+ ];
+ const row = [
+ new Date(now).toISOString().slice(0, 10),
+ argDays,
+ userRecords,
+ newUsers,
+ activeUids.size,
+ weekActive.size,
+ paymentsInWindow,
+ totalVolume,
+ totalCustodied,
+ holders.length,
+ feeRevenue,
+ bolt12Recipients.size,
+ regularBolt12.length,
+ ...typeCols.flatMap((t) => [byType[t]?.volume || 0, byType[t]?.count || 0]),
+ ];
+ const fresh = !existsSync(csvPath);
+ appendFileSync(csvPath, (fresh ? header.join(",") + "\n" : "") + row.join(",") + "\n");
+ // To stderr so it doesn't pollute the saved stdout report / --json output.
+ console.error(`[csv] appended row to ${csvPath}`);
+}
+
+// ---- output ----------------------------------------------------------------
+
+if (asJson) {
+ console.log(
+ JSON.stringify(
+ {
+ window_days: argDays,
+ generated_at: new Date(now).toISOString(),
+ users_total: userRecords,
+ new_users_in_window: newUsers,
+ mau: activeUids.size,
+ wau: weekActive.size,
+ payments_in_window: paymentsInWindow,
+ total_volume_sats: totalVolume,
+ total_custodied_sats: totalCustodied,
+ fee_revenue_sats: feeRevenue,
+ by_type: byType,
+ top_holders: holders.slice(0, TOP).map((h) => ({ user: nameOf(h.uid), bal: h.bal })),
+ most_active: mostActive.map(([uid, v]) => ({ user: nameOf(uid), ...v })),
+ highest_volume: highestVolume.map(([uid, v]) => ({ user: nameOf(uid), ...v })),
+ bolt12_recipients: bolt12Recipients.size,
+ regular_bolt12_recipients: regularBolt12.length,
+ },
+ null,
+ 2,
+ ),
+ );
+ process.exit(0);
+}
+
+const line = (s = "") => console.log(s);
+const bar = (n: number, max: number, width = 30) =>
+ "█".repeat(Math.round((n / max) * width)) + "·".repeat(width - Math.round((n / max) * width));
+
+line("═".repeat(64));
+line(` COINOS ANALYTICS — last ${argDays} days (${new Date(now).toISOString().slice(0, 16)}Z)`);
+line(` walked ${scanned.toLocaleString()} payment keys in ${walkSecs}s`);
+line("═".repeat(64));
+
+line("\n▏USERS");
+line(` Total user records ........ ${userRecords.toLocaleString()}`);
+line(` New users (window) ........ ${newUsers.toLocaleString()}`);
+line(` MAU (active ${argDays}d) ......... ${activeUids.size.toLocaleString()}`);
+line(` WAU (active 7d) ........... ${weekActive.size.toLocaleString()}`);
+line(` Payments (window) ......... ${paymentsInWindow.toLocaleString()}`);
+
+line("\n▏CUSTODY");
+line(` Total custodied ........... ${fmtSats(totalCustodied)} (${fmtBtc(totalCustodied)})`);
+line(` Holders (>0 bal) .......... ${holders.length.toLocaleString()}`);
+line(` Fee revenue (window) ...... ${fmtSats(feeRevenue)}`);
+
+line("\n▏NETWORK VOLUME (gross sats moved, window)");
+const typesSorted = Object.entries(byType)
+ .filter(([, a]) => a.count > 0)
+ .sort((a, b) => b[1].volume - a[1].volume);
+const maxVol = Math.max(...typesSorted.map(([, a]) => a.volume), 1);
+for (const [t, a] of typesSorted) {
+ const pct = ((a.volume / totalVolume) * 100).toFixed(1);
+ line(` ${t.padEnd(10)} ${bar(a.volume, maxVol, 24)} ${pct.padStart(5)}% ${a.count.toLocaleString()} txns`);
+ line(` ${fmtSats(a.volume).padEnd(22)} (${fmtBtc(a.volume)})`);
+}
+
+line("\n▏TOP HOLDERS");
+holders.slice(0, TOP).forEach((h, i) =>
+ line(` ${String(i + 1).padStart(2)}. ${nameOf(h.uid).padEnd(24)} ${fmtSats(h.bal)}`),
+);
+
+line("\n▏MOST ACTIVE USERS (by payment count)");
+mostActive.forEach(([uid, v], i) =>
+ line(` ${String(i + 1).padStart(2)}. ${nameOf(uid).padEnd(24)} ${v.count.toLocaleString()} txns, ${fmtSats(v.volume)}`),
+);
+
+line("\n▏HIGHEST VOLUME USERS (gross sats moved)");
+highestVolume.forEach(([uid, v], i) =>
+ line(` ${String(i + 1).padStart(2)}. ${nameOf(uid).padEnd(24)} ${fmtSats(v.volume)} (${v.count} txns)`),
+);
+
+line("\n▏BOLT12 (Ocean mining rewards etc.)");
+line(` Distinct bolt12 recipients ........ ${bolt12Recipients.size.toLocaleString()}`);
+line(` Regular recipients (>=4 receives) . ${regularBolt12.length.toLocaleString()}`);
+regularBolt12.slice(0, TOP).forEach(([uid, n], i) =>
+ line(` ${String(i + 1).padStart(2)}. ${nameOf(uid).padEnd(24)} ${n} bolt12 receives`),
+);
+
+if (largest)
+ line(`\n Largest single payment: ${fmtSats(largest.amount)} (${largest.type}) by ${nameOf(largest.uid)}`);
+
+line("\n" + "═".repeat(64));
+process.exit(0);
diff --git a/scripts/audit-lightning-backlog.ts b/scripts/audit-lightning-backlog.ts
new file mode 100644
index 00000000..4c78e295
--- /dev/null
+++ b/scripts/audit-lightning-backlog.ts
@@ -0,0 +1,76 @@
+import { db } from "$lib/db";
+import ln from "$lib/ln";
+import { getInvoice, getPayment, getUser } from "$lib/utils";
+
+const max = Number(process.argv[2] || 10_000);
+const stored = Number((await db.get("pay_index")) || 0);
+let cursor = stored;
+const rows: any[] = [];
+
+for (let i = 0; i < max; i++) {
+ let settled: any;
+ try {
+ settled = await ln.waitanyinvoice(cursor, 1);
+ } catch (e: any) {
+ if (Number(e?.code ?? e?.errno) === 904) break;
+ throw e;
+ }
+
+ if (!settled?.pay_index || Number(settled.pay_index) <= cursor) break;
+ cursor = Number(settled.pay_index);
+
+ const mintOwned =
+ typeof settled.label === "string" && settled.label.startsWith("lbl");
+ const paymentRequest = settled.bolt11 || settled.bolt12;
+ const invoiceKey = settled.bolt11 || settled.local_offer_id || settled.bolt12;
+ const invoice = mintOwned ? null : await getInvoice(invoiceKey);
+ const payment = paymentRequest ? await getPayment(paymentRequest) : null;
+ const user = invoice?.uid ? await getUser(invoice.uid) : null;
+ const claim = settled.payment_preimage
+ ? await db.get(`credited:${settled.payment_preimage}`)
+ : null;
+
+ rows.push({
+ payIndex: cursor,
+ amountSats: Math.round(Number(settled.amount_received_msat || 0) / 1000),
+ mintOwned,
+ invoiceFound: !!invoice,
+ paymentFound: !!payment,
+ claimFound: !!claim,
+ username: user?.username,
+ autowithdraw: !!user?.autowithdraw,
+ });
+}
+
+const coinos = rows.filter((row) => !row.mintOwned);
+const actionable = coinos.filter(
+ (row) => row.invoiceFound && !row.paymentFound && !row.claimFound,
+);
+
+console.log(
+ JSON.stringify(
+ {
+ storedPayIndex: stored,
+ latestPayIndex: cursor,
+ backlog: rows.length,
+ mintOwned: rows.filter((row) => row.mintOwned).length,
+ coinos: coinos.length,
+ alreadyProcessed: coinos.filter((row) => row.paymentFound).length,
+ missingInvoice: coinos.filter((row) => !row.invoiceFound).length,
+ claimedWithoutPayment: coinos.filter(
+ (row) => row.claimFound && !row.paymentFound,
+ ).length,
+ actionable: actionable.length,
+ actionableSats: actionable.reduce(
+ (total, row) => total + row.amountSats,
+ 0,
+ ),
+ autowithdraw: actionable.filter((row) => row.autowithdraw).length,
+ rows,
+ },
+ null,
+ 2,
+ ),
+);
+
+process.exit(0);
diff --git a/scripts/audit-missing-history.ts b/scripts/audit-missing-history.ts
new file mode 100644
index 00000000..30c2c8fc
--- /dev/null
+++ b/scripts/audit-missing-history.ts
@@ -0,0 +1,106 @@
+#!/usr/bin/env bun
+// Audit how widespread missing payment history is and classify WHY.
+//
+// For each user with a non-empty :payments list, check each referenced
+// payment:<id> across db and arc, and bucket the user:
+// - clean: all records resolve (db or arc)
+// - recoverable: some/all records missing from db but present in arc
+// - lost: some records missing from BOTH db and arc (true loss)
+// Also records per-payment counts so we can size a restore.
+//
+// Read-only. Samples by default; pass --full to scan every user (slower).
+// Usage: bun scripts/audit-missing-history.ts [--full] [--sample N] [--examples N]
+
+import { createClient } from "redis";
+
+const full = process.argv.includes("--full");
+const sampleN = Number(process.argv[process.argv.indexOf("--sample") + 1]) || 4000;
+const exN = Number(process.argv[process.argv.indexOf("--examples") + 1]) || 15;
+
+const db = createClient({ url: "redis://127.0.0.1:6379", socket: { reconnectStrategy: () => false } });
+const arc = createClient({ url: "redis://127.0.0.1:6380", socket: { reconnectStrategy: () => false } });
+await db.connect();
+await arc.connect();
+
+// Resolve uid -> username lazily (only for examples) to keep memory low.
+async function nameOf(uid: string) {
+ const u = await db.get(`user:${uid}`);
+ if (u && u[0] === "{") { try { return JSON.parse(u).username || uid.slice(0, 8); } catch {} }
+ return uid.slice(0, 8);
+}
+
+let usersScanned = 0;
+let usersWithPayments = 0;
+const bucket = { clean: 0, recoverable: 0, lost: 0 };
+let totalRefs = 0, inDb = 0, inArcOnly = 0, missingBoth = 0;
+const examples: { recoverable: string[]; lost: string[] } = { recoverable: [], lost: [] };
+
+// Helper: does payment:<id> exist in db / arc? (batched EXISTS)
+async function classifyUser(uid: string): Promise<"clean" | "recoverable" | "lost" | null> {
+ const pids = await db.lRange(`${uid}:payments`, 0, -1);
+ if (!pids.length) return null;
+ usersWithPayments++;
+
+ let anyArcOnly = false, anyMissing = false;
+ for (const pid of pids) {
+ totalRefs++;
+ const key = `payment:${pid}`;
+ const dbHas = await db.exists(key);
+ if (dbHas) { inDb++; continue; }
+ const arcHas = await arc.exists(key);
+ if (arcHas) { inArcOnly++; anyArcOnly = true; }
+ else { missingBoth++; anyMissing = true; }
+ }
+ if (anyMissing) return "lost";
+ if (anyArcOnly) return "recoverable";
+ return "clean";
+}
+
+const t0 = Date.now();
+let processed = 0;
+for await (const key of db.scanIterator({ MATCH: "user:*", COUNT: 1000 })) {
+ // Only the canonical record key user:<uuid> (skip user:<username> pointers).
+ const id = (key as string).slice("user:".length);
+ if (!/^[0-9a-f]{8}-[0-9a-f]{4}-/.test(id)) continue;
+ usersScanned++;
+ if (!full && usersScanned > sampleN) break;
+
+ const cls = await classifyUser(id);
+ if (!cls) continue;
+ bucket[cls]++;
+ if (cls === "recoverable" && examples.recoverable.length < exN) examples.recoverable.push(id);
+ if (cls === "lost" && examples.lost.length < exN) examples.lost.push(id);
+ processed++;
+}
+const secs = ((Date.now() - t0) / 1000).toFixed(1);
+
+const totalClassified = bucket.clean + bucket.recoverable + bucket.lost;
+const pct = (n: number) => totalClassified ? ((n / totalClassified) * 100).toFixed(1) + "%" : "0%";
+
+console.log("═".repeat(66));
+console.log(` MISSING-HISTORY AUDIT — ${full ? "FULL scan" : `sample of ${sampleN} users`}`);
+console.log(` scanned ${usersScanned} user records, ${usersWithPayments} with payments, in ${secs}s`);
+console.log("═".repeat(66));
+console.log(`\n Users with payment history: ${totalClassified}`);
+console.log(` clean (all records present) ...... ${bucket.clean} (${pct(bucket.clean)})`);
+console.log(` recoverable (in arc, not db) ..... ${bucket.recoverable} (${pct(bucket.recoverable)}) <- restorable`);
+console.log(` lost (missing from db AND arc) ... ${bucket.lost} (${pct(bucket.lost)}) <- true loss`);
+console.log(`\n Payment records referenced: ${totalRefs}`);
+console.log(` in db ............ ${inDb} (${((inDb/totalRefs)*100||0).toFixed(1)}%)`);
+console.log(` in arc only ...... ${inArcOnly} (${((inArcOnly/totalRefs)*100||0).toFixed(1)}%) <- restorable`);
+console.log(` missing both ..... ${missingBoth} (${((missingBoth/totalRefs)*100||0).toFixed(1)}%) <- gone`);
+
+if (examples.recoverable.length) {
+ console.log(`\n Example RECOVERABLE users:`);
+ for (const uid of examples.recoverable) console.log(` ${await nameOf(uid)} (${uid})`);
+}
+if (examples.lost.length) {
+ console.log(`\n Example LOST-record users:`);
+ for (const uid of examples.lost) console.log(` ${await nameOf(uid)} (${uid})`);
+}
+if (!full) {
+ const est = Math.round((bucket.recoverable / Math.min(usersScanned, sampleN)) * usersScanned);
+ console.log(`\n (sample only — run --full for exact totals)`);
+}
+console.log("═".repeat(66));
+process.exit(0);
diff --git a/scripts/audit-wiped-history.ts b/scripts/audit-wiped-history.ts
new file mode 100644
index 00000000..d4e94645
--- /dev/null
+++ b/scripts/audit-wiped-history.ts
@@ -0,0 +1,85 @@
+#!/usr/bin/env bun
+// Find accounts whose payment history index was wiped — the changeid bug
+// signature, distinct from the harmless arc-only archived state.
+//
+// Two buckets:
+// wiped-with-balance: balance > 0 but :payments list is empty in BOTH db and
+// arc. Real funds, no visible history. These are the changeid-rekeyed
+// accounts (password == "reset") whose list entries got dropped. Highest
+// priority — user sees a balance with no transactions.
+// wiped-no-balance: :payments empty in both, balance 0, but has :invoices
+// (so they did transact once). History gone but nothing owed; lower priority.
+//
+// Read-only. Usage: bun scripts/audit-wiped-history.ts [--full] [--examples N]
+
+import { createClient } from "redis";
+
+const full = process.argv.includes("--full");
+const exN = Number(process.argv[process.argv.indexOf("--examples") + 1]) || 30;
+
+const db = createClient({ url: "redis://127.0.0.1:6379", socket: { reconnectStrategy: () => false } });
+const arc = createClient({ url: "redis://127.0.0.1:6380", socket: { reconnectStrategy: () => false } });
+await db.connect();
+await arc.connect();
+
+let scanned = 0;
+const wipedWithBal: { name: string; uid: string; bal: number; reset: boolean }[] = [];
+const wipedNoBal: { name: string; uid: string; invoices: number }[] = [];
+let cleanCount = 0;
+
+const t0 = Date.now();
+for await (const batch of db.scanIterator({ MATCH: "user:*", COUNT: 2000 })) {
+ // This redis client version yields ARRAYS of keys per iteration, not single keys.
+ const keys = Array.isArray(batch) ? batch : [batch];
+ for (const key of keys) {
+ const uid = (key as string).slice("user:".length);
+ if (!/^[0-9a-f]{8}-[0-9a-f]{4}-/.test(uid)) continue; // skip username pointers
+ scanned++;
+
+ const dbLen = await db.lLen(`${uid}:payments`);
+ if (dbLen > 0) { cleanCount++; continue; } // has visible history
+
+ // Empty in db — is it also empty in arc, or just archived?
+ const arcLen = await arc.lLen(`${uid}:payments`);
+ if (arcLen > 0) { cleanCount++; continue; } // renders via gf(), fine
+
+ // :payments empty in both. Does it matter? Check balance + whether they ever transacted.
+ const bal = parseInt((await db.get(`balance:${uid}`)) || "0", 10);
+ const invLen = await db.lLen(`${uid}:invoices`);
+
+ if (bal > 0) {
+ const u = await db.get(`user:${uid}`);
+ let name = uid.slice(0, 8), reset = false;
+ if (u && u[0] === "{") { try { const j = JSON.parse(u); name = j.username || name; reset = j.password === "reset"; } catch {} }
+ wipedWithBal.push({ name, uid, bal, reset });
+ } else if (invLen > 0) {
+ const u = await db.get(`user:${uid}`);
+ let name = uid.slice(0, 8);
+ if (u && u[0] === "{") { try { name = JSON.parse(u).username || name; } catch {} }
+ wipedNoBal.push({ name, uid, invoices: invLen });
+ }
+ }
+}
+const secs = ((Date.now() - t0) / 1000).toFixed(1);
+
+wipedWithBal.sort((a, b) => b.bal - a.bal);
+
+console.log("═".repeat(70));
+console.log(` WIPED-HISTORY AUDIT — scanned ${scanned} users in ${secs}s`);
+console.log("═".repeat(70));
+console.log(`\n Accounts with BALANCE but no history (priority — funds visible, no txns):`);
+console.log(` count: ${wipedWithBal.length}`);
+const totalBal = wipedWithBal.reduce((s, x) => s + x.bal, 0);
+console.log(` total balance affected: ${totalBal.toLocaleString()} sats`);
+console.log(` (of which password=="reset" / changeid'd: ${wipedWithBal.filter((x) => x.reset).length})`);
+console.log("");
+for (const x of wipedWithBal.slice(0, exN)) {
+ console.log(` ${x.name.padEnd(24)} ${(x.bal + "").padStart(12)} sats ${x.reset ? "[changeid]" : ""} ${x.uid}`);
+}
+
+console.log(`\n Accounts wiped, no balance (had invoices, lower priority): ${wipedNoBal.length}`);
+for (const x of wipedNoBal.slice(0, 10)) {
+ console.log(` ${x.name.padEnd(24)} ${x.invoices} invoices ${x.uid}`);
+}
+console.log("═".repeat(70));
+process.exit(0);
diff --git a/scripts/boltz-recover-stuck.ts b/scripts/boltz-recover-stuck.ts
new file mode 100644
index 00000000..55393f40
--- /dev/null
+++ b/scripts/boltz-recover-stuck.ts
@@ -0,0 +1,91 @@
+// boltz-recover-stuck.ts — auto-recover boltz submarine swaps whose Lightning
+// payment got stuck, using the stubborn peer-force payer (pay-hard.ts).
+//
+// Background: boltz-backend pays a submarine swap's destination invoice with
+// CLN's basic `pay` and gives up after a few minutes. To inbound-scarce exchange
+// destinations (fixedfloat), that leaves the user's on-chain funds locked and the
+// swap stuck at `invoice.pending` — exactly what happened 2026-08-27. pay-hard's
+// peer-force strategy completes those. After the invoice is paid on bcln, boltz
+// reconciles (picks up the preimage and claims the lockup) on its next restart;
+// if it hasn't within a grace period, we restart it once.
+//
+// Runs on a cron from the coinos-server host (reaches cl locally and bcln/boltz
+// on cs via ssh). Quiet unless it finds a stuck swap. Log: ~/boltz-recover.log
+//
+// Usage: bun scripts/boltz-recover-stuck.ts (add --dry to detect+probe only)
+import { execSync } from "child_process";
+import { appendFileSync, writeFileSync } from "fs";
+
+const DRY = process.argv.includes("--dry");
+const LOG = "/home/adam/boltz-recover.log";
+const PAYHARD = "/home/adam/coinos-server/scripts/pay-hard.ts";
+const BUN = "/home/adam/.bun/bin/bun";
+const STUCK_MIN = 10; // only touch swaps idle at least this long (don't race boltz)
+const MAX_PER_RUN = 5; // safety cap
+const BCLN = "docker exec bcln lightning-cli --lightning-dir=/app/lightning";
+
+const sh = (c: string) => { try { return execSync(c, { encoding: "utf8", maxBuffer: 1e8 }); } catch (e: any) { return (e.stdout || "") + (e.stderr || ""); } };
+const log = (m: string) => { const line = `[${new Date().toISOString()}] ${m}`; try { appendFileSync(LOG, line + "\n"); } catch {} console.log(line); };
+const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
+
+// run a SQL query against boltz-postgres on cs (query via stdin -> no quoting hell)
+function psql(query: string): string[] {
+ writeFileSync("/tmp/boltz_recover_q.sql", query);
+ const out = sh(`ssh -o ConnectTimeout=15 cs 'docker exec -i boltz-postgres psql -U boltz -d boltz -tAF"|"' < /tmp/boltz_recover_q.sql`);
+ return out.trim().split("\n").filter(Boolean);
+}
+const bcln = (sub: string) => sh(`ssh -o ConnectTimeout=15 cs '${BCLN} ${sub}'`);
+
+// ---- find stuck submarine swaps ----
+// `swaps` = submarine swaps (user locks on-chain, boltz pays the LN invoice).
+const rows = psql(
+ `select id, invoice from swaps ` +
+ `where status='invoice.pending' and "lockupTransactionId" is not null and invoice is not null ` +
+ `and "updatedAt" < now() - interval '${STUCK_MIN} minutes' ` +
+ `order by "updatedAt" asc limit ${MAX_PER_RUN}`,
+).map((l) => { const [id, invoice] = l.split("|"); return { id, invoice }; });
+
+if (!rows.length) process.exit(0); // quiet: nothing stuck
+log(`found ${rows.length} stuck submarine swap(s): ${rows.map((r) => r.id).join(", ")}`);
+
+const paidIds: string[] = [];
+for (const { id, invoice } of rows) {
+ // skip expired invoices — those are a refund case, not ours to force
+ let dec: any = null;
+ try { dec = JSON.parse(bcln(`decode ${invoice}`)); } catch {}
+ if (dec?.created_at && dec?.expiry && Date.now() / 1000 > dec.created_at + dec.expiry) {
+ log(` ${id}: invoice expired — skipping (refund path)`);
+ continue;
+ }
+
+ if (DRY) {
+ const out = sh(`${BUN} ${PAYHARD} ${invoice} --node=bcln --dry`);
+ log(` ${id}: [dry] ${out.trim().split("\n").pop()}`);
+ continue;
+ }
+
+ log(` ${id}: running pay-hard…`);
+ const out = sh(`${BUN} ${PAYHARD} ${invoice} --node=bcln --maxfee=6000`);
+ const last = out.trim().split("\n").pop() || "";
+ const paid = /🎉 PAID|already paid|xpay succeeded/.test(out);
+ log(` → ${last}`);
+ if (paid) paidIds.push(id);
+}
+
+// ---- reconcile: make boltz claim the lockups for swaps we just paid ----
+if (paidIds.length && !DRY) {
+ await sleep(45_000); // give boltz a chance to notice on its own
+ const still = psql(
+ `select id from swaps where id in (${paidIds.map((i) => `'${i}'`).join(",")}) and status='invoice.pending'`,
+ );
+ if (still.length) {
+ log(`boltz did not auto-reconcile ${still.length} paid swap(s) [${still.join(",")}] — restarting boltz to claim`);
+ sh(`ssh -o ConnectTimeout=20 cs 'docker restart boltz'`);
+ await sleep(20_000);
+ const after = psql(`select id, status from swaps where id in (${paidIds.map((i) => `'${i}'`).join(",")})`);
+ log(` post-restart status: ${after.join(" ; ")}`);
+ } else {
+ log(`boltz reconciled all ${paidIds.length} paid swap(s) on its own`);
+ }
+}
+process.exit(0);
diff --git a/scripts/cln_schema.sql b/scripts/cln_schema.sql
new file mode 100644
index 00000000..e8bfa499
--- /dev/null
+++ b/scripts/cln_schema.sql
@@ -0,0 +1,1783 @@
+--
+-- PostgreSQL database dump
+--
+
+\restrict 3gmSHpmWoAvp3M9hYnbn0eAJg2qkrJcZzqafmBmvLGVxDFS2cVhidb6yKDBdS99
+
+-- Dumped from database version 18.3 (Debian 18.3-1.pgdg13+1)
+-- Dumped by pg_dump version 18.3 (Debian 18.3-1.pgdg13+1)
+
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET idle_in_transaction_session_timeout = 0;
+SET transaction_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SELECT pg_catalog.set_config('search_path', '', false);
+SET check_function_bodies = false;
+SET xmloption = content;
+SET client_min_messages = warning;
+SET row_security = off;
+
+SET default_tablespace = '';
+
+SET default_table_access_method = heap;
+
+--
+-- Name: addresses; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.addresses (
+ keyidx bigint,
+ addrtype integer
+);
+
+
+ALTER TABLE public.addresses OWNER TO lightning;
+
+--
+-- Name: blocks; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.blocks (
+ height integer,
+ hash bytea,
+ prev_hash bytea
+);
+
+
+ALTER TABLE public.blocks OWNER TO lightning;
+
+--
+-- Name: chain_moves; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.chain_moves (
+ id bigint NOT NULL,
+ account_channel_id bigint,
+ account_nonchannel_id bigint,
+ tag_bitmap bigint NOT NULL,
+ credit_or_debit bigint NOT NULL,
+ "timestamp" bigint NOT NULL,
+ utxo bytea NOT NULL,
+ spending_txid bytea,
+ peer_id bytea,
+ payment_hash bytea,
+ block_height integer NOT NULL,
+ output_sat bigint NOT NULL,
+ originating_channel_id bigint,
+ originating_nonchannel_id bigint,
+ output_count integer
+);
+
+
+ALTER TABLE public.chain_moves OWNER TO lightning;
+
+--
+-- Name: chain_moves_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.chain_moves_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.chain_moves_id_seq OWNER TO lightning;
+
+--
+-- Name: chain_moves_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.chain_moves_id_seq OWNED BY public.chain_moves.id;
+
+
+--
+-- Name: channel_blockheights; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_blockheights (
+ channel_id bigint,
+ hstate integer,
+ blockheight integer
+);
+
+
+ALTER TABLE public.channel_blockheights OWNER TO lightning;
+
+--
+-- Name: channel_configs; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_configs (
+ id bigint NOT NULL,
+ dust_limit_satoshis bigint,
+ max_htlc_value_in_flight_msat bigint,
+ channel_reserve_satoshis bigint,
+ htlc_minimum_msat bigint,
+ to_self_delay integer,
+ max_accepted_htlcs integer,
+ max_dust_htlc_exposure_msat bigint DEFAULT 50000000
+);
+
+
+ALTER TABLE public.channel_configs OWNER TO lightning;
+
+--
+-- Name: channel_configs_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.channel_configs_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.channel_configs_id_seq OWNER TO lightning;
+
+--
+-- Name: channel_configs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.channel_configs_id_seq OWNED BY public.channel_configs.id;
+
+
+--
+-- Name: channel_feerates; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_feerates (
+ channel_id bigint,
+ hstate integer,
+ feerate_per_kw integer
+);
+
+
+ALTER TABLE public.channel_feerates OWNER TO lightning;
+
+--
+-- Name: channel_funding_inflights; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_funding_inflights (
+ channel_id bigint NOT NULL,
+ funding_tx_id bytea NOT NULL,
+ funding_tx_outnum integer,
+ funding_feerate integer,
+ funding_satoshi bigint,
+ our_funding_satoshi bigint,
+ funding_psbt bytea,
+ last_tx bytea,
+ last_sig bytea,
+ funding_tx_remote_sigs_received integer,
+ lease_commit_sig bytea,
+ lease_chan_max_msat bigint,
+ lease_chan_max_ppt integer,
+ lease_expiry integer DEFAULT 0,
+ lease_blockheight_start integer DEFAULT 0,
+ lease_fee bigint DEFAULT 0,
+ lease_satoshi bigint,
+ splice_amnt bigint DEFAULT 0,
+ i_am_initiator integer DEFAULT 0,
+ force_sign_first integer DEFAULT 0,
+ remote_funding bytea,
+ locked_scid bigint DEFAULT 0,
+ i_sent_sigs integer DEFAULT 0
+);
+
+
+ALTER TABLE public.channel_funding_inflights OWNER TO lightning;
+
+--
+-- Name: channel_funding_inflights_channel_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.channel_funding_inflights_channel_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.channel_funding_inflights_channel_id_seq OWNER TO lightning;
+
+--
+-- Name: channel_funding_inflights_channel_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.channel_funding_inflights_channel_id_seq OWNED BY public.channel_funding_inflights.channel_id;
+
+
+--
+-- Name: channel_htlcs; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_htlcs (
+ id bigint NOT NULL,
+ channel_id bigint,
+ channel_htlc_id bigint,
+ direction integer,
+ origin_htlc bigint,
+ msatoshi bigint,
+ cltv_expiry integer,
+ payment_hash bytea,
+ payment_key bytea,
+ routing_onion bytea,
+ failuremsg bytea,
+ malformed_onion integer,
+ hstate integer,
+ shared_secret bytea,
+ received_time bigint,
+ partid bigint,
+ localfailmsg bytea,
+ we_filled integer,
+ groupid bigint,
+ min_commit_num bigint DEFAULT 0,
+ max_commit_num bigint,
+ fail_immediate integer DEFAULT 0,
+ fees_msat bigint DEFAULT 0,
+ updated_index bigint DEFAULT 0
+);
+
+
+ALTER TABLE public.channel_htlcs OWNER TO lightning;
+
+--
+-- Name: channel_htlcs_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.channel_htlcs_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.channel_htlcs_id_seq OWNER TO lightning;
+
+--
+-- Name: channel_htlcs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.channel_htlcs_id_seq OWNED BY public.channel_htlcs.id;
+
+
+--
+-- Name: channel_moves; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_moves (
+ id bigint NOT NULL,
+ account_channel_id bigint,
+ account_nonchannel_id bigint,
+ tag_bitmap bigint NOT NULL,
+ credit_or_debit bigint NOT NULL,
+ "timestamp" bigint NOT NULL,
+ payment_hash bytea,
+ payment_part_id bigint,
+ payment_group_id bigint,
+ fees bigint NOT NULL
+);
+
+
+ALTER TABLE public.channel_moves OWNER TO lightning;
+
+--
+-- Name: channel_moves_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.channel_moves_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.channel_moves_id_seq OWNER TO lightning;
+
+--
+-- Name: channel_moves_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.channel_moves_id_seq OWNED BY public.channel_moves.id;
+
+
+--
+-- Name: channel_state_changes; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channel_state_changes (
+ channel_id bigint,
+ "timestamp" bigint,
+ old_state integer,
+ new_state integer,
+ cause integer,
+ message text
+);
+
+
+ALTER TABLE public.channel_state_changes OWNER TO lightning;
+
+--
+-- Name: channels; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channels (
+ id bigint NOT NULL,
+ peer_id bigint,
+ short_channel_id text,
+ channel_config_local bigint,
+ channel_config_remote bigint,
+ state integer,
+ funder integer,
+ channel_flags integer,
+ minimum_depth integer,
+ next_index_local bigint,
+ next_index_remote bigint,
+ next_htlc_id bigint,
+ funding_tx_id bytea,
+ funding_tx_outnum integer,
+ funding_satoshi bigint,
+ funding_locked_remote integer,
+ push_msatoshi bigint,
+ msatoshi_local bigint,
+ fundingkey_remote bytea,
+ revocation_basepoint_remote bytea,
+ payment_basepoint_remote bytea,
+ htlc_basepoint_remote bytea,
+ delayed_payment_basepoint_remote bytea,
+ per_commit_remote bytea,
+ old_per_commit_remote bytea,
+ local_feerate_per_kw integer,
+ remote_feerate_per_kw integer,
+ shachain_remote_id bigint,
+ shutdown_scriptpubkey_remote bytea,
+ shutdown_keyidx_local bigint,
+ last_sent_commit_state bigint,
+ last_sent_commit_id integer,
+ last_tx bytea,
+ last_sig bytea,
+ closing_fee_received integer,
+ closing_sig_received bytea,
+ first_blocknum bigint,
+ last_was_revoke integer,
+ in_payments_offered integer DEFAULT 0,
+ in_payments_fulfilled integer DEFAULT 0,
+ in_msatoshi_offered bigint DEFAULT 0,
+ in_msatoshi_fulfilled bigint DEFAULT 0,
+ out_payments_offered integer DEFAULT 0,
+ out_payments_fulfilled integer DEFAULT 0,
+ out_msatoshi_offered bigint DEFAULT 0,
+ out_msatoshi_fulfilled bigint DEFAULT 0,
+ min_possible_feerate integer,
+ max_possible_feerate integer,
+ msatoshi_to_us_min bigint,
+ msatoshi_to_us_max bigint,
+ future_per_commitment_point bytea,
+ last_sent_commit bytea,
+ feerate_base integer,
+ feerate_ppm integer,
+ remote_upfront_shutdown_script bytea,
+ remote_ann_node_sig bytea,
+ remote_ann_bitcoin_sig bytea,
+ option_static_remotekey integer DEFAULT 0,
+ shutdown_scriptpubkey_local bytea,
+ our_funding_satoshi bigint DEFAULT 0,
+ option_anchor_outputs integer DEFAULT 0,
+ full_channel_id bytea,
+ funding_psbt bytea,
+ closer integer DEFAULT 2,
+ state_change_reason integer DEFAULT 0,
+ funding_tx_remote_sigs_received integer DEFAULT 0,
+ revocation_basepoint_local bytea,
+ payment_basepoint_local bytea,
+ htlc_basepoint_local bytea,
+ delayed_payment_basepoint_local bytea,
+ funding_pubkey_local bytea,
+ shutdown_wrong_txid bytea,
+ shutdown_wrong_outnum integer,
+ local_static_remotekey_start bigint DEFAULT 0,
+ remote_static_remotekey_start bigint DEFAULT 0,
+ lease_commit_sig bytea,
+ lease_chan_max_msat integer,
+ lease_chan_max_ppt integer,
+ lease_expiry integer DEFAULT 0,
+ htlc_maximum_msat bigint DEFAULT '2100000000000000'::bigint,
+ htlc_minimum_msat bigint DEFAULT 0,
+ alias_local bigint,
+ alias_remote bigint,
+ scid bigint,
+ require_confirm_inputs_remote integer DEFAULT 0,
+ require_confirm_inputs_local integer DEFAULT 0,
+ channel_type bytea,
+ ignore_fee_limits integer DEFAULT 0,
+ remote_feerate_base integer,
+ remote_feerate_ppm integer,
+ remote_cltv_expiry_delta integer,
+ remote_htlc_maximum_msat bigint,
+ remote_htlc_minimum_msat bigint,
+ last_stable_connection bigint DEFAULT 0,
+ close_attempt_height integer DEFAULT 0,
+ old_scids bytea,
+ withheld integer DEFAULT 0
+);
+
+
+ALTER TABLE public.channels OWNER TO lightning;
+
+--
+-- Name: channels_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.channels_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.channels_id_seq OWNER TO lightning;
+
+--
+-- Name: channels_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.channels_id_seq OWNED BY public.channels.id;
+
+
+--
+-- Name: channeltxs; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.channeltxs (
+ id bigint NOT NULL,
+ channel_id bigint,
+ type integer,
+ transaction_id bytea,
+ input_num integer,
+ blockheight integer
+);
+
+
+ALTER TABLE public.channeltxs OWNER TO lightning;
+
+--
+-- Name: channeltxs_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.channeltxs_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.channeltxs_id_seq OWNER TO lightning;
+
+--
+-- Name: channeltxs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.channeltxs_id_seq OWNED BY public.channeltxs.id;
+
+
+--
+-- Name: datastore; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.datastore (
+ key bytea NOT NULL,
+ data bytea,
+ generation bigint
+);
+
+
+ALTER TABLE public.datastore OWNER TO lightning;
+
+--
+-- Name: db_upgrades; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.db_upgrades (
+ upgrade_from integer,
+ lightning_version text
+);
+
+
+ALTER TABLE public.db_upgrades OWNER TO lightning;
+
+--
+-- Name: forwards; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.forwards (
+ in_channel_scid bigint NOT NULL,
+ in_htlc_id bigint NOT NULL,
+ out_channel_scid bigint,
+ out_htlc_id bigint,
+ in_msatoshi bigint,
+ out_msatoshi bigint,
+ state integer,
+ received_time bigint,
+ resolved_time bigint,
+ failcode integer,
+ forward_style integer,
+ rowid bigint,
+ updated_index bigint DEFAULT 0
+);
+
+
+ALTER TABLE public.forwards OWNER TO lightning;
+
+--
+-- Name: htlc_sigs; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.htlc_sigs (
+ channelid integer,
+ signature bytea,
+ inflight_tx_id bytea,
+ inflight_tx_outnum integer
+);
+
+
+ALTER TABLE public.htlc_sigs OWNER TO lightning;
+
+--
+-- Name: invoice_fallbacks; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.invoice_fallbacks (
+ scriptpubkey bytea NOT NULL,
+ invoice_id bigint
+);
+
+
+ALTER TABLE public.invoice_fallbacks OWNER TO lightning;
+
+--
+-- Name: invoicerequests; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.invoicerequests (
+ invreq_id bytea NOT NULL,
+ bolt12 text,
+ label text,
+ status integer
+);
+
+
+ALTER TABLE public.invoicerequests OWNER TO lightning;
+
+--
+-- Name: invoices; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.invoices (
+ id bigint NOT NULL,
+ state integer,
+ msatoshi bigint,
+ payment_hash bytea,
+ payment_key bytea,
+ label text,
+ expiry_time bigint,
+ pay_index bigint,
+ msatoshi_received bigint,
+ paid_timestamp bigint,
+ bolt11 text,
+ description text,
+ features bytea DEFAULT '\x'::bytea,
+ local_offer_id bytea,
+ updated_index bigint DEFAULT 0,
+ paid_txid bytea,
+ paid_outnum integer
+);
+
+
+ALTER TABLE public.invoices OWNER TO lightning;
+
+--
+-- Name: invoices_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.invoices_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.invoices_id_seq OWNER TO lightning;
+
+--
+-- Name: invoices_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.invoices_id_seq OWNED BY public.invoices.id;
+
+
+--
+-- Name: local_anchors; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.local_anchors (
+ channel_id bigint NOT NULL,
+ commitment_index bigint,
+ commitment_txid bytea,
+ commitment_anchor_outnum integer,
+ commitment_fee bigint,
+ commitment_weight integer
+);
+
+
+ALTER TABLE public.local_anchors OWNER TO lightning;
+
+--
+-- Name: local_anchors_channel_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.local_anchors_channel_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.local_anchors_channel_id_seq OWNER TO lightning;
+
+--
+-- Name: local_anchors_channel_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.local_anchors_channel_id_seq OWNED BY public.local_anchors.channel_id;
+
+
+--
+-- Name: move_accounts; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.move_accounts (
+ id bigint NOT NULL,
+ name text
+);
+
+
+ALTER TABLE public.move_accounts OWNER TO lightning;
+
+--
+-- Name: move_accounts_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.move_accounts_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.move_accounts_id_seq OWNER TO lightning;
+
+--
+-- Name: move_accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.move_accounts_id_seq OWNED BY public.move_accounts.id;
+
+
+--
+-- Name: network_events; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.network_events (
+ id bigint NOT NULL,
+ peer_id bytea NOT NULL,
+ type integer NOT NULL,
+ "timestamp" bigint,
+ reason text,
+ duration_nsec bigint,
+ connect_attempted integer NOT NULL
+);
+
+
+ALTER TABLE public.network_events OWNER TO lightning;
+
+--
+-- Name: network_events_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.network_events_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.network_events_id_seq OWNER TO lightning;
+
+--
+-- Name: network_events_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.network_events_id_seq OWNED BY public.network_events.id;
+
+
+--
+-- Name: offers; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.offers (
+ offer_id bytea NOT NULL,
+ bolt12 text,
+ label text,
+ status integer
+);
+
+
+ALTER TABLE public.offers OWNER TO lightning;
+
+--
+-- Name: outputs; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.outputs (
+ prev_out_tx bytea NOT NULL,
+ prev_out_index integer NOT NULL,
+ value bigint,
+ type integer,
+ status integer,
+ keyindex integer,
+ channel_id bigint,
+ peer_id bytea,
+ commitment_point bytea,
+ confirmation_height integer,
+ spend_height integer,
+ scriptpubkey bytea,
+ reserved_til integer,
+ option_anchor_outputs integer DEFAULT 0,
+ csv_lock integer DEFAULT 1,
+ is_in_coinbase integer DEFAULT 0
+);
+
+
+ALTER TABLE public.outputs OWNER TO lightning;
+
+--
+-- Name: payments; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.payments (
+ id bigint CONSTRAINT payments_id_not_null1 NOT NULL,
+ "timestamp" integer,
+ status integer,
+ payment_hash bytea,
+ destination bytea,
+ msatoshi bigint,
+ payment_preimage bytea,
+ path_secrets bytea,
+ route_nodes bytea,
+ route_channels bytea,
+ failonionreply bytea,
+ faildestperm integer,
+ failindex integer,
+ failcode integer,
+ failnode bytea,
+ failupdate bytea,
+ msatoshi_sent bigint,
+ faildetail text,
+ description text,
+ faildirection integer,
+ bolt11 text,
+ total_msat bigint,
+ partid bigint,
+ groupid bigint DEFAULT 0 NOT NULL,
+ local_offer_id bytea,
+ paydescription text,
+ completed_at integer,
+ failscid bigint,
+ local_invreq_id bytea,
+ updated_index bigint DEFAULT 0
+);
+
+
+ALTER TABLE public.payments OWNER TO lightning;
+
+--
+-- Name: payments_id_seq1; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.payments_id_seq1
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.payments_id_seq1 OWNER TO lightning;
+
+--
+-- Name: payments_id_seq1; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.payments_id_seq1 OWNED BY public.payments.id;
+
+
+--
+-- Name: peers; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.peers (
+ id bigint NOT NULL,
+ node_id bytea,
+ address text,
+ feature_bits bytea,
+ last_known_address bytea
+);
+
+
+ALTER TABLE public.peers OWNER TO lightning;
+
+--
+-- Name: peers_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.peers_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.peers_id_seq OWNER TO lightning;
+
+--
+-- Name: peers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.peers_id_seq OWNED BY public.peers.id;
+
+
+--
+-- Name: penalty_bases; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.penalty_bases (
+ channel_id bigint NOT NULL,
+ commitnum bigint NOT NULL,
+ txid bytea,
+ outnum integer,
+ amount bigint
+);
+
+
+ALTER TABLE public.penalty_bases OWNER TO lightning;
+
+--
+-- Name: runes; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.runes (
+ id bigint NOT NULL,
+ rune text,
+ last_used_nsec bigint
+);
+
+
+ALTER TABLE public.runes OWNER TO lightning;
+
+--
+-- Name: runes_blacklist; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.runes_blacklist (
+ start_index bigint,
+ end_index bigint
+);
+
+
+ALTER TABLE public.runes_blacklist OWNER TO lightning;
+
+--
+-- Name: runes_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.runes_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.runes_id_seq OWNER TO lightning;
+
+--
+-- Name: runes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.runes_id_seq OWNED BY public.runes.id;
+
+
+--
+-- Name: shachain_known; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.shachain_known (
+ shachain_id bigint NOT NULL,
+ pos integer NOT NULL,
+ idx bigint,
+ hash bytea
+);
+
+
+ALTER TABLE public.shachain_known OWNER TO lightning;
+
+--
+-- Name: shachains; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.shachains (
+ id bigint NOT NULL,
+ min_index bigint,
+ num_valid bigint
+);
+
+
+ALTER TABLE public.shachains OWNER TO lightning;
+
+--
+-- Name: shachains_id_seq; Type: SEQUENCE; Schema: public; Owner: lightning
+--
+
+CREATE SEQUENCE public.shachains_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER SEQUENCE public.shachains_id_seq OWNER TO lightning;
+
+--
+-- Name: shachains_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: lightning
+--
+
+ALTER SEQUENCE public.shachains_id_seq OWNED BY public.shachains.id;
+
+
+--
+-- Name: transaction_annotations; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.transaction_annotations (
+ txid bytea,
+ idx integer,
+ location integer,
+ type integer,
+ channel bigint
+);
+
+
+ALTER TABLE public.transaction_annotations OWNER TO lightning;
+
+--
+-- Name: transactions; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.transactions (
+ id bytea NOT NULL,
+ blockheight integer,
+ txindex integer,
+ rawtx bytea,
+ type bigint,
+ channel_id bigint
+);
+
+
+ALTER TABLE public.transactions OWNER TO lightning;
+
+--
+-- Name: utxoset; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.utxoset (
+ txid bytea NOT NULL,
+ outnum integer NOT NULL,
+ blockheight integer,
+ spendheight integer,
+ txindex integer,
+ scriptpubkey bytea,
+ satoshis bigint
+);
+
+
+ALTER TABLE public.utxoset OWNER TO lightning;
+
+--
+-- Name: vars; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.vars (
+ name character varying(32) NOT NULL,
+ val character varying(255),
+ intval integer,
+ blobval bytea
+);
+
+
+ALTER TABLE public.vars OWNER TO lightning;
+
+--
+-- Name: version; Type: TABLE; Schema: public; Owner: lightning
+--
+
+CREATE TABLE public.version (
+ version integer
+);
+
+
+ALTER TABLE public.version OWNER TO lightning;
+
+--
+-- Name: chain_moves id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.chain_moves ALTER COLUMN id SET DEFAULT nextval('public.chain_moves_id_seq'::regclass);
+
+
+--
+-- Name: channel_configs id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_configs ALTER COLUMN id SET DEFAULT nextval('public.channel_configs_id_seq'::regclass);
+
+
+--
+-- Name: channel_funding_inflights channel_id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_funding_inflights ALTER COLUMN channel_id SET DEFAULT nextval('public.channel_funding_inflights_channel_id_seq'::regclass);
+
+
+--
+-- Name: channel_htlcs id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_htlcs ALTER COLUMN id SET DEFAULT nextval('public.channel_htlcs_id_seq'::regclass);
+
+
+--
+-- Name: channel_moves id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_moves ALTER COLUMN id SET DEFAULT nextval('public.channel_moves_id_seq'::regclass);
+
+
+--
+-- Name: channels id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channels ALTER COLUMN id SET DEFAULT nextval('public.channels_id_seq'::regclass);
+
+
+--
+-- Name: channeltxs id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channeltxs ALTER COLUMN id SET DEFAULT nextval('public.channeltxs_id_seq'::regclass);
+
+
+--
+-- Name: invoices id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoices ALTER COLUMN id SET DEFAULT nextval('public.invoices_id_seq'::regclass);
+
+
+--
+-- Name: local_anchors channel_id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.local_anchors ALTER COLUMN channel_id SET DEFAULT nextval('public.local_anchors_channel_id_seq'::regclass);
+
+
+--
+-- Name: move_accounts id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.move_accounts ALTER COLUMN id SET DEFAULT nextval('public.move_accounts_id_seq'::regclass);
+
+
+--
+-- Name: network_events id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.network_events ALTER COLUMN id SET DEFAULT nextval('public.network_events_id_seq'::regclass);
+
+
+--
+-- Name: payments id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.payments ALTER COLUMN id SET DEFAULT nextval('public.payments_id_seq1'::regclass);
+
+
+--
+-- Name: peers id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.peers ALTER COLUMN id SET DEFAULT nextval('public.peers_id_seq'::regclass);
+
+
+--
+-- Name: runes id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.runes ALTER COLUMN id SET DEFAULT nextval('public.runes_id_seq'::regclass);
+
+
+--
+-- Name: shachains id; Type: DEFAULT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.shachains ALTER COLUMN id SET DEFAULT nextval('public.shachains_id_seq'::regclass);
+
+
+--
+-- Name: blocks blocks_height_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.blocks
+ ADD CONSTRAINT blocks_height_key UNIQUE (height);
+
+
+--
+-- Name: chain_moves chain_moves_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.chain_moves
+ ADD CONSTRAINT chain_moves_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: channel_blockheights channel_blockheights_channel_id_hstate_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_blockheights
+ ADD CONSTRAINT channel_blockheights_channel_id_hstate_key UNIQUE (channel_id, hstate);
+
+
+--
+-- Name: channel_configs channel_configs_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_configs
+ ADD CONSTRAINT channel_configs_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: channel_feerates channel_feerates_channel_id_hstate_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_feerates
+ ADD CONSTRAINT channel_feerates_channel_id_hstate_key UNIQUE (channel_id, hstate);
+
+
+--
+-- Name: channel_funding_inflights channel_funding_inflights_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_funding_inflights
+ ADD CONSTRAINT channel_funding_inflights_pkey PRIMARY KEY (channel_id, funding_tx_id);
+
+
+--
+-- Name: channel_htlcs channel_htlcs_channel_id_channel_htlc_id_direction_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_htlcs
+ ADD CONSTRAINT channel_htlcs_channel_id_channel_htlc_id_direction_key UNIQUE (channel_id, channel_htlc_id, direction);
+
+
+--
+-- Name: channel_htlcs channel_htlcs_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_htlcs
+ ADD CONSTRAINT channel_htlcs_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: channel_moves channel_moves_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_moves
+ ADD CONSTRAINT channel_moves_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: channels channels_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channels
+ ADD CONSTRAINT channels_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: channeltxs channeltxs_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channeltxs
+ ADD CONSTRAINT channeltxs_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: datastore datastore_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.datastore
+ ADD CONSTRAINT datastore_pkey PRIMARY KEY (key);
+
+
+--
+-- Name: forwards forwards_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.forwards
+ ADD CONSTRAINT forwards_pkey PRIMARY KEY (in_channel_scid, in_htlc_id);
+
+
+--
+-- Name: invoice_fallbacks invoice_fallbacks_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoice_fallbacks
+ ADD CONSTRAINT invoice_fallbacks_pkey PRIMARY KEY (scriptpubkey);
+
+
+--
+-- Name: invoicerequests invoicerequests_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoicerequests
+ ADD CONSTRAINT invoicerequests_pkey PRIMARY KEY (invreq_id);
+
+
+--
+-- Name: invoices invoices_label_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoices
+ ADD CONSTRAINT invoices_label_key UNIQUE (label);
+
+
+--
+-- Name: invoices invoices_payment_hash_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoices
+ ADD CONSTRAINT invoices_payment_hash_key UNIQUE (payment_hash);
+
+
+--
+-- Name: invoices invoices_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoices
+ ADD CONSTRAINT invoices_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: move_accounts move_accounts_name_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.move_accounts
+ ADD CONSTRAINT move_accounts_name_key UNIQUE (name);
+
+
+--
+-- Name: move_accounts move_accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.move_accounts
+ ADD CONSTRAINT move_accounts_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: network_events network_events_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.network_events
+ ADD CONSTRAINT network_events_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: offers offers_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.offers
+ ADD CONSTRAINT offers_pkey PRIMARY KEY (offer_id);
+
+
+--
+-- Name: outputs outputs_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.outputs
+ ADD CONSTRAINT outputs_pkey PRIMARY KEY (prev_out_tx, prev_out_index);
+
+
+--
+-- Name: payments payments_payment_hash_partid_groupid_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.payments
+ ADD CONSTRAINT payments_payment_hash_partid_groupid_key UNIQUE (payment_hash, partid, groupid);
+
+
+--
+-- Name: payments payments_pkey1; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.payments
+ ADD CONSTRAINT payments_pkey1 PRIMARY KEY (id);
+
+
+--
+-- Name: peers peers_node_id_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.peers
+ ADD CONSTRAINT peers_node_id_key UNIQUE (node_id);
+
+
+--
+-- Name: peers peers_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.peers
+ ADD CONSTRAINT peers_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: penalty_bases penalty_bases_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.penalty_bases
+ ADD CONSTRAINT penalty_bases_pkey PRIMARY KEY (channel_id, commitnum);
+
+
+--
+-- Name: runes runes_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.runes
+ ADD CONSTRAINT runes_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: shachain_known shachain_known_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.shachain_known
+ ADD CONSTRAINT shachain_known_pkey PRIMARY KEY (shachain_id, pos);
+
+
+--
+-- Name: shachains shachains_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.shachains
+ ADD CONSTRAINT shachains_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: transaction_annotations transaction_annotations_txid_idx_key; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.transaction_annotations
+ ADD CONSTRAINT transaction_annotations_txid_idx_key UNIQUE (txid, idx);
+
+
+--
+-- Name: transactions transactions_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.transactions
+ ADD CONSTRAINT transactions_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: utxoset utxoset_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.utxoset
+ ADD CONSTRAINT utxoset_pkey PRIMARY KEY (txid, outnum);
+
+
+--
+-- Name: vars vars_pkey; Type: CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.vars
+ ADD CONSTRAINT vars_pkey PRIMARY KEY (name);
+
+
+--
+-- Name: chain_moves_utxo_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX chain_moves_utxo_idx ON public.chain_moves USING btree (utxo);
+
+
+--
+-- Name: channel_htlcs_speedup_unresolved_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX channel_htlcs_speedup_unresolved_idx ON public.channel_htlcs USING btree (channel_id, direction) WHERE (hstate <> ALL (ARRAY[9, 19]));
+
+
+--
+-- Name: channel_htlcs_updated_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX channel_htlcs_updated_idx ON public.channel_htlcs USING btree (updated_index);
+
+
+--
+-- Name: channel_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX channel_idx ON public.htlc_sigs USING btree (channelid);
+
+
+--
+-- Name: channel_state_changes_channel_id; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX channel_state_changes_channel_id ON public.channel_state_changes USING btree (channel_id);
+
+
+--
+-- Name: forwards_created_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX forwards_created_idx ON public.forwards USING btree (rowid);
+
+
+--
+-- Name: forwards_updated_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX forwards_updated_idx ON public.forwards USING btree (updated_index);
+
+
+--
+-- Name: invoice_update_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX invoice_update_idx ON public.invoices USING btree (updated_index);
+
+
+--
+-- Name: invoices_pay_index; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE UNIQUE INDEX invoices_pay_index ON public.invoices USING btree (pay_index);
+
+
+--
+-- Name: local_anchors_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX local_anchors_idx ON public.local_anchors USING btree (channel_id);
+
+
+--
+-- Name: output_height_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX output_height_idx ON public.outputs USING btree (confirmation_height, spend_height);
+
+
+--
+-- Name: payments_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX payments_idx ON public.payments USING btree (payment_hash);
+
+
+--
+-- Name: payments_update_idx; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX payments_update_idx ON public.payments USING btree (updated_index);
+
+
+--
+-- Name: short_channel_id; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX short_channel_id ON public.utxoset USING btree (blockheight, txindex, outnum);
+
+
+--
+-- Name: utxoset_spend; Type: INDEX; Schema: public; Owner: lightning
+--
+
+CREATE INDEX utxoset_spend ON public.utxoset USING btree (spendheight);
+
+
+--
+-- Name: chain_moves chain_moves_account_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.chain_moves
+ ADD CONSTRAINT chain_moves_account_channel_id_fkey FOREIGN KEY (account_channel_id) REFERENCES public.channels(id);
+
+
+--
+-- Name: chain_moves chain_moves_account_nonchannel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.chain_moves
+ ADD CONSTRAINT chain_moves_account_nonchannel_id_fkey FOREIGN KEY (account_nonchannel_id) REFERENCES public.move_accounts(id);
+
+
+--
+-- Name: chain_moves chain_moves_originating_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.chain_moves
+ ADD CONSTRAINT chain_moves_originating_channel_id_fkey FOREIGN KEY (originating_channel_id) REFERENCES public.channels(id);
+
+
+--
+-- Name: chain_moves chain_moves_originating_nonchannel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.chain_moves
+ ADD CONSTRAINT chain_moves_originating_nonchannel_id_fkey FOREIGN KEY (originating_nonchannel_id) REFERENCES public.move_accounts(id);
+
+
+--
+-- Name: channel_blockheights channel_blockheights_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_blockheights
+ ADD CONSTRAINT channel_blockheights_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channel_feerates channel_feerates_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_feerates
+ ADD CONSTRAINT channel_feerates_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channel_funding_inflights channel_funding_inflights_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_funding_inflights
+ ADD CONSTRAINT channel_funding_inflights_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channel_htlcs channel_htlcs_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_htlcs
+ ADD CONSTRAINT channel_htlcs_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channel_moves channel_moves_account_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_moves
+ ADD CONSTRAINT channel_moves_account_channel_id_fkey FOREIGN KEY (account_channel_id) REFERENCES public.channels(id);
+
+
+--
+-- Name: channel_moves channel_moves_account_nonchannel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_moves
+ ADD CONSTRAINT channel_moves_account_nonchannel_id_fkey FOREIGN KEY (account_nonchannel_id) REFERENCES public.move_accounts(id);
+
+
+--
+-- Name: channel_state_changes channel_state_changes_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channel_state_changes
+ ADD CONSTRAINT channel_state_changes_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channels channels_peer_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channels
+ ADD CONSTRAINT channels_peer_id_fkey FOREIGN KEY (peer_id) REFERENCES public.peers(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channeltxs channeltxs_blockheight_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channeltxs
+ ADD CONSTRAINT channeltxs_blockheight_fkey FOREIGN KEY (blockheight) REFERENCES public.blocks(height) ON DELETE CASCADE;
+
+
+--
+-- Name: channeltxs channeltxs_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channeltxs
+ ADD CONSTRAINT channeltxs_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: channeltxs channeltxs_transaction_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.channeltxs
+ ADD CONSTRAINT channeltxs_transaction_id_fkey FOREIGN KEY (transaction_id) REFERENCES public.transactions(id) ON DELETE CASCADE;
+
+
+--
+-- Name: htlc_sigs htlc_sigs_channelid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.htlc_sigs
+ ADD CONSTRAINT htlc_sigs_channelid_fkey FOREIGN KEY (channelid) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: invoice_fallbacks invoice_fallbacks_invoice_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoice_fallbacks
+ ADD CONSTRAINT invoice_fallbacks_invoice_id_fkey FOREIGN KEY (invoice_id) REFERENCES public.invoices(id) ON DELETE CASCADE;
+
+
+--
+-- Name: invoices invoices_local_offer_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.invoices
+ ADD CONSTRAINT invoices_local_offer_id_fkey FOREIGN KEY (local_offer_id) REFERENCES public.offers(offer_id);
+
+
+--
+-- Name: local_anchors local_anchors_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.local_anchors
+ ADD CONSTRAINT local_anchors_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id);
+
+
+--
+-- Name: outputs outputs_confirmation_height_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.outputs
+ ADD CONSTRAINT outputs_confirmation_height_fkey FOREIGN KEY (confirmation_height) REFERENCES public.blocks(height) ON DELETE SET NULL;
+
+
+--
+-- Name: outputs outputs_spend_height_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.outputs
+ ADD CONSTRAINT outputs_spend_height_fkey FOREIGN KEY (spend_height) REFERENCES public.blocks(height) ON DELETE SET NULL;
+
+
+--
+-- Name: payments payments_local_invreq_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.payments
+ ADD CONSTRAINT payments_local_invreq_id_fkey FOREIGN KEY (local_invreq_id) REFERENCES public.invoicerequests(invreq_id);
+
+
+--
+-- Name: payments payments_local_offer_id_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.payments
+ ADD CONSTRAINT payments_local_offer_id_fkey1 FOREIGN KEY (local_offer_id) REFERENCES public.offers(offer_id);
+
+
+--
+-- Name: penalty_bases penalty_bases_channel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.penalty_bases
+ ADD CONSTRAINT penalty_bases_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES public.channels(id) ON DELETE CASCADE;
+
+
+--
+-- Name: shachain_known shachain_known_shachain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.shachain_known
+ ADD CONSTRAINT shachain_known_shachain_id_fkey FOREIGN KEY (shachain_id) REFERENCES public.shachains(id) ON DELETE CASCADE;
+
+
+--
+-- Name: transaction_annotations transaction_annotations_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.transaction_annotations
+ ADD CONSTRAINT transaction_annotations_channel_fkey FOREIGN KEY (channel) REFERENCES public.channels(id);
+
+
+--
+-- Name: transactions transactions_blockheight_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.transactions
+ ADD CONSTRAINT transactions_blockheight_fkey FOREIGN KEY (blockheight) REFERENCES public.blocks(height) ON DELETE SET NULL;
+
+
+--
+-- Name: utxoset utxoset_blockheight_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.utxoset
+ ADD CONSTRAINT utxoset_blockheight_fkey FOREIGN KEY (blockheight) REFERENCES public.blocks(height) ON DELETE CASCADE;
+
+
+--
+-- Name: utxoset utxoset_spendheight_fkey; Type: FK CONSTRAINT; Schema: public; Owner: lightning
+--
+
+ALTER TABLE ONLY public.utxoset
+ ADD CONSTRAINT utxoset_spendheight_fkey FOREIGN KEY (spendheight) REFERENCES public.blocks(height) ON DELETE SET NULL;
+
+
+--
+-- PostgreSQL database dump complete
+--
+
+\unrestrict 3gmSHpmWoAvp3M9hYnbn0eAJg2qkrJcZzqafmBmvLGVxDFS2cVhidb6yKDBdS99
+
diff --git a/scripts/close-dead-channels.sh b/scripts/close-dead-channels.sh
new file mode 100755
index 00000000..dda1efb9
--- /dev/null
+++ b/scripts/close-dead-channels.sh
@@ -0,0 +1,276 @@
+#!/bin/bash
+#
+# Close the least-active Lightning channels that have recoverable local balance.
+#
+# Usage:
+# ./close-dead-channels.sh # dry-run (default)
+# ./close-dead-channels.sh --execute # actually close channels
+#
+# Options (set via env vars):
+# FUNDS_DIR - path to balance snapshots (default: ~/coinos-server/data/funds)
+# COUNT - number of channels to close (default: 10)
+# MIN_LOCAL_PCT - minimum local balance % to consider (default: 20)
+# MONTHS - how many months of history to analyze (default: 3)
+# CLOSE_TIMEOUT - seconds to wait for cooperative close (default: 1)
+# EXCLUDE - comma-separated SCIDs or alias substrings to skip
+
+set -euo pipefail
+
+FUNDS_DIR="${FUNDS_DIR:-$HOME/coinos-server/data/funds}"
+COUNT="${COUNT:-10}"
+MIN_LOCAL_PCT="${MIN_LOCAL_PCT:-20}"
+MONTHS="${MONTHS:-3}"
+CLOSE_TIMEOUT="${CLOSE_TIMEOUT:-1}"
+EXCLUDE="${EXCLUDE:-}"
+EXECUTE=false
+
+if [[ "${1:-}" == "--execute" ]]; then
+ EXECUTE=true
+fi
+
+python3 - "$FUNDS_DIR" "$COUNT" "$MIN_LOCAL_PCT" "$MONTHS" "$EXCLUDE" << 'PYEOF'
+import json, subprocess, os, re, sys
+from datetime import datetime, timedelta
+from collections import defaultdict
+
+FUNDS_DIR = sys.argv[1]
+COUNT = int(sys.argv[2])
+MIN_LOCAL_PCT = float(sys.argv[3])
+MONTHS = int(sys.argv[4])
+EXCLUDE = [x.strip().lower() for x in sys.argv[5].split(",") if x.strip()]
+
+def parse_date(fn):
+ fn = fn.replace('balances_', '')
+ m = re.match(r'(\d{2})-(\d{2})-(\d{2})-(\d{2}):(\d{2}):(\d{2})\.json', fn)
+ if not m:
+ return None
+ month, day, year, hour, minute, sec = [int(x) for x in m.groups()]
+ try:
+ return datetime(year + 2000, month, day, hour, minute, sec)
+ except:
+ return None
+
+def fmt(s):
+ if abs(s) >= 1_000_000:
+ return f"{s/1_000_000:.2f}M"
+ elif abs(s) >= 1_000:
+ return f"{s/1_000:.1f}k"
+ return str(s)
+
+# Sample balance files every ~4 hours over the lookback period
+files = sorted(os.listdir(FUNDS_DIR))
+cutoff = datetime.now() - timedelta(days=MONTHS * 30)
+recent = [(d, f) for f in files if (d := parse_date(f)) and d >= cutoff]
+recent.sort()
+
+sampled = []
+last = None
+for d, f in recent:
+ if last is None or (d - last).total_seconds() >= 4 * 3600:
+ sampled.append((d, f))
+ last = d
+
+print(f"Analyzed {len(sampled)} snapshots over {MONTHS} months\n")
+
+# Build movement history per channel
+channel_history = defaultdict(list)
+for d, f in sampled:
+ try:
+ with open(os.path.join(FUNDS_DIR, f)) as fh:
+ data = json.load(fh)
+ except:
+ continue
+ for ch in data.get("channels", []):
+ scid = ch.get("short_channel_id", "")
+ if scid:
+ channel_history[scid].append(
+ (d, ch["our_amount_msat"], ch["amount_msat"], ch.get("state", ""))
+ )
+
+# Get currently open channels
+result = subprocess.run(
+ ["docker", "exec", "cl", "lightning-cli", "listpeerchannels"],
+ capture_output=True, text=True, timeout=15,
+)
+peerchans = json.loads(result.stdout)
+
+# Resolve aliases
+aliases = {}
+def get_alias(peer_id):
+ if peer_id in aliases:
+ return aliases[peer_id]
+ try:
+ r = subprocess.run(
+ ["docker", "exec", "cl", "lightning-cli", "listnodes", peer_id],
+ capture_output=True, text=True, timeout=10,
+ )
+ nd = json.loads(r.stdout)
+ a = nd["nodes"][0].get("alias", peer_id[:20] + "...") if nd.get("nodes") else peer_id[:20] + "..."
+ except:
+ a = peer_id[:20] + "..."
+ aliases[peer_id] = a
+ return a
+
+# Score each open channel
+candidates = []
+for ch in peerchans.get("channels", []):
+ scid = ch.get("short_channel_id", "")
+ state = ch.get("state", "")
+ if not scid or state != "CHANNELD_NORMAL":
+ continue
+
+ capacity = ch.get("total_msat", 0) // 1000
+ local = ch.get("to_us_msat", 0) // 1000
+ if capacity == 0:
+ continue
+ local_pct = 100 * local / capacity
+ if local_pct < MIN_LOCAL_PCT:
+ continue
+
+ history = channel_history.get(scid, [])
+ normal = [(d, o, t) for d, o, t, s in history if s == "CHANNELD_NORMAL"]
+ movement = 0
+ if len(normal) >= 2:
+ for i in range(1, len(normal)):
+ movement += abs(normal[i][1] - normal[i - 1][1])
+
+ candidates.append({
+ "scid": scid,
+ "peer_id": ch.get("peer_id", ""),
+ "capacity": capacity,
+ "local": local,
+ "remote": capacity - local,
+ "local_pct": local_pct,
+ "movement": movement // 1000,
+ })
+
+candidates.sort(key=lambda x: x["movement"])
+
+# Apply exclusions and take bottom N
+selected = []
+for c in candidates:
+ if len(selected) >= COUNT:
+ break
+ alias = get_alias(c["peer_id"])
+ skip = False
+ for exc in EXCLUDE:
+ if exc in c["scid"].lower() or exc in alias.lower():
+ skip = True
+ break
+ if skip:
+ print(f" SKIP (excluded): {c['scid']} {alias}")
+ continue
+ c["alias"] = alias
+ selected.append(c)
+
+# Print results
+print(f"{'SCID':<18} {'Peer':<30} {'Capacity':>10} {'Local':>10} {'Remote':>10} {'Local%':>7} {'3mo Vol':>10}")
+print("-" * 105)
+for c in selected:
+ print(
+ f"{c['scid']:<18} {c['alias']:<30} {fmt(c['capacity']):>10} "
+ f"{fmt(c['local']):>10} {fmt(c['remote']):>10} {c['local_pct']:>6.1f}% "
+ f"{fmt(c['movement']):>10}"
+ )
+
+total_local = sum(c["local"] for c in selected)
+total_cap = sum(c["capacity"] for c in selected)
+print(f"\nRecoverable local: {fmt(total_local)} sats | Capacity freed: {fmt(total_cap)} sats")
+
+# Output SCIDs for the shell to close
+scid_list = " ".join(c["scid"] for c in selected)
+print(f"\nSCIDS={scid_list}")
+PYEOF
+
+# Parse SCIDs from python output
+SCIDS=$(python3 - "$FUNDS_DIR" "$COUNT" "$MIN_LOCAL_PCT" "$MONTHS" "$EXCLUDE" << 'PYEOF' 2>/dev/null | tail -1
+import json, subprocess, os, re, sys
+from datetime import datetime, timedelta
+from collections import defaultdict
+
+FUNDS_DIR = sys.argv[1]
+COUNT = int(sys.argv[2])
+MIN_LOCAL_PCT = float(sys.argv[3])
+MONTHS = int(sys.argv[4])
+EXCLUDE = [x.strip().lower() for x in sys.argv[5].split(",") if x.strip()]
+
+def parse_date(fn):
+ fn = fn.replace('balances_', '')
+ m = re.match(r'(\d{2})-(\d{2})-(\d{2})-(\d{2}):(\d{2}):(\d{2})\.json', fn)
+ if not m: return None
+ mo, d, y, h, mi, s = [int(x) for x in m.groups()]
+ try: return datetime(y+2000, mo, d, h, mi, s)
+ except: return None
+
+files = sorted(os.listdir(FUNDS_DIR))
+cutoff = datetime.now() - timedelta(days=MONTHS*30)
+recent = [(d, f) for f in files if (d := parse_date(f)) and d >= cutoff]
+recent.sort()
+sampled, last = [], None
+for d, f in recent:
+ if last is None or (d - last).total_seconds() >= 4*3600:
+ sampled.append((d, f)); last = d
+
+ch_hist = defaultdict(list)
+for d, f in sampled:
+ try:
+ with open(os.path.join(FUNDS_DIR, f)) as fh: data = json.load(fh)
+ except: continue
+ for ch in data.get("channels", []):
+ scid = ch.get("short_channel_id", "")
+ if scid: ch_hist[scid].append((d, ch["our_amount_msat"], ch["amount_msat"], ch.get("state","")))
+
+r = subprocess.run(["docker","exec","cl","lightning-cli","listpeerchannels"], capture_output=True, text=True, timeout=15)
+peerchans = json.loads(r.stdout)
+
+aliases = {}
+def get_alias(pid):
+ if pid in aliases: return aliases[pid]
+ try:
+ r2 = subprocess.run(["docker","exec","cl","lightning-cli","listnodes",pid], capture_output=True, text=True, timeout=10)
+ nd = json.loads(r2.stdout)
+ a = nd["nodes"][0].get("alias", pid[:20]) if nd.get("nodes") else pid[:20]
+ except: a = pid[:20]
+ aliases[pid] = a; return a
+
+cands = []
+for ch in peerchans.get("channels", []):
+ scid = ch.get("short_channel_id",""); state = ch.get("state","")
+ if not scid or state != "CHANNELD_NORMAL": continue
+ cap = ch.get("total_msat",0)//1000; loc = ch.get("to_us_msat",0)//1000
+ if cap == 0: continue
+ pct = 100*loc/cap
+ if pct < MIN_LOCAL_PCT: continue
+ hist = ch_hist.get(scid, [])
+ norm = [(d,o,t) for d,o,t,s in hist if s=="CHANNELD_NORMAL"]
+ mv = sum(abs(norm[i][1]-norm[i-1][1]) for i in range(1,len(norm)))//1000 if len(norm)>=2 else 0
+ cands.append({"scid":scid,"peer_id":ch.get("peer_id",""),"movement":mv})
+
+cands.sort(key=lambda x: x["movement"])
+sel = []
+for c in cands:
+ if len(sel) >= COUNT: break
+ alias = get_alias(c["peer_id"])
+ skip = any(e in c["scid"].lower() or e in alias.lower() for e in EXCLUDE)
+ if not skip: sel.append(c["scid"])
+
+print(" ".join(sel))
+PYEOF
+)
+
+if [[ "$EXECUTE" != "true" ]]; then
+ echo ""
+ echo "DRY RUN - to actually close, run:"
+ echo " $0 --execute"
+ exit 0
+fi
+
+echo ""
+echo "Closing channels..."
+for scid in $SCIDS; do
+ echo " Closing $scid ..."
+ docker exec cl lightning-cli close "$scid" "$CLOSE_TIMEOUT" 2>&1 | head -5
+ echo ""
+done
+
+echo "Done."
diff --git a/scripts/dedup-invoices.ts b/scripts/dedup-invoices.ts
new file mode 100644
index 00000000..9232566b
--- /dev/null
+++ b/scripts/dedup-invoices.ts
@@ -0,0 +1,86 @@
+#!/usr/bin/env bun
+// One-time cleanup: remove duplicate entries from every `<id>:invoices` list.
+//
+// Background: `generate()` used to `lPush` the invoice id onto the owner's
+// `:invoices` list every time it ran — including when regenerating an existing
+// invoice (adding a tip, webhook, etc.). That pushed the same id multiple times,
+// so a user's `/invoices` response contained duplicate records. The UI's keyed
+// `{#each}` then threw `each_key_duplicate` and rendered a blank page. The source
+// is fixed in lib/invoices.ts (only push when the id is brand new); this script
+// cleans the dupes that already accumulated.
+//
+// Dedup preserves the ORIGINAL position of each id. The list is newest-first
+// (lPush adds to the head), and re-pushes added newer copies at the head, so the
+// deepest (tail-most) occurrence is the original creation push — we keep that one.
+//
+// Read-only by default (dry run). Pass --apply to actually rewrite lists.
+// Usage: bun scripts/dedup-invoices.ts [--apply]
+
+import { createClient } from "redis";
+
+const apply = process.argv.includes("--apply");
+
+const db = createClient({
+ url: "redis://127.0.0.1:6379",
+ socket: { reconnectStrategy: () => false },
+});
+await db.connect();
+
+// Keep the LAST occurrence of each id (original creation order), preserving
+// the relative order of the kept entries.
+function dedupeKeepLast(list: string[]): string[] {
+ const seen = new Set<string>();
+ const out: string[] = [];
+ for (let i = list.length - 1; i >= 0; i--) {
+ const id = list[i];
+ if (seen.has(id)) continue;
+ seen.add(id);
+ out.push(id);
+ }
+ out.reverse();
+ return out;
+}
+
+let lists = 0;
+let listsWithDupes = 0;
+let entriesBefore = 0;
+let entriesRemoved = 0;
+const worst: { key: string; before: number; after: number }[] = [];
+
+for await (const k of db.scanIterator({ MATCH: "*:invoices", COUNT: 2000 })) {
+ const keys = Array.isArray(k) ? k : [k];
+ for (const key of keys) {
+ lists++;
+ const list = await db.lRange(key, 0, -1);
+ entriesBefore += list.length;
+ const deduped = dedupeKeepLast(list);
+ const removed = list.length - deduped.length;
+ if (removed === 0) continue;
+
+ listsWithDupes++;
+ entriesRemoved += removed;
+ worst.push({ key, before: list.length, after: deduped.length });
+
+ if (apply) {
+ // Rewrite atomically: DEL then RPUSH the deduped list (RPUSH keeps head->tail order).
+ const tx = db.multi();
+ tx.del(key);
+ tx.rPush(key, deduped);
+ await tx.exec();
+ }
+ }
+}
+
+worst.sort((a, b) => b.before - b.after - (a.before - a.after));
+
+console.log(`\n${apply ? "APPLIED" : "DRY RUN (use --apply to write)"}`);
+console.log(`lists scanned: ${lists}`);
+console.log(`lists with dupes: ${listsWithDupes}`);
+console.log(`entries before: ${entriesBefore}`);
+console.log(`duplicate entries: ${entriesRemoved}`);
+console.log(`\ntop 10 most-duplicated lists:`);
+for (const w of worst.slice(0, 10)) {
+ console.log(` ${w.key} ${w.before} -> ${w.after} (-${w.before - w.after})`);
+}
+
+await db.quit();
diff --git a/scripts/kvrocks-merge-lowmem.conf b/scripts/kvrocks-merge-lowmem.conf
new file mode 100644
index 00000000..bdfa641a
--- /dev/null
+++ b/scripts/kvrocks-merge-lowmem.conf
@@ -0,0 +1,19 @@
+bind 0.0.0.0
+port 6379
+workers 2
+timeout 0
+tcp-backlog 512
+daemonize no
+dir /data
+db-name db
+log-dir stdout
+redis-cursor-compatible no
+max-io-mb 100
+rocksdb.compression snappy
+rocksdb.block_cache_size 128
+rocksdb.metadata_block_cache_size 128
+rocksdb.subkey_block_cache_size 128
+rocksdb.write_buffer_size 32
+rocksdb.max_background_compactions 1
+rocksdb.max_background_flushes 1
+rocksdb.max_background_jobs 2
diff --git a/scripts/ln-liquid-arb.ts b/scripts/ln-liquid-arb.ts
new file mode 100644
index 00000000..02d0aa1e
--- /dev/null
+++ b/scripts/ln-liquid-arb.ts
@@ -0,0 +1,126 @@
+#!/usr/bin/env bun
+// Find users exhibiting the "lightning in, liquid out" pattern that drains the
+// liquid hot wallet (forcing the liquid threshold down). For each user in the
+// window, sum receives/sends per rail and surface those who take in meaningful
+// lightning and push out meaningful liquid.
+//
+// Usage: bun scripts/ln-liquid-arb.ts [--days N] [--min SATS] [--top N]
+
+import { createClient } from "redis";
+
+const argDays = Number(process.argv[process.argv.indexOf("--days") + 1]) || 14;
+const MIN = Number(process.argv[process.argv.indexOf("--min") + 1]) || 500_000;
+const TOP = Number(process.argv[process.argv.indexOf("--top") + 1]) || 30;
+const cutoff = Date.now() - argDays * 24 * 60 * 60 * 1000;
+
+const db = createClient({ url: "redis://127.0.0.1:6379", socket: { reconnectStrategy: () => false } });
+await db.connect();
+
+const fmt = (n: number) => n.toLocaleString("en-US");
+
+// uid -> username
+const uname = new Map<string, string>();
+async function loadNames() {
+ let batch: string[] = [];
+ const drain = async () => {
+ if (!batch.length) return;
+ const vals = await db.mGet(batch);
+ for (const v of vals) {
+ if (!v || v[0] !== "{") continue;
+ try { const u = JSON.parse(v); if (u.id && u.username) uname.set(u.id, u.username); } catch {}
+ }
+ batch = [];
+ };
+ for await (const k of db.scanIterator({ MATCH: "user:*", COUNT: 1000 })) {
+ batch.push(k as string);
+ if (batch.length >= 500) await drain();
+ }
+ await drain();
+}
+await loadNames();
+const nameOf = (uid: string) => uname.get(uid) || uid.slice(0, 8);
+
+// Per user per rail: { in, out } sats
+type Rail = { in: number; out: number; inN: number; outN: number };
+const RAILS = ["lightning", "liquid", "bitcoin", "internal", "bolt12", "fund"];
+const stats = new Map<string, Record<string, Rail>>();
+const ensure = (uid: string) => {
+ let s = stats.get(uid);
+ if (!s) {
+ s = {};
+ for (const r of RAILS) s[r] = { in: 0, out: 0, inN: 0, outN: 0 };
+ stats.set(uid, s);
+ }
+ return s;
+};
+
+let scanned = 0;
+let batch: string[] = [];
+const drainP = async () => {
+ if (!batch.length) return;
+ const vals = await db.mGet(batch);
+ for (const v of vals) {
+ if (!v || v[0] !== "{") continue;
+ let p: any; try { p = JSON.parse(v); } catch { continue; }
+ if (!p.created || p.created < cutoff || typeof p.amount !== "number" || !p.uid) continue;
+ const rail = p.type;
+ if (!RAILS.includes(rail)) continue;
+ const s = ensure(p.uid)[rail];
+ if (p.amount >= 0) { s.in += p.amount; s.inN++; }
+ else { s.out += Math.abs(p.amount); s.outN++; }
+ }
+ batch = [];
+};
+for await (const k of db.scanIterator({ MATCH: "payment:*", COUNT: 2000 })) {
+ scanned++;
+ batch.push(k as string);
+ if (batch.length >= 1000) await drainP();
+}
+await drainP();
+
+// The arb pattern: significant lightning IN and significant liquid OUT.
+const flagged = [...stats.entries()]
+ .map(([uid, s]) => ({
+ uid,
+ lnIn: s.lightning.in,
+ lqOut: s.liquid.out,
+ bolt12In: s.bolt12.in,
+ arb: Math.min(s.lightning.in + s.bolt12.in, s.liquid.out), // overlap = the through-flow
+ s,
+ }))
+ .filter((x) => x.lqOut >= MIN && x.lnIn + x.bolt12In >= MIN)
+ .sort((a, b) => b.arb - a.arb)
+ .slice(0, TOP);
+
+console.log("═".repeat(72));
+console.log(` LIGHTNING-IN / LIQUID-OUT pattern — last ${argDays} days (min ${fmt(MIN)} sats each side)`);
+console.log("═".repeat(72));
+console.log(` walked ${fmt(scanned)} payment keys\n`);
+
+if (!flagged.length) {
+ console.log(" No users matched the pattern at this threshold.");
+} else {
+ console.log(" user ln_in bolt12_in liquid_out through-flow");
+ for (const x of flagged) {
+ console.log(
+ ` ${nameOf(x.uid).padEnd(20)} ${fmt(x.lnIn).padStart(11)} ${fmt(x.bolt12In).padStart(11)} ${fmt(x.lqOut).padStart(13)} ${fmt(x.arb).padStart(13)}`,
+ );
+ }
+ const totalThrough = flagged.reduce((s, x) => s + x.arb, 0);
+ console.log(`\n Combined through-flow (ln/bolt12 in -> liquid out): ${fmt(totalThrough)} sats`);
+}
+
+// Also: who are the biggest NET liquid drainers regardless of funding source?
+const netLiquid = [...stats.entries()]
+ .map(([uid, s]) => ({ uid, net: s.liquid.out - s.liquid.in, out: s.liquid.out }))
+ .filter((x) => x.net >= MIN)
+ .sort((a, b) => b.net - a.net)
+ .slice(0, 15);
+console.log("\n" + "─".repeat(72));
+console.log(" TOP NET LIQUID DRAINERS (liquid_out - liquid_in, any funding source)");
+console.log("─".repeat(72));
+for (const x of netLiquid) {
+ console.log(` ${nameOf(x.uid).padEnd(20)} net out ${fmt(x.net).padStart(13)} (gross out ${fmt(x.out)})`);
+}
+console.log("═".repeat(72));
+process.exit(0);
diff --git a/scripts/merge-kvrocks-archives.ts b/scripts/merge-kvrocks-archives.ts
new file mode 100644
index 00000000..54ea178d
--- /dev/null
+++ b/scripts/merge-kvrocks-archives.ts
@@ -0,0 +1,321 @@
+#!/usr/bin/env bun
+
+// Overlay one Redis/Kvrocks archive onto a base archive without materializing
+// keys in memory. The source wins on collisions. SCAN is intentionally sent as
+// a raw command because Kvrocks cursors can exceed JavaScript's safe integer
+// range and node-redis' high-level scan helper rounds them.
+
+import { createClient, type RedisClientType } from "redis";
+
+const valueOption = (name: string) =>
+ process.argv
+ .find((arg) => arg.startsWith(`--${name}=`))
+ ?.slice(name.length + 3);
+const hasFlag = (name: string) => process.argv.includes(`--${name}`);
+const positiveInteger = (name: string, fallback: number) => {
+ const parsed = Number(valueOption(name) || fallback);
+ if (!Number.isSafeInteger(parsed) || parsed <= 0) {
+ throw new Error(`--${name} must be a positive integer`);
+ }
+ return parsed;
+};
+
+const sourceUrl = valueOption("source-url");
+const baseUrl = valueOption("base-url");
+const targetUrl = valueOption("target-url");
+const verifyUrl = valueOption("verify-url");
+const apply = hasFlag("apply");
+const skipEqual = hasFlag("skip-equal");
+const countTypes = hasFlag("count-types");
+const batchSize = positiveInteger("batch", 512);
+const concurrency = positiveInteger("concurrency", 32);
+const progressEvery = positiveInteger("progress", 100_000);
+
+if (!sourceUrl || (apply && !targetUrl)) {
+ console.error(
+ "usage: bun scripts/merge-kvrocks-archives.ts " +
+ "--source-url=redis://127.0.0.1:6402 " +
+ "[--base-url=redis://127.0.0.1:6401] " +
+ "[--target-url=redis://127.0.0.1:6403 --apply] " +
+ "[--batch=512] [--concurrency=32]",
+ );
+ process.exit(2);
+}
+if (apply && (sourceUrl === targetUrl || baseUrl === targetUrl)) {
+ throw new Error("The target must be distinct from the source and base");
+}
+
+const makeClient = (url: string) =>
+ createClient({
+ url,
+ socket: { reconnectStrategy: () => false },
+ });
+const source = makeClient(sourceUrl);
+const base = baseUrl ? makeClient(baseUrl) : null;
+const target = targetUrl ? makeClient(targetUrl) : null;
+const verifier = verifyUrl ? makeClient(verifyUrl) : null;
+const clients = [source, base, target, verifier].filter(
+ Boolean,
+) as RedisClientType[];
+
+const concurrentMap = async <T>(
+ items: T[],
+ limit: number,
+ fn: (item: T) => Promise<void>,
+) => {
+ let next = 0;
+ const workers = Array.from(
+ { length: Math.min(limit, items.length) },
+ async () => {
+ while (next < items.length) {
+ const index = next++;
+ await fn(items[index]);
+ }
+ },
+ );
+ await Promise.all(workers);
+};
+
+const valuesMatch = async (
+ left: RedisClientType,
+ right: RedisClientType,
+ key: string,
+ type: string,
+) => {
+ if ((await right.type(key)) !== type) return false;
+ if (type === "string") {
+ const [leftValue, rightValue] = await Promise.all([
+ left.sendCommand<Buffer | null>(["GET", key], { returnBuffers: true }),
+ right.sendCommand<Buffer | null>(["GET", key], { returnBuffers: true }),
+ ]);
+ return (
+ leftValue !== null &&
+ rightValue !== null &&
+ Buffer.from(leftValue).equals(Buffer.from(rightValue))
+ );
+ }
+ if (type === "list") {
+ const [leftValues, rightValues] = await Promise.all([
+ left.sendCommand<Buffer[]>(["LRANGE", key, "0", "-1"], {
+ returnBuffers: true,
+ }),
+ right.sendCommand<Buffer[]>(["LRANGE", key, "0", "-1"], {
+ returnBuffers: true,
+ }),
+ ]);
+ return (
+ leftValues.length === rightValues.length &&
+ leftValues.every((value, index) =>
+ Buffer.from(value).equals(Buffer.from(rightValues[index])),
+ )
+ );
+ }
+ return false;
+};
+
+let scanned = 0;
+let overlaps = 0;
+let restored = 0;
+let skippedEqual = 0;
+let expiredDuringRead = 0;
+const typeCounts: Record<string, number> = {};
+let verified = 0;
+const verificationMismatches: string[] = [];
+let cursor = "0";
+let nextProgress = progressEvery;
+const startedAt = Date.now();
+
+try {
+ await Promise.all(clients.map((client) => client.connect()));
+
+ do {
+ const reply = await source.sendCommand<[string, string[]]>([
+ "SCAN",
+ cursor,
+ "COUNT",
+ String(batchSize),
+ ]);
+ cursor = String(reply[0]);
+ const keys = reply[1];
+ scanned += keys.length;
+
+ if (countTypes) {
+ await concurrentMap(keys, concurrency, async (key) => {
+ const type = await source.type(key);
+ typeCounts[type] = (typeCounts[type] || 0) + 1;
+ });
+ }
+
+ if (base) {
+ await concurrentMap(keys, concurrency, async (key) => {
+ if (await base.exists(key)) overlaps++;
+ });
+ }
+
+ if (verifier) {
+ await concurrentMap(keys, concurrency, async (key) => {
+ const [sourceType, verifyType] = await Promise.all([
+ source.type(key),
+ verifier.type(key),
+ ]);
+ if (sourceType !== verifyType) {
+ if (verificationMismatches.length < 20) {
+ verificationMismatches.push(
+ `${key}: type ${sourceType} != ${verifyType}`,
+ );
+ }
+ return;
+ }
+
+ if (sourceType === "string") {
+ const [sourceValue, verifyValue] = await Promise.all([
+ source.sendCommand<Buffer | null>(["GET", key], {
+ returnBuffers: true,
+ }),
+ verifier.sendCommand<Buffer | null>(["GET", key], {
+ returnBuffers: true,
+ }),
+ ]);
+ if (
+ sourceValue === null ||
+ verifyValue === null ||
+ !Buffer.from(sourceValue).equals(Buffer.from(verifyValue))
+ ) {
+ if (verificationMismatches.length < 20) {
+ verificationMismatches.push(`${key}: string value differs`);
+ }
+ return;
+ }
+ } else if (sourceType === "list") {
+ const [sourceValues, verifyValues] = await Promise.all([
+ source.sendCommand<Buffer[]>(["LRANGE", key, "0", "-1"], {
+ returnBuffers: true,
+ }),
+ verifier.sendCommand<Buffer[]>(["LRANGE", key, "0", "-1"], {
+ returnBuffers: true,
+ }),
+ ]);
+ if (
+ sourceValues.length !== verifyValues.length ||
+ sourceValues.some(
+ (value, index) =>
+ !Buffer.from(value).equals(Buffer.from(verifyValues[index])),
+ )
+ ) {
+ if (verificationMismatches.length < 20) {
+ verificationMismatches.push(`${key}: list value differs`);
+ }
+ return;
+ }
+ } else {
+ if (verificationMismatches.length < 20) {
+ verificationMismatches.push(
+ `${key}: verification unsupported for ${sourceType}`,
+ );
+ }
+ return;
+ }
+ verified++;
+ });
+ }
+
+ if (apply && target) {
+ await concurrentMap(keys, concurrency, async (key) => {
+ const [type, ttlRaw] = await Promise.all([
+ source.type(key),
+ source.sendCommand<string>(["PTTL", key]),
+ ]);
+ const ttl = Number(ttlRaw);
+ if (type === "none" || ttl === -2) {
+ expiredDuringRead++;
+ return;
+ }
+
+ if (skipEqual && (await valuesMatch(source, target, key, type))) {
+ const targetTtl = await target.pTTL(key);
+ const ttlMatches =
+ (ttl === -1 && targetTtl === -1) ||
+ (ttl > 0 && targetTtl > 0 && Math.abs(ttl - targetTtl) < 10_000);
+ if (ttlMatches) {
+ skippedEqual++;
+ return;
+ }
+ }
+
+ if (type === "string") {
+ const value = await source.sendCommand<Buffer | null>(["GET", key], {
+ returnBuffers: true,
+ });
+ if (value === null) {
+ expiredDuringRead++;
+ return;
+ }
+ await target.sendCommand(["SET", key, value]);
+ } else if (type === "list") {
+ await target.del(key);
+ let offset = 0;
+ while (true) {
+ const values = await source.sendCommand<Buffer[]>(
+ ["LRANGE", key, String(offset), String(offset + batchSize - 1)],
+ { returnBuffers: true },
+ );
+ if (!values.length) break;
+ await target.sendCommand(["RPUSH", key, ...values]);
+ offset += values.length;
+ if (values.length < batchSize) break;
+ }
+ } else {
+ throw new Error(`Unsupported source type ${type} for key ${key}`);
+ }
+
+ if (ttl > 0) await target.pExpire(key, ttl);
+ restored++;
+ });
+ }
+
+ if (scanned >= nextProgress) {
+ console.error(
+ JSON.stringify({
+ scanned,
+ overlaps,
+ restored,
+ skippedEqual,
+ cursor,
+ elapsedSeconds: Math.round((Date.now() - startedAt) / 1000),
+ }),
+ );
+ nextProgress += progressEvery;
+ }
+ } while (cursor !== "0");
+
+ console.log(
+ JSON.stringify(
+ {
+ mode: apply ? "apply" : "audit",
+ sourceUrl,
+ baseUrl: baseUrl || null,
+ targetUrl: targetUrl || null,
+ sourceKeysScanned: scanned,
+ overlappingBaseKeys: base ? overlaps : null,
+ sourceOnlyKeys: base ? scanned - overlaps : null,
+ restored: apply ? restored : null,
+ skippedEqual: apply ? skippedEqual : null,
+ expiredDuringRead,
+ typeCounts: countTypes ? typeCounts : null,
+ verifyUrl: verifyUrl || null,
+ verified: verifier ? verified : null,
+ verificationMismatches: verifier ? verificationMismatches : null,
+ elapsedSeconds: Math.round((Date.now() - startedAt) / 1000),
+ },
+ null,
+ 2,
+ ),
+ );
+ if (verificationMismatches.length) process.exitCode = 1;
+} finally {
+ await Promise.all(
+ clients.map(async (client) => {
+ if (client.isOpen) await client.quit();
+ }),
+ );
+}
diff --git a/scripts/migrate.py b/scripts/migrate.py
new file mode 100644
index 00000000..de7ff922
--- /dev/null
+++ b/scripts/migrate.py
@@ -0,0 +1,74 @@
+#!/usr/bin/env python3
+"""Migrate CLN SQLite database to Postgres.
+
+Reads from /tmp/lightningd.sqlite3, outputs SQL to stdout.
+Run inside the pgcl container:
+ python3 /tmp/migrate.py | psql -U lightning -d lightning
+"""
+import sqlite3
+import subprocess
+import sys
+
+sq = sqlite3.connect("/tmp/lightningd.sqlite3")
+sq.row_factory = sqlite3.Row
+
+# Get Postgres tables
+r = subprocess.run(
+ ["psql", "-U", "lightning", "-d", "lightning", "-tAc",
+ "SELECT tablename FROM pg_tables WHERE schemaname='public';"],
+ capture_output=True, text=True
+)
+pg_tables = set(r.stdout.strip().split("\n"))
+
+# Get SQLite tables
+sq_tables = [row[0] for row in sq.execute(
+ "SELECT name FROM sqlite_master WHERE type='table'").fetchall()]
+
+sql_parts = [
+ "BEGIN;",
+ "SET CONSTRAINTS ALL DEFERRED;",
+]
+total = 0
+
+# Insert tables with foreign key targets first
+priority = ["version", "blocks", "shachains", "peers", "channels",
+ "channel_configs", "offers", "invoicerequests", "move_accounts"]
+ordered = priority + [t for t in sq_tables if t not in priority]
+
+for tbl in ordered:
+ if tbl not in pg_tables:
+ print(f" SKIP {tbl}", file=sys.stderr)
+ continue
+
+ rows = sq.execute(f'SELECT * FROM [{tbl}]').fetchall()
+ if not rows:
+ print(f" {tbl}: 0 rows", file=sys.stderr)
+ continue
+
+ cols = rows[0].keys()
+ col_list = ", ".join(f'"{c}"' for c in cols)
+ count = 0
+
+ for row in rows:
+ values = []
+ for col in cols:
+ val = row[col]
+ if val is None:
+ values.append("NULL")
+ elif isinstance(val, bytes):
+ values.append(f"'\\x{val.hex()}'")
+ elif isinstance(val, (int, float)):
+ values.append(str(val))
+ else:
+ escaped = str(val).replace("'", "''")
+ values.append(f"'{escaped}'")
+ val_list = ", ".join(values)
+ sql_parts.append(f'INSERT INTO "{tbl}" ({col_list}) VALUES ({val_list});')
+ count += 1
+
+ total += count
+ print(f" {tbl}: {count} rows", file=sys.stderr)
+
+sql_parts.append("COMMIT;")
+print(f"\nTotal: {total} rows to migrate", file=sys.stderr)
+print("\n".join(sql_parts))
diff --git a/scripts/monitor-ln-socket.sh b/scripts/monitor-ln-socket.sh
new file mode 100755
index 00000000..66841f40
--- /dev/null
+++ b/scripts/monitor-ln-socket.sh
@@ -0,0 +1,90 @@
+#!/usr/bin/env bash
+set -euo pipefail
+
+SOCKET_DIR="/home/adam/coinos-server/data/sockets"
+SOCKET_MAIN="$SOCKET_DIR/lightning-rpc"
+SOCKET_LISTEN="$SOCKET_DIR/lightning-rpc-listen"
+REMOTE_SOCKET="/home/adam/coinos-server/data/lightning/bitcoin/lightning-rpc"
+REMOTE_HOST="desk"
+
+log() {
+ echo "$(date '+%Y-%m-%d %H:%M:%S') [ln-socket-monitor] $*"
+}
+
+# Find SSH pids that are forwarding our sockets
+pid_main=$(pgrep -f "ssh.*-L ${SOCKET_MAIN}:${REMOTE_SOCKET} ${REMOTE_HOST}" 2>/dev/null || true)
+pid_listen=$(pgrep -f "ssh.*-L ${SOCKET_LISTEN}:${REMOTE_SOCKET} ${REMOTE_HOST}" 2>/dev/null || true)
+
+# Check if both processes are alive and both sockets exist
+if [[ -n "$pid_main" && -n "$pid_listen" && -S "$SOCKET_MAIN" && -S "$SOCKET_LISTEN" ]]; then
+ exit 0
+fi
+
+# Something is wrong - log what we found
+if [[ -z "$pid_main" ]]; then
+ log "main SSH forward is dead"
+elif [[ ! -S "$SOCKET_MAIN" ]]; then
+ log "main socket file missing"
+fi
+
+if [[ -z "$pid_listen" ]]; then
+ log "listen SSH forward is dead"
+elif [[ ! -S "$SOCKET_LISTEN" ]]; then
+ log "listen socket file missing"
+fi
+
+# Kill any remaining SSH forwards for our sockets
+for pid in $pid_main $pid_listen; do
+ if [[ -n "$pid" ]]; then
+ log "killing stale SSH forward (pid $pid)"
+ kill "$pid" 2>/dev/null || true
+ fi
+done
+
+# Brief wait for processes to exit
+sleep 1
+
+# Clean up socket files
+for sock in "$SOCKET_MAIN" "$SOCKET_LISTEN"; do
+ if [[ -e "$sock" ]]; then
+ log "removing stale socket $sock"
+ rm -f "$sock"
+ fi
+done
+
+# Ensure socket directory exists
+mkdir -p "$SOCKET_DIR"
+
+# Re-establish both SSH forwards
+log "starting main SSH forward"
+ssh -nNT -o ExitOnForwardFailure=yes \
+ -L "${SOCKET_MAIN}:${REMOTE_SOCKET}" "$REMOTE_HOST" &
+
+log "starting listen SSH forward"
+ssh -nNT -o ExitOnForwardFailure=yes \
+ -L "${SOCKET_LISTEN}:${REMOTE_SOCKET}" "$REMOTE_HOST" &
+
+# Wait briefly for sockets to appear
+for i in 1 2 3 4 5; do
+ if [[ -S "$SOCKET_MAIN" && -S "$SOCKET_LISTEN" ]]; then
+ break
+ fi
+ sleep 1
+done
+
+# Set permissions on sockets
+if [[ -S "$SOCKET_MAIN" ]]; then
+ chmod 777 "$SOCKET_MAIN"
+ log "set permissions on $SOCKET_MAIN"
+else
+ log "WARNING: $SOCKET_MAIN did not appear after 5s"
+fi
+
+if [[ -S "$SOCKET_LISTEN" ]]; then
+ chmod 777 "$SOCKET_LISTEN"
+ log "set permissions on $SOCKET_LISTEN"
+else
+ log "WARNING: $SOCKET_LISTEN did not appear after 5s"
+fi
+
+log "SSH forwards re-established"
diff --git a/scripts/pay-hard.ts b/scripts/pay-hard.ts
new file mode 100644
index 00000000..e97b3a20
--- /dev/null
+++ b/scripts/pay-hard.ts
@@ -0,0 +1,159 @@
+// pay-hard.ts — a "stubborn" Lightning payer for hard destinations.
+//
+// Motivation (fixedfloat swap, 2026-08-27): xpay/renepay are cost-first. To a
+// well-connected but inbound-scarce node (an exchange), the CHEAPEST paths are
+// exactly the depleted ones, so cost-optimizing payers keep failing on them —
+// and after many failures CLN's askrene "learned capacity" turns pessimistic
+// and steers them away from the ONE path that actually has room. The move that
+// works is liquidity DISCOVERY, orthogonal to cost: enumerate the destination's
+// peers, keep the ones WE can reach (bonus: ones we already channel), and probe
+// each `us -> peer -> dest` leg for inbound room at the real amount. Then force
+// the payment through the peer that has room.
+//
+// Strategy:
+// 1. Preflight (decode, not-expired, not already-paid, nothing pending).
+// 2. Try xpay normally (fast path — covers ~all payments).
+// 3. On failure to a hint-less destination: rank the destination's peers
+// (ones we channel with >= amount outbound first), and for each, build a
+// route FORCED through peer->dest (getroute excluding all other dest
+// channels), with the CORRECT final CLTV from the invoice, then PROBE it
+// with a random hash. The first path that reaches the destination gets the
+// real payment.
+//
+// Safety: probe (unclaimable random hash) before every real send; refuse to
+// start if a payment is already pending; if a real send doesn't resolve
+// cleanly (still pending), STOP rather than risk a second in-flight payment.
+//
+// Usage:
+// bun pay-hard.ts <bolt11> [--node=cl|bcln] [--maxfee=<sat>] [--dry]
+// [--skip-xpay] [--max-peers=N]
+// --node which node pays (cl = local main node; bcln = boltz node on cs). default cl
+// --maxfee fee ceiling in sat (default 5000)
+// --dry find a working path and stop (no real payment)
+// --skip-xpay go straight to the peer-force strategy
+// --max-peers how many candidate peers to probe (default 12)
+import { execSync } from "child_process";
+import { writeFileSync } from "fs";
+import { randomBytes } from "crypto";
+
+const argv = process.argv.slice(2);
+const inv = argv.find((a) => !a.startsWith("--"));
+const opt = (k: string, d: string) => { const a = argv.find((x) => x.startsWith(`--${k}=`)); return a ? a.split("=").slice(1).join("=") : d; };
+const flag = (k: string) => argv.includes(`--${k}`);
+const nodeName = opt("node", "cl");
+const maxfeeSat = Number(opt("maxfee", "5000"));
+const maxPeers = Number(opt("max-peers", "12"));
+const DRY = flag("dry");
+const skipXpay = flag("skip-xpay");
+
+if (!inv) {
+ console.error("usage: bun pay-hard.ts <bolt11> [--node=cl|bcln] [--maxfee=sat] [--dry] [--skip-xpay] [--max-peers=N]");
+ process.exit(1);
+}
+
+// Node adapters. cl runs locally; bcln runs in a container on the `cs` host.
+const NODES: Record<string, { remote: boolean; prefix: string }> = {
+ cl: { remote: false, prefix: "docker exec cl lightning-cli" },
+ bcln: { remote: true, prefix: "docker exec bcln lightning-cli --lightning-dir=/app/lightning" },
+};
+const node = NODES[nodeName];
+if (!node) { console.error("unknown node:", nodeName, "(use cl or bcln)"); process.exit(1); }
+
+const sh = (c: string) => { try { return execSync(c, { encoding: "utf8", maxBuffer: 1e8 }); } catch (e: any) { return (e.stdout || "") + (e.stderr || ""); } };
+// run a shell command ON the node's host (local, or via ssh to cs). Single-quote
+// for ssh so $(cat ...) is expanded remotely, not locally. Our route/exclude
+// JSON never contains single quotes, so this quoting is safe.
+const runOn = (cmd: string) => node.remote ? sh(`ssh -o ConnectTimeout=25 cs '${cmd}'`) : sh(cmd);
+const cli = (sub: string) => runOn(`${node.prefix} ${sub}`);
+const cliJSON = (sub: string): any => { const o = cli(sub); try { return JSON.parse(o); } catch { return { __raw: o }; } };
+const putFile = (path: string, content: string) => { writeFileSync(path, content); if (node.remote) sh(`scp -q ${path} cs:${path}`); };
+const log = (...a: any[]) => console.log(...a);
+const sat = (msat: number) => Math.round(msat / 1000);
+
+// waitsendpay that returns the parsed result whether it succeeded or failed.
+const waitFor = (hash: string, secs = 60): any => { const o = cli(`waitsendpay ${hash} ${secs}`); try { return JSON.parse(o); } catch { return { __raw: o }; } };
+
+// ---- 1. preflight ----
+const dec = cliJSON(`decode ${inv}`);
+if (!dec || dec.valid === false || !dec.payment_hash) { log("invalid/undecodable invoice:", JSON.stringify(dec).slice(0, 200)); process.exit(1); }
+const amount = Number(dec.amount_msat);
+const hash = dec.payment_hash;
+const secret = dec.payment_secret;
+const finalCltv = Number(dec.min_final_cltv_expiry || 18);
+const dest = dec.payee;
+const hints = (dec.routes || []).length;
+if (!amount) { log("amountless invoice not supported"); process.exit(1); }
+log(`invoice: ${sat(amount)} sat -> ${String(dest).slice(0, 16)}… | min_final_cltv ${finalCltv} | hints ${hints} | node ${nodeName}`);
+
+const lp = cliJSON(`listpays bolt11=${inv}`);
+const alreadyDone = (lp.pays || []).find((p: any) => p.status === "complete");
+if (alreadyDone) { log("✅ already paid; preimage:", alreadyDone.preimage); process.exit(0); }
+if ((lp.pays || []).some((p: any) => p.status === "pending")) { log("⚠️ a payment for this invoice is already PENDING — not starting another. Wait for it to resolve."); process.exit(1); }
+
+// ---- 2. xpay fast path ----
+if (!skipXpay) {
+ log(`→ trying xpay (maxfee ${maxfeeSat} sat)…`);
+ const x = cliJSON(`-k xpay invstring=${inv} maxfee=${maxfeeSat * 1000}`);
+ if (x && x.payment_preimage) { log(`✅ xpay succeeded! preimage: ${x.payment_preimage} | fee ${sat(Number(x.amount_sent_msat) - amount)} sat`); process.exit(0); }
+ log(" xpay did not complete:", String(x.__raw || x.message || JSON.stringify(x)).replace(/\s+/g, " ").slice(0, 160));
+}
+
+if (hints > 0) { log("invoice carries route hints — the peer-force strategy targets the public destination node and can't honor private hints. Rely on xpay/renepay here."); process.exit(1); }
+
+// ---- 3. peer-force liquidity discovery ----
+log("→ peer-force: probing the destination's reachable peers for inbound room…");
+const destChans = (cliJSON(`listchannels destination=${dest}`).channels) || [];
+if (!destChans.length) { log("destination advertises no public channels — cannot force-route."); process.exit(2); }
+const bySource: Record<string, { scids: string[]; cap: number }> = {};
+for (const c of destChans) { (bySource[c.source] ||= { scids: [], cap: 0 }); bySource[c.source].scids.push(c.short_channel_id); bySource[c.source].cap = Math.max(bySource[c.source].cap, Number(c.amount_msat || 0)); }
+const allScids: string[] = destChans.map((c: any) => c.short_channel_id);
+
+const ours: Record<string, number> = {};
+for (const c of ((cliJSON(`listpeerchannels`).channels) || [])) if (c.state === "CHANNELD_NORMAL") ours[c.peer_id] = Number(c.spendable_msat || 0);
+
+// rank: peers we already channel with enough outbound first, then by our
+// outbound to them, then by their channel capacity to the destination.
+const candidates = Object.entries(bySource)
+ .map(([id, info]) => ({ id, scids: info.scids, cap: info.cap, our: ours[id] || 0 }))
+ .sort((a, b) => (b.our >= amount ? 1 : 0) - (a.our >= amount ? 1 : 0) || b.our - a.our || b.cap - a.cap)
+ .slice(0, maxPeers);
+log(` ${candidates.length} candidates (★ = we already channel with room): ` + candidates.slice(0, 8).map((c) => c.id.slice(0, 8) + (c.our >= amount ? "★" : "")).join(" "));
+
+for (const P of candidates) {
+ // force the route to use P->dest as the final hop by excluding every other dest channel
+ const excl = allScids.filter((s) => !P.scids.includes(s)).flatMap((s) => [`${s}/0`, `${s}/1`]);
+ putFile("/tmp/ph_excl.json", JSON.stringify(excl));
+ const rr = cliJSON(`getroute ${dest} ${amount} 10 ${finalCltv + 5} null null "$(cat /tmp/ph_excl.json)"`);
+ const route = rr.route;
+ if (!route || !route.length) { continue; }
+ putFile("/tmp/ph_route.json", JSON.stringify(route));
+
+ // PROBE with an unclaimable random hash (correct amount + correct CLTV)
+ const rh = randomBytes(32).toString("hex");
+ runOn(`${node.prefix} sendpay "$(cat /tmp/ph_route.json)" ${rh} >/dev/null 2>&1 || true`);
+ const wp = waitFor(rh, 45);
+ const d = wp.data || wp;
+ const fc = d.failcodename || d.failcode || "";
+ // "reached the destination" = the DESTINATION node itself rejected the probe
+ // (unclaimable hash) with an unknown-payment-details error. Keying on
+ // erring_node (not an index) avoids CLN's sender-is-index-0 off-by-one.
+ const reached = /UNKNOWN_PAYMENT_DETAILS|INCORRECT_OR_UNKNOWN/.test(String(fc)) && d.erring_node === dest;
+ log(` via ${P.id.slice(0, 12)}… (${route.length}h, out ${sat(P.our)} sat): ${reached ? "✅ reaches destination" : `✗ ${fc || "no reach"}@idx${d.erring_index}`}`);
+ if (!reached) continue;
+
+ if (DRY) { log(`DRY: a working path exists via ${P.id.slice(0, 16)}… (route in /tmp/ph_route.json). Not paying.`); process.exit(0); }
+
+ // fire the REAL payment through this route (same route => correct amount+CLTV)
+ log(`→ firing real payment via ${P.id.slice(0, 16)}…`);
+ cliJSON(`-k sendpay route="$(cat /tmp/ph_route.json)" payment_hash=${hash} bolt11=${inv} payment_secret=${secret} amount_msat=${amount}`);
+ const res = waitFor(hash, 70);
+ if (res.payment_preimage || res.status === "complete") { log(`🎉 PAID via ${P.id.slice(0, 12)}…! preimage: ${res.payment_preimage} | fee ${sat(Number(res.amount_sent_msat) - amount)} sat`); process.exit(0); }
+ const rd = res.data || res;
+ const rfc = rd.failcodename || rd.failcode;
+ if (!rfc) { log(`⚠️ real send did not resolve cleanly (${String(res.__raw || res.message || "").slice(0, 100)}). STOPPING to avoid a second in-flight payment — check listpays before retrying.`); process.exit(3); }
+ log(` real send failed here: ${rfc}@idx${rd.erring_index} (liquidity shifted). trying next peer…`);
+}
+
+log("✗ no reachable peer of the destination had inbound room for the full amount right now.");
+log(" (destination inbound is exhausted across all paths we can reach, or the amount is too large for any single peer path.)");
+process.exit(2);
diff --git a/scripts/payment-monitor.sh b/scripts/payment-monitor.sh
new file mode 100755
index 00000000..c50aad9e
--- /dev/null
+++ b/scripts/payment-monitor.sh
@@ -0,0 +1,210 @@
+#!/bin/bash
+# Payment failure monitor for coinos-server
+# Watches app container logs and compiles failure analytics
+# Run: ./scripts/payment-monitor.sh [hours_to_watch]
+# Output: periodic reports to stdout + detailed data to /tmp/payment-failures.jsonl
+
+WATCH_HOURS=${1:-24}
+DATA_FILE="/tmp/payment-failures.jsonl"
+REPORT_INTERVAL=1800 # report every 30 min
+NODE_ID=$(docker exec cl lightning-cli getinfo 2>/dev/null | python3 -c "import sys,json; print(json.load(sys.stdin)['id'])" 2>/dev/null || echo "unknown")
+
+echo "Payment failure monitor started (watching for ${WATCH_HOURS}h)"
+echo "Node: ${NODE_ID}"
+echo "Data file: ${DATA_FILE}"
+echo "Reports every ${REPORT_INTERVAL}s"
+echo "---"
+
+# Clear old data
+> "$DATA_FILE"
+
+docker logs app -f 2>&1 | timeout $((WATCH_HOURS * 3600)) python3 -u -c "
+import sys, json, time, re
+from collections import Counter, defaultdict
+from datetime import datetime
+
+data_file = '${DATA_FILE}'
+report_interval = ${REPORT_INTERVAL}
+last_report = time.time()
+
+# Tracking
+failures = []
+error_types = Counter()
+failed_destinations = Counter()
+failed_channels = Counter() # channels that return temporary_channel_failure
+fee_insufficient_channels = Counter()
+failed_amounts = [] # (amount, error_type)
+successful = 0
+total_attempts = 0
+
+prev_line = None
+
+def parse_failure(line, error_line):
+ global successful, total_attempts
+ total_attempts += 1
+
+ try:
+ msg = json.loads(line)['msg']
+ err_msg = json.loads(error_line)['msg'] if error_line else ''
+ except:
+ return
+
+ # Parse: username payment failed amount balance hash
+ m = re.match(r'(\S+) payment failed (\d+) (\d+)\s+(.*)', msg)
+ if not m:
+ return
+
+ username, amount, balance, rest = m.group(1), int(m.group(2)), int(m.group(3)), m.group(4)
+
+ # Categorize error
+ if 'Insufficient funds' in err_msg or 'insuficientes' in err_msg.lower():
+ etype = 'insufficient_funds'
+ elif 'excessive cost' in err_msg:
+ etype = 'excessive_cost'
+ elif 'temporary_channel_failure' in err_msg:
+ etype = 'temporary_channel_failure'
+ # Extract failed channel IDs
+ for ch in re.findall(r'(\d+x\d+x\d+/\d+)', err_msg):
+ failed_channels[ch] += 1
+ elif 'fee_insufficient' in err_msg:
+ etype = 'fee_insufficient'
+ for ch in re.findall(r'(\d+x\d+x\d+/\d+)', err_msg):
+ fee_insufficient_channels[ch] += 1
+ elif 'Timed out' in err_msg:
+ etype = 'timed_out'
+ for ch in re.findall(r'(\d+x\d+x\d+/\d+)', err_msg):
+ if 'temporary_channel_failure' in err_msg:
+ failed_channels[ch] += 1
+ if 'fee_insufficient' in err_msg:
+ fee_insufficient_channels[ch] += 1
+ elif 'Cannot send to self' in err_msg:
+ etype = 'self_pay'
+ elif 'incorrect_or_unknown' in err_msg:
+ etype = 'unknown_invoice'
+ elif 'already underway' in err_msg:
+ etype = 'already_underway'
+ elif 'already been paid' in err_msg:
+ etype = 'already_paid'
+ elif 'user not provided' in err_msg:
+ etype = 'user_not_found'
+ elif err_msg:
+ etype = err_msg[:80]
+ else:
+ etype = 'unknown'
+
+ error_types[etype] += 1
+ failed_amounts.append((amount, etype))
+
+ # Try to get destination from invoice
+ if rest and rest.startswith('ln'):
+ bolt11_prefix = rest[:20]
+ failed_destinations[bolt11_prefix] += 1
+
+ record = {
+ 'time': datetime.utcnow().isoformat(),
+ 'user': username,
+ 'amount': amount,
+ 'balance': balance,
+ 'error': etype,
+ 'detail': err_msg[:200] if err_msg else '',
+ }
+
+ with open(data_file, 'a') as f:
+ f.write(json.dumps(record) + '\n')
+
+def parse_success(line):
+ global successful
+ successful += 1
+
+def report():
+ global last_report
+ now = datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
+
+ print(f'\n====== PAYMENT FAILURE REPORT {now} UTC ======')
+ print(f'Total attempts: {total_attempts} Successful: {successful} Failed: {total_attempts}')
+ if total_attempts > 0:
+ print(f'Success rate: {successful/(successful+total_attempts)*100:.1f}%')
+
+ print(f'\n--- Error breakdown ---')
+ for etype, count in error_types.most_common(15):
+ print(f' {count:>5} {etype}')
+
+ if failed_channels:
+ print(f'\n--- Channels with liquidity failures (top 10) ---')
+ for ch, count in failed_channels.most_common(10):
+ print(f' {count:>5} {ch}')
+
+ if fee_insufficient_channels:
+ print(f'\n--- Channels with stale fee gossip (top 10) ---')
+ for ch, count in fee_insufficient_channels.most_common(10):
+ print(f' {count:>5} {ch}')
+
+ # Amount distribution of failures
+ if failed_amounts:
+ amounts_by_type = defaultdict(list)
+ for amt, etype in failed_amounts:
+ amounts_by_type[etype].append(amt)
+
+ print(f'\n--- Failed amount ranges by error ---')
+ for etype in ['insufficient_funds', 'excessive_cost', 'temporary_channel_failure', 'timed_out', 'fee_insufficient']:
+ amts = amounts_by_type.get(etype, [])
+ if amts:
+ print(f' {etype}: min={min(amts)} max={max(amts)} avg={sum(amts)//len(amts)} count={len(amts)}')
+
+ print(f'\n--- Recommendations ---')
+ if error_types.get('insufficient_funds', 0) > 10:
+ print(' * HIGH insufficient_funds: Consider showing fee preview before payment attempt')
+ if error_types.get('excessive_cost', 0) > 5:
+ print(' * HIGH excessive_cost: Increase default maxfee or improve channel connectivity')
+ if error_types.get('fee_insufficient', 0) > 5:
+ print(' * STALE GOSSIP: Some channels advertise lower fees than they charge')
+ print(' Nodes to investigate:')
+ for ch, count in fee_insufficient_channels.most_common(5):
+ scid = ch.split('/')[0]
+ print(f' {scid} ({count} failures)')
+ if failed_channels:
+ print(' * LIQUIDITY BOTTLENECKS:')
+ for ch, count in failed_channels.most_common(5):
+ if count >= 3:
+ scid = ch.split('/')[0]
+ print(f' {scid} ({count} failures) - consider opening channel to bypass')
+ if error_types.get('self_pay', 0) > 5:
+ print(' * SELF-PAY: Users trying to pay own invoices - consider UI guard')
+
+ print('=' * 55)
+ sys.stdout.flush()
+ last_report = time.time()
+
+for line in sys.stdin:
+ line = line.strip()
+ if not line:
+ continue
+
+ try:
+ d = json.loads(line)
+ msg = d.get('msg', '')
+ except:
+ prev_line = line
+ continue
+
+ # Track successes
+ if 'sent lightning' in msg or 'sent liquid' in msg or 'sent bitcoin' in msg:
+ parse_success(line)
+
+ # Track failures - need the next line for error detail
+ if prev_line and 'payment failed' in prev_line:
+ try:
+ prev_d = json.loads(prev_line)
+ if 'payment failed' in prev_d.get('msg', ''):
+ parse_failure(prev_line, line)
+ except:
+ pass
+
+ prev_line = line
+
+ # Periodic report
+ if time.time() - last_report >= report_interval:
+ report()
+" 2>&1
+
+echo "Monitor finished after ${WATCH_HOURS}h"
diff --git a/scripts/rebalance-economics.ts b/scripts/rebalance-economics.ts
new file mode 100644
index 00000000..0d4bf6e2
--- /dev/null
+++ b/scripts/rebalance-economics.ts
@@ -0,0 +1,96 @@
+#!/usr/bin/env bun
+// Models whether the lightning->liquid "bridge" usage is fee-positive for us
+// at a given rebalance batch size.
+//
+// The mechanic:
+// - A user funds via Lightning (we receive into our LN channels) and withdraws
+// via Liquid (we pay out of our Liquid hot wallet). Net: LN balance grows,
+// Liquid balance shrinks. We charge config.fee.liquid (0.1%) on the liquid
+// send, NOT offset by their credit:liquid (they bank credit:lightning from
+// the receive, which is a different key) — so we collect ~0.1% of throughput.
+// - To refill Liquid we must rebalance: close LN channels (on-chain BTC back to
+// our cl wallet) then peg-in to Liquid. That costs on-chain fees, amortized
+// over how much we move per rebalance.
+//
+// Revenue from bridging X sats of throughput = X * liquid_fee.
+// Cost to rebalance that X = (channel closes to free X) + 1 peg-in tx.
+// Break-even: revenue >= cost.
+//
+// Usage: bun scripts/rebalance-economics.ts [--feerate SAT_VB] [--avg-channel BTC] [--liquid-fee 0.001]
+
+const arg = (k: string, d: number) => {
+ const i = process.argv.indexOf(k);
+ return i >= 0 ? Number(process.argv[i + 1]) : d;
+};
+
+const SATS = 100_000_000;
+const liquidFee = arg("--liquid-fee", 0.001); // config.fee.liquid = 0.1%
+const feerate = arg("--feerate", 1.01); // sat/vB; current mutual_close ~1.01
+const avgChannelBtc = arg("--avg-channel", 0.3); // avg local balance freed per close
+
+// tx vsizes (vBytes)
+const MUTUAL_CLOSE_VB = 170; // 1 funding input + 2 outputs
+const PEGIN_VB = 150; // consolidate freed outputs -> 1 peg-in send (BTC side)
+
+function closesNeeded(btc: number) {
+ return Math.max(1, Math.ceil(btc / avgChannelBtc));
+}
+
+function rebalanceCostSats(btc: number) {
+ const closes = closesNeeded(btc);
+ const closeFees = closes * MUTUAL_CLOSE_VB * feerate;
+ const peginFee = PEGIN_VB * feerate; // one batched peg-in per rebalance
+ return { closes, closeFees, peginFee, total: closeFees + peginFee };
+}
+
+function revenueSats(btc: number) {
+ return btc * SATS * liquidFee;
+}
+
+console.log("═".repeat(70));
+console.log(" LIGHTNING -> LIQUID BRIDGE ECONOMICS");
+console.log(` liquid fee ${(liquidFee * 100).toFixed(2)}% | close feerate ${feerate} sat/vB | avg channel ${avgChannelBtc} BTC`);
+console.log("═".repeat(70));
+console.log(
+ "\n batch revenue closes close+pegin cost net margin",
+);
+console.log(" " + "-".repeat(64));
+
+for (const btc of [0.1, 0.25, 0.5, 1, 2, 5, 10]) {
+ const rev = revenueSats(btc);
+ const c = rebalanceCostSats(btc);
+ const net = rev - c.total;
+ const margin = (net / rev) * 100;
+ const tag = net >= 0 ? "PROFIT" : "LOSS";
+ console.log(
+ ` ${(btc + " BTC").padEnd(8)} ${(Math.round(rev) + "").padStart(9)}s ${(c.closes + "").padStart(4)} ${(Math.round(c.total) + "").padStart(10)}s ${(Math.round(net) + "").padStart(8)}s ${margin.toFixed(1).padStart(6)}% ${tag}`,
+ );
+}
+
+// Break-even batch size: revenue(btc) = cost(btc). With closes ~ btc/avgChannel,
+// cost grows ~linearly too, so solve numerically.
+let beBtc = 0;
+for (let b = 0.001; b <= 50; b += 0.001) {
+ if (revenueSats(b) >= rebalanceCostSats(b).total) {
+ beBtc = b;
+ break;
+ }
+}
+console.log("\n " + "-".repeat(64));
+if (beBtc) {
+ console.log(` Break-even: rebalancing >= ${beBtc.toFixed(3)} BTC per batch is fee-positive.`);
+} else {
+ console.log(` Never breaks even in range — cost scales with throughput as fast as revenue.`);
+}
+
+// The deeper truth: because closes scale with throughput, per-sat margin is
+// roughly constant. Show the asymptotic per-BTC economics.
+const big = rebalanceCostSats(10);
+const bigRev = revenueSats(10);
+console.log(
+ `\n At scale (per 1 BTC bridged): revenue ${Math.round(revenueSats(1))}s, ` +
+ `rebalance cost ~${Math.round(rebalanceCostSats(1).total)}s, ` +
+ `net ~${Math.round(revenueSats(1) - rebalanceCostSats(1).total)}s`,
+);
+console.log("═".repeat(70));
+process.exit(0);
generated by cgit v1.2.3 (git 2.51.0) at 2026-10-30 04:33:30 +0000