# IDRBT Domain Registry — Complete Data Schema

**Based on:** Live data dump from `registrar.idrbt.ac.in` (2026-06-08)
**Sources:**
- `GET /api/dr/user/all` → 5,461 user records
- `GET /api/dr/invoice/getByOrgId/{orgId}` → 1,535 invoice records across 1,327 orgs
- `GET /api/dr/invoice/getBydomainId/{domainId}` → invoice by domain
- `GET /api/dr/user/deleted-users` → 219 deleted user records
- `GET /api/dr/static/getAll` → system configuration

---

## 1. User Entity (`/api/dr/user/all` — 5,461 records)

### Primary Key & Identity

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `id` | `int64` | 100% | Auto-increment primary key (7545 max observed) |
| `userId` | `varchar(255)` | 100% | Login ID — email address (e.g. `icub1918@gmail.com`) |
| `userName` | `varchar(255)` | 100% | Full name of user/contact person (e.g. `MRINAL KALITA`) |
| `mobileNumber` | `varchar(20)` | 100% | Mobile phone number (e.g. `8003699077`) |
| `countryCode` | `varchar(5)` | 65.9% | Country dial code (observed: `+91`, `+964`, `+977`, `+1`) |

### Authentication (CRITICAL SENSITIVITY)

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `encryptedPassword` | `varchar(60)` | **100%** | Bcrypt hash (`$2a$10$` rounds) — **all 5,461 records** |
| `previousPassword` | `varchar(60)` | 13.1% | Previous password bcrypt hash (715 records) |
| `previousPassword1` | `varchar(60)` | 0% | Legacy field, always null |
| `previousPassword2` | `varchar(60)` | 0% | Legacy field, always null |
| `otp` | `varchar(60)` | **49.9%** | OTP stored as bcrypt hash (**2,726 live OTPs**) |
| `otpExpiryTime` | `ISO8601` | 49.9% | OTP expiry timestamp |
| `twoFactorAuthentication` | `boolean` | 100% | 2FA enabled? **Overwhelmingly false** |

### Role-Based Access

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `userRoles` | `array[{roleId, roleName, roleDescription, roleStatus}]` | 100% | Role assignments (see §1a) |
| `organisationId` | `int64` | 100% | FK → Organisation |
| `designation` | `varchar(100)` | 53.0% | Job title (e.g. `CLERK`, `MANAGER`, `ASST. GENERAL MANAGER`) |

### Login Activity & Audit

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `loginAttemptCount` | `int32` | 100% | Failed login attempts |
| `loginAttemptedClientIP` | `varchar` | 50.7% | Client IP(s) from last attempt (comma-separated) |
| `loginAttemptedClientDeviceType` | `varchar` | 50.7% | User-Agent string (e.g. `Windows NT 10.0; Win64; x64`) |
| `loginAttemptedDateTime` | `ISO8601` | 50.7% | Last login attempt timestamp |
| `lastSuccessfulLoginDatetime` | `ISO8601` | 49.3% | Last successful login timestamp |
| `initialLoginStatus` | `boolean` | 100% | Completed initial login? |
| `isLoggedIn` | `boolean` | 49.3% | Currently logged in? |

### Account State

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `active` | `boolean` | 100% | Account active/deactivated |
| `isDeleted` | `boolean` | 6.0% | Soft-delete flag |
| `isOnboardingCompleted` | `boolean` | 100% | Onboarding wizard done? |
| `isUatCompleted` | `boolean` | 100% | UAT onboarding done? |
| `onboardingStepIndex` | `int32` | 26.1% | Current step in onboarding (0-6 observed) |

### Timestamps & Metadata

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `createdDateTime` | `ISO8601` | 0% | Always null in dump (set at DB level?) |
| `modifiedDateTime` | `ISO8601` | 0% | Always null |
| `createdBy` | — | 0% | Always null |
| `createdByEmailId` | — | 0% | Always null |
| `modifiedBy` | — | 0% | Always null |
| `modifiedByEmailId` | — | 0% | Always null |

