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. Table definitions live under your backend rootDir (default gencow/) and use 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.userId)         export const tasks =        )
 → auto user isolation        createCrud(tasks);

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

Step 2 is covered in detail in the CRUD API guide. You get a complete API with authentication, realtime sync, and pagination — zero boilerplate.

Defining Tables

Gencow schemas are standard Drizzle ORM pgTable() definitions. The CLI discovers them from the schema option in `gencow.config.js` — a single path or an array of paths (default: ["./gencow/schema-auth.ts", "./gencow/schema.ts"]).

For a small app, keeping every table in gencow/schema.ts is fine. As the app grows, split tables by domain into separate files (for example schema-posts.ts, schema-comments.ts) and list each entry file in schema:

gencow/
├── schema-auth.ts      ← auth table boundary (from gencow init)
├── schema.ts           ← optional barrel re-export
├── schema-posts.ts
└── schema-comments.ts

// gencow.config.js
export default {
    rootDir: "./gencow",
    schema: [
        "./gencow/schema-auth.ts",
        "./gencow/schema-posts.ts",
        "./gencow/schema-comments.ts",
    ],
};

Tables are plain Drizzle — no Gencow-specific wrappers for the column definitions themselves:

// gencow/schema-posts.ts (or gencow/schema.ts in a simple app)
import { pgTable, text, timestamp, serial } from "drizzle-orm/pg-core";

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

This is a complete, valid Gencow table. Foreign keys, indexes, enums, and relations use the same Drizzle APIs you would in any Postgres project. For column types, relations, and schema patterns, see the Drizzle docs:

• SQL schema declaration — pgTable, constraints, indexes
• PostgreSQL column types — text, integer, jsonb, timestamp, and more
• Relations — relations() for typed joins

Apply schema changes with gencow db:push (development) or Drizzle migrations in production — see Applying Schema Changes below.

Row-Level Security (RLS)

Row-Level Security (RLS) lets PostgreSQL filter which rows each database session can read or write. Gencow injects the signed-in user's session into ctx.db, so RLS policies declared in your schema apply automatically in procedures and createCrud() — no manual .where(eq(table.userId, user.id)) for basic owner isolation.

RLS does not lock down a table — it filters which rows inside the table each session can see or change. Without an RLS policy, ctx.db queries can return and mutate all rows. Add ownerRls or tableRls on tables where callers should only touch a subset of rows.

Defining Tables with Access Control

Use standard pgTable() from Drizzle ORM, and apply the ownerRls() policy in the table's third callback 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 "./schema-auth";

// ✅ 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.userId)]);
// → 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

How It Works

1. You declare policies in gencow/schema.ts using ownerRls(), tableRls(), or Drizzle's pgPolicy() directly.
2. At request time, Gencow maps the auth session to PostgreSQL session variables (for example userId → app.user_id, organizationId → app.organization_id).
3. Every query and mutation through ctx.db runs with those variables set, so PostgreSQL enforces your policies.

import { procedure } from "./runtime";
import { posts } from "./schema";

// No manual userId filter — RLS on the posts table handles isolation
export const list = procedure.query.name("posts.list").handler(async ({ context: ctx }) => {
    return ctx.db.select().from(posts);
});

Drizzle pgPolicy Under the Hood

ownerRls() and tableRls() are convenience builders — they compile to Drizzle's standard `pgPolicy()` definitions from drizzle-orm/pg-core. Policies are attached in the pgTable extra-config callback, the same way you would write them by hand. gencow db:push / drizzle-kit generate emit normal PostgreSQL CREATE POLICY statements.

Prefer ownerRls / tableRls for the common cases. When you need full control — custom USING / WITH CHECK expressions, policy names, or rules Drizzle helpers don't cover — use pgPolicy directly:

import { pgTable, pgPolicy, serial, text } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { rlsContextKey } from "@gencow/core";
import { user } from "./schema-auth";

