Schema

The Schema tab in Insights lists the tables and columns you can query. Insights reads a data lake: a stored copy of your ledger with four tables.

ledgers

ledger_id PK

name

created_at, meta_data

A group of balances

identity

identity_id PK

identity_type, category

name, email, phone

address fields

People and organizations

1:N ledger_id1:N identity_id

balances

balance_id PK

ledger_id → ledgers

identity_id → identity

balance, credit, debit

currency, indicator, inflight

Stores of value. The wallets.

source / dest

transactions

transaction_id PK

source → balances

destination → balances

amount, precise_amount, precision

currency, status, reference

Source is money out. Destination is money in.

Balances belong to a ledger and optionally an identity. Each lake transaction row has a source wallet and a destination wallet.

Table	What it is	Primary key
`transactions`	Money movements between wallets	`transaction_id`
`balances`	Stores of value	`balance_id`
`ledgers`	Balance groups	`ledger_id`
`identity`	People and orgs	`identity_id`

How to join them

When a report needs columns from more than one table, join those tables on a shared ID. For example, a payment stores wallet IDs, but the owner’s name is on identity. A join brings them into one result. Every lake transaction row stores one source wallet and one destination wallet. Source is where money leaves. Destination is where money arrives. For payments that split across many wallets, see Multiple sources and Multiple destinations. Follow these steps to show each applied payment with the destination wallet and the owner’s name. Collapse balances and identity to one row per ID before you join, so multi-day copies do not multiply payment rows. See How the lake works.

Start from payments

Read from transactions and pick the payment fields you need.

SELECT
  t.reference,
  t.currency
FROM transactions t
WHERE t.status = 'APPLIED'

Collapse wallets and owners

Build one row per balance_id and one row per identity_id. Use ANY_VALUE for display fields. Do not order by created_at to pick a latest snapshot.

WITH balances_one AS (
  SELECT
    balance_id,
    ANY_VALUE(currency) AS currency,
    ANY_VALUE(identity_id) AS identity_id
  FROM balances
  GROUP BY balance_id
),
identity_one AS (
  SELECT
    identity_id,
    ANY_VALUE(first_name) AS first_name,
    ANY_VALUE(last_name) AS last_name
  FROM identity
  GROUP BY identity_id
)

Join destination wallet and owner

Connect destination to the collapsed wallet, then the wallet to the collapsed owner.

FROM transactions t
JOIN balances_one b ON t.destination = b.balance_id
JOIN identity_one i ON b.identity_id = i.identity_id

Put it together

Full query:

WITH balances_one AS (
  SELECT
    balance_id,
    ANY_VALUE(currency) AS currency,
    ANY_VALUE(identity_id) AS identity_id
  FROM balances
  GROUP BY balance_id
),
identity_one AS (
  SELECT
    identity_id,
    ANY_VALUE(first_name) AS first_name,
    ANY_VALUE(last_name) AS last_name
  FROM identity
  GROUP BY identity_id
)
SELECT
  t.reference,
  t.currency,
  b.balance_id,
  i.first_name,
  i.last_name
FROM transactions t
JOIN balances_one b ON t.destination = b.balance_id
JOIN identity_one i ON b.identity_id = i.identity_id
WHERE t.status = 'APPLIED'
LIMIT 100

This returns each applied payment with the destination wallet and the owner’s name, without duplicating payments across daily copies.

Use these connections when you need more than an ID:

Use a join from transactions.source to a one-row-per-id balances set to see the wallet a payment left.
Use a join from transactions.destination to a one-row-per-id balances set to see the wallet a payment entered.
Use a join from balances.ledger_id to a one-row-per-id ledgers set to see which ledger a wallet belongs to.
Use a join from balances.identity_id to a one-row-per-id identity set to see the owner of a wallet.

Columns you can select

Use the tabs below to see what each table stores and when to select each column. Each tab is one lake table. When a report needs columns from more than one table, join on a shared ID. Collapse repeating entity tables to one row per ID first. For example, join transactions.destination to a one-row-per-id balances set, then to a one-row-per-id identity set, to show a payment with the receiving wallet and owner.

transactions
balances
ledgers
identity

