All jobs

Add Database Indexes to a Mongoose App

Yes. An agent reads every query in your Mongoose models and services, adds the compound and TTL indexes they need, proves them with explain(), 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 starts from the queries, not the schemas, because an index that matches no query is dead weight. In an app started from hagopj13/node-express-boilerplate, package.json pins mongoose ^5.7.7, resolved to 5.13.20 in yarn.lock, with Jest 26 and the test script jest -i --colors --verbose --detectOpenHandles. src/models/token.model.js declares token with index: true, a user reference, a type enum of refresh, reset-password and verify-email, an expires date and a blacklisted flag, with timestamps. src/models/user.model.js has email with unique: true, which Mongoose turns into a unique index, a role enum and timestamps. src/config/config.js connects with useCreateIndex: true, and the test database is the same URL with -test appended; tests/utils/setupTestDB.js empties every collection with deleteMany before each test, which keeps indexes in place.

Reading the services gives the list. src/services/token.service.js looks tokens up with findOne({ token, type, user, blacklisted: false }), which the existing single-field index on token serves. src/services/auth.service.js runs Token.deleteMany({ user, type }) after a password reset and after an email verification, and logout finds by token and type; nothing serves user plus type today, so those deletes scan. generateAuthTokens saves a refresh token on every login with an expires thirty days out by default, and nothing ever removes a refresh token that is simply never used again: logout and refreshAuth remove the one they are given, so the collection grows with every login that is not followed by a logout. On users, src/controllers/user.controller.js filters GET /v1/users by name and role and the paginate plugin sorts by createdAt unless sortBy is given.

From that the agent adds three indexes and leaves two out. On tokenSchema, index({ user: 1, type: 1 }) for the deletes and for any future per-user listing, and index({ expires: 1 }, { expireAfterSeconds: 0 }), a TTL index that makes MongoDB's background task delete each token once its expires date has passed; the mongo:4.2.1-bionic image in docker-compose.yml supports it. On userSchema, index({ role: 1, createdAt: 1 }) for the filtered, sorted user list. It does not index name, because an exact-match filter on a free-text field is a rare query, and it does not touch email, which is already unique. The pull request explains that TTL deletion is a behaviour change: an expired reset or verify token disappears from the collection instead of lingering, which jwt.verify in verifyToken would have rejected anyway.

Index building is the operational half. Mongoose 5 builds indexes when a model compiles, which is fine in development and in the test database, but in production the pull request recommends autoIndex: false in the production branch of config.js and a scripts/syncIndexes.js that calls syncIndexes() on every model, with a note that syncIndexes also drops indexes the schema does not declare. Tests go in tests/unit/models/token.model.test.js using setupTestDB: after Token.syncIndexes(), Token.collection.indexes() lists the compound index and the expires index with expireAfterSeconds of zero, and Token.find({ user, type }).explain() reports an index scan on the new key rather than a collection scan. In the sandbox the agent starts a local MongoDB, runs yarn lint and yarn test as .travis.yml does, and opens the pull request.

Start it with one API call

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

{
  "agent": "claude",
  "input": "Add database indexes to this Mongoose app. Read package.json, yarn.lock for the resolved mongoose version, src/config/config.js, src/models/token.model.js, src/models/user.model.js, src/models/plugins/paginate.plugin.js, src/services/token.service.js, src/services/auth.service.js, src/services/user.service.js, src/controllers/user.controller.js, tests/utils/setupTestDB.js, tests/unit/models/user.model.test.js, docker-compose.yml and .travis.yml first. List every query each model receives, then add on tokenSchema a compound index on user and type and a TTL index on expires with expireAfterSeconds 0, and on userSchema a compound index on role and createdAt. Do not index name and do not change email. Explain in the pull request why each index exists, which query it serves, and that the TTL index deletes expired tokens on the server. Add a note recommending autoIndex false in production with a scripts/syncIndexes.js that calls syncIndexes on every model, and say that syncIndexes drops undeclared indexes. Add tests/unit/models/token.model.test.js using setupTestDB that asserts the indexes after Token.syncIndexes() through Token.collection.indexes() and that Token.find({ user, type }).explain() uses an index scan. Start a local MongoDB, run yarn lint and yarn test, then open a pull request that pastes the test output and the explain plan.",
  "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 src/models/token.model.js, src/models/user.model.js and src/config/config.js and adds scripts/syncIndexes.js and tests/unit/models/token.model.test.js. The description has a table of each index, the file and line of the query it serves, and the explain output showing IXSCAN on user_1_type_1 for the delete pattern, plus a paragraph on what the TTL index changes.

The run's record holds the MongoDB start, yarn lint, and the Jest run with the existing unit and integration suites plus the new index tests. The services, controllers and routes are untouched: the change is declarative, in the schemas, and the queries that benefit are the ones already there.

Before merging, a reviewer confirms the production rollout: with autoIndex off, the new indexes exist only after scripts/syncIndexes.js runs against the production database, and building a TTL index on a large token collection deletes every already-expired document on its first pass, which is the intended cleanup but worth doing off-peak.

Run it on every new collection or query

An automation on the Project with a github trigger on pull_request.opened and pull_request.synchronize and the prompt "if this pull request adds a find, findOne, deleteMany or sort on a model, check whether an index in that schema serves it and comment with the key it needs or say none is needed" reviews each query as it arrives. The trigger adds the pull request after the prompt; only work by an owner, member or collaborator fires it, never a fork's.

A team with several Mongoose services starts one run per repository from its own server: one POST /o/{org}/sessions each, with that repository in resources and a request_id that is a UUID v5 of the repository's full name, so a retried call returns the run already started rather than a second pull request. Each run reads that service's own services and controllers, so the indexes match that service's queries and not a template.

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

Why run it on Rush?

The hard part of this job is not writing schema.index, it is reading every service to find which fields are queried together and which collections grow without bound; the refresh tokens in this boilerplate are the kind of leak nobody notices until the collection is large. The agent does that reading in one pass, writes the indexes the queries need, and proves them with explain() against a real database rather than asserting that they help.

In its own sandbox the agent runs MongoDB, the lint and the full Jest suite, and the run keeps the explain plans for the pull request. For a team with many Node services on Mongoose, each gets its own run and its own pull request with indexes read from that service's queries.

Frequently asked questions

Will the TTL index delete tokens users still need?

No. It removes a token only after its expires date, and verifyToken already rejects an expired token through jwt.verify before it looks the document up, so nothing that is deleted could have been accepted. What changes is that expired refresh, reset and verify tokens no longer accumulate; MongoDB removes them in the background shortly after they expire.

Why not index name on users?

The user list filters name by exact match, which is a rare request against a free-text field, and an index on it would be written on every user save for little benefit. The pull request says so; if the product adds a name search, a text index is the right tool and a separate change.

What does autoIndex false change in production?

With it on, Mongoose 5 sends createIndex for every declared index each time the app starts, which on a large collection can run during startup. With it off, indexes are built only when scripts/syncIndexes.js runs, at a time the team chooses. Development and the test database keep autoIndex on so the tests see the indexes without an extra step.

Do the integration tests still pass with the new indexes?

Yes. setupTestDB empties collections with deleteMany rather than dropping them, so indexes persist across tests, and the existing tests never insert two tokens that would collide, because none of the new indexes is unique. The new test calls Token.syncIndexes() first so it does not depend on model compile order.

More Mongoose jobs

The same job on other stacks

Keep reading