# Property Tax Database Schemas

## Overview

The database schema manages property tax information, including property details, ownership, assessments, and audit trails. The system supports multi-tenant architecture and tracks the complete property lifecycle from creation to assessment.

***

## Database Tables

### 1. eg\_pt\_property

**Purpose**: Core table storing property master data and metadata.

<table><thead><tr><th width="173.86328125">Column Name</th><th width="159.2421875">Data Type</th><th width="126.28125">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for the property</td></tr><tr><td>propertyid</td><td>VARCHAR(256)</td><td>UNIQUE (with tenantid, status)</td><td>Human-readable property ID</td></tr><tr><td>tenantid</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier for multi-tenancy</td></tr><tr><td>surveyid</td><td>VARCHAR(256)</td><td></td><td>Survey number of the property</td></tr><tr><td>accountid</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Account identifier for the property</td></tr><tr><td>oldpropertyid</td><td>VARCHAR(128)</td><td></td><td>Previous/legacy property ID for migration</td></tr><tr><td>status</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Property status (ACTIVE, INACTIVE, etc.)</td></tr><tr><td>acknowldgementnumber</td><td>VARCHAR(128)</td><td></td><td>Acknowledgement number for property registration</td></tr><tr><td>propertytype</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Type of property (BUILT_UP, VACANT, etc.)</td></tr><tr><td>ownershipcategory</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Ownership category (INDIVIDUAL, INSTITUTIONAL, etc.)</td></tr><tr><td>usagecategory</td><td>VARCHAR(256)</td><td></td><td>Usage category (RESIDENTIAL, COMMERCIAL, etc.)</td></tr><tr><td>creationreason</td><td>VARCHAR(256)</td><td></td><td>Reason for property creation (NEW, SUBDIVISION, etc.)</td></tr><tr><td>nooffloors</td><td>BIGINT</td><td></td><td>Number of floors in the property</td></tr><tr><td>landarea</td><td>NUMERIC(10,2)</td><td></td><td>Total land area in square units</td></tr><tr><td>superbuiltuparea</td><td>NUMERIC(10,2)</td><td></td><td>Total super built-up area</td></tr><tr><td>linkedproperties</td><td>VARCHAR(2048)</td><td></td><td>Comma-separated list of linked property IDs</td></tr><tr><td>source</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Source system (MUNICIPAL_RECORDS, etc.)</td></tr><tr><td>channel</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Channel of creation (WEB, MOBILE, COUNTER, etc.)</td></tr><tr><td>createdby</td><td>VARCHAR(128)</td><td></td><td>User who created the record</td></tr><tr><td>lastModifiedBy</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>createdTime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp (epoch milliseconds)</td></tr><tr><td>lastModifiedTime</td><td>BIGINT</td><td></td><td>Last modification timestamp</td></tr><tr><td>additionaldetails</td><td>JSONB</td><td></td><td>Additional flexible attributes in JSON format</td></tr></tbody></table>

**Indexes**: propertyid+tenantid+status, linkedproperties, accountId, tenantid, status, oldpropertyid, acknowldgementnumber, lastmodifiedtime+id

***

### 2. eg\_pt\_owner

**Purpose**: Stores property owner information linking users to properties.

<table><thead><tr><th width="146.7265625">Column Name</th><th width="156.6953125">Data Type</th><th width="175.796875">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>ownerinfouuid</td><td>VARCHAR(256)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for owner record</td></tr><tr><td>tenantid</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>propertyid</td><td>VARCHAR(256)</td><td>FOREIGN KEY, NOT NULL</td><td>Reference to eg_pt_property.id</td></tr><tr><td>userid</td><td>VARCHAR(128)</td><td>UNIQUE (with propertyid), NOT NULL</td><td>User ID from user service</td></tr><tr><td>status</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Owner status (ACTIVE, INACTIVE)</td></tr><tr><td>isprimaryowner</td><td>BOOLEAN</td><td></td><td>Flag indicating primary owner</td></tr><tr><td>ownertype</td><td>VARCHAR(256)</td><td></td><td>Type of owner (INDIVIDUAL, COMPANY, etc.)</td></tr><tr><td>ownershippercentage</td><td>VARCHAR(128)</td><td></td><td>Percentage of ownership</td></tr><tr><td>institutionid</td><td>VARCHAR(128)</td><td></td><td>Reference to institution if applicable</td></tr><tr><td>relationship</td><td>VARCHAR(128)</td><td></td><td>Relationship with primary owner</td></tr><tr><td>createdby</td><td>VARCHAR(128)</td><td></td><td>User who created the record</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td></td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: tenantid, propertyid

