commodities

Description

Currency/commodity definitions. Each commodity has a unique code and an exponent that defines the precision of amounts (e.g. -2 for pence). Accounts reference commodities via foreign key.

Table Definition

CREATE TABLE commodities (
    id TEXT PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    exponent INTEGER NOT NULL DEFAULT -2,
    datetime TEXT,
    created_at TEXT DEFAULT (datetime('now'))
)

Columns

Name	Type	Default	Nullable	Children	Comment
code	TEXT		false	accounts	Unique commodity code (e.g. GBP, USD, BTC)
created_at	TEXT	datetime('now')	true		Timestamp when the commodity was created
datetime	TEXT		true		Optional date associated with the commodity definition
exponent	INTEGER	-2	false		Decimal exponent for amount precision (-2 = pence, -8 = satoshi)
id	TEXT		true	commodity_metadata	UUID primary key

Constraints

Name	Type	Definition
id	PRIMARY KEY	PRIMARY KEY (id)
sqlite_autoindex_commodities_1	PRIMARY KEY	PRIMARY KEY (id)
sqlite_autoindex_commodities_2	UNIQUE	UNIQUE (code)

Indexes

Name	Definition
sqlite_autoindex_commodities_1	PRIMARY KEY (id)
sqlite_autoindex_commodities_2	UNIQUE (code)

Relations

erDiagram

"accounts" }o--|| "commodities" : "FOREIGN KEY (commodity) REFERENCES commodities (code) ON UPDATE NO ACTION ON DELETE NO ACTION MATCH NONE"
"accounts" }o--|| "commodities" : "accounts.commodity -> commodities.code"
"commodity_metadata" }o--|| "commodities" : "FOREIGN KEY (commodity_id) REFERENCES commodities (id) ON UPDATE NO ACTION ON DELETE NO ACTION MATCH NONE"
"commodity_metadata" }o--|| "commodities" : "commodity_metadata.commodity_id -> commodities.id"

"commodities" {
  TEXT code "Unique commodity code (e.g. GBP, USD, BTC)"
  TEXT created_at "Timestamp when the commodity was created"
  TEXT datetime "Optional date associated with the commodity definition"
  INTEGER exponent "Decimal exponent for amount precision (-2 = pence, -8 = satoshi)"
  TEXT id PK "UUID primary key"
}
"accounts" {
  TEXT account_id "Specific account identifier within the product"
  TEXT account_type "One of: Asset, Liability, Equity, Income, Expense"
  TEXT address "Sub-address within the account (e.g. branch). 'Pending' marks pending accounts"
  TEXT commodity FK "Commodity code (FK to commodities.code)"
  TEXT created_at "Timestamp when the account was created"
  TEXT customer_id FK "Optional owning customer (FK to customers.id). A customer may have many accounts"
  TEXT full_path "Hierarchical account path, e.g. Asset:Bank:Current:Main"
  TEXT gross_interest_rate "Gross annual interest rate as a decimal (0.045 = 4.5%)"
  TEXT id PK "UUID primary key"
  INTEGER interest_accumulator "Sub-unit fractions at extended precision (method-dependent)"
  TEXT interest_method "Interest calculation method (e.g. simple_daily)"
  INTEGER is_pending "True if this is a pending/suspense account"
  TEXT opened_at "When the account was opened"
  TEXT product "Product category within the account type"
}
"commodity_metadata" {
  TEXT commodity_id FK "FK to commodities.id"
  TEXT id PK "UUID primary key"
  TEXT key "Metadata key"
  TEXT value "Metadata value"
}

Generated by tbls