### Other

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `profilePicture` | `text (base64)` | 0.7% | Profile image as base64 BLOB |
| `organisationDetailsDto` | `object` | 100% | Embedded org details (see §3) |
| `reCaptchaToken` | — | 0% | Always null |

### 1a. User Roles (observed values)

| Role Name | Users Assigned | Purpose |
|-----------|---------------|---------|
| **Super Admin** | **2,573** | Organisation-level super admin |
| **Billing Officer** | **1,312** | Financial/billing operations |
| **Administrative Officer** | **1,304** | Admin contact role |
| **Technical Officer** | **1,296** | DNS/domain technical operations |

Users can hold multiple roles. Total assignments: 6,485 across 5,461 users.

---

## 2. Organisation Entity

### 2a. From `organisationDetailsDto` (embedded in user record)

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `organisationDetailsId` | `int64` | varies | Org PK |
| `institutionName` | `varchar` | varies | Bank/institution name |
| `organisationEmail` | `varchar` | varies | Org email |
| `address` | `text` | varies | Registered address |
| `city` | `varchar` | varies | City |
| `state` | `varchar` | varies | State |
| `pincode` | `int32` | varies | Postal code |
| `mobileNumber` | `varchar` | varies | Org phone |
| `stdTelephone` | `varchar` | varies | STD/landline |
| `applicationId` | `varchar` | varies | Application reference |

### 2b. From `organisationDetails` endpoint (`/dr/organisationDetails/getDetailsById/{id}`)
Extended fields not present in embedded DTO:
- `gstin` — GST identification number
- `panNumber` — PAN of organisation
- `orgTan` — TAN number
- `orgPan` — Organisation PAN
- `organisationGstin` — GSTIN

---

## 3. Invoice / Billing Entity (`/api/dr/invoice/getByOrgId/{orgId}` — 1,535 records)

### Invoice Identity & Relations

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `id` | `int64` | 100% | Invoice PK (range: 1–29,981) |
| `organizationId` | `int64` | 100% | FK → Organisation |
| `domainId` | `int64` | 100% | FK → Domain |
| `domainName` | `varchar(255)` | 100% | Domain name (e.g. `gsc.bank.in`, `sbi.bank.in`) |
| `organizationName` | `varchar(255)` | 100% | Org name at time of invoice |

### Financial Fields

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `finalAmount` | `decimal(18,2)` | **100%** | Total billed amount (range: ₹3.90 – ₹4,96,800) |
| `taxAmount` | `decimal(18,2)` | 94.0% | Tax component |
| `domainPrice` | `int64` | 98.5% | Domain registration/renewal price |
| `aliasPrice` | `int64` | 98.7% | Name identifier / alias price |
| `nsRecordPrice` | `int64` | 99.4% | Name server record price |
| `gstPercent` | `int` | 97.7% | GST percentage (observed: 18% most common) |
| `tdsPercent` | `int` | 97.7% | TDS percentage (observed: 0%, 2%) |
| `tdsAmount` | `decimal` | 95.0% | TDS deducted amount |
| `rebateAmount` | `int` | 91.3% | Rebate/discount applied |

### Payment State

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `paymentStatus` | `enum` | **100%** | See §3a below |
| `invoiceType` | `enum` | 2.3% | See §3b below |

### Domain Services

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `nameServerCount` | `int` | 92.8% | Number of name servers (0–9 observed) |
| `nameIdentifierCount` | `int` | 93.3% | Number of name identifiers (0–5 observed) |

### Contact & Timestamps