***

### 3. eg\_pt\_institution

**Purpose**: Stores institutional owner details for properties owned by organisations.

<table><thead><tr><th width="134.46875">Column Name</th><th width="165.234375">Data Type</th><th width="191.3984375">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for institution</td></tr><tr><td>propertyid</td><td>VARCHAR(256)</td><td>FOREIGN KEY, NOT NULL</td><td>Reference to eg_pt_property.id</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>name</td><td>VARCHAR(1024)</td><td>NOT NULL</td><td>Name of the institution</td></tr><tr><td>nameofauthorizedperson</td><td>VARCHAR(1024)</td><td></td><td>Name of authorized person</td></tr><tr><td>type</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Type of institution (GOVERNMENT, PRIVATE, NGO, etc.)</td></tr><tr><td>designation</td><td>VARCHAR(128)</td><td></td><td>Designation of authorized person</td></tr><tr><td>createdby</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>User who created the record</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td></td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: tenantid, propertyid

***

### 4. eg\_pt\_address

**Purpose**: Stores property address and geolocation information.

<table><thead><tr><th width="140.2109375">Column Name</th><th width="152.66796875">Data Type</th><th width="193.57421875">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(256)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for address</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>propertyid</td><td>VARCHAR(256)</td><td>FOREIGN KEY, NOT NULL</td><td>Reference to eg_pt_property.id</td></tr><tr><td>doorno</td><td>VARCHAR(128)</td><td></td><td>Door/house number</td></tr><tr><td>plotno</td><td>VARCHAR(256)</td><td></td><td>Plot number</td></tr><tr><td>buildingName</td><td>VARCHAR(1024)</td><td></td><td>Building name</td></tr><tr><td>street</td><td>VARCHAR(1024)</td><td></td><td>Street name</td></tr><tr><td>landmark</td><td>VARCHAR(1024)</td><td></td><td>Nearby landmark</td></tr><tr><td>city</td><td>VARCHAR(512)</td><td></td><td>City name</td></tr><tr><td>pincode</td><td>VARCHAR(16)</td><td></td><td>Postal code</td></tr><tr><td>locality</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Locality/area code</td></tr><tr><td>district</td><td>VARCHAR(256)</td><td></td><td>District name</td></tr><tr><td>region</td><td>VARCHAR(256)</td><td></td><td>Region name</td></tr><tr><td>state</td><td>VARCHAR(256)</td><td></td><td>State name</td></tr><tr><td>country</td><td>VARCHAR(512)</td><td></td><td>Country name</td></tr><tr><td>latitude</td><td>NUMERIC(9,6)</td><td></td><td>Geographical latitude</td></tr><tr><td>longitude</td><td>NUMERIC(10,7)</td><td></td><td>Geographical longitude</td></tr><tr><td>createdby</td><td>VARCHAR(128)</td><td></td><td>User who created the record</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td></td><td>Last modification timestamp</td></tr><tr><td>additionaldetails</td><td>JSONB</td><td></td><td>Additional flexible attributes</td></tr></tbody></table>

**Indexes**: tenantid, locality, propertyid

***

### 5. eg\_pt\_unit

**Purpose**: Stores individual unit/floor details within a property.

<table><thead><tr><th width="133.4140625">Column Name</th><th width="166.05078125">Data Type</th><th width="177.84765625">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for unit</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>propertyid</td><td>VARCHAR(128)</td><td>FOREIGN KEY, NOT NULL</td><td>Reference to eg_pt_property.id</td></tr><tr><td>floorNo</td><td>BIGINT</td><td></td><td>Floor number</td></tr><tr><td>unitType</td><td>VARCHAR(256)</td><td></td><td>Type of unit (ROOM, FLOOR, etc.)</td></tr><tr><td>usageCategory</td><td>VARCHAR(2048)</td><td>NOT NULL</td><td>Usage category (RESIDENTIAL, COMMERCIAL, etc.)</td></tr><tr><td>occupancyType</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Occupancy type (OWNER, TENANT, UNOCCUPIED)</td></tr><tr><td>occupancyDate</td><td>BIGINT</td><td></td><td>Date of occupancy</td></tr><tr><td>carpetArea</td><td>NUMERIC(10,2)</td><td></td><td>Carpet area in square units</td></tr><tr><td>builtUpArea</td><td>NUMERIC(10,2)</td><td></td><td>Built-up area</td></tr><tr><td>plinthArea</td><td>NUMERIC(10,2)</td><td></td><td>Plinth area</td></tr><tr><td>superBuiltUpArea</td><td>NUMERIC(10,2)</td><td></td><td>Super built-up area</td></tr><tr><td>arv</td><td>NUMERIC(10,2)</td><td></td><td>Annual Rental Value</td></tr><tr><td>constructionType</td><td>VARCHAR(1024)</td><td></td><td>Type of construction (RCC, BRICK, etc.)</td></tr><tr><td>constructionDate</td><td>BIGINT</td><td></td><td>Construction completion date</td></tr><tr><td>dimensions</td><td>JSON</td><td></td><td>Unit dimensions in JSON format</td></tr><tr><td>active</td><td>BOOLEAN</td><td></td><td>Flag indicating if unit is active</td></tr><tr><td>createdby</td><td>VARCHAR(128)</td><td></td><td>User who created the record</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td></td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: tenantId, propertyid

