Schema do Banco de Dados

🗄️ Visão Geral do Schema

O Payment Gateway utiliza um schema relacional otimizado para performance, auditoria e compliance, suportando SQLite (desenvolvimento) e PostgreSQL (produção).

📊 Diagrama ERD

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│    merchants    │    │      users      │    │     payments    │
│─────────────────│    │─────────────────│    │─────────────────│
│ id (PK)         │◄───┤ id (PK)         │    │ id (PK)         │
│ name            │    │ merchant_id (FK)│    │ merchant_id (FK)│◄──┐
│ email           │    │ email           │    │ user_id (FK)    │   │
│ status          │    │ role            │    │ amount          │   │
│ created_at      │    │ created_at      │    │ currency        │   │
│ updated_at      │    │ updated_at      │    │ status          │   │
└─────────────────┘    └─────────────────┘    │ provider        │   │
                                              │ created_at      │   │
                                              │ updated_at      │   │
                                              └─────────────────┘   │
                                                        │           │
┌─────────────────┐    ┌─────────────────┐             │           │
│  transactions   │    │  webhooks_log   │             │           │
│─────────────────│    │─────────────────│             │           │
│ id (PK)         │    │ id (PK)         │             │           │
│ payment_id (FK) │◄───┤ payment_id (FK) │◄────────────┘           │
│ provider_tx_id  │    │ provider        │                         │
│ status          │    │ event_type      │                         │
│ amount          │    │ payload         │                         │
│ fees            │    │ signature       │                         │
│ metadata        │    │ status          │                         │
│ created_at      │    │ created_at      │                         │
└─────────────────┘    └─────────────────┘                         │
                                                                   │
┌─────────────────┐    ┌─────────────────┐                         │
│   audit_log     │    │ payment_methods │                         │
│─────────────────│    │─────────────────│                         │
│ id (PK)         │    │ id (PK)         │                         │
│ table_name      │    │ name            │                         │
│ record_id       │    │ type            │                         │
│ action          │    │ provider        │                         │
│ old_values      │    │ enabled         │                         │
│ new_values      │    │ config          │                         │
│ user_id         │    │ created_at      │                         │
│ created_at      │    └─────────────────┘                         │
└─────────────────┘                                                │
                                                                   │
┌─────────────────┐                                                │
│ refresh_tokens  │                                                │
│─────────────────│                                                │
│ id (PK)         │                                                │
│ user_id (FK)    │◄───────────────────────────────────────────────┘
│ token_hash      │
│ expires_at      │
│ revoked         │
│ created_at      │
└─────────────────┘

📋 Tabelas Principais

1. merchants

CREATE TABLE merchants (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    document VARCHAR(50),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
    api_key_hash TEXT,
    webhook_url TEXT,
    webhook_secret TEXT,
    settings JSON DEFAULT '{}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_merchants_email ON merchants(email);
CREATE INDEX idx_merchants_status ON merchants(status);
CREATE INDEX idx_merchants_created_at ON merchants(created_at);

2. users

CREATE TABLE users (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    merchant_id TEXT NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('admin', 'user', 'readonly')),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
    last_login_at DATETIME,
    email_verified BOOLEAN DEFAULT FALSE,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Indexes
CREATE INDEX idx_users_merchant_id ON users(merchant_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);

3. payments

CREATE TABLE payments (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    merchant_id TEXT NOT NULL,
    user_id TEXT,
    external_id VARCHAR(255),
    amount DECIMAL(15,2) NOT NULL CHECK (amount > 0),
    currency CHAR(3) NOT NULL DEFAULT 'BRL',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' 
        CHECK (status IN ('pending', 'processing', 'succeeded', 'failed', 'canceled', 'refunded')),
    payment_method VARCHAR(50) NOT NULL,
    provider VARCHAR(50) NOT NULL,
    provider_payment_id VARCHAR(255),
    description TEXT,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    customer_document VARCHAR(50),
    billing_address JSON,
    shipping_address JSON,
    metadata JSON DEFAULT '{}',
    expires_at DATETIME,
    processed_at DATETIME,
    refunded_at DATETIME,
    refunded_amount DECIMAL(15,2) DEFAULT 0,
    fees DECIMAL(15,2) DEFAULT 0,
    net_amount DECIMAL(15,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Indexes
CREATE INDEX idx_payments_merchant_id ON payments(merchant_id);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_provider ON payments(provider);
CREATE INDEX idx_payments_created_at ON payments(created_at);
CREATE INDEX idx_payments_customer_email ON payments(customer_email);
CREATE INDEX idx_payments_external_id ON payments(external_id);
CREATE INDEX idx_payments_provider_payment_id ON payments(provider_payment_id);

4. transactions

CREATE TABLE transactions (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    payment_id TEXT NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('payment', 'refund', 'chargeback', 'fee')),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'processing', 'succeeded', 'failed')),
    amount DECIMAL(15,2) NOT NULL,
    currency CHAR(3) NOT NULL,
    provider_transaction_id VARCHAR(255),
    provider_response JSON,
    error_code VARCHAR(50),
    error_message TEXT,
    metadata JSON DEFAULT '{}',
    processed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
);

-- Indexes
CREATE INDEX idx_transactions_payment_id ON transactions(payment_id);
CREATE INDEX idx_transactions_type ON transactions(type);
CREATE INDEX idx_transactions_status ON transactions(status);
CREATE INDEX idx_transactions_created_at ON transactions(created_at);
CREATE INDEX idx_transactions_provider_transaction_id ON transactions(provider_transaction_id);

5. webhooks_log

CREATE TABLE webhooks_log (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    payment_id TEXT,
    provider VARCHAR(50) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payload TEXT NOT NULL,
    signature VARCHAR(255),
    status VARCHAR(20) DEFAULT 'pending' 
        CHECK (status IN ('pending', 'processing', 'processed', 'failed', 'ignored')),
    processed_at DATETIME,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
);

