Forem: Vasiliy Zakharchenko

AI Magic in Atlassian Forge: Local Semantic Search with Forge SQL

Vasiliy Zakharchenko — Fri, 10 Apr 2026 16:53:03 +0000

Introduction

Did you know that Forge SQL can be used for semantic search?

Since Forge SQL is backed by TiDB, and TiDB supports Vector Search, it is now possible to store embeddings in the database and query them by semantic similarity. That means your app can search by meaning, not only by exact keyword matches. PingCAP highlights semantic search, recommendation systems, and Retrieval-Augmented Generation (RAG) as key use cases for this capability.

This is a very practical AI pattern. Traditional search works best when users type the same words that already exist in the stored content. Semantic search works differently: both the documents and the user query are converted into embeddings, and the database returns the closest matches based on vector distance. Because of that, a query can still find the right result even when the wording is completely different.

This is also one of the core ideas behind RAG. Before an LLM generates an answer, the system first retrieves the most relevant documents and passes them as context. Better retrieval usually means better answers.

In this article, I will show how to build this pattern inside an Atlassian Forge app while still keeping the architecture aligned with Runs on Atlassian. In this example, embeddings are generated locally in the Custom UI frontend, stored in Forge SQL using Forge SQL ORM, and queried directly with vector search. No external AI API is required for the semantic search flow. Runs on Atlassian eligibility depends on meeting platform requirements, including around egress, so this kind of local approach can be especially useful for Forge developers.

The article has two parts:

How embeddings are generated in Custom UI and processed in the Forge backend
A short demonstration of the app in action, with a link to the full video walkthrough

Building the Local Semantic Search Flow

To build semantic search inside a Forge app, we need a way to generate embeddings locally in the browser, send them to the backend, and then use Forge SQL to search by vector similarity.

In this example, the flow consists of four steps:

choosing a lightweight embedding model
configuring the frontend to run the model locally
generating vectors from user input
sending those vectors to the backend and querying Forge SQL

1. Choosing the embedding model

The first step was choosing an embedding model that could realistically run inside Forge Custom UI.

For this example, I used Xenova/all-MiniLM-L6-v2.

I chose it for a very practical reason: it is lightweight and fits much better into the kind of size and runtime constraints that Forge developers usually care about.

Since the goal of this example is to keep the semantic search flow local, the model needs to run directly in the browser without relying on an external AI API. A smaller model is a much better fit for that approach.

From the full model repository, I only needed a small set of files:

config.json
special_tokens_map.json
tokenizer.json
tokenizer_config.json
onnx/model_quantized.onnx

In short, each file has a specific role:

config.json contains the main model configuration
special_tokens_map.json defines special tokens used by the tokenizer
tokenizer.json contains the tokenizer itself, including how text is split into tokens
tokenizer_config.json contains tokenizer settings and metadata
onnx/model_quantized.onnx is the actual neural network model used for inference in the browser

The most important part here is model_quantized.onnx. This is the file that performs embedding generation. It is a quantized ONNX model, which means it is optimized to be smaller and more practical for client-side execution.

The tokenizer files are also essential, because the model does not work directly with raw text. First, the input text must be converted into tokens in exactly the same way the model expects. Only after that can the ONNX model generate the embedding vector.

So in practice, the setup is split into two parts:

the tokenizer files prepare the text input
the ONNX model converts that tokenized input into an embedding vector

This is enough to run local embedding generation in the frontend and a good fit for a Forge app that aims to stay simple, portable, and aligned with Runs on Atlassian badge.

2. Configuring the frontend to run the model locally

To run semantic search fully inside the Forge app, the frontend first needs to load the embedding model locally in the browser.

The main dependency for this is:

npm i @huggingface/transformers -S

After that, the application needs two groups of files:

the model files
the ONNX runtime WebAssembly files

2.1 Adding the model files

The model itself, together with its tokenizer and configuration files, can be placed into the frontend public folder. In my case, I used this structure:

public/
   models/
      all-MiniLM-L6-v2/
         config.json
         special_tokens_map.json
         tokenizer.json
         tokenizer_config.json
         onnx/
           model_quantized.onnx

This allows the frontend to load the model directly from the app’s own static assets.

2.2 Adding the ONNX WebAssembly runtime files

In addition to the model, the browser also needs the ONNX runtime files used to execute the model locally.

These files can be copied from node_modules/onnxruntime-web/dist/ into public/wasm:

ort-wasm-simd-threaded.mjs
ort-wasm-simd-threaded.wasm
ort-wasm-simd-threaded.asyncify.mjs
ort-wasm-simd-threaded.asyncify.wasm

So the final structure looks like this:

public/
   models/
      all-MiniLM-L6-v2/
         onnx/
             model_quantized.onnx
         config.json
         special_tokens_map.json
         tokenizer.json
         tokenizer_config.json
   wasm/
      ort-wasm-simd-threaded.mjs
      ort-wasm-simd-threaded.wasm
      ort-wasm-simd-threaded.asyncify.mjs
      ort-wasm-simd-threaded.asyncify.wasm

At this point, the frontend has everything it needs to load the model and run inference locally.

2.3 Initializing the model in the frontend

The next step is to initialize the model when the frontend starts. This only needs to happen once. After the first load, the browser can reuse cached assets, which makes the next startup much faster.

Here is the code I used:

/// <reference types="vite/client" />
import { env, FeatureExtractionPipeline, pipeline, ProgressInfo } from "@huggingface/transformers";

env.localModelPath = `./models/`;

env.allowLocalModels = true;
env.allowRemoteModels = false;
env.useBrowserCache = false;
env.useWasmCache = true;
const isDevMode = import.meta.env.DEV;
env.backends.onnx.wasm!.wasmPaths = isDevMode ? `${window.location.origin}/wasm/` : `../wasm/`;

const MODEL_NAME = `all-MiniLM-L6-v2`;

export interface VectorBuilder {
  getVector(text: string): Promise<number[]>;
}

interface MiniLLM {
  init(progress: (progressInfo: ProgressInfo) => void): Promise<VectorBuilder>;
}

class VectorBuilderImpl implements VectorBuilder {
  private readonly extractor: FeatureExtractionPipeline;

  constructor(extractor: FeatureExtractionPipeline) {
    this.extractor = extractor;
  }

  async getVector(text: string): Promise<number[]> {
    const output = await this.extractor(text, {
      pooling: "mean",
      normalize: true,
    });
    return Array.from(output.data) as number[];
  }
}

class MiniLLMImpl implements MiniLLM {
  async init(progress: (progressInfo: ProgressInfo) => void): Promise<VectorBuilder> {
    const extractor = await pipeline("feature-extraction", MODEL_NAME, {
      progress_callback: progress,
    });
    return new VectorBuilderImpl(extractor);
  }
}

export const miniLLM: MiniLLM = new MiniLLMImpl();

What this configuration does

There are a few important details here.

env.localModelPath = './models/' tells transformers.js where to find the model files inside the frontend assets.

env.allowLocalModels = true and env.allowRemoteModels = false make sure that the application only uses local model files and does not try to download anything from an external model registry.

env.useWasmCache = true allows the WebAssembly runtime files to be cached, which helps reduce repeated loading costs.

The following line is especially important:

const isDevMode = import.meta.env.DEV;

env.backends.onnx.wasm!.wasmPaths = isDevMode ? `${window.location.origin}/wasm/` : `../wasm/`;

I added isDevMode because the WebAssembly path needs to be resolved differently when running through forge tunnel. Without that adjustment, the runtime files might not load correctly in local development mode.

Tracking model loading progress

The progress callback is used to show model loading progress in the UI, for example through a spinner or progress indicator.

progress: (progressInfo: ProgressInfo) => void

This is useful because the model is loaded into the frontend on startup, and that can take a little time on the first run.

An important detail here is that these files are loaded from the app’s own host, not from an external service. So this is just normal asset loading from the frontend itself, not an external AI API call.

Result

After this setup, the frontend is ready to initialize the embedding model locally and generate vectors directly in the browser.

In the next step, we can use this initialized pipeline to convert document text and search queries into embedding vectors.

3. Generating the vector

Once the model is available in the frontend, the application can convert plain text into an embedding vector.

This happens in two places:

when a user adds a document
when a user enters a search query

In both cases, the frontend takes the input text, runs it through the embedding model, and produces a fixed-size numeric vector. That vector is the semantic representation of the text. Instead of relying on exact words, the application can now compare meanings by comparing vectors.

This is the core idea behind semantic search: text is first transformed into embeddings, and only then used for similarity search.

In practice, generating the vector is very simple:

const vectorBuilder = await miniLLM.init(onProgress);
const vector = await vectorBuilder.getVector(text);

Here, miniLLM.init(onProgress) initializes the local embedding pipeline, and getVector(text) converts the input text into a numeric vector.

The same approach is used both for storing documents and for searching. When a document is added, the frontend generates an embedding for the document text before sending it to the backend. When a user performs a search, the frontend generates an embedding for the query text and sends that vector to the search resolver.

So from this point on, the application no longer works with plain text only. It works with semantic representations of that text, which is what makes similarity search possible.

4. Sending the vector to the backend and processing it in Forge SQL

Once the vector is generated in the frontend, it can be sent to a Forge resolver and stored in Forge SQL together with the original document.

In my example, the model is defined like this:

export const embeddedDocuments = mysqlTable(
  "embedded_documents",
  {
    id: int().autoincrement().notNull(),
    document: text().notNull(),
    title: varchar({ length: 255 }).notNull(),
    embedding: vectorTiDBType("embedding", { dimension: 384 }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.id], name: "id" })],
);

And the migration looks like this:

import { MigrationRunner } from "@forge/sql/out/migration";

export default (migrationRunner: MigrationRunner): MigrationRunner => {
  return migrationRunner.enqueue(
    "v1_MIGRATION0",
    "CREATE TABLE `embedded_documents` ( `id` int AUTO_INCREMENT NOT NULL, `document` text NOT NULL, `title` VARCHAR(255) NOT NULL, `embedding` VECTOR(384) NOT NULL, CONSTRAINT `id` PRIMARY KEY(`id`) )",
  );
};

The important part here is the vector dimension. It must exactly match the output dimension of the embedding model. In this case, all-MiniLM-L6-v2 produces vectors with dimension 384, so both the ORM model and the SQL migration use 384 as well.