***

### 6. eg\_pt\_document

**Purpose**: Stores document attachments related to properties.

<table><thead><tr><th width="126.74609375">Column Name</th><th width="153.6171875">Data Type</th><th width="185.79296875">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for document</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>entityid</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Entity ID (property/owner/unit) this document belongs to</td></tr><tr><td>documentType</td><td>VARCHAR(128)</td><td></td><td>Type of document (OWNERSHIP_PROOF, ID_PROOF, etc.)</td></tr><tr><td>fileStoreid</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>File store service reference ID</td></tr><tr><td>documentuid</td><td>VARCHAR(128)</td><td></td><td>Document unique identifier</td></tr><tr><td>status</td><td>VARCHAR(128)</td><td></td><td>Document status (ACTIVE, INACTIVE)</td></tr><tr><td>createdBy</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>User who created the record</td></tr><tr><td>lastModifiedBy</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>createdTime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastModifiedTime</td><td>BIGINT</td><td></td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: entityid, tenantid

***

#### 7. eg\_pt\_asmt\_assessment

**Purpose**: Stores property tax assessment information for each financial year.

<table><thead><tr><th width="147.0390625">Column Name</th><th width="147.40625">Data Type</th><th width="191.83984375">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(256)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for assessment</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>assessmentNumber</td><td>VARCHAR(64)</td><td>UNIQUE (with tenantId), NOT NULL</td><td>Human-readable assessment number</td></tr><tr><td>financialyear</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Financial year (e.g., 2023-24)</td></tr><tr><td>propertyId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Reference to property</td></tr><tr><td>status</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Assessment status (ACTIVE, CANCELLED, etc.)</td></tr><tr><td>source</td><td>VARCHAR(64)</td><td></td><td>Source system</td></tr><tr><td>channel</td><td>VARCHAR(256)</td><td></td><td>Channel of creation</td></tr><tr><td>assessmentDate</td><td>BIGINT</td><td>NOT NULL</td><td>Date of assessment</td></tr><tr><td>additionalDetails</td><td>JSONB</td><td></td><td>Additional flexible attributes</td></tr><tr><td>createdby</td><td>VARCHAR(64)</td><td></td><td>User who created the record</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(64)</td><td></td><td>User who last modified the record</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: assessmentNumber, propertyId, tenantid, financialyear, createdtime+id

***

### 8. eg\_pt\_asmt\_unitusage

**Purpose**: Stores unit-level usage details for tax assessments.

<table><thead><tr><th width="136.69140625">Column Name</th><th width="148.13671875">Data Type</th><th width="176.859375">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(256)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for unit usage</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>assessmentId</td><td>VARCHAR(256)</td><td>FOREIGN KEY, NOT NULL</td><td>Reference to eg_pt_asmt_assessment.id</td></tr><tr><td>unitId</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Reference to unit</td></tr><tr><td>usageCategory</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Usage category for assessment</td></tr><tr><td>occupancyType</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Occupancy type</td></tr><tr><td>occupancyDate</td><td>BIGINT</td><td></td><td>Date of occupancy</td></tr><tr><td>active</td><td>BOOLEAN</td><td></td><td>Flag indicating if record is active</td></tr><tr><td>createdby</td><td>VARCHAR(64)</td><td></td><td>User who created the record</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(64)</td><td></td><td>User who last modified the record</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: assessmentid

***

### 9. eg\_pt\_asmt\_document

**Purpose**: Stores document attachments related to assessments.

