Database Schema - LeoKit Documentation

Overview

This reference documents the database schema used by LeoKit for storing quotes, transactions, tokens, and operational data. Use this schema for backend integrations, analytics, and custom implementations.

Core Tables

quotes Table

Stores all quote requests and responses with timing information.

CREATE TABLE quotes (
  id UUID PRIMARY KEY,
  timestamp BIGINT,
  created_at TIMESTAMPTZ,
  response_time NUMERIC,
  from_address TEXT,
  to_address TEXT,
  quote_offers JSONB,
  client_api TEXT,
  quoting_type TEXT,
  from_asset TEXT,
  to_asset TEXT,
  amount TEXT
);

Field Descriptions

Field	Type	Description	Constraints
id	UUID	Unique quote identifier	PRIMARY KEY
timestamp	BIGINT	Unix timestamp (milliseconds)	-
created_at	TIMESTAMPTZ	Timestamp with timezone	-
response_time	NUMERIC	API response time in ms	-
from_address	TEXT	Source wallet address	Nullable
to_address	TEXT	Destination wallet address	NOT NULL
quote_offers	JSONB	Array of protocol quotes	NOT NULL
client_api	TEXT	Client API key identifier	NOT NULL
quoting_type	TEXT	’http’ or ‘streaming’	NOT NULL
from_asset	TEXT	Source asset (CHAIN.SYMBOL-ADDRESS)	NOT NULL
to_asset	TEXT	Destination asset (CHAIN.SYMBOL-ADDRESS)	NOT NULL
amount	TEXT	Input amount in base units	NOT NULL

Example Row

INSERT INTO quotes VALUES (
  'f47ac10b-58cc-4372-a567-0e02b2c3d479',
  1704067200000,
  '2024-01-01 00:00:00+00',
  2350.5,
  'bc1qxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh',
  '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb',
  '[
    {
      "protocol": "thorchain",
      "data": {
        "expected_amount_out": "9850000000",
        "total_swap_seconds": 180,
        "fees": [...]
      }
    }
  ]'::jsonb,
  'client_abc123',
  'http',
  'BTC.BTC',
  'ETH.USDC-0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48',
  '100000000'
);

Indexes

CREATE INDEX idx_quotes_client_api ON quotes(client_api);
CREATE INDEX idx_quotes_created_at ON quotes(created_at);
CREATE INDEX idx_quotes_from_asset ON quotes(from_asset);
CREATE INDEX idx_quotes_to_asset ON quotes(to_asset);
CREATE INDEX idx_quotes_timestamp ON quotes(timestamp);

deposits Table

Stores deposit transaction details after quote selection.

CREATE TABLE deposits (
  id SERIAL PRIMARY KEY,
  client_api TEXT,
  quote_id UUID UNIQUE,
  req_id TEXT,
  transaction JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Field Descriptions

Field	Type	Description	Constraints
id	SERIAL	Auto-incrementing ID	PRIMARY KEY
client_api	TEXT	Client API key identifier	NOT NULL
quote_id	UUID	Reference to quotes.id	UNIQUE, NOT NULL
req_id	TEXT	Custom request identifier	Nullable
transaction	JSONB	Unsigned transaction data	NOT NULL
created_at	TIMESTAMPTZ	Creation timestamp	DEFAULT NOW()

Example Row

INSERT INTO deposits (client_api, quote_id, req_id, transaction) VALUES (
  'client_abc123',
  'f47ac10b-58cc-4372-a567-0e02b2c3d479',
  'user_request_001',
  '{
    "protocol": "thorchain",
    "unsigned_transactions": [{
      "to": "bc1qxy2...",
      "value": "100000000",
      "memo": "=:ETH.USDC-0xA0b...:0x742d35..."
    }],
    "chain": "BTC"
  }'::jsonb
);

Indexes

