Hono Walkthrough — Schema

This is the first hands-on page of the file-by-file migration of the Hono/Drizzle template onto Auther. We start with the schema layer, because identity now lives outside your database.

Here’s the shape of the work. Once you adopt the Shadow-User pattern — where Auther is the identity provider (IdP) and your app keeps only a thin local copy of each user — Auther owns sessions, accounts, credentials, two-factor secrets, and the signing keys. The tables in your app that mirror those become dead weight. So you’ll:

• Delete the tables the IdP now owns (sessions, accounts, verifications, jwks, two_factors).
• Shrink users to a cache — a local projection of the identity claims Auther vends, plus the handful of fields that are genuinely yours.
• Let the compiler find the rest. Removing those exports turns every now-dead reference into a compile error, which becomes your to-do list.

The walkthrough touches four files in packages/db: the schema definition (auth.ts), the barrel export (index.ts), the ID-prefix table (ids.ts), and the Drizzle relations (relations.ts). The next page, Hono Walkthrough — Auth Core, picks up the server code that these schema changes break.

Shrinking auth.ts

The template’s auth.ts defines a full local identity model: a wide users table plus sessions, accounts, verifications, jwks, and two_factors. Under Auther, all of those auxiliary tables disappear and users collapses to a thin local projection.

Before — the current Hono template file:

packages/db/src/schema/auth.ts

export const users = pgTable("users", {
  id: varchar("id", { length: 255 })
    .primaryKey()
    .$defaultFn(() => generatePrefixedCuid(ID_PREFIXES.user)),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  image: text("image"),
  createdAt: timestamp(...).defaultNow().notNull(),
  updatedAt: timestamp(...).defaultNow().$onUpdate(...).notNull(),
  status: text("status").default("active").notNull(),
  deactivatedAt: timestamp(...),
  deactivatedBy: varchar("deactivated_by", { length: 255 }),
  deactivatedReason: text("deactivated_reason"),
  failedLoginAttempts: integer(...).default(0).notNull(),
  lockedUntil: timestamp(...),
  roleSlugs: text("role_slugs").array().default([]).notNull(),
  onboardingCompletedAt: timestamp(...),
  twoFactorEnabled: boolean(...).default(false).notNull(),
});

export const sessions = pgTable("sessions", {...});       // DELETE
export const accounts = pgTable("accounts", {...});       // DELETE
export const verifications = pgTable("verifications", {...}); // DELETE
export const jwkss = pgTable("jwks", {...});              // DELETE
export const twoFactors = pgTable("two_factors", {...});  // DELETE

What changes and why: every table except users is auth machinery the IdP now owns, so it goes. users itself loses every column tied to how a user authenticates and survives only as a read cache of identity. The biggest single change is id (highlighted below): it stops minting its own prefixed CUID via $defaultFn and instead stores Auther’s sub claim — the IdP’s user identifier — verbatim.

After — only the local projection remains:

packages/db/src/schema/auth.ts

import { boolean, pgTable, text, timestamp, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: varchar("id", { length: 255 }).primaryKey(),                // ← Auther's sub, no $defaultFn
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  image: text("image"),

  // App-only fields
  onboardingCompletedAt: timestamp("onboarding_completed_at", { withTimezone: true }),

  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
});

// sessions, accounts, verifications, jwks, two_factors: all removed.

Notice what stays: name, email, emailVerified, and image are kept as a convenient local mirror of Auther’s identity claims, and onboardingCompletedAt is a genuinely app-owned field that has no home in Auther. Everything tied to how a user authenticates — lockout counters, status flags, two-factor enablement, role slugs — moves to Auther.

The schema change produces a Drizzle migration that drops the auxiliary tables and the now-orphaned columns. The generated SQL should look like this:

-- drop auth tables owned by Auther now
DROP TABLE IF EXISTS "two_factors" CASCADE;
DROP TABLE IF EXISTS "jwks" CASCADE;
DROP TABLE IF EXISTS "verifications" CASCADE;
DROP TABLE IF EXISTS "accounts" CASCADE;
DROP TABLE IF EXISTS "sessions" CASCADE;