export const tasks = pgTable(
    "tasks",
    {
        id: serial("id").primaryKey(),
        title: text("title").notNull(),
        userId: text("user_id")
            .notNull()
            .references(() => user.id, { onDelete: "cascade" }),
    },
    (t) => [
        pgPolicy("tasks_select", { for: "select", using: sql`${t.userId} = ${rlsContextKey("userId")}` }),
        pgPolicy("tasks_insert", { for: "insert", withCheck: sql`${t.userId} = ${rlsContextKey("userId")}` }),
        pgPolicy("tasks_update", {
            for: "update",
            using: sql`${t.userId} = ${rlsContextKey("userId")}`,
            withCheck: sql`${t.userId} = ${rlsContextKey("userId")}`,
        }),
        pgPolicy("tasks_delete", { for: "delete", using: sql`${t.userId} = ${rlsContextKey("userId")}` }),
    ],
);

rlsContextKey("userId") expands to current_setting('app.user_id', true) — the same GUC ownerRls() uses. For typed keys, use rlsContextKeys<AppRls>() (as in tableRls examples below). Keys must match what you return from defineAuth.rls.mapSessionToContext.

Note: ownerRls() also registers table metadata used by createCrud() (for example auto-injecting userId on create). If you hand-write pgPolicy for owner isolation, you lose that metadata unless you add equivalent procedure logic yourself.

ownerRls() — Single-User Rows

For the common case where each row belongs to one user, use ownerRls(). It is a preset built on tableRls() that matches the row's owner column to the signed-in user from the auth session — equivalent to the pgPolicy example above, in one line:

(t) => [ownerRls(t.userId)]

createCrud() auto-injects userId on create when the column exists and ownerRls is applied — see CRUD API.

tableRls() — Multi-Tenant and Custom Rules

For org-scoped data, role-based rules, or any policy beyond a single owner column, use tableRls() directly. It composes PostgreSQL policies from session keys you map in defineAuth.rls.mapSessionToContext — for example matching organizationId on each row to the active organization in the session.

import { pgTable, serial, text } from "drizzle-orm/pg-core";
import { tableRls, rlsContextKeys } from "@gencow/core";

type AppRls = { userId: string; organizationId: string };
const ctx = rlsContextKeys<AppRls>();

export const projects = pgTable(
    "projects",
    {
        id: serial("id").primaryKey(),
        organizationId: text("organization_id").notNull(),
        name: text("name").notNull(),
    },
    (t) => [tableRls(t).matchColumn(t.organizationId, ctx("organizationId")).build()],
);

Configure session → context mapping in your auth setup so organizationId (and other keys) are available to PostgreSQL on every request. See Authentication for defineAuth and RLS context.

ctx.db vs ctx.unsafeDb

// ✅ Default — RLS filters rows automatically
await ctx.db.select().from(tasks);

// ⚠️ System-plane handle — use only in audited admin/system handlers
await ctx.unsafeDb.select().from(tasks);

Avoid ctx.unsafeDb unless the handler is an explicit admin or system path. Anonymous procedures that use ctx.unsafeDb or raw SQL are blocked by deploy audit unless annotated with a gencow-allow-unsafe-db comment — see the SECURITY.md file generated by gencow init.

Choosing a Pattern

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.userId)]);

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

• CRUD API — Auto-generate list, get, create, update, remove from your tables
• Queries — Custom read queries when createCrud() isn't enough
• Mutations — Custom write operations
• Authentication — Auth setup and mapSessionToContext for custom RLS

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() (required for createCrud)
2. Include createdAt: timestamp("created_at").defaultNow().notNull() for sorting
3. Include userId: text("user_id") for user-owned data
4. Use ownerRls(t.userId) for multi-user apps
5. Use tableRls() when rows are scoped by org, team, or custom session keys

Multi-User SaaS

• pgTable with (t) => [ownerRls(t.userId)]
• Foreign key: userId → user.id with onDelete: "cascade"
• CRUD and custom procedures use ctx.db — never add redundant owner .where() for basic isolation

Admin / Internal Tool

• Standard pgTable without ownerRls
• See CRUD API for allowAnonymous and frontend { public: true } options