After that, saving a document is straightforward. The frontend sends the document text, title, and generated embedding vector to the resolver, and the backend inserts them into the table:

resolver.define(
  "create",
  async (req: Request<{ data: InferInsertModel<typeof embeddedDocuments> }>): Promise<number> => {
    const payload = req.payload.data;
    const res = await forgeSQL.insert(embeddedDocuments).values([payload]);
    return res[0].insertId;
  },
);

At this point, the database stores not only the original text, but also its semantic representation as a vector.

The search flow works in a similar way. The frontend generates an embedding for the user’s query and sends that vector to the backend. Then the backend uses vecCosineDistance to compare the query vector with all stored document vectors and return the closest matches:

resolver.define(
  "search",
  async (
    req: Request<{ vector: number[] }>,
  ): Promise<{ id: number; title: string; document: string; distance: number }[]> => {
    const vector = req.payload.vector;
    const fieldAlias = sql.raw("distance");
    const distance = sql<number>`${vecCosineDistance(embeddedDocuments.embedding, vector)} as \`${fieldAlias}\``;
    return forgeSQL
      .select({
        id: embeddedDocuments.id,
        document: embeddedDocuments.document,
        title: embeddedDocuments.title,
        distance: distance,
      })
      .from(embeddedDocuments)
      .orderBy(asc(fieldAlias))
      .limit(formatLimitOffset(5));
  },
);

Under the hood, the generated SQL looks like this:

select
`id` as `a_id_id`,
`document` as `a_document_document`,
`title` as `a_title_title`,
 VEC_COSINE_DISTANCE(`embedded_documents`.`embedding`, VEC_FROM_TEXT(?)) as `distance`
from `embedded_documents`
order by distance asc
limit 5

This is the key step where semantic search actually happens. Instead of checking whether the query contains the same words as the document, Forge SQL compares vector similarity and returns the nearest results.

So the backend responsibility is very simple:

store document embeddings
receive the query embedding
calculate vector distance in Forge SQL
return the nearest documents sorted by similarity

At this point, the full semantic search pipeline is complete: the frontend generates embeddings locally, the backend stores them in Forge SQL, and search works by vector similarity instead of exact keyword matching - while still preserving Runs on Atlassian eligibility.

A short demonstration of the app in action

After the technical setup is complete, the easiest way to understand the value of semantic search is to see it working on a small set of example documents.

For this demo, I added five documents to the application. Each document has a title and a longer text description. When a document is submitted, the frontend generates its embedding locally and the backend stores both the original text and the vector in Forge SQL.

Adding sample documents

To populate the demo dataset, I added the following documents.

Title: Dogs

Document Text:

The Unwavering Bond: A Comprehensive Look at Domestic Dogs

Domestic dogs, scientifically known as *Canis lupus familiaris*, have shared a unique evolutionary journey with humans for over fifteen thousand years. Originally descended from ancient wolves, these resilient mammals have transitioned from wild predators to beloved family members, earning their reputation as "man's best friend." Their primary role has shifted significantly through history; while they were once valued strictly for their hunting prowess and guarding abilities, modern canines are now primarily cherished for their companionship and emotional support.

Physically, dogs exhibit an incredible diversity in size, coat texture, and temperament. From the tiny Chihuahua to the massive Great Dane, every breed possesses specific traits developed through centuries of selective breeding. Beyond their physical attributes, dogs are highly intelligent social animals capable of understanding human emotions and complex commands. They communicate through a sophisticated range of vocalizations, including barks and whines, alongside subtle body language like tail wagging or ear positioning.

Furthermore, the working capabilities of dogs remain vital to society today. Specialized service animals assist individuals with visual impairments, while brave search-and-rescue teams navigate treacherous terrain to save lives. Their acute sense of smell, which is thousands of times more sensitive than a human's, allows them to detect specific scents with remarkable precision. Whether they are performing a high-stakes job or simply waiting patiently for their owner to return home, dogs continue to demonstrate an unparalleled level of loyalty, devotion, and unconditional love that enriches human lives across every culture.

Title: Tree

Document Text:

The Silent Giants: Understanding the Life of Trees

Trees are the fundamental pillars of our planet's terrestrial ecosystems, serving as complex biological organisms that sustain life on Earth. As perennial plants with an elongated stem or trunk, they are uniquely characterized by their woody structure and extensive root systems. Through the remarkable process of photosynthesis, trees convert sunlight, water, and carbon dioxide into life-sustaining oxygen and glucose. This chemical transformation not only supports the tree's own growth but also regulates the global atmospheric balance, making forests the "lungs of our planet."

The internal anatomy of a tree is a marvel of natural engineering. Beneath the protective outer bark lies the cambium layer, which facilitates the growth of new cells, and the xylem, a sophisticated vascular system that transports nutrients from the earth to the highest leaves. Throughout the seasons, deciduous trees undergo dramatic transformations, shedding their foliage in autumn to conserve energy before the harsh winter months. In contrast, evergreens maintain their needles year-round, showcasing the diverse evolutionary strategies plants use to survive in varying climates.

Beyond their biological functions, trees provide critical habitats for countless species of insects, birds, and fungi. They stabilize the soil against erosion, offer cooling shade during intense heat, and contribute to the water cycle by releasing moisture through transpiration. For humanity, trees have been an essential resource for millennia, providing timber for construction, fruit for sustenance, and a profound sense of tranquility. Protecting these ancient, towering organisms is vital for maintaining biodiversity and ensuring the environmental health of future generations.

Title: Fish

Document Text:

The Aquatic Realm: Exploring the World of Fish

Fish represent a diverse group of craniate organisms that have mastered life in the world's oceans, rivers, and lakes for over five hundred million years. As cold-blooded vertebrates, they are perfectly adapted to their underwater environments, utilizing specialized organs called gills to extract life-sustaining oxygen directly from the water. Unlike land-dwelling mammals, fish possess streamlined bodies covered in protective scales and use various fins for propulsion, stability, and precise maneuvering through dense aquatic currents.

The biological variety among fish is staggering, ranging from the tiny, colorful inhabitants of tropical coral reefs to the colossal whale sharks that roam the open sea. Many species have evolved incredible sensory capabilities, such as the lateral line system, which detects minute vibrations and pressure changes in the surrounding water. This "sixth sense" allows them to navigate in complete darkness, avoid predators, and hunt with remarkable accuracy. Additionally, some fish exhibit complex social behaviors, forming massive schools that move in perfect unison to confuse attackers or increase foraging efficiency.

Reproduction and survival strategies in the aquatic world are equally fascinating. While some fish lay thousands of delicate eggs in hidden nests, others, like certain sharks, give birth to fully formed live young. Their role in the global food web is indispensable, as they serve as a primary protein source for billions of humans and countless other predators. From the deepest abyssal trenches to the shallowest mountain streams, fish continue to thrive as a testament to evolutionary resilience, playing a vital role in maintaining the delicate ecological balance of our blue planet's hydrosphere.

Title: Cat

Document Text:

The Enigmatic Grace: Understanding the Domestic Cat

The domestic cat, or *Felis catus*, is a small carnivorous mammal celebrated for its agility, independent spirit, and mysterious demeanor. Having lived alongside humans for nearly ten thousand years, cats were originally revered in ancient societies—most notably in Egypt—for their ability to protect grain stores from rodents. Unlike dogs, which were bred for cooperation, cats have largely retained their solitary hunting instincts, making them fascinatingly self-sufficient companions in modern households.

Physically, cats are marvels of biological engineering. Their skeletons are incredibly flexible, allowing them to squeeze through tight spaces and always land on their feet thanks to a highly developed righting reflex. They possess extraordinary sensory perceptions; their night vision is far superior to that of humans, and their retractable claws allow for silent stalking and efficient climbing. A cat’s communication is equally nuanced, ranging from the gentle vibration of a purr, which often signals contentment or self-healing, to the sharp hiss used for territorial defense.

Behaviorally, cats are known for their fastidious grooming habits and complex social signals. While they are often labeled as aloof, many cats form deep emotional bonds with their owners, expressing affection through "kneading" or gentle head-butts. Their predatory prowess remains intact, even in indoor environments, where they often treat toys as "prey" to satisfy their instinctive need to hunt. As one of the world's most popular pets, cats continue to captivate us with their blend of wild heritage and domestic charm, offering a quiet, observant presence that has inspired artists and thinkers for millennia.

Title: Mice

Document Text:

The Smallest Survivors: The World of Mice

Mice are small rodents belonging to the family Muridae, known for their incredible adaptability and presence in nearly every corner of the globe. Characterized by their pointed snouts, large rounded ears, and long, thin tails, these tiny mammals have successfully thrived alongside human civilizations for thousands of years. While often viewed as mere pests in granaries, mice are highly complex creatures with sophisticated social structures and remarkable survival instincts that allow them to inhabit diverse environments ranging from dense forests to urban households.

Biologically, mice are built for stealth and speed. Their whiskers, or vibrissae, are highly sensitive tactile organs that allow them to navigate in total darkness by sensing air currents and physical obstacles. They possess an extraordinary reproductive rate, a necessary evolutionary strategy to counter their role as a primary food source for numerous predators, including owls, snakes, and felines. Despite their small stature, mice are surprisingly intelligent; they exhibit problem-solving abilities and can communicate with one another using ultrasonic vocalizations that are completely inaudible to the human ear.

In the realm of science and history, the mouse has played an indispensable role. Due to their genetic similarity to humans, mice are the most commonly studied model organisms in medical research, contributing to countless breakthroughs in genetics and pharmacology. Whether they are scurrying through a field or living in a controlled laboratory setting, mice demonstrate a level of resilience and biological efficiency that far outweighs their size. Their ability to find food in the most difficult conditions and their cautious, nocturnal nature continue to make them one of the most successful mammalian species on Earth.

Trying semantic search queries

Once the dataset is ready, the next step is to test how the application behaves with natural-language queries.

The interesting part here is that the queries do not need to contain the exact title of the document. In fact, they work best when they describe the concept in a more human way.

Example 1:

I am looking for information about large organisms that live for hundreds of years, have a woody trunk, and use their leaves to turn sunlight into energy while providing shade and stabilized soil for the ecosystem.

