What the agent does
The agent builds the list from the queries, not from the entities. In a NestJS app like lujakob/nestjs-realworld-example-app (TypeORM 0.2.24 on MySQL) it reads every repository call in src/article/article.service.ts, src/user/user.service.ts and src/profile/profile.service.ts and writes down the columns each one filters or sorts on. findOne({slug}) appears in seven article methods and delete({slug}) in an eighth. Login runs findOne({email}), profiles and the article filters run findOne({username}), and registration looks for either with a query builder. The feed starts with followsRepository.find({followerId}), and following checks findOne({followerId, followingId}). Both listings sort with orderBy('article.created', 'DESC') under a limit and offset.
Then it reads the five entities and finds no index decorator in any of them. slug in ArticleEntity, username and email in UserEntity, and both columns of FollowsEntity are plain @Column() fields, and FollowsEntity declares no relation, so its ids are bare integers with no foreign key. Two columns are already covered without anyone asking: author on the article and article on the comment are @ManyToOne relations, and InnoDB creates an index on a foreign key column when none exists. The agent says so and does not add a duplicate. It also marks one query as beyond the reach of any index: the tag filter is article.tagList LIKE '%tag%' on a simple-array column, a comma-separated string, and a pattern with a leading wildcard cannot use a B-tree. That is a schema change, a tag join table, and the agent reports it without attempting it.
Uniqueness is where this job can break a deploy. slug, email and username should each be unique, and the pair in follows should be too, but nothing has enforced that so far. slugify appends a random base-36 suffix, registration checks and then inserts, and follow looks for a row and then saves one, so duplicates are possible in any database that has taken concurrent traffic. The committed ormconfig.json.example sets "synchronize": true, which means a new unique index is built when the next version starts, and the build fails if two rows share a value. So before it writes a decorator the agent writes the duplicate check for each candidate, a GROUP BY with HAVING COUNT(*) > 1, runs it in the sandbox, and puts the same queries in the pull request for someone to run against production data.
Evidence comes from the database server, not from reasoning about it. The agent installs a MySQL-compatible server from system packages, creates ormconfig.json from the example, starts the app so the tables exist, and loads enough generated rows that the optimizer has a reason to choose. It runs EXPLAIN on each statement TypeORM emits, captured from its query log, before and after: a lookup by slug should move from a full scan to a single-row index read, the follow check to the composite index. An index that the plan does not pick up is removed again. What stays is expressed as @Index({ unique: true }) on the three single columns, @Index(['followerId', 'followingId'], { unique: true }) on the follows class, and a non-unique index on created only if the listing's plan stops sorting the whole table because of it.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "codex",
"input": "An issue labelled performance says lookups in this NestJS and TypeORM application get slower as tables grow. Add the indexes the queries need, staying on the installed TypeORM version. Read package.json, ormconfig.json.example, every entity, and every repository and query builder call in the services first. Make a table of each query, the columns it filters and sorts on, and whether an index already covers them, counting indexes the database creates by itself for foreign keys. Install a database server of the kind the config names from system packages and say which server and version you installed. Create the schema the way the application does, load enough generated rows that query plans are meaningful, and capture the SQL that TypeORM emits for each query. Run EXPLAIN on each before any change and keep the output. For every column or pair that should be unique, first write and run a query that finds existing duplicates, and include those queries in the pull request so they can be run on production data before deploying. Add indexes with TypeORM decorators on the entities. Run EXPLAIN again and remove any index the plan does not use. Do not change column types, do not add relations, and do not rewrite queries; where a query cannot be helped by an index, say why and what schema change would help. If the project uses migrations instead of synchronize, generate one for the indexes. Add tests that inserting a duplicate into each unique index is refused. Open a pull request with the query table, the before and after plans, the duplicate checks and the test output.",
"resources": [
{
"url": "https://github.com/your-org/your-app"
}
]
}- 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.
- 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.
- 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
The pull request changes a handful of decorator lines in article.entity.ts, user.entity.ts and follows.entity.ts and adds specs. Its description leads with the query table: each repository call, the columns it uses, the index that now serves it, and the EXPLAIN row from before and after. Queries that needed nothing, because a foreign key index was already there, are in the table too, so a reviewer can see they were considered.
A section headed before you deploy holds the duplicate checks, one per unique index, and says what to do with rows they return: merge or rename them first, because with synchronize on the application will not start until the index can be built. If the repository had moved to migrations, the same section would hold the generated migration and the order to run things in. The tag filter gets its own short paragraph explaining why no index was added.
The reviewer decides whether the unique constraints match the product's rules, for instance whether usernames that differ only by case are the same user. They can rerun the EXPLAIN statements from the description on a staging copy, where table sizes are closer to real ones than anything generated in a sandbox.
Run it on every GitHub issue labelled performance
Slow lookups are reported one endpoint at a time. Add an automation to the Project in the console with a github trigger on issues.labeled and performance in its labels. The saved prompt carries the method: map queries to columns, check duplicates before any unique index, prove each index with a plan, remove the ones that do not help. The issue is appended after the prompt and tells the agent which endpoint someone measured.
Only an owner, member or collaborator of the repository can fire a github trigger, and bot senders never do. The automation has its own max_runs_per_hour, so a burst of labelled issues past it waits queued instead of starting a dozen runs that would all touch the same entity files.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
Why run it on Rush?
An index is a claim about a query plan, and a plan only exists on a server with data in it. The agent gets a database of its own for the run, fills it, and keeps every EXPLAIN in the run's record, so the pull request shows plans instead of asserting them. Running the duplicate checks and the failing unique inserts for real is what separates a safe constraint from one that stops the next deploy.
On a laptop this work means resetting a development database, generating rows, and remembering to undo it all. In a sandbox there is nothing to undo. The developer who filed the issue reviews a diff of a few decorators with the measurements attached, and several endpoints can be investigated in separate runs at once.
Frequently asked questions
Should I use @Index, @Unique or unique: true on the column?
For one column they end in much the same place on MySQL, a unique index. @Index with unique: true is the form that also covers several columns and takes a name, so the agent uses it throughout and names each index, which keeps later migrations readable.
Does a unique index on email treat upper and lower case as the same?
On MySQL with a default collation, yes: comparison ignores case, so two addresses that differ only by case collide. The duplicate check uses the column's own collation and therefore finds exactly the rows the index would refuse. On PostgreSQL the comparison is case-sensitive unless you index a lowered expression.
Will creating the indexes lock my tables?
It depends on the server and version; recent InnoDB builds a secondary index without blocking writes, while the build still takes time proportional to the table. With synchronize on, that time is spent during application start. The description states this and suggests creating the indexes by hand first on a large table, after which the application finds them already in place.
Why does it not index the tag filter?
Because the column holds all tags as one comma-separated string and the query searches inside it with a leading wildcard. No ordinary index can serve that. Making it fast means storing tags in their own table and joining, which changes entities and data, so the agent describes it as follow-up work.