CREATE INDEX idx_deposits_client_api ON deposits(client_api);
CREATE INDEX idx_deposits_quote_id ON deposits(quote_id);
CREATE INDEX idx_deposits_created_at ON deposits(created_at);

tx_status Table

Tracks transaction status and updates over time.

CREATE TABLE tx_status (
  id SERIAL PRIMARY KEY,
  quote_id UUID,
  deposit_id INTEGER REFERENCES deposits(id),
  tx_hash TEXT,
  req_id TEXT,
  status TEXT,
  protocol TEXT,
  response JSONB,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Field Descriptions

Field	Type	Description	Constraints
id	SERIAL	Auto-incrementing ID	PRIMARY KEY
quote_id	UUID	Reference to quotes.id	Nullable
deposit_id	INTEGER	Reference to deposits.id	FOREIGN KEY
tx_hash	TEXT	Blockchain transaction hash	Nullable
req_id	TEXT	Custom request identifier	Nullable
status	TEXT	Transaction status	NOT NULL
protocol	TEXT	Protocol name	NOT NULL
response	JSONB	Status response from protocol	NOT NULL
updated_at	TIMESTAMPTZ	Last update timestamp	DEFAULT NOW()

Status Values

Status	Description
pending	Transaction submitted but not confirmed
processing	Transaction confirmed, swap in progress
completed	Swap completed successfully
failed	Transaction or swap failed
refunded	Transaction refunded due to error

Example Row

INSERT INTO tx_status (quote_id, deposit_id, tx_hash, status, protocol, response) VALUES (
  'f47ac10b-58cc-4372-a567-0e02b2c3d479',
  1,
  'abc123...def456',
  'processing',
  'thorchain',
  '{
    "stages": {
      "inbound_observed": { "completed": true },
      "swap_finalised": { "completed": true },
      "outbound_signed": { "completed": false }
    }
  }'::jsonb
);

Indexes

CREATE INDEX idx_tx_status_quote_id ON tx_status(quote_id);
CREATE INDEX idx_tx_status_tx_hash ON tx_status(tx_hash);
CREATE INDEX idx_tx_status_status ON tx_status(status);
CREATE INDEX idx_tx_status_updated_at ON tx_status(updated_at);

Asset & Pricing Tables

tokens Table

Stores token metadata and pricing information.

CREATE TABLE tokens (
  id SERIAL PRIMARY KEY,
  blockchain TEXT,
  symbol TEXT,
  address TEXT,
  decimals INTEGER,
  price_usd NUMERIC,
  coingecko_id TEXT,
  is_popular BOOLEAN,
  updated_at TIMESTAMPTZ,
  icon_url TEXT,
  UNIQUE(blockchain, address)
);

Field Descriptions

Field	Type	Description	Constraints
id	SERIAL	Auto-incrementing ID	PRIMARY KEY
blockchain	TEXT	Blockchain short name (ETH, BSC, etc.)	NOT NULL
symbol	TEXT	Token symbol	NOT NULL
address	TEXT	Contract address (NULL for native)	Nullable
decimals	INTEGER	Token decimals	NOT NULL
price_usd	NUMERIC	Current price in USD	Nullable
coingecko_id	TEXT	CoinGecko API identifier	Nullable
is_popular	BOOLEAN	Flag for popular tokens	DEFAULT FALSE
updated_at	TIMESTAMPTZ	Last price update	-
icon_url	TEXT	Token icon URL	Nullable

Example Rows

-- Native asset
INSERT INTO tokens (blockchain, symbol, address, decimals, price_usd, coingecko_id, is_popular, icon_url) VALUES
('BTC', 'BTC', NULL, 8, 65432.50, 'bitcoin', true, 'https://assets.coingecko.com/coins/images/1/large/bitcoin.png');

