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
// src/components/ReactQueryProvider.tsx
"use client";
import { QueryClient, QueryClientProvider } from '@tanstack/react-query';
import { ReactNode } from 'react';
const queryClient = new QueryClient();
export default function ReactQueryProvider({ children }: { children: ReactNode }) {
return (
<QueryClientProvider client={queryClient}>
{children}
</QueryClientProvider>
);
}
2. Update Root Layout
Update the root layout to include the React Query provider src/app/layout.tsx:
// src/app/layout.tsx
import './globals.css';
import type { Metadata } from 'next';
import CustomWeldProvider from '@/components/WeldProvider';
import ReactQueryProvider from '@/components/ReactQueryProvider';
export const metadata: Metadata = {
title: 'Cardano Escrow',
description: 'Lock and unlock funds securely on the Cardano blockchain',
};
export default function RootLayout({
children,
}: {
children: React.ReactNode;
}) {
return (
<html lang="en">
<body>
<ReactQueryProvider>
<CustomWeldProvider>
{children}
</CustomWeldProvider>
</ReactQueryProvider>
</body>
</html>
);
}
Transaction API Endpoint
Let's create an API endpoint to fetch transactions for a specific wallet src/app/api/escrow/transactions/route.ts:
Let's update the submit endpoint to store transaction data in the database src/app/api/escrow/submit/route.ts:
// src/app/api/escrow/submit/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { submitTransaction } from '@/lib/anvil-api';
import { TX_STATUS } from '@/lib/types';
import { upsertWallet, updateTxStatus } from '@/lib/db';
export async function POST(request: NextRequest) {
try {
const body = await request.json();
// Add changeAddress from the request body
const { complete, signature, changeAddress } = body;
// Validate inputs
if (!complete || !signature || !changeAddress) {
return NextResponse.json(
{ error: 'Missing complete transaction or signature' },
{ status: 400 }
);
}
const result = await submitTransaction(signature, complete);
// Normally we would have this as PENDING, but since we're simulating a confirmation
// we set it to CONFIRMED. When we get live updates from the blockchain, we'll update
// the status to PENDING.
updateTxStatus(result.txHash, TX_STATUS.CONFIRMED);
return NextResponse.json({ txHash: result.txHash });
} catch (error: unknown) {
console.error('Error submitting transaction:', error);
const message = error instanceof Error ? error.message : String(error);
return NextResponse.json(
{ error: message || 'Failed to submit transaction' },
{ status: 500 }
);
}
}
Update Transaction Hooks
Add the following functions to src/hooks/useTransactions.ts:
fetchTransactions - Fetches transactions from the API
useTransactionsByWallet - Retrieves transactions for a specific wallet
// src/hooks/useTransactions.ts
"use client";
import { useQuery, useQueryClient } from '@tanstack/react-query';
// ... other imports
// Function to fetch transactions from our API
async function fetchTransactions(wallet: string): Promise<Transaction[]> {
if (!wallet) return [];
const response = await fetch(`/api/escrow/transactions?wallet=${encodeURIComponent(wallet)}`);
if (!response.ok) {
const error = await response.json();
throw new Error(error.error || 'Failed to fetch transactions');
}
return response.json();
}
// Hook to get transactions by wallet
export function useTransactionsByWallet(wallet?: string) {
return useQuery({
queryKey: ['transactions', wallet],
queryFn: () => fetchTransactions(wallet || ''),
enabled: !!wallet, // Only run query if wallet is provided
});
}
Create the MyTransactions Component
Let's create a component to display transaction history src/components/MyTransactions.tsx:
Database Storage: When a transaction is created, it's stored in the SQLite database
API Endpoint: The /api/escrow/transactions endpoint fetches transactions for a specific wallet
React Query: Manages data fetching, caching, and periodic refreshing
MyTransactions Component: Displays transaction data to the user
Testing Your Transaction Dashboard
To test the transaction dashboard:
Start your development server:
npm run dev
Navigate to http://localhost:3000 in your browser
Connect your wallet
Use the LockFundsForm to lock some funds
Observe the transaction appearing in the MyTransactions component.
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.
