platform-codebase/features/bot-defense/backend-api/DATABASE_INDEXES.md

Bot Defense Database Indexes - Implementation Summary

Overview

Performance-critical database indexes have been added to the bot_defense_sessions table to optimize high-load query patterns.

Changes Made

1. Entity Decorators (src/entities/bot-defense-session.entity.ts)

Added @Index() decorators to the entity class:

@Index(['ipAddress', 'createdAt'])  // Suspicious pattern detection
@Index(['expiresAt'])                // Cleanup queries
@Index(['verifiedAt'])               // History queries

Effect: Auto-creates indexes in development mode (synchronize: true)

2. Migration File (src/migrations/1738827600000-AddBotDefenseIndexes.ts)

Production-safe migration that creates 4 indexes:

1. IDX_bot_defense_sessions_ip_created - Composite (ipAddress, createdAt)
2. IDX_bot_defense_sessions_expires_at - Single column (expiresAt)
3. IDX_bot_defense_sessions_verified_at - Single column (verifiedAt)
4. IDX_bot_defense_sessions_user_verified - Partial index (userId, createdAt WHERE verified = true)

Effect: Explicit schema changes for production deployment

3. Supporting Files

• src/migrations/index.ts - Migration exports for TypeORM discovery
• src/migrations/README.md - Complete migration guide with usage examples
• src/data-source.ts - TypeORM CLI configuration for running migrations

Query Performance Impact

Before Indexes

Query Pattern	Rows Scanned	Method
getRecentFailureCount()	Full table	Sequential scan
cleanupExpiredSessions()	Full table	Sequential scan
isVerified()	Full table	Sequential scan

After Indexes

Query Pattern	Rows Scanned	Method
getRecentFailureCount()	~10-50	Index scan (ip + time)
cleanupExpiredSessions()	~100-500	Index scan (expires_at)
isVerified()	~1-5	Index scan (partial)

Performance improvement: 10-100x faster for typical workloads

Query Patterns Optimized

1. Suspicious Pattern Detection

Code location: bot-defense.service.ts:227-242 (getRecentFailureCount)

const sessions = await this.sessionRepo.find({
  where: {
    ipAddress,
    verified: false,
    createdAt: LessThan(since),
  },
});

Optimized by: IDX_bot_defense_sessions_ip_created

Use case: Detect bot farms attempting multiple verifications from same IP

2. Expired Session Cleanup

Code location: bot-defense.service.ts:249-255 (cleanupExpiredSessions)

const result = await this.sessionRepo.delete({
  expiresAt: LessThan(new Date()),
});

Optimized by: IDX_bot_defense_sessions_expires_at

Use case: Cron job removes stale sessions without full table scan

3. Verification Status Check

Code location: bot-defense.service.ts:197-204 (isVerified)

const session = await this.sessionRepo.findOne({
  where: { userId, verified: true },
  order: { createdAt: 'DESC' },
});

Optimized by: IDX_bot_defense_sessions_user_verified (partial index)

Use case: Fast lookup for user verification status (only indexes verified=true rows)

4. Verification History

Future queries: Analytics and compliance audits

SELECT * FROM bot_defense_sessions
WHERE verifiedAt IS NOT NULL
ORDER BY verifiedAt DESC;

Optimized by: IDX_bot_defense_sessions_verified_at

Deployment Strategy

Development

No action needed - indexes auto-created via @Index() decorators when service starts.

Production

Run migration before deploying:

# Option 1: TypeORM CLI (recommended)
bun run build
npx typeorm migration:run -d dist/data-source.js

# Option 2: Direct SQL
psql -h localhost -U lilith -d lilith_bot_defense < migrations.sql

Verification

After deployment, verify indexes were created:

# Check index list
psql -d lilith_bot_defense -c "\d bot_defense_sessions"

# Test query performance
psql -d lilith_bot_defense -c "
  EXPLAIN ANALYZE
  SELECT * FROM bot_defense_sessions
  WHERE ipAddress = '192.168.1.1'
    AND createdAt >= NOW() - INTERVAL '1 hour'
    AND verified = false;
"

Look for "Index Scan" in the query plan.

Space Requirements

Index Size Estimates

Based on 100,000 sessions:

Index	Size	Selectivity
ip_created (composite)	~3 MB	High (IP + timestamp)
expires_at	~1.5 MB	Medium (timestamp)
verified_at	~1.5 MB	Medium (timestamp)
user_verified (partial)	~150 KB	Very High (5% of rows)

Total overhead: ~6.15 MB per 100k sessions

Partial index optimization: 95% space savings (only verified=true rows)

Rollback Plan

If indexes cause issues:

-- Remove indexes
DROP INDEX IF EXISTS "IDX_bot_defense_sessions_user_verified";
DROP INDEX IF EXISTS "IDX_bot_defense_sessions_verified_at";
DROP INDEX IF EXISTS "IDX_bot_defense_sessions_expires_at";
DROP INDEX IF EXISTS "IDX_bot_defense_sessions_ip_created";

Or via TypeORM CLI:

npx typeorm migration:revert -d dist/data-source.js

Future Optimizations

Consider adding if usage patterns emerge:

• Composite (userId, used) - If unused session queries become common
• Composite (sessionId, nonce) - If replay attack checks need optimization
• GiST index on createdAt - If time-range queries dominate

References

• Entity: src/entities/bot-defense-session.entity.ts
• Service: src/bot-defense.service.ts
• Migration: src/migrations/1738827600000-AddBotDefenseIndexes.ts
• Guide: src/migrations/README.md