# Database & Prisma

## Schema Overview

The schema lives at `prisma/schema.prisma`. Prisma 7 is used with the `driverAdapters` preview feature and the `@prisma/adapter-neon` or `@prisma/adapter-pg` adapter (configured via `prisma.config.ts`).

---

## Core Data Models

### Master Data (mostly read-only after seeding)

| Model | Description |
|---|---|
| `Pradesh` | Top-level geographic/organizational unit |
| `Mandal` | Sub-unit under a Pradesh |
| `Karyakarta` | Field volunteer; linked to Mandal and optionally to a User |
| `KaryakartaDataTable` | Extended karyakarta data (ID docs, location) — separate from the login Karyakarta table |
| `MahayagRates` | Indian seva place rates |
| `NriMahayagRates` | NRI seva place rates |

### Transactional Data

| Model | Description |
|---|---|
| `User` | Login account for all roles |
| `Registration` | Couple registration |
| `Transaction` | Payment transaction (cash or online) |
| `PaymentSession` | Razorpay session linked to a Transaction |
| `CashflowBunch` | Batch of cash transactions from a Pradesh |
| `NimitSevak` | Volunteer registration |
| `Notification` | In-app notification record |
| `RegistrationOtp` | OTP for new registration phone verification |
| `PersonalPasswordResetOtp` | OTP for personal account password reset |

### Archive / Audit

| Model | Description |
|---|---|
| `DeletedRegistrationArchive` | Full JSON snapshot of hard-deleted registrations |
| `DeletedRegistrationAction` | Log of who deleted a registration and why |
| `PradeshikSantPradeshAssignment` | Many-to-many between PRADESHIK_SANT users and Pradeshes |

---

## Prisma Configuration

`prisma.config.ts` reads the database URL using whichever env file is loaded via `DOTENV_FILE`. This means the same codebase uses `.env` for production and `.env.local` for development by switching which npm script runs.

```ts
// prisma.config.ts — loads DOTENV_FILE for DB URL routing
```

---

## Production Migration Workflow

**Never run `prisma db push` against production from a developer machine.**

### Why
- `db push` issues direct DDL including `ALTER TYPE` for enums.
- PostgreSQL only allows enum changes from the **owner** of that enum type.
- The production app user is not the owner of the `Role` type, so operations like `ALTER TYPE "Role" ADD VALUE 'PRADESHIK_SANT'` fail with `must be owner of type "Role"`.

### Correct flow
1. Develop schema changes locally and verify with `npm run prisma:push:local`.
2. Generate a migration: `npx prisma migrate dev --name <migration_name>`.
3. Apply in production using an owner-capable `DATABASE_URL`: `npm run prisma:migrate:prod`.
4. If production credentials cannot own schema objects, provide the generated SQL to the DB owner to apply manually.

---

## Connection Pool Settings

The backend Prisma client is tagged and pool-configured via env vars:

```dotenv
DB_APPLICATION_NAME=sdm-backend-api   # Identifies traffic in Neon query logs
PG_POOL_MAX=10
PG_IDLE_TIMEOUT_MS=30000
PG_CONNECT_TIMEOUT_MS=10000
```

Seed scripts use `DB_APPLICATION_NAME=sdm-backend-seed` with a smaller pool.

---

## Neon Query Log Noise (Normal)

Seeing queries against `pg_catalog` or `information_schema` in Neon logs is **expected** with Prisma. These are ORM metadata and session-management queries:

- `SELECT ... FROM pg_catalog...`
- `SELECT ... FROM information_schema...`
- `ROLLBACK`, `RESET ALL`, `DISCARD TEMP`, `SET SESSION AUTHORIZATION DEFAULT`

Filter by `application_name = sdm-backend-api` to isolate actual API business queries.

---

## Diagnosing Duplicate DB Calls

1. Trigger one frontend action once.
2. Find the `Incoming request` / `Request completed` log entries in the backend.
3. Compare `x-request-id` values across all log lines for that action.
4. Two different `x-request-id` values for one click = duplication before DB (frontend retry, double-submit, or multiple fetch calls).
5. One `x-request-id` mapping to many queries = heavy includes or multiple Prisma calls inside one endpoint.

---

## Daily DB Backup

`prisma/daily-db-backup.js` is intended to run as a scheduled task (`npm run backup:daily`). It backs up the production database on a daily cadence.

---

## DB Clone (prod → dev)

`private-seeds/clone-prod-db-to-dev.js`:
- Reads production DB URL from `.env` (source: `sdm_db`)
- Reads dev DB URL from `.env.local` (target: `sdm_dev`)
- Uses Docker image `postgres:16-alpine` to stream `pg_dump` into `psql`
- Resets `public` schema first
- Verifies row counts match between source and target

**Requirement**: Docker Desktop must be running. The script does not use local `pg_dump`/`psql` binaries.

Run with: `npm run db:clone-dev`