Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/soker90/finper/llms.txt

Use this file to discover all available pages before exploring further.

Finper’s entire persistence layer is a single SQLite file managed by Drizzle ORM. There is no external database server to configure or maintain. The schema and the connection factory live in the @soker90/finper-db workspace package, which is built and then imported by the API at runtime.

​
Drizzle ORM and SQLite

The @soker90/finper-db package exports a createDb(file) factory that opens a better-sqlite3 connection and returns a typed Drizzle database instance. The API creates this connection in src/db.ts: 
// packages/api/src/db.ts
import { createDb, type DB } from '@soker90/finper-db'
import config from './config'

export const db: DB = createDb(config.database.file)
Automatic migrations — when the API starts, server.ts calls Drizzle’s migrate() pointing at the packages/db/drizzle/ folder: 
// packages/api/src/server.ts (sqlite method)
migrate(sqliteDb as any, {
  migrationsFolder: path.resolve(__dirname, '../../db/drizzle')
})
console.log('[finper-api] Drizzle SQLite migrations applied')
This means any pending migration files are applied before the first request is handled. You never need to run a separate migration command in production.

​
Multi-tenancy

Every table except users contains a user column (type TEXT, references users.username). Every query in the repository layer filters by this column so that each user’s data is completely isolated. A user can never read or modify another user’s records.Example from the accounts schema:
export const accounts = sqliteTable('accounts', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  bank: text('bank').notNull(),
  balance: real('balance').notNull().default(0),
  isActive: integer('is_active', { mode: 'boolean' }).default(true),
  user: text('user').notNull().references(() => users.username),
})
authMiddleware sets req.user = username (the string, not a numeric ID) and every controller passes it to the service, which passes it to the repository as the user filter.

​
Entity overview

EntityTable(s)Key FieldsRelations
Userusersid, username, password, isActive, createdAtRoot anchor for all other data
Accountaccountsid, name, bank, balance, isActive, userReferenced by Transaction, Loan, Subscription, Supply
Categorycategoriesid, name, type, parentId, budgetRuleClass, userSelf-referential parent; referenced by Transaction, Budget, Loan
Transactiontransactionsid, date, categoryId, amount, type, accountId, note, storeId, subscriptionId, tags, userBelongs to Account, Category, Store, Subscription
Storestoresid, name, userReferenced by Transaction
Budgetbudgetsid, year, month, amount, categoryId, userUnique index on (user, month, year, categoryId)
Debtdebtsid, from, date, amount, concept, type, userStandalone
Goalgoalsid, name, targetAmount, currentAmount, deadline, color, icon, userStandalone
Loanloansid, name, initialAmount, pendingAmount, interestRate, startDate, monthlyPayment, accountId, categoryId, userHas many LoanPayment, LoanEvent
LoanPaymentloan_paymentsid, loanId, date, amount, interest, principal, accumulatedPrincipal, pendingCapital, type, userBelongs to Loan
LoanEventloan_eventsid, loanId, date, newRate, newPayment, userBelongs to Loan
Subscriptionsubscriptionsid, name, amount, currency, cycle, nextPaymentDate, categoryId, accountId, logoUrl, userReferenced by Transaction
SubscriptionCandidatesubscription_candidatesid, transactionId, subscriptionIds, createdAt, userStandalone candidate detection record
Stockstocksid, platform, ticker, name, shares, price, type, date, userStandalone
Pensionpensionsid, date, employeeAmount, employeeUnits, companyAmount, companyUnits, value, userStandalone
Propertypropertiesid, name, userHas many Supply
Supplysuppliesid, name, type, propertyId, power/price fields, userBelongs to Property; has many SupplyReading
SupplyReadingsupply_readingsid, supplyId, startDate, endDate, amount, consumption fields, userBelongs to Supply

​
Schema conventions

Dates stored as Number (Unix milliseconds) All date/time columns use integer with Drizzle’s default numeric storage (Unix ms timestamp). The serializer layer converts these to JavaScript Date objects or ISO strings when building API responses. Exceptions:
  • goals.deadline — stored as a plain integer (may be null for goals with no deadline).
  • subscription_candidates.createdAt — stored as a plain integer Unix ms timestamp.
Amounts stored as REAL (IEEE 754 double) Monetary values are stored as SQLite REAL (64-bit float). To prevent floating-point drift, any calculated amount must be rounded with the roundMoney helper before being written back to the database or sent to the client. Values read directly from the database without arithmetic do not need rounding. 
// Calculated value — round before use
const portfolioValue = roundMoney(stock.shares * stock.price)

// Direct read — no rounding needed
const balance = account.balance
IDs serialized as strings Primary keys are stored as text in SQLite and are always serialized as strings in API responses. Enum values Domain enumerations are stored as plain strings and defined as constants in packages/db/src/constants.ts:
ConstantValues
TRANSACTIONexpense | income | not_computable
LOAN_PAYMENTordinary | extraordinary
SUPPLY_TYPEelectricity | water | gas | other
Stock transaction types (buy | sell | dividend) and debt direction types (from | to) are documented in their respective schema files.

​
Backing up the database

The SQLite file path is controlled by the DATABASE_FILE environment variable (default: ./finper-dev.db). A backup is a simple file copy — no special tooling is required: 
# Copy the live database file to a timestamped backup
cp /path/to/finper.db "/path/to/backups/finper-$(date +%Y%m%d-%H%M%S).db"
For Docker deployments, docker-compose.yml maps the database to /home/node/app/data/finper.db inside a named volume. Back up the file from the host volume mount point: 
# Find the volume mount path
docker inspect finper-api | jq '.[].Mounts'

# Then copy from the host path shown in "Source"
cp /var/lib/docker/volumes/finper_data/_data/finper.db ./finper-backup.db
Tests use an in-memory SQLite database. When NODE_ENV=test, createDb is called with an in-memory path so every Jest test suite starts from a completely clean database. There is no risk of test runs corrupting your development or production data file.

Build docs developers (and LLMs) love

Get started for freeTalk to us