# sdm-backend

## Neon Query Log Notes

Seeing unfamiliar queries in Neon (for `pg_catalog` / `information_schema`) is expected with Prisma.
These are metadata and session-management queries, not business-table reads.

Common examples that are normal:
- `SELECT ... FROM pg_catalog...`
- `SELECT ... FROM information_schema...`
- `ROLLBACK`, `RESET ALL`, `DISCARD TEMP`, `SET SESSION AUTHORIZATION DEFAULT`

## What We Configured

The backend Prisma client now:
- Tags sessions with `application_name` (`DB_APPLICATION_NAME`, default `sdm-backend-api`)
- Uses explicit pg pool settings:
	- `PG_POOL_MAX` (default `10`)
	- `PG_IDLE_TIMEOUT_MS` (default `30000`)
	- `PG_CONNECT_TIMEOUT_MS` (default `10000`)

The seed Prisma client uses the same pattern with default application name `sdm-backend-seed` and smaller default pool size.

## Recommended .env Additions

Add these optional env vars in your backend environment:

```dotenv
DB_APPLICATION_NAME=sdm-backend-api
PG_POOL_MAX=10
PG_IDLE_TIMEOUT_MS=30000
PG_CONNECT_TIMEOUT_MS=10000
FORM_CACHE_TTL_MS=300000
FORM_DETAIL_CACHE_TTL_MS=120000
```

Cache env vars reduce repeated reads for public form endpoints:
- `FORM_CACHE_TTL_MS`: cache TTL for `mahayag-rates` and `hierarchy`
- `FORM_DETAIL_CACHE_TTL_MS`: cache TTL for `karyakarta/:code`

OTP policy is centralized in `src/services/otpPolicyService.js`.

## How To Detect Real Duplicate Calls

Use this flow to separate real duplicate API calls from harmless ORM metadata traffic:

1. Trigger one frontend action once.
2. In backend logs, find `Incoming request` / `Request completed` for that route.
3. Compare `x-request-id` values.
4. If two different request IDs appear for one click, duplication is happening before DB (frontend retry, double submit, or multiple fetch calls).
5. If one request ID maps to many queries, inspect the endpoint for multiple Prisma operations or heavy includes.

Tip: in Neon logs, filter by `application_name = sdm-backend-api` to isolate API traffic from scripts and tooling.