# 02. Database Schema & Data Model
## 1. Entity Relationship Overview
The database is designed to support a complex, many-to-many relationship between **Events**, **Gateways**, and **Customers**. This flexibility allows the system to route payments through different vendor credentials based on the business context.
---
## 2. Core Tables
### A. `payment_intents` (Primary Workflow Entry)
Stores the initial request from the ERP.
- `id` (UUID): Primary Key.
- `payment_intent_id` (UUID): Public token used for user redirection.
- `erp_reference_id`: Original ID from the calling ERP system.
- `erp_module`: The specific ERP module (e.g., Accounts, Admission).
- `amount` & `currency`: Financial details.
- `status`: Lifecycle status (`INITIATED`, `SUCCESS`, `FAILED`).
- `return_url` & `notify_url`: Callback points for user redirection and server-to-server notifications.
### B. `payment_transactions` (Audit Trail)
Logs every attempt to pay against a specific intent.
- `payment_intent_id`: Links back to the intent.
- `gateway`: The provider used (e.g., `razorpay`, `ccavenue`).
- `gateway_transaction_id`: The ID returned by the PG.
- `status`: Status of the individual attempt.
- `response` (JSON): The full raw payload from the PG for debugging and reconciliation.
### C. `payment_gateway_credentials` (Configuration)
Stores the API keys and secrets for various gateways.
- `payment_gateway_id`: Links to the `payment_gateways` metadata table.
- `customer_id`: Enables multi-tenancy.
- `test_config` & `live_config` (JSON): Stores vendor-specific keys (e.g., `key_id`, `secret`).
- `mode`: Toggle between `test` and `live`.
---
## 3. Relationships & Mappings
### Gateway Selection Logic
The system uses the `event_credential_mapping` table (Pivot) to determine which credentials can be used for a specific event.
- **Dynamic Routing**: An "Admission" event can be mapped to a "Razorpay" credential for Customer A, while the same event is mapped to "CCAvenue" for Customer B.
### Data Model Diagram (Conceptual)
```mermaid
erDiagram
CUSTOMER ||--o{ PAYMENT_INTENT : "initiates"
CUSTOMER ||--o{ PAYMENT_GATEWAY_CREDENTIAL : "owns"
EVENT ||--o{ PAYMENT_INTENT : "context"
EVENT ||--o{ EVENT_CREDENTIAL_MAPPING : "allowed_creds"
PAYMENT_GATEWAY_CREDENTIAL ||--o{ EVENT_CREDENTIAL_MAPPING : "mapped_to"
PAYMENT_INTENT ||--o{ PAYMENT_TRANSACTION : "logged_as"
PAYMENT_GATEWAY ||--o{ PAYMENT_GATEWAY_CREDENTIAL : "defines"
```
---
## 4. Key Schema Optimizations
- **JSON Column Usage**: Both `test_config` and `live_config` use JSON types, allowing the system to support new gateways with different configuration structures without schema changes.
- **Soft-deletes/Status flags**: Tables like `payment_gateway_credentials` and `event_credential_mapping` use `is_active` flags to safely enable/disable configurations without losing historical data integrity.
- **UUID Primary Keys**: Used for `payment_intents` to ensure global uniqueness and prevent sequential ID guessing.