LayerLedger Example Queries

This document provides example queries for common LayerLedger operations.

---

1. Get Full Provenance for Asset X

Use Case: Display complete history of an asset (like a block explorer)

-- Get asset with full provenance
WITH asset_data AS (
    SELECT 
        ar.id,
        ar.asset_type,
        ar.app_id,
        ar.title,
        ar.nfc_uuid,
        ar.mpt_issuance_id,
        ar.issuer_user_id,
        ar.created_at as asset_created_at,
        ast.owner_user_id as current_owner,
        ast.custody_type as current_custody,
        ast.onchain_status,
        ast.last_updated_at
    FROM layer_engine.asset_registry ar
    LEFT JOIN layer_engine.asset_states ast ON ar.id = ast.asset_id
    WHERE ar.id = $1
)
SELECT 
    ad.*,
    json_agg(
        json_build_object(
            'id', at.id,
            'tx_type', at.tx_type,
            'actor_user_id', at.actor_user_id,
            'from_user_id', at.from_user_id,
            'to_user_id', at.to_user_id,
            'quantity', at.quantity,
            'layer_fee', at.layer_fee,
            'onchain_tx_hash', at.onchain_tx_hash,
            'data', at.data,
            'created_at', at.created_at
        ) ORDER BY at.created_at ASC
    ) FILTER (WHERE at.id IS NOT NULL) as transactions,
    json_agg(
        json_build_object(
            'id', ae.id,
            'event_type', ae.event_type,
            'user_id', ae.user_id,
            'data', ae.data,
            'created_at', ae.created_at
        ) ORDER BY ae.created_at ASC
    ) FILTER (WHERE ae.id IS NOT NULL) as events
FROM asset_data ad
LEFT JOIN layer_engine.asset_transactions at ON ad.id = at.asset_id
LEFT JOIN layer_engine.asset_events ae ON ad.id = ae.asset_id
GROUP BY ad.id, ad.asset_type, ad.app_id, ad.title, ad.nfc_uuid, ad.mpt_issuance_id, 
         ad.issuer_user_id, ad.asset_created_at, ad.current_owner, ad.current_custody, 
         ad.onchain_status, ad.last_updated_at;

tRPC Usage:

const provenance = await api.layerledger.getAssetProvenance.query({
  asset_id: 'uuid-here'
});

---

2. Get Full History for User Y

Use Case: MyLayer page, user dashboard, activity feed

-- Get all assets owned by user
SELECT 
    ar.*,
    ast.*,
    COUNT(DISTINCT at.id) as transaction_count,
    COUNT(DISTINCT ae.id) as event_count,
    MAX(at.created_at) as last_transaction_at
FROM layer_engine.asset_states ast
JOIN layer_engine.asset_registry ar ON ast.asset_id = ar.id
LEFT JOIN layer_engine.asset_transactions at ON ar.id = at.asset_id
LEFT JOIN layer_engine.asset_events ae ON ar.id = ae.asset_id
WHERE ast.owner_user_id = $1
GROUP BY ar.id, ast.asset_id
ORDER BY ast.last_updated_at DESC
LIMIT 50;

-- Get all transactions involving user
SELECT 
    at.*,
    ar.title as asset_title,
    ar.asset_type,
    ar.app_id
FROM layer_engine.asset_transactions at
JOIN layer_engine.asset_registry ar ON at.asset_id = ar.id
WHERE at.actor_user_id = $1 
   OR at.from_user_id = $1 
   OR at.to_user_id = $1
ORDER BY at.created_at DESC
LIMIT 100;

-- Get all events by user
SELECT 
    ae.*,
    ar.title as asset_title,
    ar.asset_type
FROM layer_engine.asset_events ae
JOIN layer_engine.asset_registry ar ON ae.asset_id = ar.id
WHERE ae.user_id = $1
ORDER BY ae.created_at DESC
LIMIT 100;

tRPC Usage:

const history = await api.layerledger.getUserHistory.query({
  user_id: 'user-uuid-here',
  limit: 50,
  offset: 0
});

---

3. Get Gas Report for Managed Custody Action Z

