Transaction Dashboard

Create a transaction dashboard to display locked funds and track transaction status using React Query and SQLite.

Introduction

In this part, we'll build a transaction dashboard that displays the user's transaction history and status. This will allow users to monitor their locked funds and see when transactions are confirmed on the blockchain. We'll also set up a database to persistently store transaction data.

Database Configuration

In previous parts, we built a wallet connector and fund locking functionality, but we didn't store transaction information. Let's add database support now to track transactions.

1. Create Data Directory

Create a directory for the SQLite database. Add a data directory to the root folder. This will be used to store the database file escrow.db.

2. Set Up the Database

Create a database utility file src/lib/db.ts:

// src/lib/db.ts
import sqlite from 'better-sqlite3';
import path from 'path';
import { TransactionStatus } from './types';

const dbPath = process.env.SQLITE_DB_PATH || './data/escrow.db';
const db = sqlite(dbPath);

// Initialize database tables
db.exec(`
  CREATE TABLE IF NOT EXISTS wallets (
    address TEXT PRIMARY KEY
  );

  CREATE TABLE IF NOT EXISTS transactions (
    txHash TEXT PRIMARY KEY,
    wallet TEXT NOT NULL,
    amount INTEGER NOT NULL,
    status TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    FOREIGN KEY (wallet) REFERENCES wallets(address)
  );
`);

export function upsertWallet(address: string) {
  db.prepare(`INSERT OR IGNORE INTO wallets(address) VALUES (?)`).run(address);
}

export function upsertTx(
  txHash: string,
  wallet: string,
  amount: number,
  status: TransactionStatus
) {
  db.prepare(
    `INSERT OR REPLACE INTO transactions(
       txHash, wallet, amount, status, timestamp
     ) VALUES (?, ?, ?, ?, ?)`
  ).run(txHash, wallet, amount, status, Date.now());
}

export function getTxsByWallet(wallet: string) {
  return db
    .prepare(`SELECT * FROM transactions WHERE wallet = ? ORDER BY timestamp DESC`)
    .all(wallet);
}

export function updateTxStatus(
  txHash: string,
  status: TransactionStatus
) {
  db.prepare(
    `UPDATE transactions SET status = ?, timestamp = ? WHERE txHash = ?`
  ).run(status, Date.now(), txHash);
}

export function getTxsByHash(txHash: string) {
  return db
    .prepare(`SELECT * FROM transactions WHERE txHash = ?`)
    .get(txHash);
}

React Query Setup

Now let's set up React Query for data fetching src/components/ReactQueryProvider.tsx:

1. Create the React Query Provider

2. Update Root Layout

Update the root layout to include the React Query provider src/app/layout.tsx:

Transaction API Endpoint

Let's create an API endpoint to fetch transactions for a specific wallet src/app/api/escrow/transactions/route.ts:

Update Submit Transaction Endpoint

Let's update the submit endpoint to store transaction data in the database src/app/api/escrow/submit/route.ts:

Update Transaction Hooks

Add the following functions to src/hooks/useTransactions.ts:

  1. fetchTransactions - Fetches transactions from the API

  2. useTransactionsByWallet - Retrieves transactions for a specific wallet

Create the MyTransactions Component

Let's create a component to display transaction history src/components/MyTransactions.tsx:

Update the Home Page

Finally, update the home page to include the MyTransactions component src/app/page.tsx:

Understanding the Transaction Dashboard

Data Flow

Here's how data flows through our application:

  1. Database Storage: When a transaction is created, it's stored in the SQLite database

  2. API Endpoint: The /api/escrow/transactions endpoint fetches transactions for a specific wallet

  3. React Query: Manages data fetching, caching, and periodic refreshing

  4. MyTransactions Component: Displays transaction data to the user

Testing Your Transaction Dashboard

To test the transaction dashboard:

  1. Start your development server:

  1. Navigate to http://localhost:3000 in your browser

  2. Connect your wallet

  3. Use the LockFundsForm to lock some funds

  4. Observe the transaction appearing in the MyTransactions component.

  5. Notice that the transaction sits in the Pending state. In the next part, we'll implement webhook notifications for immediate updates when enough confirmations are received.

Troubleshooting

Database Issues

If you encounter database-related errors:

  • Ensure the data directory exists and has proper permissions

  • Check that the SQLITE_DB_PATH environment variable is set correctly

  • If using Windows, you might need to install build tools with npm install --global --production windows-build-tools

Transaction Data Not Showing

If transactions don't appear in the dashboard:

  • Verify that your wallet is correctly connected

  • Check the browser console for API errors

  • Make sure you're using the same wallet address that was used to create the transactions

Congratulations! You've completed Part 4 of the guide. Your application now has a fully functioning transaction dashboard to monitor locked funds.

PreviousFund LockingNextReal-time Updates

Last updated

Was this helpful?