<table><thead><tr><th width="122.34375">Column Name</th><th width="143.03125">Data Type</th><th width="193.5">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for document</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>entityid</td><td>VARCHAR(128)</td><td>FOREIGN KEY, NOT NULL</td><td>Reference to eg_pt_asmt_unitusage.id</td></tr><tr><td>documentType</td><td>VARCHAR(128)</td><td></td><td>Type of document</td></tr><tr><td>fileStoreId</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>File store service reference ID</td></tr><tr><td>documentuid</td><td>VARCHAR(128)</td><td></td><td>Document unique identifier</td></tr><tr><td>status</td><td>VARCHAR(128)</td><td></td><td>Document status</td></tr><tr><td>createdBy</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>User who created the record</td></tr><tr><td>lastModifiedBy</td><td>VARCHAR(128)</td><td></td><td>User who last modified the record</td></tr><tr><td>createdTime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>lastModifiedTime</td><td>BIGINT</td><td>NOT NULL</td><td>Last modification timestamp</td></tr></tbody></table>

**Indexes**: entityid

***

### 10. eg\_pt\_property\_audit

**Purpose**: Audit trail table storing complete property snapshots for history tracking.

<table><thead><tr><th width="121.52734375">Column Name</th><th width="145.53125">Data Type</th><th width="154.6328125">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>audituuid</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Unique identifier for audit record</td></tr><tr><td>propertyid</td><td>VARCHAR(128)</td><td>NOT NULL</td><td>Reference to property</td></tr><tr><td>property</td><td>JSONB</td><td>NOT NULL</td><td>Complete property object snapshot in JSON</td></tr><tr><td>auditcreatedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Audit record creation timestamp</td></tr></tbody></table>

**Indexes**: propertyid

***

### 11. eg\_pt\_asmt\_assessment\_audit

**Purpose**: Audit trail for assessment records.

<table><thead><tr><th width="190.96484375">Column Name</th><th width="155.3671875">Data Type</th><th width="128.01171875">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Assessment ID</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>assessmentNumber</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Assessment number</td></tr><tr><td>financialyear</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Financial year</td></tr><tr><td>propertyId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Property reference</td></tr><tr><td>status</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Status</td></tr><tr><td>source</td><td>VARCHAR(64)</td><td></td><td>Source system</td></tr><tr><td>channel</td><td>VARCHAR(256)</td><td></td><td>Channel</td></tr><tr><td>assessmentDate</td><td>BIGINT</td><td>NOT NULL</td><td>Assessment date</td></tr><tr><td>additionalDetails</td><td>JSONB</td><td></td><td>Additional details</td></tr><tr><td>createdby</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Creator</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation time</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Last modifier</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Last modification time</td></tr><tr><td>auditcreatedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Audit record creation timestamp</td></tr></tbody></table>

***

### 12. eg\_pt\_asmt\_unitusage\_audit

**Purpose**: Audit trail for unit usage records.

<table><thead><tr><th width="154.1015625">Column Name</th><th width="162.40625">Data Type</th><th width="173.5546875">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Unit usage ID</td></tr><tr><td>tenantId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant/ULB identifier</td></tr><tr><td>assessmentId</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Assessment reference</td></tr><tr><td>unitId</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Unit reference</td></tr><tr><td>usageCategory</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Usage category</td></tr><tr><td>occupancyType</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Occupancy type</td></tr><tr><td>occupancyDate</td><td>BIGINT</td><td></td><td>Occupancy date</td></tr><tr><td>active</td><td>BOOLEAN</td><td></td><td>Active flag</td></tr><tr><td>createdby</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Creator</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation time</td></tr><tr><td>lastmodifiedby</td><td>VARCHAR(64)</td><td>NOT NULL</td><td>Last modifier</td></tr><tr><td>lastmodifiedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Last modification time</td></tr><tr><td>auditcreatedtime</td><td>BIGINT</td><td>NOT NULL</td><td>Audit record creation timestamp</td></tr></tbody></table>

***

### 13. eg\_pt\_property\_migration

**Purpose**: Tracks property data migration batches and progress.

<table><thead><tr><th width="137.56640625">Column Name</th><th width="143.58203125">Data Type</th><th width="226.12890625">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier for migration batch</td></tr><tr><td>batch</td><td>BIGINT</td><td>NOT NULL</td><td>Batch number</td></tr><tr><td>batchsize</td><td>BIGINT</td><td>NOT NULL</td><td>Size of the batch</td></tr><tr><td>tenantid</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant identifier</td></tr><tr><td>recordCount</td><td>BIGINT</td><td>NOT NULL</td><td>Number of records migrated</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr></tbody></table>

