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

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 and 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, 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 demonstrates the pattern:

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.

Index on updatedAt for Ordering

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

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. The schema includes an index on the join column to keep permission checks performant:

(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.
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:

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:

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:

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

Efficient: Projecting only necessary columns:

// 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 for schema changes and 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 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 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}