How to Implement Access Control with Ownable Columns and Scoped Queries in Agent-Native

Agent-Native implements fine-grained access control by combining ownableColumns() for ownership tracking, companion share tables for explicit grants, and accessFilter() to automatically scope database queries against the current user’s permissions.

Agent-Native, an open-source framework maintained by BuilderIO, provides a declarative authorization pattern that enforces row-level security at the database layer. Rather than manually checking permissions in every action, you define ownable columns on your tables and use scoped query helpers to filter results automatically. This approach centers on three mechanisms: direct ownership via owner_email, visibility scopes (private | org | public), and explicit shares stored in companion tables.

Declaring Ownable Resources with ownableColumns()

To make a resource ownable, spread ...ownableColumns() into your Drizzle table definition and create a matching shares table using createSharesTable(). This pattern is defined in packages/core/src/sharing/schema.ts.

The ownableColumns() utility adds three columns automatically populated by the framework when a resource is created: owner_email, org_id, and visibility. The companion shares table stores per-user or per-organization role grants (viewer | editor | admin).

// src/templates/videos/server/db/schema.ts
import { table, text, ownableColumns, createSharesTable } from "@agent-native/core/db/schema";

export const decks = table("decks", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  data: text("data").notNull(),
  ...ownableColumns(),               // adds ownerEmail, orgId, visibility
});

export const deckShares = createSharesTable("deck_shares"); // explicit grants

Scoping Queries with accessFilter()

The accessFilter() function in packages/core/src/sharing/access.ts generates a Drizzle WHERE clause that enforces the three-layer access model. It evaluates ownership, visibility settings, and explicit share records in a single SQL expression.

According to the source code in access.ts (lines 101–162), the function checks:

• Owner match: owner_email matches the current user and passes the ownerScopeFilter (org-aware)
• Org visibility: visibility = 'org' and the request organization matches the row’s org_id
• Public visibility: Only applied if options.includePublic is true and the registry does not disable public access
• Explicit shares: An EXISTS subquery against the shares table filtered by minRole and organization membership

import { accessFilter } from "@agent-native/core/sharing";
import * as schema from "./db/schema";

export async function listDecks() {
  const rows = await db
    .select()
    .from(schema.decks)
    .where(accessFilter(schema.decks, schema.deckShares));
  return rows;
}

You can require a minimum role by passing the fourth argument. For example, in templates/videos/actions/create-design-system.ts, the filter ensures the user has at least editor rights.

Protecting Write Operations with assertAccess()

For mutations, use assertAccess() to verify the caller’s effective role before proceeding. This function internally calls resolveAccess() (lines 31–48 in access.ts) to load the row, evaluate ownership, visibility, and shares, then throws ForbiddenError if the role is insufficient.

import { assertAccess } from "@agent-native/core/sharing";

export async function updateDeck(deckId: string, payload: Partial<Deck>) {
  // Ensures the caller has at least 'editor' rights
  await assertAccess("decks", deckId, "editor");
  // …perform the update…
}

If you need only the resolved role without throwing, call resolveAccess() directly.

Configuring Custom Access Policies

The sharing registry (packages/core/src/sharing/registry.ts) allows templates to declare custom flags that alter how accessFilter() and assertAccess() behave:

• allowPublic: false: Disables public discovery even if a row’s visibility is set to public
• ownerAccessIgnoresOrg: true: Allows owners to access resources regardless of current organization scope
• requireOrgMemberForUserShares: true: Restricts user shares to only function when the user is a member of the same organization as the resource

These flags are read by accessFilter() (lines 108–112, 172–176, and 212–216) and by resolveAccess() to adjust the authorization logic dynamically.

Complete Implementation Example

Here is an end-to-end implementation for a new "Projects" resource:

Schema definition:

// src/templates/projects/server/db/schema.ts
import { table, text, ownableColumns, createSharesTable } from "@agent-native/core/db/schema";

export const projects = table("projects", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  ...ownableColumns(),
});

export const projectShares = createSharesTable("project_shares");

List action with scoped queries:

// src/templates/projects/actions/list-projects.ts
import { accessFilter } from "@agent-native/core/sharing";
import * as schema from "./db/schema";

export async function listProjects() {
  return db
    .select()
    .from(schema.projects)
    .where(accessFilter(schema.projects, schema.projectShares));
}

Update action with access assertion:

// src/templates/projects/actions/update-project.ts
import { assertAccess } from "@agent-native/core/sharing";

export async function updateProject(projectId: string, data: Partial<Project>) {
  await assertAccess("projects", projectId, "editor");
  // …apply updates…
}

Summary

• Add ...ownableColumns() to every resource table that requires ownership tracking in packages/core/src/sharing/schema.ts
• Create a companion shares table using createSharesTable() to store explicit user and organization grants
• Filter all list queries with accessFilter(resourceTable, sharesTable, ctx?, minRole?, options?) to enforce row-level security automatically
• Guard write actions with assertAccess(resourceType, resourceId, minRole) to prevent unauthorized mutations
• Register custom policies in the sharing registry when you need flags like allowPublic or ownerAccessIgnoresOrg

Frequently Asked Questions

What columns does ownableColumns() add to my table?

The ownableColumns() function adds three columns: owner_email (Text), org_id (Text), and visibility (Enum with values private, org, and public). These columns are automatically populated by the framework when a resource is created, establishing the ownership baseline and default visibility scope.

How does accessFilter handle public visibility?

The accessFilter() function includes public rows only when options.includePublic is set to true and the resource registry does not have allowPublic: false. When these conditions are met, it adds a clause checking visibility = 'public' to the generated SQL query. This allows global read access while still permitting the template to disable public discovery entirely.

Can I require specific roles (like admin) instead of just any access?

Yes, pass the minRole parameter to accessFilter() or assertAccess(). Valid roles are viewer, editor, and admin, with each level implying the previous. For example, passing minRole: 'editor' to accessFilter() will match rows where the user is the owner, has an explicit editor or admin share, or the visibility rules grant default access at that level.

Where should I register custom access control flags?

Register custom flags in packages/core/src/sharing/registry.ts using the listShareableResources() function or the appropriate registration mechanism for your template. Flags like allowPublic, ownerAccessIgnoresOrg, and requireOrgMemberForUserShares are read by the logic in packages/core/src/sharing/access.ts to modify query behavior without changing the underlying schema.

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