Crypto Transaction Accounting System Development

Development of Crypto Transaction Accounting System

Crypto transaction accounting differs from traditional in that each transaction requires: fair market value at moment of event, determination of tax lot (which exactly purchased portion was sold), and classification of event type. System should automate this for hundreds of transactions per year.

Database Schema

-- Main transactions table
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    transaction_type VARCHAR(50) NOT NULL, -- BUY, SELL, SWAP, TRANSFER, INCOME, FEE
    
    -- Asset received
    asset_in VARCHAR(20),
    amount_in DECIMAL(36, 18),
    price_in_usd DECIMAL(18, 2), -- price per unit in USD
    value_in_usd DECIMAL(18, 2), -- total value
    
    -- Asset given
    asset_out VARCHAR(20),
    amount_out DECIMAL(36, 18),
    price_out_usd DECIMAL(18, 2),
    value_out_usd DECIMAL(18, 2),
    
    -- Metadata
    fee_amount DECIMAL(36, 18),
    fee_currency VARCHAR(20),
    fee_usd DECIMAL(18, 2),
    
    source VARCHAR(50),      -- BINANCE, COINBASE, ETHEREUM, etc.
    tx_hash VARCHAR(100),
    from_address VARCHAR(100),
    to_address VARCHAR(100),
    
    -- Classification
    tax_category VARCHAR(50), -- DISPOSAL, INCOME, NON_TAXABLE, UNCLASSIFIED
    is_self_transfer BOOLEAN DEFAULT false,
    notes TEXT,
    requires_review BOOLEAN DEFAULT false,
    
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tax lots (portions of purchased assets)
CREATE TABLE tax_lots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    asset VARCHAR(20) NOT NULL,
    acquired_at TIMESTAMPTZ NOT NULL,
    original_amount DECIMAL(36, 18) NOT NULL,
    remaining_amount DECIMAL(36, 18) NOT NULL,
    cost_per_unit_usd DECIMAL(18, 8) NOT NULL,
    total_cost_usd DECIMAL(18, 2) NOT NULL,
    acquisition_transaction_id UUID REFERENCES transactions(id),
    source VARCHAR(50),
    is_long_term BOOLEAN GENERATED ALWAYS AS (
        EXTRACT(EPOCH FROM (NOW() - acquired_at)) > 365 * 86400
    ) STORED
);

-- Realized gains/losses
CREATE TABLE realized_gains (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    disposal_transaction_id UUID REFERENCES transactions(id),
    lot_id UUID REFERENCES tax_lots(id),
    asset VARCHAR(20) NOT NULL,
    amount_disposed DECIMAL(36, 18) NOT NULL,
    proceeds_usd DECIMAL(18, 2) NOT NULL,
    cost_basis_usd DECIMAL(18, 2) NOT NULL,
    gain_loss_usd DECIMAL(18, 2) GENERATED ALWAYS AS (proceeds_usd - cost_basis_usd) STORED,
    acquired_at TIMESTAMPTZ NOT NULL,
    disposed_at TIMESTAMPTZ NOT NULL,
    holding_days INTEGER GENERATED ALWAYS AS (
        EXTRACT(DAY FROM (disposed_at - acquired_at))::INTEGER
    ) STORED,
    is_long_term BOOLEAN NOT NULL,
    cost_basis_method VARCHAR(20) NOT NULL -- FIFO, LIFO, HIFO, AVG_COST
);

Accounting with Different Cost Basis Methods

class LotAccountingService {
  async processDisposal(params: {
    userId: string;
    asset: string;
    amount: number;
    proceedsUSD: number;
    timestamp: Date;
    method: "FIFO" | "LIFO" | "HIFO" | "AVG_COST";
  }): Promise<RealizedGain[]> {
    
    const lots = await this.db.getAvailableLots(params.userId, params.asset, params.method);
    const gains: RealizedGain[] = [];
    let remaining = params.amount;
    
    for (const lot of lots) {
      if (remaining <= 0) break;
      
      const used = Math.min(lot.remaining, remaining);
      const costBasis = (used / lot.originalAmount) * lot.totalCostUSD;
      const proceeds = (used / params.amount) * params.proceedsUSD;
      
      gains.push({
        lotId: lot.id,
        amountDisposed: used,
        proceedsUSD: proceeds,
        costBasisUSD: costBasis,
        gainLossUSD: proceeds - costBasis,
        acquiredAt: lot.acquiredAt,
        isLongTerm: this.isLongTerm(lot.acquiredAt, params.timestamp),
      });
      
      await this.db.reduceLotAmount(lot.id, used);
      remaining -= used;
    }
    
    if (remaining > 0) {
      // Insufficient lots — may be missing import transactions
      await this.db.flagForReview(params.userId, params.asset, remaining);
    }
    
    return gains;
  }
}

Price Conversion

class PriceConversionService {
  private cache = new Map<string, number>();
  
  async getUSDValue(asset: string, amount: number, timestamp: Date): Promise<number> {
    const price = await this.getHistoricalPrice(asset, timestamp);
    return price * amount;
  }
  
  async getHistoricalPrice(asset: string, timestamp: Date): Promise<number> {
    const key = `${asset}:${timestamp.toISOString().split("T")[0]}`;
    if (this.cache.has(key)) return this.cache.get(key)!;
    
    // CoinGecko historical prices
    const date = timestamp.toISOString().split("T")[0].split("-").reverse().join("-");
    const response = await coingecko.get(`/coins/${this.getCoinId(asset)}/history?date=${date}`);
    const price = response.data.market_data.current_price.usd;
    
    this.cache.set(key, price);
    return price;
  }
}

Crypto transaction accounting system with support for FIFO/LIFO/HIFO/Average Cost, automatic price conversion, and realized gains/losses calculation — 3-4 weeks development.