Orbit Optimus - SQL optimizer

Orbit Optimus, a SQL and database performance expert embedded in GitLab Duo. It analyses SQL queries, schema definitions in a codebase from the GitLab Orbit Knowledge Graph and source files of project

Comment

Inspiration

Every codebase accumulates SQL debt silently. Developers write queries without knowing which columns are indexed, add N+1 loops without realising it, fire synchronous bulk writes on hot paths, and miss caching opportunities all before a single line of code is reviewed.

The initial spark came from a deep dive into SQL internals. While exploring Use The Index, Luke a brilliant resource on how database indexes actually work under the hood I got completely hooked on the mechanics of B-tree indexes, composite index ordering, the leftmost prefix rule, and why a perfectly valid index can be completely invisible to the query planner depending on how you write your WHERE clause. That site changed how I think about SQL performance and made me realise how much invisible damage poorly indexed queries do in production systems every day.

From that point, I wanted to build something on the lines of a SQL performance optimisation tool something that could catch these issues automatically, before they reach production. When the Transcend Hackathon came along with GitLab Orbit and the Duo Agent Platform, it felt like the perfect platform to build and showcase exactly that. Orbit gives us the Knowledge Graph to traverse real codebases and real schemas. Duo gives us the agent infrastructure to trigger analysis on every MR without any deployment overhead.

Orbit Optimus is the result, catch database performance issues before they reach production, automatically, on every MR, across any tech stack.

What it does

Orbit Optimus is a GitLab Duo custom flow that acts as a SQL and database performance expert. Trigger it by mentioning @ai-orbit-optimus-gitlab-ai-hackathon on any MR or issue it analyses the changed files, cross-references the real database schema and indexes using the GitLab Orbit Knowledge Graph, and posts a single structured performance report as a comment.

It detects 10 performance rule categories across 8 programming languages:

Rule What it catches
1a — Missing Index Column used in WHERE/LIKE/JOIN with no index
1b — Composite Index Order Leftmost prefix rule violated — index unusable
1c — Covering Index Separate indexes where one composite would be faster
1d — B-tree vs LIKE B-tree index ineffective for suffix/contains LIKE
2 — N+1 Detection Database query inside a loop
3 — Two Queries → One Two queries on same table merged in application code
4 — Unparameterised SQL String interpolation inside SQL-executing calls
5 — Growing OR Chain Dynamic OR list that degrades query plan efficiency
6 — Missing Cache Aggregation query on hot path with no cache layer
7 — Sync Bulk Write Bulk INSERT/UPDATE/DELETE blocking the request thread

Supported stacks: Ruby/Rails, Python/Django/SQLAlchemy, Go/GORM, Java/JDBC/JPA, JavaScript/TypeScript (Knex, Prisma, pg), PHP/Laravel, C#/Entity Framework, raw SQL files.

Every finding includes the exact file path, line number, a code snippet, a one-sentence explanation of the performance impact, and a language specific copy pasteable fix.

How we built it

Orbit Optimus is a GitLab Duo custom flow — zero infrastructure, no server, no deployment. It lives entirely in the repository.

5-stage sequential pipeline: @mention on MR/Issue

  • Orchestrator — determines trigger type and scan scope
  • Schema Agent — reads db/structure.sql via Orbit → builds index map
  • Query Agent — reads changed files via read_file → detects SQL patterns
  • Analysis Agent — applies 10 perf rules against the real schema map
  • Report Agent — posts single structured markdown comment on the MR

Key technical decisions:

  • Orbit Knowledge Graph locates schema files (db/structure.sql, migrations, Prisma schemas) and cross-references real indexes against detected SQL patterns. Every index check is against the actual schema not a guess.

  • read_file for MR content — Orbit only indexes the default branch. MR files live on unmerged branches. The query agent always uses read_file in MR mode, which reads from the source branch context of the running flow.

  • Ruby 3.4 local tooling — A full Ruby library with OrbitClient,GitLabClient, SchemaAgent, QueryAgent, AnalysisAgent, ReportAgent, and a Pipeline orchestrator. Runnable locally via bundle exec rake optimus:analyze for fast iteration without triggering the full flow.

  • Skills as ground truth — skills/sql-pattern-detector/SKILL.md and skills/perf-advisor/SKILL.md define the detection rules and recommendation templates. The LLM loads these as context — it does not invent rules.

  • Language-aware recommendations — Every recommendation shows only the relevant language's fix. A Ruby codebase gets Rails migration syntax. A Python codebase gets Django/SQLAlchemy syntax. No noise from other stacks.