Result: Tree (55.66%)

This query contains a lot of extra words such as “I am looking for information about”, but the important semantic signals are still there: woody trunk, sunlight into energy, shade, and stabilized soil. A keyword search might be less reliable here, but semantic search can still understand the meaning and rank Tree as the closest match.

Example 2:

I am looking for information about small domestic predators that were respected in ancient history, are very independent, have excellent night vision, and can land on their feet when they jump from high places.

Result: Cat (39.90%)

This example is useful because the query never says the word cat. Instead, it describes distinctive traits: ancient history, independence, night vision, and landing on their feet. A traditional search for the exact word would fail here, but semantic search can still connect the description to the Cat document.

Example 3:

Tell me about tiny mammals that are often found in houses or fields, which scientists use in laboratories to study genetics and develop new medicines because they breed very fast and are biologically similar to humans.

Result: Mice

This is a good example of a long user-style query. The user might not remember the exact word mice, but they remember the context: small mammals, homes and fields, laboratory research, genetics, and fast reproduction. That is exactly the kind of scenario where semantic search becomes much more useful than plain keyword matching.

What this demo shows

This small demo shows the practical difference between keyword search and semantic search.

The application is not matching documents only by title or exact words. Instead, it compares the semantic meaning of the query vector against the stored document vectors and returns the nearest results. That is why the search can still work even when the user describes the idea indirectly or uses very different wording.

If you want to see the complete flow in action, including model loading, document creation, and semantic search in the UI, you can watch the full video walkthrough here:

Conclusion

This example shows that semantic search can be implemented directly in Atlassian Forge by combining local embeddings in Custom UI with vector search in Forge SQL.

That already gives you a useful AI-powered retrieval flow: the app searches by meaning, not just by exact words, while still preserving Runs on Atlassian eligibility.

It is also a natural foundation for RAG.

If the top matching documents returned by Forge SQL are passed into the Forge LLM API as context, the app can move from semantic search to full Retrieval-Augmented Generation. The retrieval step stays in Forge SQL, and the generation step can be handled by Atlassian-hosted LLMs.

So this example is not only a semantic search demo. It is also a practical starting point for building a fully Forge-native RAG application.

If you want to explore the full source code, you can find the example application here: Example application on GitHub

Virtual Materialized Views for Atlassian Forge SQL: A Two-Level Caching Approach in forge-sql-orm

Vasiliy Zakharchenko — Mon, 16 Mar 2026 16:29:16 +0000

If you build non-trivial apps on Atlassian Forge SQL, sooner or later you run into the same limitation: TiDB in Forge does not support materialized views.

This becomes painful as soon as your application needs read-heavy features built on complex joins, aggregations, or denormalized relational data.

In practice, this usually leaves you with two unattractive options: recompute expensive queries on every resolver call and risk hitting strict Forge SQL limits (like the 5s connection timeout or 16MB memory usage), or manually maintain precomputed tables and synchronization logic, which quickly turns into brittle application code.

In forge-sql-orm, I’ve implemented a two-level caching approach that acts as a virtual materialized view at the application layer.

The first level is an invocation-scoped in-memory cache that eliminates duplicate queries during a single resolver execution.
The second level is a persistent cross-invocation cache backed by @forge/kvs, allowing expensive results to be reused across requests.

The library provides cache-aware write operations and automatic invalidation of affected queries after data changes. This means you can effectively “materialize” the results of expensive read queries and keep your relational features responsive without building a manual synchronization subsystem.

In this article, I’ll show how this pattern works, why a single invocation cache layer is usually not enough for Forge apps, and how this “virtual materialized“ view approach helps reduce repeated expensive SQL execution and keeps cacheable read paths fast within Forge platform limits.

The “Why”: Understanding the 5-Second Timeout and the 16MB Memory Limit

When you start building simple apps on Forge SQL, TiDB feels surprisingly capable. Basic queries are fast, relational modeling is straightforward, and everything works well on small datasets. But as the application grows, the query layer changes too. Instead of simple lookups, you start building read-heavy features based on joins, aggregations, sorting, and denormalized relational views.

That is usually the point where you hit the real platform boundaries.

The 5-Second Timeout

Forge SQL enforces a strict 5-second connection timeout for SELECT queries. Even if the Forge function itself still has time left to run (up to 25s), the database query can be terminated before the resolver finishes.

This creates a common and dangerous pattern: a query that performs well on a small development dataset can become unreliable or fail completely on a large production tenant. Managing this is difficult for two main reasons:

Small datasets hide the problem: A query that looks acceptable during development can cross the timeout boundary once the tables contain hundreds of thousands or millions of rows.
Tenant size is unpredictable: Forge apps run in a multi-tenant environment where different customers have radically different data volumes. You cannot reliably predict query behavior from your local setup alone.

The 16MB Memory Limit

Execution time is not the only constraint. Forge SQL also enforces a strict per-query memory limit of 16MB.

This means a query can still fail even when it is not particularly slow. Complex joins or memory-heavy execution strategies, such as HashJoin, may exceed the memory budget and get cancelled with an “Exceeding the allowed memory limit” error. In practice, this often shows up only on enterprise-scale tenants, making these failures especially painful to reproduce locally.

Observability, Optimization, and the Caching Alternative

Once you hit these limits, you need a clear strategy.

Observability: You need to know which queries are slow or failing. I explored how to detect and surface these cases in my work on Practical SQL Observability for Forge Apps.
Optimization: Some queries can be improved with better indexing or rewriting logic using EXPLAIN ANALYZE. I covered this in detail in Optimizing Forge SQL on a 600K database with TiDB EXPLAIN.
The Caching Alternative: However, optimization is not always enough. Some queries are expensive by nature due to business requirements. As discussed in my AtlasCamp 2026 session, Making Forge SQL observable, caching becomes an architectural necessity.

The “Virtual Materialized View” Approach

The most practical way to bypass these limits is to mimic the behavior of a Materialized View at the application layer. Since TiDB in Forge does not support native materialized views, forge-sql-orm implements a Virtual Materialized View pattern using two-level caching:

Materialized Read Results: Expensive query results are stored in @forge/kvs, creating a persistent snapshot that survives across invocations.
Transparent Integration: Methods like selectCacheable() and executeCacheable() allow caching to happen behind the scenes, with cache keys derived from the SQL and parameters.
Automatic Invalidation: The ORM tracks affected tables and automatically invalidates cached entries after INSERT, UPDATE, or DELETE operations.
Batch-based Eviction: To stay within Forge platform limits, invalidation is performed in controlled batches (up to 25 per transaction), maintaining consistency without manual synchronization code.

The result is a practical alternative to native materialized views: expensive relational queries are no longer recomputed on every request, keeping your application responsive within strict platform limits.

Deep Dive: The Two-Level Caching Architecture

To improve performance while staying within Forge platform limits, forge-sql-orm uses a hybrid two-level caching strategy. The idea is simple: combine the low-latency benefits of in-memory caching inside a single invocation with the cross-invocation persistence of the Forge Key-Value Store (KVS).

Level 1: Local Invocation Cache

The first cache layer lives entirely in memory and is scoped to a single resolver invocation. It is implemented using Node.js AsyncLocalStorage.

This layer solves a common problem in Forge applications: the same data may be requested multiple times during one execution path. A resolver might call helper functions, perform permission checks, or fetch related entities - all of which can trigger repeated reads for the same query.

With L1 cache, the database is queried only once for a given cacheable read during that invocation. Any subsequent access returns the result directly from memory in <1ms, avoiding additional SQL execution and unnecessary work. Because it is invocation-scoped, it is automatically discarded when the resolver finishes, ensuring no state is carried across unrelated requests.

Level 2: Persistent Cross-Invocation Cache

The second layer extends caching beyond a single invocation by storing results in @forge/kvs.

Unlike in-memory state, KVS-backed entries survive across cold starts and new lambda initializations until their TTL expires or they are explicitly invalidated. This makes L2 caching ideal for expensive read results that are stable enough to be reused across requests. Instead of recalculating a heavy join for every user, the ORM reuses the stored result.

To make this deterministic, the ORM generates a unique cache key from a hash of the SQL query text and its parameters. KVS is significantly faster than Forge SQL; while a SQL query might take 500ms (including latency), KVS typically returns results in 40–50ms.

Why This Architecture Fits Forge Well

This two-level design addresses two distinct inefficiencies:

Reduces repeated SQL work within an invocation: It stops duplicate reads triggered by nested service calls or helper functions.
Reduces repeated computation across invocations: Since local process memory is not persistent in serverless environments, @forge/kvs provides the necessary persistence across execution boundaries.

This architecture creates a practical optimization path. While KVS doesn’t “solve” a 5-second SQL timeout for a query that is inherently too slow to run even once, it allows you to decompose complex queries into stable, reusable parts. By caching these intermediate or final results, you significantly reduce pressure on Forge SQL limits.

Finally, reusing cached results also reduces repeated processing work in the application layer.

Implementing the Virtual Materialized View Pattern

To make the two-level caching model practical, forge-sql-orm provides a set of cache-aware query methods that let you treat expensive relational reads as reusable, cacheable views. Instead of manually working with @forge/kvs, you use an API that stays close to regular Drizzle ORM query building while adding transparent caching support for joins, distinct queries, and raw SQL.

The library includes several cache-aware methods:

selectCacheable() - the main method for custom selections, joins, and derived read models.
selectCacheableFrom() - shorthand for selecting and caching all columns with automatic field aliasing.
selectDistinctCacheable() and selectDistinctCacheableFrom() - variants for queries that require distinct results.
executeCacheable() - caching support for raw SQL queries.

The Core Method: selectCacheable()

The most flexible entry point is selectCacheable(). It allows you to define exactly which columns should be included in the cached result and how the underlying relational query should be composed.

In practice, this method handles the full read lifecycle:

Check the invocation-scoped memory cache (L1).
Check the persistent KVS cache (L2).
Fall back to Forge SQL if no cached result exists.

In the following example, a joined query over users and orders is treated as a virtual materialized view. To make the behavior easier to observe, the query includes SLEEP(0.5) to simulate an expensive read path.

