All jobs

Move a Socket.IO Chat From SQLite to PostgreSQL

Yes. An agent replaces the sqlite3 store in your clustered Socket.IO server with a pg pool, keeps message ids and reconnect replay intact, tests it, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads the whole server first, because the store is tied to how reconnection works. In a chat built like socketio/chat-example, index.js forks one worker per CPU with node:cluster, each on PORT 3000 plus its index, and runs setupPrimary() for the @socket.io/cluster-adapter. Every worker opens the same chat.db file with sqlite3 through the sqlite wrapper and runs CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, client_offset TEXT UNIQUE, content TEXT). The chat message handler inserts the content with the client's offset, treats e.errno === 19, SQLite's constraint error, as a duplicate and acknowledges it, and otherwise broadcasts the message with result.lastID as the server offset. On a connection that was not recovered, it replays SELECT id, content FROM messages WHERE id > ? from socket.handshake.auth.serverOffset. In index.html the client sends ${socket.id}-${counter++} as its offset, retries with ackTimeout: 10000 and retries: 3, and stores each serverOffset it receives.

Those three facts fix the design: ids must stay monotonic because clients hold them as offsets, the duplicate check must survive the change because the client retries, and each worker needs its own connection because each is its own process. The agent replaces sqlite and sqlite3 in package.json with pg, creates one Pool per worker from DATABASE_URL, and writes the table as id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, client_offset TEXT UNIQUE, content TEXT NOT NULL. The insert becomes INSERT ... VALUES ($1, $2) RETURNING id, the duplicate check becomes err.code === '23505', PostgreSQL's unique violation, and the replay adds ORDER BY id, which SQLite's rowid scan gave for free and PostgreSQL does not promise.

Existing messages matter because every connected browser holds an offset into them. The agent adds scripts/import-sqlite.js, which reads chat.db with the old driver and inserts each row with OVERRIDING SYSTEM VALUE so ids are preserved, then sets the identity sequence past the highest id. A fresh database with ids restarting at 1 would make a client with serverOffset: 4000 miss everything until the counter caught up, which is the bug the script prevents. A .env.example carries DATABASE_URL, the README gets the two commands, and connectionStateRecovery and the cluster adapter are left as they are.

The repository has no tests, so the agent writes test/replay.test.js with node:test and socket.io-client: it starts one worker against a PostgreSQL it brings up in its sandbox, sends a message from one client, connects a second with serverOffset: 0 and asserts the replay, resends the first message with the same client offset and asserts a single row. It runs the test, runs the import script against a copied chat.db, and starts the clustered server to see two workers broadcast to each other through the adapter.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Move this Socket.IO chat server from SQLite to PostgreSQL. Read package.json, index.js, index.html and README.md first. Replace sqlite and sqlite3 with pg, open one Pool per cluster worker from DATABASE_URL, create the messages table with a BIGINT identity id, a unique client_offset and a non-null content, use INSERT ... RETURNING id, treat error code 23505 as the duplicate the acknowledgement already handles, and add ORDER BY id to the replay query. Keep connectionStateRecovery and the cluster adapter unchanged. Add scripts/import-sqlite.js that copies chat.db rows into PostgreSQL with their ids preserved using OVERRIDING SYSTEM VALUE and advances the identity sequence, a .env.example with DATABASE_URL, and README instructions. Write a node:test test with socket.io-client that proves replay from serverOffset 0 and deduplication of a retried message against a real PostgreSQL, run it, run the import script against a copy of chat.db, and open a pull request quoting the test output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that changes index.js and package.json, adds the import script, the test, .env.example and a test script, and extends the README. The description quotes the node --test output and the import script's count of rows copied and the sequence value it set, and shows the before and after of the three queries that changed.

The run's record keeps the test run, the import run and the clustered server's startup lines, so a reviewer can see two workers broadcasting one message rather than take the adapter's behaviour on trust.

Before merging, a reviewer settles the pool size per worker, since a machine with many CPUs now opens many pools against one database, and whether DATABASE_URL should fall back to anything in development. The agent leaves the fallback out so a missing variable fails at startup instead of writing to a surprise database.

Run it on every chat service still on a SQLite file

A team with several small realtime services starts one run per repository with one POST /o/{org}/sessions each, this prompt as input and the repository in resources. Each run reads its own handler and schema, so a service whose table has a room column or a different duplicate check gets the PostgreSQL version of what it actually does, not of this example.

Send a request_id that is a UUID v5 of the repository's full name, so a retried call returns the run it already started. The key's webhook_url reports when each run the key starts finishes, with session_url to read the pull request from and the run's final answer in result.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

The migration is small in lines and large in consequences: an id sequence that restarts, a replay query without an order, a duplicate check that still looks for SQLite's error number, and every browser that reconnects after the deploy sees the wrong history. Each of those is caught only by running the server against a real PostgreSQL with real clients, which the agent does in its own sandbox before anyone deploys.

The pull request carries the test, the import script's output and the server's startup lines, so the reviewer reads evidence rather than a description of what should happen. Across several services the job runs in parallel, one sandbox and one pull request each.

Frequently asked questions

Why one pool per worker rather than one shared connection?

Because each cluster worker is a separate Node.js process; they share nothing but the primary's adapter channel. Each worker opened its own SQLite handle before, and each opens its own Pool now. The pool's size is the setting to watch, since it multiplies by the number of CPUs.

Does reconnection still work during the switch?

Yes, provided ids are preserved. A client's serverOffset is the id of the last message it saw, and connectionStateRecovery handles short gaps on its own. The import script keeps every id and advances the sequence, so a client that reconnects after the deploy replays from where it left off.

What happens to the retry when the insert fails for another reason?

The same as before: the handler returns without acknowledging, and the client's retries: 3 with ackTimeout: 10000 sends it again. Only the unique violation, now 23505, is acknowledged as a duplicate.

Can this use a hosted PostgreSQL?

Yes. DATABASE_URL is the only setting, and pg reads sslmode from it. The test needs a database it can create a table in, which is why the agent runs one in its sandbox rather than pointing the test at yours.

More Socket.IO jobs

The same job on other stacks

Keep reading