Use Case: User wants to see their gas usage, treasury coverage, billing

-- Get detailed gas report for user
SELECT 
    gl.*,
    ar.title as asset_title,
    ar.asset_type,
    at.tx_type as related_transaction_type
FROM layer_engine.gas_ledger gl
LEFT JOIN layer_engine.asset_registry ar ON gl.asset_id = ar.id
LEFT JOIN layer_engine.asset_transactions at ON gl.related_transaction_id = at.id
WHERE gl.user_id = $1
  AND ($2::text IS NULL OR gl.action = $2)
  AND ($3::timestamptz IS NULL OR gl.created_at >= $3)
  AND ($4::timestamptz IS NULL OR gl.created_at <= $4)
ORDER BY gl.created_at DESC;

-- Get summary totals
SELECT 
    user_id,
    COUNT(*) as total_actions,
    SUM(gas_required) as total_gas_required,
    SUM(gas_paid_by_user) as total_gas_paid_by_user,
    SUM(gas_paid_by_treasury) as total_gas_paid_by_treasury,
    AVG(gas_required) as avg_gas_per_action
FROM layer_engine.gas_ledger
WHERE user_id = $1
  AND ($2::text IS NULL OR action = $2)
  AND ($3::timestamptz IS NULL OR created_at >= $3)
  AND ($4::timestamptz IS NULL OR created_at <= $4)
GROUP BY user_id;

tRPC Usage:

const gasReport = await api.layerledger.getGasReport.query({
  user_id: 'user-uuid-here',
  action: 'TRANSFERRED', // optional
  start_date: '2024-01-01T00:00:00Z', // optional
  end_date: '2024-12-31T23:59:59Z' // optional
});

---

4. Rebuild XRPL → Internal State

Use Case: State reconstruction, audit, reconciliation

-- Get all XRPL transactions for an asset
SELECT 
    xsl.*,
    ar.title as asset_title,
    ar.asset_type
FROM layer_engine.xrpl_sync_log xsl
LEFT JOIN layer_engine.asset_registry ar ON xsl.asset_id = ar.id
WHERE xsl.asset_id = $1
  AND ($2::bigint IS NULL OR xsl.ledger_index >= $2)
  AND ($3::bigint IS NULL OR xsl.ledger_index <= $3)
ORDER BY xsl.ledger_index ASC;

-- Reconstruct ownership from XRPL transactions
WITH xrpl_ownership AS (
    SELECT DISTINCT ON (asset_id)
        asset_id,
        (data->>'to')::text as owner_address,
        ledger_index,
        tx_hash
    FROM layer_engine.xrpl_sync_log
    WHERE asset_id = $1
      AND tx_type IN ('Payment', 'MPTokenIssuanceCreate')
    ORDER BY asset_id, ledger_index DESC
)
SELECT 
    ar.*,
    xo.owner_address as xrpl_owner,
    xo.ledger_index as last_ledger_index,
    xo.tx_hash as last_tx_hash,
    ast.owner_user_id as internal_owner,
    ast.custody_type,
    ast.onchain_status
FROM layer_engine.asset_registry ar
LEFT JOIN xrpl_ownership xo ON ar.id = xo.asset_id
LEFT JOIN layer_engine.asset_states ast ON ar.id = ast.asset_id
WHERE ar.id = $1;

tRPC Usage:

// Get XRPL transactions
const xrplTxs = await api.layerledger.getXRPLTransactions.query({
  asset_id: 'asset-uuid-here',
  limit: 100
});

// Rebuild state (mutation - would need implementation)
// await api.layerledger.rebuildStateFromXRPL.mutate({
//   asset_id: 'asset-uuid-here',
//   from_ledger_index: 1000000,
//   to_ledger_index: 2000000
// });

---

5. Get Assets by Type/App

Use Case: Browse assets, filter by app, marketplace listings

-- Get all Mintlings
SELECT 
    ar.*,
    ast.owner_user_id,
    ast.custody_type,
    ast.onchain_status,
    COUNT(DISTINCT at.id) as transaction_count
