MongoDB index update — strategy and scripts

Purpose: when to sync indexes, how to run or add scripts, and constraints so implementations stay consistent with this repo.

---

Mongodb Index

• Indexes in MongoDB speed queries; Mongoose declares them via index: true on schema paths and Schema.index({ ... }) in src/models/*.ts.
• Drift: the database may still have old or manual indexes until you sync.
• Model.syncIndexes(): creates indexes missing vs schema; drops indexes not matching schema. Return value = names of dropped indexes only (created indexes are not listed the same way).
• Text index: at most one classic $text index per collection. Changing text fields usually requires dropping the old text index first if MongoDB errors, then syncIndexes() again.
• Shared cluster: if staging and production use the same URI, one sync affects both—prefer dry-run on MONGODB_URI pointing at a local/restored DB first; snapshot before production sync.

---

When to run or add an index sync

Trigger sync after the schema file is updated in git:

Trigger	Required action
Add/remove/change index: true or *.index({ ... }) on a model	Run existing sync script for that model or add one (see template below).
Change text index fields	Expect possible manual dropIndex of old text index name, then sync.
Request to “apply indexes” / “sync DB with schema”	Use script + verify with getIndexes().

Do not run syncIndexes() on every request or app startup. Use a one-off script (tsx) or an explicit maintenance command.

When to run the script: after index-related schema changes are merged and you are ready to apply them to a given database (e.g. deploy or planned maintenance)—not on every server boot.

While the script runs: expect higher latency and slower queries on that collection until index work finishes—nothing breaks; data stays intact and the app can keep serving traffic. For production, prefer a quieter window (lower traffic) so fewer users hit the slowdown and the cluster has more headroom.

---

Existing implementation: jobs

Item	Value
Model file	src/models/Job.ts
Collection	jobs (third arg to mongoose.model)
Script	scripts/sync-job-indexes.ts
Package command	pnpm sync:job-indexes
Env	MONGODB_URI from .env.local (or override in environment)
Dry-run	MONGODB_URI='mongodb://localhost/your_db' pnpm sync:job-indexes

Failure: syncIndexes errors on conflicting text index → obtain text index name from db.jobs.getIndexes(), dropIndex that name in mongosh/Compass, re-run pnpm sync:job-indexes.

Verify: db.jobs.getIndexes() matches Job.ts; optional $text smoke test (e.g. external job search API).

---

How to add a sync script for another model

1. Edit the Mongoose schema first (index: true / Schema.index) and merge; do not sync before schema changes exist.
2. Create scripts/sync-<collection>-indexes.ts (or sync-<model-name>-indexes.ts) following the same pattern as scripts/sync-job-indexes.ts:
   • config({ path: resolve(__dirname, '../.env.local') }) before importing ../src/lib/mongoose (that module reads MONGODB_URI at load time).
   • Dynamic or static import: connectDB from ../src/lib/mongoose, target Model from ../src/models/<ModelFile>.
   • await connectDB() then await <Model>.syncIndexes().
   • Log the returned array (dropped index names).
   • mongoose.connection.close() and process.exit on success/failure.
3. Add a package.json script: "sync:<name>-indexes": "tsx scripts/sync-<name>-indexes.ts".
4. Document the command in a one-line comment at top of the script.

Do not import @/lib/mongoose in scripts unless the repo’s tsx resolves path aliases; this repo uses relative imports to ../src/lib/mongoose like delete-test-account.ts.