CommunityLend: Neighborhood Lending Platform

CommunityLend — Product Requirements Document #

Version: 1.0 (Final) · Document type: Buildable specification for an AI coding agent or engineering team

Overview #

CommunityLend is a subscription web application for apartment communities, offices, row houses and gated communities in Indian cities. Members aged 18 and over list books, toys, games and other household items; neighbours in the same community request to borrow them; the owner approves; the borrower pays a refundable security deposit in-app; the handoff and return take place at fixed pickup points set by the community admin; the deposit is refunded automatically once the owner confirms a good-condition return; and the community admin decides damage or loss disputes from the photos and chat history. Every member pays their own monthly or annual subscription. Notifications are delivered in-app, by Web Push and by email.

This document is the complete contract for building the product. It is self-contained: every rule, value, state, endpoint, table, job and screen is specified here, and every decision that the original idea left open has been made and recorded. It is organised so that each concern is defined in exactly one section and referenced by number everywhere else. Section 1 lists the few customisation choices with their defaults; Section 30 tells an executor how to work through the document.

Scope at launch: one responsive web app (installable as a PWA) plus a versioned REST API designed so that native mobile apps can be added later without a rewrite. Explicitly out of scope: cross-community borrowing, native iOS/Android apps, smart lockers or unattended handoffs, SMS, a free tier, shipping or delivery, government ID verification, and insurance products beyond the deposit mechanism.

Table of Contents #

1. Before You Start
2. Project Overview & Vision
3. Technology Stack & Architecture
4. Conventions & Best Practices
5. API Design Conventions
6. Data Model & Schema
7. Configuration & Environment Variables
8. Background Jobs & Scheduling
9. Accounts, Authentication & Profiles
10. Communities & Membership
11. Pickup Points
12. Community Admin Dashboard
13. Platform Operator Console
14. Item Listings & Catalog
15. Search & Discovery
16. Borrow Requests & Loan Lifecycle
17. Handoff & Return at Pickup Points
18. In-App Messaging
19. Ratings & Reviews
20. Payments Integration (Razorpay)
21. Security Deposits & Refunds
22. Subscription Billing
23. Disputes & Deposit Forfeiture
24. Notifications (Push & Email)
25. Frontend Architecture & Design System
26. Security, Privacy & Compliance
27. Observability, Deployment & Operations
28. Testing Strategy
29. Milestones & Execution Plan
30. Executor Instructions
31. Appendices

1. Before You Start #

This section is a customization checklist for the team that builds from this specification. Every question below has a working default. A default is a decision: if nobody overrides it, build the default exactly as stated. Do not leave any of these open while building — if a value is not overridden before the corresponding section is implemented, use the default.

When a value is chosen (default or override), record it in a file named DECISIONS.md at the repository root, in a Key | Value table, one row per question below, using the same "Key" wording as the left column of the table. This file is created by the development team; it is not part of this document and no section of this document depends on it existing before Section 3's setup steps are run — it is a record for humans, not a runtime configuration source. Runtime configuration is environment variables (Section 7). Section 30.2 describes the file's full template.

1.1 Customization questions #

1.2 How to use this table #

• Read every row. For each, either accept the default or record an explicit override in DECISIONS.md before building the section(s) listed in the right-hand column.
• Do not invent a new question-and-default pair mid-build; if something is genuinely undecided and not covered by this table or by the rules in the sections that follow, that is a gap in this document, not a place for silent improvisation — flag it in DECISIONS.md under a "Follow-ups" heading and pick the most conservative option (the one that limits scope, cost, or liability) so the build is not blocked.
• Prices, limits, and flags in this table are runtime values (environment variables or database rows), not compiled constants. Section 7 lists every environment variable; Section 6 lists every database default. Where this table and a later section both state a number, they agree — this table exists for discoverability, not as a second source of truth. If they ever disagree, the owning section in 1.3 wins.

1.3 Ownership of decisions #

Each concern below is defined in exactly one owning section. Every other section cites the owning section by number instead of restating the rule, and may repeat a value inline only for readability. If two sections disagree, resolve the conflict in favour of the owning section in this table and record the resolution in DECISIONS.md under a "Resolved conflicts" heading (Section 30.2 repeats this rule for the executor).

2. Project Overview & Vision #

2.1 Problem #

Households in Indian apartment communities, offices, row houses, and gated communities routinely buy books, toys, board games, and other items that get used a handful of times and then sit idle. Neighbours in the same building or campus often want to borrow exactly that kind of item for a short period, but there is no trusted, low-friction way to find out what is available next door, agree on terms, hand the item over safely, and get a deposit back without an argument. Informal lending among neighbours works occasionally but breaks down at scale: nobody remembers who has what, there is no record of condition at handoff, and disputes over damage or non-return have no neutral process.

2.2 Solution #

CommunityLend is a subscription web application, used in the browser on desktop and mobile and installable as a Progressive Web App, that lets members of the same community list items they own, lets neighbours request to borrow them, and manages the entire lifecycle: approval, a refundable security deposit collected in-app, a scheduled handoff at a pickup point the community itself defines (lobby desk, security gate), a return at the same kind of point, an automatic deposit refund when the return is confirmed in good condition, and a structured dispute process — decided by a community admin who can review the loan's chat history and any submitted photos — when a return is contested. Every member pays for their own subscription; there is no free tier.

2.3 Target users #

1. Members — the primary user. Any signed-up, subscribed, 18+ adult who joins one or more communities to lend items they own and/or borrow items neighbours have listed.
2. Community admins — members who also administer one community: approve join requests, define pickup points, moderate listings, and resolve disputes raised within their community. A community admin is always also a member and uses every member-facing feature themselves.
3. Platform operators — CommunityLend staff. They do not belong to any community by virtue of their role. They operate the platform: manage subscription plans, investigate and manually adjust payments/refunds/payouts, decide disputes that escalate past the community admin level, moderate users, communities and reported content, and toggle feature flags. See Section 13.
4. Prospective members (visitors) — people who have not yet signed up or who have signed up but not yet subscribed. They can view marketing/paywall content and complete signup and email verification, but cannot use any lending feature until subscribed (Section 22.4 lists the exact boundary).

2.4 The community model #

A community is the trust boundary of the product. Every listing, borrow request, message, rating, and dispute happens inside exactly one community; there is no cross-community borrowing (explicitly out of scope — see 2.6). A community is created by a member, who becomes its first admin; up to two more members can be promoted to admin (three admins maximum per community, Section 10). Membership is not automatic: a user requests to join (via an 8-character join code shared by the admin, or by finding the community through name/city search) and supplies a unit identifier (flat number, office number, house number); an admin approves or rejects the request. A user may hold up to three active community memberships at a time (Section 10).

2.5 In scope #

• Account signup, email verification, login/logout, password reset, profile editing, self-service account deletion with a cooling-off period.
• Creating and joining communities; admin approval workflow; promoting/demoting admins; removing members; leaving a community.
• Admin-defined pickup points with names, location hints, and operating hours; 30-minute pickup slots inside those hours.
• Listing items (books, toys, games, other) with photos, condition, category-specific attributes, and an optional refundable deposit.
• Searching and browsing items within a member's own community.
• Requesting to borrow, owner approval/decline, deposit payment, scheduled handoff at a pickup point using a handoff code, optional handoff and return photos, rescheduling of the pickup slot, return marking, return confirmation, automatic deposit refund.
• One loan extension per loan, subject to owner approval.
• In-app messaging scoped to a loan.
• Post-loan double-blind ratings of the counterparty and, by the borrower, of item condition accuracy; reporting of abusive messages and ratings.
• Razorpay-based deposit payments, refunds, and recurring subscription billing.
• Damage/loss disputes: borrower response window, community-admin decision, escalation to the platform operator when needed, forfeiture payouts to owners.
• Push and email notifications, each governed by a per-category user preference.
• A community admin dashboard and a platform operator console.
• A versioned REST API (/api/v1) as the sole interface between the browser/PWA client and the backend, so that a future native client can be built against the same API without backend changes.

2.6 Out of scope #

The following are explicitly not designed anywhere in this specification. Where a later section would naturally touch one of these, it says so in one sentence, cites this list as "Section 2.6", and stops there; none of them get a data model, an endpoint, or a UI.

• Cross-community borrowing. An item is only ever visible and requestable inside the community it was listed in.
• Native iOS/Android applications. The REST API is versioned specifically to make a future native app possible without a backend rewrite, but building one is not part of this project.
• Smart lockers or any unattended/automated pickup mechanism. All handoffs and returns are between two people, in person, at a pickup point.
• SMS notifications. Notifications are push (Web Push) and email only.
• A free tier or usage-based pricing. Every member pays the same flat monthly or annual subscription price to use the product at all.
• Shipping or courier delivery of items between members.
• Government ID verification. Age is a self-declared checkbox; no document upload or third-party identity check.
• Insurance products. The refundable deposit is the only financial protection against loss or damage; CommunityLend does not sell or broker insurance.
• Product analytics beyond server logs. No analytics events table or endpoint exists at launch; the client exposes a no-op track() hook so one can be added later (Section 25).

2.7 Success metrics #

These are the numeric targets the product is designed against. They inform default limits and timers elsewhere in this document (e.g., approval and payment deadlines in Section 16) but are not themselves enforced by application code except where a later section says so explicitly.

"Terminal successful state" is a product metric, not a loan status: resolved with no_forfeit is counted as successful for reporting purposes only, and Section 19 still treats every returned or resolved loan as a completed exchange for rating purposes.

2.8 Key user journeys #

Times in the journeys are shown in Asia/Kolkata; every pickup slot is exactly 30 minutes long (Section 11). Every id in this document is a UUID v7 string (4.8).

2.8.1 Member: sign up through first completed loan (borrower side) #

