CRUD API

Auto-generate list, get, create, update, and remove procedures with createCrud()

Prerequisite: Define tables in `gencow/schema.ts` first. Tables that use createCrud() must follow the schema rules below.

createCrud(table) is the recommended way to expose standard data operations. It auto-generates typed procedure.query / procedure.mutation handlers with authentication, realtime invalidation, input validation, and PostgreSQL RLS applied through ctx.db.

Quick Start

Step 1 — Schema (gencow/schema.ts): define the table with id and an optional RLS policy — see Schema.

export const tasks = pgTable("tasks", {
  id: serial("id").primaryKey(),
  title: text(),
  userId: text(),
});

Step 2 — createCrud (gencow/tasks.ts + gencow/index.ts): wire the table into the API.

// gencow/tasks.ts
export const tasksCrud = createCrud(tasks);

// gencow/index.ts
defineApi({ crud: { tasks: tasksCrud } });

Step 3 — React (src/App.tsx): call the generated list procedure.

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const { data: result } = useQuery(api.tasks.list);

result?.data.map(t => <li>{t.title}</li>);

useQuery(api.tasks.list, {}) is equivalent and remains a safe explicit style, especially when sharing examples with older runtimes.

That's it — list, get, create, update, and remove with auth and realtime sync, no boilerplate.

Schema Rules for createCrud()

Important: Your table must follow these rules before you call createCrud(table).

Required: id Column

Every table using createCrud() must have an id column. This is the primary key used by get, update, and remove.

// ✅ serial id (auto-increment number)
id: serial("id").primaryKey(),

// ✅ text id (UUID string — auto-detected by createCrud)
id: text("id").primaryKey(),

// ❌ No id column — createCrud() will throw:
// [createCrud] Table "..." must have an 'id' column.

Why id? This is a universal convention (Convex, Supabase, Prisma all use it). It keeps the API predictable for AI code generation and vibe-coding.

Recommended Columns

Complete Template (Recommended)

