Database Schema

The Veil dApp server caches on-chain state, logs transactions, curates the pool admin allowlist, and stores single-use auth nonces in Neon Postgres . All tables defined in veil-landing/lib/db/schema.sql.

The migration is idempotent — re-running npm run db:migrate is safe.

Cluster-scoped rows. Tables that hold on-chain state (pools, positions, tx_log, audit_log) carry a cluster column whose value is one of 'mainnet' | 'devnet' | 'localnet'. Every API route filters reads and stamps writes with the active NETWORK constant so that one Neon database can hold rows for multiple clusters without collision — useful in dev when you switch builds between localnet and devnet. For production you should still point mainnet at its own dedicated Neon project; the column makes mistakes loud rather than catastrophic.

Conventions

All on-chain numeric quantities are stored as NUMERIC (for u64-fitting values like share counts) or TEXT (for u128-WAD values that exceed Postgres’s BIGINT range).
All public keys are stored as base58 TEXT.
All timestamps are TIMESTAMPTZ with DEFAULT now().
Soft deletes (revoked_at) preferred over hard deletes for the audit-relevant pool_admins table.

`pool_admins`

The off-chain allowlist. A wallet is “active” iff it has a row with revoked_at IS NULL.


CREATE TABLE pool_admins (
  pubkey       TEXT PRIMARY KEY,
  role         TEXT NOT NULL DEFAULT 'pool_admin',
                                       -- 'super_admin' | 'pool_admin'
  label        TEXT,
  added_by     TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at   TIMESTAMPTZ
);
CREATE INDEX idx_pool_admins_role
  ON pool_admins(role) WHERE revoked_at IS NULL;

Column	Notes
`pubkey`	Base58 wallet address. PK ensures one role per wallet.
`role`	Free string; routes enforce the closed set `{super_admin, pool_admin}`.
`label`	Human-readable note (e.g. `"team multisig"`).
`added_by`	Pubkey of the super-admin that inserted the row. `'system'` for the bootstrap row written by the migration. `'cli'` for rows added via `npm run db:add-admin`.
`revoked_at`	Set on `DELETE /api/admin/allowlist`. Cannot revoke self.

The bootstrap super-admin is written by lib/db/migrate.ts from SUPER_ADMIN_PUBKEY in .env.local.

`pools`

Cached LendingPool snapshot. Refreshed by POST /api/pools/sync.


CREATE TABLE pools (
  cluster          TEXT NOT NULL,            -- 'mainnet'|'devnet'|'localnet'
  pool_address     TEXT NOT NULL,
  token_mint       TEXT NOT NULL,
  symbol           TEXT,
  authority        TEXT NOT NULL,
  vault            TEXT NOT NULL,
  pool_bump        INTEGER NOT NULL,
  authority_bump   INTEGER NOT NULL,
  vault_bump       INTEGER NOT NULL,
  paused           BOOLEAN NOT NULL DEFAULT false,
 
  total_deposits   NUMERIC NOT NULL DEFAULT 0,
  total_borrows    NUMERIC NOT NULL DEFAULT 0,
  accumulated_fees NUMERIC NOT NULL DEFAULT 0,
 
  ltv_wad                  TEXT,
  liquidation_threshold_wad TEXT,
  liquidation_bonus_wad    TEXT,
  protocol_liq_fee_wad     TEXT,
  reserve_factor_wad       TEXT,
  close_factor_wad         TEXT,
  base_rate_wad            TEXT,
  optimal_util_wad         TEXT,
  slope1_wad               TEXT,
  slope2_wad               TEXT,
  flash_fee_bps            INTEGER,
  decimals                 INTEGER NOT NULL DEFAULT 9,
 
  oracle_price       NUMERIC,
  oracle_conf        NUMERIC,
  oracle_expo        INTEGER,
  pyth_price_feed    TEXT,
 
  created_by         TEXT,
  init_signature     TEXT,
  last_synced_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (cluster, pool_address)
);
CREATE INDEX idx_pools_mint      ON pools(cluster, token_mint);
CREATE INDEX idx_pools_authority ON pools(cluster, authority);

WAD-scaled risk parameters (ltv_wad, liquidation_threshold_wad, etc.) are stored as TEXT because their u128 range overflows Postgres’s signed BIGINT. Decode them on the read side as BigInt(row.ltv_wad).