const SQL_CACHE_QUERY = FORGE_SQL_ORM
.selectCacheable({
  userId: demoUsers.id,
  userName: demoUsers.name,
  product: demoOrders.product,
  productId: demoOrders.id,
  sleep: sql`SLEEP(0.5)`,
}).from(demoUsers).leftJoin(demoOrders, eq(demoOrders.userId, demoUsers.id));

What Happens at Runtime

When this cacheable query is executed, the ORM performs a deterministic sequence of steps:

Cache key generation: A unique key is derived from the generated SQL and its bound parameters.
L1 cache lookup: If the same query was already executed during the current resolver invocation, the result is returned directly from memory in <1ms.
L2 cache lookup: If no in-memory entry exists, the ORM checks @forge/kvs for a persisted result. KVS typically responds in 40–50ms, which is much faster than a full SQL execution.
Database execution on cache miss: If both layers miss, the ORM executes the SQL, stores the result in the cache layers, and reuses it for subsequent reads.

Using Virtual Materialized Views in Subsequent Queries

Once you have "materialized" your complex query results into a cached array, you can use that data to drive further relational queries with high efficiency. A common pattern is to use the IDs or keys from your materialized view to filter a primary table, essentially performing a "join" between a cached snapshot and live SQL data.

Because the materialized result is already in memory (L1) or quickly retrieved from KVS (L2), you can use it to build highly targeted WHERE ... IN (...) clauses. This avoids re-executing the original complex logic while ensuring you only fetch the specific records relevant to your materialized view.

Example: Filtering by Materialized IDs

In this example, we take the results of our joined SQL_CACHE_QUERY and use the resulting user IDs to fetch full profiles from the database.

// 1. Fetch the materialized view results (from cache or SQL fallback)
const materializedViewResult = await SQL_CACHE_QUERY;

// 2. Use the cached results to drive a targeted SQL query
// This is significantly faster than re-joining inside the database
const detailedUsers = await FORGE_SQL_ORM
  .selectFrom(demoUsers)
  .where(
    inArray(
      demoUsers.id, 
      materializedViewResult.map(m => m.userId)
    )
  );

The Hard Part: Cache Invalidation and Consistency

A Virtual Materialized View is only as good as its invalidation strategy. In Forge, the real challenge is not storing expensive query results, but keeping them consistent after the underlying relational data changes.

forge-sql-orm addresses this by automating the synchronization between Forge SQL tables and cached entries stored in @forge/kvs.

Automatic Invalidation

The library tracks table-level dependencies for cacheable queries. When data is modified through the ORM, any cached query associated with the affected tables is invalidated automatically.

This applies to write operations such as INSERT, UPDATE, and DELETE, but only when they are executed through the cache-aware ORM APIs.

Immediate Eviction for Single Writes

For standalone write operations, the library provides methods that execute the SQL statement and immediately evict related cache entries:

insertAndEvictCache()
updateAndEvictCache()
deleteAndEvictCache()

These methods are essential when a resolver performs a single modification and consistency must be preserved immediately.

Consolidated Eviction with Cache Context

When a resolver modifies multiple tables in one logical flow, evicting cache entries after each individual write is inefficient.

To solve this, forge-sql-orm provides executeWithCacheContext(). Instead of triggering eviction immediately for every operation, this wrapper collects all affected tables during execution and performs one consolidated invalidation step at the end.

This makes multi-step write flows both faster and cheaper, which is critical in the Forge environment where execution time and KVS operations are metered.

Example: Batched Invalidation Across Multiple Tables

In the resolver below, both demo_users and demo_orders may be modified inside the same cache context. Instead of purging related cache entries multiple times, the ORM aggregates the affected tables and performs one batch invalidation pass after the block completes.

resolver.define(“insertUserOrOrder”, async (req: Request) => {
  await FORGE_SQL_ORM.executeWithCacheContext(async () => {
     let userId = req.payload.userId;
     if (!userExists) {
      // Registers demoUsers for consolidated eviction
      const result = await FORGE_SQL_ORM.insert(demoUsers).values({
        name: req.payload.userName,
      });
      userId = result[0].insertId;
    }
    if (newOrder) {
      // Registers demoOrders for consolidated eviction
      await FORGE_SQL_ORM.insert(demoOrders).values({
      userId,
      product: req.payload.product,
        createdAt: new Date(),
      });
    }
    // After the block finishes, the ORM invalidates cache entries
    // associated with the affected tables in one consolidated pass.
  });
});

Optional Scheduled Cleanup

As cache usage grows, invalidation-related operations can become more expensive, especially if many expired cache entries remain in storage.

Although Forge KVS supports TTL, expired entries are not removed immediately; physical cleanup is asynchronous and can take up to 48 hours. Over time, this can increase the amount of stale metadata the invalidation logic needs to process.

To reduce this overhead, forge-sql-orm provides an optional scheduled cleanup trigger. It periodically removes expired cache entries using an expiration index, keeping the cache footprint small and eviction performance predictable.

// index.ts
import { clearCacheSchedulerTrigger } from “forge-sql-orm”;

export const clearCache = () => {
   return clearCacheSchedulerTrigger({
     cacheEntityName: “cache”,
    });
};

# manifest.yml
  scheduledTrigger:
    - key: clear-cache-trigger
      function: clearCache
      interval: fiveMinute # Proactive cleanup every 5 minutes
  function:
    - key: clearCache
       handler: index.clearCache

This scheduler is particularly useful in high-traffic apps with heavy cache churn, preventing the accumulation of expired entries from slowing down your INSERT and UPDATE operations.

Performance Benchmarks and Conclusion

To see the practical effect of the virtual materialized view pattern, let’s compare the execution of the same joined query in two modes: direct SQL execution and cached execution through forge-sql-orm.

For this example, the query joins demo_users and demo_orders. To make the difference easier to observe, the SQL includes an artificial SLEEP(0.5) delay that simulates an expensive read path.


const SQL_CACHE_QUERY = FORGE_SQL_ORM.selectCacheable({
  userId: demoUsers.id,
  userName: demoUsers.name,
  product: demoOrders.product,
  productId: demoOrders.id,
  sleep: sql`SLEEP(0.5)`,
})
.from(demoUsers).leftJoin(demoOrders, eq(demoOrders.userId, demoUsers.id));

Scenario A: Direct SQL Execution

Without caching, every request forces Forge SQL to execute the full joined query again. That means the database must repeat the join work and consume execution resources for every invocation.

The Execution Plan (EXPLAIN):

SQL: SELECT … FROM demo_users LEFT JOIN demo_orders … | Time: 4014 ms
Plan:
Projection_6 | task:root | … | \[estRows:8.75, actRows:8\] | execution info:time:4.01s
└─IndexHashJoin_13 | task:root | left outer join, inner:IndexLookUp_10 …
├─TableReader_30(Build) | task:root | data:TableFullScan_29 | \[estRows:7.00, actRows:7\]
│ └─TableFullScan_29 | task:cop\[tikv\] | access object:table:demo_users
└─IndexLookUp_10(Probe) | task:root | \[estRows:8.75, actRows:8\]
├─IndexRangeScan_8(Build) | task:cop\[tikv\] | access object:table:demo_orders, index:user_id
└─TableRowIDScan_9(Probe) | task:cop\[tikv\] | access object:table:demo_orders

In the benchmark environment, this query completed in 4014 ms. From a platform perspective, that is already uncomfortably close to the 5-second SELECT timeout. On a larger tenant, this kind of query can easily cross the boundary and fail.

Scenario B: L2 Cache Hit

Once the query result has been stored in the persistent KVS-backed cache, subsequent executions no longer need to hit Forge SQL for the same read.

In the benchmark environment, the cached result was returned in 20 ms. The important difference is not only lower latency, but also a different failure profile: the application avoids repeating the expensive SQL execution path entirely.

Benchmark Summary

Metric	Direct SQL	Cache Hit	Effect
Response time	4014 ms	20 ms	Significantly lower latency
SQL timeout exposure	High	None	Safer read path
Database execution cost	Paid on every call	Avoided	Less repeated SQL work

When This Pattern Makes Sense

The virtual materialized view approach is especially useful for read-heavy features where the underlying data changes less frequently than it is read. Typical examples include:

Dashboards and reporting queries.
Derived read models or aggregated relational views.
Permission-related lookups and complex configurations.

It also serves as a late-stage optimization strategy. When a query remains expensive even after better indexes or query rewrites, caching the materialized result at the application layer becomes a practical alternative.

Best Practices

Use this pattern selectively. Small, cheap, and highly dynamic reads are often better executed directly in SQL to avoid KVS overhead.
Mind Platform Limits. This pattern works best for filtered, aggregated, or derived results, not for copying large tables into the cache.
Consistency Matters. If a resolver modifies multiple tables, wrapping the operation in executeWithCacheContext() helps keep invalidation efficient and predictable.

Final Thoughts

Forge SQL gives developers a powerful relational model, but platform limits mean some read-heavy workloads need more than query tuning alone. When native materialized views are not available, moving that behavior into the application layer keeps complex features responsive.

That is the role of the virtual materialized view pattern in forge-sql-orm: combining two-level caching, deterministic cache keys, and automated invalidation to make expensive relational reads reusable without building a separate synchronization subsystem by hand.

Resources & Implementation Examples

If you want to dive deeper into the code or see how this pattern is implemented in production-grade apps, explore the following resources:

Core Library: forge-sql-orm on GitHub - the complete source code, documentation, and advanced features.
Official Cache Example: Advanced Caching Capabilities Example- a dedicated example showcasing performance monitoring and tiered caching.
Real-World Production App: Forge Secure Notes for Jira - a full implementation of an AI-powered security app using the Virtual Materialized View pattern for secure analytics.
Atlassian Marketplace: Secure Notes for Jira - see the final product in action on the marketplace.

Practical SQL Observability for Forge Apps with forge-sql-orm

Vasiliy Zakharchenko — Tue, 02 Dec 2025 20:01:44 +0000

Over the past month, I introduced a new observability layer inside my library forge-sql-orm.
The goal was simple: make Forge SQL measurable and transparent — without breaking Runs on Atlassian compliance and without accessing any customer data.

As part of my Codegeist project, I integrated this layer into a real Forge app and connected it to PostHog.
This instantly gave me end-to-end visibility into resolver performance across environments, tenants, and app versions - all while staying fully within the platform boundary.

