High-Level Architecture

System Overview

Oktuple is built as a microservices architecture with two main services that work together to provide a comprehensive project management solution:

1. PMN (Project Management Node) - The primary API service
2. Syncer - The data synchronization service

Target Architecture

Microservices Design

Data Flow Architecture

1. Primary Operations: PMN handles all CRUD operations
2. Real-time Sync: Syncer captures changes via logical replication
3. Data Indexing: Data synchronized to other databases
4. WebSocket Notifications: Real-time updates to clients
5. Fallback Queuing: Redis-based message persistence

Multi-Database Architecture Pattern

Overview

Oktuple implements a polyglot persistence (multi-database) architecture pattern, where different types of data are stored in specialized databases optimized for specific use cases. This approach follows the principle of "use the right tool for the right job" rather than forcing all data into a single database system.

Database Specialization Strategy

1. PostgreSQL - Primary Transactional Database

Purpose: Main data persistence and ACID-compliant transactions

Why PostgreSQL:

• ACID Compliance: Ensures data consistency for critical business operations
• Relational Integrity: Maintains referential integrity across complex project hierarchies
• JSON Support: Native JSON/JSONB support for flexible schema evolution
• Logical Replication: Built-in change data capture for real-time synchronization
• Mature Ecosystem: Extensive tooling and community support

Data Types:

• User accounts and authentication data
• Project structures and hierarchies (ENode system)
• Issues, tasks, and work items
• Templates and workflows
• Add-ons configuration and metadata
• Audit logs and change history

2. Elasticsearch - Full-Text Search & Analytics Engine

Purpose: Advanced search capabilities and real-time analytics

Why Elasticsearch:

• Aggregations: Complex analytics and reporting capabilities
• Real-Time Indexing: Near real-time search index updates
• Scalability: Horizontal scaling for large datasets
• Query Flexibility: Rich query DSL for complex search requirements

Data Types:

• Searchable content from all project data
• Analytics aggregations and metrics
• Advanced filtering and faceted search
• Real-time dashboards and reporting

3. Typesense - Vector Search & Autocomplete Engine

Purpose: Fast autocomplete, similarity search, and vector-based operations

Why Typesense:

• Performance: Sub-millisecond search response times
• Vector Search: Native support for similarity and semantic search
• Autocomplete: Optimized for real-time search suggestions
• Simplicity: Easy to deploy and maintain
• Memory Efficiency: Lower resource requirements than Elasticsearch

Data Types:

• Search suggestions and autocomplete
• Similar content recommendations
• Vector embeddings for AI-powered features
• Fast filtering and faceted search
• Real-time search as you type

4. TimescaleDB - Time-Series Data Storage

Purpose: Time-based analytics and historical data tracking

Why TimescaleDB:

• Time-Series Optimization: Built specifically for time-based data
• Compression: Automatic data compression for historical data
• Continuous Aggregates: Pre-computed time-based aggregations
• PostgreSQL Compatibility: Seamless integration with existing PostgreSQL tools
• Retention Policies: Automatic data lifecycle management

Data Types:

• Project progress metrics over time
• User activity patterns and analytics
• Performance metrics and system monitoring
• Historical reporting and trend analysis
• Time-based notifications and reminders

Architectural Benefits

1. Performance Optimization

Specialized Indexing:

• Each database uses indexing strategies optimized for its data type
• PostgreSQL: B-tree indexes for relational queries
• Elasticsearch: Inverted indexes for full-text search
• Typesense: Optimized indexes for vector and autocomplete operations
• TimescaleDB: Time-partitioned indexes for temporal queries

Query Performance:

• Search queries execute in milliseconds on specialized engines
• Complex analytics run on purpose-built aggregation engines
• Time-series queries leverage time-optimized data structures

2. Scalability & Resource Efficiency

Independent Scaling:

• Each database can be scaled independently based on usage patterns
• Search engines can be scaled horizontally for high query volumes
• Time-series data can be compressed and archived efficiently

Resource Optimization:

• Different databases optimized for different access patterns
• Memory usage optimized per database type
• Storage costs reduced through specialized compression

Why Syncer Instead of Message Brokers?

Design Philosophy: Lightweight & Cost-Effective

Oktuple's data synchronization strategy deliberately avoids heavy message broker systems in favor of a custom Syncer service built on PostgreSQL's logical replication. This architectural decision is based on several key principles:

1. Lightweight Architecture

Minimal Resource Footprint:

• No Additional Infrastructure: Syncer leverages existing PostgreSQL infrastructure
• Low Memory Usage: Direct database connection vs. message broker's JVM overhead
• Simple Deployment: Single service vs. message broker cluster management
• Reduced Complexity: Fewer moving parts to monitor and maintain

Performance Benefits:

• Direct Database Access: No network hops between database and message broker
• Native Replication: PostgreSQL's built-in logical replication is highly optimized
• Low Latency: Direct change stream processing without queuing delays
• Efficient Resource Usage: Minimal CPU and memory overhead

2. Cost-Effective Data Transfer

Infrastructure Cost Optimization:

• No Message Broker Cluster: Eliminates need for separate message broker infrastructure
• Shared Resources: Syncer runs on existing application servers
• Reduced Network Traffic: Direct replication vs. publish-subscribe overhead
• Simplified Monitoring: Single point of monitoring vs. distributed system complexity

Operational Cost Benefits:

• Lower Maintenance: Fewer services to maintain and update
• Simplified Backup: Single database backup strategy
• Reduced Complexity: Easier troubleshooting and debugging
• Cost Predictability: Linear scaling with database growth

3. Saga Pattern Implementation for Data Consistency

Event-Driven Consistency Model:

Saga Pattern Benefits:

1. Compensating Transactions:

   • If search database sync fails, Syncer can retry or rollback
   • PostgreSQL remains the source of truth
   • Eventual consistency guaranteed through retry mechanisms

2. Event Ordering:

   • PostgreSQL logical replication maintains event order
   • No need for complex message ordering in message brokers
   • Natural event sequencing through database transactions

3. Failure Recovery:

   • Failed syncs can be retried from PostgreSQL change log
   • No message loss due to direct database connection
   • Automatic recovery on service restart

4. Why Not Message Brokers?

Message Broker Complexity Overhead:

• Cluster Management: Requires coordination services (Zookeeper, etc.)
• Partition Management: Complex partitioning strategies for data consistency
• Consumer Group Coordination: Additional complexity for load balancing
• Schema Evolution: Requires Schema Registry for data evolution

Resource Requirements:

• JVM Overhead: Message brokers typically run on JVM with significant memory requirements
• Disk I/O: Additional disk operations for message persistence
• Network Overhead: Multiple network hops for message processing
• Operational Complexity: Requires specialized message broker expertise

Cost Implications:

• Infrastructure: Additional servers for message broker cluster
• Storage: Duplicate data storage in message topics
• Network: Increased network traffic for message distribution
• Monitoring: Additional monitoring tools for message broker health

5. Syncer Architecture Advantages

Direct Database Integration:

PostgreSQL Logical Replication → Syncer Service → Specialized Databases

Benefits:

• Real-time Processing: Immediate change detection and processing
• ACID Compliance: Leverages PostgreSQL's transaction guarantees
• Schema Awareness: Direct access to database schema and constraints
• Native Performance: Optimized for PostgreSQL's replication protocol

Saga Implementation Details:

1. Transaction Boundaries:

   • Each PostgreSQL transaction becomes a saga step
   • Syncer processes changes within transaction boundaries
   • Rollback capabilities through PostgreSQL's transaction log

2. Compensation Logic:

   • Failed syncs trigger compensation actions
   • Retry mechanisms with exponential backoff
   • Dead letter queue for persistent failures

3. Event Sourcing:

   • All changes captured in PostgreSQL's WAL (Write-Ahead Log)
   • Complete audit trail of all data modifications
   • Point-in-time recovery capabilities

6. Performance Characteristics

Latency Comparison:

• Message Brokers: ~10-50ms (network + serialization + queuing)
• Syncer: ~1-5ms (direct database replication)

Throughput:

• Message Brokers: High throughput but with overhead
• Syncer: Optimized for PostgreSQL's replication capacity

Resource Usage:

• Message Brokers: High memory and CPU usage
• Syncer: Minimal resource footprint

7. Future Scalability Considerations

When to Consider Message Brokers:

• High Message Volume: >100K messages/second
• Multiple Consumers: Complex fan-out patterns
• Geographic Distribution: Multi-region deployments
• Message Persistence: Long-term message retention requirements

Current Syncer Limitations:

• Single Consumer: One Syncer instance per database
• PostgreSQL Dependency: Tightly coupled to PostgreSQL
• Limited Message Retention: No long-term message storage

Migration Path:

• Syncer can be enhanced to support multiple consumers
• Event sourcing can be extended to support message persistence
• Future migration to message brokers possible if requirements change

8. Monitoring and Observability

Syncer Monitoring:

• Database Metrics: PostgreSQL replication lag monitoring
• Sync Metrics: Success/failure rates per target database
• Performance Metrics: Processing latency and throughput
• Health Checks: Service availability and database connectivity

Alerting Strategy:

• Replication Lag: Alert when sync falls behind
• Sync Failures: Immediate alert on sync failures
• Resource Usage: Monitor CPU and memory consumption
• Database Health: Monitor PostgreSQL replication status

Service Architecture

PMN Service

Purpose: Primary API service handling all project management operations

Responsibilities:

• Project and issue management
• Template management
• Reporting and analytics
• Add-ons and metadata
• Excel import/export capabilities

Technology Stack:

• Framework: Hono (lightweight, fast)
• Language: TypeScript
• Database: PostgreSQL with Prisma ORM
• Cache: Redis
• Authentication: Casdoor integration
• Validation: Zod schemas

Key Components:

pmn/
├── src/
│   ├── routes/           # API endpoints
│   ├── services/         # Business logic
│   ├── middleware/       # Authentication, permissions
│   ├── utils/            # Helper functions
│   └── interfaces/       # Type definitions
├── prisma/               # Database schema
├── infra/                # Infrastructure services
└── worker/               # Background job processing

Syncer Service

Purpose: Real-time data synchronization between PostgreSQL and search engines

Responsibilities:

• Capture database changes via logical replication
• Sync data to Elasticsearch and Typesense
• Provide WebSocket notifications
• Handle queue management with BullMQ
• Ensure data consistency across systems

Technology Stack:

• Language: TypeScript
• Database: PostgreSQL with logical replication
• Search: Elasticsearch, Typesense
• Queue: Redis + BullMQ
• Real-time: WebSocket

Key Components:

syncer/
├── src/
│   ├── services/         # Sync and connector services
│   ├── utils/            # Data processing utilities
│   ├── config/           # Environment configuration
│   └── types/            # Type definitions
└── bootstrap/            # Database initialization

Data Flow Architecture

1. Primary Data Operations (PMN)

Client Request → PMN API → Prisma ORM → PostgreSQL → Response

Flow:

1. Client sends request to PMN API
2. Request validated with Zod schemas
3. Authentication/authorization middleware
4. Business logic in service layer
5. Database operations via Prisma
6. Response returned to client

2. Real-time Data Synchronization (Syncer)

PostgreSQL Change → Logical Replication → Syncer → Search Engines

Flow:

1. Database change occurs in PostgreSQL
2. Logical replication captures the change
3. Syncer processes the change
4. Data synchronized to Elasticsearch and Typesense
5. WebSocket notification sent to clients
6. Fallback queue if WebSocket fails

3. Real-time Updates (WebSocket)

Database Change → Syncer → WebSocket → Client Applications

Flow:

1. Syncer detects database change
2. WebSocket notification prepared
3. Real-time update sent to connected clients
4. Client applications update UI immediately

Data Storage Architecture

Primary Database (PostgreSQL)

Purpose: Main data persistence for all project management data

Key Tables:

• enode: Project hierarchy and structure
• issue: Tasks, bugs, and work items
• people: User management and roles
• template: Project templates and workflows
• addon: Extended functionality and metadata

Supporting Tables:

• attachment: File uploads and documents
• link: Dependencies
• marker: Visual indicators
• reminder: Notifications
• role: Permissions
• history: Change tracking

Search Engines

Typesense:

• Vector search for similarity matching
• Fast autocomplete and suggestions
• Real-time search results

Caching Layer (Redis)

Purpose: Performance optimization and session management

Usage:

• Frequently accessed data caching
• Session storage
• Rate limiting
• Job queue management (BullMQ)

Security Architecture

Authentication

• Casdoor Integration: Centralized identity management
• JWT Tokens: Secure session management
• Role-based Access Control: Granular permissions

Authorization

• Permission Middleware: Route-level access control
• Data-level Security: Row-level permissions
• Audit Logging: Comprehensive activity tracking

Data Protection

• Input Validation: Zod schema validation
• SQL Injection Prevention: Prisma ORM protection
• HTTPS Enforcement: Secure communication

Scalability Considerations

Horizontal Scaling

• PMN Service: Multiple instances behind load balancer
• Syncer Service: Multiple instances for high availability
• Database: Read replicas for read-heavy workloads

Performance Optimization

• Redis Caching: Frequently accessed data
• Database Indexing: Optimized query performance
• Connection Pooling: Efficient database connections
• Background Processing: Async job processing

Monitoring and Observability

• Logging: Structured logging across services
• Metrics: Performance and health monitoring with prometheus
• Tracing: Distributed request tracing with sentry
• Health Checks: Service health monitoring with kuma uptime

Deployment Architecture

Containerization

• Docker: Consistent deployment environment
• Docker Compose: Local development setup
• Kubernetes: Production deployment

Environment Management

• Development: Local Docker Compose setup
• Staging: Production-like environment
• Production: Container orchestration (Kubernetes ready)

APIs

• REST API: Comprehensive project management endpoints
• WebSocket: Real-time collaboration

Future Considerations

Technology Evolution

• Database: Consider TimescaleDB for time-series data
• Search: Evaluate newer search technologies
• Caching: Consider distributed caching solutions
• Monitoring: Enhanced observability tools