1. Visitor signs up with email and password, confirms the 18+ checkbox, and accepts the current Terms version (Section 9).
2. Visitor verifies their email via a 6-digit OTP (Section 9).
3. User subscribes to a monthly or annual plan via Razorpay Subscriptions (Section 22); without a subscription granting full_access the user can only view the paywall, manage their own account, and complete loans already in motion (Section 22.4).
4. User joins a community using a join code or by searching name + city; status is pending until a community admin approves it (Section 10).
5. User browses or searches items within the community (Section 15) and opens an item they want.
6. User submits a loan request with the number of days, a preferred pickup point and a 30-minute slot, for example Saturday 10:00–10:30 (Section 16).
7. Owner approves the request within 72 hours, confirming or changing the pickup point and slot.
8. If the item has a deposit, the borrower pays it via Razorpay Checkout within 24 hours of approval; the loan reaches awaiting_pickup (immediately, with no payment step, if the deposit is zero).
9. At the pickup point, the owner hands the item to the borrower; the borrower shows the 6-digit handoff code from the app and the owner enters it to confirm handover; the loan becomes active (Section 17).
10. Before the due date, the borrower may request one extension of up to 14 days, which the owner approves or declines (Section 16).
11. At return, the borrower marks the item returned in the app at the pickup point (optionally with photos); the owner has 48 hours to confirm good condition or open a dispute (Section 17).
12. On confirmation (explicit or automatic after 48 hours), the deposit refund is initiated via Razorpay Refunds (Section 21).
13. Both parties rate each other; the borrower also rates the accuracy of the stated item condition; ratings are revealed once both are submitted or 7 days after the loan closed, whichever comes first (Section 19).

2.8.2 Member: list an item (owner side) #

1. Subscribed member in an active community creates a listing: category, title, description, condition, category-specific attributes, 1–6 photos, deposit amount, maximum borrow days, preferred pickup point (Section 14).
2. If the community requires admin listing review, the item starts hidden_by_admin and the community admins are asked to review it; otherwise it is available as soon as at least one photo has been processed.
3. Owner receives and approves/declines incoming loan requests, confirms handoff and return as in 2.8.1, and receives the deposit-backed protection if the item comes back damaged (Section 23).

2.8.3 Community admin journey #

1. Admin creates a community (becoming its first admin) or is promoted by an existing admin.
2. Admin defines one or more pickup points with names, location hints, and operating hours (Section 11).
3. Admin reviews and approves/rejects join requests as they arrive, checking the submitted unit identifier (Section 10).
4. Admin optionally moderates listings (hide/unhide) and monitors loans and disputes via the community admin dashboard (Section 12).
5. When a dispute is raised in the admin's community, the admin reviews the loan's chat transcript and any submitted photos and decides no forfeiture / partial forfeiture / full forfeiture (Section 23). If the owner or borrower in the dispute is themselves an admin of that community, the dispute is created already escalated to the platform operator; a dispute nobody decides within 14 days also escalates.

2.8.4 Dispute journey #

1. Within 48 hours of the borrower marking an item returned — or, for a suspected loss on a loan that was never marked returned, from 14 days after the due date — the owner opens a dispute stating a type (damage, loss, other), a description, a claimed amount up to the deposit, and, for damage and other, at least one evidence photo (Section 23).
2. The borrower is notified and has 48 hours to respond with text and optional photos.
3. The community admin (or, on escalation, the platform operator) reviews the loan's message history and all submitted evidence and resolves the dispute with one of: no forfeiture, partial forfeiture (an amount below the claim), full forfeiture (the claimed amount).
4. The forfeited amount is paid out to the owner (Section 21, Section 23); the deposit minus the forfeited amount is refunded to the borrower.

2.9 Constraints #

• Operates only in India; all money is INR; all deposits, subscription charges, refunds, and payouts flow through Razorpay/RazorpayX.
• Members must be 18 or older, confirmed by a signup-time checkbox; no document-based identity verification is performed.
• The UI ships in English only at launch; user-facing copy is centralized so additional locales can be added later without restructuring the frontend (Section 25).
• All data is stored in an Indian region (Section 3, Section 26, Section 27).
• No feature in this specification depends on SMS, native app shells, smart lockers, shipping, government ID checks, or insurance — see 2.6.

2.10 Glossary #

A complete glossary of terms and abbreviations used throughout this specification is in 31.1.

2.11 Non-goals #

CommunityLend does not aim to be a general-purpose marketplace, a peer-to-peer sales platform, or a classifieds site. Items are only ever lent and returned, never sold, within this specification. It does not aim to replace a building's existing security/access-control system; pickup points are informational locations defined by the admin, not integrated with any physical access hardware.

2.12 Future phase (not part of this specification) #

A native mobile client (iOS/Android) is anticipated as a later phase but is explicitly not built as part of this specification (2.6). The reason the REST API (Section 5) is versioned, uses bearer-token authentication as a first-class option alongside cookies, and keeps all business logic in a framework-agnostic service layer (Section 3) is so that a native client — or a split-out standalone API host — can be added later by building against /api/v1 without changing any backend code described here.

3. Technology Stack & Architecture #

This section is the sole owner of dependency versions and of the repository layout. Every other section refers back to "the version in Section 3" instead of restating a number, and to 3.5 for where a file lives.

3.1 Version floor #

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

Redis 8.x is tri-licensed (RSALv2 / SSPLv1 / AGPLv3); CommunityLend uses it unmodified as an external service and never links or redistributes it, so no distribution obligation arises, and Valkey (BSD-3-Clause), which BullMQ supports, is a drop-in alternative if the licence position changes. Section 31.10 records the same position in the licence table. Container image tags elsewhere in this document (node:24-slim, postgres:18, redis:8) are these major lines and are never pinned to a patch.

3.2 Architecture overview #

                     ┌────────────────────────────┐
                     │   Browser / installed PWA   │
                     │  (React 19.x, service worker│
                     │   for push + offline shell) │
                     └──────────────┬───────────────┘
                                    │ HTTPS
                                    ▼
                     ┌────────────────────────────┐
                     │   Next.js app (single host)  │
                     │  ┌──────────────────────┐   │
                     │  │  UI (App Router pages) │   │
                     │  └──────────────────────┘   │
                     │  ┌──────────────────────┐   │
                     │  │ /api/v1/* Route       │   │
                     │  │ Handlers (thin)       │   │
                     │  └──────────┬───────────┘   │
                     │             │ calls          │
                     │  ┌──────────▼───────────┐   │
                     │  │ src/server/<module>/   │   │
                     │  │ service.ts (all logic) │   │
                     │  └──────────┬───────────┘   │
                     └─────────────┼────────────────┘
                                   │
        ┌──────────────┬──────────┼──────────┬───────────────┬───────────────┐
        ▼              ▼          ▼           ▼               ▼               ▼
  ┌──────────┐   ┌──────────┐ ┌───────┐  ┌──────────┐   ┌──────────┐   ┌────────────┐
  │PostgreSQL│   │  Redis   │ │  S3   │  │ Razorpay │   │  Resend  │   │ Web Push /  │
  │  18.x    │   │  8.x     │ │(objects)│ │ APIs +   │   │ (email)  │   │ VAPID       │
  │ (Prisma) │   │ (BullMQ, │ │       │  │ webhooks │   │          │   │             │
  │          │   │ counters)│ │       │  │          │   │          │   │             │
  └──────────┘   └────┬─────┘ └───────┘  └──────────┘   └──────────┘   └────────────┘
                       │
                       ▼
               ┌────────────────┐
               │  apps/worker    │
               │ (BullMQ workers:│
               │  deadline       │
               │  sweeps,        │
               │  reminders,     │
               │  webhook        │
               │  processing,    │
               │  email/push     │
               │  dispatch)      │
               └────────────────┘

One Next.js application serves both the member/admin/operator UI and the versioned REST API (/api/v1/*) as Route Handlers. A separate long-running Node process (apps/worker) consumes BullMQ queues backed by Redis for everything that must not block an HTTP request: deadline sweeps, scheduled reminders, inbound webhook processing, image normalisation, and outbound email/push dispatch. Redis also holds short-lived counters and reservations (rate-limit windows in 5.10, login-failure counters in Section 9.5, upload reservations in 5.13); it is never the system of record for anything.

3.3 Why each major choice #

• Next.js App Router serving both UI and API. One deployable artifact keeps the initial build simple and avoids CORS between UI and API during this phase, while Route Handlers under /api/v1 are written exactly as they would be in a standalone API service, so nothing about this choice blocks splitting the API out later (see 3.4).
• A framework-agnostic service layer. Keeping all business logic in src/server/<module>/ with no import of next/server types means the same service functions can be called from Route Handlers today and from a standalone API host, the worker, or a CLI script without rewriting logic.
• PostgreSQL with Prisma. The domain is strongly relational (loans reference items, users, communities, payments, all with foreign keys and state machines); Postgres's transactional integrity, partial unique indexes (one active loan per item, one open deposit order per loan, Section 6) and Prisma's typed query layer and migration tooling fit that directly.
• Redis + BullMQ for background work. Deadline sweeps, reminders, webhook processing, and notification dispatch must run reliably outside the request/response cycle and survive process restarts; BullMQ gives retries, delayed jobs, and repeatable jobs on a broker the team already runs for counters.
• Razorpay for payments, subscriptions, and payouts. It is the payment gateway with first-class support for Indian UPI/cards/net banking, has a dedicated Subscriptions product for recurring billing, and RazorpayX exposes a payouts API for sending money to owners — all needed without building three separate integrations.
• S3-compatible object storage with presigned uploads. Item photos, avatars, message attachments, handoff/return photos and dispute evidence are uploaded directly from the browser to storage via presigned PUT URLs (5.13), keeping large binary uploads off the Next.js request path entirely. Only item photos and avatars are publicly readable; everything else is served through short-lived presigned GET URLs (Section 26.10).
• Argon2id, opaque sessions, no third-party auth library. The authentication surface is small (email/password, sessions, OTP-based email verification and password reset) and fully owned by the team; a custom implementation avoids taking on a general-purpose auth framework's surface area for a narrow set of flows (Section 9).
• Zod schemas shared between API and forms. Defining validation once in packages/shared and using it both server-side (API boundary) and client-side (React Hook Form resolver) keeps client and server validation from drifting apart.
• PWA with a narrow service worker. The service worker exists to receive Web Push and to cache the app shell for fast repeat loads; it does not attempt offline data sync (3.10).

3.4 The service-layer rule (why a future standalone API needs no rewrite) #

Every Route Handler under apps/web/src/app/api/v1/**/route.ts is thin: it parses the request with a Zod schema from packages/shared, resolves the authenticated user/session, calls exactly one function in apps/web/src/server/<module>/service.ts, and maps the result or thrown AppError (Section 4.4) to the response envelope (Section 5.2). No database query, no business rule, no Razorpay/Resend/S3 call, and no state-transition logic lives in a Route Handler file. All of that lives in the service layer, which imports only framework-agnostic packages (Prisma client, the packages/shared DTOs and Zod schemas, and provider wrappers) and never imports anything from next/server; the ESLint rules in 4.1 enforce this boundary. If the API is later split into a standalone host, the new host's handlers call the same src/server/<module>/service.ts functions (moved or re-exported unchanged) behind whatever routing layer that host uses; no service function signature or business rule changes.

