Database schema

There are two tables. Here they are as plain SQL, ready to paste into your own migration:

ledger_postgresql.sql
ledger_sqlite.sql

Both are generated from the same metadata the library uses, and a style gate regenerates and diffs them, so they cannot drift from the code. Regenerate locally with python scripts/emit_schema.py.

auradefi never emits DDL and never opens a connection you did not hand it. The schema is yours: apply it with Alembic, Flyway, Liquibase, Rails, Prisma, psql -f, or whatever reviews your migrations. If you would rather not, one call does it for you:

from auradefi.ledger.backends.models import metadata
metadata.create_all(engine)          # fine for a script; read the warning below

`auradefi_ledger_transactions`

One row per transaction, per tenant.

Column	Type	Null	Meaning
`tenant_id`	`VARCHAR`	no	PK part 1. The `usr_…` id. Every query is scoped by it.
`id`	`VARCHAR`	no	PK part 2. The derived `txn_…` id, stable across runs and backends.
`chain_id`	`VARCHAR`	no	CAIP-2, e.g. `eip155:1`.
`tx_hash`	`VARCHAR`	no	On-chain hash. Not unique on its own: one hash can touch several accounts.
`account_id`	`VARCHAR`	no	Which connection this row was ingested for.
`block_number`	`BIGINT`	yes	`NULL` while pending.
`initiated_at`	`BIGINT`	no	Millisecond epoch.
`confirmed_at`	`BIGINT`	yes	Millisecond epoch; `NULL` until confirmed.
`entries_json`	`VARCHAR`	no	The movements, as canonical JSON. See below.
`removed`	`BOOLEAN`	no	Reorg tombstone. A removed row is kept, never deleted.
`last_modified_seq`	`BIGINT`	no	Cursor ordering. Indexed with `tenant_id`.

Index: ix_auradefi_ledger_transactions_tenant_seq (tenant_id, last_modified_seq). sync() filters and orders on exactly that pair, so this index is what makes the cursor feed cheap. Keep it.

`entries_json`

Canonical JSON, sorted keys, no whitespace. Each entry looks like this:

[{"asset_id":"eip155:1/slip44:60","decimals":18,"direction":"in","raw":"1000000000000000000"}]

raw is a decimal-int string and never a JSON number. That choice is load-bearing: json.loads("1e77") yields a float that is wrong by about 10^60. Because these rows live in your database, something other than auradefi may write them, so a numeric raw is rejected on read instead of being coerced. An error is easier to deal with than a plausible-looking wrong amount. direction is in, out or self.

`auradefi_ledger_seqs`

Column	Type	Null	Meaning
`tenant_id`	`VARCHAR`	no	PK.
`seq`	`BIGINT`	no	Monotonic counter for that tenant; first value is 1.

The cursor counter lives in the database rather than in the process, so a restart cannot hand out a sequence number twice. Allocation is documented single-writer: run one ingest worker per tenant, or hold a lock, until Postgres hardening lands.

Two hazards

Every numeric column is BIGINT, and it has to be. Python int maps to SQLAlchemy Integer, which is int4 on PostgreSQL, and a millisecond epoch (1_754_000_000_000) overflows int4 by 816 times. Until 0.1.2 these columns were INTEGER, which meant the SQL ledger could not work on PostgreSQL at all: the first insert would fail with integer out of range. SQLite never noticed, because its INTEGER affinity is already 8 bytes, and that is how a fully green test suite hid the bug. If you hand-write this schema, use 64-bit integers.

metadata is the global SQLModel.metadata. If your application also uses SQLModel, its tables sit in the same registry, so:

metadata.create_all(engine)     # creates OUR two tables AND all of yours

That is rarely what you want against a production database, and it is the best reason to apply the .sql files instead, since they create exactly two tables and touch nothing else. The auradefi_ prefix on every table name exists for the same reason: these objects land in your database, beside your own.

What is not here

Only the ledger persists. Tenancy, API keys, quota counters, the audit log, webhook endpoints and deliveries, and embed sync-state are all in-memory in this release. They have no tables, and a restart forgets them.

That is a real limitation of the release, not an omission from this page. If you need any of it durable, the ports are there: bind your own sync_state (Bring your own), and keep tenancy in your own schema until a SQL backend for it ships. The audit log in particular is security-relevant and in-memory, so treat it accordingly.