-- ERC20 token
INSERT INTO tokens (blockchain, symbol, address, decimals, price_usd, coingecko_id, is_popular, icon_url) VALUES
('ETH', 'USDC', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48', 6, 1.00, 'usd-coin', true, 'https://assets.coingecko.com/coins/images/6319/large/USD_Coin_icon.png');

Indexes

CREATE INDEX idx_tokens_blockchain ON tokens(blockchain);
CREATE INDEX idx_tokens_symbol ON tokens(symbol);
CREATE INDEX idx_tokens_is_popular ON tokens(is_popular);
CREATE UNIQUE INDEX idx_tokens_blockchain_address ON tokens(blockchain, address);

gas_prices Table

Stores historical gas price data per chain.

CREATE TABLE gas_prices (
  id SERIAL PRIMARY KEY,
  chain TEXT,
  price NUMERIC,
  timestamp BIGINT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Field Descriptions

Field	Type	Description	Constraints
id	SERIAL	Auto-incrementing ID	PRIMARY KEY
chain	TEXT	Blockchain short name	NOT NULL
price	NUMERIC	Gas price in chain units	NOT NULL
timestamp	BIGINT	Unix timestamp (milliseconds)	NOT NULL
created_at	TIMESTAMPTZ	Record creation time	DEFAULT NOW()

Gas Price Units by Chain

Chain Type	Unit	Description
EVM	gwei	10^9 wei
Bitcoin	sat/vbyte	Satoshis per virtual byte
Cosmos	native	Native token units
NEAR	TGas	TeraGas units

Example Rows

INSERT INTO gas_prices (chain, price, timestamp) VALUES
('ETH', 25.5, 1704067200000),    -- 25.5 gwei
('BTC', 10, 1704067200000),      -- 10 sat/vbyte
('ARB', 0.1, 1704067200000),     -- 0.1 gwei
('THOR', 0.02, 1704067200000);   -- 0.02 RUNE

Indexes

CREATE INDEX idx_gas_prices_chain ON gas_prices(chain);
CREATE INDEX idx_gas_prices_timestamp ON gas_prices(timestamp);
CREATE INDEX idx_gas_prices_created_at ON gas_prices(created_at);

Error & Debug Tables

error_logs Table

Stores error traces for debugging and support.

CREATE TABLE error_logs (
  id SERIAL PRIMARY KEY,
  trace_id TEXT UNIQUE,
  client_api TEXT,
  error_details JSONB,
  endpoint TEXT,
  request_params JSONB,
  timestamp TIMESTAMPTZ DEFAULT NOW()
);

Field Descriptions

Field	Type	Description	Constraints
id	SERIAL	Auto-incrementing ID	PRIMARY KEY
trace_id	TEXT	Unique error trace ID	UNIQUE, NOT NULL
client_api	TEXT	Client API key identifier	NOT NULL
error_details	JSONB	Error message and stack	NOT NULL
endpoint	TEXT	API endpoint that errored	NOT NULL
request_params	JSONB	Request parameters	Nullable
timestamp	TIMESTAMPTZ	Error occurrence time	DEFAULT NOW()

Example Row

INSERT INTO error_logs (trace_id, client_api, error_details, endpoint, request_params) VALUES (
  'err_abc123def456',
  'client_abc123',
  '{
    "code": "INSUFFICIENT_BALANCE",
    "message": "Insufficient balance (including gas fees).",
    "status": 400,
    "context": {
      "required": "1.05 ETH",
      "available": "1.00 ETH"
    }
  }'::jsonb,
  '/leokit/deposit',
  '{
    "quote_id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
    "selected_protocol": "thorchain"
  }'::jsonb
);

Indexes

CREATE INDEX idx_error_logs_trace_id ON error_logs(trace_id);
CREATE INDEX idx_error_logs_client_api ON error_logs(client_api);
CREATE INDEX idx_error_logs_timestamp ON error_logs(timestamp);

Cleanup Policy

Error logs older than 30 days are automatically purged:

DELETE FROM error_logs WHERE timestamp < NOW() - INTERVAL '30 days';

Client Configuration

clients Table (Reference)

Stores API key configurations and permissions.

