# How to Optimize List Queries with Column Projection and Indexing in Agent-Native

> Optimize Agent-Native list queries using column projection and indexing. Boost performance by avoiding full-table scans and reducing memory usage.

- Repository: [Builder.io/agent-native](https://github.com/BuilderIO/agent-native)
- Tags: performance
- Published: 2026-06-27

---

**Agent-Native optimizes list queries by projecting only required columns and indexing filter and sort columns, eliminating expensive full-table scans and reducing memory overhead from large JSON blobs.**

Agent-Native stores application data in a SQLite database accessed through Drizzle ORM. When rendering list views, fetching entire rows—including heavy JSON fields like `assets` or `custom_instructions`—creates unnecessary I/O and latency. This guide explains how to implement optimizing list queries with column projection and indexing in Agent-Native, referencing production patterns from [`packages/core/src/db/schema.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/db/schema.ts) and [`templates/videos/actions/list-design-systems.ts`](https://github.com/BuilderIO/agent-native/blob/main/templates/videos/actions/list-design-systems.ts).

## The Performance Cost of Full-Row Selection

Fetching complete rows via a plain `.select()` statement pulls every column from the database, including large text or JSON blobs that list views rarely display. This increases read latency, memory pressure on the JavaScript runtime, and serialization overhead when crossing the database bridge.

In [`templates/videos/actions/list-design-systems.ts`](https://github.com/BuilderIO/agent-native/blob/main/templates/videos/actions/list-design-systems.ts), the framework avoids this anti-pattern by explicitly selecting only the columns the UI consumes. The heavy `assets` field is excluded from the list query and fetched on-demand via a separate `get-design-system` action.

## Implementing Column Projection

**Column projection** means passing a specific object to Drizzle’s `.select()` method that names only the columns required for the view. This reduces the amount of data read, the size of the result set, and the time spent serializing rows.

### Example: List Design Systems Action

The following code from [`templates/videos/actions/list-design-systems.ts`](https://github.com/BuilderIO/agent-native/blob/main/templates/videos/actions/list-design-systems.ts) demonstrates the pattern:

```typescript
const rows = await db
  .select({
    id: schema.designSystems.id,
    title: schema.designSystems.title,
    description: schema.designSystems.description,
    data: schema.designSystems.data,
    isDefault: schema.designSystems.isDefault,
    visibility: schema.designSystems.visibility,
    createdAt: schema.designSystems.createdAt,
    updatedAt: schema.designSystems.updatedAt,
  })
  .from(schema.designSystems)
  .where(accessFilter(schema.designSystems, schema.designSystemShares))
  .orderBy(desc(schema.designSystems.updatedAt));

```

This query excludes the `assets` column, which may contain large JSON payloads, while retrieving only the lightweight metadata needed for the list view.

## Indexing for Fast Lookups and Ordering

Indexes accelerate queries that filter (`WHERE`) or sort (`ORDER BY`). In Agent-Native, indexes are defined in the third argument of the `sqliteTable` function in [`packages/core/src/db/schema.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/db/schema.ts).

### Index on updatedAt for Ordering

The `list-design-systems` action sorts results by `updatedAt` in descending order. The schema defines `updatedAtIdx` to support this:

```typescript
export const designSystems = sqliteTable(
  "design_systems",
  {
    id: integer("id").primaryKey(),
    title: text("title").notNull(),
    updatedAt: integer("updated_at").notNull(),
    // ... other columns
  },
  (t) => ({
    updatedAtIdx: index("design_systems_updated_at_idx").on(t.updatedAt),
  })
);

```

This index allows the `ORDER BY desc(updatedAt)` clause to run in O(log N) time instead of performing a full-table sort.

### Index for Access Control Joins

List queries also filter by permissions via the `accessFilter` helper from [`packages/core/src/sharing/access.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/sharing/access.ts). The schema includes an index on the join column to keep permission checks performant:

```typescript
(t) => ({
  updatedAtIdx: index("design_systems_updated_at_idx").on(t.updatedAt),
  shareIdx: index("design_system_shares_idx").on(t.id),
})

```

The `shareIdx` index optimizes the join against the `designSystemShares` table, ensuring access control checks remain fast even when many share rows exist.

## Step-by-Step: Adding Indexed Columns for New List Queries

To add a new filterable or sortable column to an existing list action:

1. **Add the column** to the table definition in [`packages/core/src/db/schema.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/db/schema.ts).
2. **Create an index** in the table’s third argument using `index("name").on(column)`.
3. **Project the column** in the action’s `.select({ ... })` call.
4. **Filter or sort** on that column—the SQLite query planner will use the index automatically.

**Example: Adding a `category` column to `design_systems`**

Update the schema:

```typescript
export const designSystems = sqliteTable(
  "design_systems",
  {
    id: integer("id").primaryKey(),
    title: text("title").notNull(),
    category: text("category"), // new column
    updatedAt: integer("updated_at").notNull(),
  },
  (t) => ({
    categoryIdx: index("design_systems_category_idx").on(t.category), // new index
    updatedAtIdx: index("design_systems_updated_at_idx").on(t.updatedAt),
  })
);

```

Use the indexed column in a list action:

```typescript
const rows = await db
  .select({
    id: schema.designSystems.id,
    title: schema.designSystems.title,
    category: schema.designSystems.category, // projected
  })
  .from(schema.designSystems)
  .where(and(
    accessFilter(schema.designSystems, schema.designSystemShares),
    eq(schema.designSystems.category, args.category) // filter uses index
  ))
  .orderBy(desc(schema.designSystems.updatedAt));

```

## Query Patterns to Avoid

**Inefficient**: Fetching all columns including large blobs:

```typescript
// Bad: pulls every column
const rows = await db.select().from(schema.designSystems);

```

**Efficient**: Projecting only necessary columns:

```typescript
// Good: minimal data transfer
const rows = await db
  .select({
    id: schema.designSystems.id,
    title: schema.designSystems.title,
    isDefault: schema.designSystems.isDefault,
  })
  .from(schema.designSystems);

```

## Summary

- **Project columns explicitly** using `.select({ col: table.col })` to avoid transferring heavy JSON fields like `assets` or `custom_instructions`.
- **Create indexes** on columns used in `WHERE` clauses and `ORDER BY` operations, such as `updatedAtIdx` on the `updated_at` column.
- **Index foreign key columns** used by `accessFilter` joins to maintain fast permission checks as data grows.
- **Verify file paths** like [`packages/core/src/db/schema.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/db/schema.ts) for schema changes and [`templates/videos/actions/list-design-systems.ts`](https://github.com/BuilderIO/agent-native/blob/main/templates/videos/actions/list-design-systems.ts) for query implementation.

## Frequently Asked Questions

### When should I use column projection versus fetching full rows?

Use column projection whenever a list view does not require every column, especially when tables contain large text or JSON blobs. Fetch full rows only for detail views where the entire record is necessary, such as the `get-design-system` action that retrieves the `assets` field after a user selects a specific item from the list.

### How does the `accessFilter` helper utilize indexes?

The `accessFilter` function in [`packages/core/src/sharing/access.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/sharing/access.ts) generates SQL joins against share tables. When these share tables define indexes on their foreign key columns (like `design_system_shares_idx`), the database performs index scans instead of table scans to check permissions, keeping list query latency low even with thousands of share records.

### What happens if I don't index columns used in ORDER BY clauses?

Without an index on columns used for sorting, SQLite performs a full-table scan followed by an in-memory sort operation. As tables grow, this becomes O(N log N) and consumes significant CPU and memory. The `updatedAtIdx` in [`packages/core/src/db/schema.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/db/schema.ts) ensures sorting happens in O(log N) using the index tree.

### Can I use these optimization patterns with PostgreSQL instead of SQLite?

Yes. Agent-Native uses Drizzle ORM, which abstracts database dialects. The `sqliteTable` calls in [`packages/core/src/db/schema.ts`](https://github.com/BuilderIO/agent-native/blob/main/packages/core/src/db/schema.ts) can be replaced with `pgTable` for PostgreSQL deployments, and the `.select()` projection patterns remain identical. However, index naming conventions and specific SQLite functions may require adjustment for PostgreSQL syntax.