Why Observability Is Especially Hard in Forge SQL

Forge SQL is multitenant — the physical database is shared across many customers, and each tenant gets its own logical slice of data.

In practice, this creates two major challenges:

1. Tenants can have radically different dataset sizes

A resolver that runs in 50–100 ms for one customer may take hundreds of milliseconds, or even seconds, for another.

And you have no way to know this ahead of time:

you cannot see the tenant’s actual table sizes
you cannot log into the underlying database
you cannot estimate index selectivity per tenant
you can receive slow-query entries from TiDB, but only after a tenant has already experienced degraded performance

2. Platform-level analytics are available - but not enough

Forge SQL exposes some low-level database metrics:

slow-query logs
cluster statistics
execution summaries

However, these analytics are:

not tied to specific resolvers
not correlated with app versions or environments
not connected to payload size or resolver logic
not continuous (visible only when TiDB marks a query as “slow”)
not designed to show trends, regressions, or per-tenant behavior

They help diagnose extreme cases, but they’re not sufficient for understanding how your application performs in real-world multi-tenant conditions.

You only see what your resolver sees — nothing more.

Why That Becomes a Real Problem

As schemas grow and joins become more complex, behavior becomes unpredictable:

A query with a perfect execution plan can still be slow for a large tenant.
Pagination with large OFFSET becomes inconsistent between customers.
A new join may be harmless in dev but catastrophic for a tenant with millions of rows.
Regression detection is impossible without telemetry — you cannot see if performance worsened after a release.

Because all real data lives inside Atlassian infrastructure, the app developer has almost no visibility into how SQL behaves “out in the wild.”

This is exactly the gap that the new observability layer is designed to fill.

What the New Observability Layer Provides

The layer automatically captures performance characteristics for every resolver invocation.

Aggregated total DB execution time

All SQL statements executed by the resolver contribute to a single aggregated DB time metric.

Tiered logging thresholds

Debug when total DB time > 1000 ms
Warn when total DB time > 2000 ms

Thresholds can be tuned per resolver.

Automatic SQL plan dump

When total DB time exceeds 2000 ms, the ORM prints full execution plans for all queries executed inside the resolver directly into Forge logs.

This helps diagnose:

unexpected TableFullScan
heavy IndexJoin
missing indexes
window functions with large memory needs
skewed statistics for a large tenant
inefficient pagination

Performance telemetry sent to allowed analytics tools (e.g., PostHog)

Only anonymized metadata is sent:

cloudId
environment
resolverName
appVersion
totalDbExecutionTime
totalResponseSize

This enables weekly insights, multi-tenant comparisons, and regression detection — fully compliant and PII-free.

Automatic timeout and out-of-memory diagnostics

The observability layer also detects and analyzes failures such as:

“Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query.”
“The provided query took more than 5000 milliseconds to execute.”

When these errors occur, the ORM retrieves and logs execution plans for the failed queries — making it possible to understand the issue even without access to tenant data.

Configurable by Design

One important aspect of this observability layer is that it is not global.
It is resolver-level, meaning every resolver can define its own behavior independently.

Each resolver can configure:

custom thresholds
warning levels
plan-dump behavior
analytics logic
additional metadata
sampling rules
environment-specific overrides
or disable observability entirely

This flexibility is powered by a simple callback structure:

executeWithMetadata(
  async () => {
    // resolver logic
  },
  async (totaldbTime, totalResponseSize, printPlan) => {
    // your custom logic here
  }
);

Because the logic lives at the resolver level, the system is:

easy to tune
lightweight
platform-safe
precise and predictable

Each resolver gets exactly the level of observability it needs — no more, no less.

Even If Analytics Are Disabled — Observability Still Works

Some customers disable analytics or block outbound requests entirely.

In this case, no telemetry is sent to PostHog (or any analytics tool), but the observability layer still provides full visibility.

All essential diagnostic information remains available directly in the logs:

totalDbExecutionTime
totalResponseSize
per-query execution details
full SQL plans
timeout and OOM diagnostics
resolver-level warnings and thresholds

This means that even if analytics events never leave the customer’s infrastructure, the customer can still provide logs that fully explain:

which resolver was slow
which queries were executed
what each plan looked like
why the slowdown happened (bad plan, full scan, index join, statistics skew, etc.)

Observability does not depend on outbound analytics — telemetry is optional, but diagnostics are always available.

100% inside the Forge boundary

No external storage
No outbound data beyond anonymized telemetry
No PII
No customer content

Runs on Atlassian — by design.

Integration Example

1. `manifest.yml` (permissions)

source

permissions:
  external:
    fetch:
      backend:
        - address: "*.posthog.com"
          category: analytics
          inScopeEUD: false

2. Wrapping a resolver

source

resolver.define('Test Resolver', async (req: Request) => {
    const resolverName = 'Test Resolver';
    return FORGE_SQL_ORM.executeWithMetadata(
        async () => {
           return ... // resolver logic
        },
        async (totalDbExecutionTime, totalResponseSize, printQueriesWithPlan) => {
            await ANALYTIC_SERVICE.sendAnalytics(
                "sql_resolver_performance",
                resolverName,
                req.context.cloudId,
                { totalDbExecutionTime, totalResponseSize },
            );

            if (totalDbExecutionTime > 2000) {
                console.warn(
                    `Resolver ${resolverName} has high database execution time: ${totalDbExecutionTime}ms`,
                );
                await printQueriesWithPlan();
            } else if (totalDbExecutionTime > 1000) {
                console.debug(
                    `Resolver ${resolverName} has elevated database execution time: ${totalDbExecutionTime}ms`,
                );
            }
        },
    );
});

3. Sending analytics to PostHog

source

const appContext = getAppContext();
const properties = {
    resolverName,
    cloudId,
    envName: appContext.environmentType,
    envId: appContext.environmentAri.environmentId,
    version: appContext.appVersion,
    parsedVersion: this.parseVersion(appContext.appVersion),
    totalDbExecutionTime: data.totalDbExecutionTime,
    totalResponseSize: data.totalResponseSize,
    eventVersion: 1,
};

await fetch("https://eu.i.posthog.com/capture/", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
        api_key: process.env.ANALYTICS_API_KEY,
        event: eventName,
        distinct_id: cloudId,
        timestamp: new Date().toISOString(),
        properties: properties,
    }),
});

This is enough to build dashboards that reflect real-world performance across tenants.

4. PostHog query for weekly resolver performance

SELECT
  count(uuid) AS event_count,
  properties.envId,
  properties.cloudId,
  AVG(properties.totalDbExecutionTime) AS avgTime,
  concat(properties.cloudId, ':', properties.envName, ':', properties.resolverName) AS resolverName,
  max(properties.parsedVersion),
  max(timestamp) AS last_seen_at
FROM events
WHERE event = 'sql_resolver_performance'
  AND properties.eventVersion = 1
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY
  properties.envId,
  properties.cloudId,
  properties.envName,
  properties.resolverName
HAVING avgTime > 500;

Putting It to the Test

To verify the full pipeline, I intentionally left a performance bottleneck in place:

SELECT SLEEP(2)

This pushed the total DB time above the 2000 ms threshold.

Here’s what happened:

In PostHog, I immediately saw a spike:

I opened the Forge logs and found the detailed plans:

The execution plan pointed directly to the problematic place in code:

After removing the artificial delay, latency dropped from ~2200 ms → ~150 ms.

From Black Box to Glass Box

Before, Forge SQL was essentially a black box.
Now, with observability integrated directly into forge-sql-orm, it becomes:

diagnosable
measurable
predictable
transparent

This observability layer is lightweight, compliant, and extremely helpful when building applications with complex schemas, heavy joins, and tenant-specific performance patterns.

Try It Yourself

Codegeist project:
👉 https://github.com/vzakharchenko/Forge-Secure-Notes-for-Jira

forge-sql-orm repository:
👉 https://github.com/vzakharchenko/forge-sql-orm

Updates:

1. New deterministic default mode (TopSlowest)

The default behavior no longer depends on CLUSTER_STATEMENTS_SUMMARY.

forge-sql-orm now logs the exact SQL digests executed inside the resolver, giving deterministic diagnostics even for long-running logic.

By default it prints:

the single slowest query, and
optionally that query’s execution plan (showSlowestPlans: true)

Configurable like this:

{
  topQueries: 2,          // how many slowest queries to analyze
  showSlowestPlans: true, // re-executes them with EXPLAIN ANALYZE
}

If showSlowestPlans is enabled — the library re-executes these queries with EXPLAIN ANALYZE.
If disabled — it prints only SQL + timing.

plan enabled:

plan disabled:

2. SummaryTable mode (optional)

SummaryTable mode still exists, but now works as an advanced diagnostic option.

It uses a short memory window:

summaryTableWindowTime: 15000 // 15s default

If resolver execution exceeds this window, forge-sql-orm automatically falls back to TopSlowest, avoiding stale diagnostics.

This keeps SummaryTable useful for fresh metadata, but avoids relying on it for long workflows.

3. Updated API

Here is the updated API with all configuration options:

executeWithMetadata(
  async () => {
    // resolver logic
  },
  async (totalDbTime, totalResponseSize, printPlan) => {
    // your custom logic:
    // analytics, thresholds, alerts, logging, etc.
    // e.g.: if (totalDbTime > 1000) await printPlan();
  },
  {
    mode?: QueryPlanMode;            // "TopSlowest" | "SummaryTable" (default: TopSlowest)
    summaryTableWindowTime?: number; // ms window for SummaryTable (default: 15000)
    topQueries?: number;             // number of slowest queries to print (default: 1)
    showSlowestPlans?: boolean;      // print EXPLAIN ANALYZE in TopSlowest mode (default: true)
  }
);

Everything is opt-in and Forge-safe by design.

4. Timeout & OOM post-mortem diagnostics

For catastrophic SQL failures, the library performs an immediate post-mortem lookup.

Right after a Timeout or OOM, TiDB’s metadata is still in memory — so forge-sql-orm extracts the actual plan of the failing query before eviction can occur.

Case A: Timeout

“The provided query took more than 5000 milliseconds to execute…”

forge-sql-orm automatically logs the execution plan of the failing query:

Case B: Out of Memory (OOM)

“Your query has been cancelled due to exceeding the allowed memory limit…”

