# Database Access And Persistence

## Summary

The service uses MySQL through `mysql-connector-python`. Database operations are mostly schema-driven: endpoint schemas define query type, table, columns, values, clauses, and raw SQL parameters.

Main files:

| File | Responsibility |
| --- | --- |
| `db/database.py` | MySQL connection pool and context manager |
| `db/query.py` | Query builder and execution helper |
| `db/database_operations.py` | Converts schema operation dictionaries into query execution |
| `db/direct_query.py` | Shared raw SQL snippets used by Python helpers |
| `data_persistence.py` | Executes endpoint-level persistence blocks from schema |

## Connection Management

`Database` is implemented as a singleton-style class. It creates a MySQL connection pool once:

```python
pooling.MySQLConnectionPool(
    pool_name='api_wrapper_pool',
    pool_size=5,
    host=os.getenv('MYSQL_HOST'),
    database=os.getenv('MYSQL_DB'),
    user=os.getenv('MYSQL_USER'),
    password=os.getenv('MYSQL_PASSWORD')
)
```

Queries use a dictionary cursor:

```python
self._connection.cursor(dictionary=True)
```

## Query Execution

`QueryExecution.fetch_result(query, params)` executes SQL and returns:

```python
(True, result)
```

Return shape depends on query result:

- one fetched row: returns a dictionary
- multiple fetched rows: returns a list of dictionaries
- insert with last row ID: returns the inserted ID

On exceptions it logs the error and traceback.

## Query Builder

`QueryBuilder.create_query()` supports:

- `select`
- `insert`
- `update`

`QueryBuilder.create_raw_query()` supports raw SQL with resolved params.

### Select

Representative schema:

```json
{
  "queryType": "select",
  "table": "merchant",
  "columns": ["id", "merchant_name"],
  "clause": {
    "where": ["id"],
    "queryParams": ["$merchant_id"],
    "valueOperator": "="
  }
}
```

Resulting SQL pattern:

```sql
select id,merchant_name from merchant where id = %s
```

### Insert

Representative schema:

```json
{
  "queryType": "insert",
  "table": "consumer_application",
  "values": {
    "name": "$request.name",
    "mobile_number": "$request.phone"
  },
  "auto_id": "id"
}
```

Resulting SQL pattern:

```sql
insert into consumer_application (name, mobile_number) values (%s,%s)
```

When `auto_id` is present, the inserted ID is stored in request state as:

```text
{table}_{auto_id}
```

Example:

```text
consumer_application_id
```

### Update

Representative schema:

```json
{
  "queryType": "update",
  "table": "consumer_application",
  "values": {
    "state": "submitted"
  },
  "clause": {
    "where": ["id"],
    "queryParams": ["$consumer_application_id"],
    "valueOperator": "="
  }
}
```

Resulting SQL pattern:

```sql
update consumer_application set state = %s where id = %s
```

### Raw

Representative schema:

```json
{
  "queryType": "raw",
  "dbRaw": "select id from merchant where slug = %s",
  "queryParams": ["$request.slug"],
  "stateKey": "merchant"
}
```

Raw SQL should be reviewed carefully because the query string itself comes from schema.

## Persistence Blocks

Endpoint persistence is read from:

```text
paths.{endpoint}.{method}.persistence
```

`DataPersistence.execute_persistence()` loops over each item.

Two forms are supported.

Unconditional persistence:

```json
{
  "queryType": "insert",
  "table": "example",
  "values": {
    "name": "$request.name"
  }
}
```

Conditional persistence:

```json
{
  "conditions": ["$request.has_offer", "equals", true],
  "true": {
    "queryType": "update",
    "table": "consumer_application",
    "values": {
      "offer_id": "$request.offerId"
    },
    "clause": {
      "where": ["id"],
      "queryParams": ["$consumer_application_id"]
    }
  }
}
```

## Direct Query Constants

`db/direct_query.py` contains shared raw SQL snippets used by `static_functions.py` and `main.py`.

Current snippets support:

- merchant category file name lookup
- application data lookup
- Digio VKYC lookup
- Karza VKYC lookup
- mandate lookup
- EMI and pro forma details
- payment totals
- co-applicant checks
- merchant details
- user lookup
- file lookup

## Tables Touched By Python Helpers

Based on local code, helper functions directly reference tables such as:

- `merchant`
- `merchant_category`
- `company`
- `state`
- `consumer_application`
- `consumer_application_element`
- `consumer_application_files`
- `utility_validation`
- `comments`
- `digio_vkyc`
- `karza_video_webhooks`
- `mandate`
- `emi_calculation`
- `pro_forma_invoice`
- `payment_transactions`
- `online_payments`
- `loan_hypothecation_model`
- `homeville_document_info`
- `pirimid_bank_analysis`
- `users`

Endpoint schemas may reference additional tables.

## Review Checklist For DB Changes

- Prefer parameterized values through `values` or `queryParams`.
- Review all raw SQL in schemas.
- Confirm state keys required by later functions/responses are set.
- Confirm insert auto IDs use consistent `auto_id` naming.
- Confirm one-row vs multi-row result shape is handled correctly.
- Document tables touched in the endpoint doc.
- Add migration or table documentation outside this repo if schema depends on new columns.