***

### 14. eg\_pt\_enc\_audit

**Purpose**: Audit trail for data encryption operations.

<table><thead><tr><th width="149.41796875">Column Name</th><th width="162.8671875">Data Type</th><th width="228.80078125">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier</td></tr><tr><td>batchoffset</td><td>BIGINT</td><td>NOT NULL</td><td>Batch offset for encryption</td></tr><tr><td>createdtime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr><tr><td>recordCount</td><td>BIGINT</td><td>NOT NULL</td><td>Number of records encrypted</td></tr><tr><td>tenantid</td><td>VARCHAR(256)</td><td>NOT NULL</td><td>Tenant identifier</td></tr><tr><td>message</td><td>VARCHAR(2048)</td><td>NOT NULL</td><td>Encryption message/log</td></tr><tr><td>encryptiontime</td><td>BIGINT</td><td>NOT NULL</td><td>Time taken for encryption</td></tr></tbody></table>

***

### 15. eg\_pt\_id\_enc\_audit

**Purpose**: Audit trail for property ID encryption operations.

<table><thead><tr><th width="158">Column Name</th><th width="171.31640625">Data Type</th><th width="177.0078125">Constraints</th><th>Description</th></tr></thead><tbody><tr><td>id</td><td>VARCHAR(128)</td><td>PRIMARY KEY, NOT NULL</td><td>Unique identifier</td></tr><tr><td>tenantid</td><td>VARCHAR(256)</td><td>UNIQUE (with propertyid, acknowldgementnumber), NOT NULL</td><td>Tenant identifier</td></tr><tr><td>propertyid</td><td>VARCHAR(256)</td><td></td><td>Property ID reference</td></tr><tr><td>acknowldgementnumber</td><td>VARCHAR(128)</td><td></td><td>Acknowledgement number</td></tr><tr><td>createdTime</td><td>BIGINT</td><td>NOT NULL</td><td>Creation timestamp</td></tr></tbody></table>

***

### Entity Relationship Diagram (ASCII)