`positions`

Cached UserPosition snapshot. Populated by indexers (none ship with v0.1 — the schema is in place for future indexer work). The health_factor_wad column is what an automated liquidator would query.


CREATE TABLE positions (
  cluster           TEXT NOT NULL,
  position_address  TEXT NOT NULL,
  pool_address      TEXT NOT NULL,
  owner             TEXT NOT NULL,
  deposit_shares    NUMERIC NOT NULL DEFAULT 0,
  borrow_principal  NUMERIC NOT NULL DEFAULT 0,
  deposit_idx_snap  TEXT,
  borrow_idx_snap   TEXT,
  health_factor_wad TEXT,
  last_synced_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (cluster, position_address),
  FOREIGN KEY (cluster, pool_address)
    REFERENCES pools(cluster, pool_address) ON DELETE CASCADE
);
CREATE INDEX idx_positions_owner  ON positions(cluster, owner);
CREATE INDEX idx_positions_pool   ON positions(cluster, pool_address);
CREATE INDEX idx_positions_health ON positions(cluster, health_factor_wad)
  WHERE borrow_principal > 0;

The idx_positions_health partial index — only over rows with non-zero debt — is the index a liquidator bot will hit when scanning for unhealthy positions.

`tx_log`

Append-only log of submitted transactions. The dApp posts to this from useVeilActions after confirmTransaction resolves. The pair (cluster, signature) is UNIQUE, so callers can safely POST the same signature with an updated status (e.g. pending → confirmed) without creating a duplicate row — the endpoint uses ON CONFLICT (cluster, signature) DO UPDATE.


CREATE TABLE tx_log (
  id              BIGSERIAL PRIMARY KEY,
  cluster         TEXT NOT NULL,
  signature       TEXT NOT NULL,
  pool_address    TEXT,
  wallet          TEXT NOT NULL,
  action          TEXT NOT NULL,
  amount          NUMERIC,
  status          TEXT NOT NULL DEFAULT 'pending',
  error_msg       TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (cluster, signature)
);
CREATE INDEX idx_tx_log_wallet ON tx_log(cluster, wallet, created_at DESC);
CREATE INDEX idx_tx_log_pool   ON tx_log(cluster, pool_address, created_at DESC);
CREATE INDEX idx_tx_log_action ON tx_log(cluster, action, created_at DESC);

`audit_log`

Records administrative actions: allowlist additions/revocations, pool initialisations registered via /api/pools/init, and any future operations that warrant traceability.


CREATE TABLE audit_log (
  id           BIGSERIAL PRIMARY KEY,
  cluster      TEXT NOT NULL,
  actor        TEXT NOT NULL,
  action       TEXT NOT NULL,
  target       TEXT,
  details      JSONB,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_log_actor ON audit_log(cluster, actor, created_at DESC);

`auth_nonces`

Single-use ed25519 nonces for the admin auth handshake. The /api/auth/nonce endpoint inserts a row; protected endpoints DELETE … RETURNING to consume it atomically.


CREATE TABLE auth_nonces (
  pubkey      TEXT NOT NULL,
  nonce       TEXT NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (pubkey, nonce)
);
CREATE INDEX idx_auth_nonces_expires ON auth_nonces(expires_at);

The migration sets a TTL of 5 minutes; expired rows are GC’d opportunistically on every /api/auth/nonce call (DELETE FROM auth_nonces WHERE expires_at < now()).

The DATABASE_URL is a high-trust secret. Anyone with write access to this database can insert into pool_admins and bypass the signed-nonce flow. Rotate it on team membership changes; treat it the way you would a Stripe webhook key.

Migration entrypoints

Command	Effect
`npm run db:migrate`	Applies `lib/db/schema.sql` and seeds `SUPER_ADMIN_PUBKEY` from `.env.local`. Idempotent.
`npm run db:add-admin -- <pubkey> [pool_admin\|super_admin] [label]`	Direct insert into `pool_admins`. Idempotent on `pubkey`.

Both scripts are TypeScript files run via tsx; both source .env.local through dotenv at startup. Both connect via the Neon WebSocket Pool (rather than the HTTP serverless driver) so they can run multi-statement DDL in a transaction.