# Database

PostgreSQL configuration, schema management, migrations, and performance tuning for FluxiQ PIX.

## Prerequisites

- PostgreSQL 16 installed and accessible
- Extensions `uuid-ossp` and `pg_trgm` available
- Database user with CREATE/ALTER/DROP privileges

## Schema Overview

FluxiQ PIX uses 8 PostgreSQL schemas to separate concerns:

| Schema | Tables | Purpose |
|--------|--------|---------|
| `monetarie_auth` | ~10 | Users, sessions, MFA, institutions, groups, permissions |
| `monetarie_dict` | ~8 | PIX keys, claims, infraction reports, CID sync |
| `monetarie_spi` | ~12 | Messages (transactions), payments, accounts, balances |
| `monetarie_spi_ref` | ~6 | Reference data: banks, status codes, message types |
| `monetarie_spi_msg` | ~4 | Crypto keys, XML messages |
| `monetarie_audit` | ~4 | XML archive, BACEN API validations |
| `monetarie_settlement` | ~10 | Netting, reconciliation, QR codes, GL accounting |
| `bacen_simulator` | ~24 | Simulator config, scenarios, test runs |

## Connection Configuration

```elixir
# config/runtime.exs
config :shared, Shared.Repo,
  hostname: System.get_env("DB_HOST", "localhost"),
  port: String.to_integer(System.get_env("DB_PORT", "5432")),
  username: System.get_env("DB_USER", "postgres"),
  password: System.get_env("DB_PASS", "postgres"),
  database: System.get_env("DB_NAME", "monetarie"),
  pool_size: String.to_integer(System.get_env("POOL_SIZE", "250"))
```

### Connection Pool Sizing

| Environment | POOL_SIZE | Repos | Pods | Total Connections |
|-------------|-----------|-------|------|-------------------|
| Development | 10 | 4 | 1 | 40 |
| Staging | 50 | 4 | 2 | 400 |
| Production | 200 | 4 | 2 | 1,600 |
| High TPS | 250 | 4 | 4 | 4,000 |

Ensure `max_connections` on PostgreSQL exceeds total connections by at least 10%.

## Migrations

FluxiQ PIX has 25 migrations. Run them with:

```bash
# Development
cd backend && mix ecto.migrate

# Production (Kubernetes)
kubectl exec -n pix deployment/pix-backend -- bin/monetarie_pix eval "Shared.Release.migrate()"
```

### Migration Summary

| # | Timestamp | Purpose |
|---|-----------|---------|
| 1 | 20260201000001 | Initial schemas and tables |
| 2 | 20260203000001 | 13 gap resolution tables |
| 3 | 20260203000002 | Reference data seeding |
| 4 | 20260203100001 | PostgreSQL extensions and ENUMs |
| 5-9 | 20260203100002-06 | Missing tables across schemas |
| 10 | 20260203100007 | DB functions, triggers, views |
| 11 | 20260203100008 | BACEN simulator tables |
| 12 | 20260203100009 | Seed SQL loading |
| 13 | 20260206000001 | Auth institutions and groups |
| 14-16 | 20260206200001-03 | BACEN compliance, domain values, alcada |
| 17 | 20260207300001 | MFA fields |
| 18 | 20260207400001 | GL accounting (44 COSIF + 6 cost centers) |
| 19 | 20260207500001 | System config (13 parameters) |
| 20 | 20260208000001 | Performance indexes (E2E ID, status, dates) |
| 21 | 20260208100001 | Account ISPB, user_groups junction |
| 22 | 20260209000001 | Recreate infraction_reports (UUID PK, MED 2.0) |
| 23 | 20260209100001 | TPS indexes (8: branch, CPF, GIN trigram) |
| 24 | 20260209200001 | Audit fixes (reported_cpf_cnpj, user_groups fields) |
| 25 | 20260210000001 | Partitioned tables (activity_log, login_history) |

## ENUMs

| Schema | Enum | Values |
|--------|------|--------|
| `monetarie_dict` | `key_type` | CPF, CNPJ, PHONE, EMAIL, EVP |
| `monetarie_spi` | `debit_credit` | DEBIT, CREDIT |
| `monetarie_spi` | `message_direction` | OUTBOUND, INBOUND |

## Performance Tuning

### Key Indexes (Migration 20, 23)

| Table | Index | Type | Purpose |
|-------|-------|------|---------|
| `monetarie_spi.messages` | `end_to_end_id::text` | GIN trigram | Fuzzy E2E ID search |
| `monetarie_spi.messages` | `status_id` | B-tree | Status filtering |
| `monetarie_spi.messages` | `branch_id, status_id` | Composite | Branch dashboard |
| `monetarie_spi.messages` | `inserted_at` | B-tree | Date range queries |
| `monetarie_spi.payments` | `debtor_cpf_cnpj` | B-tree | CPF/CNPJ lookup |
| `monetarie_dict.dict_keys` | `key_type, key_value` | Composite | Key lookup |

::: warning GIN TRIGRAM ON CHAR COLUMNS
`gin_trgm_ops` does not accept CHAR type columns. You must cast to text: `(end_to_end_id::text) gin_trgm_ops`.
:::

### PostgreSQL Configuration (Production)

```sql
ALTER SYSTEM SET max_connections = 6000;
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET random_page_cost = 1.1;  -- SSD storage
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET max_wal_size = '4GB';
```

## Table Partitioning

Migration 25 creates monthly partitions for high-volume audit tables:

| Table | Partition Key | Strategy |
|-------|--------------|----------|
| `monetarie_audit.activity_log` | `created_at` | Monthly RANGE |
| `monetarie_auth.login_history` | `login_at` | Monthly RANGE |

The `PartitionManager` GenServer automatically creates partitions 60 days ahead and purges expired records.

## Seed Data

```bash
mix run apps/shared/priv/repo/seeds.exs
```

| Data | Count |
|------|-------|
| Participants | 10 |
| Users | 7 |
| Reference banks | 369 (+98 additional) |
| PIX keys | 277 |
| DICT operations | 15,442 |
| Transactions (30d) | 1,000+ |
| COSIF accounts | 44 |
| XSD schemas | 27 |
| Error codes | 161 |

## Expected Outcome

After configuring the database:

- All 25 migrations applied without errors
- 8 schemas created with all tables and indexes
- Seed data loaded (if desired)
- Connection pool sized for your deployment model
- Partitioning active for audit tables