Custom Tables

Custom tables let you add application-specific data to the same database as your commerce data, with foreign key constraints to core tables like sellable_entities and customers.

Define the table

Create a schema file in your app. Import core tables from @porulle/core/schema to reference them in foreign key constraints.

src/schema/reviews.ts

import { index, integer, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { sellableEntities, customers, organization } from "@porulle/core/schema";

export const reviews = pgTable("reviews", {
  id: uuid("id").defaultRandom().primaryKey(),
  // Every table holding tenant data needs this column. See below.
  organizationId: text("organization_id")
    .notNull()
    .references(() => organization.id, { onDelete: "cascade" }),
  entityId: uuid("entity_id")
    .notNull()
    .references(() => sellableEntities.id, { onDelete: "cascade" }),
  customerId: uuid("customer_id")
    .references(() => customers.id, { onDelete: "set null" }),
  rating: integer("rating").notNull(),
  title: text("title"),
  body: text("body"),
  status: text("status", { enum: ["pending", "approved", "rejected"] })
    .notNull()
    .default("pending"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
}, (table) => ({
  orgIdx: index("idx_reviews_org").on(table.organizationId),
}));

Use onDelete: "cascade" when the row should be deleted with its parent. Use onDelete: "set null" when the row should survive parent deletion — the FK column must be nullable in that case.

Name the column organizationId or you get no tenant isolation

Porulle scopes a custom table to a tenant by looking for an organizationId column. A table without one is invisible to that machinery, so every row is readable by every organization — and nothing warns you, because a table with no tenant column looks deliberately global.

Add it to any table holding per-merchant data. Genuinely global lookup tables — currency codes, country lists — correctly omit it.

Register the table

Add the table to the schema array in commerce.config.ts:

commerce.config.ts

import { defineConfig } from "@porulle/core";
import { reviews } from "./src/schema/reviews.js";

export default defineConfig({
  schema: [{ reviews }],
  // ...
});

Add to drizzle.config.ts

Add the schema file path so drizzle-kit push and drizzle-kit generate include it:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";
import { getSchemaFiles } from "@porulle/core";

export default defineConfig({
  dialect: "postgresql",
  schema: [
    ...getSchemaFiles(),
    "./src/schema/reviews.ts",
  ],
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL ?? "postgres://localhost:5432/my_store",
  },
});

Push the schema to apply the new table:

bunx drizzle-kit push --config drizzle.config.ts

For production, generate migration files instead:

bunx drizzle-kit generate --config drizzle.config.ts
bunx drizzle-kit migrate --config drizzle.config.ts

Add routes

There are two surfaces, and they differ in one way that matters.

| surface | database handle | tenant scoping | | --- | --- | --- | | config.routes | kernel.database.scoped | automatic, per request | | config.routes | kernel.database.db | raw, unscoped; warns on access | | a plugin’s PluginContext | ctx.database.db | automatic, per request |

config.routes is the simplest way to add an endpoint. Use kernel.database.scoped for organization-owned tables: it resolves the organization from the request and constrains select, insert, update and delete on tables with an organizationId column, including queries with no WHERE clause. kernel.database.db remains available for intentional cross-organization work, but it is raw and emits a warning when accessed.

The plugin handle has the same query-builder guarantees through ctx.database.db.

Register route handlers via the routes field in commerce.config.ts. The callback receives the Hono app instance and the kernel:

src/routes/reviews.ts

import type { Hono } from "hono";
import { eq, desc } from "drizzle-orm";
import { reviews } from "../schema/reviews.js";
import { sellableEntities } from "@porulle/core/schema";
import { resolveOrgIdForCommerce } from "@porulle/core";
import type { CommerceConfig } from "@porulle/core";
import type { PostgresJsDatabase } from "drizzle-orm/postgres-js";

function db(raw: unknown): PostgresJsDatabase<Record<string, unknown>> {
  return raw as PostgresJsDatabase<Record<string, unknown>>;
}

export function reviewRoutes(app: Hono, kernel: unknown) {
  const k = kernel as { database: { db: unknown; scoped: unknown }; config: CommerceConfig };

  app.post("/api/reviews", async (c) => {
    const body = await c.req.json();
    const drizzle = db(k.database.scoped);

    // The scoped handle stamps the request organization on the row.
    const organizationId = resolveOrgIdForCommerce(c.get("actor"), k.config);

    const [review] = await drizzle
      .insert(reviews)
      .values({
        organizationId,
        entityId: body.entityId,
        customerId: body.customerId ?? null,
        rating: body.rating,
        title: body.title ?? null,
        body: body.body ?? null,
      })
      .returning();

    return c.json({ data: review }, 201);
  });

  app.get("/api/reviews/:entityId", async (c) => {
    const entityId = c.req.param("entityId");
    const drizzle = db(k.database.scoped);

    const rows = await drizzle
      .select({
        review: reviews,
        productSlug: sellableEntities.slug,
      })
      .from(reviews)
      .innerJoin(sellableEntities, eq(reviews.entityId, sellableEntities.id))
      .where(eq(reviews.entityId, entityId))
      .orderBy(desc(reviews.createdAt));

    return c.json({ data: rows });
  });
}

Wire the routes in your config:

commerce.config.ts

import { defineConfig } from "@porulle/core";
import { reviews } from "./src/schema/reviews.js";
import { reviewRoutes } from "./src/routes/reviews.js";

export default defineConfig({
  schema: [{ reviews }],
  routes: (app, kernel) => {
    reviewRoutes(app, kernel);
  },
  // ...
});

If you need routes from multiple files, call each registration function inside the same routes callback.

Writing raw SQL

Both handles are full Drizzle instances, so db.execute() is available when the query builder cannot express what you need — a window function, a recursive CTE, a tsvector ranking.

Raw SQL is never tenant-scoped, on either surface. The scoping proxy works by wrapping select / insert / update / delete; it cannot parse arbitrary SQL to find the right place to add a predicate. So a db.execute() on a plugin’s ctx.database.db is exactly as unscoped as one on kernel.database.db.

import { sql } from "drizzle-orm";

const organizationId = resolveOrgIdForCommerce(c.get("actor"), k.config);

const rows = await drizzle.execute(sql`
  SELECT entity_id, AVG(rating)::float AS average
  FROM reviews
  WHERE organization_id = ${organizationId}
  GROUP BY entity_id
`);

Two rules keep this safe:

1. Filter by organization in every statement. There is no safety net here.
2. Interpolate values, never fragments. ${organizationId} inside a sql template becomes a bind parameter, so it is never parsed as SQL. Reach for sql.raw() only for strings your own code produced — a validated column name, never anything from a request.

// ✘ user input through sql.raw is an injection
await drizzle.execute(sql`SELECT * FROM reviews WHERE title = ${sql.raw(userInput)}`);

// ✓ user input as a bind parameter
await drizzle.execute(sql`SELECT * FROM reviews WHERE title = ${userInput}`);

Use the plugin system for reusable tables

If the same table will be used across multiple projects, package it as a plugin. Plugins define schema, hooks, and routes together, and are published as standalone packages. See Build a Loyalty Plugin for a complete example.

Related

• Configuration Reference — all defineConfig options
• Plugin Architecture — why plugins are config transforms
• TypeScript Patterns — type-safe DB access with PluginDb