[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_112":113,"_114":115},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","xdv","name","subscriptions-feedback","path","backend/subscriptions.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":-5,"_58":59,"_60":61,"_62":34,"_63":-5,"_64":-5,"_65":66,"_67":68,"_69":70,"_71":72,"_73":74,"_75":-5,"_76":77,"_78":77,"_79":80,"_81":80,"_82":80,"_83":77,"_84":77,"_85":86,"_87":88,"_89":90,"_91":44},"file",{"_22":23,"_11":24,"_25":26,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_45":-5,"_13":46},"permalink","https://www.val.town/x/xdv/subscriptions-feedback/code/backend/subscriptions.ts","extension","id","14b027c1-d6a6-4a09-bf89-98c16b4d5c5a","subscriptions.ts","fileId","01a05e91-3c61-747c-96e4-65597fe6435a","parentId","01a05e91-3c61-747c-96e4-6bafb8891850","branchId","3cb18c55-e6c7-42be-b35f-89b94db8140e","branchVersion",19,"latest",true,"createdAt",["D",1788302892448],"version",1,"fileType","script","content","/** CRUD for services, subscriptions, charges and ratings, plus the row -> view mapping. */\nimport type {\n BillingCycle,\n Charge,\n Currency,\n Rating,\n Subscription,\n SubscriptionStatus,\n SubscriptionView,\n} from \"../shared/types.ts\";\nimport { isLive, LIVE_STATUSES } from \"../shared/types.ts\";\nimport { computeVerdict, medianMinor } from \"../shared/grade.ts\";\nimport {\n currentPeriod,\n monthlyEquivalentMinor,\n normaliseNextCharge,\n today,\n yearlyEquivalentMinor,\n} from \"../shared/money.ts\";\nimport { bool, exec, nameKey, num, query, queryOne, Row, str } from \"./db.ts\";\nimport { convertMinor, getRates, rateFor, type RateTable } from \"./fx.ts\";\nimport { findDuplicate } from \"../shared/dedupe.ts\";\n\nconst SUB_SELECT = `\n SELECT s.*, sv.name AS service_name, sv.category AS category\n FROM subscriptions s\n JOIN services sv ON sv.id = s.service_id\n`;\n\nexport { LIVE_STATUSES };\n\nfunction mapSubscription(r: Row): Subscription {\n return {\n id: num(r.id),\n serviceId: num(r.service_id),\n serviceName: str(r.service_name) ?? \"(unknown)\",\n category: str(r.category),\n planName: str(r.plan_name),\n amountMinor: num(r.amount_minor),\n currency: (str(r.currency) ?? \"PLN\") as Currency,\n billingCycle: (str(r.billing_cycle) ?? \"monthly\") as BillingCycle,\n startedOn: str(r.started_on),\n nextChargeOn: str(r.next_charge_on),\n status: (str(r.status) ?? \"active\") as SubscriptionStatus,\n cancelledOn: str(r.cancelled_on),\n cancelReason: str(r.cancel_reason),\n paymentMethod: str(r.payment_method),\n autoRenew: bool(r.auto_renew),\n source: (str(r.source) ?? \"manual\") as \"manual\" | \"email\",\n notes: str(r.notes),\n createdAt: str(r.created_at) ?? \"\",\n updatedAt: str(r.updated_at) ?? \"\",\n };\n}\n\nfunction mapRating(r: Row): Rating {\n return {\n id: num(r.id),\n subscriptionId: num(r.subscription_id),\n period: str(r.period) ?? \"\",\n quality: num(r.quality),\n usage: num(r.usage),\n note: str(r.note),\n createdAt: str(r.created_at) ?? \"\",\n };\n}\n\nfunction mapCharge(r: Row): Charge {\n return {\n id: num(r.id),\n subscriptionId: num(r.subscription_id),\n chargedOn: str(r.charged_on) ?? \"\",\n amountMinor: num(r.amount_minor),\n currency: (str(r.currency) ?? \"PLN\") as Currency,\n baseMinor: num(r.base_minor),\n fxRate: num(r.fx_rate),\n source: (str(r.source) ?? \"manual\") as Charge[\"source\"],\n orderRef: str(r.order_ref),\n inboundEmailId: r.inbound_email_id == null ? null : num(r.inbound_email_id),\n createdAt: str(r.created_at) ?? \"\",\n };\n}\n\nexport async function listRaw(includeCancelled = true): Promise {\n const sql = includeCancelled\n ? `${SUB_SELECT} ORDER BY s.status = 'cancelled', sv.name COLLATE NOCASE`\n : `${SUB_SELECT} WHERE s.status IN ('active','trial') ORDER BY sv.name COLLATE NOCASE`;\n return (await query(sql)).map(mapSubscription);\n}\n\nexport async function getSubscription(id: number): Promise {\n const row = await queryOne(`${SUB_SELECT} WHERE s.id = ?`, [id]);\n return row ? mapSubscription(row) : null;\n}\n\nexport async function allRatings(): Promise> {\n const rows = await query(`SELECT * FROM ratings ORDER BY period DESC`);\n const bySub = new Map();\n for (const r of rows) {\n const rating = mapRating(r);\n const list = bySub.get(rating.subscriptionId) ?? [];\n list.push(rating);\n bySub.set(rating.subscriptionId, list);\n }\n return bySub;\n}\n\nexport async function ratingsFor(subscriptionId: number): Promise {\n const rows = await query(\n `SELECT * FROM ratings WHERE subscription_id = ? ORDER BY period DESC`,\n [subscriptionId],\n );\n return rows.map(mapRating);\n}\n\nexport async function chargesFor(subscriptionId: number): Promise {\n const rows = await query(\n `SELECT * FROM charges WHERE subscription_id = ? ORDER BY charged_on DESC`,\n [subscriptionId],\n );\n return rows.map(mapCharge);\n}\n\n/**\n * Everything the dashboard renders, in one pass: costs normalised to month and\n * year in both native and base currency, plus a verdict from recent ratings.\n * The \"expensive\" threshold is the median live monthly cost, so it adapts to the\n * portfolio instead of hard-coding what counts as a lot of money.\n */\nexport async function listViews(includeCancelled = true): Promise {\n const [subs, ratings, rates] = await Promise.all([listRaw(includeCancelled), allRatings(), getRates()]);\n const period = currentPeriod();\n\n const liveMonthly = subs\n .filter((s) => isLive(s.status))\n .map((s) => convertMinor(monthlyEquivalentMinor(s.amountMinor, s.billingCycle), s.currency, rates));\n const threshold = medianMinor(liveMonthly);\n\n return subs.map((s) => toView(s, ratings.get(s.id) ?? [], rates, period, threshold));\n}\n\nexport function toView(\n s: Subscription,\n ratings: Rating[],\n rates: RateTable,\n period: string,\n expensiveThresholdMinor?: number,\n): SubscriptionView {\n const monthlyMinor = monthlyEquivalentMinor(s.amountMinor, s.billingCycle);\n const yearlyMinor = yearlyEquivalentMinor(s.amountMinor, s.billingCycle);\n const monthlyBaseMinor = convertMinor(monthlyMinor, s.currency, rates);\n const verdict = computeVerdict({\n ratings,\n currentPeriod: period,\n monthlyBaseMinor,\n expensiveThresholdMinor,\n });\n const live = isLive(s.status);\n return {\n ...s,\n monthlyMinor,\n yearlyMinor,\n monthlyBaseMinor,\n yearlyBaseMinor: convertMinor(yearlyMinor, s.currency, rates),\n fxRate: rateFor(s.currency, rates),\n verdict,\n latestRating: ratings[0] ?? null,\n ratingDue: live && !ratings.some((r) => r.period === period),\n };\n}\n\n// --- writes -----------------------------------------------------------------\n\nexport async function findServiceId(name: string): Promise {\n const row = await queryOne(\"SELECT id FROM services WHERE name_key = ?\", [nameKey(name)]);\n return row ? num(row.id) : null;\n}\n\nexport async function findServiceByDomain(domain: string): Promise {\n const row = await queryOne(\"SELECT id FROM services WHERE vendor_domain = ?\", [domain.toLowerCase()]);\n return row ? num(row.id) : null;\n}\n\nexport async function upsertService(\n name: string,\n opts: { vendorDomain?: string | null; category?: string | null } = {},\n): Promise {\n const existing = await findServiceId(name);\n if (existing) {\n // Fill in blanks we learn later (e.g. the domain of a forwarded receipt).\n if (opts.vendorDomain) {\n await exec(\"UPDATE services SET vendor_domain = COALESCE(vendor_domain, ?) WHERE id = ?\", [\n opts.vendorDomain.toLowerCase(),\n existing,\n ]);\n }\n if (opts.category) {\n await exec(\"UPDATE services SET category = COALESCE(category, ?) WHERE id = ?\", [opts.category, existing]);\n }\n return existing;\n }\n const res = await exec(\n \"INSERT INTO services (name, name_key, vendor_domain, category) VALUES (?, ?, ?, ?)\",\n [name.trim(), nameKey(name), opts.vendorDomain?.toLowerCase() ?? null, opts.category ?? null],\n );\n return res.lastInsertRowid!;\n}\n\nexport interface SubscriptionInput {\n serviceName: string;\n planName?: string | null;\n amountMinor: number;\n currency: Currency;\n billingCycle: BillingCycle;\n startedOn?: string | null;\n nextChargeOn?: string | null;\n status?: SubscriptionStatus;\n paymentMethod?: string | null;\n category?: string | null;\n vendorDomain?: string | null;\n notes?: string | null;\n source?: \"manual\" | \"email\";\n}\n\nexport async function createSubscription(input: SubscriptionInput): Promise {\n const serviceId = await upsertService(input.serviceName, {\n vendorDomain: input.vendorDomain,\n category: input.category,\n });\n const started = input.startedOn ?? today();\n const next = input.nextChargeOn\n ? normaliseNextCharge(input.nextChargeOn, input.billingCycle, today())\n : normaliseNextCharge(started, input.billingCycle, today());\n\n const res = await exec(\n `INSERT INTO subscriptions\n (service_id, plan_name, amount_minor, currency, billing_cycle, started_on,\n next_charge_on, status, payment_method, notes, source)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n serviceId,\n input.planName ?? null,\n input.amountMinor,\n input.currency,\n input.billingCycle,\n started,\n next,\n input.status ?? \"active\",\n input.paymentMethod ?? null,\n input.notes ?? null,\n input.source ?? \"manual\",\n ],\n );\n return res.lastInsertRowid!;\n}\n\nconst PATCHABLE: Record = {\n planName: \"plan_name\",\n amountMinor: \"amount_minor\",\n currency: \"currency\",\n billingCycle: \"billing_cycle\",\n startedOn: \"started_on\",\n nextChargeOn: \"next_charge_on\",\n status: \"status\",\n paymentMethod: \"payment_method\",\n notes: \"notes\",\n autoRenew: \"auto_renew\",\n cancelledOn: \"cancelled_on\",\n cancelReason: \"cancel_reason\",\n};\n\nexport async function updateSubscription(id: number, patch: Record): Promise {\n const sets: string[] = [];\n const args: unknown[] = [];\n for (const [key, column] of Object.entries(PATCHABLE)) {\n if (!(key in patch)) continue;\n let value = patch[key];\n if (key === \"autoRenew\") value = value ? 1 : 0;\n sets.push(`${column} = ?`);\n args.push(value ?? null);\n }\n if (sets.length === 0) return;\n sets.push(\"updated_at = datetime('now')\");\n args.push(id);\n await exec(`UPDATE subscriptions SET ${sets.join(\", \")} WHERE id = ?`, args);\n}\n\n/**\n * Cancelling keeps the row. The whole point of tracking cancellations is to be\n * able to say what they saved, which needs the price and the date they stopped.\n */\nexport async function cancelSubscription(id: number, opts: { on?: string; reason?: string } = {}): Promise {\n await exec(\n `UPDATE subscriptions\n SET status = 'cancelled', cancelled_on = ?, cancel_reason = ?, next_charge_on = NULL,\n updated_at = datetime('now')\n WHERE id = ?`,\n [opts.on ?? today(), opts.reason ?? null, id],\n );\n}\n\nexport async function reactivateSubscription(id: number, nextChargeOn?: string): Promise {\n const sub = await getSubscription(id);\n if (!sub) return;\n const next = normaliseNextCharge(nextChargeOn ?? today(), sub.billingCycle, today());\n await exec(\n `UPDATE subscriptions\n SET status = 'active', cancelled_on = NULL, cancel_reason = NULL, next_charge_on = ?,\n updated_at = datetime('now')\n WHERE id = ?`,\n [next, id],\n );\n}\n\n/**\n * Hard delete, for a row that should never have existed — a misread receipt, say.\n * To stop paying for something real, cancel it instead: the history is what the\n * savings figure is made of.\n *\n * The dependent rows go explicitly rather than through ON DELETE CASCADE, which\n * only fires when foreign key enforcement happens to be enabled.\n */\nexport async function deleteSubscription(id: number): Promise {\n const sub = await getSubscription(id);\n await exec(\"DELETE FROM ratings WHERE subscription_id = ?\", [id]);\n await exec(\"DELETE FROM charges WHERE subscription_id = ?\", [id]);\n await exec(\"DELETE FROM review_items WHERE subscription_id = ?\", [id]);\n await exec(\"DELETE FROM subscriptions WHERE id = ?\", [id]);\n\n // A service outlives any one of its subscriptions — a plan change makes a new\n // subscription row against the same service — so it is only removed once the\n // last one referring to it is gone. Otherwise \"delete\" would leave a row behind\n // that nothing can ever show again.\n if (sub) {\n await exec(\n `DELETE FROM services\n WHERE id = ? AND NOT EXISTS (SELECT 1 FROM subscriptions WHERE service_id = ?)`,\n [sub.serviceId, sub.serviceId],\n );\n }\n}\n\n/**\n * Category lives on the service, not the subscription, so editing it from a\n * subscription row has to hop across. Worth allowing: the category drives the\n * spend breakdown, and a receipt's guess is often close but not right.\n */\nexport async function setCategory(subscriptionId: number, category: string | null): Promise {\n await exec(\n `UPDATE services SET category = ?\n WHERE id = (SELECT service_id FROM subscriptions WHERE id = ?)`,\n [category, subscriptionId],\n );\n}\n\nexport async function saveRating(\n subscriptionId: number,\n period: string,\n quality: number,\n usage: number,\n note?: string | null,\n): Promise {\n const clamp = (n: number) => Math.max(1, Math.min(5, Math.round(n)));\n await exec(\n `INSERT INTO ratings (subscription_id, period, quality, \"usage\", note)\n VALUES (?, ?, ?, ?, ?)\n ON CONFLICT(subscription_id, period) DO UPDATE\n SET quality = excluded.quality, \"usage\" = excluded.\"usage\", note = excluded.note`,\n [subscriptionId, period, clamp(quality), clamp(usage), note ?? null],\n );\n}\n\n/**\n * Records a payment that actually happened, pinning the FX rate used at the time.\n *\n * Checks first whether we already have this payment under another guise — the same\n * order number, or the same amount a day or two apart — because a single charge can\n * reach us twice: once when you mention it, once when the receipt arrives.\n */\nexport async function recordCharge(opts: {\n subscriptionId: number;\n chargedOn: string;\n amountMinor: number;\n currency: Currency;\n source?: Charge[\"source\"];\n orderRef?: string | null;\n inboundEmailId?: number | null;\n}): Promise<{ recorded: boolean; reason?: string }> {\n const existing = await chargesFor(opts.subscriptionId);\n const duplicate = findDuplicate(existing, {\n chargedOn: opts.chargedOn,\n amountMinor: opts.amountMinor,\n currency: opts.currency,\n orderRef: opts.orderRef,\n });\n\n if (duplicate) {\n // Still worth keeping the order number if this telling of it had one and the\n // record we already hold does not.\n if (opts.orderRef && !duplicate.charge.orderRef) {\n await exec(\"UPDATE charges SET order_ref = ? WHERE id = ?\", [opts.orderRef, duplicate.charge.id]);\n }\n return { recorded: false, reason: duplicate.reason };\n }\n\n const rates = await getRates();\n const rate = rateFor(opts.currency, rates);\n await exec(\n `INSERT INTO charges (subscription_id, charged_on, amount_minor, currency, base_minor, fx_rate, source, order_ref, inbound_email_id)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n ON CONFLICT (subscription_id, charged_on, amount_minor)\n DO UPDATE SET order_ref = COALESCE(charges.order_ref, excluded.order_ref)`,\n [\n opts.subscriptionId,\n opts.chargedOn,\n opts.amountMinor,\n opts.currency,\n Math.round(opts.amountMinor * rate),\n rate,\n opts.source ?? \"manual\",\n opts.orderRef ?? null,\n opts.inboundEmailId ?? null,\n ],\n );\n return { recorded: true };\n}\n","deleted",false,"label",[47,24],"backend","9bc728ac-50f9-4071-a163-6ccfe685ce52",["D",1788292971385],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Subscription tracker — separate instance for colleague feedback","imageUrl","userId","5f1525c6-51a0-4c57-858d-f94f1e686d46","pinnedFileId","01a05e91-3c61-747c-96e4-e7f8845199ec","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","01a05e29-777a-77c1-a910-e4a1d8102a40","tursoName","9bc728ac50f94071a1636ccfe685ce52","tursoHostname","9bc728ac50f94071a1636ccfe685ce52-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789514495229],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_111":-5},"org",{"_9":10,"_105":106,"_107":-5,"_58":-5,"_108":109,"_110":44},"branch",{"_37":92,"_11":93,"_22":30,"_94":48,"_95":96,"_97":98,"_99":53,"_100":101,"_102":68,"_103":12,"_104":10},"isOwned",20,"main","projectId","forkedBranchId","723ee93a-6b67-428c-b3cc-6f9491011d78","forkedBranchVersion",60,"forkedProjectPrivacy","forkedProjectId","5588f200-1c41-400f-bf66-81f9f6ac0211","forkedProjectOrgId","projectName","userHandle","displayName","xd.ventures","avatar","tier","free","canRestrictVals","profileImageUrl","ctx",{"_85":-5,"_145":146,"_147":148,"_149":150,"_151":44,"_152":44,"_153":44,"_154":34,"_155":-5,"_156":-7,"_157":-7,"_158":66,"_159":44,"_160":44,"_161":-5,"_162":44,"_163":44,"_164":44,"_165":-5},"ENV",{"_116":117,"_118":119,"_120":93,"_121":122,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138,"_139":140,"_141":142,"_143":144},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]