```
     PROPERTY TAX SYSTEM - COMPLETE ER DIAGRAM               ║
╚═══════════════════════════════════════════════════════════════════════════════╝


┌─────────────────────────────────────────────────────────────────────────────┐
│                         CORE PROPERTY ENTITIES                               │
└─────────────────────────────────────────────────────────────────────────────┘

                    ┌───────────────────────────────────┐
                    │       eg_pt_property              │
                    ├───────────────────────────────────┤
                    │ PK  id                 VARCHAR(128)│
                    │     propertyid         VARCHAR(256)│
                    │     tenantid           VARCHAR(256)│
                    │     surveyid           VARCHAR(256)│
                    │     accountid          VARCHAR(128)│
                    │     oldpropertyid      VARCHAR(128)│
                    │     status             VARCHAR(128)│
                    │     acknowldgementnumber VARCHAR(128)│
                    │     propertytype       VARCHAR(256)│
                    │     ownershipcategory  VARCHAR(256)│
                    │     usagecategory      VARCHAR(256)│
                    │     creationreason     VARCHAR(256)│
                    │     nooffloors         BIGINT      │
                    │     landarea           NUMERIC(10,2)│
                    │     superbuiltuparea   NUMERIC(10,2)│
                    │     linkedproperties   VARCHAR(2048)│
                    │     source             VARCHAR(128)│
                    │     channel            VARCHAR(128)│
                    │     createdby          VARCHAR(128)│
                    │     lastModifiedBy     VARCHAR(128)│
                    │     createdTime        BIGINT      │
                    │     lastModifiedTime   BIGINT      │
                    │     additionaldetails  JSONB       │
                    └──────────┬─────────────────────────┘
                               │
             ┌─────────────────┼──────────────────┬──────────────────┐
             │                 │                  │                  │
             │                 │                  │                  │
             │                 │                  │                  │
   ┌─────────▼──────────┐  ┌──▼─────────────┐  ┌▼──────────────────┐  ┌────────▼────────────┐
   │   eg_pt_owner      │  │  eg_pt_unit    │  │  eg_pt_address    │  │ eg_pt_institution   │
   ├────────────────────┤  ├────────────────┤  ├───────────────────┤  ├─────────────────────┤
   │PK ownerinfouuid    │  │PK id           │  │PK id              │  │PK id                │
   │  VARCHAR(256)      │  │  VARCHAR(128)  │  │  VARCHAR(256)     │  │  VARCHAR(128)       │
   │FK propertyid       │  │FK propertyid   │  │FK propertyid      │  │FK propertyid        │
   │  VARCHAR(256)      │  │  VARCHAR(128)  │  │  VARCHAR(256)     │  │  VARCHAR(256)       │
   │  tenantid          │  │  tenantId      │  │  tenantId         │  │  tenantId           │
   │  VARCHAR(256)      │  │  VARCHAR(256)  │  │  VARCHAR(256)     │  │  VARCHAR(256)       │
   │  userid            │  │  floorNo       │  │  doorno           │  │  name               │
   │  VARCHAR(128)      │  │  BIGINT        │  │  VARCHAR(128)     │  │  VARCHAR(1024)      │
   │  status            │  │  unitType      │  │  plotno           │  │  nameofauthorized   │
   │  VARCHAR(128)      │  │  VARCHAR(256)  │  │  VARCHAR(256)     │  │   person            │
   │  isprimaryowner    │  │  usageCategory │  │  buildingName     │  │  VARCHAR(1024)      │
   │  BOOLEAN           │  │  VARCHAR(2048) │  │  VARCHAR(1024)    │  │  type               │
   │  ownertype         │  │  occupancyType │  │  street           │  │  VARCHAR(128)       │
   │  VARCHAR(256)      │  │  VARCHAR(256)  │  │  VARCHAR(1024)    │  │  designation        │
   │  ownershippercentage│ │  occupancyDate │  │  landmark         │  │  VARCHAR(128)       │
   │  VARCHAR(128)      │  │  BIGINT        │  │  VARCHAR(1024)    │  │  createdby          │
   │  institutionid     │  │  carpetArea    │  │  city             │  │  VARCHAR(128)       │
   │  VARCHAR(128)      │  │  NUMERIC(10,2) │  │  VARCHAR(512)     │  │  createdtime        │
   │  relationship      │  │  builtUpArea   │  │  pincode          │  │  BIGINT             │
   │  VARCHAR(128)      │  │  NUMERIC(10,2) │  │  VARCHAR(16)      │  │  lastmodifiedby     │
   │  createdby         │  │  plinthArea    │  │  locality         │  │  VARCHAR(128)       │
   │  VARCHAR(128)      │  │  NUMERIC(10,2) │  │  VARCHAR(128)     │  │  lastmodifiedtime   │
   │  createdtime       │  │  superBuiltUpArea│ │  district         │  │  BIGINT             │
   │  BIGINT            │  │  NUMERIC(10,2) │  │  VARCHAR(256)     │  └─────────────────────┘
   │  lastmodifiedby    │  │  arv           │  │  region           │
   │  VARCHAR(128)      │  │  NUMERIC(10,2) │  │  VARCHAR(256)     │
   │  lastmodifiedtime  │  │  constructionType│ │  state            │
   │  BIGINT            │  │  VARCHAR(1024) │  │  VARCHAR(256)     │
   └────────────────────┘  │  constructionDate│ │  country          │
                           │  BIGINT        │  │  VARCHAR(512)     │
                           │  dimensions    │  │  latitude         │
                           │  JSON          │  │  NUMERIC(9,6)     │
                           │  active        │  │  longitude        │
                           │  BOOLEAN       │  │  NUMERIC(10,7)    │
                           │  createdby     │  │  createdby        │
                           │  VARCHAR(128)  │  │  VARCHAR(128)     │
                           │  createdtime   │  │  createdtime      │
                           │  BIGINT        │  │  BIGINT           │
                           │  lastmodifiedby│  │  lastmodifiedby   │
                           │  VARCHAR(128)  │  │  VARCHAR(128)     │
                           │  lastmodifiedtime│ │  lastmodifiedtime │
                           │  BIGINT        │  │  BIGINT           │
                           └────────────────┘  │  additionaldetails│
                                               │  JSONB            │
                                               └───────────────────┘
```

```
 ┌────────────────────┐
                           │  eg_pt_document    │
                           ├────────────────────┤
                           │PK id               │
                           │  VARCHAR(128)      │
                           │  tenantId          │
                           │  VARCHAR(256)      │
                           │  entityid          │
                           │  VARCHAR(256)      │
                           │  documentType      │
                           │  VARCHAR(128)      │
                           │  fileStoreid       │
                           │  VARCHAR(128)      │
                           │  documentuid       │
                           │  VARCHAR(128)      │
                           │  status            │
                           │  VARCHAR(128)      │
                           │  createdBy         │
                           │  VARCHAR(128)      │
                           │  lastModifiedBy    │
                           │  VARCHAR(128)      │
                           │  createdTime       │
                           │  BIGINT            │
                           │  lastModifiedTime  │
                           │  BIGINT            │
                           └────────────────────┘
                           (Links to property/owner/unit via entityid)
```

