Implement storage adapter interface for persisting location tracks to PostgreSQL

[levibliz]

Background

The README and architecture diagram explicitly describe a Storage Adapter layer: "Extensible Storage Adapter — Plug in your preferred database (PostgreSQL, MongoDB, InfluxDB, etc.) for persisting historical tracks." The docker-compose.yml already provisions a PostgreSQL container. Currently, no storage code exists — location_update messages are broadcast and immediately forgotten.

Category: Enhancement / Missing Feature
Difficulty: Medium
Priority: High
Labels: enhancement, backend

Problem Statement

There is no persistence layer. Location updates are ephemeral — once broadcast, they are lost. The PostgreSQL service defined in docker-compose.yml is never connected to. Operators cannot query historical tracks or replay events.

Requirements

• Create src/storage.js exporting a createStorageAdapter(config) factory.
• The adapter must implement an async saveLocation(clientId, roomId, payload) method.
• Implement a NullAdapter (no-op) used by default when DATABASE_URL is not set.
• Implement a PostgresAdapter that writes to a locations table: id, client_id, room_id, latitude, longitude, altitude, accuracy, speed, recorded_at.
• Wire the adapter into server.js: after a valid location_update is broadcast, call adapter.saveLocation(...) fire-and-forget with error logging.
• Add a SQL migration file at db/migrations/001_create_locations.sql.
• Write unit tests for the NullAdapter and a mocked PostgresAdapter.

Acceptance Criteria

• createStorageAdapter() with no DATABASE_URL returns a NullAdapter with a no-op saveLocation.
• createStorageAdapter({ url: 'postgres://...' }) returns a PostgresAdapter.
• A location_update processed by server.js triggers adapter.saveLocation().
• Storage errors are logged but do not crash the server or reject the client message.
• db/migrations/001_create_locations.sql creates the locations table.
• Unit tests for both adapters pass.

Implementation Notes

Use the postgres npm package (not an ORM). Keep the adapter interface minimal — only saveLocation is required for this issue. Connection pooling and migration tooling are out of scope.

Fire-and-forget pattern:

adapter.saveLocation(clientId, roomId, msg.payload).catch((err) => {
  logger.error('Failed to persist location', { clientId, error: err.message });
});

Files Likely to Change

• src/storage.js (new)
• src/server.js
• src/index.js
• db/migrations/001_create_locations.sql (new)
• tests/storage.test.js (new)

Definition of Done

Location updates are persisted when DATABASE_URL is set. Service degrades gracefully when it is not. Both paths are tested.

Estimated Complexity

Medium — new module, clear interface, but requires async lifecycle management and error handling discipline.

[xJeffx23]

Hi team! 👋

My name is Jefferson. I have experience with Node.js, backend architecture, PostgreSQL, SQL, asynchronous programming, automated testing, and designing modular systems, along with previous open-source contributions.

I'd like to work on this issue by implementing the storage adapter architecture with both NullAdapter and PostgresAdapter, integrating persistence into the location update flow, adding the SQL migration, and covering the new functionality with comprehensive unit tests while ensuring graceful error handling.

I'd love the opportunity to contribute to this project. Thank you!