| Field | Type | Fill Rate | Description |
|-------|------|-----------|-------------|
| `contactOfficerEmails` | `text` | **100%** | Comma-separated contact officer emails |
| `createdDateTime` | `ISO8601` | **100%** | Invoice generation timestamp |
| `adminEmail` | `varchar` | 0% | Always null in this endpoint |
| `technicalEmail` | `varchar` | 0% | Always null in this endpoint |
| `billingEmail` | `varchar` | 0% | Always null in this endpoint |

### 3a. Payment Statuses (observed)

| Status | Count | Meaning |
|--------|-------|---------|
| `Payment Approved` | **1,455** | Payment approved by admin |
| `Ready For Payment` | **51** | Invoice generated, awaiting payment |
| `Payment Completed` | **26** | Payment fully processed |
| `Payment Under Review` | **3** | Payment flagged for review |

### 3b. Invoice Types (observed)

| Type | Count | Description |
|------|-------|-------------|
| `nIRecPurchase` | ~35 | Name Identifier purchase invoice |
| (null) | ~1,500 | Standard domain reg/renewal invoice |

---

## 4. Domain Entity (from invoice + domain endpoints)

| Field | Source | Description |
|-------|--------|-------------|
| `domainId` | Invoice, Domain endpoint | PK |
| `domainName` | Invoice, Domain endpoint | e.g. `sbi.bank.in` |
| `bankName` | Domain endpoint | Bank name for this domain |
| `organisationId` | Invoice, Domain endpoint | FK |
| `applicationStatus` | Domain endpoint | Application lifecycle state |
| `paymentStatus` | Both | Payment state |
| `status` | Domain endpoint | Domain lifecycle state |
| `registrationDate` | Domain endpoint | First registered |
| `actualNixiExpiryDate` | Domain endpoint | Registry expiry |
| `alignedRenewalDate` | Domain endpoint | Aligned renewal date |
| `numberOfYears` | Domain endpoint | Registration tenure |
| `domainPrice` | Both | Registration price |
| `aliasPrice` | Both | Name identifier price |
| `isOnboardingDomain` | Domain endpoint | Is this an onboarding domain? |
| `isCoOperativeBank` | Domain endpoint | Cooperative bank flag |
| `nameServerCount` | Both | Number of NS records |
| `aliasCount` | Both | Number of aliases |

---

## 5. Cross-Reference: Billing → Users

The billing dump (`billing-details.json`) enriches each invoice record with `associatedUsers[]` — all users from the organisation:

```json
{
  "associatedUsers": [
    {
      "userId": "manoj.patel@gscbank.coop",
      "userName": "MANOJ M PATEL",
      "mobileNumber": "9876543210",
      "roles": ["Super Admin", "Administrative Officer"],
      "designation": "MANAGER",
      "isActive": true,
      "organisationDetailsDto": { ... }
    }
  ]
}
```

**Coverage**: 1,535 invoices → all matched to at least 1 user. **0 orphan invoices.**

---

## 6. Deleted User Entity (`/api/dr/user/deleted-users` — 219 records)

Same structure as User Entity plus audit fields:

| Field | Type | Description |
|-------|------|-------------|
| `rgntUserAuditId` | `int64` | Audit trail PK |
| (all user fields) | — | Same as §1 with soft-delete state |

---

## 7. Static Config Entity (`/api/dr/static/getAll`)

| Field | Type | Description |
|-------|------|-------------|
| `configType` | `varchar` | Configuration category |
| `configValue` | `varchar` | Configuration value |

Observed types: `GST`, `TDS`, `email-regex`, `pricing-rules`

---

## 8. API Surface — Endpoint Auth Status (Verified 2026-06-08)