3.5 Monorepo layout #

This tree is the single source for where code lives. Sections 27, 28, 29 and 30 cite it and never list a different path. Workspace packages use the npm scope @communitylend/*.

communitylend/
├── apps/
│   ├── web/                          # @communitylend/web — Next.js app: UI + /api/v1 API
│   │   └── src/
│   │       ├── app/                  # App Router routes: pages (Section 25) + /api/v1/** route handlers
│   │       ├── server/               # Framework-agnostic service layer, one folder per module (4.3)
│   │       │   ├── accounts/         #   auth, sessions, OTPs, profile, account deletion (Section 9)
│   │       │   ├── communities/      #   communities, memberships, settings, admin dashboard queries (Sections 10, 12)
│   │       │   ├── pickup-points/    #   pickup points and slot rules (Section 11)
│   │       │   ├── operator/         #   operator console: users, communities, plans, flags, alerts (Section 13)
│   │       │   ├── items/            #   listings, photo contract, item status (Section 14)
│   │       │   ├── search/           #   full-text query builder, filters, ranking (Section 15)
│   │       │   ├── loans/            #   loan state machine, extensions, handoff, return, reschedule (Sections 16, 17)
│   │       │   ├── messaging/        #   threads, messages, attachments (Section 18)
│   │       │   ├── ratings/          #   ratings, reveal (Section 19)
│   │       │   ├── payments/         #   service.ts, razorpay-gateway.ts (the ONLY importer of the razorpay SDK),
│   │       │   │                     #   gateway.ts (PaymentGateway interface), refunds (Sections 20, 21)
│   │       │   ├── payouts/          #   RazorpayX contacts, fund accounts, payouts, payout details (Sections 13, 21, 23)
│   │       │   ├── subscriptions/    #   plans, subscriptions, access gate (Section 22)
│   │       │   ├── disputes/         #   disputes, evidence, resolution, escalation (Section 23)
│   │       │   ├── notifications/    #   event emitters, preferences, push subscriptions (Section 24)
│   │       │   ├── storage/          #   presigned PUT/GET URLs, upload reservations, object deletion (5.13, Section 26.10)
│   │       │   └── reports/          #   content reports on messages and ratings (Sections 13, 18, 19)
│   │       ├── lib/                  # Cross-cutting: db.ts, redis.ts, auth.ts (guards, 5.9), errors.ts (4.4),
│   │       │                         # logger.ts (4.11), time.ts (4.9), crypto.ts (Section 26.8), rate-limit.ts (5.10)
│   │       └── components/           # React components (PascalCase files), organised by feature (Section 25)
│   └── worker/                       # @communitylend/worker — standalone Node process: BullMQ consumers
│       └── src/
│           ├── queues/               # Queue definitions, job-name constants, repeatable schedules (Section 8)
│           └── processors/           # One processor module per job (Section 8)
├── packages/
│   ├── shared/                       # @communitylend/shared — Zod schemas, DTOs, event keys, error codes, config schema (Section 7)
│   ├── db/                           # @communitylend/db — prisma/schema.prisma, prisma/migrations/, prisma/seed.ts,
│   │                                 # scripts/reencrypt.ts (`pnpm crypto:reencrypt`, Section 26.8)
│   └── emails/                       # @communitylend/emails — React Email templates (Section 24)
├── docker-compose.yml                # postgres, redis, minio, mailpit (3.9)
├── turbo.json
├── pnpm-workspace.yaml
├── DECISIONS.md                      # Section 1.2, Section 30.2
└── README.md

All Razorpay and RazorpayX API calls go through apps/web/src/server/payments/razorpay-gateway.ts (payments, refunds, subscriptions, signature helpers, and the RazorpayX calls that payouts/service.ts makes); no other file imports the razorpay package or calls a Razorpay URL, and the worker only ever imports the exported service functions (3.7).

3.6 Request lifecycle #

1. Client sends an HTTPS request to /api/v1/... with either the cl_session cookie or an Authorization: Bearer token, and, for cookie-authenticated mutating requests, an X-CSRF-Token header (Section 5.8).
2. The Route Handler resolves the session/token via src/lib/auth.ts, attaches { userId, sessionId } to a per-request context, and generates or adopts a requestId (UUID v7, Section 5.16).
3. The handler runs requireAuth first when the endpoint requires authentication, then parses the request body/query with the relevant Zod schema from packages/shared; a parse failure returns 422 VALIDATION_FAILED immediately (Section 5.11), before any service function is called.
4. The handler applies its remaining declared authorization checks (requireSubscription, requireMembership(communityId, role), requireOperator, requireResourceOwner — Section 5.9) in order; the first failing check short-circuits with the matching error code.
5. The handler calls one service function, passing the parsed input and the request context.
6. The service function runs inside a Prisma transaction when it performs more than one write (Section 4.6), applies business rules, writes state transitions with the version-guarded conditional update (4.6), and returns a plain DTO or throws an AppError subclass.
7. The handler maps a successful return to the { data, meta } envelope and a thrown AppError to the { error } envelope with the matching HTTP status (Section 5.2), always including the requestId.
8. If the operation has side effects that should not block the response (an email, a push notification, a refund call to Razorpay, a non-critical audit write), the service function inserts the durable record inside the transaction and enqueues a BullMQ job after commit rather than performing the side effect inline.

3.7 Job lifecycle #

1. A producer (a service function in apps/web, or a repeatable schedule in apps/worker) enqueues a job on a named BullMQ queue with a typed payload defined in packages/shared. Queue names are kebab-case and job names are domain.camelCaseAction (4.2); Section 8 lists every job, its queue, its cadence and its retry policy.
2. A processor in apps/worker/src/processors/<name>.ts consumes the job, using the same Prisma client package (packages/db) and the same service-layer functions where applicable (a processor calls apps/web/src/server/<module>/service.ts functions exported for that purpose) so business rules are not duplicated between the web app and the worker. The worker never imports razorpay, next/server, or a Route Handler.
3. On failure, BullMQ retries with the backoff defined per job in Section 8 (the defaults live in apps/worker/src/queues/); after the final retry a job moves to the queue's failed state and is logged at error level with the job payload and error, and the alerting rules in Section 27.11 apply.
4. Scheduled/repeatable jobs (deadline sweeps, reminders, digests, purges) are registered once at worker startup using BullMQ's repeatable-job feature with a cron expression in the timezone Section 8.4 specifies. Deadlines are never implemented as one delayed job per loan: every deadline is a column on the row (approval_deadline_at, deposit_deadline_at, pickup_deadline_at, return_confirm_deadline_at, and so on, Section 6) and a sweep job every 5 minutes acts on rows whose deadline has passed (Section 8.1).
5. Every job that transitions a row uses the version-guarded conditional update from 4.6; a job that finds zero rows affected treats the row as already handled and exits without error.

3.8 Webhook lifecycle #

1. Razorpay sends an event to POST /api/v1/webhooks/razorpay. The endpoint is rate-limited per IP at 600 requests/minute (Section 5.10) and is otherwise reachable without a session.
2. The Route Handler reads the raw request body and verifies the X-Razorpay-Signature header (HMAC-SHA256 with the webhook secret from Section 7) before parsing JSON. An invalid or missing signature returns 400 with body { "received": false }, is logged at warn with the source IP, and persists nothing — no webhook_events row, no job. Unparseable JSON after a valid signature also returns 400.
3. On a valid signature, the handler takes the event id from the x-razorpay-event-id request header (falling back to event + ':' + entity.id + ':' + created_at only when that header is absent), writes a row to webhook_events (Section 6) with that event_id using an insert that no-ops if the id already exists (idempotent receipt), then enqueues a BullMQ job referencing the webhook_events.id and returns 200 { "received": true } immediately.
4. A worker processor loads the event and dispatches on event_type to the matching handler in the single handler table in Section 20.6.2 (payment captured, refund processed, subscription charged, subscription cancelled, payout processed, and the rest), and marks processed_at on success or writes error on failure (leaving the job to retry per 3.7).
5. Webhook processing is idempotent: reprocessing the same event_id (after a retry, or an operator replay from Section 13.10) must produce the same end state, not duplicate side effects such as a second refund. Handlers achieve this with the version-guarded transition in 4.6 and the status preconditions in Section 20.
6. Ordering is not assumed: a payment.captured webhook may arrive before or after the client's POST /payments/verify; both paths converge on the same service function with the same preconditions (Section 20.6.3).

3.9 Local development setup #

docker-compose.yml at the repository root brings up four services for local development:

pnpm install at the repo root installs all workspace packages. pnpm dev starts apps/web and apps/worker together (via Turborepo). pnpm db:migrate:dev applies Prisma migrations against the local Postgres container and pnpm --filter @communitylend/db seed runs packages/db/prisma/seed.ts. The seed always writes the reference rows every environment needs (the two subscription plans and the feature-flag rows, Section 6.9); it creates the development fixtures defined in Section 6.9 — one community with two pickup points, four users (one community admin and three members), twelve items, and two loans (one active, one returned with revealed ratings), no disputes — only when SEED_DEV_FIXTURES=true (Section 7). Section 27.2 repeats the command sequence for a fresh clone.

3.10 PWA and service worker responsibilities #

The application is installable as a Progressive Web App (manifest with icons, theme color, and display: standalone). The service worker has exactly two responsibilities: receiving and displaying Web Push notifications (Section 24) when the app is not in the foreground, and caching the static app shell (JS/CSS bundles, the offline fallback page) so repeat loads are fast and a network blip shows a friendly offline page instead of a blank screen. The service worker does not cache or replay API responses, does not queue mutating requests made while offline, and does not attempt background sync; all data requests require a live network connection. A mutation attempted while offline fails immediately with the page-level error "You're offline — reconnect and try again" and nothing is queued for later (Section 25.13 defines the error presentation).

3.11 Environment overview #

Every environment variable consumed by apps/web and apps/worker — database and Redis connection strings; S3/R2 endpoint, credentials, bucket and public base URL; Razorpay/RazorpayX keys and webhook secret (the Razorpay environment is derived from the key id prefix, Section 1 row 26); Resend/SMTP settings; VAPID keys; Sentry DSN; SESSION_SECRET, CSRF_SECRET, ENCRYPTION_KEYS, SEED_DEV_FIXTURES, OTEL_TRACE_SAMPLE_RATIO and the feature-flag seed — is listed with type, required/optional, and default in Section 7. Both processes read configuration only through the single Zod-validated config object in packages/shared (Section 7.3); no configuration file is read at runtime and no module reads process.env directly (4.1).

3.12 Data residency #

The Postgres database, Redis instance, and S3 (or R2) bucket are all provisioned in an Indian region (ap-south-1 by default, Section 1) for both production and any staging environment that holds real member data. Local development and CI use ephemeral containers and are not subject to this constraint. Section 26 covers the compliance posture this residency requirement supports.

4. Conventions & Best Practices #

4.1 Code style #

• TypeScript strict mode ("strict": true in tsconfig.json) across every package; no implicit any, no unchecked null access.
• ESLint with @typescript-eslint, eslint-plugin-react, eslint-plugin-react-hooks, and eslint-plugin-import (enforce import order: builtin → external → internal → relative). no-unused-vars, no-floating-promises, and no-explicit-any are errors, not warnings.
• Boundary rules, all errors:
  • no-process-env: process.env may be read only in packages/shared/src/config.ts (Section 7.3); every other module imports the validated config object.
  • no-restricted-imports in apps/web: the Worker export of bullmq may not be imported (the web app only produces jobs; Section 8.7); razorpay may be imported only by apps/web/src/server/payments/razorpay-gateway.ts (3.5).
  • no-restricted-imports in apps/web/src/server/** and apps/worker/**: next/server, next/headers and next/navigation are forbidden (3.4).
• Prettier with: 2-space indent, single quotes, semicolons on, trailing commas (all), print width
  1. Prettier runs as an ESLint integration (eslint-config-prettier) so formatting and linting never disagree; both run in CI and as a pre-commit hook.
• No default exports for anything except Next.js page/layout/route files, which require them.

4.2 Naming #

4.3 Folder-per-module pattern #

Every domain module under apps/web/src/server/<module>/ follows the same four-file shape:

• schemas.ts — re-exports or thinly wraps the module's Zod schemas from packages/shared; the single source of truth for shapes still lives in packages/shared so client forms can import the same schema.
• repository.ts — all Prisma queries for the module's tables; no business logic, only data access (including the version-guarded conditional updates and the three sanctioned SELECT … FOR UPDATE statements, Section 4.6, and the application-side id generation, 4.8).
• service.ts — business logic: validation beyond shape (e.g., "borrower cannot request their own item"), state transitions, transaction boundaries, calls to repository.ts and to other modules' service.ts functions, and enqueueing of BullMQ jobs.
• handlers.ts (optional) — used only when a module's Route Handler logic is more than a few lines of parse-authorize-call-map, to keep the actual route.ts file minimal; still no business logic here, only request/response shaping.

Route Handler files (apps/web/src/app/api/v1/**/route.ts) import only from their module's service.ts (directly, or via handlers.ts) and from src/lib/auth.ts for authorization helpers.