```
 ASSESSMENT ENTITIES                                 │
└─────────────────────────────────────────────────────────────────────────────┘

                    ┌────────────────────────────────┐
                    │  eg_pt_asmt_assessment         │
                    ├────────────────────────────────┤
                    │PK id                VARCHAR(256)│
                    │  tenantId           VARCHAR(256)│
                    │  assessmentNumber   VARCHAR(64) │
                    │  financialyear      VARCHAR(256)│
                    │  propertyId         VARCHAR(256)│ (ref to eg_pt_property)
                    │  status             VARCHAR(64) │
                    │  source             VARCHAR(64) │
                    │  channel            VARCHAR(256)│
                    │  assessmentDate     BIGINT      │
                    │  additionalDetails  JSONB       │
                    │  createdby          VARCHAR(64) │
                    │  createdtime        BIGINT      │
                    │  lastmodifiedby     VARCHAR(64) │
                    │  lastmodifiedtime   BIGINT      │
                    └──────────────┬─────────────────┘
                                   │
                                   │ 1:N
                                   │
                    ┌──────────────▼─────────────────┐
                    │  eg_pt_asmt_unitusage          │
                    ├────────────────────────────────┤
                    │PK id               VARCHAR(256)│
                    │  tenantId          VARCHAR(256)│
                    │FK assessmentId     VARCHAR(256)│
                    │  unitId            VARCHAR(64) │
                    │  usageCategory     VARCHAR(256)│
                    │  occupancyType     VARCHAR(64) │
                    │  occupancyDate     BIGINT      │
                    │  active            BOOLEAN     │
                    │  createdby         VARCHAR(64) │
                    │  createdtime       BIGINT      │
                    │  lastmodifiedby    VARCHAR(64) │
                    │  lastmodifiedtime  BIGINT      │
                    └──────────────┬─────────────────┘
                                   │
                                   │ 1:N
                                   │
                    ┌──────────────▼─────────────────┐
                    │  eg_pt_asmt_document           │
                    ├────────────────────────────────┤
                    │PK id               VARCHAR(128)│
                    │  tenantId          VARCHAR(256)│
                    │FK entityid         VARCHAR(128)│ (ref to unitusage)
                    │  documentType      VARCHAR(128)│
                    │  fileStoreId       VARCHAR(128)│
                    │  documentuid       VARCHAR(128)│
                    │  status            VARCHAR(128)│
                    │  createdBy         VARCHAR(128)│
                    │  lastModifiedBy    VARCHAR(128)│
                    │  createdTime       BIGINT      │
                    │  lastModifiedTime  BIGINT      │
                    └────────────────────────────────┘
```

```
  AUDIT TABLES                                    │
└─────────────────────────────────────────────────────────────────────────────┘

  ┌───────────────────────────┐  ┌─────────────────────────────┐  ┌──────────────────────────┐
  │ eg_pt_property_audit      │  │ eg_pt_asmt_assessment_audit │  │ eg_pt_asmt_unitusage     │
  ├───────────────────────────┤  ├─────────────────────────────┤  │         _audit           │
  │ audituuid      VARCHAR(128)│  │ id              VARCHAR(256)│  ├──────────────────────────┤
  │ propertyid     VARCHAR(128)│  │ tenantId        VARCHAR(256)│  │ id          VARCHAR(256) │
  │ property       JSONB       │  │ assessmentNumber VARCHAR(64)│  │ tenantId    VARCHAR(256) │
  │ auditcreatedtime BIGINT    │  │ financialyear   VARCHAR(256)│  │ assessmentId VARCHAR(256)│
  └───────────────────────────┘  │ propertyId      VARCHAR(256)│  │ unitId      VARCHAR(64)  │
                                  │ status          VARCHAR(64) │  │ usageCategory VARCHAR(256)│
                                  │ source          VARCHAR(64) │  │ occupancyType VARCHAR(64)│
                                  │ channel         VARCHAR(256)│  │ occupancyDate BIGINT     │
                                  │ assessmentDate  BIGINT      │  │ active      BOOLEAN      │
                                  │ additionalDetails JSONB     │  │ createdby   VARCHAR(64)  │
                                  │ createdby       VARCHAR(64) │  │ createdtime BIGINT       │
                                  │ createdtime     BIGINT      │  │ lastmodifiedby VARCHAR(64)│
                                  │ lastmodifiedby  VARCHAR(64) │  │ lastmodifiedtime BIGINT  │
                                  │ lastmodifiedtime BIGINT     │  │ auditcreatedtime BIGINT  │
                                  │ auditcreatedtime BIGINT     │  └──────────────────────────┘
                                  └─────────────────────────────┘
```