FROM layer_engine.asset_registry ar
LEFT JOIN layer_engine.asset_states ast ON ar.id = ast.asset_id
LEFT JOIN layer_engine.asset_transactions at ON ar.id = at.asset_id
WHERE ar.app_id = 'mintlings'
  AND ar.asset_type = 'mintling'
GROUP BY ar.id, ast.asset_id
ORDER BY ar.created_at DESC
LIMIT 50;

-- Get all assets in a pack
SELECT 
    ar.*,
    ast.*
FROM layer_engine.asset_states ast
JOIN layer_engine.asset_registry ar ON ast.asset_id = ar.id
WHERE ast.parent_container_id = $1
ORDER BY ar.created_at ASC;

---

6. Get Asset Timeline

Use Case: Display chronological timeline of asset events

-- Get unified timeline (transactions + events)
SELECT 
    'transaction' as event_source,
    at.id as event_id,
    at.tx_type as event_type,
    at.created_at,
    at.actor_user_id as user_id,
    json_build_object(
        'tx_type', at.tx_type,
        'from_user_id', at.from_user_id,
        'to_user_id', at.to_user_id,
        'quantity', at.quantity,
        'onchain_tx_hash', at.onchain_tx_hash
    ) as event_data
FROM layer_engine.asset_transactions at
WHERE at.asset_id = $1

UNION ALL

SELECT 
    'event' as event_source,
    ae.id as event_id,
    ae.event_type,
    ae.created_at,
    ae.user_id,
    ae.data as event_data
FROM layer_engine.asset_events ae
WHERE ae.asset_id = $1

ORDER BY created_at ASC;

---

7. Get Fraction Ownership

Use Case: Track who owns fractions of an asset

-- Get all fractions of an asset
SELECT 
    ar.*,
    ast.fraction_count,
    ast.owner_user_id,
    COUNT(DISTINCT at.id) FILTER (WHERE at.tx_type = 'FRACTIONED') as fraction_transactions
FROM layer_engine.asset_registry ar
JOIN layer_engine.asset_states ast ON ar.id = ast.asset_id
LEFT JOIN layer_engine.asset_transactions at ON ar.id = at.asset_id
WHERE ast.container_type = 'fraction_set'
  AND ast.parent_container_id = $1
GROUP BY ar.id, ast.asset_id
ORDER BY ar.created_at ASC;

---

8. Get Pack Contents

Use Case: Display what's inside a pack before/after opening

-- Get pack contents
SELECT 
    ar.*,
    ast.*,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM layer_engine.asset_transactions 
            WHERE asset_id = ar.id AND tx_type = 'PACK_OPENED'
        ) THEN true
        ELSE false
    END as is_opened
FROM layer_engine.asset_states ast
JOIN layer_engine.asset_registry ar ON ast.asset_id = ar.id
WHERE ast.parent_container_id = $1
ORDER BY ar.created_at ASC;

---

9. Get Treasury Gas Coverage Report

Use Case: Admin dashboard, treasury management

-- Get all treasury-covered gas transactions
SELECT 
    gl.*,
    ar.title as asset_title,
    u.email as user_email,
    at.tx_type as transaction_type
FROM layer_engine.gas_ledger gl
LEFT JOIN layer_engine.asset_registry ar ON gl.asset_id = ar.id
LEFT JOIN auth.users u ON gl.user_id = u.id
LEFT JOIN layer_engine.asset_transactions at ON gl.related_transaction_id = at.id
WHERE gl.treasury_covered = true
  AND gl.gas_paid_by_treasury > 0
ORDER BY gl.created_at DESC;

-- Get summary by user
SELECT 
    gl.user_id,
    u.email as user_email,
    COUNT(*) as total_actions_covered,
    SUM(gl.gas_paid_by_treasury) as total_treasury_gas,
    AVG(gl.gas_paid_by_treasury) as avg_treasury_gas_per_action
FROM layer_engine.gas_ledger gl
LEFT JOIN auth.users u ON gl.user_id = u.id
WHERE gl.treasury_covered = true
  AND gl.gas_paid_by_treasury > 0
GROUP BY gl.user_id, u.email
ORDER BY total_treasury_gas DESC;

---

10. Search Assets

Use Case: Search functionality, marketplace