4.4 Error handling #

An AppError base class in apps/web/src/lib/errors.ts carries code (one of the 14 codes in Section 5.4), httpStatus, message, and optional details. One subclass per error code fixes the code/httpStatus pair so a service function only has to supply the message (and, for validation only, the details):

Service functions throw AppError subclasses; they never return an error object. A single error-mapping function used by every Route Handler catches AppError and produces the Section 5.2 error envelope; any other error (including a Prisma unique-constraint violation a service function did not translate) is logged at error level with the stack trace and mapped to 500 INTERNAL with a generic message, never leaking internal details. Service functions translate the unique-constraint violations they expect (one active loan per item, one open deposit order per loan, one payout per dispute — Section 6) into ConflictError before they reach the mapper. Provider errors from Razorpay, Resend or S3 are never forwarded verbatim: the client receives the matching AppError with a generic message; the provider's text goes to the log and, where a column exists (payouts.failure_reason, webhook_events.error, Section 6), to the database.

4.5 Validation #

Every request body, query string, and route param is parsed with a Zod schema from packages/shared at the API boundary (inside the Route Handler, before the service function is called) — service functions receive already-validated, typed input and do not re-validate shape. Service functions do perform business-rule validation that Zod cannot express (uniqueness, cross-field rules, state-dependent rules) and throw the matching AppError subclass. The same Zod schemas are imported client-side as the resolver for React Hook Form, so client and server accept exactly the same shape. Zod schemas for the per-category item attributes, community settings and every request body are the canonical shape definitions; a "representative schema" printed in another section is an excerpt of the packages/shared file it names, never a second definition.

4.6 Transactions and concurrency control #

Every service function that performs more than one write uses a Prisma interactive transaction (prisma.$transaction(async (tx) => { ... })) so partial writes can never be observed or persisted. Jobs are enqueued after the transaction commits, never inside it; the durable fact the job acts on (a refunds row, a notifications row) is written inside the transaction.

Optimistic locking with a version column. The tables whose rows move through a state machine — loans, subscriptions, disputes, community_memberships, and payouts — carry version integer NOT NULL DEFAULT 0 (Section 6). Every transition of such a row, whether triggered by an API request, a webhook handler, or a sweep job, is written as a single conditional update:

UPDATE loans
SET status = 'approved', version = version + 1, updated_at = now(), ...
WHERE id = $1 AND status = 'requested' AND version = $2;

The service function reads the row (including version), checks the business rules, and issues the update with both the expected current status and the expected version. If the update affects zero rows, the row changed under it; the service function throws ConflictError (409 CONFLICT, message "The record was modified by another request. Reload and try again.") and performs no automatic retry — the caller re-reads the resource and decides whether the action still applies. A job that gets zero rows treats the row as already handled and exits without error (3.7). The version is returned as version on every versioned resource's detail response so clients can send it back.

Optional client precondition. Every mutating endpoint on a versioned resource accepts an optional If-Match: <version> header (the integer, not a quoted ETag). When present and different from the row's current version, the handler returns 409 CONFLICT before calling the service function. Omitting the header skips only this pre-check; the server-side conditional update above always runs and is the actual guarantee.

The three sanctioned row locks. SELECT … FOR UPDATE is used on a parent row only in these three places, each inside a transaction, each documented in its owning section, and nowhere else:

SELECT … FOR UPDATE is never used on loans, subscriptions, disputes, community_memberships or payouts; the conditional update is the only mechanism there.

Deadlines are columns, not timers. No code schedules a delayed job per loan, dispute or subscription. Every deadline is a timestamptz column on the row (approval_deadline_at, deposit_deadline_at, pickup_deadline_at, return_confirm_deadline_at, grace_until, and the rest in Section 6) and a sweep job runs every 5 minutes selecting rows whose deadline has passed and whose status still expects it (Section 8.1). Leaving the status removes the row from the sweep; changing the deadline (a reschedule, an extension) is a plain column update.

Isolation level. Transactions run at PostgreSQL's default READ COMMITTED; the conditional update and the three locks above make transitions safe, so no code raises the isolation level.

4.7 Soft-delete policy #

Only four tables carry a deleted_at column and are soft-deleted: users, communities, items, pickup_points (Section 6.1). Every other table listed in Section 6 is append-only or is deleted only by the retention jobs in Section 8 (loans, payments, refunds, payouts, disputes, messages, ratings, and their related tables are never deleted by a user action, only transitioned to a terminal status). Every Prisma query against a soft-deletable table filters deleted_at: null unless the query is explicitly an admin/operator "show including removed" view; this filter is applied centrally in each module's repository.ts, not repeated ad hoc in service functions.

4.8 ID generation #

All primary keys are UUID v7, generated application-side with the uuid package's v7() function (Section 3) in repository.ts immediately before insert, not generated by the database. Prisma models therefore declare id String @id @db.Uuid with no @default (Section 6.6), and no table uses gen_random_uuid(). UUID v7 is time-ordered, which keeps Postgres index locality reasonable for high-insert tables (loan_events, messages, notifications) while remaining globally unique and non-guessable enough for use in URLs. Request ids (5.16), idempotency keys supplied by clients (5.7) and storage-key components (Section 26.10) are also UUID v7 strings; every example id in this document is written as a plain UUID string such as 0192f31a-7c1e-7d3b-9a2f-4e5d6c7b8a90, never with a prefix.

4.9 Time handling #

All timestamps are stored as timestamptz in UTC. All business-time calculations (deadlines, reminder eligibility, "due today") are performed in UTC internally and converted to Asia/Kolkata only at the display layer and when deciding the wall-clock moment a day-based reminder should fire (fixed at 09:00 IST, Section 8.4). Day-boundary comparisons in SQL always use the idiom (column AT TIME ZONE 'Asia/Kolkata')::date that Section 8.4 defines; a bare ::date cast is a bug. A single src/lib/time.ts module wraps this conversion so no component or service function calls a timezone library directly. Durations in this document are exact: "48 hours" means interval '48 hours' from the stored timestamp, not "two calendar days".

4.10 Money handling #

Every monetary amount is stored and passed between layers as an integer number of paise; floating- point numbers are never used for money anywhere in the codebase, including in TypeScript, SQL, or JSON. A shared formatPaise(amountPaise: number): string helper in packages/shared renders paise as a ₹-prefixed rupee string for display (e.g., 9900 → "₹99.00"); no component formats money without it. Arithmetic on money (splitting a deposit into a forfeited amount and a remainder, for example) is performed with integer paise and validated to sum exactly back to the original amount before any write; Section 21 defines the ledger invariant that refunds plus payouts never exceed the captured payment.

