platform feat: ledger id null trigger

What

Trigger emails for null set for 'ledgerId' column

Summary by CodeRabbit

New Features
- Added automatic alert email notifications when ledger IDs become null in the database.
- Alerts include rate-limiting to prevent excessive notifications.
Chores
- Updated database driver dependency.
- Added configuration placeholder for alert email recipients.

_{✏️ Tip: You can customize this high-level summary in your review settings.}

Nov 25 '25 14:11 GHkrishna

Walkthrough

Adds a DB trigger that NOTIFYs when org_agents.ledgerId becomes NULL, a PG LISTEN in the API Gateway that publishes a NATS alert when thresholds and env flags permit, a Utility microservice handler to rate‑limit and send alert emails, DTO typing and DI module updates, small env/sample comments, and a pg dependency bump.

Changes

Cohort / File(s)	Summary
Environment configuration `/.env.demo`, `/.env.sample`	Added commented placeholders `DB_ALERT_ENABLE` and `DB_ALERT_EMAILS` documenting alert enablement and recipient configuration.
Database migration `libs/prisma-service/prisma/migrations/.../migration.sql`	Added `alert_ledger_null()` function and `ledger_null_trigger` on `org_agents` to `NOTIFY 'ledger_null'` when `ledgerId` transitions non-NULL → NULL.
API Gateway — startup & utilities `apps/api-gateway/src/main.ts`, `apps/api-gateway/src/utilities/utilities.service.ts`	Startup logs DB alert enablement; UtilitiesService adds PgClient LISTEN on `ledger_null`, queries `org_agents` counts on notification, computes NULL %, and publishes NATS message `alert-db-ledgerId-null` when enabled and threshold exceeded; implements onModuleInit/onModuleDestroy and duplicate-alert guard.
Utility microservice — controller & service `apps/utility/src/utilities.controller.ts`, `apps/utility/src/utilities.service.ts`	Controller: new `@MessagePattern({ cmd: 'alert-db-ledgerId-null' })` handler forwarding `EmailDto`. Service: extends `BaseService`, injects `EmailService`, and adds `handleLedgerAlert(emailDto)` with retry and 2‑hour rate-limit (`lastAlertTime`, `isSendingAlert`) to send alert emails.
Email DTO & helpers `libs/common/src/dtos/email.dto.ts`, `libs/common/src/resend-helper-file.ts`	`EmailDto.emailTo` widened from `string` to `string
Prisma service module & usage `libs/prisma-service/src/prisma-service.module.ts`, `libs/prisma-service/src/prisma-service.service.ts`	Added `CommonModule` to `PrismaServiceModule` imports; removed public `UserDevicesRepository` member from `PrismaService`.
Module DI adjustments `apps/ledger/src/ledger.module.ts`, `libs/org-roles/src/org-roles.module.ts`	Replaced direct `PrismaService` provider usage with `PrismaServiceModule` in module imports and removed `PrismaService` from providers.
Dependency `package.json`	Bumped `pg` dependency from `^8.11.2` to `^8.16.3`.

Sequence Diagram(s)

sequenceDiagram
    participant DB as PostgreSQL
    participant Trigger as DB Trigger
    participant API as API Gateway (UtilitiesService)
    participant NATS as NATS
    participant Utility as Utility Microservice
    participant Email as EmailService

    DB->>Trigger: UPDATE org_agents (ledgerId non-NULL → NULL)
    Trigger->>DB: NOTIFY ledger_null (agentId, orgId, ts)
    DB-->>API: LISTEN notification delivered

    API->>DB: Query org_agents (total, ledgerId IS NULL)
    API->>API: Compute % NULL, check `DB_ALERT_ENABLE` and threshold
    alt Alert required
        API->>NATS: publish { cmd: 'alert-db-ledgerId-null', emailDto }
        NATS->>Utility: deliver message
        Utility->>Utility: enforce cooldown & isSendingAlert guard
        Utility->>Email: send email(payload) with retries
        Email-->>Utility: delivery result
    else No alert
        API-->>API: no further action
    end

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~60 minutes

Pay extra attention to:
- apps/api-gateway/src/utilities/utilities.service.ts — PgClient lifecycle, LISTEN flow, notification parsing, query correctness, threshold logic, NATS payload shape.
- libs/prisma-service/prisma/migrations/.../migration.sql — trigger WHEN clause and NOTIFY payload shape.
- apps/utility/src/utilities.service.ts — retry logic, cooldown/rate-limit correctness, concurrency guards, EmailService integration.
- libs/common/src/dtos/email.dto.ts — widened emailTo type impact on callers and message handlers.

Possibly related PRs

credebl/platform#1512 — Related changes to EmailService, EmailDto and resend helper intersect with the new alert-email flow.
credebl/platform#1524 — Related changes touching PrismaService and Prisma configuration that align with PrismaServiceModule adjustments.

Suggested labels

enhancement

Suggested reviewers

RinkalBhojani
tipusinghaw
KambleSahil3

Poem

🐇 I nibbled at a ledger line, a tiny spark,
The DB whispered “null” and sent a mark.
NATS carried news across the night,
I stitched a mail and sent the light. ✉️

Pre-merge checks and finishing touches

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 0.00% which is insufficient. The required threshold is 80.00%.	You can run `@coderabbitai generate docstrings` to improve docstring coverage.

✅ Passed checks (2 passed)

Check name	Status	Explanation
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title check	✅ Passed	The title 'feat: ledger id null trigger' directly describes the main change: implementing a database trigger that activates when ledgerId becomes null, which aligns with the PR's core objective and the migration that creates the ledger_null_trigger.

✨ Finishing touches

[ ] 📝 Generate docstrings

🧪 Generate unit tests (beta)

[ ] Create PR with unit tests
[ ] Post copyable unit tests in a comment
[ ] Commit unit tests in branch feat/ledgerId-null-trigger

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

Nov 25 '25 14:11 coderabbitai[bot]

@coderabbitai review

Nov 27 '25 14:11 GHkrishna

✅ Actions performed

Review triggered.