The library captures the memory-heavy plan that triggered the crash:

Why this matters

No re-execution required - avoids triggering the same timeout or OOM again.
Plans come from the real execution - captured with actual data distribution and bind parameters.
No tenant data is exposed - metadata only, fully compliant.
Runs entirely inside the Forge boundary - no privileged access or special APIs.
Works reliably even for complex, deeply nested SQL workloads - joins, pagination chains, window functions, etc.

This gives developers a safe way to understand severe failures without privileged access.

5. Why developer-side observability matters

This configurability — implemented on the application side — lets developers enable observability exactly where it’s needed:

resolver/long function/scheduler-level instrumentation
custom thresholds
selective plan printing
sampling
environment rules
optional analytics

And importantly:

You don’t need my library to do any of this.
Developers can implement the same pattern manually — forge-sql-orm simply makes it easier and more consistent.

Developer-side observability naturally complements platform-level observability.

Together, they enable building Forge apps with deep SQL execution paths: complex joins, multi-stage pagination, window functions, large OFFSET workflows — while still maintaining transparency and safety.

Natural-Language SQL on Atlassian Forge: A Secure Pattern with Rovo (LLM) + Forge SQL (TiDB)

Vasiliy Zakharchenko — Mon, 17 Nov 2025 07:00:00 +0000

While working on a Forge app that stores structured metadata in Forge SQL, I explored how Rovo could be used not only for documentation/explanations but also for natural-language analytics.
(For context: this comes from the Codegeist 2025 project Forge Secure Notes for Jira — built with forge-sql-orm)

Rovo can generate SQL from natural language with high accuracy, but executing LLM-generated SQL requires a carefully controlled environment.

This post describes a reproducible pattern for using Rovo with Forge SQL safely and predictably - with strict query validation, enforced single-table scope, row-level security, metadata checks, and EXPLAIN-based join detection.

Problem: Safe Execution of LLM-Generated SQL

Executing arbitrary SQL from an AI model introduces risks:
• ensuring the query is strictly SELECT
• ensuring it only targets the intended table
• preventing hidden joins or subqueries
• preventing column spoofing
• enforcing row-level security
• ensuring context variables cannot be manipulated

Rovo must never be trusted to enforce constraints.

All protections belong in the backend executor.

Solution: The “Guide + Guard” Pattern

Two independent layers:

Guide - the Rovo prompt in manifest.yml
Defines strict boundaries and shapes expected SQL.
Guard - backend validator in RovoService.ts
Enforces safety regardless of the generated SQL.

The Guide improves accuracy.
The Guard guarantees security.

1. Guide Layer (manifest.yml)

On the “guide” side, the rovo:agent prompt inside manifest.yml defines strict boundaries for what Rovo is allowed to generate.
This ensures the SQL surface is small, predictable, and secure.
The pattern follows one core rule:
One Rovo Action → One table → No joins → Single SELECT statement

Step 1. Hard constraints enforced by the agent

The prompt defines non-negotiable SQL restrictions:

Use only read-only SELECT statements
Query only the designated table (security_notes in this case)
Never generate any join:
- no INNER / LEFT / RIGHT / FULL / CROSS JOINs
- no implicit joins
Never reference other tables:
- not in FROM
- not in JOINs
- not in subqueries
- not in CTEs
No DML or DDL:
- no INSERT, UPDATE, DELETE
- no ALTER, DROP, CREATE
All execution must go through the backend action
Permissions and row-level filtering are enforced server-side, not by Rovo itself

These restrictions ensure that all generated SQL falls inside a safe, enforceable envelope.

Step 2. Mandatory security columns

Every query must include exactly as raw table fields:

* created_by
* target_user_id

These fields allow the backend to apply strict row-level security and to validate the column origins.
The agent is forbidden from:

aliasing
renaming
duplicating
wrapping in expressions
generating constants
pulling them from derived tables or subqueries

The backend enforces this via metadata (orgTable) returned by Forge SQL.

Step 3. Context variables

The agent may insert these placeholders, which the backend later replaces with authenticated Forge context values:

:currentUserId
:currentIssueKey
:currentProjectKey

The prompt also defines natural-language mappings:

“my notes” → (created_by = :currentUserId OR target_user_id = :currentUserId)
“notes I created” → created_by = :currentUserId
“this issue” → issue_key = :currentIssueKey
“last week” → created_at >= DATE_SUB(NOW(), INTERVAL 1 WEEK)

This gives Rovo deterministic, controlled semantics.

Step 4. Tools / actions

At the end of every interaction, Rovo must:

Build a valid SELECT query following all rules
Execute it strictly via the action:

{ "sql": "<your select query>" }

After receiving the backend result, the agent must:
- summarize the results clearly
- highlight useful counts or trends
- optionally present a small table of values

This gives users friendly natural-language insights while preserving backend-enforced safety.

2. Guard Layer (RovoService.ts)

This function rewrites and validates SQL generated by Rovo.

Step 1. SQL normalization

All queries are normalized:

• newlines removed
• tabs and multiple spaces collapsed
• trailing semicolon removed
• user URI prefixes removed

This prevents multiline or formatting-based evasion.


sql
.replace(/\[\\n\\r\\t\]+/g, " ")
.replace(/\\s+/g, " ")
.replace(/\\s\*;\\s\*$/, “”)
.trim();

Step 2. Static analysis: single SELECT via AST
Before touching the forge sql, the query is parsed into an AST using node-sql-parser.
This guarantees that:

the query is a single statement
the statement type is strictly SELECT (no INSERT/UPDATE/DELETE/DDL)
multiple statements or mixed types are immediately rejected

const parser = new Parser();
let ast;

try {
  ast = parser.astify(normalizedQuery);
} catch (err) {
  throw new Error(
    `SQL parsing error: ${err.message || "Invalid SQL syntax"}. ` +
    "Please check your query syntax."
  );
}

// AST may be a single node or an array of statements
if (Array.isArray(ast)) {
  if (ast.length !== 1 || ast[0].type !== "select") {
    throw new Error(
      "Only a single SELECT query is allowed. " +
      "Multiple statements or non-SELECT statements are not permitted."
    );
  }
  ast = ast[0];
} else if (!ast || ast.type !== "select") {
  throw new Error("Only SELECT queries are allowed.");
}

Step 3. AST-based table scope + + EXPLAIN-based join detection
Two protections work together:

Step 3.1. The query must reference only the allowed table

const tablesInQuery = this.extractTables(ast);      // walks FROM, JOIN, subqueries
const uniqueTables = [...new Set(tablesInQuery)];
const invalidTables = uniqueTables.filter(
  (table) => table !== "SECURITY_NOTES"
);

if (invalidTables.length > 0) {
  throw new Error(
    "Security violation: query references table(s) other than 'security_notes': " +
      invalidTables.join(", ") +
      ". Only queries against the security_notes table are allowed. " +
      "JOINs, subqueries, or references to other tables are not permitted."
  );
}

Because the check is AST-based, it also catches:
• tables used inside scalar subqueries
• tables referenced in nested expressions
• tables hidden behind aliases

Step 3.2. EXPLAIN plan must not contain join operators

This prevents hidden joins, optimizer-rewritten joins, or subquery-based joins.

const explainRows =
await FORGE_SQL_ORM.analyze().explainRaw(normalizedQuery, [ ]);
const hasJoin = explainRows.some(row => {
    const op = (row.operatorInfo || “”).toUpperCase();
    return (
       op.includes(“JOIN”) ||
       op.includes(“HASH JOIN”) ||
       op.includes(“NESTED LOOP”) ||
       op.includes(“CARTESIAN”)
    );
});
if (hasJoin) {
    throw new Error(“JOIN operations are not allowed for this Rovo action.”);
}

Step 3.3. EXPLAIN plan must not contain window functions

To keep the model simple and safe, we completely block any window functions at the EXPLAIN level.

Window functions like COUNT(*) OVER(...) can leak information about the total number of rows that exist beyond the current user’s slice of data.
This ensures the query executes strictly against a single table.

const hasWindow = explainRows.some((row) => {
  const id = row.id.toUpperCase();
  const info = (row.operatorInfo ?? "").toUpperCase();
  return id.includes("WINDOW") || info.includes(" OVER(") || info.includes(" OVER()");
});

if (hasWindow) {
   throw new Error(
     "Window functions (for example COUNT(*) OVER(...)) are not allowed in Rovo SQL for this app. " +
          "Please rephrase your question so that it uses regular aggregates instead of window functions.",
    );
}

Step 3.4. EXPLAIN plan must not reference other tables

To guarantee that this Rovo action truly works only on security_notes, we validate the execution plan itself.

We scan the accessObject column from EXPLAIN and reject any plan that touches a table other than security_notes:

// Detect any table access other than `security_notes`
const tablesInPlan = explainRows.filter(
  (row) =>
    row.accessObject?.startsWith("table:") &&
    row.accessObject !== "table:security_notes",
);

if (tablesInPlan.length > 0) {
  throw new Error(
    "Security violation: query plan references tables other than 'security_notes'. " +
      "This Rovo action only allows analytics over the security_notes table. " +
      "Please remove JOINs, subqueries, or references to other tables and try again.",
  );
}

This ensures the query executes strictly against a single table.

Step 4. Replace context variables

Placeholders are replaced with authenticated values:

:currentUserId
:currentIssueKey
:currentProjectKey

This prevents Rovo from injecting or modifying identity context.

Step 5. Row-Level Security (non-admin users)

Non-admin queries are wrapped with enforced filtering:

SELECT * FROM (<normalized_rovo_query>) AS t
       WHERE t.created_by = ‘currentUserId’ OR t.target_user_id = ‘currentUserId’;

This guarantees users can only see:
• notes they created
• notes shared with them
even if the model produces an incorrect or unsafe WHERE clause.

Step 6. Post-execution metadata validation

Forge SQL exposes orgTable metadata for each column.
The executor validates:

created_by.orgTable = “security_notes”
target_user_id.orgTable = “security_notes”

If the columns originate from:
• constants
• expressions
• subqueries
• other tables

the query is rejected.
Metadata validation prevents column spoofing and ensures RLS correctness.

Additionally, we validate that every column that has an orgTable defined must also come from:

orgTable === "security_notes"