Column	What it is	When you need it
`transaction_id`	Unique ID for the payment row	Look up one payment, or join related rows
`reference`	Your external or idempotency ID	Match a payment to your own system
`status`	Lifecycle state (`APPLIED`, `INFLIGHT`, and others)	Filter money that posted vs still held
`source`	Wallet ID money left	Join to the sending wallet
`destination`	Wallet ID money entered	Join to the receiving wallet
`precise_amount`	Amount in the smallest unit (cents, satoshi)	Build money totals with `precision`
`precision`	How many smallest units make one whole unit	Divide `precise_amount` into a real amount
`amount`	Human-readable amount Blnk derives	Display a single row; prefer `precise_amount / precision` for totals
`currency`	Currency code	Group or filter by currency
`description`	Optional note on the payment	Search or show context in a listing
`parent_transaction`	ID of a related parent payment	Trace refunds, inflight commit/void children, or child legs
`scheduled_for`	When a scheduled payment should run	Report on scheduled payments
`created_at`	When the row was created	Date filters and daily reports
`meta_data`	Custom JSON you attached	Filter or show your own labels

Column	What it is	When you need it
`balance_id`	Unique wallet ID	Join from `source` or `destination`, or look up one wallet
`ledger_id`	Ledger this wallet belongs to	Join to `ledgers` for the ledger name
`identity_id`	Owner of this wallet	Join to `identity` for the person’s or org’s name
`balance`	Balance amount in smallest units as copied that day (no `precision` column on balances)	Inspect a single day copy. Not a live holding. Do not `SUM` across days or currencies. For current amounts, use the Data API.
`credit_balance`	Credits in smallest units as copied that day	Same caveat as `balance`
`debit_balance`	Debits in smallest units as copied that day	Same caveat as `balance`
`inflight_balance`	Inflight amount in smallest units as copied that day	Same caveat as `balance`. For open holds across days, prefer the inflight template on `transactions`.
`inflight_credit_balance`	Inflight credits in smallest units as copied that day	Same caveat as `balance`
`inflight_debit_balance`	Inflight debits in smallest units as copied that day	Same caveat as `balance`
`currency`	Wallet currency	Group or filter wallets by currency
`indicator`	Wallet indicator / alias when set	Find a wallet by indicator
`created_at`	When the wallet was created	Growth and age reports. Not a lake snapshot or freshness timestamp.
`meta_data`	Custom JSON you attached	Filter or show your own labels

Column	What it is	When you need it
`ledger_id`	Unique ledger ID	Join from `balances.ledger_id`
`name`	Ledger display name	Show which ledger a wallet belongs to
`created_at`	When the ledger was created	Ledger inventory reports
`meta_data`	Custom JSON you attached	Filter or show your own labels

Column	What it is	When you need it
`identity_id`	Unique person or org ID	Join from `balances.identity_id`
`identity_type`	Person or organization	Split people vs orgs in a report
`first_name` / `last_name` / `other_names`	Person name fields	Show who owns a wallet
`organization_name`	Org display name	Show which organization owns a wallet
`email_address` / `phone_number`	Contact fields	Support and ops lookups
`category`	Your category label	Segment identities
`street` / `city` / `state` / `country` / `post_code`	Address fields	Location reports
`gender` / `dob` / `nationality`	Profile fields	Compliance or profile reports when you store them
`created_at`	When the identity was created	Identity growth reports
`meta_data`	Custom JSON you attached	Filter or show your own labels

In the Schema tab (or in a SELECT * result), you may see columns that start with _blnk_ or __lake_. Those are internal lake fields and can change. Select the named columns in the tabs above instead.

Transaction status

Most reports filter on status. Core stores each state as its own row. See Transaction lifecycle. From QUEUED, a transaction can become INFLIGHT, APPLIED, or REJECTED. From INFLIGHT, it can become APPLIED or VOID. APPLIED, VOID, and REJECTED are terminal. An applied transaction does not later become void or rejected.

Transaction lifecycle: QUEUED can become INFLIGHT, APPLIED, or REJECTED. INFLIGHT can become APPLIED or VOID. — APPLIED, VOID, and REJECTED are terminal. Only INFLIGHT is money still on hold.

Status	Meaning	Count it as money moved?
`QUEUED`	Accepted, not yet applied	No
`INFLIGHT`	Funds held, waiting for commit or void	No. Watch open holds.
`APPLIED`	Final. Balances updated.	Yes
`VOID`	Inflight was cancelled. Balances reset.	No
`REJECTED`	Failed validation or processing	No

Need help?

We are very happy to help you make the most of Blnk, regardless of whether it is your first time or you are switching from another tool. To ask questions or discuss issues, please contact us or join our Discord community.

Start Here

Instances

Ledgers

Balances

Transactions

Identities

Reconciliation

Filters & Views

Alerts

Monitoring

Insights

Apps

Settings

Integrations

How to join them

Columns you can select

Transaction status

Need help?

​How to join them

​Columns you can select

​Transaction status

​Need help?

How to join them

Columns you can select

Transaction status

Need help?