-- shrink users
ALTER TABLE "users" DROP COLUMN IF EXISTS "status";
ALTER TABLE "users" DROP COLUMN IF EXISTS "deactivated_at";
ALTER TABLE "users" DROP COLUMN IF EXISTS "deactivated_by";
ALTER TABLE "users" DROP COLUMN IF EXISTS "deactivated_reason";
ALTER TABLE "users" DROP COLUMN IF EXISTS "failed_login_attempts";
ALTER TABLE "users" DROP COLUMN IF EXISTS "locked_until";
ALTER TABLE "users" DROP COLUMN IF EXISTS "role_slugs";
ALTER TABLE "users" DROP COLUMN IF EXISTS "two_factor_enabled";

What deliberately does not change are the foreign keys pointing at users.id. Because the id column keeps the same type and value (Auther’s sub is still a string primary key), every existing relationship survives untouched:

Reference	Target	Status
notifications.user_id	users.id	same FK, works unchanged
push_tokens.user_id	users.id	same
notification_preferences.user_id	users.id	same
audit_logs.actor_id	users.id	same
members.user_id	users.id	same (org memberships)
auth_relations.subject_id	—	plain text, no FK, unchanged

Migration hazard

DROP TABLE sessions will fail if live sessions are referenced elsewhere. There are none in this template, but check your branch before running it. Apply this migration as part of the planned cutover, not mid-traffic.

Removing the barrel exports

index.ts re-exports the schema. The line itself does not change — but what flows through it does, because auth.ts now exports only users.

packages/db/src/schema/index.ts

// before
export * from "./auth";   // exports users, sessions, accounts, verifications, jwkss, twoFactors

// after
export * from "./auth";   // exports only users now — same line, different contents

What changed and why: nothing on the line — but auth.ts no longer exports sessions, accounts, and friends, so they vanish from the barrel. Every consumer that imported them is now referencing something that doesn’t exist.

Let the compiler do the inventory

Don’t go hunting for dead references by hand. Anywhere in the codebase that still imports sessions or accounts from @repo/db/schema now produces a compile error — and that error list is a complete, machine-checked inventory of every place the migration breaks. Run the type-checker and treat the output as your to-do list. The next page walks through fixing each one in apps/server.

Pruning ID prefixes

ids.ts holds the prefix table used to mint local CUIDs. Auther now generates user IDs, so the user prefix is dead, along with the prefixes for the auxiliary tables you just deleted. Keep the prefixes your app still owns.

packages/db/src/ids.ts

// before
export const ID_PREFIXES = {
  user: "usr",
  session: "ses",       // UNUSED
  account: "act",       // UNUSED
  verification: "ver",  // UNUSED
  relation: "rel",
  notification: "ntf",
  ...
};

// after
export const ID_PREFIXES = {
  // user prefix unused now — Auther generates IDs
  relation: "rel",
  notification: "ntf",
  ...
};

What changed and why: the user, session, account, and verification prefixes minted IDs for tables that no longer exist (or, for user, IDs that Auther now generates). They’re dead, so they go. The prefixes for tables your app still owns stay put.

Trimming the user relations

Finally, relations.ts declares Drizzle relations from users to the tables that referenced it. Drop the relations to sessions, accounts, and twoFactors — those tables no longer exist — and keep the app-owned ones.

packages/db/src/relations.ts

// before
export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  accounts: many(accounts),
  twoFactors: many(twoFactors),
  notifications: many(notifications),
  memberships: many(members),
  ...
}));

// after
export const usersRelations = relations(users, ({ many }) => ({
  notifications: many(notifications),
  memberships: many(members),
  pushTokens: many(pushTokens),
  notificationPreferences: many(notificationPreferences),
}));

What changed and why: the sessions, accounts, and twoFactors relations point at tables you just deleted, so Drizzle can no longer resolve them — drop the three lines. The app-owned relations (notifications, memberships, push tokens, preferences) are untouched.

With the schema layer reduced to a thin local projection, the database no longer pretends to own identity. The compile errors surfaced by the trimmed exports are the to-do list for the next step: Hono Walkthrough — Auth Core, where you replace the local better-auth instance with JWT verification against Auther.