What is this?

Payments DB is the authoritative database for all payment transactions, payment methods, and related financial data. It uses PostgreSQL 15 and serves as the system of record for payment processing, refunds, and transaction history.

What does it store?

• Payments: Transaction records including amount, currency, status, timestamps, and gateway references
• Payment Methods: Tokenized payment method details (cards, bank accounts) with PCI-compliant storage
• Refunds: Refund records linked to original payment transactions
• Payment Attempts: Historical record of all payment attempts for debugging and analytics
• Relationships: Payment methods belong to customers, payments reference orders and payment methods

Who writes to it?

• PaymentService creates payment records, processes transactions, and manages payment methods
• PaymentGatewayService updates payment status based on gateway responses
• Transaction records are immutable after settlement for audit compliance

Who reads from it?

• FraudDetectionService analyzes payment patterns and transaction history
• BillingService retrieves payment history for invoice generation
• OrdersService checks payment status for order fulfillment
• SubscriptionService validates payment methods for recurring charges
• Finance/Analytics teams for reconciliation and reporting

High-level data model

• A payment belongs to an order and uses a payment_method
• Payment lifecycle tracked via status: INITIATED, AUTHORIZED, CAPTURED, FAILED, REFUNDED, CANCELLED
• Monetary values stored as integer cents to avoid floating-point issues
• All transactions are idempotent using idempotency_key
• Tables: payments, payment_methods, refunds

Common queries

-- Get payment details with method info
SELECT p.*, pm.type AS payment_method_type, pm.last_four, pm.brand
FROM payments p
LEFT JOIN payment_methods pm USING (payment_method_id)
WHERE p.payment_id = $1;

-- List customer payments (for payment history)
SELECT *
FROM payments
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;

-- Find failed payments for retry analysis
SELECT payment_id, order_id, failure_reason, created_at
FROM payments
WHERE status = 'FAILED'
  AND created_at >= now() - interval '24 hours'
ORDER BY created_at DESC;

-- Calculate daily payment volume
SELECT date_trunc('day', captured_at) AS day,
       COUNT(*) AS transaction_count,
       SUM(amount_cents)/100.0 AS total_amount
FROM payments
WHERE status = 'CAPTURED'
  AND captured_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

Lifecycle and data flow

1. Customer initiates payment → PaymentService creates record with status INITIATED
2. Payment gateway authorization → status updated to AUTHORIZED
3. Capture after order fulfillment → status updated to CAPTURED
4. Refunds create separate refunds record linked to original payment
5. All state changes emit domain events for downstream consumers

Security and compliance

• PCI DSS Level 1 compliance: Card data tokenized via payment gateway, no raw card numbers stored
• Encryption at rest: Database encrypted using AWS KMS
• Encryption in transit: TLS 1.3 for all connections
• Access control: Role-based access, audit logging enabled
• PII handling: Customer PII encrypted, access logged for GDPR compliance

Access patterns and guidance

• Use payment_id for point lookups; indexed by order_id and customer_id
• Query by idempotency_key to prevent duplicate charges
• Payments are immutable after CAPTURED status - use refunds for reversals
• Never store raw card numbers - always use gateway tokens
• Use read replicas for analytics queries to avoid impacting transaction processing

Retention and residency

• Retention: 10 years (regulatory requirement for financial records)
• Residency: eu-west-1 (GDPR compliance, data localization)
• Archived records older than 3 years moved to cold storage

Backups and recovery

• Automated continuous backups with 35-day retention
• Point-in-time recovery with 5-minute RPO
• Cross-region backup replication for disaster recovery
• Monthly restore testing to validate RTO objectives

Monitoring and alerts

• Transaction latency monitored (p99 < 200ms)
• Failed payment rate alerts (threshold: > 5%)
• Database connection pool monitoring
• Slow query alerts for queries > 1 second

Requesting access

To request access to the Payments DB:

1. Development/Staging access:

   • Submit an access request via ServiceNow
   • Select “Database Access Request” → “Payments DB (Non-Production)”
   • Specify read-only or read-write access level
   • Approval required from Payment Service team lead
   • Access granted within 24 hours

2. Production access:

   • Production read access requires manager approval + security review
   • Production write access is restricted to PaymentService automated deployments only
   • For emergency production access, contact on-call via PagerDuty
   • All production queries are logged and audited for compliance

3. Analytics/Reporting access:

   • Use the read-replica endpoint for reporting queries
   • Request access via #payments-data Slack channel
   • Data export requests require data governance approval due to PCI/GDPR

Contact: For questions, contact the Payment Service team:

• Slack: #payments-team
• Email: payments-team@company.com
• Team lead: @dboyne

Local development

• Connection string: PAYMENTS_DB_URL environment variable
• Seed data available via npm run seed:payments
• Test payment methods use gateway test tokens
• Local development uses Docker: docker-compose up payments-db

Common issues and troubleshooting

• Duplicate payments: Always include idempotency_key in payment creation
• Stuck authorizations: Run daily job to release authorizations older than 7 days
• Gateway timeouts: Implement retry logic with exponential backoff
• Currency mismatches: Validate order currency matches payment currency

For more information, see the PaymentService documentation and PCI compliance guidelines.