Part of the Sanchayam series.

The Problem with Per-Type Tables

The obvious design for a multi-asset tracker is a table per asset type: one for stocks, one for mutual funds, one for real estate. That approach breaks down when you need cross-asset aggregation. Computing total portfolio value means querying five different tables and unioning them. Adding a new asset type means new tables, new routes, new UI components.

Sanchayam uses a single assets catalog and a single holdings table. Behavior differences between asset types are encoded in schema flags, not in separate tables.

The Assets Catalog

The assets table is admin-managed. Users cannot create assets - they create holdings that reference catalog entries:

CREATE TABLE assets (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name               VARCHAR NOT NULL UNIQUE,
  currency           VARCHAR(10) NOT NULL REFERENCES currencies(code),
  unit_type          asset_unit_type NOT NULL,
  update_mode        asset_update_mode NOT NULL,
  update_frequency   asset_update_frequency NOT NULL DEFAULT 'as_required',
  cost_basis_mode    asset_cost_basis_mode NOT NULL,
  data_type          VARCHAR,
  symbol             VARCHAR,
  locked_unit_cost   NUMERIC,
  locked_unit_quantity NUMERIC,
  is_active          BOOLEAN NOT NULL DEFAULT true,
  ...
);

Five flags drive all behavioral differences: unit_type, update_mode, cost_basis_mode, locked_unit_cost, and locked_unit_quantity.

unit_type

CREATE TYPE asset_unit_type AS ENUM ('whole', 'fractional', 'single');

• whole - integer quantities only. Used for assets you cannot buy fractionally (most Indian equities are whole units).
• fractional - decimal quantities. Used for mutual funds (NAV units), crypto, and US equities where fractional shares are possible.
• single - quantity is always 1. Used for real estate (one property) and bank accounts (one account). The holding is the asset.

single assets skip the lot entry UI entirely. There is nothing to buy in units - the value is entered directly.

update_mode

CREATE TYPE asset_update_mode AS ENUM ('manual', 'api');

• api - price is fetched automatically from a data provider. The data_type and symbol columns tell the price feed which provider to use and with what identifier.
• manual - price is entered by the user. Manual-mode assets use the holding_values table instead of asset_price_history.

An Indian equity (equity_india) is api mode with symbol = 'RELIANCE:NSE'. A property is manual with no symbol.

cost_basis_mode

CREATE TYPE asset_cost_basis_mode AS ENUM ('fixed', 'floating');

• fixed - the asset has a definable cost basis. Buy/sell transactions are tracked as lots. FIFO P&L is computed. XIRR is computed from the lot history. Used for all tradable assets.
• floating - no cost basis makes sense. The asset’s value is whatever you enter. Used for real estate, where the “cost” is not the tracked quantity - you just update the current market value periodically.

floating assets skip FIFO and XIRR entirely. Their contribution to portfolio value comes from the latest entry in holding_values.

locked_unit_cost and locked_unit_quantity

These two columns handle a specific case: assets where cost per unit or quantity per lot are fixed by definition and should not be shown in the UI as inputs.

locked_unit_cost: set to 1 for bank_balance assets. A bank balance denominated in INR always has a cost of 1 INR per unit - one rupee is one rupee. When the user records a deposit, they enter an amount (say 50,000). The backend stores this as a lot with quantity 50,000 and price_per_unit = 1. The current balance is the sum of all deposits minus withdrawals. There is no price feed involved.

locked_unit_quantity: set to 1 for assets that are always bought/sold as a single unit at a time - useful for some structured products or bonds.

When a field is locked, the frontend hides that input and the backend supplies the fixed value instead of reading it from the request.

Holdings and Lots

Users create holdings that reference catalog assets:

CREATE TABLE holdings (
  id       UUID PRIMARY KEY,
  user_id  UUID NOT NULL REFERENCES users(id),
  asset_id UUID NOT NULL REFERENCES assets(id),
  status   holding_status NOT NULL DEFAULT 'active',
  tags     JSONB NOT NULL DEFAULT '[]',
  UNIQUE (user_id, asset_id)
);

One user cannot hold the same asset twice - the holding is the container. Buy/sell transactions are lots under the holding:

CREATE TABLE lots (
  id                UUID PRIMARY KEY,
  holding_id        UUID NOT NULL REFERENCES holdings(id),
  transaction_type  lot_transaction_type NOT NULL,  -- 'buy' | 'sell'
  quantity          NUMERIC(38,8) NOT NULL,
  remaining_quantity NUMERIC(38,8),
  price_per_unit    NUMERIC(38,0) NOT NULL,         -- stored in minor units
  transaction_date  DATE NOT NULL,
  ...
);

remaining_quantity is only set on buy lots and tracks how much of that lot has been consumed by subsequent FIFO sells. price_per_unit is in minor currency units (paise for INR, cents for USD) to avoid floating point.

How the Flags Combine

The behavioral matrix across asset types:

A bank_balance holding with locked_unit_cost = 1 in INR means: the user records amounts, not units and prices. The holding value is the running lot balance. No price feed. No FIFO P&L (cost and value are always the same thing). But it still participates in portfolio snapshots and XIRR at the portfolio level via its contribution to total value.

Next: Auth: OTP Reset, Invite Signup, and Token Rotation