Challenges we ran into

Orbit indexes only the default branch. When a developer opens an MR, the changed files exist only on the source branch Orbit has never seen them. Early versions tried to find MR files via query_graph File nodes and got nothing, then fell back to scanning files from completely unrelated projects that happened to share similar names. The fix: always use read_file for MR mode, and use Orbit only for schema files (which live on main) and for chat mode directory scans.

The id field requirement in query_graph. Every node selector in the Orbit DSL requires both id (a variable name like "f", "b") and entity. Missing id causes a schema violation error. This caused several failed sessions before we identified it and added explicit working examples to every agent prompt.

Multi-stage flow validation. The GitLab Duo flow schema is strict — unit_primitives is required on every prompt, inter-component references must use context:<name>.final_answer, and every input variable must have a matching {{placeholder}} in the prompt template. Each of these caused validation errors that had to be debugged iteratively.

False positives in SQL detection. Initial regex patterns matched and in log messages, raise error strings with interpolation, and Ruby regex pattern definitions inside the SqlDetector class itself. Each required tightening the detection logic — requiring interpolation to be inside a SQL-executing method call, and explicitly skipping lib/orbit_optimus/ from scans.

Cross-project Orbit noise. Without a reliable project filter, Orbit's global index returns files from any indexed project. Auto-detection of source directories had to be scoped using the project path's last segment as a prefix to avoid scanning unrelated codebases.

Accomplishments that we're proud of

Zero infrastructure. Orbit Optimus runs entirely as a GitLab Duo custom flow — no server, no Docker, no webhook, no deployment. Publishable to the AI Catalog and works for any team that enables it with one click.

Real schema cross-referencing. The schema agent reads the actual db/structure.sqland builds a real index map. When it flagsweb_url` as missing an index, it checked the real schema and confirmed no index exists not a guess.

Composite index analysis. Rule 1b detects when a composite index exists but the leftmost column is not in the query filter — making the index completely unusable. This is a subtle and common performance trap that most tools miss entirely.

Multi-stack in one agent. The same flow analyses Ruby, Python, Go, Java, JavaScript, PHP, and C# — with language-specific detection patterns and language-specific recommendations.

Local Ruby 3.4 tooling. The full pipeline is also runnable locally via bundle exec rake optimus:analyze giving developers fast feedback without needing to trigger the full GitLab flow. Uses Data.define for immutable value objects, frozen string literals, and pattern matching throughout.

What we learned

Orbit is a graph, not a project filter. The Knowledge Graph is global. Scoping queries requires path prefix filters derived from the project's own file structure, not a project ID filter.

read_file is the right tool for MR analysis. The flow executor runs with the source branch context, so read_file can access files on unmerged branches. Orbit cannot. For pre-merge analysis — which is the entire point read_file is always the correct approach.

Prompt engineering for structured pipelines. In a multi-stage flow, each agent's output becomes the next agent's input. The LLM needs very explicit instructions about output format. Working examples in every prompt are essential — if the schema agent outputs malformed JSON, the analysis agent has nothing to work with.

False positives destroy trust. A tool that flags log messages and error strings as SQL performance issues loses developer trust immediately. Every detection pattern needs to be grounded in calling context not just string content.

The AI Catalog changes the distribution model. Building as a custom flow means the agent is publishable, discoverable, and enableable by any team with one click — no installation, no configuration, no ops burden.

What's next for Orbit Optimus

Automatic migration generation. When a missing index is detected, generate the complete Rails migration file (or Django migration, or Flyway SQL) as a suggested commit not just a recommendation string.

EXPLAIN ANALYZE integration. When Orbit's CALLS edges are indexed for a project, trace the full call chain from a SQL query back to the controller action that triggers it and estimate query cost based on table size heuristics.

Trend tracking. A microservice layer that persists findings across MRs, tracks which performance issues are recurring, and surfaces a SQL debt dashboard showing which tables and query patterns are the most problematic over time.

IDE integration. Surface findings inline in VS Code and JetBrains via the GitLab Language Server highlight the problematic line with the severity emoji and recommendation before the developer even opens an MR.

Support for more schema formats. Currently handles Rails db/structure.sql and schema.rb, Prisma, and Django models. Next: Flyway XML, Liquibase changelogs, TypeORM entities, and Hibernate hbm.xml.

Async batching advisor. Extend Rule 7 to suggest specific Sidekiq job classes, Celery task signatures, or Go worker queue patterns based on the detected ORM and framework — not just "move this to a background job."

Built With

Share this project:

Updates