`SqliteMessageQueue` for single-node deployments

[dahlia]

Summary

Add a SQLite-based MessageQueue implementation to the @fedify/sqlite package to complement the existing SqliteKvStore. This would enable complete Fedify stack deployment without requiring Redis or PostgreSQL infrastructure, particularly beneficial for single-node instances.

Motivation

Currently, @fedify/sqlite provides SqliteKvStore but no MessageQueue implementation, unlike @fedify/redis and @fedify/postgres which offer both. This creates an asymmetry where users choosing SQLite for key–value storage must still set up additional infrastructure for message queuing.

Use cases that would benefit:

• Personal or small community fediverse instances running on single servers
• Development and testing environments
• Self-hosted instances prioritizing simplicity and minimal infrastructure
• Docker/container deployments aiming for single-container operation

Proposed implementation

Core features

• ACID-compliant message enqueue/dequeue operations
• Topic-based message routing
• Retry mechanism with configurable attempts
• Message expiration/cleanup policies
• WAL mode for improved concurrency

Example schema

CREATE TABLE IF NOT EXISTS fedify_message_queue (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  payload TEXT NOT NULL,
  created INTEGER NOT NULL,
  scheduled INTEGER NOT NULL,
  processed INTEGER,
  retry_count INTEGER DEFAULT 0,
  max_retries INTEGER DEFAULT 3,
  error TEXT
);

CREATE INDEX IF NOT EXISTS idx_scheduled_processed 
  ON fedify_message_queue(scheduled, processed);

API design

The implementation should follow the existing MessageQueue interface:

export class SqliteMessageQueue implements MessageQueue {
  readonly nativeRetrial = false;
  
  constructor(db: Database, options?: SqliteMessageQueueOptions);
  
  enqueue(
    message: any,
    options?: MessageQueueEnqueueOptions,
  ): Promise<void>;
  
  enqueueMany?(
    messages: any[],
    options?: MessageQueueEnqueueOptions,
  ): Promise<void>;
  
  listen(
    handler: (message: any) => Promise<void> | void,
    options?: MessageQueueListenOptions,
  ): Promise<void>;
}

Limitations and considerations

Explicit limitations

This implementation would be explicitly designed for single-node deployments only:

• Cannot be used in multi-node/distributed setups
• Write operations are serialized even in WAL mode
• Not suitable for very high throughput scenarios (thousands of messages per second)
• Network file systems (NFS) are not recommended

Documentation requirements

• Clear documentation stating “single-node only” constraint
• Performance characteristics and expected throughput
• Migration path from SQLite to Redis/PostgreSQL as applications scale
• Best practices for PRAGMA settings and performance tuning

Technical considerations

Concurrency handling

• Use WAL mode for better read concurrency
• Implement proper locking with SELECT ... FOR UPDATE or equivalent
• Prevent duplicate message processing in multi-worker scenarios

Performance optimization

• Configurable PRAGMA settings (journal_mode, synchronous, cache_size, etc.)
• Efficient indexing for topic-based queries
• Batch processing capabilities
• Periodic cleanup of processed messages

Error handling

• Dead letter queue for repeatedly failed messages
• Configurable retry delays (exponential backoff)
• Logging of processing errors

Benefits

1. Simplified deployment: Complete Fedify stack with just SQLite
2. Consistent backup: Single SQLite file for both KvStore and MessageQueue
3. Lower operational overhead: No additional services to manage
4. Better DX: Easier local development setup

Open questions

1. Should SqliteKvStore and SqliteMessageQueue share the same SQLite database file, or use separate files?
2. What default values should we use for retry counts, cleanup intervals, etc.?
3. Should timestamp columns use INTEGER (Unix timestamp in milliseconds) or TEXT (ISO 8601 format)?

Related work

• sqlite-queue
• better-sqlite3-queue
• Celery's SQLite broker support