SQL & Database Bridges

Relevant source files

The following files were used as context for generating this wiki page:

• bubus/bridge_postgres.py
• bubus/bridge_sqlite.py
• bubus-ts/src/bridge_postgres.ts
• bubus-ts/src/bridge_sqlite.ts
• bubus-ts/tests/bridges.test.ts
• ui/main.py

SQL and Database Bridges provide a mechanism for persistent event forwarding and cross-runtime communication within the bubus ecosystem. These bridges allow events dispatched in one environment (e.g., a Python backend) to be mirrored into a database and subsequently picked up by another environment (e.g., a TypeScript/Node.js worker), ensuring durable delivery and history tracking.

The system primarily supports PostgreSQL and SQLite backends. The PostgreSQL bridge utilizes LISTEN/NOTIFY for real-time reactive updates, while the SQLite bridge employs a high-frequency polling loop with Write-Ahead Logging (WAL) support for local inter-process communication. Both implementations share a unified "flat-table" schema designed for efficient indexing and payload extraction.

Core Database Schema

Both PostgreSQL and SQLite bridges utilize a standardized table structure. This "flat-table" design promotes interoperability between Python and TypeScript runtimes.

Column	Type	Description
event_id	TEXT	Primary Key. The unique identifier for the event.
event_created_at	TEXT	ISO 8601 timestamp of event creation (indexed).
event_type	TEXT	The class name or string type of the event (indexed).
event_payload	JSON/TEXT	The full JSON-serialized event object.
event_*	TEXT	Dynamic columns created for each top-level event field.

Sources: bubus/bridge_postgres.py:17-25, bubus-ts/src/bridge_sqlite.ts:133-138

Dynamic Column Projection

When an event is dispatched through a bridge, the system automatically inspects the event payload. Any key starting with event_ (excluding the main event_payload column) is projected into its own dedicated column in the database table. If the column does not exist, the bridge issues an ALTER TABLE command to add it dynamically.

Sources: bubus/bridge_postgres.py:270-285, bubus-ts/src/bridge_sqlite.ts:197-211

PostgreSQL Bridge

The PostgresEventBridge uses a combination of table storage for durability and the LISTEN/NOTIFY protocol for low-latency distribution.

Architecture and Data Flow

When an event is dispatched, it is inserted into the configured table. Immediately following the insert, the bridge executes pg_notify on a specific channel, passing the event_id. Listening bridges receive this notification and query the table for the specific record to re-hydrate the event.

The bridge maintains two separate connections: a write connection for dispatching and a dedicated listener connection for receiving notifications.

Sources: bubus/bridge_postgres.py:100-112, bubus-ts/src/bridge_postgres.ts:121-137

Implementation Details

The TypeScript and Python versions share consistent URL parsing logic for configuration:
postgresql://user:pass@host:5432/dbname[/tablename]

• Python (asyncpg): Uses connection.add_listener() to handle asynchronous notifications within the asyncio loop. Sources: bubus/bridge_postgres.py:145-155
• TypeScript (pg): Listens for the notification event on the client object and triggers an internal dispatchByEventId method. Sources: bubus-ts/src/bridge_postgres.ts:127-135

SQLite Bridge

The SQLiteEventBridge is optimized for local inter-process communication (IPC) where a shared database file is accessible to multiple runtimes.

Architecture and Polling Logic

Unlike PostgreSQL, SQLite lacks a native cross-process notification system. To achieve inter-process forwarding, the SQLite bridge implements a polling loop. It tracks progress using a "cursor" based on event_created_at and event_id.

To ensure consistency and allow concurrent reads/writes without locking, the bridge explicitly enables PRAGMA journal_mode = WAL.

Sources: bubus/bridge_sqlite.py:175-188, bubus-ts/src/bridge_sqlite.ts:114-118

Cursor Persistence

On startup, the bridge executes a query to find the latest existing row and sets its internal cursor to that point. This prevents the bridge from re-dispatching the entire history of the table upon initialization.

Sources: bubus/bridge_sqlite.py:276-291, bubus-ts/src/bridge_sqlite.ts:225-235

Integration with Event Monitoring UI

The project includes a monitoring UI that consumes the data produced by these bridges, specifically the SQLite variant.

• History Mirroring: The SQLiteHistoryMirrorMiddleware (referenced in UI logic) populates the tables that the UI reads. Sources: ui/main.py:441-445
• Real-time Streaming: The UI uses a HistoryStreamState to track the last seen event and result IDs, providing a live view of the bus activity via WebSockets. Sources: ui/main.py:456-474

Summary of Configuration Options

Option	Type	Default	Bridge
table_url	String	N/A	Postgres
channel	String	bubus_events	Postgres
path	String	N/A	SQLite
table	String	bubus_events	SQLite
poll_interval	Float	0.25s	SQLite

Sources: bubus/bridge_postgres.py:65-72, bubus-ts/src/bridge_sqlite.ts:47-50

Conclusion

SQL & Database Bridges act as the persistent backbone for the bubus library. By leveraging standard SQL features (PostgreSQL NOTIFY or SQLite WAL), they provide a reliable way to bridge the gap between isolated process runtimes while maintaining the rich metadata and schema-validated nature of bubus events.