CREATE TABLE clients (
  id SERIAL PRIMARY KEY,
  api_key TEXT UNIQUE NOT NULL,
  name TEXT,
  enabled_protocols TEXT[],
  fee_recipient TEXT,
  affiliate_bps INTEGER,
  rate_limit INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Field Descriptions

Field	Type	Description
id	SERIAL	Auto-incrementing ID
api_key	TEXT	Unique API key
name	TEXT	Client name/organization
enabled_protocols	TEXT[]	Array of enabled protocols
fee_recipient	TEXT	Affiliate fee recipient address
affiliate_bps	INTEGER	Default affiliate fee (BPS)
rate_limit	INTEGER	Requests per minute limit
created_at	TIMESTAMPTZ	Account creation time
updated_at	TIMESTAMPTZ	Last configuration update

Relationships

Entity Relationship Diagram

quotes (1) ──────── (1) deposits
  │                      │
  │                      │
  └────────────────┬─────┘
                   │
                   │
                  (N)
              tx_status

quotes (N) ──────── (1) clients
deposits (N) ──────── (1) clients
error_logs (N) ──────── (1) clients

tokens (N) ──────── (1) gas_prices (by blockchain)

Join Examples

Get Quote with Deposit and Status

SELECT
  q.id AS quote_id,
  q.from_asset,
  q.to_asset,
  q.amount,
  d.transaction AS deposit_data,
  t.tx_hash,
  t.status,
  t.response AS status_response
FROM quotes q
LEFT JOIN deposits d ON q.id = d.quote_id
LEFT JOIN tx_status t ON d.id = t.deposit_id
WHERE q.id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';

Get Client Analytics

SELECT
  c.name AS client_name,
  COUNT(q.id) AS total_quotes,
  COUNT(d.id) AS total_deposits,
  COUNT(CASE WHEN t.status = 'completed' THEN 1 END) AS completed_swaps,
  SUM(q.response_time) / COUNT(q.id) AS avg_response_time_ms
FROM clients c
LEFT JOIN quotes q ON q.client_api = c.api_key
LEFT JOIN deposits d ON d.quote_id = q.id
LEFT JOIN tx_status t ON t.deposit_id = d.id
WHERE q.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.id, c.name;

Get Popular Tokens

SELECT
  t.blockchain,
  t.symbol,
  t.price_usd,
  COUNT(DISTINCT q.id) AS quote_count
FROM tokens t
LEFT JOIN quotes q ON (
  q.from_asset LIKE t.blockchain || '.' || t.symbol || '%' OR
  q.to_asset LIKE t.blockchain || '.' || t.symbol || '%'
)
WHERE t.is_popular = true
  AND q.created_at >= NOW() - INTERVAL '7 days'
GROUP BY t.id, t.blockchain, t.symbol, t.price_usd
ORDER BY quote_count DESC
LIMIT 20;

Data Retention

Recommended Retention Policies

Table	Retention Period	Cleanup Query
quotes	90 days	`DELETE FROM quotes WHERE created_at < NOW() - INTERVAL '90 days'`
deposits	90 days	`DELETE FROM deposits WHERE created_at < NOW() - INTERVAL '90 days'`
tx_status	90 days	`DELETE FROM tx_status WHERE updated_at < NOW() - INTERVAL '90 days'`
error_logs	30 days	`DELETE FROM error_logs WHERE timestamp < NOW() - INTERVAL '30 days'`
gas_prices	7 days	`DELETE FROM gas_prices WHERE created_at < NOW() - INTERVAL '7 days'`
tokens	Indefinite	N/A (update pricing only)

Archival Strategy

For long-term analytics, consider archiving old records to separate tables:

-- Archive old quotes
INSERT INTO quotes_archive
SELECT * FROM quotes
WHERE created_at < NOW() - INTERVAL '90 days';

DELETE FROM quotes
WHERE created_at < NOW() - INTERVAL '90 days';

Performance Optimization

Query Optimization Tips

Use appropriate indexes for your query patterns
Partition large tables by date (monthly partitions recommended)
Use JSONB indexes for frequently queried JSON fields
Analyze query plans with EXPLAIN ANALYZE
Vacuum regularly to maintain table performance

JSONB Indexing Examples

-- Index for quote offers protocol lookup
CREATE INDEX idx_quotes_offers_protocol ON quotes
USING GIN ((quote_offers -> 'protocol'));

-- Index for transaction status
CREATE INDEX idx_tx_status_response ON tx_status
USING GIN (response);

-- Index for error codes
CREATE INDEX idx_error_details_code ON error_logs
((error_details ->> 'code'));

Backup & Recovery

Backup Strategy

# Daily full backup
pg_dump leokit_db > backup_$(date +%Y%m%d).sql

# Continuous archiving (PITR)
# Configure in postgresql.conf:
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /backup/wal/%f'

Recovery Example

# Restore from backup
psql leokit_db < backup_20240101.sql

# Point-in-time recovery
pg_restore -d leokit_db -t quotes backup_20240101.sql

Migration Scripts

Adding New Fields

-- Add new field to quotes table
ALTER TABLE quotes
ADD COLUMN user_agent TEXT;

-- Add index
CREATE INDEX idx_quotes_user_agent ON quotes(user_agent);

Schema Versioning

Maintain schema version in dedicated table:

CREATE TABLE schema_version (
  version INTEGER PRIMARY KEY,
  applied_at TIMESTAMPTZ DEFAULT NOW(),
  description TEXT
);

INSERT INTO schema_version (version, description) VALUES
(1, 'Initial schema'),
(2, 'Added user_agent to quotes table');

Getting Started

Integration Guides

Configuration

Reference

​Overview

​Core Tables

​quotes Table

​Field Descriptions

​Example Row

​Indexes

​deposits Table

​Field Descriptions

​Example Row

​Indexes

​tx_status Table

​Field Descriptions

​Status Values

​Example Row

​Indexes

​Asset & Pricing Tables

​tokens Table

​Field Descriptions

​Example Rows

​Indexes

​gas_prices Table

​Field Descriptions

​Gas Price Units by Chain

​Example Rows

​Indexes

​Error & Debug Tables

​error_logs Table

​Field Descriptions

​Example Row

​Indexes

​Cleanup Policy

​Client Configuration

​clients Table (Reference)

​Field Descriptions

​Relationships

​Entity Relationship Diagram

​Join Examples

​Get Quote with Deposit and Status

​Get Client Analytics

​Get Popular Tokens

​Data Retention

​Recommended Retention Policies

​Archival Strategy

​Performance Optimization

​Query Optimization Tips

​JSONB Indexing Examples

​Backup & Recovery

​Backup Strategy

​Recovery Example

​Migration Scripts

​Adding New Fields

​Schema Versioning

Overview

Core Tables

quotes Table

Field Descriptions

Example Row

Indexes

deposits Table

Field Descriptions

Example Row

Indexes

tx_status Table

Field Descriptions

Status Values

Example Row

Indexes

Asset & Pricing Tables

tokens Table

Field Descriptions

Example Rows

Indexes

gas_prices Table

Field Descriptions

Gas Price Units by Chain

Example Rows

Indexes

Error & Debug Tables

error_logs Table

Field Descriptions

Example Row

Indexes

Cleanup Policy

Client Configuration

clients Table (Reference)

Field Descriptions

Relationships

Entity Relationship Diagram

Join Examples

Get Quote with Deposit and Status

Get Client Analytics

Get Popular Tokens

Data Retention

Recommended Retention Policies

Archival Strategy

Performance Optimization

Query Optimization Tips

JSONB Indexing Examples

Backup & Recovery

Backup Strategy

Recovery Example

Migration Scripts

Adding New Fields

Schema Versioning