384 lines
18 KiB
JavaScript
384 lines
18 KiB
JavaScript
// Generated, do not edit. sha256:bbb7036ed8755194a6ae32362bf5faec1d944b2733c93e94b0d21804b434b99e
|
|
import { createHash } from 'node:crypto';
|
|
|
|
import * as db from '../db.js';
|
|
import { CONFIG } from '../../shared/config.js';
|
|
import { fail } from '../../shared/app-error.js';
|
|
import { listOpenValuationDiffs } from './valuations.js';
|
|
import { listPriceGaps, getPricePoint } from './price-history.js';
|
|
import { trackUpdateMany } from './change-log.js';
|
|
import { positionsByAsset } from './positions.js';
|
|
import { daysBetween } from './_shared.js';
|
|
import { healthChecks } from '../registers.mjs';
|
|
|
|
function rij(row, counts = {}) {
|
|
return { row, counts };
|
|
}
|
|
|
|
function dubbeleEvents() {
|
|
return db.all(`SELECT Event.assetId, Asset.symbol, Event.type, Event.occurredAt,
|
|
Event.productId, MetalProduct.name AS product_name, COUNT(*) AS n
|
|
FROM Event
|
|
JOIN Asset ON Asset.id = Event.assetId
|
|
LEFT JOIN MetalProduct ON MetalProduct.id = Event.productId
|
|
WHERE Event.type <> 'reservation'
|
|
GROUP BY Event.assetId, Event.type, Event.occurredAt, Event.amount, Event.productId
|
|
HAVING COUNT(*) > 1
|
|
ORDER BY n DESC, Event.occurredAt DESC`)
|
|
.map(r => rij({ assetId: r.assetId, symbol: r.symbol, type: r.type, occurredAt: r.occurredAt,
|
|
productId: r.productId, product_name: r.product_name }, { keer: r.n }));
|
|
}
|
|
|
|
function assetsZonderBalans() {
|
|
return db.all(`SELECT Asset.id AS assetId, Asset.symbol, COUNT(Event.id) AS events
|
|
FROM Asset
|
|
JOIN Event ON Event.assetId = Asset.id AND Event.type <> 'reservation'
|
|
WHERE NOT EXISTS (SELECT 1 FROM Valuation WHERE Valuation.assetId = Asset.id)
|
|
GROUP BY Asset.id, Asset.symbol
|
|
ORDER BY events DESC`)
|
|
.map(r => rij({ assetId: r.assetId, symbol: r.symbol }, { boekingen: r.events }));
|
|
}
|
|
|
|
function onverzoendeBalansen() {
|
|
const symbolen = new Map(db.all(`SELECT id, symbol FROM Asset`).map(a => [a.id, a.symbol]));
|
|
return listOpenValuationDiffs()
|
|
.map(v => rij({
|
|
assetId: v.assetId, symbol: symbolen.get(v.assetId) ?? null,
|
|
accountId: v.accountId, valuedOn: v.valuedOn,
|
|
}));
|
|
}
|
|
|
|
const LAATSTE_KOERS = `(SELECT MAX(observedAt) FROM PriceHistory WHERE PriceHistory.assetId = Asset.id)`;
|
|
function achterlopendeKoersen() {
|
|
return db.all(`SELECT Asset.id AS assetId, Asset.symbol,
|
|
MAX(Event.occurredAt) AS occurredAt,
|
|
${LAATSTE_KOERS} AS observedAt
|
|
FROM Asset
|
|
JOIN Event ON Event.assetId = Asset.id AND Event.type <> 'reservation'
|
|
GROUP BY Asset.id, Asset.symbol
|
|
HAVING ${LAATSTE_KOERS} IS NULL OR ${LAATSTE_KOERS} < MAX(Event.occurredAt)
|
|
ORDER BY Asset.symbol`)
|
|
.map(r => rij(r));
|
|
}
|
|
|
|
const EERSTE_KOERS = `(SELECT MIN(observedAt) FROM PriceHistory WHERE PriceHistory.assetId = Asset.id)`;
|
|
function koersenBeginnenTeLaat() {
|
|
const maandDag = `${String(CONFIG.PEILDATUM.MONTH).padStart(2, '0')}-${String(CONFIG.PEILDATUM.DAY).padStart(2, '0')}`;
|
|
const EERSTE_PEILDATUM = `(CAST(strftime('%Y', MIN(Event.occurredAt)) AS INTEGER) + 1) || '-${maandDag}'`;
|
|
return db.all(`SELECT Asset.id AS assetId, Asset.symbol,
|
|
MIN(Event.occurredAt) AS occurredAt,
|
|
${EERSTE_KOERS} AS observedAt
|
|
FROM Asset
|
|
JOIN Event ON Event.assetId = Asset.id AND Event.type <> 'reservation'
|
|
GROUP BY Asset.id, Asset.symbol
|
|
HAVING ${EERSTE_KOERS} IS NOT NULL
|
|
AND substr(${EERSTE_KOERS}, 1, 10) > ${EERSTE_PEILDATUM}
|
|
ORDER BY Asset.symbol`)
|
|
.map(r => rij(r));
|
|
}
|
|
|
|
function gatenInKoershistorie() {
|
|
const perAsset = new Map();
|
|
for (const g of listPriceGaps({ head: false, tail: false, limit: null }).rows) {
|
|
const a = perAsset.get(g.assetId) || { assetId: g.assetId, symbol: g.symbol, gaten: 0, grootste: 0 };
|
|
a.gaten++;
|
|
a.grootste = Math.max(a.grootste, g.days);
|
|
perAsset.set(g.assetId, a);
|
|
}
|
|
return [...perAsset.values()].sort((x, y) => y.grootste - x.grootste)
|
|
.map(r => rij({ assetId: r.assetId, symbol: r.symbol }, { gaten: r.gaten, grootsteGatInDagen: r.grootste }));
|
|
}
|
|
|
|
function koersGestopt() {
|
|
const vandaag = new Date().toISOString().slice(0, 10);
|
|
const uit = [];
|
|
for (const a of positionsByAsset()) {
|
|
const grens = CONFIG.PRICE_HISTORY.CARRY_FORWARD_DAYS[a.kind];
|
|
if (!(a.net > 0) || grens == null) continue;
|
|
const laatste = db.one(`SELECT MAX(observedAt) AS observedAt FROM PriceHistory WHERE assetId = ? AND fiatPrice > 0`, [a.id]).observedAt;
|
|
if (!laatste) continue;
|
|
const dagen = daysBetween(laatste.slice(0, 10), vandaag);
|
|
if (dagen > grens) uit.push({ dagen, r: rij({ assetId: a.id, symbol: a.symbol, observedAt: laatste }, { dagenZonderKoers: dagen }) });
|
|
}
|
|
return uit.sort((x, y) => y.dagen - x.dagen).map(x => x.r);
|
|
}
|
|
|
|
function verplaatsingZonderTegenkant() {
|
|
return db.all(`SELECT Event.id AS eventId, Event.assetId, Asset.symbol, Event.occurredAt,
|
|
Event.fromAccountId, vanaf.name AS from_name,
|
|
Event.toAccountId, naar.name AS to_name
|
|
FROM Event
|
|
JOIN Asset ON Asset.id = Event.assetId
|
|
LEFT JOIN Account vanaf ON vanaf.id = Event.fromAccountId
|
|
LEFT JOIN Account naar ON naar.id = Event.toAccountId
|
|
WHERE Event.type = 'transfer'
|
|
AND (Event.fromAccountId IS NULL OR Event.toAccountId IS NULL)
|
|
AND (Event.externalAddress IS NULL OR Event.externalAddress = '')
|
|
AND Event.priceOrigin IS NOT 'none'
|
|
ORDER BY Event.occurredAt DESC`)
|
|
.map(r => rij(r));
|
|
}
|
|
|
|
function kostenZonderEffect() {
|
|
return db.all(`SELECT Event.id AS eventId, Event.assetId, Asset.symbol, Event.type, Event.occurredAt,
|
|
Event.source
|
|
FROM Event
|
|
JOIN Asset ON Asset.id = Event.assetId
|
|
WHERE (Event.type = 'interest' AND Event.fiatFee > 0)
|
|
OR (Event.type IN ('buy','interest') AND Event.unitFee > 0)
|
|
ORDER BY Event.occurredAt DESC`)
|
|
.map(r => rij(r));
|
|
}
|
|
|
|
function ongekoppeldeBewegingen() {
|
|
return db.all(`SELECT WalletTransaction.id AS walletTransactionId, WalletTransaction.accountId,
|
|
Account.name AS account_name,
|
|
WalletTransaction.direction, WalletTransaction.blockTime,
|
|
WalletTransaction.createdAt
|
|
FROM WalletTransaction
|
|
LEFT JOIN Account ON Account.id = WalletTransaction.accountId
|
|
-- Lightning only from the node, the same rule as syncTxProposals (proposals.js): an NWC
|
|
-- payment without its own event is not unlinked, it hangs on its LND twin; on an account
|
|
-- without a LND source the NWC wallet counts on its own.
|
|
WHERE (WalletTransaction.layer = 'onchain'
|
|
OR EXISTS (SELECT 1 FROM WalletSource
|
|
WHERE WalletSource.id = WalletTransaction.sourceId
|
|
AND (WalletSource.kind = 'lnd'
|
|
OR (WalletSource.kind = 'nwc' AND NOT EXISTS (
|
|
SELECT 1 FROM WalletSource node
|
|
WHERE node.accountId = WalletTransaction.accountId
|
|
AND node.kind = 'lnd')))))
|
|
AND NOT EXISTS (SELECT 1 FROM WalletTransactionEventLink
|
|
WHERE WalletTransactionEventLink.walletTransactionId = WalletTransaction.id)
|
|
AND NOT EXISTS (SELECT 1 FROM ReconcileProposal
|
|
WHERE ReconcileProposal.walletTransactionId = WalletTransaction.id
|
|
AND ReconcileProposal.status = 'pending')
|
|
ORDER BY WalletTransaction.blockTime DESC`)
|
|
.map(r => rij(r));
|
|
}
|
|
|
|
function dekkingZonderAsset() {
|
|
return db.all(`SELECT ProviderCoverage.providerId, ProviderCoverage.symbol
|
|
FROM ProviderCoverage
|
|
WHERE NOT EXISTS (SELECT 1 FROM Asset WHERE Asset.symbol = ProviderCoverage.symbol)
|
|
ORDER BY ProviderCoverage.symbol`)
|
|
.map(r => rij(r));
|
|
}
|
|
|
|
function koersRijen() {
|
|
return db.all(`SELECT PriceHistory.id AS priceHistoryId, PriceHistory.assetId, Asset.symbol, Asset.kind,
|
|
PriceHistory.providerId, Provider.name AS provider_name, PriceHistory.resolution,
|
|
PriceHistory.observedAt, PriceHistory.fiatPrice
|
|
FROM PriceHistory
|
|
JOIN Asset ON Asset.id = PriceHistory.assetId
|
|
JOIN Provider ON Provider.id = PriceHistory.providerId
|
|
ORDER BY PriceHistory.assetId, PriceHistory.observedAt, PriceHistory.id`);
|
|
}
|
|
const koersRij = (r, counts) => rij({ priceHistoryId: r.priceHistoryId, assetId: r.assetId, symbol: r.symbol,
|
|
observedAt: r.observedAt, providerId: r.providerId, provider_name: r.provider_name }, counts);
|
|
const factor = x => Math.round(x * 10) / 10;
|
|
|
|
function reeksenPerAsset() {
|
|
const m = new Map();
|
|
for (const r of koersRijen()) {
|
|
if (!(r.fiatPrice > 0)) continue;
|
|
if (!m.has(r.assetId)) m.set(r.assetId, []);
|
|
m.get(r.assetId).push(r);
|
|
}
|
|
return m;
|
|
}
|
|
const isOunceJump = ratio => {
|
|
const oz = CONFIG.METALS.UNITS.ozt, tol = CONFIG.HYGIENE.TROY_OUNCE_TOLERANCE;
|
|
const bij = (x, doel) => x >= doel * (1 - tol) && x <= doel * (1 + tol);
|
|
return bij(ratio, oz) || bij(ratio, 1 / oz);
|
|
};
|
|
|
|
function eenheidsverwisseling() {
|
|
const uit = [];
|
|
for (const reeks of reeksenPerAsset().values()) {
|
|
for (let i = 1; i < reeks.length; i++) {
|
|
const ratio = reeks[i].fiatPrice / reeks[i - 1].fiatPrice;
|
|
if (isOunceJump(ratio)) uit.push(koersRij(reeks[i], { factor: factor(ratio >= 1 ? ratio : 1 / ratio) }));
|
|
}
|
|
}
|
|
return uit;
|
|
}
|
|
|
|
function uitschieters() {
|
|
const f = CONFIG.HYGIENE.OUTLIER_FACTOR;
|
|
const uit = [];
|
|
for (const reeks of reeksenPerAsset().values()) {
|
|
for (let i = 1; i < reeks.length - 1; i++) {
|
|
const voor = reeks[i].fiatPrice / reeks[i - 1].fiatPrice, na = reeks[i].fiatPrice / reeks[i + 1].fiatPrice;
|
|
if (isOunceJump(voor) || isOunceJump(na)) continue;
|
|
const omhoog = voor >= f && na >= f, omlaag = voor <= 1 / f && na <= 1 / f;
|
|
if (omhoog || omlaag) uit.push(koersRij(reeks[i], { factor: factor(omhoog ? Math.min(voor, na) : 1 / Math.max(voor, na)) }));
|
|
}
|
|
}
|
|
return uit;
|
|
}
|
|
|
|
function bronnenOneens() {
|
|
const grens = CONFIG.HYGIENE.SOURCES_DISAGREE_SHARE;
|
|
const perDag = new Map();
|
|
for (const r of koersRijen()) {
|
|
if (r.resolution !== 'day' || !(r.fiatPrice > 0)) continue;
|
|
const sleutel = `${r.assetId}|${r.observedAt.slice(0, 10)}`;
|
|
if (!perDag.has(sleutel)) perDag.set(sleutel, []);
|
|
perDag.get(sleutel).push(r);
|
|
}
|
|
const uit = [];
|
|
for (const rijen of perDag.values()) {
|
|
if (new Set(rijen.map(r => r.providerId)).size < 2) continue;
|
|
const laag = rijen.reduce((a, b) => (b.fiatPrice < a.fiatPrice ? b : a));
|
|
const hoog = rijen.reduce((a, b) => (b.fiatPrice > a.fiatPrice ? b : a));
|
|
const spreiding = hoog.fiatPrice / laag.fiatPrice - 1;
|
|
if (spreiding > grens && laag.providerId !== hoog.providerId) {
|
|
uit.push(rij({ assetId: laag.assetId, symbol: laag.symbol, observedAt: laag.observedAt,
|
|
low_provider_name: laag.provider_name, high_provider_name: hoog.provider_name },
|
|
{ verschilProcent: Math.round(spreiding * 1000) / 10 }));
|
|
}
|
|
}
|
|
return uit;
|
|
}
|
|
|
|
function koersNietPositief() {
|
|
return koersRijen().filter(r => r.fiatPrice <= 0).map(r => koersRij(r, {}));
|
|
}
|
|
|
|
function koersInToekomst() {
|
|
const nu = new Date().toISOString();
|
|
return koersRijen().filter(r => r.observedAt > nu).map(r => koersRij(r, {}));
|
|
}
|
|
|
|
function boekingenMetDagkoers({ origin } = {}) {
|
|
const rijen = db.all(`SELECT Event.id AS eventId, Event.assetId, Asset.symbol, Event.type, Event.occurredAt,
|
|
Event.priceOrigin, Event.fiatPrice
|
|
FROM Event
|
|
JOIN Asset ON Asset.id = Event.assetId
|
|
WHERE Event.type IN ('buy','sell') AND Event.fiatPrice > 0
|
|
AND Event.priceOrigin IS NOT 'none'
|
|
AND (?1 = 0 OR Event.priceOrigin IS NULL)
|
|
ORDER BY Event.occurredAt DESC, Event.id DESC`, [origin === 'unknown' ? 1 : 0]);
|
|
const uit = [];
|
|
for (const r of rijen) {
|
|
const dag = getPricePoint(r.assetId, String(r.occurredAt).slice(0, 10));
|
|
if (dag && dag.fiatPrice > 0) uit.push({ ...r, ratio: r.fiatPrice / dag.fiatPrice });
|
|
}
|
|
return uit;
|
|
}
|
|
const boekingRij = (r, counts) => rij({ eventId: r.eventId, assetId: r.assetId, symbol: r.symbol, type: r.type,
|
|
occurredAt: r.occurredAt, priceOrigin: r.priceOrigin }, counts);
|
|
|
|
function boekingsprijsWijktAf() {
|
|
const grens = CONFIG.HYGIENE.BOOKING_PRICE_DEVIATION;
|
|
const uit = [];
|
|
for (const r of boekingenMetDagkoers()) {
|
|
if (isOunceJump(r.ratio)) uit.push(boekingRij(r, { factor: factor(r.ratio >= 1 ? r.ratio : 1 / r.ratio) }));
|
|
else if (Math.abs(r.ratio - 1) > grens) uit.push(boekingRij(r, { afwijkingProcent: Math.round((r.ratio - 1) * 1000) / 10 }));
|
|
}
|
|
return uit;
|
|
}
|
|
|
|
const SAME_PRICE_SHARE = 0.0005;
|
|
const afleidbaar = () => boekingenMetDagkoers({ origin: 'unknown' })
|
|
.filter(r => Math.abs(r.ratio - 1) <= SAME_PRICE_SHARE);
|
|
function herkomstAfleidbaar() {
|
|
return afleidbaar().map(r => boekingRij(r, {}));
|
|
}
|
|
|
|
export async function fillDerivablePriceOrigins() {
|
|
const ids = afleidbaar().map(r => r.eventId);
|
|
if (!ids.length) return 0;
|
|
trackUpdateMany('Event', ids, () => db.run(
|
|
`UPDATE Event SET priceOrigin = 'market_day'
|
|
WHERE priceOrigin IS NULL AND id IN (${ids.map(() => '?').join(',')})`, ids));
|
|
await db.persist();
|
|
return ids.length;
|
|
}
|
|
|
|
const CHECKS = Object.freeze([
|
|
{ kind: 'duplicate_events', severity: 'warn', run: dubbeleEvents, countsFrom: ['id'] },
|
|
{ kind: 'assets_without_balance', severity: 'info', run: assetsZonderBalans, countsFrom: ['id'] },
|
|
{ kind: 'unreconciled_valuations', severity: 'warn', run: onverzoendeBalansen, countsFrom: [] },
|
|
{ kind: 'stale_prices', severity: 'warn', run: achterlopendeKoersen, countsFrom: [] },
|
|
{ kind: 'late_price_history', severity: 'warn', run: koersenBeginnenTeLaat, countsFrom: [] },
|
|
{ kind: 'price_gaps', severity: 'info', run: gatenInKoershistorie, countsFrom: ['observedAt'] },
|
|
{ kind: 'price_stopped', severity: 'warn', run: koersGestopt, countsFrom: ['observedAt'] },
|
|
{ kind: 'transfer_without_counterpart', severity: 'warn', run: verplaatsingZonderTegenkant, countsFrom: [] },
|
|
{ kind: 'fee_without_effect', severity: 'info', run: kostenZonderEffect, countsFrom: [] },
|
|
{ kind: 'unlinked_transactions', severity: 'warn', run: ongekoppeldeBewegingen, countsFrom: [] },
|
|
{ kind: 'coverage_without_asset', severity: 'info', run: dekkingZonderAsset, countsFrom: [] },
|
|
{ kind: 'price_unit_swap', severity: 'warn', run: eenheidsverwisseling, countsFrom: ['fiatPrice'] },
|
|
{ kind: 'price_outlier', severity: 'warn', run: uitschieters, countsFrom: ['fiatPrice'] },
|
|
{ kind: 'price_sources_disagree', severity: 'info', run: bronnenOneens, countsFrom: ['fiatPrice'] },
|
|
{ kind: 'price_not_positive', severity: 'warn', run: koersNietPositief, countsFrom: [] },
|
|
{ kind: 'price_in_future', severity: 'warn', run: koersInToekomst, countsFrom: [] },
|
|
{ kind: 'booking_price_deviates', severity: 'warn', run: boekingsprijsWijktAf, countsFrom: ['fiatPrice'] },
|
|
{ kind: 'price_origin_derivable', severity: 'info', run: herkomstAfleidbaar, countsFrom: ['fiatPrice'] },
|
|
]);
|
|
|
|
export function hygieneKinds() {
|
|
return checks().map(c => ({ kind: c.kind, severity: c.severity }));
|
|
}
|
|
|
|
function checks() {
|
|
const extra = healthChecks.list().map(e => ({ kind: e.id, severity: e.severity, run: e.run, countsFrom: e.countsFrom }));
|
|
const dubbel = extra.find(e => CHECKS.some(c => c.kind === e.kind));
|
|
if (dubbel) throw new Error(`health check '${dubbel.kind}' is registered and also a core check`);
|
|
return [...CHECKS, ...extra];
|
|
}
|
|
|
|
export function hygieneFingerprint(kind, row = {}, counts = {}) {
|
|
const vast = obj => JSON.stringify(Object.keys(obj).sort().map(k => [k, obj[k]]));
|
|
return createHash('sha256').update(`${kind}|${vast(row)}|${vast(counts)}`).digest('hex').slice(0, 32);
|
|
}
|
|
|
|
export function listHygieneAcceptances() {
|
|
return db.all(`SELECT id, kind, fingerprint, summary, note, createdAt
|
|
FROM HygieneAcceptance ORDER BY createdAt DESC, id DESC`);
|
|
}
|
|
|
|
export async function acceptHygieneFinding({ kind, row = {}, counts = {}, summary, note = null } = {}) {
|
|
if (!checks().some(c => c.kind === kind)) {
|
|
fail('health.acceptUnknownKind', {}, `Unknown hygiene finding kind '${kind}'.`);
|
|
}
|
|
if (!summary || !String(summary).trim()) {
|
|
fail('health.acceptNeedsSummary', {}, 'A hygiene acceptance needs a readable summary.');
|
|
}
|
|
const fingerprint = hygieneFingerprint(kind, row, counts);
|
|
db.run(`INSERT INTO HygieneAcceptance (kind, fingerprint, summary, note, createdAt)
|
|
VALUES (?,?,?,?,?)
|
|
ON CONFLICT(fingerprint) DO UPDATE SET
|
|
summary = excluded.summary, note = excluded.note, createdAt = excluded.createdAt`,
|
|
[kind, fingerprint, String(summary).trim(), note ? String(note).trim() : null, new Date().toISOString()]);
|
|
await db.persist();
|
|
return fingerprint;
|
|
}
|
|
|
|
export async function unacceptHygieneFinding(id) {
|
|
db.run(`DELETE FROM HygieneAcceptance WHERE id = ?`, [id]);
|
|
await db.persist();
|
|
}
|
|
|
|
export function hygieneReport({ kinds = null } = {}) {
|
|
const alle = checks();
|
|
const gekozen = kinds ? alle.filter(c => kinds.includes(c.kind)) : alle;
|
|
const grens = CONFIG.HYGIENE.MAX_ROWS_PER_FINDING;
|
|
const aanvaard = new Set(db.all(`SELECT fingerprint FROM HygieneAcceptance`).map(r => r.fingerprint));
|
|
const bevindingen = gekozen.map(c => {
|
|
const alles = aanvaard.size
|
|
? c.run().filter(r => !aanvaard.has(hygieneFingerprint(c.kind, r.row, r.counts)))
|
|
: c.run();
|
|
return {
|
|
kind: c.kind, severity: c.severity, countsFrom: c.countsFrom,
|
|
total: alles.length,
|
|
rows: alles.length > grens ? alles.slice(0, grens) : alles,
|
|
};
|
|
});
|
|
return {
|
|
total: bevindingen.reduce((n, b) => n + b.total, 0),
|
|
findings: bevindingen,
|
|
};
|
|
}
|