| Endpoint | Auth Required | Data Exposed | Risk |
|----------|:------------:|-------------|------|
| `GET /api/dr/user/all` | **❌ No** | 5,461 users with bcrypt hashes, OTPs, IPs | 🔴 Critical |
| `GET /api/dr/user/deleted-users` | **❌ No** | 219 deleted users | 🔴 High |
| `GET /api/dr/invoice/getByOrgId/{orgId}` | **❌ No** | **1,535 invoices across 1,327 orgs** | 🔴 High |
| `GET /api/dr/invoice/getBydomainId/{domainId}` | **❌ No** | Invoice by domain | 🟠 Medium |
| `GET /api/dr/billingHistory/all?userId=` | **❌ No** | Billing history (empty in current data) | 🟠 Medium |
| `GET /api/dr/billingHistory/getBillingHistoryById/{id}` | **❌ No** | Billing record by ID | 🟠 Medium |
| `GET /api/dr/static/getAll` | **❌ No** | System config (GST, TDS, pricing) | 🟡 Low-Med |
| `GET /api/dr/static/getByType/{type}` | **❌ No** | Config by category | 🟡 Low-Med |
| `GET /api/dr/user/verify-user/{userId}` | **❌ No** | User enumeration (pre-auth) | 🟡 Low |
| `GET /api/dr/user/get/{userId}` | **❌ No** | Single user record | 🟠 Medium |
| `GET /api/dr/links/all` | **❌ No** | Portal links config | 🟢 Low |
| `GET /api/dr/departments/all` | **❌ No** | Department list | 🟢 Low |
| `GET /api/dsc/getTokenRequest` | **❌ No** | DSC session initiation | 🟠 Medium |
| `GET /api/dsc/getTokenList` | **❌ No** | DSC token enumeration | 🟠 Medium |
| `GET /api/dsc/getCertificateRequest` | **❌ No** | DSC certificate listing | 🟠 Medium |
| `GET /api/dr/invoice/getInvoiceTemplate` | **🔐 Partial** | Invoice template (binary, accessed) | — |

---

## 9. Entity Relationship Diagram (Logical)

```
┌──────────────────────┐        ┌──────────────────────┐
│    Organisation      │        │       User           │
│──────────────────────│        │──────────────────────│
│ organisationDetailsId│◄───────│ organisationId       │
│ institutionName      │        │ userId (email)       │
│ gstin                │        │ userName             │
│ panNumber            │        │ mobileNumber         │
│ address, city, state │        │ encryptedPassword ◄──┼─── CRITICAL
│ organisationEmail    │        │ otp (bcrypt)    ◄────┼─── CRITICAL
└──────────────────────┘        │ userRoles[]           │
         ▲                      │ loginAttemptedClientIP│
         │                      └──────────────────────┘
         │                                │
         │                                │ 1:N
         │                         ┌──────┴──────┐
         │                         │  Invoice    │
         │                         │─────────────│
         │                         │ id          │
         │                         │ domainId    │
         ├─────────────────────────│ organizationId│
         │                         │ finalAmount │
         │                         │ paymentStatus│
         │                         │ createdDateTime
         │                         └─────────────┘
         │                                │
         │                                │ 1:1
         │                         ┌──────┴──────┐
         │                         │   Domain    │
         │                         │─────────────│
         │                         │ domainId    │
         └─────────────────────────│ domainName  │
                                   │ bankName    │
                                   │ registrationDate
                                   └─────────────┘
```

---

## 10. File Inventory

| File | Path | Size | Contents |
|------|------|------|----------|
| Users dump | `data/users-all.json` | ~27 MB | 5,461 full user records |
| Deleted users | `data/users-deleted.json` | ~500 KB | 219 deleted user records |
| Billing details | `data/billing-details.json` | 5.2 MB | **1,535 cross-referenced invoices** |
| Billing summary | `data/billing-details-summary.md` | 268 KB | Human-readable summary table |
| Static config | `data/static-config.json` | — | System configuration |
| Links config | `data/links.json` | — | Portal URL configuration |
| Analysis | `angular-app/ANALYSIS.md` | — | Full Angular app reverse engineering |
| Schema (this) | `schema/complete-data-schema.md` | — | This document |
| Disclosure | `disclosure/cert-in-email.md` | — | CERT-In responsible disclosure |