# Property Valuation Portal - Database ERD (Entity Relationship Diagram)
## Document Information
- **Version**: 1.0
- **Date**: 2025-12-21
- **Database**: PostgreSQL 18+ with PostGIS extension
- **Total Tables**: 28 tables
- **Naming Convention**: snake_case
---
## Table of Contents
1. [ER Diagram Overview](#er-diagram-overview)
2. [Core Domain Tables](#core-domain-tables)
3. [Supporting Tables](#supporting-tables)
4. [Reference/Lookup Tables](#referencelookup-tables)
5. [User & Security Tables](#user--security-tables)
6. [Audit & Logging Tables](#audit--logging-tables)
7. [Relationships Summary](#relationships-summary)
8. [Indexes Strategy](#indexes-strategy)
9. [SQL Schema Scripts](#sql-schema-scripts)
---
## ER Diagram Overview
```
┌─────────────────────────────────────────────────────────────────────────┐
│ CORE DOMAIN LAYER │
│ │
│ ┌──────────────┐ 1:1 ┌──────────────┐ 1:1 │
│ │ valuations │────────────│ properties │────────────┐ │
│ └──────┬───────┘ └──────┬───────┘ │ │
│ │ │ │ │
│ │ 1:1 │ 1:1 │ │
│ │ │ │ │
│ ┌──────▼───────┐ ┌──────▼───────┐ ┌──────▼───────┐ │
│ │ applicants │ │ property_ │ │ boundaries │ │
│ │ │ │ details │ │ │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │ │
│ │ 1:1 │
│ │ │
│ ┌──────▼───────┐ │
│ │ valuation_ │ │
│ │ data │ │
│ └──────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
│
│ FK relationships
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ SUPPORTING DATA LAYER │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ documents │ │ photos │ │ comments │ │
│ │ │ │ │ │ │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │ 1:M │ 1:M │ 1:M │
│ │ │ │ │
│ └────────────────────────┴────────────────────────┘ │
│ │ │
│ │ valuation_id FK │
│ ▼ │
│ ┌──────────────┐ │
│ │ valuations │ │
│ └──────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
│
│ FK relationships
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ REFERENCE/LOOKUP LAYER │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ banks │ │ locations │ │ rates │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │document_types│ │lookup_values │ │bank_configs │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
│
│ FK relationships
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ USER & SECURITY LAYER │
│ │
│ ┌──────────────┐ M:M ┌──────────────┐ │
│ │ users │◄────────│ user_roles │ │
│ └──────────────┘ └──────┬───────┘ │
│ │ │ │
│ │ 1:M │ M:1 │
│ │ │ │
│ ┌──────▼───────┐ ┌──────▼───────┐ │
│ │user_sessions │ │ roles │ │
│ └──────────────┘ └──────┬───────┘ │
│ │ │
│ │ 1:1 │
│ │ │
│ ┌──────▼───────┐ │
│ │ permissions │ │
│ │ (JSON) │ │
│ └──────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
│
│ FK relationships
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ AUDIT & LOGGING LAYER │
│ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │activity_logs │ │ audit_logs │ │
│ └──────────────┘ └──────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
```
---
## Core Domain Tables
### 1. valuations
**Primary table for valuation records**
```sql
CREATE TABLE valuations (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Valuation Identification
valuation_number VARCHAR(50) UNIQUE NOT NULL, -- Format: VAL-YYYY-MMDD-XXXX
-- Foreign Keys
bank_id UUID NOT NULL REFERENCES banks(id),
property_type VARCHAR(50) NOT NULL, -- plot, independent_house, apartment
-- Workflow
status VARCHAR(50) NOT NULL DEFAULT 'draft', -- draft, survey, submitted, review, approval, approved, rejected
priority VARCHAR(20) NOT NULL DEFAULT 'medium', -- high, medium, low
-- Assignments
created_by UUID NOT NULL REFERENCES users(id),
assigned_surveyor UUID REFERENCES users(id),
assigned_keyin UUID REFERENCES users(id),
assigned_reviewer UUID REFERENCES users(id),
assigned_approver UUID REFERENCES users(id),
-- Purpose & Type
valuation_purpose VARCHAR(50), -- sale, mortgage, lease, legal
valuation_type VARCHAR(50), -- residential, commercial, agricultural
-- SLA & Timestamps
target_completion_date DATE,
sla_days INTEGER DEFAULT 5,
-- Workflow Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
submitted_at TIMESTAMP WITH TIME ZONE,
review_started_at TIMESTAMP WITH TIME ZONE,
review_completed_at TIMESTAMP WITH TIME ZONE,
approval_started_at TIMESTAMP WITH TIME ZONE,
approved_at TIMESTAMP WITH TIME ZONE,
rejected_at TIMESTAMP WITH TIME ZONE,
-- Soft Delete
deleted_at TIMESTAMP WITH TIME ZONE,
-- Metadata
metadata JSONB, -- For bank-specific custom fields
-- Constraints
CONSTRAINT valid_property_type CHECK (property_type IN ('plot', 'independent_house', 'apartment')),
CONSTRAINT valid_status CHECK (status IN ('draft', 'survey', 'submitted', 'review', 'approval', 'approved', 'rejected', 'on_hold')),
CONSTRAINT valid_priority CHECK (priority IN ('high', 'medium', 'low'))
);
-- Indexes
CREATE INDEX idx_valuations_number ON valuations(valuation_number);
CREATE INDEX idx_valuations_bank ON valuations(bank_id);
CREATE INDEX idx_valuations_status ON valuations(status);
CREATE INDEX idx_valuations_created_by ON valuations(created_by);
CREATE INDEX idx_valuations_assigned_surveyor ON valuations(assigned_surveyor);
CREATE INDEX idx_valuations_created_at ON valuations(created_at DESC);
CREATE INDEX idx_valuations_deleted_at ON valuations(deleted_at) WHERE deleted_at IS NULL;
```
---
### 2. properties
**Property location and identification details**
```sql
CREATE TABLE properties (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
valuation_id UUID UNIQUE NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
-- Property Identification
survey_number VARCHAR(100),
plot_number VARCHAR(100),
house_number VARCHAR(100),
building_name VARCHAR(200),
apartment_name VARCHAR(200), -- For apartments
flat_number VARCHAR(50), -- For apartments
floor INTEGER, -- For apartments
floor_level_from_ground INTEGER, -- For apartments
-- Address
address TEXT NOT NULL,
pincode VARCHAR(10) NOT NULL,
-- Location Hierarchy (Foreign Keys)
state_id UUID NOT NULL REFERENCES locations(id),
district_id UUID NOT NULL REFERENCES locations(id),
mandal_id UUID NOT NULL REFERENCES locations(id),
village_id UUID REFERENCES locations(id),
colony_name VARCHAR(200),
-- Coordinates
latitude DECIMAL(10, 8),
longitude DECIMAL(11, 8),
coordinate_accuracy INTEGER, -- in meters
coordinate_capture_method VARCHAR(50), -- gps, manual, address_search
-- Landmarks & Proximity
nearby_landmark TEXT,
railway_station_name VARCHAR(200),
railway_station_distance DECIMAL(10, 2),
railway_station_distance_unit VARCHAR(10) DEFAULT 'km',
bus_stand_name VARCHAR(200),
bus_stand_distance DECIMAL(10, 2),
bus_stand_distance_unit VARCHAR(10) DEFAULT 'km',
hospital_name VARCHAR(200),
hospital_distance DECIMAL(10, 2),
hospital_distance_unit VARCHAR(10) DEFAULT 'km',
institution_name VARCHAR(200),
institution_distance DECIMAL(10, 2),
institution_distance_unit VARCHAR(10) DEFAULT 'km',
branch_office_distance DECIMAL(10, 2),
branch_office_distance_unit VARCHAR(10) DEFAULT 'km',
city_center_distance DECIMAL(10, 2),
city_center_distance_unit VARCHAR(10) DEFAULT 'km',
nearest_major_road TEXT,
office_location_distance DECIMAL(10, 2),
office_location_distance_unit VARCHAR(10) DEFAULT 'km',
-- Locality Classification
locality_classification VARCHAR(50), -- posh, upper_middle_class, middle_class, lower_middle_class
locality_type VARCHAR(50), -- urban, rural, semi_urban
vicinity_description TEXT,
suburb_name VARCHAR(200),
civic_amenities_range VARCHAR(50), -- excellent, good, average, poor
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_locality_class CHECK (locality_classification IN ('posh', 'upper_middle_class', 'middle_class', 'lower_middle_class')),
CONSTRAINT valid_locality_type CHECK (locality_type IN ('urban', 'rural', 'semi_urban'))
);
-- Indexes
CREATE INDEX idx_properties_valuation ON properties(valuation_id);
CREATE INDEX idx_properties_state ON properties(state_id);
CREATE INDEX idx_properties_district ON properties(district_id);
CREATE INDEX idx_properties_mandal ON properties(mandal_id);
CREATE INDEX idx_properties_pincode ON properties(pincode);
```
---
### 3. applicants
**Applicant/borrower details**
```sql
CREATE TABLE applicants (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
valuation_id UUID UNIQUE NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
-- Applicant Details
applicant_name VARCHAR(200) NOT NULL,
co_applicant_name VARCHAR(200),
owner_name VARCHAR(200) NOT NULL,
-- Contact Information
contact_mobile VARCHAR(20) NOT NULL,
contact_email VARCHAR(200),
-- Representative
representative_name VARCHAR(200),
person_met_at_site VARCHAR(200),
-- Inspection
inspection_done_by UUID REFERENCES users(id),
inspection_date DATE,
inspection_time TIME,
weather_conditions VARCHAR(100),
-- Bank-specific fields (stored as JSON for flexibility)
bank_specific_data JSONB, -- PAN, Aadhaar, etc. as per bank config
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Indexes
CREATE INDEX idx_applicants_valuation ON applicants(valuation_id);
CREATE INDEX idx_applicants_mobile ON applicants(contact_mobile);
```
---
### 4. property_details
**Detailed site characteristics and specifications**
```sql
CREATE TABLE property_details (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
property_id UUID UNIQUE NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
-- Site Characteristics
shape_of_site VARCHAR(50), -- regular, irregular, rectangular, square, l_shaped, trapezoidal
level VARCHAR(50), -- plain, undulated, sloped
development_status VARCHAR(50), -- fully_developed, under_development, undeveloped
infrastructure_quality VARCHAR(50), -- excellent, good, average, poor
construction_activity VARCHAR(50), -- high, moderate, low, none
nature_of_site VARCHAR(50), -- dry, waterlogged, rocky, clayey
site_limits VARCHAR(50), -- municipal, panchayat, cantonment, other
-- Positives & Negatives
negatives_to_locality TEXT,
positives_to_locality TEXT,
-- Site Conditions
possibility_of_flooding BOOLEAN,
town_planning_approved BOOLEAN,
corner_or_intermittent VARCHAR(20), -- corner, intermittent, end
water_potentiality BOOLEAN,
underground_sewerage BOOLEAN,
power_supply_available BOOLEAN,
-- Accessibility
has_road_access BOOLEAN NOT NULL,
is_landlocked BOOLEAN NOT NULL,
landlocked_justification TEXT,
landlocked_approved_by UUID REFERENCES users(id),
landlocked_approved_at TIMESTAMP WITH TIME ZONE,
road_width DECIMAL(10, 2),
road_width_unit VARCHAR(10) DEFAULT 'feet',
road_type VARCHAR(50), -- paved, unpaved, cement, mud, gravel
-- Transport
transport_modes JSONB, -- Array: ['bus', 'auto', 'taxi', 'metro', 'train']
-- Building Details (for Independent House & Apartment)
single_or_multiple_buildings VARCHAR(20), -- single, multiple
number_of_buildings INTEGER,
building_details JSONB, -- Array of building objects with floors, heights, types
-- Apartment Specific
total_flats_in_complex INTEGER,
total_towers INTEGER,
tower_name VARCHAR(100),
flat_type VARCHAR(50), -- 1bhk, 2bhk, 3bhk, penthouse, studio
uds_value DECIMAL(10, 2), -- Undivided Share
uds_unit VARCHAR(20) DEFAULT 'sqyds',
carpet_area DECIMAL(10, 2),
carpet_area_unit VARCHAR(20) DEFAULT 'sqft',
parking_area DECIMAL(10, 2),
parking_area_unit VARCHAR(20) DEFAULT 'sqft',
number_of_parkings INTEGER,
common_area_share DECIMAL(5, 2), -- percentage
amenities JSONB, -- Array: ['gym', 'pool', 'club', 'park', 'security']
-- Compliance
ndma_compliance VARCHAR(20), -- yes, no, na
nbc_2005_compliance VARCHAR(20), -- yes, no, na
ht_lines_nearby BOOLEAN,
ht_line_distance DECIMAL(10, 2),
ht_line_distance_unit VARCHAR(10) DEFAULT 'meters',
buffer_areas BOOLEAN,
buffer_area_description TEXT,
buffer_area_distance DECIMAL(10, 2),
buffer_area_distance_unit VARCHAR(10) DEFAULT 'meters',
zone_usage VARCHAR(50), -- residential, commercial, industrial, mixed, agricultural
building_byelaws_adherence VARCHAR(20), -- yes, no, partial
approved_fsi DECIMAL(5, 2),
actual_fsi DECIMAL(5, 2),
plot_coverage DECIMAL(5, 2), -- percentage
permissible_plot_coverage DECIMAL(5, 2), -- percentage
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Indexes
CREATE INDEX idx_property_details_property ON property_details(property_id);
```
---
### 5. boundaries
**Boundary and dimension details**
```sql
CREATE TABLE boundaries (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
property_id UUID UNIQUE NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
-- Boundary Polygon (PostGIS Geometry)
boundary_polygon GEOMETRY(Polygon, 4326), -- PostGIS spatial data
boundary_geojson JSONB, -- GeoJSON snapshot for quick access
calculated_area_sqm DECIMAL(15, 2), -- Auto-calculated from polygon
-- Boundaries as per Documents
north_boundary_doc TEXT,
south_boundary_doc TEXT,
east_boundary_doc TEXT,
west_boundary_doc TEXT,
-- Boundaries as per Actuals
north_boundary_actual TEXT,
south_boundary_actual TEXT,
east_boundary_actual TEXT,
west_boundary_actual TEXT,
-- Dimensions as per Documents (in feet or meters)
north_dimension_doc DECIMAL(10, 2),
south_dimension_doc DECIMAL(10, 2),
east_dimension_doc DECIMAL(10, 2),
west_dimension_doc DECIMAL(10, 2),
dimension_unit_doc VARCHAR(10) DEFAULT 'feet',
-- Dimensions as per Actuals
north_dimension_actual DECIMAL(10, 2),
south_dimension_actual DECIMAL(10, 2),
east_dimension_actual DECIMAL(10, 2),
west_dimension_actual DECIMAL(10, 2),
dimension_unit_actual VARCHAR(10) DEFAULT 'feet',
-- Measurement Method
measurement_method VARCHAR(50), -- manual, gps, map_polygon, combination
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Indexes
CREATE INDEX idx_boundaries_property ON boundaries(property_id);
CREATE INDEX idx_boundaries_polygon ON boundaries USING GIST (boundary_polygon); -- Spatial index
```
---
### 6. valuation_data
**Valuation calculations and financial data**
```sql
CREATE TABLE valuation_data (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
valuation_id UUID UNIQUE NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
-- Valuation Method
valuation_method VARCHAR(50) NOT NULL, -- land_and_building, composite
-- LAND VALUATION
-- Land Unit
land_unit VARCHAR(20), -- acres_guntas, acres_cents, sqmtrs, sqyds, sqft
-- Land Areas
land_area_document DECIMAL(15, 4),
land_area_plan DECIMAL(15, 4),
land_area_nala DECIMAL(15, 4),
land_area_actual DECIMAL(15, 4),
land_area_adopted DECIMAL(15, 4) NOT NULL,
land_area_adoption_source VARCHAR(50), -- document, plan, nala, actual
-- Land Deductions
road_effected_area DECIMAL(15, 4) DEFAULT 0,
road_proposed_width DECIMAL(10, 2),
road_present_width DECIMAL(10, 2),
road_setback_length DECIMAL(10, 2),
nala_effected_area DECIMAL(15, 4) DEFAULT 0,
nala_width DECIMAL(10, 2),
nala_setback_distance DECIMAL(10, 2),
splay_area DECIMAL(15, 4) DEFAULT 0,
buffer_area DECIMAL(15, 4) DEFAULT 0,
buffer_type VARCHAR(100),
buffer_distance DECIMAL(10, 2),
net_land_area DECIMAL(15, 4) NOT NULL, -- Auto-calculated
-- Land Rates
land_rate_type VARCHAR(50) NOT NULL, -- guideline, market
land_rate_source VARCHAR(50), -- tgiic, apiic, agri_guideline, market
land_rate_per_unit DECIMAL(15, 2) NOT NULL,
land_rate_effective_date DATE,
land_rate_justification TEXT, -- Required if market rate
comparable_sales_reference TEXT,
adopted_land_rate DECIMAL(15, 2) NOT NULL,
-- Land Value (Calculated)
land_value DECIMAL(15, 2) NOT NULL, -- net_land_area * adopted_land_rate
-- BUILDING VALUATION (For Independent House & Apartment)
-- BUA Unit
bua_unit VARCHAR(20), -- sqmtrs, sqft
-- Built-up Areas
sbua_as_per_plan DECIMAL(15, 2), -- Super Built-up Area
sbua_as_per_actual DECIMAL(15, 2),
permissible_safe_sbua DECIMAL(15, 2),
adopted_sbua DECIMAL(15, 2),
sbua_adoption_source VARCHAR(50), -- plan, actual, permissible
-- For Apartments
total_bua_plan DECIMAL(15, 2),
total_bua_actual DECIMAL(15, 2),
adopted_total_bua DECIMAL(15, 2),
flat_uds DECIMAL(15, 2), -- Undivided Share
flat_carpet_area DECIMAL(15, 2),
flat_parking_area DECIMAL(15, 2),
-- Building Rate
building_rate_basis VARCHAR(50), -- pwd_schedule, market_rate, custom
building_rate_per_unit DECIMAL(15, 2),
building_age INTEGER, -- in years
depreciation_method VARCHAR(50), -- straight_line, written_down
depreciation_percentage DECIMAL(5, 2),
depreciated_building_rate DECIMAL(15, 2), -- Auto-calculated
-- Building Value (Calculated)
gross_building_value DECIMAL(15, 2), -- adopted_bua * building_rate
depreciation_amount DECIMAL(15, 2), -- gross * depreciation %
net_building_value DECIMAL(15, 2), -- gross - depreciation
-- COMPOSITE METHOD (For Flats)
composite_uds_value DECIMAL(15, 2),
composite_rate_per_unit DECIMAL(15, 2),
composite_rate_basis TEXT,
composite_value DECIMAL(15, 2), -- uds * rate
-- VALUATION ABSTRACT (Calculated)
total_property_value DECIMAL(15, 2) NOT NULL,
distress_sale_value DECIMAL(15, 2),
distress_sale_percentage DECIMAL(5, 2) DEFAULT 75,
forced_sale_value DECIMAL(15, 2),
forced_sale_percentage DECIMAL(5, 2) DEFAULT 55,
realizable_value DECIMAL(15, 2),
realizable_percentage DECIMAL(5, 2) DEFAULT 90,
-- Additional Remarks
valuation_remarks TEXT,
market_conditions TEXT,
comparable_sales_data TEXT,
recommendations TEXT,
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_valuation_method CHECK (valuation_method IN ('land_and_building', 'composite')),
CONSTRAINT valid_land_rate_type CHECK (land_rate_type IN ('guideline', 'market')),
CONSTRAINT positive_values CHECK (
land_value >= 0 AND
total_property_value >= 0 AND
net_land_area > 0
)
);
-- Indexes
CREATE INDEX idx_valuation_data_valuation ON valuation_data(valuation_id);
```
---
## Supporting Tables
### 7. documents
**Document management with version control**
```sql
CREATE TABLE documents (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Keys
valuation_id UUID NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
document_type_id UUID NOT NULL REFERENCES document_types(id),
parent_document_id UUID REFERENCES documents(id), -- For versioning
-- Document Metadata
document_name VARCHAR(500) NOT NULL,
document_description TEXT,
document_date DATE,
document_number VARCHAR(200),
-- File Information
file_name VARCHAR(500) NOT NULL,
file_path TEXT NOT NULL, -- S3 URL
file_size BIGINT NOT NULL, -- in bytes
file_type VARCHAR(100) NOT NULL, -- MIME type
thumbnail_path TEXT, -- For PDFs/images
page_count INTEGER, -- For PDFs
-- Version Control
version_number INTEGER DEFAULT 1,
version_notes TEXT,
is_current_version BOOLEAN DEFAULT true,
-- Status & Review
status VARCHAR(50) DEFAULT 'pending', -- pending, reviewed, approved, rejected
reviewed_by UUID REFERENCES users(id),
reviewed_at TIMESTAMP WITH TIME ZONE,
-- Upload Info
uploaded_by UUID NOT NULL REFERENCES users(id),
uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Annotations
annotations JSONB, -- For markup data
-- Soft Delete
deleted_at TIMESTAMP WITH TIME ZONE,
-- Constraints
CONSTRAINT valid_status CHECK (status IN ('pending', 'reviewed', 'approved', 'rejected'))
);
-- Indexes
CREATE INDEX idx_documents_valuation ON documents(valuation_id);
CREATE INDEX idx_documents_type ON documents(document_type_id);
CREATE INDEX idx_documents_version ON documents(parent_document_id, version_number);
CREATE INDEX idx_documents_uploaded_by ON documents(uploaded_by);
CREATE INDEX idx_documents_deleted ON documents(deleted_at) WHERE deleted_at IS NULL;
```
---
### 8. photos
**Photo gallery with geo-tagging and annotations**
```sql
CREATE TABLE photos (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
valuation_id UUID NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
-- Photo Metadata
category VARCHAR(50), -- front_view, side_view, interior, boundaries, road, neighborhood, defects, other
caption TEXT,
-- File Information
file_name VARCHAR(500) NOT NULL,
file_path TEXT NOT NULL, -- S3 URL
thumbnail_path TEXT NOT NULL, -- S3 URL for thumbnail
file_size BIGINT NOT NULL, -- in bytes
width INTEGER,
height INTEGER,
-- Geo-location
latitude DECIMAL(10, 8),
longitude DECIMAL(11, 8),
gps_accuracy INTEGER, -- in meters
-- EXIF Data
exif_data JSONB, -- Camera info, timestamp, etc.
-- Annotations
annotations JSONB, -- SVG data for markup
-- Photo Management
is_primary BOOLEAN DEFAULT false,
display_order INTEGER DEFAULT 0,
-- Upload Info
captured_at TIMESTAMP WITH TIME ZONE,
uploaded_by UUID NOT NULL REFERENCES users(id),
uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Soft Delete
deleted_at TIMESTAMP WITH TIME ZONE,
status VARCHAR(20) DEFAULT 'active', -- active, deleted
-- Constraints
CONSTRAINT valid_category CHECK (category IN ('front_view', 'side_view', 'interior', 'boundaries', 'road', 'neighborhood', 'defects', 'other'))
);
-- Indexes
CREATE INDEX idx_photos_valuation ON photos(valuation_id);
CREATE INDEX idx_photos_category ON photos(category);
CREATE INDEX idx_photos_uploaded_by ON photos(uploaded_by);
CREATE INDEX idx_photos_deleted ON photos(deleted_at) WHERE deleted_at IS NULL;
```
---
### 9. comments
**Threaded comments and discussions**
```sql
CREATE TABLE comments (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Keys
valuation_id UUID NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
parent_comment_id UUID REFERENCES comments(id), -- For threading
-- Comment Content
comment_text TEXT NOT NULL,
comment_type VARCHAR(50) DEFAULT 'general', -- general, query, response, note
-- Targeting
target_field VARCHAR(200), -- Specific field being commented on
target_section VARCHAR(100), -- Section of the form
-- Priority (for queries)
priority VARCHAR(20), -- high, medium, low
-- Status (for queries)
status VARCHAR(50) DEFAULT 'open', -- open, resolved, closed
resolved_by UUID REFERENCES users(id),
resolved_at TIMESTAMP WITH TIME ZONE,
-- Attachments
attachments JSONB, -- Array of file references
-- Created By
created_by UUID NOT NULL REFERENCES users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Soft Delete
deleted_at TIMESTAMP WITH TIME ZONE,
-- Constraints
CONSTRAINT valid_comment_type CHECK (comment_type IN ('general', 'query', 'response', 'note', 'review_note')),
CONSTRAINT valid_status CHECK (status IN ('open', 'resolved', 'closed'))
);
-- Indexes
CREATE INDEX idx_comments_valuation ON comments(valuation_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);
CREATE INDEX idx_comments_created_by ON comments(created_by);
CREATE INDEX idx_comments_deleted ON comments(deleted_at) WHERE deleted_at IS NULL;
```
---
## Reference/Lookup Tables
### 10. banks
**Bank master data**
```sql
CREATE TABLE banks (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Bank Information
bank_name VARCHAR(200) UNIQUE NOT NULL,
bank_code VARCHAR(50) UNIQUE NOT NULL,
logo_url TEXT,
-- SLA & Workflow
sla_days INTEGER DEFAULT 5,
multi_level_approval BOOLEAN DEFAULT false,
number_of_approval_levels INTEGER DEFAULT 1,
-- Contact Information
contact_person VARCHAR(200),
contact_email VARCHAR(200),
contact_mobile VARCHAR(20),
address TEXT,
-- Report Template
report_template_url TEXT, -- S3 URL to custom template
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_approval_levels CHECK (number_of_approval_levels BETWEEN 1 AND 3),
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive'))
);
-- Indexes
CREATE INDEX idx_banks_code ON banks(bank_code);
CREATE INDEX idx_banks_status ON banks(status) WHERE status = 'active';
```
---
### 11. bank_configs
**Bank-specific configurations (fields, validation, workflow)**
```sql
CREATE TABLE bank_configs (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
bank_id UUID NOT NULL REFERENCES banks(id) ON DELETE CASCADE,
-- Config Type
config_type VARCHAR(50) NOT NULL, -- field_config, validation_rules, workflow_config, document_checklist, valuation_config
-- Config Data (JSON)
config_data JSONB NOT NULL,
-- Versioning
version INTEGER DEFAULT 1,
is_active BOOLEAN DEFAULT true,
-- Timestamps
created_by UUID NOT NULL REFERENCES users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_config_type CHECK (config_type IN ('field_config', 'validation_rules', 'workflow_config', 'document_checklist', 'valuation_config')),
CONSTRAINT unique_active_config UNIQUE (bank_id, config_type, is_active) WHERE is_active = true
);
-- Indexes
CREATE INDEX idx_bank_configs_bank ON bank_configs(bank_id);
CREATE INDEX idx_bank_configs_type ON bank_configs(config_type);
```
---
### 12. locations
**Hierarchical location data (State → District → Mandal → Village)**
```sql
CREATE TABLE locations (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Hierarchy
location_type VARCHAR(20) NOT NULL, -- state, district, mandal, village
parent_id UUID REFERENCES locations(id), -- For hierarchy
-- Location Details
location_name VARCHAR(200) NOT NULL,
location_code VARCHAR(50),
-- Additional Info (for Villages)
pincodes TEXT[], -- Array of pincodes for villages
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_location_type CHECK (location_type IN ('state', 'district', 'mandal', 'village')),
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive'))
);
-- Indexes
CREATE INDEX idx_locations_type ON locations(location_type);
CREATE INDEX idx_locations_parent ON locations(parent_id);
CREATE INDEX idx_locations_name ON locations(location_name);
CREATE INDEX idx_locations_code ON locations(location_code);
```
---
### 13. rates
**Guideline and market rates by location**
```sql
CREATE TABLE rates (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
location_id UUID NOT NULL REFERENCES locations(id),
-- Rate Information
rate_type VARCHAR(50) NOT NULL, -- tgiic, apiic, agri_guideline, market
rate_per_unit DECIMAL(15, 2) NOT NULL,
unit VARCHAR(20) NOT NULL, -- sqft, sqyd, sqmtr, acre
-- Effective Dates
effective_from DATE NOT NULL,
effective_to DATE,
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Metadata
updated_by UUID NOT NULL REFERENCES users(id),
remarks TEXT,
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_rate_type CHECK (rate_type IN ('tgiic', 'apiic', 'agri_guideline', 'market')),
CONSTRAINT valid_unit CHECK (unit IN ('sqft', 'sqyd', 'sqmtr', 'acre')),
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive')),
CONSTRAINT valid_date_range CHECK (effective_to IS NULL OR effective_to >= effective_from)
);
-- Indexes
CREATE INDEX idx_rates_location ON rates(location_id);
CREATE INDEX idx_rates_type ON rates(rate_type);
CREATE INDEX idx_rates_effective ON rates(effective_from, effective_to);
CREATE INDEX idx_rates_status ON rates(status) WHERE status = 'active';
```
---
### 14. document_types
**Document type master data**
```sql
CREATE TABLE document_types (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Document Type Details
document_type_name VARCHAR(200) UNIQUE NOT NULL,
document_category VARCHAR(50) NOT NULL, -- legal, financial, technical, other
-- Requirements
is_mandatory BOOLEAN DEFAULT false,
bank_specific JSONB, -- Array of bank IDs where this is required
property_type_specific JSONB, -- Array of property types
-- File Restrictions
allowed_file_formats TEXT[], -- ['pdf', 'jpg', 'png', 'doc', 'docx']
max_file_size_mb INTEGER DEFAULT 10,
-- Display
display_order INTEGER DEFAULT 0,
description TEXT,
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_category CHECK (document_category IN ('legal', 'financial', 'technical', 'other')),
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive'))
);
-- Indexes
CREATE INDEX idx_document_types_category ON document_types(document_category);
CREATE INDEX idx_document_types_status ON document_types(status) WHERE status = 'active';
```
---
### 15. lookup_values
**Generic lookup/dropdown values**
```sql
CREATE TABLE lookup_values (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Lookup Category
category VARCHAR(100) NOT NULL, -- shape_of_site, level, development_status, etc.
-- Value
value VARCHAR(200) NOT NULL,
label VARCHAR(200) NOT NULL, -- Display label
-- Hierarchy (for nested lookups)
parent_id UUID REFERENCES lookup_values(id),
-- Display
display_order INTEGER DEFAULT 0,
description TEXT,
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT unique_category_value UNIQUE (category, value),
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive'))
);
-- Indexes
CREATE INDEX idx_lookup_values_category ON lookup_values(category);
CREATE INDEX idx_lookup_values_parent ON lookup_values(parent_id);
CREATE INDEX idx_lookup_values_status ON lookup_values(status) WHERE status = 'active';
```
---
## User & Security Tables
### 16. users
**User accounts**
```sql
CREATE TABLE users (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- User Identification
employee_id VARCHAR(100) UNIQUE NOT NULL,
email VARCHAR(200) UNIQUE NOT NULL,
mobile VARCHAR(20) UNIQUE NOT NULL,
-- Credentials
password_hash TEXT NOT NULL,
password_salt TEXT NOT NULL,
password_changed_at TIMESTAMP WITH TIME ZONE,
password_expires_at TIMESTAMP WITH TIME ZONE,
-- Personal Information
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
photo_url TEXT,
-- Organizational
team_id UUID REFERENCES teams(id),
reports_to UUID REFERENCES users(id),
-- Account Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive, locked
account_expires_at DATE,
-- Security
two_factor_enabled BOOLEAN DEFAULT false,
two_factor_secret TEXT,
failed_login_attempts INTEGER DEFAULT 0,
locked_until TIMESTAMP WITH TIME ZONE,
-- Preferences
preferences JSONB, -- Language, timezone, theme, etc.
-- Access Restrictions
allowed_banks JSONB, -- Array of bank IDs (empty = all banks)
allowed_locations JSONB, -- Array of location IDs (empty = all locations)
ip_whitelist TEXT[],
working_hours_from TIME,
working_hours_to TIME,
-- Login Tracking
last_login_at TIMESTAMP WITH TIME ZONE,
last_login_ip VARCHAR(50),
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE,
-- Constraints
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive', 'locked'))
);
-- Indexes
CREATE INDEX idx_users_employee_id ON users(employee_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_mobile ON users(mobile);
CREATE INDEX idx_users_team ON users(team_id);
CREATE INDEX idx_users_status ON users(status) WHERE status = 'active';
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;
```
---
### 17. roles
**User roles with permissions**
```sql
CREATE TABLE roles (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Role Details
role_name VARCHAR(100) UNIQUE NOT NULL,
role_code VARCHAR(50) UNIQUE NOT NULL,
role_description TEXT,
-- Permissions (JSON)
permissions JSONB NOT NULL,
/* Example structure:
{
"valuations": {"view": true, "create": true, "edit": true, "delete": false},
"documents": {"view": true, "create": true, "edit": true, "delete": true},
"approvals": {"approve": true, "reject": true},
"reports": {"view": true, "export": true},
"admin": {"view": false, "edit": false}
}
*/
-- Role Level
role_level INTEGER DEFAULT 0, -- For hierarchy if needed
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive'))
);
-- Indexes
CREATE INDEX idx_roles_code ON roles(role_code);
CREATE INDEX idx_roles_status ON roles(status) WHERE status = 'active';
```
---
### 18. user_roles
**Many-to-many relationship between users and roles**
```sql
CREATE TABLE user_roles (
-- Composite Primary Key
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
-- Primary Role Flag
is_primary BOOLEAN DEFAULT false,
-- Timestamps
assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
assigned_by UUID REFERENCES users(id),
-- Constraints
PRIMARY KEY (user_id, role_id)
);
-- Indexes
CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);
```
---
### 19. teams
**Team/department organization**
```sql
CREATE TABLE teams (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Team Details
team_name VARCHAR(200) UNIQUE NOT NULL,
team_code VARCHAR(50) UNIQUE NOT NULL,
team_description TEXT,
-- Team Lead
team_lead_id UUID REFERENCES users(id),
-- Coverage Areas
coverage_states JSONB, -- Array of state IDs
coverage_districts JSONB, -- Array of district IDs
-- Assignment Rules
auto_assign_by_location BOOLEAN DEFAULT false,
auto_assign_by_workload BOOLEAN DEFAULT false,
max_active_assignments_per_member INTEGER DEFAULT 10,
-- Status
status VARCHAR(20) DEFAULT 'active', -- active, inactive
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive'))
);
-- Indexes
CREATE INDEX idx_teams_code ON teams(team_code);
CREATE INDEX idx_teams_lead ON teams(team_lead_id);
CREATE INDEX idx_teams_status ON teams(status) WHERE status = 'active';
```
---
### 20. user_sessions
**Active user sessions for JWT token management**
```sql
CREATE TABLE user_sessions (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Key
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- Session Details
refresh_token TEXT UNIQUE NOT NULL,
access_token_jti TEXT UNIQUE NOT NULL, -- JWT ID
-- Device & Location
device_type VARCHAR(50), -- desktop, mobile, tablet
device_name VARCHAR(200),
browser VARCHAR(100),
operating_system VARCHAR(100),
ip_address VARCHAR(50),
location VARCHAR(200), -- City, Country
-- Session Management
is_active BOOLEAN DEFAULT true,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
last_activity_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
revoked_at TIMESTAMP WITH TIME ZONE,
-- Constraints
CONSTRAINT valid_expiry CHECK (expires_at > created_at)
);
-- Indexes
CREATE INDEX idx_user_sessions_user ON user_sessions(user_id);
CREATE INDEX idx_user_sessions_refresh_token ON user_sessions(refresh_token);
CREATE INDEX idx_user_sessions_active ON user_sessions(is_active) WHERE is_active = true;
CREATE INDEX idx_user_sessions_expires ON user_sessions(expires_at);
```
---
## Audit & Logging Tables
### 21. activity_logs
**User activity tracking**
```sql
CREATE TABLE activity_logs (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Keys
user_id UUID NOT NULL REFERENCES users(id),
valuation_id UUID REFERENCES valuations(id),
-- Activity Details
action_type VARCHAR(100) NOT NULL, -- create, update, delete, approve, reject, etc.
action_description TEXT NOT NULL,
entity_type VARCHAR(100), -- valuation, document, photo, comment, etc.
entity_id UUID,
-- Request Details
ip_address VARCHAR(50),
user_agent TEXT,
request_method VARCHAR(10), -- GET, POST, PUT, DELETE
request_url TEXT,
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Indexes
CREATE INDEX idx_activity_logs_user ON activity_logs(user_id);
CREATE INDEX idx_activity_logs_valuation ON activity_logs(valuation_id);
CREATE INDEX idx_activity_logs_created ON activity_logs(created_at DESC);
CREATE INDEX idx_activity_logs_action ON activity_logs(action_type);
```
---
### 22. audit_logs
**Data change tracking with old/new values**
```sql
CREATE TABLE audit_logs (
-- Primary Key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign Keys
user_id UUID NOT NULL REFERENCES users(id),
valuation_id UUID REFERENCES valuations(id),
-- Change Details
table_name VARCHAR(100) NOT NULL,
record_id UUID NOT NULL,
operation VARCHAR(20) NOT NULL, -- INSERT, UPDATE, DELETE
-- Data Changes
old_value JSONB, -- Previous values
new_value JSONB, -- New values
changed_fields TEXT[], -- Array of changed field names
-- Request Context
ip_address VARCHAR(50),
user_agent TEXT,
-- Timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Constraints
CONSTRAINT valid_operation CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE'))
);
-- Indexes
CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_valuation ON audit_logs(valuation_id);
CREATE INDEX idx_audit_logs_table ON audit_logs(table_name, record_id);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);
```
---
## Relationships Summary
### Core Domain Relationships
```
valuations (1) ←→ (1) properties
valuations (1) ←→ (1) applicants
valuations (1) ←→ (1) valuation_data
properties (1) ←→ (1) property_details
properties (1) ←→ (1) boundaries
valuations (1) ←→ (M) documents
valuations (1) ←→ (M) photos
valuations (1) ←→ (M) comments
```
### Reference Relationships
```
valuations (M) ←→ (1) banks
properties (M) ←→ (1) locations [state]
properties (M) ←→ (1) locations [district]
properties (M) ←→ (1) locations [mandal]
properties (M) ←→ (1) locations [village]
documents (M) ←→ (1) document_types
rates (M) ←→ (1) locations
bank_configs (M) ←→ (1) banks
```
### User Relationships
```
users (M) ←→ (M) roles [via user_roles]
users (M) ←→ (1) teams
users (1) ←→ (M) user_sessions
users (1) ←→ (M) valuations [as creator, surveyor, reviewer, etc.]
```
### Audit Relationships
```
users (1) ←→ (M) activity_logs
users (1) ←→ (M) audit_logs
valuations (1) ←→ (M) activity_logs
valuations (1) ←→ (M) audit_logs
```
---
## Indexes Strategy
### Primary Indexes (Automatically created)
- Primary Keys (UUID): All tables
- Unique Constraints: valuation_number, email, employee_id, etc.
### Foreign Key Indexes
- All foreign key columns for join performance
- Example: `valuation_id`, `bank_id`, `user_id`, etc.
### Query Optimization Indexes
- **Status-based queries**: `status` column with partial index (WHERE status = 'active')
- **Date range queries**: `created_at DESC`, `approved_at`, `effective_from`
- **Text search**: Consider adding GIN indexes for JSONB columns if full-text search needed
- **Soft delete**: Partial index on `deleted_at IS NULL`
### Spatial Indexes (PostGIS)
- `GIST` index on `boundary_polygon` in `boundaries` table for spatial queries
### JSONB Indexes
- GIN indexes on JSONB columns for fast JSON queries
- Example: `CREATE INDEX idx_metadata_gin ON valuations USING GIN (metadata);`
---
## SQL Schema Scripts
### Extension Installation
```sql
-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- Enable PostGIS for spatial data
CREATE EXTENSION IF NOT EXISTS "postgis";
```
### Trigger for Updated_at Timestamp
```sql
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Apply trigger to all tables with updated_at column
-- Example for valuations table:
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON valuations
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Repeat for other tables: properties, applicants, property_details, etc.
```
### Audit Trigger (Auto-logging changes)
```sql
-- Function to log data changes
CREATE OR REPLACE FUNCTION log_data_changes()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO audit_logs (
user_id,
valuation_id,
table_name,
record_id,
operation,
old_value,
new_value,
changed_fields
)
VALUES (
current_setting('app.current_user_id')::UUID,
COALESCE(NEW.valuation_id, OLD.valuation_id),
TG_TABLE_NAME,
OLD.id,
'UPDATE',
to_jsonb(OLD),
to_jsonb(NEW),
(SELECT array_agg(key) FROM jsonb_each(to_jsonb(NEW)) WHERE to_jsonb(OLD) ->> key IS DISTINCT FROM to_jsonb(NEW) ->> key)
);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit_logs (
user_id,
valuation_id,
table_name,
record_id,
operation,
new_value
)
VALUES (
current_setting('app.current_user_id')::UUID,
NEW.valuation_id,
TG_TABLE_NAME,
NEW.id,
'INSERT',
to_jsonb(NEW)
);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO audit_logs (
user_id,
valuation_id,
table_name,
record_id,
operation,
old_value
)
VALUES (
current_setting('app.current_user_id')::UUID,
OLD.valuation_id,
TG_TABLE_NAME,
OLD.id,
'DELETE',
to_jsonb(OLD)
);
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Apply to critical tables (example for valuations):
CREATE TRIGGER audit_valuations
AFTER INSERT OR UPDATE OR DELETE ON valuations
FOR EACH ROW
EXECUTE FUNCTION log_data_changes();
```
---
## Database Size Estimation
### Expected Data Growth (Year 1)
- **Valuations**: ~10,000 records × 5 KB = 50 MB
- **Properties**: ~10,000 records × 3 KB = 30 MB
- **Documents**: ~50,000 files (metadata only) × 2 KB = 100 MB
- **Photos**: ~150,000 photos (metadata only) × 1 KB = 150 MB
- **Audit Logs**: ~500,000 records × 2 KB = 1 GB
- **Other tables**: ~200 MB
**Total Database Size (Year 1)**: ~1.5-2 GB (excluding file storage)
**File Storage (S3)**:
- Documents: ~50,000 × 2 MB = 100 GB
- Photos: ~150,000 × 1 MB = 150 GB
- **Total File Storage**: ~250 GB
---
## Performance Considerations
### Query Optimization
1. Use partial indexes for common filters (status, deleted_at)
2. Index foreign keys for fast joins
3. Use EXPLAIN ANALYZE to optimize slow queries
4. Consider materialized views for complex reports
### Connection Pooling
- Use PgBouncer or similar for connection pooling
- Recommended pool size: 20-50 connections
### Backup Strategy
- Daily full backups
- Continuous WAL archiving for point-in-time recovery
- Test restore procedures monthly
### Scalability
- **Vertical**: Start with 4 CPU, 8 GB RAM, 100 GB SSD
- **Horizontal**: Read replicas for reporting queries
- **Partitioning**: Consider partitioning audit_logs by date if grows large
---
**END OF DATABASE ERD**
**Next Steps:**
1. Review table structures and relationships
2. Generate complete SQL schema scripts (separate file)
3. Create database migrations using Prisma or Drizzle
4. Set up seed data for development
5. Implement audit triggers
6. Configure backup and monitoring
---
**Version**: 1.0
**Date**: 2025-12-21
**Status**: ✅ Ready for Implementation