```
 UTILITY TABLES                                    │
└─────────────────────────────────────────────────────────────────────────────┘

  ┌─────────────────────────┐  ┌──────────────────────────┐  ┌───────────────────────────┐
  │ eg_pt_property_migration│  │  eg_pt_enc_audit         │  │  eg_pt_id_enc_audit       │
  ├─────────────────────────┤  ├──────────────────────────┤  ├───────────────────────────┤
  │PK id         VARCHAR(128)│  │PK id         VARCHAR(128)│  │PK id         VARCHAR(128) │
  │  batch       BIGINT      │  │  batchoffset BIGINT      │  │  tenantid    VARCHAR(256) │
  │  batchsize   BIGINT      │  │  createdtime BIGINT      │  │  propertyid  VARCHAR(256) │
  │  tenantid    VARCHAR(256)│  │  recordCount BIGINT      │  │  acknowldgementnumber     │
  │  recordCount BIGINT      │  │  tenantid    VARCHAR(256)│  │              VARCHAR(128) │
  │  createdtime BIGINT      │  │  message     VARCHAR(2048)│ │  createdTime BIGINT       │
  └─────────────────────────┘  │  encryptiontime BIGINT   │  └───────────────────────────┘
                                └──────────────────────────┘
```

```
  RELATIONSHIP SUMMARY                                │
└─────────────────────────────────────────────────────────────────────────────┘

FOREIGN KEY RELATIONSHIPS:
  eg_pt_property (1) ──────< eg_pt_owner (N)
                     └─────< eg_pt_unit (N)
                     └─────< eg_pt_address (N)
                     └─────< eg_pt_institution (N)

  eg_pt_asmt_assessment (1) ──────< eg_pt_asmt_unitusage (N)
  eg_pt_asmt_unitusage (1) ───────< eg_pt_asmt_document (N)

REFERENCE RELATIONSHIPS (No FK):
  eg_pt_asmt_assessment.propertyId ────> eg_pt_property.id
  eg_pt_document.entityid ─────────────> property/owner/unit (varies)


LEGEND:
═══════════════════════════════════════════════════════════════════════════════
PK  = Primary Key
FK  = Foreign Key
──< = One-to-Many Relationship (Foreign Key)
───> = Reference Relationship (No FK Constraint)
(N) = Many side of relationship
(1) = One side of relationship
═══════════════════════════════════════════════════════════════════════════════
```

### Relationship Summary

#### Primary Relationships

1. **Property → Owners** (1:N)
   * One property can have multiple owners
   * FK: eg\_pt\_owner.propertyid → eg\_pt\_property.id
2. **Property → Units** (1:N)
   * One property can have multiple units/floors
   * FK: eg\_pt\_unit.propertyid → eg\_pt\_property.id
3. **Property → Address** (1:N)
   * One property can have multiple addresses
   * FK: eg\_pt\_address.propertyid → eg\_pt\_property.id
4. **Property → Institution** (1:N)
   * One property can be linked to multiple institutions
   * FK: eg\_pt\_institution.propertyid → eg\_pt\_property.id
5. **Assessment → Unit Usage** (1:N)
   * One assessment can have multiple unit usage entries
   * FK: eg\_pt\_asmt\_unitusage.assessmentId → eg\_pt\_asmt\_assessment.id
6. **Unit Usage → Assessment Documents** (1:N)
   * One unit usage record can have multiple documents
   * FK: eg\_pt\_asmt\_document.entityid → eg\_pt\_asmt\_unitusage.id

#### Reference Relationships (No FK Constraint)

1. **Assessment → Property** (N:1)
   * Many assessments reference one property
   * Ref: eg\_pt\_asmt\_assessment.propertyId → eg\_pt\_property.id
2. **Document → Entity** (N:1)
   * Documents can be linked to various entities (property, owner, unit)
   * Ref: eg\_pt\_document.entityid → various tables


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.digit.org/local-governance/access/local-governance-stack/property-tax/property-tax-architecture/property-tax-database-schemas.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.