This provides a third layer of protection by ensuring that no fields in the result set were sourced from other tables via joins or nested queries — even if the SQL text tried to hide it.

Metadata validation fully closes the loop and ensures the backend executes exactly what was intended:
a safe, single-table query over security_notes(Computed fields remain allowed) with correct RLS boundaries.

Step 7. Most Important, platform guarantees that strengthen the pattern

In addition to app-level validation, Forge SQL provides two important safety properties:

• Single-statement execution:

Forge SQL only allows one SQL statement per call.

No multi-statements, no chaining — making traditional SQL injection impossible.

• Tenant isolation:

Forge SQL’s architecture ensures strict separation between tenants.

Even incorrectly formed queries cannot access data outside the current tenant’s schema.

Architectural Note: One Table per Rovo Action

The security guarantees in this pattern rely on the fact that each Rovo Action operates on exactly one table.

This constraint keeps the execution predictable:

• ensures stable SQL structure
• simplifies EXPLAIN-based join detection
• makes metadata validation (orgTable) reliable
• ensures enforceable row-level security
• eliminates ambiguity in column origin
If analytics require data from multiple datasets, there are several possible approaches that remain compatible with the “one table per action” pattern.

Option A — Use a consolidated analytics table

A process (scheduled trigger, background sync, or nightly aggregation) can populate a dedicated table containing the combined fields needed for analytics.
Rovo then queries that single table.

Option B — Use a separate rovo:action per table

If the application exposes multiple datasets, each dataset can have its own Rovo Action with its own validation logic — each still limited to one table.
This keeps the security properties of the pattern intact.
Both approaches preserve the core property:
each Rovo Action should operate on one table to ensure strict, deterministic validation.

⸻

Result

With a structured Guide in the manifest and a strict Guard in the executor:
• natural-language analytics becomes possible
• all LLM-generated SQL is sandboxed
• row-level security is always enforced
• joins and cross-table access are safely blocked
• SQL injection is prevented by design
• users get a powerful analytics interface without compromising security

This pattern provides a safe execution envelope for Rovo-driven SQL in Forge apps.

⸻

Full Source Code

Rovo Prompt manifest.yml

Secure Executor RovoService.ts

Integrating External Services in Atlassian Forge — Without Losing the “Runs on Atlassian” Eligibility

Vasiliy Zakharchenko — Sun, 12 Oct 2025 09:59:13 +0000

When building Forge apps, sooner or later you’ll want to integrate with an external system — maybe for monitoring, data synchronization, or automation.
But the moment you call an external API using fetch(), your app loses the Runs on Atlassian badge.

This article shows how to design such an integration without any egress — meaning the Forge app never sends data outside Atlassian Cloud — and still communicates securely with an external backend.

The full demo project is available on GitHub:
Forge Health Monitor

The Architecture

The solution is based on two key capabilities that Atlassian Forge provides.

1. Safe navigation to external resources via router.navigate

Forge allows navigation to external pages using the @forge/bridge API:

import { router } from "@forge/bridge";

await router.navigate(registrationUrl);

When this happens, Forge automatically shows a security confirmation dialog, warning the user that they’re leaving the Atlassian environment.
Importantly, this does not require any permissions in your manifest.yml.

The key idea is that during this redirect, the app passes non-sensitive contextual data to the external backend, such as:
• cloudId — the unique tenant identifier (used to separate data by tenant on the external backend)
• accountId — the Atlassian account of the user (may be shared across multiple tenants)
• triggerUrl — a static web trigger endpoint unique to each tenant (unauthenticated by default, so custom authorization logic must be implemented)
• callbackUrl — the Forge page to return

This pattern can also be extended to support OAuth authorization flows (Google, Microsoft, or Keycloak).

2. Static Web Triggers for inbound communication

Static Web Triggers are the second key mechanism.
They allow external services to send HTTP requests into Forge — and doing so does not violate the Runs on Atlassian requirement, because the call direction is inbound, not outbound.

Web triggers don’t provide built-in authentication, so you must implement it yourself.
In this example, authentication is done using Ed25519-signed requests — it’s a minimal working approach, though it could be further improved.

What Forge Health Monitor Application Does

Forge Health Monitor is a monitoring solution that allows you to track the health status of external services directly from your Jira instance. Here's what it does:

Core Functionality:

Monitors External Services - Continuously checks if your external APIs, websites, or services are running
Real-time Status Display - Shows current health status (ALIVE/DOWN) for all monitored services
User-friendly Interface - Simple Jira global page where you can view all your service statuses
Automatic Updates - Refreshes status every 30 seconds without manual intervention

Here’s how it works conceptually:

How to Run the Example

Clone the repository

git clone https://github.com/vzakharchenko/Forge-Health-Monitor
cd Forge-Health-Monitor

Install dependencies
```
npm install
```
Register the app in your Atlassian Developer Console
```
forge register
```
Start an ngrok tunnel on port 8080 — this will expose your custom backend:
```
ngrok http 8080
# Note the HTTPS URL (e.g., https://abc123.ngrok.app)
```

Set the ngrok URL as a Forge environment variable

forge variables set BACKEND_URL https://abc123.ngrok.app

Deploy the Forge app
```
forge deploy
```
Install it into your Jira instance
```
forge install
```

Start the custom backend

cd ./customBackend
npm install
npm run start

The app and backend are now running — open your Forge app in Jira and test the flow!

Exploring the App

Open your Jira instance and launch the Forge app
Click “Add Service”
Confirm navigation by clicking “Continue” in the system dialog
Enter any publicly available health check URL that returns HTTP 200 (no authentication required). Only http and https URLs are accepted in this demo for simplicity.
Click Continue, and you’ll return to the Forge app. You’ll see the new service in the list — the backend will now periodically (every 5 minutes) check the service’s availability.

Result

With this setup, we successfully integrated an external service without violating Runs on Atlassian.
All outbound requests are performed by the external backend, while Forge remains completely isolated and only receives signed updates through web triggers.

Update (confirmed by Atlassian Staff)

Atlassian Staff member rmassaioli confirmed that this inbound-only integration pattern —
using route.navigate and static web triggers — is exactly what those features were designed for and fully compatible with the Runs on Atlassian model.

This means the approach isn’t a workaround — it’s an intended architectural path for secure external integrations that stay fully within Atlassian’s trusted boundary.

Pagination with COUNT(*) OVER() in Atlassian Forge SQL: How to avoid double queries — and stay secure in TypeScript

Vasiliy Zakharchenko — Sun, 06 Apr 2025 11:21:41 +0000

Are you working with @forge/sql and struggling with pagination + total count?

Here's a more efficient and safer way to do it using window functions.

💡 The problem

In typical pagination, we often do two queries:

SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT COUNT(*) FROM users;

But if your SQL engine supports window functions — like TiDB in Atlassian Forge — you can get everything in a single query:

SELECT 
  id,
  name,
  COUNT(*) OVER() AS total_count
FROM users
ORDER BY name
LIMIT 10 OFFSET 20;

You’ll get a total_count in each row — and no second query needed.

✅ Using it with `forge-sql-orm`

import { sql } from "drizzle-orm";
import ForgeSQL from "forge-sql-orm";
import { users } from "./schema";

const forgeSQL = new ForgeSQL();

const result = await forgeSQL
  .select({
    id: users.id,
    name: users.name,
    totalCount: sql<number>`COUNT(*) OVER()`,
  })
  .from(users)
  .orderBy(users.name)
  .offset(formatLimitOffset(offset))
  .limit(formatLimitOffset(limit));

And here’s a safe helper for LIMIT/OFFSET:

export function formatLimitOffset(limitOrOffset: number): number {
  if (typeof limitOrOffset !== "number" || isNaN(limitOrOffset)) {
    throw new Error("limitOrOffset must be a valid number");
  }
  return sql.raw(`${limitOrOffset}`) as unknown as number;
}

⚠️ Why this matters — Forge SQL doesn’t support bind params for LIMIT/OFFSET

Libraries like Drizzle normally compile queries like:

SELECT ... FROM users ORDER BY name LIMIT ? OFFSET ?

But Forge SQL (powered by TiDB) doesn’t support parameterized LIMIT or OFFSET.

So you have to inline the values — carefully.

🔐 And yes, you can still get SQL injection in `.prepare(...)`

Even this Forge-native code is vulnerable, if limit or offset come from user input:

const result = await sql
  .prepare<User>(`SELECT * FROM users LIMIT ${limit} OFFSET ${offset}`)
  .execute();

Just because limit: number in TypeScript doesn’t mean it’s actually a number at runtime.

✅ Use runtime checks:

function safeLimitOffset(value: unknown): number {
  if (typeof value !== "number" || isNaN(value)) {
    throw new Error("Invalid limit or offset");
  }
  return value;
}

✅ One query, safer code, better performance

This pattern works in Atlassian Forge SQL, MySQL, TiDB, PlanetScale, and more.

If you're using @forge/sql, forge-sql-orm, or raw Drizzle — this will help keep things clean and fast.

🧑‍💻 Project: forge-sql-orm

Questions and feedback welcome!

Type-safe ORM for Atlassian Forge SQL: forge-sql-orm with Drizzle support

Vasiliy Zakharchenko — Sat, 05 Apr 2025 12:41:02 +0000

💡 `forge-sql-orm`: Type-safe ORM for @forge/sql based on Drizzle

Are you working with @forge/sql and tired of writing raw SQL with manual typings?

forge-sql-orm brings a structured, type-safe, and developer-friendly experience using Drizzle ORM — fully compatible with Forge’s infrastructure.

Hey everyone! 👋

I'd like to introduce forge-sql-orm — a type-safe ORM designed for working with the Atlassian Forge platform using @forge/sql, built on top of Drizzle ORM.

🔍 Why `forge-sql-orm`?

🗋 Atlassian provides `@forge/sql`...

...but you still have to write SQL queries manually and manage typings yourself:

import sql from '@forge/sql';

interface City {
  name: string;
  state?: string;
  country: string;
};

const results = await sql
  .prepare<City>(`SELECT * FROM cities WHERE name = ?`)
  .bindParams('New York')
  .execute();

console.log(results.rows[0].country);

⛔️ You must manually define interfaces, manage aliasing, and ensure column matching for joins.

