Database Integration Guide

Learn how to integrate Payme with your database for transaction management.

Overview

Proper database integration is crucial for:

• Tracking transaction states
• Reconciliation with Payme
• Handling idempotency
• Audit trails
• Reporting

Database Schema

Payme Transactions Table

CREATE TABLE IF NOT EXISTS payme_transactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  payme_id TEXT UNIQUE NOT NULL,           -- Payme transaction ID (24 chars)
  order_id TEXT NOT NULL,                  -- Your order ID
  amount INTEGER NOT NULL,                 -- Amount in tiyin
  state INTEGER NOT NULL DEFAULT 1,        -- Transaction state
  reason INTEGER,                          -- Cancel reason (if cancelled)
  create_time INTEGER NOT NULL,            -- Creation timestamp
  perform_time INTEGER DEFAULT 0,          -- Perform timestamp
  cancel_time INTEGER DEFAULT 0,           -- Cancel timestamp
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_payme_transactions_payme_id ON payme_transactions(payme_id);
CREATE INDEX idx_payme_transactions_order_id ON payme_transactions(order_id);
CREATE INDEX idx_payme_transactions_state ON payme_transactions(state);
CREATE INDEX idx_payme_transactions_create_time ON payme_transactions(create_time);

Orders Table

CREATE TABLE IF NOT EXISTS orders (
  id TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  status TEXT DEFAULT 'pending',           -- pending, awaiting_payment, paid, cancelled
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

Card Tokens Table (for Subscribe API)

CREATE TABLE IF NOT EXISTS card_tokens (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  token TEXT UNIQUE NOT NULL,
  card_number TEXT NOT NULL,               -- Masked (e.g., "860006******6311")
  card_expire TEXT NOT NULL,               -- MMYY format
  verified BOOLEAN DEFAULT FALSE,
  recurrent BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes
CREATE INDEX idx_card_tokens_user_id ON card_tokens(user_id);
CREATE INDEX idx_card_tokens_token ON card_tokens(token);

Bun SQLite Integration

Setup

import { Database } from 'bun:sqlite';

const db = new Database('app.db');

// Enable foreign keys
db.run('PRAGMA foreign_keys = ON');

// Create tables
db.run(`
  CREATE TABLE IF NOT EXISTS payme_transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    payme_id TEXT UNIQUE NOT NULL,
    order_id TEXT NOT NULL,
    amount INTEGER NOT NULL,
    state INTEGER NOT NULL DEFAULT 1,
    reason INTEGER,
    create_time INTEGER NOT NULL,
    perform_time INTEGER DEFAULT 0,
    cancel_time INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

Transaction Operations

import { Database } from 'bun:sqlite';
import { PaymeMerchant, TransactionStates } from '@joyida/payme';

const db = new Database('app.db');
const payme = new PaymeMerchant({
  merchantId: process.env.PAYME_MERCHANT_ID!,
  secretKey: process.env.PAYME_SECRET_KEY!
});

// Save transaction
function saveTransaction(data: {
  paymeId: string;
  orderId: string;
  amount: number;
  state: number;
  createTime: number;
}) {
  db.run(`
    INSERT INTO payme_transactions 
    (payme_id, order_id, amount, state, create_time)
    VALUES (?, ?, ?, ?, ?)
  `, [
    data.paymeId,
    data.orderId,
    data.amount,
    data.state,
    data.createTime
  ]);
}

// Get transaction by Payme ID
function getTransactionByPaymeId(paymeId: string) {
  return db.query(`
    SELECT * FROM payme_transactions WHERE payme_id = ?
  `).get(paymeId);
}

// Get transaction by order ID
function getTransactionByOrderId(orderId: string) {
  return db.query(`
    SELECT * FROM payme_transactions WHERE order_id = ?
  `).get(orderId);
}

// Update transaction state
function updateTransactionState(
  paymeId: string,
  state: number,
  performTime?: number,
  cancelTime?: number,
  reason?: number
) {
  if (performTime) {
    db.run(`
      UPDATE payme_transactions 
      SET state = ?, perform_time = ?, updated_at = CURRENT_TIMESTAMP
      WHERE payme_id = ?
    `, [state, performTime, paymeId]);
  } else if (cancelTime) {
    db.run(`
      UPDATE payme_transactions 
      SET state = ?, cancel_time = ?, reason = ?, updated_at = CURRENT_TIMESTAMP
      WHERE payme_id = ?
    `, [state, cancelTime, reason, paymeId]);
  } else {
    db.run(`
      UPDATE payme_transactions 
      SET state = ?, updated_at = CURRENT_TIMESTAMP
      WHERE payme_id = ?
    `, [state, paymeId]);
  }
}

// Get transactions for reconciliation
function getTransactionsForPeriod(from: number, to: number) {
  return db.query(`
    SELECT * FROM payme_transactions 
    WHERE create_time >= ? AND create_time <= ?
    ORDER BY create_time ASC
  `).all(from, to);
}

Complete Payment Flow

async function processPayment(orderId: string) {
  // 1. Get order
  const order = db.query(`
    SELECT * FROM orders WHERE id = ? AND status = 'pending'
  `).get(orderId);
  
  if (!order) {
    throw new Error('Order not found or already processed');
  }
  
  // 2. Check if payment is possible
  const check = await payme.checkPerformTransaction({
    amount: order.amount,
    account: { order_id: orderId }
  });
  
  if (!check.allow) {
    throw new Error('Payment not allowed');
  }
  
  // 3. Create transaction
  const paymeId = generatePaymeId();
  const created = await payme.createTransaction({
    id: paymeId,
    time: Date.now(),
    amount: order.amount,
    account: { order_id: orderId }
  });
  
  // 4. Save to database
  saveTransaction({
    paymeId,
    orderId,
    amount: order.amount,
    state: created.state,
    createTime: created.create_time
  });
  
  // 5. Update order status
  db.run(`
    UPDATE orders 
    SET status = 'awaiting_payment', updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `, [orderId]);
  
  // 6. Perform transaction
  const performed = await payme.performTransaction({
    id: paymeId
  });
  
  // 7. Update database
  updateTransactionState(paymeId, performed.state, performed.perform_time);
  
  db.run(`
    UPDATE orders 
    SET status = 'paid', updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `, [orderId]);
  
  return performed;
}

PostgreSQL Integration

Setup

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD
});

// Create tables
await pool.query(`
  CREATE TABLE IF NOT EXISTS payme_transactions (
    id SERIAL PRIMARY KEY,
    payme_id VARCHAR(24) UNIQUE NOT NULL,
    order_id VARCHAR(255) NOT NULL,
    amount BIGINT NOT NULL,
    state INTEGER NOT NULL DEFAULT 1,
    reason INTEGER,
    create_time BIGINT NOT NULL,
    perform_time BIGINT DEFAULT 0,
    cancel_time BIGINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )
`);

await pool.query(`
  CREATE INDEX IF NOT EXISTS idx_payme_transactions_payme_id 
  ON payme_transactions(payme_id)
`);

Transaction Operations

// Save transaction
async function saveTransaction(data: {
  paymeId: string;
  orderId: string;
  amount: number;
  state: number;
  createTime: number;
}) {
  await pool.query(`
    INSERT INTO payme_transactions 
    (payme_id, order_id, amount, state, create_time)
    VALUES ($1, $2, $3, $4, $5)
  `, [
    data.paymeId,
    data.orderId,
    data.amount,
    data.state,
    data.createTime
  ]);
}

// Get transaction
async function getTransactionByPaymeId(paymeId: string) {
  const result = await pool.query(`
    SELECT * FROM payme_transactions WHERE payme_id = $1
  `, [paymeId]);
  
  return result.rows[0];
}

// Update transaction state
async function updateTransactionState(
  paymeId: string,
  state: number,
  performTime?: number
) {
  if (performTime) {
    await pool.query(`
      UPDATE payme_transactions 
      SET state = $1, perform_time = $2, updated_at = CURRENT_TIMESTAMP
      WHERE payme_id = $3
    `, [state, performTime, paymeId]);
  } else {
    await pool.query(`
      UPDATE payme_transactions 
      SET state = $1, updated_at = CURRENT_TIMESTAMP
      WHERE payme_id = $2
    `, [state, paymeId]);
  }
}

Idempotency Handling

Payme requires idempotent operations - same request should return same response.

async function createTransactionIdempotent(params: {
  id: string;
  time: number;
  amount: number;
  account: Record<string, string | number>;
}) {
  // Check if transaction already exists
  const existing = getTransactionByPaymeId(params.id);
  
  if (existing) {
    // Return existing transaction
    return {
      create_time: existing.create_time,
      transaction: existing.id.toString(),
      state: existing.state,
      receivers: null
    };
  }
  
  // Create new transaction
  const created = await payme.createTransaction(params);
  
  // Save to database
  saveTransaction({
    paymeId: params.id,
    orderId: params.account.order_id as string,
    amount: params.amount,
    state: created.state,
    createTime: created.create_time
  });
  
  return created;
}

Reconciliation

Reconcile transactions with Payme for a period:

async function reconcileTransactions(from: number, to: number) {
  // Get transactions from Payme
  const statement = await payme.getStatement({ from, to });
  
  // Get transactions from database
  const dbTransactions = getTransactionsForPeriod(from, to);
  
  // Compare
  const paymeIds = new Set(statement.transactions.map(t => t.id));
  const dbIds = new Set(dbTransactions.map(t => t.payme_id));
  
  // Find missing in database
  const missingInDb = statement.transactions.filter(t => !dbIds.has(t.id));
  
  // Find missing in Payme
  const missingInPayme = dbTransactions.filter(t => !paymeIds.has(t.payme_id));
  
  console.log('Reconciliation results:');
  console.log(`Total in Payme: ${statement.transactions.length}`);
  console.log(`Total in DB: ${dbTransactions.length}`);
  console.log(`Missing in DB: ${missingInDb.length}`);
  console.log(`Missing in Payme: ${missingInPayme.length}`);
  
  return {
    paymeTransactions: statement.transactions,
    dbTransactions,
    missingInDb,
    missingInPayme
  };
}

Best Practices

✅ Do's

1. Always save Payme ID

   saveTransaction({
     paymeId: params.id, // ✅ Save this!
     orderId: params.account.order_id,
     amount: params.amount,
     state: created.state,
     createTime: created.create_time
   });

2. Use transactions for consistency

   db.run('BEGIN TRANSACTION');
   try {
     saveTransaction(data);
     updateOrderStatus(orderId, 'paid');
     db.run('COMMIT');
   } catch (error) {
     db.run('ROLLBACK');
     throw error;
   }

3. Index frequently queried columns

   CREATE INDEX idx_payme_transactions_payme_id ON payme_transactions(payme_id);
   CREATE INDEX idx_payme_transactions_order_id ON payme_transactions(order_id);

4. Store all timestamps

   {
     create_time: created.create_time,
     perform_time: performed.perform_time,
     cancel_time: cancelled.cancel_time
   }

5. Implement reconciliation

   // Run daily reconciliation
   setInterval(async () => {
     const yesterday = Date.now() - 86400000;
     await reconcileTransactions(yesterday, Date.now());
   }, 86400000);

❌ Don'ts

1. Don't lose Payme ID
2. Don't skip state updates
3. Don't ignore timestamps
4. Don't forget indexes
5. Don't skip reconciliation

Next Steps

• Learn about Payment Flow
• Explore Error Handling
• Check Complete Examples
• See Testing Guide