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
- id: string
- asset_id: string
- effective_date: string
- ratio_from: number
- ratio_to: number
- source: string [enum: manual | yahoo]
- status: string [enum: pending | applied | dismissed]
- cache_applied: boolean
- created_at: date
- updated_at: date
### 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)
- id: string
- transaction_id: string
- asset_id: string
- quantity: string
- price_per_unit: number [minor units - divide by 100]
- created_at: date
### category
- id: string
- user_id: string
- name: string
- icon: string (nullable)
- parent_id: string (nullable)
- created_at: date
- updated_at: date
- deleted_at: date (nullable)
### 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>:<condition key>'); 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
- id: string
- user_id: string
- name: string
- notes: string (nullable)
- created_at: date
- updated_at: date
- deleted_at: date (nullable)
### 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
- id: string
- type: string [enum: crypto | stock | real_estate | vehicle | commodity | bond | other | collectible]
- symbol: string
- price: number [minor units - divide by 100]
- currency: string
- fetched_at: date
### 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
- id: string
- asset_id: string
- value: number [minor units - divide by 100]
- currency: string
- fetched_at: date
### 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")