Run a single Postgres SELECT against the user's data. The database automatically scopes results to the current user - do NOT add WHERE user_id clauses. For net worth, budget, or asset valuation use the dedicated tools instead. Provide a one-line rationale explaining the query.
IMPORTANT - money units: columns the schema below marks as minor units (the marker reads roughly "[minor units - divide by 100]") are stored as INTEGER minor units - the real amount x 100, for ALL currencies including IDR with no decimal places (e.g. Rp 2,500,000 is stored as 250000000). Divide ONLY those marked columns by 100.0 in your SELECT projection to get real amounts, e.g. SELECT SUM(amount) / 100.0 AS total. Do NOT divide columns that are already in major units - in particular the v_chat_transaction view's amount_major and signed_amount are already real major-unit amounts, so use them directly (they are the easiest correct way to sum spend/income). Never multiply by 100.
asset
purchase_price is the per-unit purchase price in purchase_currency
rows with deleted_at IS NOT NULL are soft-deleted; exclude them
for per-asset P&L questions the getAssets tool already returns cost basis, P&L, and P&L % in the user's base currency; prefer it over hand-rolled SQL
id: string
user_id: string
account_id: string (nullable)
type: string [enum: crypto | stock | real_estate | vehicle | commodity | bond | other | collectible]
symbol: string (nullable)
display_symbol: string (nullable)
quantity: string
unit: string (nullable)
purchase_price: number (nullable) [minor units - divide by 100]
purchase_currency: string (nullable)
valuation_mode: string [enum: auto | manual]
created_at: date
updated_at: date
deleted_at: date (nullable)
asset_split
asset_transaction
per-trade buy/sell record for an asset; join transaction on transaction_id for date, type (buy | sell), currency, and soft-delete status (exclude deleted_at IS NOT NULL)
price_per_unit is in the joined transaction's currency
an asset conversion (swap of one asset for another) appears as a paired sell + buy on the same date whose transactions have account_id IS NULL; they move no cash (v_chat_transaction reports them as neutral with signed_amount 0)
category
collectible_acquisition
copies of a card added without a trade (with what was paid, not paid from an account): one row per time copies were added, dated acquired_on; join asset on asset_id (exclude soft-deleted assets)
price_per_unit is what one copy cost, in currency; both are null when the user did not say what they paid
a card's cost comes from these rows plus its buy trades in asset_transaction, never from asset.purchase_price; the getAssets tool already combines them into costBasis and P&L
copies from a booster box or packs opened together have opening_id set: the box's price is split over them by market value (opening_weight), so price_per_unit is their share
id: string
asset_id: string
quantity: number
price_per_unit: number (nullable) [minor units - divide by 100]
currency: string (nullable)
acquired_on: string
opening_id: string (nullable)
opening_weight: number (nullable)
share_fixed: boolean
created_at: date
collectible_disposal
copies of a card that left the collection without a sale: reason 'removed' (taken out by the user) or 'traded' (given in a trade); join asset on asset_id (exclude soft-deleted assets)
they leave at their average cost, so they realise no profit or loss; a sale is a sell trade in asset_transaction instead
id: string
asset_id: string
quantity: number
disposed_on: string
reason: string
created_at: date
collectible_holding
one row per trading card position: the asset (type = 'collectible') it belongs to, the exact print (variant_id) and how the card is held
join asset on asset_id for quantity and deleted_at (exclude soft-deleted assets); join collectible_item_cache on variant_id for the card's name, set, number and language
grading is 'raw' (then condition is nm | lp | mp | hp | dmg) or 'graded' (then grader is psa | bgs | cgc | sgc | tag | ace | ars | other and grade runs 1 to 10)
a card's value: when asset.valuation_mode = 'auto' it is the market price, the price row with type 'collectible' and symbol = asset.symbol (collectible_price_meta on the same symbol says how it was built); when 'manual' it is the user's own latest valuation row, or, when source_pin is set, the price of that one pinned source, written into valuation each time it changes
for value and P&L questions the getAssets tool (type 'collectible') already returns them in the user's base currency; prefer it
asset_id: string
variant_id: string
grading: string
condition: string (nullable)
grader: string (nullable)
grade: string (nullable)
grade_label: string (nullable)
source_pin: string (nullable)
notes: string (nullable)
created_at: date
updated_at: date
collectible_item_cache
one row per card print (global, no user data): game (pokemon, the only game Hundo supports for now), language (en | ja | id ...), set_code, set_name, card_number, card_name
card_name is as printed (Japanese cards in Japanese); card_name_en holds the English name when there is one
variant_id: string
game: string
language: string
set_code: string
set_name: string
card_number: string
card_name: string
card_name_en: string (nullable)
finish: string
finish_label: string (nullable)
rarity: string (nullable)
image_url: string (nullable)
has_official_image: boolean
is_secret: boolean
status: string
synced_at: date
collectible_price_meta
one row per card market price (global, no user data), keyed by symbol = asset.symbol for a card ('ctg:<variant_id>:'); the price itself is in price (type 'collectible', same symbol), in the currency the source quotes
basis: market (one card market's recent sales price alone: TCGplayer in USD first, else Cardmarket in EUR), single_seller (1 shop or eBay seller), thin (the lower of 2), median (trimmed median of 3 or more); source_count: how many sources the price is built from; observed_on: the day the newest evidence was seen
stale = true or status <> 'priced' means the price is old: the source marked it stale, or no source prices the card any more and the last price is kept; estimate = true means a played copy priced from near mint times factor
sources is a JSON array of {seller, platform, kind ('ask' = a shop's or an eBay seller's asking price, 'sold' = a real sale), price, currency, observedOn, counted}; price there is in minor units - divide by 100; counted = false means the source is listed but the price is not built from it (a market's sale beats the shops' asks); a missing counted means true
symbol: string
variant_id: string
condition_key: string
status: string
basis: string (nullable)
source_count: number
observed_on: string (nullable)
stale: boolean
estimate: boolean
derived_from: string (nullable)
factor: number (nullable)
sources: json
computed_at: date (nullable)
synced_at: date
history_synced_at: date (nullable)
counterparty
envelope
this table represents what the user calls a 'budget' - the DB table is named envelope for historical reasons, but always say 'budget' in user-facing replies
budgets are buckets that group categories - a user mentioning a budget by name (e.g., 'dating', 'vacation') almost always means a row here, not a category
v_chat_transaction.envelope_name resolves the (transaction → budget) link for you; prefer that over joining envelope_category yourself
id: string
user_id: string
name: string
budgeted_amount: number [minor units - divide by 100]
currency: string
period: string [enum: weekly | monthly]
tracking_mode: string [enum: category | manual]
include_investments: boolean
category_id: string (nullable)
created_at: date
updated_at: date
deleted_at: date (nullable)
envelope_category
junction table linking categories to budgets; v_chat_transaction.envelope_name already follows this path, so you rarely need to query envelope_category directly
id: string
envelope_id: string
category_id: string
exchange_rate
stored one direction per pair (typically from_currency = USD); for the reverse direction, use 1/rate from the existing row rather than expecting an explicit inverse row
use the latest row per (from_currency, to_currency) pair (order by fetched_at DESC, limit 1)
id: string
from_currency: string
to_currency: string
rate: string
fetched_at: date
checked_at: date
financial_account
balance_cache is the live balance in minor units
balance_cache = initial_balance + the sum of the account's transactions, so initial_balance is the balance before the first transaction, not always the balance the user typed when adding the account
rows with deleted_at IS NOT NULL are soft-deleted; exclude them
id: string
user_id: string
name: string
type: string [enum: checking | savings | credit_card | cash | investment | crypto | loan | mortgage | property | vehicle | receivable | payable]
currency: string
institution: string (nullable)
icon: string (nullable)
is_liability: boolean
initial_balance: number [minor units - divide by 100]
balance_cache: number [minor units - divide by 100]
credit_limit: number (nullable) [minor units - divide by 100]
is_active: boolean
is_virtual: boolean
counterparty_id: string (nullable)
created_at: date
updated_at: date
deleted_at: date (nullable)
net_worth_snapshot
id: string
user_id: string
total_assets: number [minor units - divide by 100]
total_liabilities: number [minor units - divide by 100]
net_worth: number [minor units - divide by 100]
currency: string
breakdown_json: json (nullable)
snapshot_date: string
created_at: date
price
cached market price per unit of the asset, keyed by (type, symbol); join asset on both columns
use the latest row per (type, symbol) (order by fetched_at DESC, limit 1)
for commodities the price is per troy ounce regardless of asset.unit; convert before comparing to per-unit purchase prices
transaction
amount is always non-negative; direction lives in type
for spend/income totals, prefer the v_chat_transaction view
rows with deleted_at IS NOT NULL are soft-deleted; exclude them
in_opening_balance = true marks a payment the user logged as already inside the balance they typed when adding the account; it is real spending on its date, but it changed initial_balance, not balance_cache
id: string
user_id: string
account_id: string (nullable)
type: string [enum: income | expense | transfer | buy | sell]
amount: number [minor units - divide by 100]
currency: string
exchange_rate: string (nullable)
date: string
description: string (nullable)
category_id: string (nullable)
envelope_id: string (nullable)
notes: string (nullable)
receipt_image_url: string (nullable)
in_opening_balance: boolean
created_at: date
updated_at: date
deleted_at: date (nullable)
valuation
one row per asset per day; latest row is the current value
v_chat_transaction (view)
Presentation view over transaction. Soft-deleted rows already excluded.
Prefer this over the raw transaction table for any spend/income question.
id: string
user_id: string
date: date
type: string [enum: income | expense | transfer | buy | sell]
description: string (nullable)
currency: string
amount_major: numeric (always >= 0; major units, e.g. dollars not cents)
signed_amount: numeric (negative for expense and buy; positive for income and sell; zero for transfer and for cashless asset-conversion legs)
direction: string [enum: outflow | inflow | neutral]
account_id: string (nullable; null on the cashless legs of an asset conversion, which move no cash)
category_id: string (nullable)
category_name: string (nullable, joined from category)
envelope_id: string (nullable; direct budget assignment, often null)
envelope_name: string (nullable; the user's budget label, resolved via direct envelope_id OR via the envelope_category junction on category_id - match this when the user names a budget like "Dating")
Inputs: sql (required), rationale (required).
