Ma'lumotlar Bazasi Integratsiyasi Qo'llanmasi

Payme ni tranzaktsiya boshqaruvi uchun ma'lumotlar bazangiz bilan integratsiya qilishni o'rganing.

Umumiy Ko'rinish

To'g'ri ma'lumotlar bazasi integratsiyasi muhim:

Tranzaktsiya holatlarini kuzatish
Payme bilan muvofiqlash
Idempotentlikni ishlash
Audit izlarini yuritish
Hisobot tayyorlash

Ma'lumotlar BazasiSxemasi

Payme Tranzaktsiyalari Jadvali

sql

CREATE TABLE IF NOT EXISTS payme_transactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  payme_id TEXT UNIQUE NOT NULL,           -- Payme tranzaktsiya ID (24 belgi)
  order_id TEXT NOT NULL,                  -- Sizning buyurtma ID ingiz
  amount INTEGER NOT NULL,                 -- Tiyinda suma
  state INTEGER NOT NULL DEFAULT 1,        -- Tranzaktsiya holati
  reason INTEGER,                          -- Bekor qilish sababi (agar bekor qilingan bo'lsa)
  create_time INTEGER NOT NULL,            -- Yaratish timestamp
  perform_time INTEGER DEFAULT 0,          -- Bajarish timestamp
  cancel_time INTEGER DEFAULT 0,           -- Bekor qilish timestamp
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indekslar unumdorlik uchun
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);

Buyurtmalar Jadvali

sql

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)
);

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

sql

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,               -- Maskalangan (masalan, "860006******6311")
  card_expire TEXT NOT NULL,               -- MMYY formati
  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)
);

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

Bun SQLite Integratsiyasi

Sozlash

typescript

import { Database } from 'bun:sqlite';

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

// Foreign keylarni yoqish
db.run('PRAGMA foreign_keys = ON');

// Jadvalarni yaratish
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
  )
`);

Tranzaktsiya Operatsiyalari

typescript

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!
});

// Tranzaktsiyani saqlash
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
  ]);
}

// Payme ID bo'yicha tranzaktsiyani olish
function getTransactionByPaymeId(paymeId: string) {
  return db.query(`
    SELECT * FROM payme_transactions WHERE payme_id = ?
  `).get(paymeId);
}

// Buyurtma ID bo'yicha tranzaktsiyani olish
function getTransactionByOrderId(orderId: string) {
  return db.query(`
    SELECT * FROM payme_transactions WHERE order_id = ?
  `).get(orderId);
}

// Tranzaktsiya holatini yangilash
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]);
  }
}

// Muvofiqlash uchun tranzaktsiyalarni olish
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);
}

To'liq To'lov Oqimi

typescript

async function processPayment(orderId: string) {
  // 1. Buyurtmani olish
  const order = db.query(`
    SELECT * FROM orders WHERE id = ? AND status = 'pending'
  `).get(orderId);

  if (!order) {
    throw new Error('Buyurtma topilmadi yoki allaqachon qayta ishlandi');
  }

  // 2. To'lov mumkinligini tekshirish
  const check = await payme.checkPerformTransaction({
    amount: order.amount,
    account: { order_id: orderId }
  });

  if (!check.allow) {
    throw new Error('To\'lov ruxsat etilmagan');
  }

  // 3. Tranzaktsiya yaratish
  const paymeId = generatePaymeId();
  const created = await payme.createTransaction({
    id: paymeId,
    time: Date.now(),
    amount: order.amount,
    account: { order_id: orderId }
  });

  // 4. Ma'lumotlar bazasiga saqlash
  saveTransaction({
    paymeId,
    orderId,
    amount: order.amount,
    state: created.state,
    createTime: created.create_time
  });

  // 5. Buyurtma holatini yangilash
  db.run(`
    UPDATE orders
    SET status = 'awaiting_payment', updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `, [orderId]);

  // 6. Tranzaktsiyani bajarish
  const performed = await payme.performTransaction({
    id: paymeId
  });

  // 7. Ma'lumotlar bazasini yangilash
  updateTransactionState(paymeId, performed.state, performed.perform_time);

  db.run(`
    UPDATE orders
    SET status = 'paid', updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `, [orderId]);

  return performed;
}

PostgreSQL Integratsiyasi

Sozlash

typescript

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
});

// Jadvalarni yaratish
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)
`);

Tranzaktsiya Operatsiyalari

typescript

// Tranzaktsiyani saqlash
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
  ]);
}

// Tranzaktsiyani olish
async function getTransactionByPaymeId(paymeId: string) {
  const result = await pool.query(`
    SELECT * FROM payme_transactions WHERE payme_id = $1
  `, [paymeId]);

  return result.rows[0];
}

// Tranzaktsiya holatini yangilash
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]);
  }
}

Idempotentlikni Ishlash

Payme idempotent operatsiyalarni talab qiladi - bir xil so'rov bir xil javobni qaytarishi kerak.

