Data Access Layer — Andrei Ghiorghiu

Drizzle ORM over SQLite, with each service owning its own schema file. A single JSON meta column lets one books table serve every category — with the shape enforced by a declarative YAML rules engine at the operation boundary.

Schema Ownership Follows Services

There is no central models file. Each service owns its tables in a local schema.ts, and the database package simply re-exports them:

// server/src/db/schema.ts
export * from "../services/accounts/schema"
export * from "../services/catalogs/schema"
export * from "../services/books/schema"

export * from "./relations"

Migrations are generated by drizzle-kit and applied on boot when config.migrate is set — so a fresh checkout is one command away from a working database.

One Table, Every Category

Books store their attributes in a single JSON meta column. A fiction book carries { title, author, isbn, genre }; another category can carry entirely different fields — same table, no migration:

// server/src/services/books/schema.ts
export const books = table("books", {
  price: real("price").notNull(),

  catalogId: text("catalog_id")
    .notNull()
    .references(() => catalogs.id, { onDelete: "cascade" }),

  // JSON-stringified metadata (e.g. { title, author, isbn, genre })
  meta: text("meta").notNull().default("{}"),

  id,
  createdAt,
  updatedAt,
})

The catalog names which rule set governs its books:

// server/src/services/catalogs/schema.ts (excerpt)
export const catalogs = table("catalogs", {
  id: text("id").unique().primaryKey().default(uniqId),
  name: text("name").notNull(),
  owner: text("owner").notNull(),
  fee: real("fee").default(0),
  // name of a rules file (e.g. "fiction") used to validate this catalog's books
  rules: text("rules"),
  createdAt,
  updatedAt,
})

The Rules Engine

Schema flexibility without validation anarchy: before a book is written, the operation loads the catalog’s YAML rule set and enforces it. Data, not code — a new category is a new file:

# server/src/rules/fiction.yaml
price:
  min: 0.99
  max: 500
meta:
  fields:
    title:  { required: true, maxLen: 200 }
    author: { required: true, maxLen: 120 }
    isbn:   { required: true, regex: "^[0-9-]{10,17}$" }
    genre:  { enum: [fiction, sci-fi, fantasy, mystery, romance] }

validateItem walks the rules — price bounds, then per-field required / regex / enum / minLen / maxLen / contains — and throws a typed InvalidError with a human-readable message that travels all the way to the UI form:

// server/src/rules/validate.ts (excerpt)
if (rule.regex !== undefined && !new RegExp(rule.regex).test(str)) {
  throw new InvalidError(
    `${prefix}Field "${key}" does not match pattern ${rule.regex}`
  )
}

Enforcement at the Operation Boundary

books/add shows the full picture — authentication, ownership, rules, then the write:

// server/src/services/books/add.ts (excerpt)
if (!io.identity) throw new UnauthenticatedError("Must be authenticated")

const catalog = await db.query.catalogs.findFirst({
  where: eq(db.catalogs.id, data.catalogId),
})
if (!catalog) throw new NotFoundError("Catalog not found")
if (!io.isSystem && catalog.owner !== io.identity.accountId)
  throw new UnauthorizedError("Not the owner of this catalog")

// enforce the catalog's rule set, if any
if (catalog.rules) {
  validateItem(loadRules(catalog.rules), { price: data.price, meta: data.meta })
}

const [record] = await db
  .insert(db.books)
  .values({
    price: data.price,
    catalogId: data.catalogId,
    meta: JSON.stringify(data.meta ?? {}),
  })
  .returning()

The database stays schema-light where the domain is flexible (book metadata) and strict where it matters (ownership, referential integrity with cascade deletes, typed enums on accounts). Validation lives at the boundary where the best error messages can be produced.