4.11 Logging conventions #

pino is the only logger. Every log line in a request context includes requestId; lines associated with an authenticated user include userId; lines inside a community-scoped operation include communityId; lines inside a loan-scoped operation include loanId; lines inside a job include jobId and jobName. A shared createLogger(context) helper in src/lib/logger.ts binds these fields once per request/job so individual log calls do not have to repeat them. Log levels: error for anything requiring investigation (unhandled exceptions, failed webhook processing after final retry, payment/refund/payout failures), warn for handled-but-notable conditions (an AppError mapped to 409/422 is not logged at warn — it is an expected outcome — but a webhook signature failure, a payment verification mismatch, or a rate-limit trip is), info for lifecycle events (loan created, payment captured, subscription activated), debug for anything more granular, disabled in production by default. Session tokens, OTP codes, handoff codes, passwords, encryption keys, payout destinations, Razorpay secrets and full request bodies are never logged at any level; Section 26.11 defines the redaction list and the logger applies it centrally.

4.12 Git conventions #

Branch names follow type/short-description where type is one of feat, fix, chore, refactor, test, docs (matches 4.2). Commits follow Conventional Commits (type(scope): summary, e.g., feat(loans): add extension request endpoint). Every pull request description states what changed and why, links any related issue, and confirms the PR checklist below.

4.13 Pull request checklist #

• pnpm turbo run lint typecheck test passes locally.
• New or changed API behavior has a corresponding Vitest integration test and, if it touches a user-facing flow, a Playwright test (Section 28).
• New environment variables are added to .env.example, to the config schema, and to the table in Section 7.
• New database fields/tables have a Prisma migration checked in, with any partial index or check constraint in hand-written SQL (Section 6.8).
• New or changed endpoints are reflected in the Zod schemas in packages/shared so openapi.json regenerates correctly (Section 5.17), and in the endpoint index (5.18).
• New state transitions use the version-guarded conditional update (4.6) and set or clear the relevant deadline column.
• UI changes follow the accessibility conventions in Section 25.
• No secret, credential, or API key is committed; new secrets are added to .env.example as a placeholder only (Section 7).
• No TODO, FIXME or XXX marker is left in the diff; an open question goes to DECISIONS.md "Follow-ups" instead (1.2).

4.14 Pointers #

Accessibility conventions: Section 25. Testing conventions (what must be covered, how to run each test tier): Section 28. Secrets handling (where each credential lives, rotation expectations): Section 7.4. Encryption of stored fields and key rotation: Section 26.8.

5. API Design Conventions #

This section owns every cross-cutting API rule: the response envelope, HTTP status usage, the error-code list, pagination, filtering, idempotency, authentication headers and CSRF, the authorization guard order, rate limits, the upload pattern, the inbound webhook contract, versioning, request ids, OpenAPI generation, and the endpoint index. Sections that define individual endpoints (9–24) follow every rule in this section without restating it and cite "Section 5" only for these topics; business values (password rules, deposit steps, plan prices, reminder offsets) live in their owning sections per Section 1.3.

5.1 Base path and resource naming #

• Base path: /api/v1. All application endpoints live under it. The only unversioned paths are the two health endpoints GET /api/health and GET /api/ready (Section 27.7).
• Resources are plural nouns in kebab-case paths (/communities, /items, /pickup-points, /disputes, /payments, /subscriptions, /notifications). A loan is created as a sub-resource of an item (POST /items/{itemId}/loan-requests) and thereafter addressed as /loans/{id}; a dispute is created under its loan (POST /loans/{id}/disputes) and thereafter addressed as /disputes/{id}.
• Community-scoped resources nest under /communities/{communityId}/... (e.g. /communities/{id}/pickup-points, /communities/{id}/items). Resources that are globally addressable once created (an item, a loan, a dispute) are referenced by their own id at the top level thereafter (/items/{itemId}, /loans/{id}) even though they belong to a community, to avoid callers having to know the community id for every subsequent call.
• Caller-scoped resources live under /me/... (/me/loans, /me/subscription, /me/notifications).
• Actions that are not plain CRUD are verbs as the last path segment (/approve, /decline, /cancel, /handoff/confirm, /return/mark, /rotate-join-code), always POST.
• JSON request and response bodies use camelCase keys (4.2); the database uses snake_case (Section 6); the Prisma layer is the translation boundary and no handler or service function emits snake_case JSON.

5.2 Response envelopes #

Success:

{
  "data": { "...": "..." },
  "meta": {
    "requestId": "018f2f3a-6f2b-7c3e-9b1a-6c1a2b3c4d5e",
    "nextCursor": "eyJpZCI6IjAxOGYy..."
  }
}

meta.nextCursor is present (string or null) on list endpoints only and absent on single-resource endpoints. data is an object for single-resource responses and an array for list responses. A single-resource response never carries a cursor; a resource that embeds a bounded sub-list (for example the most recent 50 events on a loan) states the bound and names the list endpoint that pages the rest.

Error:

{
  "error": {
    "code": "VALIDATION_FAILED",
    "message": "The request body failed validation.",
    "details": [
      { "path": "title", "message": "Title must be at most 120 characters." }
    ],
    "requestId": "018f2f3a-6f2b-7c3e-9b1a-6c1a2b3c4d5e"
  }
}

details is present only for VALIDATION_FAILED and omitted (not null, not [], omitted) for every other error code; an example error body anywhere in this document for any other code has no details key. requestId is always present and always matches the value logged server-side for that request, so a user-reported problem can be traced from a support ticket to server logs.

5.3 HTTP status code usage #

5.4 Error codes #

This is the complete list of the 14 error codes used across the API; no endpoint returns any other code. Every occurrence of any of these codes anywhere else in this specification refers back to this table, and Section 31.2 repeats this table with an added 'Returned when' column for the appendix error reference.

Status codes are fixed per code: LIMIT_EXCEEDED is always 409, PAYMENT_FAILED is always 402, and a CSRF failure is always 403 FORBIDDEN. Conditions such as "this conversation is closed", "the rating window has closed" or "the handoff is locked" are INVALID_STATE_TRANSITION with a descriptive message, not new codes.

5.5 Pagination #