// ✅ Recommended pattern — works perfectly with createCrud()
export const tasks = pgTable("tasks", {
    id: serial("id").primaryKey(),
    title: text("title").notNull(),
    description: text("description"),
    status: text("status").default("pending").notNull(),
    userId: text("user_id")
        .notNull()
        .references(() => user.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Tables That Don't Need createCrud()

For join tables, settings, or logs where createCrud() doesn't fit, use custom procedures instead — see Queries and Mutations.

Creating CRUD Endpoints

Instead of manually writing get, list, create, update, remove, register one createCrud export per table:

// gencow/posts.ts
import { createCrud, procedure } from "./runtime";
import { v } from "@gencow/core";
import { eq, ilike, desc } from "drizzle-orm";
import { posts } from "./schema";

export const postsCrud = createCrud(posts, { prefix: "posts" });

// gencow/index.ts
import { defineApi } from "@gencow/core";
import { postsCrud } from "./posts";

export default defineApi({
    crud: { posts: postsCrud },
});

Filtering

Use allowedFilters to whitelist which columns clients may filter on in list. Columns outside the list are rejected.

export const tasksCrud = createCrud(tasks, {
    prefix: "tasks",
    allowedFilters: ["status", "category"],
    searchFields: ["title", "description"],
});

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

// Simple equality
const { data: active } = useQuery(api.tasks.list, {
    filters: { status: "active" },
});

// Multiple filters (implicit AND)
const { data: filtered } = useQuery(api.tasks.list, {
    filters: { status: "active", category: "tech" },
});

// Full-text search (requires searchFields on the server)
const { data: results } = useQuery(api.tasks.list, { search: "meeting" });

Set allowedFilters explicitly for any table where clients can pass filters. For comparison operators (gte, in, ilike) and OR / AND groups, see Core API — Advanced Filters.

Sorting

By default, list sorts by createdAt desc (or id desc when createdAt is missing). Clients pass orderBy and orderDir:

const newest = useQuery(api.tasks.list, { orderBy: "createdAt", orderDir: "desc" });
const byTitle = useQuery(api.tasks.list, { orderBy: "title", orderDir: "asc" });

Restrict sortable columns with sortable — any orderBy outside the list is rejected:

export const tasksCrud = createCrud(tasks, {
    sortable: ["createdAt", "title", "status"],
});

Pagination

list returns { data, total, nextCursor? }. Without a cursor option, pagination is offset-based via page and limit (defaults: page 1, limit 20, max 100):

export const tasksCrud = createCrud(tasks, {
    defaultLimit: 20,
    maxLimit: 100,
});

import { useState } from "react";
import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const [page, setPage] = useState(1);
const { data: result } = useQuery(api.tasks.list, { page, limit: 20 });

// result?.data — current page
// result?.total — total matching rows

For large or live-updating lists, use keyset pagination with cursor. The sort order is fixed by the configured columns — include the primary key as the final segment for stable pages:

export const tasksCrud = createCrud(tasks, {
    cursor: ["createdAt", "id"],
});

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const { data: first } = useQuery(api.tasks.list, { limit: 20 });
const { data: next } = useQuery(api.tasks.list, {
    limit: 20,
    cursor: first?.nextCursor,
    orderDir: "desc",
});

When cursor is configured, page and orderBy are not supported — pass cursor and orderDir only. nextCursor is present when another page exists.

Limiting Generated Endpoints

Expose only the operations you need with methods. Omitted endpoints are not registered on the server and are excluded from gencow/api.ts after codegen:

// Read-only API — no create, update, or remove
export const logsCrud = createCrud(logs, {
    prefix: "logs",
    methods: ["list", "get"],
});

// Append-only feed
export const eventsCrud = createCrud(events, {
    prefix: "events",
    methods: ["list", "get", "create"],
});

Valid values: "list", "get", "create", "update", "remove". Default: all five.

Operation-Level Access Policies

createCrud() accepts an access field for gates that run before any database access. Prefer checks against session data (for example ctx.auth.requireAuth().role) so the gate stays fast.

Return false to reject with Forbidden before the handler touches the database.

export const tasksCrud = createCrud(tasks, {
    access: {
        read: () => true,
        create: (ctx) => ctx.auth.requireAuth().role === "admin",
        update: (ctx) => ctx.auth.requireAuth().role === "admin",
        remove: (ctx) => ctx.auth.requireAuth().role === "admin",
    },
});

Public / Anonymous Access

For dashboards, CMS tools, or other apps that don't require sign-in:

export const articlesCrud = createCrud(articles, { allowAnonymous: true, prefix: "articles" });

After gencow codegen, gencow/api.ts carries that flag on each generated procedure (for example defineProcedureQuery(..., { allowAnonymous: true })). The client picks it up automatically — no extra useQuery options needed:

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const { data: result } = useQuery(api.articles.list);

Use allowAnonymous: true only for non-sensitive data.

Realtime Invalidation

createCrud() wires realtime invalidation for generated list and get queries. By default, list subscribers share one broad bucket for the table: public CRUD uses a public bucket, authenticated CRUD uses an authorized bucket, and the bucket scope is {}.

// Default: list realtime enabled, get invalidation enabled
export const tasksCrud = createCrud(tasks, { prefix: "tasks" });

// Full opt-out: no list realtime and no exact get invalidation
export const batchLogsCrud = createCrud(batchLogs, {
    prefix: "batchLogs",
    realtime: false,
});

// Disable list realtime only; update/remove still invalidate tasks.get({ id })
export const tasksCrud = createCrud(tasks, {
    prefix: "tasks",
    realtime: { listRealtime: false },
});

For multi-tenant lists, configure a scoped bucket. scope(args) maps the list query arguments to a JSON-serializable object, and scopeFromRow({ row, ctx }) maps a written row to the same object so writes only invalidate matching list subscribers.

export const tasksCrud = createCrud(tasks, {
    prefix: "tasks",
    realtime: {
        policy: "authorized",
        resourceIdentity: "tasks",
        scope: (args) => ({ workspaceId: args?.filters?.workspaceId }),
        scopeFromRow: ({ row }) => ({ workspaceId: row.workspaceId }),
        authorize: async ({ ctx, args }) => {
            const user = ctx.auth.requireAuth();
            await assertWorkspaceMember(ctx.db, user.id, args?.filters?.workspaceId);
        },
    },
});

Use policy: "public" only for list results safe for anonymous subscribers. Authorized scoped realtime must provide authorize; scope objects must be plain, non-null, JSON-serializable objects. create invalidates matching list buckets, while update and remove invalidate both matching list buckets and the exact get({ id }) subscription when get is generated. Exact generated get watchers keep the same auth/query contract as the generated get endpoint itself: public CRUD uses a public query grant, protected CRUD uses an authorized query grant.

createCrud() + Custom Procedures Together

export const search = procedure.query
    .name("posts.search")
    .input(v.object({ keyword: v.string() }))
    .handler(async ({ context: ctx, input }) => {
        ctx.auth.requireAuth();
        return ctx.db.select().from(posts)
            .where(ilike(posts.title, `%${input.keyword}%`))
            .orderBy(desc(posts.createdAt));
    });

Next Steps

• Queries — Custom read queries when createCrud() isn't enough
• Mutations — Custom write operations
• Core API — Full createCrud() options reference

Common Patterns for AI Assistants

Table + CRUD Checklist

1. Always include id: serial("id").primaryKey() or id: text("id").primaryKey()
2. Include createdAt: timestamp("created_at").defaultNow().notNull() for sorting
3. Include userId: text("user_id") for user-owned data
4. Register via defineApi({ crud: { tasks: tasksCrud } }) in gencow/index.ts

Public / anonymous access

• Backend: createCrud(table, { allowAnonymous: true, prefix: "articles" })
• Frontend: useQuery(api.xxx.list, {}) — codegen sets allowAnonymous on the generated api.ts entry
• Realtime: generated public CRUD lists subscribe through the public policy; use explicit scoped realtime for multi-tenant public data

Authenticated CRUD (default)

• Backend: createCrud(table, { prefix: "tasks" }) (auth enabled by default)
• Frontend: useQuery(api.xxx.list, {}) (token sent automatically)
• createCrud().list returns { data: T[], total: number, nextCursor?: string } — use .data to access the array
• Set allowedFilters, sortable, and methods to control list behavior and exposed endpoints
• Realtime: generated lists default to one authorized table bucket; use realtime.scope / scopeFromRow to narrow fanout by workspace, owner, or tenant