-- Search assets by title
SELECT 
    ar.*,
    ast.owner_user_id,
    ast.custody_type,
    ast.onchain_status
FROM layer_engine.asset_registry ar
LEFT JOIN layer_engine.asset_states ast ON ar.id = ast.asset_id
WHERE ar.title ILIKE '%' || $1 || '%'
  AND ($2::text IS NULL OR ar.app_id = $2)
  AND ($3::text IS NULL OR ar.asset_type = $3)
ORDER BY ar.created_at DESC
LIMIT 50;

---

11. Get Latest Checkpoint

Use Case: Get the most recent state commitment for verification

-- Get latest checkpoint
SELECT 
    id,
    state_root,
    tx_root,
    created_at,
    xrpl_tx_hash,
    notes
FROM layer_engine.checkpoints
ORDER BY created_at DESC
LIMIT 1;

tRPC Usage:

// Would need to add this endpoint
const latestCheckpoint = await api.layerledger.getLatestCheckpoint.query();

---

12. Compare Last 3 State Roots

Use Case: Verify state consistency, detect changes

-- Get last 3 checkpoints for comparison
SELECT 
    id,
    state_root,
    tx_root,
    created_at,
    xrpl_tx_hash,
    notes,
    LAG(state_root) OVER (ORDER BY created_at DESC) as previous_state_root,
    LAG(tx_root) OVER (ORDER BY created_at DESC) as previous_tx_root
FROM layer_engine.checkpoints
ORDER BY created_at DESC
LIMIT 3;

---

13. List All Asset Transactions Since Last Checkpoint

Use Case: Get all transactions that occurred since the last checkpoint

-- Get transactions since last checkpoint
WITH last_checkpoint AS (
    SELECT created_at as checkpoint_time
    FROM layer_engine.checkpoints
    ORDER BY created_at DESC
    LIMIT 1
)
SELECT 
    at.*,
    ar.title as asset_title,
    ar.asset_type,
    ar.app_id
FROM layer_engine.asset_transactions at
JOIN layer_engine.asset_registry ar ON at.asset_id = ar.id
CROSS JOIN last_checkpoint lc
WHERE at.created_at > lc.checkpoint_time
ORDER BY at.created_at ASC;

---

14. Get Checkpoint Diff Summary

Use Case: Summary of changes between checkpoints

-- Get summary of changes between two checkpoints
WITH checkpoint_range AS (
    SELECT 
        MIN(created_at) as start_time,
        MAX(created_at) as end_time
    FROM layer_engine.checkpoints
    WHERE id IN ($1, $2) -- Two checkpoint IDs
)
SELECT 
    COUNT(DISTINCT at.id) as transaction_count,
    COUNT(DISTINCT at.asset_id) as assets_affected,
    COUNT(DISTINCT at.actor_user_id) as unique_actors,
    COUNT(DISTINCT CASE WHEN at.tx_type = 'MINTED' THEN at.id END) as mints,
    COUNT(DISTINCT CASE WHEN at.tx_type = 'TRANSFERRED' THEN at.id END) as transfers,
    SUM(at.layer_fee) as total_fees
FROM layer_engine.asset_transactions at
CROSS JOIN checkpoint_range cr
WHERE at.created_at BETWEEN cr.start_time AND cr.end_time;

---

Performance Tips

1. Use Indexes: All foreign keys and common query columns are indexed
2. Limit Results: Always use LIMIT for large result sets
3. Use State Table: Query asset_states for current ownership instead of scanning transactions
4. Partition Large Tables: Consider partitioning asset_transactions by date for very large datasets
5. Cache Frequently Queried Data: Consider caching asset states and recent transactions

---

tRPC Helper Functions

All these queries are available via tRPC:

// Get asset provenance
const provenance = await api.layerledger.getAssetProvenance.query({ asset_id });

// Get user history
const history = await api.layerledger.getUserHistory.query({ user_id, limit: 50 });

// Get gas report
const gasReport = await api.layerledger.getGasReport.query({ user_id });

// Get XRPL transactions
const xrplTxs = await api.layerledger.getXRPLTransactions.query({ asset_id });