All list endpoints use cursor-based pagination: ?cursor=<opaque>&limit=<n>. limit accepts 1–50 and defaults to 24 when omitted on every list endpoint in this document; a limit outside that range is a VALIDATION_FAILED error, not a silently clamped value. The cursor is an opaque base64url-encoded string; server-side it encodes the sort key of the last item on the current page ({ createdAt, id } for newest-first lists, or the owning section's sort key plus id for tie-breaking) and must not be decoded or constructed by clients — clients only ever pass back a cursor value they received in meta.nextCursor. A cursor that fails to decode, or that was issued for a different sort order or filter set, is VALIDATION_FAILED with path cursor. meta.nextCursor is null on the last page.

5.6 Filtering and sorting #

Filter parameters are plain query string keys named after the field they filter, using the camelCase field name (e.g., ?category=book&availability=available). Endpoints that support more than one sort order accept a sort query parameter with an explicit enum of allowed values (each owning section lists its own, e.g., Section 15 lists newest, mostBorrowed, lowestDeposit for item search); an unrecognized sort value is VALIDATION_FAILED. There is no generic free-form filter/sort DSL — every filterable/sortable field is an explicit, documented query parameter on its endpoint. Boolean query parameters accept exactly true or false.

5.7 Idempotency #

The Idempotency-Key header (a client-generated UUID v7) is REQUIRED on exactly these endpoints:

POST /payments/verify and POST /subscriptions/verify do not take the header: they are idempotent by construction (Section 20.6.3) and a repeat simply returns the current state.

Rules:

• A missing header on a listed endpoint, or a value that is not a UUID, is 422 VALIDATION_FAILED with details[0].path = "Idempotency-Key".
• The server stores each accepted request in idempotency_keys (Section 6), primary key (user_id, key), with a SHA-256 request_hash of method + path + body, the response_status and response_body once known, and expires_at = created_at + 24 hours. Two different users may use the same key value without collision; a key is never shared across users.
• A repeated request with the same key, the same caller and the same request_hash within 24 hours returns the stored status and body verbatim without re-executing the operation.
• A repeated request with the same key but a different request_hash is 409 CONFLICT with message "A different request was already sent with this Idempotency-Key."
• A repeated request that arrives while the first is still in flight (response_status IS NULL) is 409 CONFLICT with message "This request is already in progress." — the client waits and retries with the same key.
• Expired rows are purged by maintenance.purgeExpiredIdempotencyKeys (Section 8); after expiry the same key is treated as new.

5.8 Authentication and CSRF #

Two supported credential types, both accepted on every authenticated endpoint unless an endpoint says otherwise:

• Cookie — browser clients receive an httpOnly, Secure, SameSite=Lax cookie named cl_session on login (Section 9.5). Cookie-authenticated mutating requests (POST, PATCH, PUT, DELETE) additionally require an X-CSRF-Token header whose value matches the CSRF token issued alongside the session (double-submit pattern: the token is an HMAC-SHA256 over the session id keyed with CSRF_SECRET, Section 7, and is also set as a non-httpOnly cookie cl_csrf so client-side JavaScript can read it and echo it back as a header). A missing or mismatched header on a cookie-authenticated mutation is 403 FORBIDDEN with message "CSRF token missing or invalid." GET requests never require the CSRF header.
• Bearer — API clients send Authorization: Bearer <opaque-session-token>. Bearer-authenticated requests are exempt from the CSRF check (there is no ambient browser cookie to forge against).

When both a cookie and a bearer header are present, the bearer token is used and the cookie is ignored. A request presenting neither a valid cookie session nor a valid bearer token on an endpoint that requires authentication returns 401 UNAUTHENTICATED, as does a session that has expired or been revoked. Session lifetime, sliding expiry and the per-user session cap are defined in Section 9.6.

A third cookie, cl_sub_status (non-httpOnly, 1-hour max-age, set by the API on login, GET /me and the subscription endpoints, Section 22.4), is a UI hint for the middleware in Section 25.4 and is never read by any authorization guard.

5.9 Authorization declaration pattern #

Every Route Handler declares its authorization requirements as an explicit, ordered list of guard calls before touching the service layer, using shared helpers from src/lib/auth.ts:

• requireAuth(request) — resolves and returns { userId, sessionId } or throws UNAUTHENTICATED. Also throws FORBIDDEN when users.status is suspended or deleted (Section 9.14).
• requireSubscription(userId) — throws SUBSCRIPTION_REQUIRED unless the caller's access level under Section 22.4 is full_access. Endpoints that read-only members may still use (completing a loan in motion, reading a thread) do not declare this guard; Section 22.4 lists them.
• requireMembership(userId, communityId, role?) — throws NOT_A_MEMBER if the user has no active membership in communityId; if role is passed (e.g., 'admin'), throws FORBIDDEN when the membership exists but is not that role. Also throws INVALID_STATE_TRANSITION when the community is archived and the endpoint mutates it (Section 10.10).
• requireOperator(userId) — throws FORBIDDEN unless users.platform_role = 'operator'.
• requireAnyOf(...guards) — succeeds if any listed guard succeeds; used for the endpoints in 5.18 labelled "Admin or Operator" (requireMembership(admin) or requireOperator).
• requireResourceOwner(userId, resource, side?) — throws NOT_FOUND when the resource does not exist or is not visible to the caller, and FORBIDDEN when it is visible but the caller is not the party the action expects (owner, borrower, or either party of a loan; the reporter of a report; the uploader of an object).

Guards run in the order listed above where more than one applies (authentication, then subscription, then membership/role, then resource ownership), so a request always fails on the most fundamental problem first (an unauthenticated caller gets 401, never a 403 or 404 that would leak whether a resource exists; a non-member gets NOT_A_MEMBER, never a 404 that reveals an item id is valid).

5.10 Rate limiting #

A request exceeding its limit returns 429 with error code RATE_LIMITED and a Retry-After header (seconds until the limiting window resets). Rate-limit counters are stored in Redis keyed ratelimit:{scope}:{identifier}:{windowStart} with a fixed window per scope and a TTL equal to the window length; fixed windows are sufficient for every limit above. Limits are evaluated after requireAuth (so the per-user scope applies to authenticated callers and the per-IP scope to everyone else) and before body parsing. Other sections cite this table by number and never state a different value.

5.11 Request validation flow #

1. Route Handler receives the request.
2. requireAuth runs first when the endpoint requires authentication, so an unauthenticated malformed request still reports 401, not 422 (avoids leaking that a resource/shape exists to anonymous callers).
3. The rate limit for the request's scope is checked (5.10).
4. Body/query/route params are parsed against the endpoint's Zod schema from packages/shared. A schema failure returns 422 VALIDATION_FAILED with one details entry per failing field, before any further authorization guard or service call runs. Route params that must be UUIDs ({id}, {itemId}, and so on) are validated here; a malformed id is 422, never 404.
5. The remaining authorization guards run in the 5.9 order.
6. If the endpoint mutates a versioned resource and the request carries If-Match, the version pre-check in 4.6 runs (409 CONFLICT on mismatch).
7. The service function runs and applies any remaining business-rule validation, throwing the matching AppError subclass (4.4) on failure.

5.12 Response caching #

Every authenticated response includes Cache-Control: private, no-store; nothing served under /api/v1 is cached by a shared cache or browser disk cache. The one exception is GET /loans/{id}/messages, which sets Cache-Control: private, no-cache and supports ETag/If-None-Match so the 10-second polling in Section 18.9 can return 304 Not Modified. The only publicly cacheable responses are the two unversioned health endpoints (Section 27.7), GET /plans, and the generated openapi.json (5.17), which use Cache-Control: public, max-age=60.

5.13 File upload pattern #

Every upload (item photos, avatars, message attachments, handoff and return photos, dispute evidence) follows the same three-step pattern. The owning section defines the size and type limits, the maximum count, and the confirm endpoint's extra fields; the object-key scheme and the public/private split of the bucket are defined in Section 26.10.

1. Reserve. The client calls the resource's */upload-url endpoint with { "contentType": "image/jpeg", "sizeBytes": 2483112 } (plus any resource-specific fields). The server checks the caller's right to attach to that resource, the per-resource count limit (409 LIMIT_EXCEEDED when full), and the declared type and size against the owning section's limits (422 VALIDATION_FAILED for a disallowed type, 413 PAYLOAD_TOO_LARGE for a declared size over the limit). It then generates the storage key (<key>.upload for the original, Section 26.10), writes a Redis reservation upload:{storageKey} → { userId, resourceType, resourceId, contentType, sizeBytes } with TTL 900 seconds, and returns a presigned S3 PUT URL that signs both Content-Type and Content-Length equal to the declared values, so an object of a different type or size cannot be uploaded with it. No database row is created. Response 201:

   Copy

   {
     "data": {
       "uploadUrl": "https://s3.ap-south-1.amazonaws.com/communitylend-prod-media/items/0192f2b1-7a3c-7e4d-8b5f-1c2d3e4f5a6b/0192f2c3-1b2c-7d4e-9f60-7a8b9c0d1e2f.upload?X-Amz-Algorithm=...",
       "storageKey": "items/0192f2b1-7a3c-7e4d-8b5f-1c2d3e4f5a6b/0192f2c3-1b2c-7d4e-9f60-7a8b9c0d1e2f.upload",
       "expiresAt": "2026-09-17T10:30:00.000Z",
       "requiredHeaders": { "Content-Type": "image/jpeg", "Content-Length": "2483112" }
     },
     "meta": { "requestId": "018f2f3a-6f2b-7c3e-9b1a-6c1a2b3c4d5e" }
   }

2. Upload. The client PUTs the file bytes directly to the uploadUrl with exactly the requiredHeaders. Presigned URLs expire 15 minutes (900 seconds) after issuance, everywhere in this document.

3. Confirm. The client calls the resource's confirm endpoint (for example POST /items/{itemId}/photos, POST /me/avatar, POST /loans/{id}/handoff-photos) with the storageKey (and the owning section's extra fields such as caption). The server:

   1. loads the Redis reservation; a missing or expired reservation, or one whose userId, resourceType or resourceId do not match the caller and the target resource, is 404 NOT_FOUND with message "Unknown or expired upload";
   2. issues a HEAD request for the object; a missing object is the same 404;
   3. rejects and deletes the object if ContentLength exceeds the owning section's limit (413 PAYLOAD_TOO_LARGE) or ContentType differs from the reserved value (422 VALIDATION_FAILED, path storageKey);
   4. re-checks the count limit inside the transaction (with the row lock in 4.6 where the owning section requires it), inserts the database row with status processing where the table has a status column (Section 6), deletes the Redis reservation, and enqueues media.processImage (Section 8), which normalises the image to WebP, writes the final key(s), updates width/height, marks the row ready (or failed), and deletes the .upload original. Confirm returns 201 with the created row. Exception: POST /me/avatar normalises the 256×256 avatar synchronously inside the confirm call (Section 9.9.2) and does not enqueue media.processImage.

An object uploaded but never confirmed is removed by media.purgeOrphans (Section 8) once its reservation has expired. Objects under private prefixes are never returned as raw keys to clients: responses carry a presigned GET URL valid for 15 minutes, generated only after the party/role check for that resource (Section 26.10); only item photos and avatars are served from the public base URL.

5.14 Webhook inbound pattern #

POST /api/v1/webhooks/razorpay is the sole inbound webhook endpoint. It is reachable without a session (Razorpay cannot authenticate as a CommunityLend user) and is protected by the signature check in Section 3.8 plus the per-IP limit of 600 requests/minute in 5.10; no other rate limit applies to it. Its contract:

The endpoint never waits for the worker to finish processing, so Razorpay's delivery does not time out and retry unnecessarily; the body is the bare object above, not the { data, meta } envelope, because the consumer is Razorpay, not an API client. The event-to-handler table is in Section 20.6.2; replay of a stored event is an operator action (Section 13.10).

5.15 API versioning #

The version is in the URL path (/api/v1). A breaking change to any existing endpoint's request or response shape ships as /api/v2 of that endpoint (or the whole API, if the change is broad) rather than mutating /api/v1 in place; /api/v1 continues to serve existing behavior until it is formally deprecated, and this specification defines no deprecation timeline beyond "do not break v1". Adding an optional request field, adding a response field, or adding an enum value that clients are told to tolerate is not a breaking change. All timestamps in requests and responses are ISO 8601 in UTC with millisecond precision (2026-09-17T10:15:30.000Z). All money values are integer paise; objects carry a currency field only where the owning section's response shape includes one, and its value is always "INR" (Section 6).

5.16 Request ID propagation #

Every request is assigned a requestId (UUID v7, generated with v7() from the uuid package) at the top of the Route Handler if the client did not supply one; a client MAY supply X-Request-Id and, when the value is a syntactically valid UUID, the server echoes it back as the authoritative requestId for that call instead of generating a new one, which lets a client correlate its own logs with server logs (an invalid value is ignored and a new id generated). The requestId is bound into the request-scoped logger (4.11), included in every response envelope (meta.requestId on success, error.requestId on failure) and in the X-Request-Id response header, and included in any BullMQ job enqueued as a side effect of that request, so a job's logs can be traced back to the originating HTTP request.

5.17 OpenAPI #

packages/shared's Zod schemas are the single source of truth for every request/response shape. zod-to-openapi generates an OpenAPI 3.x document from those schemas plus a small per-endpoint registration (method, path, summary, auth label from 5.18, the 5.4 error codes each endpoint can return, whether Idempotency-Key is required), served at GET /api/v1/openapi.json (publicly readable, cached per 5.12). This document is generated at build time (or on demand in development) — it is never hand-maintained separately from the Zod schemas, so it cannot drift from the actual validation the server enforces. A CI check (Section 27.5) fails the build when an endpoint exists in the route tree but not in the registration, or vice versa.

5.18 Complete endpoint index #

This is the complete list of endpoints in this specification; an endpoint that is not listed here does not exist, and Section 31.5 is a compact copy of this table grouped by section. Auth column values:

