Schema

Define your database schema with Drizzle ORM and PostgreSQL RLS

Gencow uses Drizzle ORM combined with PostgreSQL Row-Level Security (RLS) for schema definition. Your schema lives in gencow/schema.ts and uses standard PostgreSQL column types.

Quick Start — Build an App in 3 Steps

New to Gencow? Here's all you need to know:

Step 1: Define data         Step 2: Create API          Step 3: Use in React
──────────────────         ──────────────────         ──────────────────
gencow/schema.ts           gencow/tasks.ts             src/App.tsx

pgTable("tasks", {         import { createCrud }       const result = useQuery(
  id: serial().pk(),           from "./runtime";          api.tasks.list
  title: text(),             import { tasks }           );
  userId: text(),              from "./schema";         result?.data.map(t =>
})                                                       <li>{t.title}</li>
ownerRls(t)                export const {               )
 → auto user isolation        list, get, create,
                             update, remove
                           } = createCrud(tasks);

That's it! You get a complete API with authentication, realtime sync, and pagination — zero boilerplate.

Defining Tables with Access Control

Use standard pgTable() from Drizzle ORM, and apply the ownerRls() policy to automatically isolate data per user.

import { pgTable, text, timestamp, boolean, integer, jsonb, serial } from "drizzle-orm/pg-core";
import { ownerRls } from "@gencow/core";
import { user } from "./generated/auth-schema";

// ✅ ownerRls() applies PostgreSQL Row-Level Security automatically
export const posts = pgTable("posts", {
    id: serial("id").primaryKey(),
    title: text("title").notNull(),
    content: text("content"),
    published: boolean("published").default(false).notNull(),
    views: integer("views").default(0).notNull(),
    metadata: jsonb("metadata"),
    userId: text("user_id")
        .notNull()
        .references(() => user.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (t) => [ownerRls(t)]);
// → ctx.db.select().from(posts) automatically returns only the current user's posts

Public Tables (No Auth Required)

If a table should be accessible to everyone (e.g., global reference data), simply omit the RLS policy:

export const categories = pgTable("categories", {
    id: serial("id").primaryKey(),
    name: text("name").notNull(),
});
// No RLS policy means standard unfiltered access

Schema Rules for createCrud()

⚠️ Important: If you plan to use createCrud(table) (recommended for 90% of apps), your table must follow these rules:

Required: id Column

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

// ✅ 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.

Column Purpose Used By
id ✅ Required — Primary key get(id), update(id), remove(id)
userId Auto-injected on create createCrud auto-sets from auth session
createdAt Default sort order list sorts by createdAt desc
updatedAt Auto-set on update createCrud auto-sets to new Date() 
// ✅ 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(),
}, (t) => [ownerRls(t)]);

Tables That Don't Need createCrud()

For tables like join tables, settings, or logs where createCrud() doesn't fit, use procedure.query / procedure.mutation:

import { procedure } from "./runtime";
import { v } from "@gencow/core";

// ✅ No id needed — not using createCrud()
export const settings = pgTable("settings", {
    key: text("key").primaryKey(),
    value: jsonb("value"),
});

export const getSetting = procedure.query
    .name("settings.get")
    .input(v.object({ key: v.string() }))
    .handler(async ({ context: ctx, input }) => {
        const [row] = await ctx.db.select().from(settings).where(eq(settings.key, input.key));
        return row?.value ?? null;
    });

App Type Patterns — Which Approach to Use

Choose the right pattern based on your app type:

App Type Table Definition Example
Multi-User SaaS pgTable + ownerRls(t) Task manager, Notes app
Admin / Internal Tool Pure pgTable (No RLS) Dashboard, CMS, Crawler

Admin / Internal Tool Pattern

For apps that don't need user authentication (e.g., admin dashboards, internal tools, crawlers), you don't need to specify ownerRls() or authenticate the API endpoints.

// gencow/schema.ts — Admin tool pattern
import { pgTable, text, timestamp, serial } from "drizzle-orm/pg-core";

// ✅ No RLS applied, all data accessible — still needs id for createCrud()
export const articles = pgTable("articles", {
    id: serial("id").primaryKey(),
    title: text("title").notNull(),
    content: text("content"),
    source: text("source"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
});
// gencow/articles.ts — Public CRUD (no auth)
import { createCrud } from "./runtime";
import { articles } from "./schema";

export const { list, get, create, update, remove } = createCrud(articles, { allowAnonymous: true });
// Frontend — use { public: true } option
const result = useQuery(api.articles.list, {}, { public: true });
result?.data.map(article => <ArticleCard article={article} />)

Creating CRUD Endpoints

Instead of manually writing get, list, create, update, remove operations, use createCrud — it auto-generates typed procedure.query / procedure.mutation handlers with authentication, realtime push, and RLS built in.

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

// ✅ One line → list, get, create, update, remove with auth + realtime
export const { get, list, create, update, remove } = createCrud(posts);

// For public (no auth) endpoints:
// export const { list, get, create, update, remove } = createCrud(posts, { allowAnonymous: true });

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));
    });

Relationships

Foreign Keys (User Ownership)

Always use userId with .references() for data that belongs to a user:

userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),

The onDelete: "cascade" ensures that when a user is deleted, all their data is automatically cleaned up.

One-to-Many

A post has many comments:

export const comments = pgTable("comments", {
    id: serial("id").primaryKey(),
    content: text("content").notNull(),
    postId: integer("post_id")
        .notNull()
        .references(() => posts.id, { onDelete: "cascade" }),
    userId: text("user_id")
        .notNull()
        .references(() => user.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
}, (t) => [ownerRls(t)]);

Applying Schema Changes

Quick Sync (Development)

Push changes instantly without migration files:

gencow db:push

This applies changes instantly — automatic schema sync with zero config. Best for rapid prototyping.

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

If you are an AI coding assistant generating Gencow code, follow these patterns:

Table Schema 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. Use ownerRls(t) for multi-user apps

Admin / Internal Tool (no user auth)

  • Use standard pgTable without ownerRls
  • Backend: createCrud(table, { allowAnonymous: true })
  • Frontend: useQuery(api.xxx.list, {}, { public: true })

Multi-User SaaS (with user auth)

  • Use pgTable with (t) => [ownerRls(t)]
  • Backend: createCrud(table) (auth enabled by default)
  • Frontend: useQuery(api.xxx.list) (token sent automatically)
  • createCrud().list returns { data: T[], total: number } — use .data to access array
Project StructureQueries