aboutsummaryrefslogtreecommitdiffstats
path: root/app/src/lib/server
diff options
context:
space:
mode:
Diffstat (limited to 'app/src/lib/server')
-rw-r--r--app/src/lib/server/actual.ts20
-rw-r--r--app/src/lib/server/db/schema.ts27
-rw-r--r--app/src/lib/server/sb1.ts158
-rw-r--r--app/src/lib/server/session-log.ts4
4 files changed, 93 insertions, 116 deletions
diff --git a/app/src/lib/server/actual.ts b/app/src/lib/server/actual.ts
index 4cf0262..fb1a4fb 100644
--- a/app/src/lib/server/actual.ts
+++ b/app/src/lib/server/actual.ts
@@ -1,13 +1,13 @@
-import { ACTUAL_BUDGET_ID, ACTUAL_HOST, ACTUAL_PASS } from "$env/static/private";
+import { ACTUAL_FILE_ID, ACTUAL_HOST, ACTUAL_PASS } from "$env/static/private";
import * as actual from "@actual-app/api"
import { existsSync, mkdirSync } from "node:fs";
import path from "node:path"
import process from "node:process";
-import type { Sb1Transaction } from "./sb1";
import type { ImportTransactionEntity } from "@actual-app/api/@types/loot-core/src/types/models/import-transaction";
import { Temporal } from "temporal-polyfill";
+import type { Sb1Transaction } from "$lib/shared";
-async function init_actual() {
+export async function init_actual() {
const dataDir = path.resolve(process.cwd(), "data/actualDataDir")
if (!existsSync(dataDir)) mkdirSync(dataDir, { recursive: true });
return actual.init({
@@ -15,33 +15,37 @@ async function init_actual() {
serverURL: ACTUAL_HOST,
dataDir: dataDir
}).then(async () => {
- await actual.downloadBudget(ACTUAL_BUDGET_ID)
+ await actual.downloadBudget(ACTUAL_FILE_ID)
await actual.sync()
})
}
export async function import_transactions(account: string, transactions: Sb1Transaction[], dryRun: boolean) {
-
+ await init_actual()
function parsedDate(date: number) {
const instant = Temporal.Instant.fromEpochMilliseconds(date)
return instant.toString({ timeZone: "Europe/Oslo" }).split("T")[0]
}
function notes(transaction: Sb1Transaction) {
- const {description,cleanedDescription} =transaction
+ const { description, cleanedDescription } = transaction
if (description.toLowerCase().trim() === cleanedDescription.toLowerCase().trim()) return undefined
return description
}
+ function amount(amount: number) {
+ return Math.round(amount * 100)
+ }
+
const actualMappedTransactions: ImportTransactionEntity[] = transactions.filter(c => c.bookingStatus === "BOOKED").map(c => ({
account,
date: parsedDate(c.date),
- amount: c.amount,
+ amount: amount(c.amount),
notes: notes(c),
payee_name: c.cleanedDescription
}))
- return await actual.importTransactions(account, actualMappedTransactions, { dryRun })
+ await actual.importTransactions(account, actualMappedTransactions, { dryRun })
}
export async function get_budgets() {
diff --git a/app/src/lib/server/db/schema.ts b/app/src/lib/server/db/schema.ts
index 4d13ed6..dbff3a2 100644
--- a/app/src/lib/server/db/schema.ts
+++ b/app/src/lib/server/db/schema.ts
@@ -1,17 +1,17 @@
import { relations, sql } from 'drizzle-orm';
-import { numeric, text, pgTable, uuid, json } from "drizzle-orm/pg-core";
-import type { Sb1Tokens } from '../sb1';
+import { numeric, text, pgTable, uuid, jsonb } from "drizzle-orm/pg-core";
import type { SessionLogType } from '../session-log';
+import type { Sb1Tokens, Sb1Transaction, Sb1TransactionDetails } from '$lib/shared';
-export const syncSession = pgTable("session", {
+export const SyncSessionTable = pgTable("session", {
id: uuid('id').primaryKey().default(sql`uuidv7()`),
authzState: text("authzState"),
accessTokenCreated: numeric("accessTokenCreated"),
refreshTokenCreated: numeric("refreshTokenCreated"),
- tokens: json("tokens").$type<Sb1Tokens>()
+ tokens: jsonb("tokens").$type<Sb1Tokens>()
})
-export const syncLog = pgTable("session_log", {
+export const SyncLogTable = pgTable("session_log", {
id: uuid('id').primaryKey().default(sql`uuidv7()`),
sessionId: text("session_id"),
dateTime: text("date_time"),
@@ -19,13 +19,18 @@ export const syncLog = pgTable("session_log", {
msg: text("msg")
})
-export const syncLogRelation = relations(syncLog, ({ one }) => ({
- author: one(syncSession, {
- fields: [syncLog.sessionId],
- references: [syncSession.id],
+export const TransactionsTable = pgTable("transactions", {
+ transaction: jsonb("transaction").$type<Sb1Transaction>(),
+ details: jsonb("details").$type<Sb1TransactionDetails>()
+})
+
+export const SyncLogRelation = relations(SyncLogTable, ({ one }) => ({
+ author: one(SyncSessionTable, {
+ fields: [SyncLogTable.sessionId],
+ references: [SyncSessionTable.id],
})
}))
-export const syncSessionLogRelation = relations(syncSession, ({ many }) => ({
- logs: many(syncLog)
+export const SyncSessionLogRelation = relations(SyncSessionTable, ({ many }) => ({
+ logs: many(SyncLogTable)
}))
diff --git a/app/src/lib/server/sb1.ts b/app/src/lib/server/sb1.ts
index f6507ef..0a51649 100644
--- a/app/src/lib/server/sb1.ts
+++ b/app/src/lib/server/sb1.ts
@@ -1,84 +1,11 @@
import { SB1_FIN_INST, SB1_ID, SB1_REDIRECT_URI, SB1_SECRET } from "$env/static/private";
-import { eq } from "drizzle-orm";
+import { eq, sql } from "drizzle-orm";
import { Temporal } from "temporal-polyfill";
import { randomUUID } from "node:crypto";
import { db } from "./db";
-import { syncSession } from "./db/schema";
+import { SyncSessionTable, TransactionsTable } from "./db/schema";
import { add_session_log } from "./session-log";
-
-export type Sb1Tokens = {
- access_token: string
- expires_in: number
- refresh_token_expires_in: number
- refresh_token_absolute_expires_in: number
- token_type: string
- refresh_token: string
-}
-
-export type Sb1Transaction = {
- id: string
- nonUniqueId: string
- description: string
- cleanedDescription: string
- accountNumber: Sb1AccountNumber
- amount: number
- date: number
- interestDate: number
- typeCode: string
- typeText: string
- currencyCode: string
- canShowDetails: boolean
- source: string
- isConfidential: boolean
- bookingStatus: string
- accountName: string
- accountKey: string
- accountCurrency: string
- isFromCurrencyAccount: boolean
- classificationInput: Sb1ClassificationInput
-}
-
-export type Sb1Account = {
- key: string;
- accountNumber: string;
- iban: string;
- name: string;
- description: string;
- balance: number;
- availableBalance: number;
- currencyCode: string;
- owner: Sb1AccountOwner;
- productType: string;
- type: string;
- productId: string;
- descriptionCode: string;
- accountProperties: { [key: string]: boolean };
-}
-
-export type Sb1AccountOwner = {
- name: string;
- firstName: string;
- lastName: string;
- type: string;
- age: number;
- customerKey: string;
- ssnKey: string;
-}
-
-
-export type Sb1AccountNumber = {
- value: string
- formatted: string
- unformatted: string
-}
-
-export type Sb1ClassificationInput = {
- id: string
- amount: number
- type: string
- text: string
- date: string
-}
+import type { Sb1Account, Sb1Tokens, Sb1Transaction } from "$lib/shared";
const auth = {
async is_ready(): Promise<boolean> {
@@ -87,10 +14,10 @@ const auth = {
},
async get_auth_info() {
const entity = await db.select({
- refreshTokenCreated: syncSession.refreshTokenCreated,
- accessTokenCreated: syncSession.accessTokenCreated,
- tokens: syncSession.tokens
- }).from(syncSession)
+ refreshTokenCreated: SyncSessionTable.refreshTokenCreated,
+ accessTokenCreated: SyncSessionTable.accessTokenCreated,
+ tokens: SyncSessionTable.tokens
+ }).from(SyncSessionTable)
if (!entity[0]) return undefined
const { tokens, accessTokenCreated, refreshTokenCreated } = entity[0]
if (!tokens) return undefined
@@ -104,7 +31,7 @@ const auth = {
async init_auth_session(): Promise<string> {
const state = randomUUID()
- await db.insert(syncSession).values({
+ await db.insert(SyncSessionTable).values({
authzState: state
})
@@ -118,19 +45,25 @@ const auth = {
},
async get_access_token() {
const result = await db.select({
- tokens: syncSession.tokens,
- refreshTokenCreated: syncSession.refreshTokenCreated,
- accessTokenCreated: syncSession.accessTokenCreated
- }).from(syncSession)
+ tokens: SyncSessionTable.tokens,
+ refreshTokenCreated: SyncSessionTable.refreshTokenCreated,
+ accessTokenCreated: SyncSessionTable.accessTokenCreated
+ }).from(SyncSessionTable)
+
if (!result[0]) return undefined
+
const { tokens, accessTokenCreated, refreshTokenCreated } = result[0]
+
if (!tokens) return undefined
+
const nowInstant = Temporal.Now.instant()
+
const accessTokenExpiredInstant = Temporal.Instant.fromEpochMilliseconds(Number(accessTokenCreated)).add({ seconds: tokens.expires_in })
if (Temporal.Instant.compare(nowInstant, accessTokenExpiredInstant) >= 0) {
const refreshedTokens = await this.refresh_token()
if (refreshedTokens) return refreshedTokens.access_token
}
+
const refreshTokenExpiredInstant = Temporal.Instant.fromEpochMilliseconds(Number(refreshTokenCreated)).add({ seconds: tokens.refresh_token_expires_in })
if (Temporal.Instant.compare(nowInstant, refreshTokenExpiredInstant) >= 0) {
return undefined
@@ -141,9 +74,9 @@ const auth = {
async refresh_token(): Promise<Sb1Tokens | null> {
console.log("Refreshing tokens")
const entity = await db.select({
- tokens: syncSession.tokens,
- id: syncSession.id
- }).from(syncSession)
+ tokens: SyncSessionTable.tokens,
+ id: SyncSessionTable.id
+ }).from(SyncSessionTable)
const { tokens: currentTokens, id } = entity[0]
@@ -167,7 +100,7 @@ const auth = {
const tokens = await res.json() as Sb1Tokens
const epoch = Temporal.Now.instant().epochMilliseconds
if (res.ok) {
- await db.update(syncSession).set({ tokens, accessTokenCreated: epoch.toString(), refreshTokenCreated: epoch.toString() }).where(eq(syncSession.id, id))
+ await db.update(SyncSessionTable).set({ tokens, accessTokenCreated: epoch.toString(), refreshTokenCreated: epoch.toString() }).where(eq(SyncSessionTable.id, id))
await add_session_log(id, "REFRESH_SB1_TOKEN", "Done")
return tokens
} else {
@@ -195,25 +128,60 @@ const data = {
},
async get_transactions(accountKey: string, delta?: Temporal.Instant) {
const token = await auth.get_access_token()
-
if (!token) return undefined
+
const params = new URLSearchParams({
"accountKey": accountKey,
});
- if (delta) params.append("fromDate", formatInstant(delta, "yyyy-MM-dd"))
+
+ if (delta) {
+ params.append("fromDate", formatInstant(delta, "yyyy-MM-dd"))
+ params.append("Transaction source", "ALL")
+ }
+
const response = await fetch("https://api.sparebank1.no/personal/banking/transactions?" + params, {
headers: {
Authorization: `Bearer ${token}`,
+ Accept: "application/vnd.sparebank1.v1+json;charset=utf-8"
},
});
+
const json = await response.json()
- return json["transactions"] as Sb1Transaction[];
+ return json["transactions"] as Sb1Transaction[]
}
}
-export default { auth, data }
+export default { auth, data, init }
-export function formatInstant(
+let importInterval: NodeJS.Timeout
+let inited = false
+
+async function init() {
+ if (inited) return
+ if (importInterval) clearInterval(importInterval)
+ await importTransactions()
+ importInterval = setInterval(async () => importTransactions, 60 * 60 * 1000)
+ inited = true
+}
+
+async function importTransactions() {
+ console.log("Creating sb1 transactions indb")
+ const accounts = await data.get_accounts()
+ for (const account of accounts?.accounts ?? []) {
+ const transactions = await data.get_transactions(account.key)
+ for (const transaction of transactions ?? []) {
+ // if (await transactionExists(transaction.id)) continue
+ await db.insert(TransactionsTable).values({ transaction })
+ }
+ }
+}
+
+async function transactionExists(transactionId: string) {
+ const query = sql`select data ->>'id' as id from ${TransactionsTable} where id=${transactionId}`;
+ return (await db.execute(query)).rowCount ?? 0 > 0
+}
+
+function formatInstant(
instant: Temporal.Instant,
format: string,
timeZone: string = "UTC"
@@ -236,4 +204,4 @@ export function formatInstant(
/yyyy|MM|dd|HH|mm|ss/g,
(token) => replacements[token]
);
-}
+} \ No newline at end of file
diff --git a/app/src/lib/server/session-log.ts b/app/src/lib/server/session-log.ts
index 1195621..54498f3 100644
--- a/app/src/lib/server/session-log.ts
+++ b/app/src/lib/server/session-log.ts
@@ -1,11 +1,11 @@
import { Temporal } from "temporal-polyfill"
import { db } from "./db"
-import { syncLog } from "./db/schema"
+import { SyncLogTable } from "./db/schema"
export type SessionLogType = "CREATED" | "SYNC_START" | "REFRESH_SB1_TOKEN"
export async function add_session_log(id: string, type: SessionLogType, msg: string) {
- db.insert(syncLog).values({
+ db.insert(SyncLogTable).values({
dateTime: String(Temporal.Now.instant().epochMilliseconds),
sessionId: id,
type: type,
generated by cgit v1.3 (git 2.53.0) at 2026-03-07 11:46:36 +0100