⚠️ Also, prepare<City>() does not guarantee that the returned SQL data actually matches the City interface — it's just a TypeScript hint, not runtime-validated.

📝 While this works fine for simple queries, it becomes problematic when:

Fields are selected dynamically (e.g., from frontend input),
The structure of the result changes conditionally,
You join multiple tables that may share the same column names.

SELECT users.name, company.name ... -- which 'name' is which?

You end up needing:

SELECT users.name AS users_name, company.name AS company_name

And then you must manually write:

.prepare<{ users_name: string; company_name: string }>()

🙄 It's verbose and hard to maintain.

✅ `forge-sql-orm` solves this in 2 ways:

1. Automatically via `forgeSQL.select({...})`

import ForgeSQL from "forge-sql-orm";
const forgeSQL = new ForgeSQL();

const result = await forgeSQL
  .select({ user: users, order: orders })
  .from(orders)
  .innerJoin(users, eq(orders.userId, users.id));

🌟 Columns are aliased by object keys (user.name, order.id) and typed safely.

2. Manually via `selectAliased(...)`

import { drizzle } from "drizzle-orm/mysql-proxy";
import { forgeDriver, patchDbWithSelectAliased } from "forge-sql-orm";

const db = patchDbWithSelectAliased(drizzle(forgeDriver()));

const result = await db
  .selectAliased({ user: users, order: orders })
  .from(orders)
  .innerJoin(users, eq(orders.userId, users.id));

📌 Ideal for those working directly with Drizzle's low-level API.

📐 Schema First Migration Strategy

forge-sql-orm and forge-sql-orm-cli follow a Schema First approach — where your database schema is the single source of truth, rather than relying on manually written model definitions.

This means you have two paths to get started:

If you already have a working Forge SQL database, you can extract the schema directly from it.
Or, if you're starting fresh, you can create a brand-new local or shared MySQL database and define your schema manually.

🧠 What does that mean?

You define your tables using SQL or existing Forge migrations.
You can extract your current schema from Forge SQL and apply it to a local environment.
forge-sql-orm-cli then generates Drizzle models based on the actual schema — not assumptions.

✅ Bonus: it works with any MySQL-compatible database, not just Forge SQL. That means you can use a shared or local dev DB as your source of truth.

This is especially useful if:

You already have an existing Forge SQL project with migrations.
You want to migrate to a type-safe ORM layer.
You work in a team and want consistent, reliable database structure.

🌐 Extracting schema from a Forge app

import { fetchSchemaWebTrigger } from "forge-sql-orm";

export const fetchSchema = async () => {
  return fetchSchemaWebTrigger();
};

manifest.yml configuration:

webtrigger:
  - key: fetch-schema
    function: fetchSchema
sql:
  - key: main
    engine: mysql
function:
  - key: fetchSchema
    handler: index.fetchSchema

Example output:

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS users (...);
CREATE TABLE IF NOT EXISTS orders (...);
SET foreign_key_checks = 1;

🧪 You can apply this script to a local database and run:

npx forge-sql-orm-cli generate:model

🏗️ Defining a Schema from Scratch

Alternatively, you don't need to start with a Forge SQL database at all:

You can create a brand-new local (or shared) MySQL database,
Define your schema from scratch or apply an existing SQL script,
Then use it as your reference schema for generating models and migrations.

This makes forge-sql-orm ideal for greenfield projects or building outside-in from an existing backend architecture.

To create an initial migration:

npx forge-sql-orm-cli migrations:create

To generate follow-up migrations after schema changes:

npx forge-sql-orm-cli migrations:update
npx forge-sql-orm-cli generate:model

🐳 Docker Example for Local Database

export MYSQL_ROOT_PASSWORD=admin

docker run -d \
  --name forge-sql-orm-example-db \
  -p 3366:3306 \
  -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
  --security-opt seccomp=unconfined \
  --restart=always \
  mysql

# Wait 30 seconds, then:
docker exec forge-sql-orm-example-db \
  mysql -uroot -padmin -e "create database forgesqlorm"

🧬 UUID as Primary Key (with VARBINARY(16))

Although Atlassian’s examples often use AUTO_INCREMENT for primary keys, TiDB documentation recommends UUIDs.

✅ Why UUID?

Better support for distributed systems.
Lower chance of key conflicts.
More scalable for indexing.
When using UUID_TO_BIN, binary UUIDs reduce storage size and improve index performance.
Using UUID_TO_BIN(uuid, 1) sorts UUIDs by timestamp, enhancing insertion order and avoiding random I/O bottlenecks.

Recommended pattern:

id VARBINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID()))

To implement UUIDs with varbinary(16) in Drizzle:

export const uuidBinary = customType<{
  data: string;
  driverData: { type: "Buffer"; data: number[] };
  config: [];
}>({
  dataType() {
    return "varbinary(16)";
  },
  toDriver(value) {
    return sql`UUID_TO_BIN(${value})`;
  },
  fromDriver(value) {
    const buffer = Buffer.from(value.data);
    return uuidStringify(new Uint8Array(buffer));
  },
});

Usage:

export const userStatus = mysqlTable("user_status", {
  id: uuidBinary().notNull(),
  name: varchar({ length: 100 }).notNull(),
});

🧠 TiDB also supports UUID_TO_BIN(uuid, 1) to reorder bits and improve clustering/indexing.

🔒 Optimistic Locking Support

To prevent race conditions when multiple users edit the same record, forge-sql-orm supports optimistic locking using a version column.

On crud().insert(), if a version field exists:
- If it's a number, it defaults to 1.
- If it's a date/time, it's set to current timestamp.
On crud().updateById():
- The version is incremented or updated.
- The version is included in the WHERE clause to ensure no conflicts.

This helps avoid overwriting someone else's changes by accident.

⚠️ This logic is not available if you use .getDrizzleQueryBuilder().insert(...) — that API is lower-level and does not handle version automatically.

🧪 Insert Examples

// Single insert
const userId = await forgeSQL.crud().insert(users, [{ id: 1, name: "Smith" }]);

// Bulk insert
await forgeSQL.crud().insert(users, [
  { id: 2, name: "Smith" },
  { id: 3, name: "Vasyl" },
]);

📦 Quick Start

npm install forge-sql-orm @forge/sql drizzle-orm moment -S
npm install forge-sql-orm-cli -D

🔹 Generate migration:

npx forge-sql-orm-cli migrations:create

🔹 Generate models:

npx forge-sql-orm-cli generate:model

🔹 Query example:

import ForgeSQL from "forge-sql-orm";

const forgeSQL = new ForgeSQL();

const orderWithUser = await forgeSQL
  .select({ user: users, order: orders })
  .from(orders)
  .innerJoin(users, eq(orders.userId, users.id));

💬 Feedback

Give it a try and let me know what you think!

If you're already using @forge/sql and want to level up your developer experience with type safety and migrations, I’d love to hear your feedback.

⭐️ Star the repo on GitHub: vzakharchenko/forge-sql-orm

🧑‍💻 PRs, issues, and questions are welcome — or just drop a comment below!

Forem: Vasiliy Zakharchenko

AI Magic in Atlassian Forge: Local Semantic Search with Forge SQL

Introduction

Building the Local Semantic Search Flow

1. Choosing the embedding model

2. Configuring the frontend to run the model locally

2.1 Adding the model files

2.2 Adding the ONNX WebAssembly runtime files

2.3 Initializing the model in the frontend

What this configuration does

Tracking model loading progress

Result

3. Generating the vector

4. Sending the vector to the backend and processing it in Forge SQL

A short demonstration of the app in action

Adding sample documents

Title: Dogs

Title: Tree

Title: Fish

Title: Cat

Title: Mice

Trying semantic search queries

Example 1:

Result: Tree (55.66%)

Example 2:

Result: Cat (39.90%)

Example 3:

Result: Mice

What this demo shows

Conclusion

Virtual Materialized Views for Atlassian Forge SQL: A Two-Level Caching Approach in forge-sql-orm

The “Why”: Understanding the 5-Second Timeout and the 16MB Memory Limit

The 5-Second Timeout

The 16MB Memory Limit

Observability, Optimization, and the Caching Alternative

The “Virtual Materialized View” Approach

Deep Dive: The Two-Level Caching Architecture

Level 1: Local Invocation Cache

Level 2: Persistent Cross-Invocation Cache

Why This Architecture Fits Forge Well

Implementing the Virtual Materialized View Pattern

The Core Method: selectCacheable()

What Happens at Runtime

Using Virtual Materialized Views in Subsequent Queries

The Hard Part: Cache Invalidation and Consistency

Automatic Invalidation

Immediate Eviction for Single Writes

Consolidated Eviction with Cache Context

Example: Batched Invalidation Across Multiple Tables

Optional Scheduled Cleanup

Performance Benchmarks and Conclusion

Scenario A: Direct SQL Execution

The Execution Plan (EXPLAIN):

Scenario B: L2 Cache Hit

Benchmark Summary

When This Pattern Makes Sense

Best Practices

Final Thoughts

Resources & Implementation Examples

Practical SQL Observability for Forge Apps with forge-sql-orm

Why Observability Is Especially Hard in Forge SQL

1. Tenants can have radically different dataset sizes

2. Platform-level analytics are available - but not enough

Why That Becomes a Real Problem

What the New Observability Layer Provides

Aggregated total DB execution time

Tiered logging thresholds

Automatic SQL plan dump

Performance telemetry sent to allowed analytics tools (e.g., PostHog)

Automatic timeout and out-of-memory diagnostics

Configurable by Design

Even If Analytics Are Disabled — Observability Still Works

100% inside the Forge boundary

Integration Example

1. manifest.yml (permissions)

2. Wrapping a resolver

3. Sending analytics to PostHog

4. PostHog query for weekly resolver performance

Putting It to the Test

From Black Box to Glass Box

1. `manifest.yml` (permissions)

✅ Using it with `forge-sql-orm`

🔐 And yes, you can still get SQL injection in `.prepare(...)`

💡 `forge-sql-orm`: Type-safe ORM for @forge/sql based on Drizzle

🔍 Why `forge-sql-orm`?

🗋 Atlassian provides `@forge/sql`...

✅ `forge-sql-orm` solves this in 2 ways:

1. Automatically via `forgeSQL.select({...})`

2. Manually via `selectAliased(...)`