Where an endpoint's precise rule is more specific than this coarse label, the owning section states the exact rule; this table is a navigational index, and the owning section is the authorization source of truth. "Idem" in Notes means Idempotency-Key is required (5.7).

Every path above except the three at the end is relative to /api/v1. Every mutating endpoint carries an explicit auth label; an endpoint labelled Auth still applies the resource checks its owning section states (POST /payments/verify accepts only the payment's own payer). Actions that live inside another endpoint's body (operator suspend/force_logout, reassign_admin) are not separate paths.

6. Data Model & Schema #

6.1 Conventions #

• Database: PostgreSQL, version per Section 3. ORM and migrations: Prisma, version per Section 3.
• Table names: snake_case, plural (users, loan_events). Prisma model names: PascalCase, singular, mapped to the table with @@map.
• Column names: snake_case in the database, mapped to camelCase Prisma fields. JSON payloads returned by the API use camelCase keys (Section 5).
• Primary keys: id uuid, generated application-side as UUID v7 (time-ordered, uuid package per Section 3) in the repository layer before insert. Prisma models declare @id with NO @default; the database never generates a UUID (gen_random_uuid() is not used), so the application controls every ID before the row exists.
• Every table has created_at timestamptz NOT NULL DEFAULT now(); every mutable table also has updated_at timestamptz NOT NULL DEFAULT now(), maintained by Prisma @updatedAt on every write. Append-only tables (loan_events, audit_logs, webhook_events, messages, notifications, subscription_events, dispute_evidence, loan_photos) have no updated_at.
• Optimistic concurrency: version integer NOT NULL DEFAULT 0 exists on the five tables with concurrent-write-prone state transitions — loans, subscriptions, disputes, community_memberships, payouts. Every state transition on these tables is written as UPDATE … SET …, version = version + 1 WHERE id = $1 AND status = $currentStatus AND version = $v; zero affected rows means another writer won and the caller maps it to 409 CONFLICT (Section 4.6 owns the pattern and its documented SELECT … FOR UPDATE exceptions).
• Soft delete (deleted_at timestamptz NULL) exists ONLY on users, communities, items, pickup_points. All other tables are append-only or hard-deleted only through the retention jobs in Section 8. Soft-deleted rows are excluded from default Prisma queries via an explicit deleted_at: null filter in the service layer (Prisma has no native soft-delete middleware at the version in Section 3; every repository function filters explicitly).
• Money: integer paise, column suffix _paise, type integer (Postgres int4, max ~21 million INR, sufficient for all amounts in this system; deposits are capped at 500000 paise and subscription amounts are in the low thousands of paise). Currency is always INR and is not stored as a column except on payments.currency for forward compatibility with Razorpay's response echoing.
• Time: all timestamps stored timestamptz in UTC. Display conversion to Asia/Kolkata happens in the frontend (Section 25) and in email/push templates (Section 24). Any "day boundary" business rule (reminders, digests) is evaluated in Asia/Kolkata by the worker (Section 8.4) even though storage stays UTC.
• Enums: implemented as native Postgres enum types via Prisma enum blocks, not free-text with a check constraint, so invalid values are rejected at the database layer in addition to Zod validation at the API layer.
• Foreign keys: every FK is declared with an explicit ON DELETE behaviour (no implicit RESTRICT left undocumented). Because soft delete is used for the four tables above, most FKs pointing at them use ON DELETE RESTRICT (the row is never hard-deleted while referenced) with the soft-delete flag doing the real work.
• Encrypted columns carry the suffix _encrypted and hold AES-256-GCM ciphertext prefixed with the key version (v<n>:…) under ENCRYPTION_KEYS (Section 7.1; mechanism and rotation owned by Section 26.8). Hashed columns carry the suffix _hash.
• Object-storage keys (avatar_key, storage_key, attachment_key) follow the key scheme owned by Section 26.10: items/{itemId}/{photoId}.webp (+ .thumb.webp), avatars/{userId}/{version}.webp, loans/{loanId}/messages/{messageId}.webp, loans/{loanId}/photos/{photoId}.webp, loans/{loanId}/dispute-evidence/{evidenceId}.webp. Only items/* and avatars/* are public-read; every other key is served through a presigned GET URL valid 15 minutes.
• Every table has a primary key index (implicit) plus the secondary indexes listed per table below. Index names follow ix_<table>_<columns> for non-unique and uq_<table>_<columns> for unique. Partial and expression indexes, CHECK constraints, the citext/pg_trgm extensions and the search_vector generated column are written as hand-written SQL in the --create-only migrations of Section 6.8 because the Prisma schema language cannot express them.

6.2 Enums #

6.3 Tables #

6.3.1 users #

Indexes: uq_users_email UNIQUE (email) WHERE deleted_at IS NULL; uq_users_phone UNIQUE (phone) WHERE deleted_at IS NULL AND phone IS NOT NULL; ix_users_status (status); ix_users_deletion_requested (deletion_requested_at) WHERE deletion_requested_at IS NOT NULL for the finaliser sweep. Check constraints: char_length(full_name) BETWEEN 2 AND 80; char_length(display_name) BETWEEN 2 AND 40; phone ~ '^\+91[6-9][0-9]{9}$' when not null. Login-failure lockout counters and OTP attempt counters that need sub-second updates live in Redis (keys documented in Section 9.5), not in this table.

6.3.2 sessions #

Indexes: uq_sessions_token_hash UNIQUE (token_hash); ix_sessions_user_active (user_id, revoked_at) for the "max 10 active sessions" check (Section 9.6) and GET /me/sessions; ix_sessions_expires_at (expires_at) for the purge job (Section 8.3.23). FK behaviour: ON DELETE CASCADE is safe here because users rows are anonymised, never hard-deleted (Section 6.10); the finaliser job deletes session rows explicitly.

6.3.3 email_otps #

Indexes: ix_email_otps_user_purpose (user_id, purpose, created_at DESC) for resend-cooldown lookups; ix_email_otps_expires_at (expires_at) for the purge job (Section 8.3.24).

6.3.4 communities #

settings default JSON:

{
  "defaultMaxBorrowDays": 14,
  "allowZeroDeposit": true,
  "maxDepositPaise": 500000,
  "requireAdminListingReview": false,
  "pickupReminderHours": 24
}

Indexes: uq_communities_slug UNIQUE (slug); uq_communities_join_code UNIQUE (join_code); ix_communities_city_status (city, status) WHERE deleted_at IS NULL for GET /communities/search; ix_communities_name_trgm GIN (name gin_trgm_ops) WHERE deleted_at IS NULL AND status = 'active' for the fuzzy name match in Section 10.4 (requires the pg_trgm extension, Section 6.8). Check constraints: pincode regex above; member_count >= 0; char_length(name) BETWEEN 3 AND 80; char_length(address_line1) BETWEEN 3 AND 120; char_length(locality) BETWEEN 2 AND 80; char_length(city) BETWEEN 2 AND 60.

6.3.5 community_memberships #

Indexes: uq_community_memberships_user_community UNIQUE (user_id, community_id); ix_community_memberships_community_status (community_id, status) for admin listing screens; ix_community_memberships_user_status (user_id, status) for GET /me/communities and the "max 3 active communities" check; ix_community_memberships_pending_requested (requested_at) WHERE status = 'pending' for the reminder, digest and expiry jobs (Sections 8.3.10–8.3.12). One row per (user, community) for the lifetime of the pair: a re-request after rejected, left or removed UPDATES the existing row back to pending (Section 10.5); history of decisions is in audit_logs. Business rules enforced in the service layer (they span a filtered count rather than a simple constraint): a user may have at most 3 rows with status = 'active'; a community may have at most 3 rows with role = 'admin' AND status = 'active' (Section 10.8; the admin-count check takes SELECT … FOR UPDATE on the community row, the documented exception in Section 4.6).

6.3.6 pickup_points #

Indexes: ix_pickup_points_community (community_id, status, sort_order) WHERE deleted_at IS NULL; uq_pickup_points_community_name UNIQUE (community_id, lower(name)) WHERE deleted_at IS NULL. hours example: {"mon":[{"start":"07:00","end":"22:00"}],"tue":[{"start":"07:00","end":"22:00"}], ...}. An empty array for a day means closed that day.

6.3.7 items #

attributes schema by category (Section 14.3 owns validation; Section 6 owns storage):

// book
{ "author": "string, 1-120", "isbn": "string, ISBN-10 or ISBN-13 per the Section 14.3 pattern, optional", "language": "string, 1-40", "pages": "integer, 1-20000, optional" }
// toy
{ "ageRange": "string e.g. '3-5'", "brand": "string, optional, 1-60", "pieceCount": "integer, optional, 1-100000" }
// game
{ "type": "board|card|video|puzzle", "players": "string e.g. '2-6'", "minAge": "integer, 1-99, optional" }
// other
{ "brand": "string, optional, 1-60" }

Indexes (all WHERE deleted_at IS NULL unless stated):

• ix_items_community_status_created (community_id, status, created_at DESC, id DESC) — catalog browse, sort newest (Section 15.4).
• ix_items_community_status_borrow (community_id, status, borrow_count DESC, created_at DESC, id DESC) — sort mostBorrowed.
• ix_items_community_status_deposit (community_id, status, deposit_paise ASC, created_at DESC, id DESC) — sort lowestDeposit.
• ix_items_community_category_status (community_id, category, status) — category-filtered browse.
• ix_items_owner (owner_id) — GET /me/items and the "max 200 active items per user" check (Section 14.2).
• ix_items_search_vector GIN (search_vector) — no partial predicate (GIN indexes are combined with the btree filters by bitmap scan).
• ix_items_archived_deleted (deleted_at) WHERE status = 'archived' — 90-day photo purge (Section 8.3.22). Check constraints: deposit_paise BETWEEN 0 AND 500000 AND deposit_paise % 5000 = 0; max_borrow_days IN (7,14,21,28); char_length(title) BETWEEN 1 AND 120; description IS NULL OR char_length(description) BETWEEN 1 AND 2000.

6.3.8 item_photos #

Indexes: ix_item_photos_item (item_id, sort_order); ix_item_photos_failed (updated_at) WHERE status = 'failed' for the 24-hour purge (Section 8.3.22). Application-level rule (not a DB constraint): 1–6 rows per item_id (Section 14.2); enforced at upload-confirm time by counting existing rows after SELECT … FOR UPDATE on the item row (documented exception in Section 4.6).

6.3.9 loans #

Indexes:

• ix_loans_item_status (item_id, status) — item detail "current loan" lookup.
• uq_loans_item_active UNIQUE (item_id) WHERE status IN ('approved','awaiting_pickup','active','return_marked','disputed') — at most one loan can hold an item at a time; a violation during approval maps to 409 CONFLICT (Section 16.4).
• ix_loans_borrower_status (borrower_id, status) — GET /me/loans?role=borrower and the "max 5 concurrent / max 3 pending" checks (Section 16.3).
• ix_loans_owner_status (owner_id, status) — role=owner.
• ix_loans_community_status (community_id, status) — admin dashboard.
• ix_loans_due_at (due_at) WHERE status = 'active' — due/overdue reminder scans and loss eligibility.
• ix_loans_approval_deadline (approval_deadline_at) WHERE status = 'requested'.
• ix_loans_deposit_deadline (deposit_deadline_at) WHERE status = 'approved'.
• ix_loans_pickup_deadline (pickup_deadline_at) WHERE status = 'awaiting_pickup'.
• ix_loans_return_confirm_deadline (return_confirm_deadline_at) WHERE status = 'return_marked'.
• ix_loans_scheduled_slot_start (scheduled_slot_start) WHERE status = 'awaiting_pickup' — pickup reminder (Section 8.3.4).
• ix_loans_closed_at (closed_at) WHERE closed_at IS NOT NULL — rating reveal (Section 8.3.26) and retention sweep (Section 8.3.27). Check constraints: requested_days BETWEEN 1 AND 28; extension_days BETWEEN 0 AND 14; deposit_paise BETWEEN 0 AND 500000; reschedule_count BETWEEN 0 AND 3; handoff_code_generation BETWEEN 0 AND 3; handoff_code_failed_attempts BETWEEN 0 AND 5; requested_slot_end > requested_slot_start.

6.3.10 loan_events #

Append-only: no updated_at, no update or delete path in the service layer. Indexes: ix_loan_events_loan (loan_id, created_at).

6.3.11 loan_extension_requests #

Indexes: uq_loan_extension_requests_loan_pending UNIQUE (loan_id) WHERE status = 'pending' — at most one pending request per loan at the database layer, on top of the "one extension per loan" rule enforced in the service layer by checking that no prior approved row exists before allowing a new request; ix_loan_extension_requests_loan (loan_id, created_at).

Check constraints: char_length(note) <= 500.

6.3.12 payments #

Indexes: uq_payments_razorpay_order_id UNIQUE (razorpay_order_id); uq_payments_razorpay_payment_id UNIQUE (razorpay_payment_id); uq_payments_loan_open UNIQUE (loan_id) WHERE purpose = 'deposit' AND status IN ('created','authorized','captured') — at most one open deposit payment per loan; a second POST /loans/{id}/deposit/order returns the existing row (Section 21.2); ix_payments_user (user_id, created_at DESC); ix_payments_loan (loan_id); ix_payments_status_created (status, created_at) for the reconciliation job (Section 8.3.15).

6.3.13 refunds #

Indexes: uq_refunds_razorpay_refund_id UNIQUE (razorpay_refund_id); uq_refunds_loan_reason UNIQUE (loan_id, reason) WHERE status <> 'failed' — one live refund per loan and reason, so a retried transition cannot insert a duplicate; ix_refunds_loan (loan_id); ix_refunds_status_created (status, created_at) for the reconciliation job (Section 8.3.15). Ledger invariant, checked in the service layer on every insert and by the nightly job in Section 8.3.33: SUM(refunds.amount_paise WHERE status <> 'failed') + SUM(payouts.amount_paise WHERE status <> 'failed') <= payments.amount_paise for the loan's deposit payment (Section 21.7).

6.3.14 payouts #

Indexes: uq_payouts_razorpayx_payout_id UNIQUE (razorpayx_payout_id); uq_payouts_dispute UNIQUE (dispute_id) WHERE status <> 'failed' — one live payout per dispute; a re-execute after failed reuses the row rather than inserting; ix_payouts_status (status); ix_payouts_user (user_id).

6.3.15 payout_details #

Indexes: uq_payout_details_user UNIQUE (user_id). Check constraint: (upi_id_encrypted IS NOT NULL) OR (bank_account_number_encrypted IS NOT NULL AND ifsc IS NOT NULL AND account_holder_name IS NOT NULL). The row is deleted outright on account-deletion finalisation (Section 6.10).

6.3.16 subscription_plans #

Indexes: uq_subscription_plans_code UNIQUE (code); uq_subscription_plans_razorpay_plan_id UNIQUE (razorpay_plan_id).

6.3.17 subscriptions #

Indexes: uq_subscriptions_user UNIQUE (user_id) — a concurrent second insert fails here and is mapped to 409 CONFLICT (Section 22.13); uq_subscriptions_razorpay_subscription_id UNIQUE (razorpay_subscription_id); ix_subscriptions_status (status) for the grace-expiry, reminder and reconciliation jobs; ix_subscriptions_current_period_end (current_period_end).

6.3.18 subscription_events #

Indexes: uq_subscription_events_razorpay_event_id UNIQUE (razorpay_event_id); ix_subscription_events_subscription (subscription_id, created_at).

6.3.19 disputes #

Indexes: uq_disputes_loan UNIQUE (loan_id); ix_disputes_community_status (community_id, status) for the admin dashboard; ix_disputes_status_escalated (status, escalated_at) for the operator queue; ix_disputes_created_at (created_at) WHERE status IN ('awaiting_borrower','under_review') for the 14-day stale-escalation scan (Section 8.3.8); ix_disputes_resolved_at (resolved_at) WHERE status = 'resolved' for the evidence-object purge (Section 8.3.27). Check constraints: forfeit_paise IS NULL OR (forfeit_paise BETWEEN 0 AND claimed_paise); (escalation_reason IS NULL) = (escalated_at IS NULL).

6.3.20 dispute_evidence #

Indexes: ix_dispute_evidence_dispute (dispute_id). Rows are kept with the loan (8 years); the objects are deleted 2 years after disputes.resolved_at (Section 6.10), after which the API returns url: null for the row.

6.3.21 conversations #

Indexes: uq_conversations_loan UNIQUE (loan_id).

6.3.22 messages #

Indexes: ix_messages_conversation_created (conversation_id, created_at) for cursor pagination; ix_messages_unread_owner (conversation_id) WHERE read_by_owner_at IS NULL; ix_messages_unread_borrower (conversation_id) WHERE read_by_borrower_at IS NULL — unread counts (Section 18.7, GET /me/messages/unread-count). Check constraints: char_length(body) <= 2000; char_length(body) > 0 OR attachment_key IS NOT NULL; (is_system AND sender_id IS NULL) OR (NOT is_system AND sender_id IS NOT NULL).

6.3.23 ratings #

Indexes: uq_ratings_loan_rater UNIQUE (loan_id, rater_id); ix_ratings_ratee (ratee_id, revealed_at) for public profile aggregates; ix_ratings_unrevealed_loan (loan_id) WHERE revealed_at IS NULL for the reveal job. Check constraints: score BETWEEN 1 AND 5; item_condition_score IS NULL OR item_condition_score BETWEEN 1 AND 5; (role_of_rater = 'borrower') = (item_condition_score IS NOT NULL).

6.3.24 notifications #

Indexes: ix_notifications_user_created (user_id, created_at DESC) for the in-app centre; ix_notifications_user_unread (user_id) WHERE read_at IS NULL for the unread-count endpoint; uq_notifications_user_dedupe UNIQUE (user_id, dedupe_key) WHERE dedupe_key IS NOT NULL; ix_notifications_created_at (created_at) for the 180-day purge (Section 8.3.27).

6.3.25 notification_preferences #

Indexes: uq_notification_preferences_user_category UNIQUE (user_id, category). Rows are created lazily with the Section 24.2 defaults on first read (GET /me/notification-preferences materialises any missing category rows); the security category is never rendered as an opt-out toggle (Section 24.2 owns which categories and events are mandatory).

6.3.26 push_subscriptions #

Indexes: uq_push_subscriptions_endpoint UNIQUE (endpoint); ix_push_subscriptions_user (user_id).

6.3.27 audit_logs #

Append-only. Indexes: ix_audit_logs_community_created (community_id, created_at DESC) for GET /communities/{id}/admin/audit-log; ix_audit_logs_target (target_type, target_id); ix_audit_logs_created_at (created_at) for GET /operator/audit-log and the 3-year purge (Section 8.3.32).

6.3.28 webhook_events #

Indexes: uq_webhook_events_provider_event_id UNIQUE (provider, event_id); ix_webhook_events_unprocessed (created_at) WHERE processed_at IS NULL; ix_webhook_events_created_at (created_at) for the 1-year purge (Section 8.3.27).

6.3.29 idempotency_keys #

Primary key: (user_id, key). Indexes: ix_idempotency_keys_expires_at (expires_at) for the purge job (Section 8.3.25).

6.3.30 feature_flags #

Managed via the operator console (Section 13.9); read with a short in-process cache (60 s TTL) by both apps/web and apps/worker to avoid a database round trip per request.

6.3.31 loan_photos #

Indexes: ix_loan_photos_loan (loan_id, kind, created_at). Application-level rule: at most 6 rows per (loan_id, uploaded_by, kind), enforced at confirm time (Section 17.12). Rows are kept with the loan (8 years, Section 6.10).

6.3.32 content_reports #

Indexes: uq_content_reports_reporter_target UNIQUE (reporter_id, target_type, target_id) — one report per reporter per item; a repeat returns 409 CONFLICT; ix_content_reports_status_created (status, created_at) for GET /operator/reports?type=&status=; ix_content_reports_target (target_type, target_id). Rate limit: 20 reports per day per user (Section 5.10).

6.3.33 operator_alerts #