Database Setup

Dharini uses PostgreSQL with the PostGIS extension for geospatial capabilities.

Local Development

For local development, PostgreSQL runs in Docker via docker-compose.yml.

Start PostgreSQL

docker compose --profile development up -d postgres

Connect to Local Database

docker exec -it postgres16-postgis psql -U postgres -d dharini

Useful Commands

-- List all tables
\dt

-- Describe table structure
\d table_name

-- List all databases
\l

-- Connect to different database
\c database_name

-- Quit
\q

Production with AWS RDS

Create RDS Instance

See Production Setup for detailed RDS creation steps.

Connect to RDS

RDS requires SSL connections in production:

PGSSLMODE=require psql \
  -h your-database-instance.xxxxxxxxx.region.rds.amazonaws.com \
  -U postgres \
  -d postgres

Install Required Extensions

After creating the RDS instance, install required PostgreSQL extensions:

-- PostGIS for spatial data
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Additional extensions (optional)
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- For similarity searches
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- For specialized indexes

Verify extensions:

SELECT * FROM pg_extension;

Database Migrations

Dharini uses TypeORM for database migrations.

Run Migrations

On first deployment or after pulling new code:

docker exec -it backend npm run migration:run

Generate New Migration

After modifying entity files:

docker exec -it backend npm run migration:generate -- -n MigrationName

Revert Last Migration

docker exec -it backend npm run migration:revert

View Migration Status

Check which migrations have been applied:

SELECT * FROM migrations;

Database Schema

Dharini’s main tables include:

• users - User accounts and authentication
• projects - Surveillance projects
• teams - Team organization
• roles - User roles and permissions
• sites - Surveillance sites with geospatial data
• visits - Scheduled and completed site visits
• samples - Sample collection records
• lab_tests - Laboratory test results
• diseases - Disease definitions
• surveillance_types - Types of surveillance activities
• regions - Geographical regions
• media - Uploaded files and images

Initial Data Setup

Create Super Admin User

A super admin user is automatically created during migrations:

• Email: sneha+superadmin@artpark.in
• Phone: 99988888665
• Designation: super-admin

You can modify this in the wipedata script or create additional users via the application.

Wipe and Reinitialize Database

Warning: This will delete all data!

The repository includes a script for wiping data and recreating the super admin:

# For Docker development setup
./jobs/db/wipedata.sh

# For RDS (requires modification of script)
# See script comments for RDS usage

Backup and Restore

Backup Database

Local (Docker)

docker exec -it postgres16-postgis pg_dump -U postgres dharini > backup.sql

RDS

PGSSLMODE=require pg_dump \
  -h your-database-instance.xxxxxxxxx.region.rds.amazonaws.com \
  -U postgres \
  -d postgres \
  > backup.sql

Restore Database

Local (Docker)

docker exec -i postgres16-postgis psql -U postgres dharini < backup.sql

RDS

PGSSLMODE=require psql \
  -h your-database-instance.xxxxxxxxx.region.rds.amazonaws.com \
  -U postgres \
  -d postgres \
  < backup.sql

Automated Backups (RDS)

RDS provides automated backups:

1. Configure retention period (default: 7 days)
2. Set backup window (time when backups occur)
3. Enable point-in-time recovery for granular restore

Access backups in RDS Console → Your instance → Maintenance & backups

Performance Optimization

Indexes

Ensure critical indexes are created:

-- Spatial index on site locations
CREATE INDEX idx_sites_location ON sites USING GIST (location);

-- User lookups
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users("contactNumber");

-- Visit queries
CREATE INDEX idx_visits_site ON visits("siteId");
CREATE INDEX idx_visits_date ON visits("visitDate");

-- Sample tracking
CREATE INDEX idx_samples_visit ON samples("visitId");
CREATE INDEX idx_samples_barcode ON samples("barcode");

Connection Pooling

TypeORM handles connection pooling automatically. Configuration in app.module.ts:

{
  type: 'postgres',
  // ... other config
  // Connection pool settings (TypeORM defaults)
  extra: {
    max: 10,  // Maximum pool size
    idleTimeoutMillis: 30000,
  }
}

Query Optimization

Monitor slow queries:

-- Enable slow query logging (RDS)
-- Set parameter group: log_min_duration_statement = 1000 (ms)

-- View slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Security

RDS Security

1. Network Isolation:

   • Place RDS in private subnet
   • Only allow access from EC2 security group

2. Encryption:

   • Enable encryption at rest
   • Force SSL connections

3. Credentials:

   • Use strong passwords
   • Rotate credentials regularly
   • Store in AWS Secrets Manager (optional)

Security Group Rules

RDS security group inbound rules:

Type	Port	Source	Description
PostgreSQL	5432	EC2 security group	Application access

Monitoring

RDS CloudWatch Metrics

Monitor key metrics:

• CPUUtilization - Should stay below 80%
• FreeableMemory - Watch for memory pressure
• DatabaseConnections - Monitor connection usage
• ReadLatency / WriteLatency - Query performance

Query Performance

Enable Performance Insights in RDS for detailed query analysis.

Connection Count

Check active connections:

SELECT count(*) FROM pg_stat_activity;

View connection details:

SELECT
  datname,
  usename,
  application_name,
  client_addr,
  state,
  query
FROM pg_stat_activity
WHERE datname = 'postgres';

Troubleshooting

Cannot Connect to RDS

1. Check security group - Ensure EC2 instance can reach RDS
2. Verify endpoint - Confirm RDS endpoint is correct
3. Test SSL connection:

   PGSSLMODE=require psql -h your-endpoint -U postgres -d postgres

SSL Connection Errors

If you see “no pg_hba.conf entry for host” errors:

# Always use SSL mode for RDS
PGSSLMODE=require psql -h your-rds-endpoint -U postgres -d postgres

The application automatically enables SSL when:

• NODE_ENV=production
• NODE_ENV=staging
• DB_HOST contains rds.amazonaws.com

Migration Failures

Check backend logs:

docker logs backend --tail 100

Common issues:

• PostGIS extension not installed
• Insufficient permissions
• Network connectivity to RDS

Database Performance Issues

1. Check connection count
2. Review slow query log
3. Analyze explain plans for slow queries:

   EXPLAIN ANALYZE SELECT ...;

4. Rebuild indexes if needed
5. Scale RDS instance if consistently high CPU/memory

Next Steps

• Troubleshooting - Common issues and solutions
• Production Setup - Complete deployment guide