lyzr-architect-pg

Per-app PostgreSQL database layer for Architect-generated Next.js apps — the Postgres/Drizzle successor to the Mongoose-based lyzr-architect package. Provides owner-scoped repositories (application-level row security), auth scaffolding (register/login/logout/me with JWT + httpOnly cookie), and drop-in React auth components.

• Server (lyzr-architect-pg) — DB singleton, auth middleware/handlers, scopedRepo
• Schema (lyzr-architect-pg/schema) — Drizzle pg-core builders + column helpers; apps import table-definition tooling only from here
• Client (lyzr-architect-pg/client) — "use client" React components (API-identical to lyzr-architect/client)

Quick reference — what to use, when, from where

Three import roots only: lyzr-architect-pg (server), lyzr-architect-pg/schema (tables/columns), lyzr-architect-pg/client (React).

| You need to… | Do this | |---|---| | Auth route files | export { handleRegister as POST } from 'lyzr-architect-pg' (+ handleLogin/handleLogout, handleMe as GET). Per-route subpaths lyzr-architect-pg/routes/auth/{register,login,logout,me} also work. | | Protect a data route | Wrapper: export const GET = authMiddleware(async (req) => { … scopedRepo(t) … }). Guard: const auth = await authMiddleware(req); if (!auth) return 401; … scopedRepo(t, auth.user.id) … | | Read/write owned rows | scopedRepo(table) (wrapper) or scopedRepo(table, userId) (guard). Methods: findMany(opts?), findOne(where?), insert(v) / create(v), update(where, v), delete(where?), count(where?), .system(). | | Fetch from the client | const { authFetch } = useAuth() → authFetch('/api/...') (sends Authorization: Bearer). Never bare fetch for authed routes — the cookie is blocked in a cross-origin preview iframe. | | No-auth / single-owner | optionalAuth + scopedRepo(t).system() (or getDb()), no ProtectedRoute. |

authMiddleware accepts both forms: authMiddleware(handler) (wrapper) and authMiddleware(req) → { user: { id, email }, userId } | null (guard). scopedRepo(table[, userId]) and repo.create (alias of insert) are supported so common idioms just work.

Environment contract

| Variable | Required | Description | |---|---|---| | DATABASE_URL | yes | Postgres connection string, e.g. postgres://user:pass@host:5432/db?sslmode=require. TLS is enabled automatically when the URL contains sslmode=require. | | APP_JWT_SECRET | yes | HMAC secret for signing auth JWTs (HS256, 7-day expiry). | | DATABASE_PROVIDER | convention | Set to postgres in generated apps so tooling knows which package variant is in use. |

The connection pool is a lazy singleton (max: 3, prepare: false — safe behind transaction-mode poolers like PgBouncer/Supavisor).

Defining schema

Generated apps import only from lyzr-architect-pg/schema:

// db/schema.ts
import {
  pgTable, text, integer, index,
  timestamps, ownerUserId, generateId,
} from 'lyzr-architect-pg/schema';

export const todos = pgTable('todos', {
  id: text('id').primaryKey().$defaultFn(generateId),
  title: text('title').notNull(),
  priority: integer('priority'),
  owner_user_id: ownerUserId(),
  ...timestamps,
}, (t) => [
  // Always index owner_user_id — every scoped query filters on it.
  index('todos_owner_idx').on(t.owner_user_id),
]);

• timestamps — created_at / updated_at (timestamptz, defaultNow, updated_at auto-bumped on update), parity with the old package's Mongoose timestamps.
• ownerUserId() — the text('owner_user_id').notNull() column scopedRepo requires.
• generateId() — 24-char hex ids, format-compatible with Mongo ObjectId strings.
• users — the package-managed auth table (Postgres table _users). Include it in your drizzle-kit schema so migrations create it; never query it directly.