-- Indexes
CREATE INDEX idx_webhooks_log_payment_id ON webhooks_log(payment_id);
CREATE INDEX idx_webhooks_log_provider ON webhooks_log(provider);
CREATE INDEX idx_webhooks_log_status ON webhooks_log(status);
CREATE INDEX idx_webhooks_log_created_at ON webhooks_log(created_at);

6. audit_log

CREATE TABLE audit_log (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    table_name VARCHAR(50) NOT NULL,
    record_id TEXT NOT NULL,
    action VARCHAR(20) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values JSON,
    new_values JSON,
    user_id TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Indexes
CREATE INDEX idx_audit_log_table_name ON audit_log(table_name);
CREATE INDEX idx_audit_log_record_id ON audit_log(record_id);
CREATE INDEX idx_audit_log_action ON audit_log(action);
CREATE INDEX idx_audit_log_user_id ON audit_log(user_id);
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);

🔧 Triggers para Auditoria

Trigger para payments

CREATE TRIGGER payments_audit_trigger
AFTER UPDATE ON payments
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (
    table_name, record_id, action, old_values, new_values, created_at
  ) VALUES (
    'payments',
    NEW.id,
    'UPDATE',
    json_object(
      'status', OLD.status,
      'amount', OLD.amount,
      'updated_at', OLD.updated_at
    ),
    json_object(
      'status', NEW.status,
      'amount', NEW.amount,
      'updated_at', NEW.updated_at
    ),
    CURRENT_TIMESTAMP
  );
END;

Trigger para updated_at automático

CREATE TRIGGER payments_updated_at_trigger
BEFORE UPDATE ON payments
FOR EACH ROW
BEGIN
  UPDATE payments SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

📊 Views para Analytics

Payment Analytics View

CREATE VIEW payment_analytics AS
SELECT 
    p.merchant_id,
    DATE(p.created_at) as payment_date,
    p.currency,
    p.provider,
    p.payment_method,
    p.status,
    COUNT(*) as transaction_count,
    SUM(p.amount) as total_amount,
    AVG(p.amount) as avg_amount,
    SUM(p.fees) as total_fees,
    SUM(p.net_amount) as total_net_amount,
    COUNT(CASE WHEN p.status = 'succeeded' THEN 1 END) as successful_count,
    COUNT(CASE WHEN p.status = 'failed' THEN 1 END) as failed_count,
    ROUND(
        COUNT(CASE WHEN p.status = 'succeeded' THEN 1 END) * 100.0 / COUNT(*), 2
    ) as success_rate
FROM payments p
GROUP BY p.merchant_id, DATE(p.created_at), p.currency, p.provider, p.payment_method, p.status;

Provider Performance View

CREATE VIEW provider_performance AS
SELECT 
    provider,
    DATE(created_at) as date,
    COUNT(*) as total_payments,
    SUM(amount) as total_volume,
    AVG(amount) as avg_amount,
    COUNT(CASE WHEN status = 'succeeded' THEN 1 END) as successful_payments,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_payments,
    ROUND(
        COUNT(CASE WHEN status = 'succeeded' THEN 1 END) * 100.0 / COUNT(*), 2
    ) as success_rate,
    AVG(
        CASE 
            WHEN processed_at IS NOT NULL AND created_at IS NOT NULL 
            THEN (julianday(processed_at) - julianday(created_at)) * 24 * 60 * 60 
        END
    ) as avg_processing_time_seconds
FROM payments
GROUP BY provider, DATE(created_at);

🔍 Consultas Úteis

Pagamentos por Status

SELECT 
    status,
    COUNT(*) as count,
    SUM(amount) as total_amount,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM payments), 2) as percentage
FROM payments 
WHERE created_at >= DATE('now', '-30 days')
GROUP BY status
ORDER BY count DESC;

Top Merchants por Volume

SELECT 
    m.name,
    COUNT(p.id) as payment_count,
    SUM(p.amount) as total_volume,
    AVG(p.amount) as avg_payment_amount
FROM merchants m
JOIN payments p ON m.id = p.merchant_id
WHERE p.created_at >= DATE('now', '-30 days')
    AND p.status = 'succeeded'
GROUP BY m.id, m.name
ORDER BY total_volume DESC
LIMIT 10;

Provider Success Rate

SELECT 
    provider,
    COUNT(*) as total_attempts,
    COUNT(CASE WHEN status = 'succeeded' THEN 1 END) as successful,
    ROUND(
        COUNT(CASE WHEN status = 'succeeded' THEN 1 END) * 100.0 / COUNT(*), 2
    ) as success_rate
FROM payments
WHERE created_at >= DATE('now', '-7 days')
GROUP BY provider
ORDER BY success_rate DESC;

🚀 Otimizações de Performance

Particionamento por Data (PostgreSQL)

-- Para tabelas com alto volume, particionar por mês
CREATE TABLE payments_2024_01 PARTITION OF payments
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE payments_2024_02 PARTITION OF payments
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Indexes Compostos

-- Para queries complexas de analytics
CREATE INDEX idx_payments_merchant_status_date 
ON payments(merchant_id, status, created_at);

CREATE INDEX idx_payments_provider_status_date 
ON payments(provider, status, created_at);

📝 Políticas de Retenção

Cleanup de Dados Antigos

-- Delete audit logs older than 2 years
DELETE FROM audit_log 
WHERE created_at < DATE('now', '-2 years');

-- Archive old webhooks (keep last 6 months)
DELETE FROM webhooks_log 
WHERE created_at < DATE('now', '-6 months')
    AND status = 'processed';

---

Última atualização: December 2024