Database structure and entities rules

Transactions

The transactions table stores all financial transaction data.

Schema Definition

Column Name	Type	Description
id	bigserial	Unique primary key for the transaction.
source_amount	numeric	Amount from the source account (in source_currency).
source_currency	text	Currency code (e.g., USD, EUR) of the source amount. Required (can be empty string).
destination_amount	numeric	Amount to the destination account (in destination_currency). In case of Withdrawal, can foreign_amount
destination_currency	text	Currency code of the destination amount. Required (can be empty string).
source_account_id	integer	Reference to the source account ID (nullable).
destination_account_id	integer	Reference to the destination account ID (nullable).
tag_ids	integer[]	Optional array of tag IDs associated with the transaction.
created_at	timestamp	Time the transaction was created in the system. Defaults to now().
updated_at	timestamp	Last time the transaction was updated. Defaults to now().
notes	text	Free-form notes or memo attached to the transaction.
extra	jsonb	Optional key-value metadata. Defaults to empty object ({}).
transaction_date_time	timestamp	Actual timestamp when the transaction occurred.
transaction_date_only	date	Transaction date (without time) for grouping/aggregation.
transaction_type	integer	Internal type/category (e.g., transfer, expense, income).
flags	bigint	Bit flags used for marking transaction status, e.g., synced, reviewed.
voided_by_transaction_id	bigint	Optional reference to another transaction that voids this one.
title	text	Short description or title of the transaction.
reference_number	text	External reference number (e.g., bank transaction ID).
internal_reference_number	text	Internal system reference number (e.g., reconciliation ID).
destination_amount_in_base_currency	numeric	Normalized destination amount in base currency.
source_amount_in_base_currency	numeric	Normalized source amount in base currency.

Types

const (
	TransactionType_TRANSACTION_TYPE_UNSPECIFIED               TransactionType = 0
	TransactionType_TRANSACTION_TYPE_TRANSFER_BETWEEN_ACCOUNTS TransactionType = 1
	TransactionType_TRANSACTION_TYPE_DEPOSIT                   TransactionType = 2
	TransactionType_TRANSACTION_TYPE_WITHDRAWAL                TransactionType = 3
	TransactionType_TRANSACTION_TYPE_RECONCILIATION            TransactionType = 5
)

Deposit

• source_currency - always empty string
• source_amount - always null
• source_account_id - always null
• source_amount_in_base_currency - always null

---

• destination_currency - always set
• destination_amount - always >= 0
• destination_account_id - always set
• destination_amount_in_base_currency - always set according to currency rate (>= 0)

Withdrawal

• source_currency - always set
• source_amount - always <= 0 (for example -121.52)
• source_account_id - always set
• source_amount_in_base_currency - always set according to currency rate (<=0)

---

• destination_currency - optional, can be set if "foreign currency" is set
• destination_amount - optional, can be set if "foreign currency" is set, <= 0
• destination_account_id - always null
• destination_amount_in_base_currency - optional, can be set if "foreign currency" is set, <= 0

Transfer between accounts

• source_currency - always set
• source_amount - always <= 0 (for example -121.52)
• source_account_id - always set
• source_amount_in_base_currency - always set according to currency rate (<=0)

---

• destination_currency - always set
• destination_amount - always >= 0
• destination_account_id - always set
• destination_amount_in_base_currency - always set according to currency rate (>= 0)

DailyStat

The daily_stat table stores account balance at a specific point in time (daily) in the account currency.

Schema Definition

Field	Type	DB Tag	Description
account_id	int32	primaryKey	Unique identifier for the account. Combined with Date as the composite primary key.
date	date	primaryKey	The date the stat is recorded for. Part of the composite primary key.
amount	decimal	(none)	Aggregated amount/value for that account on the given day. Can be positive or negative. value is in account currency