mosaic/stack
1
0
Fork 0
Code Issues 10 Pull Requests Actions Packages Projects Releases 2 Wiki Activity

Add composite database index for job_events table #189

New Issue
Closed
opened 2026-02-02 17:24:46 +00:00 by jason.woltje · 0 comments
jason.woltje commented 2026-02-02 17:24:46 +00:00
Owner
Copy Link

Problem

The job_events table queries on (job_id, timestamp) but only has individual indexes, causing poor query performance on event streaming.

Location

apps/api/prisma/migrations/20260201205935_add_job_tracking/migration.sql:88-94

Current indexes:

  • job_events_job_id_idx on (job_id)
  • job_events_timestamp_idx on (timestamp)

Query Pattern

// apps/api/src/runner-jobs/runner-jobs.service.ts:269-275
const events = await this.prisma.jobEvent.findMany({
  where: {
    jobId: id,
    timestamp: { gt: lastEventTime },
  },
  orderBy: { timestamp: "asc" },
});

Impact

  • Slow SSE streaming with many events
  • Increased database load
  • Poor performance with concurrent streams
  • Sequential scans instead of index-only scans

Acceptance Criteria

  • Create composite index on (job_id, timestamp)
  • Generate Prisma migration
  • Test query performance improvement
  • Verify index is used with EXPLAIN ANALYZE
  • Update schema.prisma
  • Document index rationale

Implementation

-- New migration
CREATE INDEX "job_events_job_id_timestamp_idx" 
ON "job_events"("job_id", "timestamp");

// schema.prisma
model JobEvent {
  // ...
  @@index([jobId, timestamp], name: "job_events_job_id_timestamp_idx")
}

Testing

EXPLAIN ANALYZE
SELECT * FROM job_events 
WHERE job_id = 'xxx' AND timestamp > NOW() - INTERVAL '1 hour'
ORDER BY timestamp ASC;

Should show index scan instead of sequential scan.

References

M4.2-Infrastructure verification report (2026-02-02)
Code review agent ID: a27cfbd

## Problem The job_events table queries on (job_id, timestamp) but only has individual indexes, causing poor query performance on event streaming. ## Location apps/api/prisma/migrations/20260201205935_add_job_tracking/migration.sql:88-94 Current indexes: - job_events_job_id_idx on (job_id) - job_events_timestamp_idx on (timestamp) ## Query Pattern ```typescript // apps/api/src/runner-jobs/runner-jobs.service.ts:269-275 const events = await this.prisma.jobEvent.findMany({ where: { jobId: id, timestamp: { gt: lastEventTime }, }, orderBy: { timestamp: "asc" }, }); ``` ## Impact - Slow SSE streaming with many events - Increased database load - Poor performance with concurrent streams - Sequential scans instead of index-only scans ## Acceptance Criteria - [ ] Create composite index on (job_id, timestamp) - [ ] Generate Prisma migration - [ ] Test query performance improvement - [ ] Verify index is used with EXPLAIN ANALYZE - [ ] Update schema.prisma - [ ] Document index rationale ## Implementation ```sql -- New migration CREATE INDEX "job_events_job_id_timestamp_idx" ON "job_events"("job_id", "timestamp"); ``` ```prisma // schema.prisma model JobEvent { // ... @@index([jobId, timestamp], name: "job_events_job_id_timestamp_idx") } ``` ## Testing ```sql EXPLAIN ANALYZE SELECT * FROM job_events WHERE job_id = 'xxx' AND timestamp > NOW() - INTERVAL '1 hour' ORDER BY timestamp ASC; ``` Should show index scan instead of sequential scan. ## References M4.2-Infrastructure verification report (2026-02-02) Code review agent ID: a27cfbd
jason.woltje added this to the M4.2-Infrastructure (0.0.4) milestone 2026-02-02 17:24:46 +00:00
jason.woltje added the performancedatabasedatabasep1 labels 2026-02-02 17:24:46 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
ai
AI/LLM integration
 api
Backend/API work
 api
auth
Authentication
 database
database
Database/schema work
 devops
Docker/deployment
 docs
Documentation
 frontend
graph
knowledge-module
migration
Data migration
 orchestrator
Orchestrator service (apps/orchestrator/)
 p0
Critical priority
 p1
High priority
 p2
Medium priority
 p3
Low priority
 performance
Performance work
 phase-1
phase-2
phase-3
phase-4
phase-5
plugin
MoltBot plugin work
 search
security
Security-related
 setup
Initial setup
 testing
Testing/QA
 web
Frontend work
No Label database database p1 performance
Milestone
No items
No Milestone M4.2-Infrastructure (0.0.4)
Projects
Clear projects
No project
Assignees
Clear assignees
jason.woltje
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: mosaic/stack#189
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?