SQL Completion Engine

SQL Completion Engine

Zentro implements context-aware SQL autocomplete entirely in the frontend (frontend/src/lib/monaco/). It does not call the backend for suggestion data — all schema information is read from the in-memory schemaStore and resultStore caches.

---

File map

frontend/src/lib/monaco/
├── sqlCompletionProvider.ts      # Monaco CompletionItemProvider registration
├── sqlCompletionEngine.ts        # Public re-export barrel
├── sqlCompletionCore.ts          # High-level API (analyzeSqlText, buildSqlCompletionItems, …)
├── sqlCompletionAnalysis.ts      # Statement boundary detection + full SqlAnalysis builder
├── sqlCompletionTokenizer.ts     # Token stream from raw SQL text
├── sqlCompletionBuilder.ts       # Assemble CompletionItem[] from analysis + schema
├── sqlCompletionBuilderUtils.ts  # Range helpers, offset-at-position
├── sqlCompletionCatalog.ts       # findCatalogMatches — look up tables/views in schema tree
├── sqlCompletionSources.ts       # Extract FROM/JOIN sources + CTEs from statement
├── sqlCompletionIdentifiers.ts   # Alias normalization, quoteIdentifierForDriver, getSchemasForActiveDatabase
├── sqlCompletionJoinSuggestions.ts  # JOIN … ON snippet generation from FK relationships
├── sqlCompletionProjection.ts    # SELECT column projection (table.* expansion)
├── sqlCompletionTypes.ts         # All shared TypeScript types
├── sqlCompletionConstants.ts     # SQL keyword lists, clause keywords
├── sqlDriverFacade.ts            # Per-driver dialect hooks (quoting style, keywords)
├── sqlSuggestionSchemaContext.ts # Schema-aware suggestion ranking
├── sqlSuggestionTableDocs.ts     # resolveCompletionItem: lazy-load column docs
├── sqlFolding.ts                 # Code folding provider (BEGIN/END blocks)
├── sqlHighlight.ts               # Custom token colorization rules
├── sqlTableNavigation.ts         # Go-to-table action
└── resultFilterCompletion.ts     # Completion for the result-panel filter bar

---

Registration

registerContextAwareSQLCompletion is called once after Monaco is initialized (inside features/editor/MonacoEditor.tsx). It registers a CompletionItemProvider for the sql language with a set of trigger characters:

triggerCharacters: ['.', ' ', ',', '(', 'j', 'J', 'o', 'O', 'i', 'I', 'n', 'N']

The j/o/i/n characters trigger early so JOIN snippet suggestions appear as the user types the keyword.

Any time the connection changes (new schema), the old provider is disposed and a new one is registered with the updated schema.

---

Analysis pipeline

When the user triggers completion, provideCompletionItems runs this pipeline:

model.getValue()           → full editor text
cursorOffset               → position in chars from start

analyzeSqlText(text, offset)
  │
  ├─ findStatementStartOffset()  → locate the ; before cursor
  ├─ findStatementEndOffset()    → locate the ; after cursor
  ├─ tokenizeSql(statementText)  → SqlToken[] + finalDepth (paren depth)
  ├─ detectClause(tokens, depth) → "select" | "from" | "join" | "where" | "on" | …
  ├─ isInsideLineCommentOrString() → bail early if true
  ├─ afterDotMatch               → is cursor after a dot? (table.column completion)
  ├─ extractCtes(statementSpan)  → WITH cte_name AS (…) → CTE map
  └─ extractSources(span, tokens) → FROM/JOIN table references + aliases

returns SqlAnalysis {
    clause, isInCommentOrString, afterDot, dotIdentifier,
    sources, ctes, cursorDepth, statementText, ...
}

---

Tokenizer

tokenizeSql in sqlCompletionTokenizer.ts makes a single linear pass over the SQL text character by character. It tracks:

• String literals ('...') and line comments (--) and block comments (/* */) — tokens inside these are suppressed
• Parenthesis depth — used to scope subquery clause detection
• Keyword tokens (normalized to uppercase) with their position offsets
• Punctuation tokens: (, ), ,, ;, .

detectClause walks the token array from the cursor backward (skipping nested subqueries at deeper depth) to find the nearest clause keyword at the cursor's paren depth.

---

Schema lookup

getSchemasForActiveDatabase reads directly from useSchemaStore.getState().trees keyed by "ProfileName:DBName" — no async call. The schema tree is populated when the user connects (via the schema:loaded Wails event).

const schemas = getSchemasForActiveDatabase(
    useSchemaStore.getState().trees,
    profile.name,   // profileKey
    profile.db_name // dbName
);
// schemas: SchemaNode[] = [{ Name: "public", Tables: ["users", "orders"], Views: [...], ... }]

Column definitions are fetched lazily — checkAndFetchColumns in schemaStore deduplicates in-flight requests and caches results for 5 minutes (TTL: COLUMN_CACHE_TTL_MS).

---

Completion item assembly

buildSqlCompletionItems in sqlCompletionBuilder.ts decides what to suggest based on SqlAnalysis.clause and afterDot:

Condition	Suggestions
afterDot === true, dotIdentifier matches an alias or table name	Columns of that table
afterDot === true, dotIdentifier matches a schema name	Tables in that schema
clause === "select"	Columns from all FROM/JOIN sources + functions + keywords
clause === "from" or clause === "join"	Tables, views, CTEs from all schemas
clause === "where" or clause === "on"	Columns from all sources + operators
Typing jo, joi, join in FROM/JOIN context	JOIN snippet suggestions (see below)
Any clause	SQL keywords, functions, templates from templateStore

---

JOIN suggestion logic

sqlCompletionJoinSuggestions.ts activates when the cursor is in a from or join clause and the current word starts with j or matches a JOIN prefix.

For each table already in the FROM/JOIN list (analysis.sources), it:

1. Calls fetchRelationships(schemaName, tableName) — returns foreign key pairs from schemaStore (cached, async).
2. Groups FK column pairs by (constraintName, joinTable) to handle composite keys.
3. Generates a Monaco CompletionItem of kind Snippet with an insertText like:

INNER JOIN public.orders o ON users.id = o.user_id

---

Column projection

sqlCompletionProjection.ts handles SELECT table.* expansion. When the cursor is in the SELECT clause after a tablealias.* pattern, it fetches all columns for that table and expands them into individual column references.

---

resolveCompletionItem — lazy documentation

When the user hovers or selects a table suggestion in the dropdown, Monaco calls resolveCompletionItem. sqlSuggestionTableDocs.ts intercepts this, fetches the column list from schemaStore, and injects a formatted Markdown documentation string listing column names and types — without blocking the initial suggestion list.

---

Driver dialect hooks

sqlDriverFacade.ts and sqlDriverFacade registration allow per-driver quoting and keyword customization. The active driver name comes from useConnectionStore.getState().activeProfile?.driver. Quoting styles:

Driver	Identifier quoting
postgres	"identifier"
mysql	`identifier`
sqlserver	[identifier]
sqlite	"identifier"