Push schema with drizzle-kit (drizzle.config.ts pointing schema at your schema file(s) plus lyzr-architect-pg's exported users table, dialect: 'postgresql', dbCredentials.url = process.env.DATABASE_URL).

Owner-scoped data access

scopedRepo(table) replaces the old Mongoose rlsPlugin. Every read/update/delete is ANDed with owner_user_id = <current user>; inserts stamp owner_user_id from the auth context and ignore any caller-supplied value (spoof-proof).

import { authMiddleware, scopedRepo } from 'lyzr-architect-pg';
import { eq } from 'lyzr-architect-pg/schema';
import { todos } from '@/db/schema';

const repo = scopedRepo(todos);

export const GET = authMiddleware(async () => {
  const items = await repo.findMany({ orderBy: todos.created_at, limit: 50 });
  return Response.json({ items });
});

export const POST = authMiddleware(async (req) => {
  const body = await req.json();
  const [item] = await repo.insert({ title: body.title }); // owner stamped automatically
  return Response.json({ item });
});

Methods: findMany({ where?, orderBy?, limit?, offset? }), findOne(where?), insert(values | values[]), update(where, values), delete(where?), count(where?). All return awaitable handles that also expose .toSQL() for inspection.

Deny-by-default semantics (parity with the old rlsPlugin):

• Called outside authMiddleware/optionalAuth → throws No auth context — wrap the route in authMiddleware or use .system().
• Under optionalAuth with no token (userId: null) → reads match nothing; inserts throw.
• repo.system() → unscoped variant for cron/admin jobs (no context needed; caller supplies owner_user_id on insert).
• scopedRepo refuses to wrap the auth tables (users/sessions/_users/_sessions) and tables missing owner_user_id.

Auth

Route handlers (App Router), identical request/response shapes to lyzr-architect:

// app/api/auth/[action]/route.ts — or one file per action
import { handleRegister, handleLogin, handleLogout, handleMe } from 'lyzr-architect-pg';

export const POST = handleRegister; // /api/auth/register  { email, password, name? }

• handleRegister / handleLogin — bcrypt (cost 12), HS256 JWT ({ userId, email }, 7d), sets httpOnly auth_token cookie (SameSite=None; Secure in production, Lax in dev). Generic 409 on duplicate email (no account enumeration).
• handleLogout — clears the cookie. handleMe — returns { user } or { user: null }.
• authMiddleware(handler) / optionalAuth(handler) — wrap route handlers; token from auth_token cookie or Authorization: Bearer; populates the async-local auth context consumed by scopedRepo, and sets req.userId / req.userEmail.
• Primitives exported: signToken, verifyToken, hashPassword, verifyPassword, runWithContext, getRequestContext, getCurrentUserId.
• DB access: getDb() (Drizzle), getSql() (raw postgres.js tagged-template client), initDB() (parity alias).

Client components

Unchanged from lyzr-architect/client — they only call /api/auth/*:

'use client';
import { AuthProvider, LoginForm, RegisterForm, ProtectedRoute, UserMenu, useAuth } from 'lyzr-architect-pg/client';

AuthProvider({ children, basePath = '/api/auth' }), LoginForm({ onSuccess?, onSwitchToRegister?, className? }), RegisterForm({ onSuccess?, onSwitchToLogin?, className? }), ProtectedRoute({ children, loadingFallback?, unauthenticatedFallback?, loginPath = '/login' }), UserMenu({ className? }), useAuth().

v2 upgrade path: native Postgres RLS

scopedRepo enforces ownership at the application layer (same trust model as the old Mongoose plugin). v2 will move enforcement into the database using native ROW LEVEL SECURITY:

1. ALTER TABLE ... ENABLE ROW LEVEL SECURITY + a USING (owner_user_id = current_setting('app.user_id', true)) policy per owned table.
2. The pool switches to setting app.user_id per request (SET LOCAL inside a transaction).
3. scopedRepo keeps the same API and becomes a thin convenience layer — the owner_user_id column, the authMiddleware context, and all call sites carry over unchanged.

Because the schema (dedicated owner_user_id text not null column) and the middleware context are already RLS-shaped, the v2 migration requires no application-code changes in generated apps.

Development & publishing

npm install
npm run build     # tsup — dual CJS+ESM + .d.ts for ., ./schema, ./client
npm test          # vitest — no live DB needed (SQL-shape tests via .toSQL())

Publish:

npm version <patch|minor|major>
npm publish       # prepublishOnly runs clean + build + test

License

MIT