Emanuel Almeida
b4ba42cbf1
Security & Data Integrity: - Centralized transaction helper with deadlock retry (exponential backoff) - SafeQueryBuilder for safe parameterized queries - Zod-based input validation middleware - Audit logging to Outline's events table Performance: - Cursor-based pagination for large datasets - Pool monitoring with configurable alerts - Database index migrations for optimal query performance Changes: - Refactored bulk-operations, desk-sync, export-import to use centralized transaction helper - Added 7 new utility modules (audit, monitoring, pagination, query-builder, transaction, validation) - Created migrations/001_indexes.sql with 40+ recommended indexes Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
1.7 KiB
1.7 KiB
Database Migrations
This directory contains optional database migrations for improving MCP Outline PostgreSQL performance.
Index Migration (001_indexes.sql)
This migration creates recommended indexes to improve query performance.
Before Running
- Backup your database - Always backup before applying migrations
- Review the indexes - Some may already exist in your Outline installation
- Test in staging - Apply to a staging environment first
Running the Migration
# Connect to your Outline database
psql -d outline -f migrations/001_indexes.sql
# Or via DATABASE_URL
psql $DATABASE_URL -f migrations/001_indexes.sql
Index Categories
| Category | Tables | Impact |
|---|---|---|
| Documents | documents | 10-100x faster searches and listings |
| Memberships | collection_users, group_users, user_permissions | 10x faster permission checks |
| Events | events | 5-20x faster audit log queries |
| User Interaction | stars, pins, views | 5x faster bookmark/view queries |
| Full-text Search | documents (GIN) | Dramatically faster text search |
Checking Index Usage
After applying, verify indexes are being used:
-- Check if indexes exist
SELECT indexname, tablename
FROM pg_indexes
WHERE indexname LIKE 'idx_%'
ORDER BY tablename, indexname;
-- Check index usage statistics
SELECT
schemaname,
tablename,
indexname,
idx_scan as times_used,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE 'idx_%'
ORDER BY idx_scan DESC;
Removing Indexes
If you need to remove specific indexes:
DROP INDEX IF EXISTS idx_documents_search;
-- etc.
MCP Outline PostgreSQL | Descomplicar® | 2026