38 lines
1.6 KiB
SQL
38 lines
1.6 KiB
SQL
-- SolPay Database Schema
|
|
-- This schema includes the validated_at field needed for transaction validation
|
|
|
|
CREATE DATABASE IF NOT EXISTS solpay;
|
|
USE solpay;
|
|
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
tx VARCHAR(255) NOT NULL UNIQUE,
|
|
target_address VARCHAR(255) NOT NULL,
|
|
amount VARCHAR(255) NOT NULL,
|
|
token_mint VARCHAR(255) NOT NULL,
|
|
token_program VARCHAR(255) NOT NULL,
|
|
sender_address VARCHAR(255) NOT NULL,
|
|
metadata TEXT,
|
|
validated_at TIMESTAMP NULL DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_tx (tx),
|
|
INDEX idx_validated_at (validated_at),
|
|
INDEX idx_sender_address (sender_address),
|
|
INDEX idx_target_address (target_address),
|
|
INDEX idx_created_at (created_at)
|
|
);
|
|
|
|
-- Insert sample data for testing
|
|
INSERT INTO transactions (tx, target_address, amount, token_mint, token_program, sender_address, metadata, validated_at) VALUES
|
|
('sample_tx_1', 'receiver_address_1', '1000000', 'So11111111111111111111111111111111111111111', '11111111111111111111111111111111', 'sender_address_1', 'Sample transaction 1', NULL),
|
|
('sample_tx_2', 'receiver_address_2', '500000', 'So11111111111111111111111111111111111111111', '11111111111111111111111111111111', 'sender_address_2', 'Sample transaction 2', NULL),
|
|
('sample_tx_3', 'receiver_address_3', '750000', 'So11111111111111111111111111111111111111111', '11111111111111111111111111111111', 'sender_address_3', 'Sample transaction 3', NULL);
|
|
|
|
-- Show the table structure
|
|
DESCRIBE transactions;
|
|
|
|
-- Show sample data
|
|
SELECT * FROM transactions;
|