typescript

async function createTransactionIdempotent(params: {
  id: string;
  time: number;
  amount: number;
  account: Record<string, string | number>;
}) {
  // Tranzaktsiya allaqachon mavjudligini tekshirish
  const existing = getTransactionByPaymeId(params.id);

  if (existing) {
    // Mavjud tranzaktsiyani qaytarish
    return {
      create_time: existing.create_time,
      transaction: existing.id.toString(),
      state: existing.state,
      receivers: null
    };
  }

  // Yangi tranzaktsiya yaratish
  const created = await payme.createTransaction(params);

  // Ma'lumotlar bazasiga saqlash
  saveTransaction({
    paymeId: params.id,
    orderId: params.account.order_id as string,
    amount: params.amount,
    state: created.state,
    createTime: created.create_time
  });

  return created;
}

Muvofiqlash

Tranzaktsiyalarni Payme bilan davr uchun muvofiqlashtirish:

typescript

async function reconcileTransactions(from: number, to: number) {
  // Payme dan tranzaktsiyalarni olish
  const statement = await payme.getStatement({ from, to });

  // Ma'lumotlar bazasidan tranzaktsiyalarni olish
  const dbTransactions = getTransactionsForPeriod(from, to);

  // Taqqoslash
  const paymeIds = new Set(statement.transactions.map(t => t.id));
  const dbIds = new Set(dbTransactions.map(t => t.payme_id));

  // Ma'lumotlar bazasida yo'qolganlarni topish
  const missingInDb = statement.transactions.filter(t => !dbIds.has(t.id));

  // Payme da yo'qolganlarni topish
  const missingInPayme = dbTransactions.filter(t => !paymeIds.has(t.payme_id));

  console.log('Muvofiqlash natijalari:');
  console.log(`Payme da jami: ${statement.transactions.length}`);
  console.log(`Ma\'lumotlar bazasida jami: ${dbTransactions.length}`);
  console.log(`Ma\'lumotlar bazasida yo\'q: ${missingInDb.length}`);
  console.log(`Payme da yo\'q: ${missingInPayme.length}`);

  return {
    paymeTransactions: statement.transactions,
    dbTransactions,
    missingInDb,
    missingInPayme
  };
}

Eng Yaxshi Amaliyotlar

✅ Kerakli ishlar

Doimo Payme ID ni saqlang

typescript

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

Muvofiqlik uchun tranzaktsiyalardan foydalaning

typescript

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

Tez-tez so'raladigan ustunlarni indekslang

sql

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

Barcha timestamplarni saqlang

typescript

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

Muvofiqlashni implementatsiya qiling

typescript

// Kunlik muvofiqlashni ishga tushirish
setInterval(async () => {
  const yesterday = Date.now() - 86400000;
  await reconcileTransactions(yesterday, Date.now());
}, 86400000);

❌ Kerakli emaslar

Payme ID ni yo'qotmang
Holat yangilanishlarini o'tkazib yubormang
Timestamplarni e'tiborsiz qoldirmang
Indekslarni unutmang
Muvofiqlashni o'tkazib yubormang

Keyingi Qadamlar

To'lov Oqimi haqida bilib oling
Xatolarni Ishlash ni o'rganing
To'liq Misollarni tekshiring
Testlash Qo'llanmasini ko'ring

Ma'lumotlar Bazasi Integratsiyasi Qo'llanmasi ​

Umumiy Ko'rinish ​

Ma'lumotlar BazasiSxemasi ​

Payme Tranzaktsiyalari Jadvali ​

Buyurtmalar Jadvali ​

Karta Tokenlari Jadvali (Subscribe API uchun) ​

Bun SQLite Integratsiyasi ​

Sozlash ​

Tranzaktsiya Operatsiyalari ​

To'liq To'lov Oqimi ​

PostgreSQL Integratsiyasi ​

Sozlash ​

Tranzaktsiya Operatsiyalari ​

Idempotentlikni Ishlash ​

Muvofiqlash ​

Eng Yaxshi Amaliyotlar ​

✅ Kerakli ishlar ​

❌ Kerakli emaslar ​

Keyingi Qadamlar ​

Ma'lumotlar Bazasi Integratsiyasi Qo'llanmasi

Umumiy Ko'rinish

Ma'lumotlar BazasiSxemasi

Payme Tranzaktsiyalari Jadvali

Buyurtmalar Jadvali

Karta Tokenlari Jadvali (Subscribe API uchun)

Bun SQLite Integratsiyasi

Sozlash

Tranzaktsiya Operatsiyalari

To'liq To'lov Oqimi

PostgreSQL Integratsiyasi

Sozlash

Tranzaktsiya Operatsiyalari

Idempotentlikni Ishlash

Muvofiqlash

Eng Yaxshi Amaliyotlar

✅ Kerakli ishlar

❌ Kerakli emaslar

Keyingi Qadamlar