Skip to content

Database Schema

dichit-backend uses PostgreSQL 16 as its primary datastore, described entirely by prisma/schema.prisma. The schema is the single source of truth; migrations and the generated client are derived from it.

At a glance

  • 113 models, 104 enums, 434 declared indexes/unique constraints (grep -c on schema.prisma).
  • IDs are cuid()-style strings by default (e.g. User.id), amounts/balances use PostgreSQL Decimal, timestamps are DateTime with created_at / updated_at / optional soft-delete deleted_at.
  • Column names are snake_case via @map; Prisma fields are camelCase.

Domain clusters

flowchart LR
    USR["User / Auth<br/>User, UserAuthProvider, UserSession, UserLoginEvent"]
    SUB["Subscriber / Enrollment<br/>Subscriber, Nominee, Address, EmploymentDetails"]
    CO["Company tenancy<br/>Company, Branch, Membership, AccessRole"]
    PRG["Programs & Cycles<br/>Program, ProgramCycle, EnrolledSubscriber"]
    AUC["Auctions & Bids<br/>Auction, AuctionBid, AuctionPrebid, BidActivity*"]
    PAY["Billing / Payments<br/>CycleInvoice, CycleTransaction, GatewayPayload, Refunds"]
    DOC["Documents<br/>DocumentTemplate, DocumentSubmission, SignatureAsset"]
    NOT["Notifications<br/>NotificationTemplate, UserNotification, NotificationQueue"]
    CAM["Engagement<br/>Challenges, Campaign, FAQ, Banner, TrackedLink"]
    LOC["Locations<br/>Country, State, District, City, Pincode, PostOffice"]

    USR --- SUB
    USR --- CO
    CO --- PRG
    PRG --- AUC
    PRG --- PAY
    USR --- DOC
    USR --- NOT
    PRG --- CAM
    SUB --- LOC
    CO --- LOC

Core identity

  • User — platform identity, role (SUBSCRIBER/COMPANY/SUPERADMIN), profileStatus, accountType (incl. DEMO, APPLE_APP_REVIEW, INTERNAL_TEST).
  • UserAuthProvider — bound OAuth/SMS sign-in methods (PHONE_OTP, WHATSAPP_OTP, EMAIL_PASSWORD, GOOGLE, APPLE, META, LINKEDIN).
  • UserSession / UserLoginEvent — session + audit trail.
  • Subscriber / Nominee / SubscriberEmploymentDetails — KYC/profile data.

Companies, branches, and employees

  • Company.id is the canonical tenant identifier. Company login User.id values are identities, not tenant IDs.
  • Branch owns operational contact details, one optional BranchAddress, bank accounts, and programs. Every company has exactly one live headquarters branch.
  • Program has required companyId and branchId; a composite foreign key guarantees that the branch belongs to the same company.
  • CompanyMembership binds one company-side user to exactly one company.
  • Permission, AccessRole, RolePermission, and MembershipRoleAssignment implement additive company/branch/program access.
  • CompanyEmployeeInvitation stores only an HMAC token hash and proposed assignments; AuthorizationAuditLog records security-sensitive mutations.
  • BankAccount is branch-owned. Company legal registration, managers, MOU, policies, presets, and security types remain company-owned.

Programs, cycles, auctions

  • Program (chit program) → ProgramCycle (monthly cycles) → EnrolledSubscriber.
  • Auction holds auction state; AuctionBid + AuctionPrebid are the bidding records; AuctionBidActivitySequence/Event and AuctionActionLog are the event/audit stream that powers the WebSocket log and replay.
  • CycleWinner, CycleInvoice, CycleTransaction, GatewayPayload, Refund* form the billing and payout flow.

Notifications & engagement

  • NotificationTemplate → UserNotification → NotificationQueue (with NotificationEvent for delivery tracking).
  • Challenge, Campaign, FAQ, Banner, TrackedLink/TrackedLinkClick, WebsiteVisitor, Lead.

Auxiliary

  • AppVersion (release gates), SupportTicket, ReminderSchedule, CompanyEmployeeInvitation, DeletedUser, SentryGithubIssueMapping, SuperAdminFileUpload / CompanyFileUpload / SubscriberFileUpload.

Naming and conventions

  • Model name → table: Prisma @@map or default pluralisation; explicit @map("snake_case") on columns.
  • Timestamps: every mutable model has created_at, updated_at; soft-delete models add deleted_at.
  • Decimal money: Decimal fields (e.g. CycleInvoice.amount) are returned as strings by the API (Fastify bigint: 'string' serializer covers BigInt; Decimal is serialised by Prisma/JSON as string).
  • Consistency: adding a model means adding a matching domain entity (see development/folder-structure.md).

How to read the schema

Open it in an editor with Prisma support, or browse with:

pnpm studio:local     # Prisma Studio on local DB (needs docker + migrate)