database

Managed Data Store

• The Cake20 website uses managed PostgreSQL and Redis.
• Website DB and only when there is an actual model or view schema in server/db Create and connect Prisma Client.
• DB can be created with only an empty DB folder, enum·type, SQL function, seed, and migration. It doesn't create it.
• Even if you remove the last model/view, existing DB data is not automatically deleted. It just stops connecting to the site.
• Redis automatically issues a dedicated ACL account for each website, and only your own website key can access it.
• The website code and Prisma use the DATABASE_URL injected by Cake20 instead of a real address or account.
• package.json data accepts auto, local, or server. When omitted, the internal project.data value is normalized to auto.
• auto uses managed PostgreSQL on Cake20 and the local file area and installed PostgreSQL when running through the local CLI.
• Setting data to server also makes local execution connect to PostgreSQL through DATABASE_URL. The selected data mode remains in package.json.

Getting started with DB model

• export const Name = {...} format to server/db/note.db.ts Create a data model.
• Cake20 internally wraps the exported field object with z.model(), so the user can There is no need to write z.model({...}) yourself.
• When adding a table, use server/db/user.db.ts, server/db/post.db.ts The default is to create a separate *.db.ts file for each table.
• If there are many files, the required depth is You can organize them into folders.
• Models with relationships can also reference each other with z.ref("Model") without merging files. there is. Only enums and views specific to that table can be placed in the same file.
• The field name is written as an object key, and the field type and constraints are written as a z method chain.
• z.id() declares the BigInt primary key and autoincrement default value at once.
• Cake20 converts it to a hidden .prisma file immediately upon saving and creates a Prisma Client.
• Websites that only have an existing .prisma will automatically create a .db.ts with the same name.
• *.db.ts reads the TypeScript AST statically rather than executing it, so imports, function calls, Calculation formulas and dynamic values ​​cannot be used.

export const State = z.enum(["draft", "published"]);

export const Note = {
  id: z.id(),
  title: z.string().min(1).max(100),
  active: z.boolean().default(true),
  state: z.ref("State"),
  meta: z.json(),
  createdAt: z.date().defaultNow().timestamp()
};

Edit row data

• A boolean column contains only true, false, 1, or 0 regardless of case. You can enter it and it will be saved as an actual boolean value.
• Nullable text and varchar columns are saved as DB NULL when the input is completely empty.
• Nulls entered into text and varchar are stored as strings rather than special values.
• To directly select NULL and DEFAULT, use the N and D buttons to the right of the cell.

Data shared by the editor, review, and production

• The editor, Design Preview, build/debug review, and production Release share one website PostgreSQL database, Redis namespace, and files storage.
• Database and cache editing changes current website data immediately; there is no separate disposable test copy.
• The review URL isolates code and the process, not data. Scheduled tasks remain disabled during review.
• MCP target: test is a compatibility alias and accesses the same database and Redis keys as target: production.
• Before an MCP database write, Cake20 creates a dump and restores it on failure. Use create_site_backup before broad schema or data changes that need a named restore point.

Open DB DUMP and Save DUMP

• Opening DUMP and saving DUMP in the left database panel are not the selected table It applies to the entire current database of the website.
• If no database exists, Open DUMP creates web-{website ID} and restores the PostgreSQL dump.
• If there is an existing DB, first remove all tables and data and then dump it. The existing DB is automatically backed up and restored if it fails.
• DUMP save saves the entire current DB as a PostgreSQL dump file.
• The file name time applies the timezone in package.json.
• The file name format is YYYYMMDD-HHmmss-{website ID}.dump.

Model seeds and initial data

• Each *.db.ts seed returns rows for that file and never calls db directly.
• A single-model file returns an array. A multi-model file returns an object keyed by model name.
• Every row needs an explicit id or unique field. Cake20 validates rows against the latest schema and safely upserts only seed files whose checksum changed.
• Removing a row from a seed never deletes existing operating data.
• New *.db.ts files include an empty export const seed = async () => {}; and Cake20 updates app/preview/<Model>.json from the current returned rows.
• Cake20 does not periodically scan the operating database or extract five rows for Preview, and it never injects Preview JSON into the website database.
• Use optional seed.sql.ts only for procedural initialization across models. It runs after model seeds and reruns when changed, so keep it idempotent.
• Declare password hashes with z.password().hashFrom("pwdText"). pwdText is a write-only plaintext input that Cake20 hashes and removes.
• Never put real personal data, passwords, auth tokens, Secrets, or external API dependencies in Preview samples.

// server/db/setting.db.ts
export const Setting = {
  id: z.string().id().max(30),
  locale: z.string().max(10),
  signup: z.boolean().default(true)
};

export const seed = async () => [
  { id: "default", locale: "en", signup: true }
];

Reusable DB function

• *.sql.ts functions at any depth under server/db are registered in db.sql.
• Function names must be unique across the entire server/db, regardless of folder.
• API, route, hook, event, and seed.sql.ts call db.sql.<name>() without imports.
• Common execution logic for reading or writing DB is not shared/utils Place it in server/db/*.sql.ts.
• shared/utils/*.ts is loaded before SQL function registration and is also loaded in the browser. It is used. If you reference db.sql.*, the save will be rejected with an instruction error.
• seed.sql.ts is procedural initialization after model seeds and is excluded from auto-registration.
• If you export the same function name even if the folder or file is different, a build error will occur.
• Test your SQL functions immediately in the editor with func and args from config.sample.
• The args values ​​are passed to the function's parameters in the order they were written to the object.
• Runtime values ​​can only be exported to named functions and test configs.
• Type and interface exports are also allowed.
• Don't run DB operations at the top level of the file.

// server/db/user.sql.ts
/**
 * User query example to be reused in multiple APIs.
 * Call db.sql.findUser(email) from anywhere on the server.
 */
export const config = {
  sample: {
    func: "findUser",
    args: { email: "[email protected]" }
  }
};

export const findUser = (email: string) => {
  return db.user.findFirst({ where: { email } });
};

// server/api/users.post.ts
export default async () => {
  return db.sql.findUser("[email protected]");
};

field type

• Supports string, int, bigint, number, decimal, boolean, date, json and bytes.
• array() creates a PostgreSQL scalar array or relational list.
• ref("Name") refers to another model or enum type.
• timestamp() is PostgreSQL timestamp(0), timestampTz() is timezone Specifies timestamptz(0), including: Different precision from 1 to 6 Pass numbers only when necessary.
• Native types without short methods are like db("Decimal", 12, 2). You can use the existing db(name, ...args) syntax as is.
• Field types currently supported by Prisma and PostgreSQL but without short methods are: You can preserve it with raw("...").

export const Product = {
  id: z.id(),
  name: z.string(),
  stock: z.int(),
  viewCount: z.bigint(),
  rating: z.number(),
  price: z.decimal().db("Decimal", 12, 2),
  active: z.boolean(),
  publishedAt: z.date().timestamp(),
  happenedAt: z.date().timestampTz(),
  options: z.json(),
  data: z.bytes().nullable(),
  tags: z.string().array()
};

min and max

• The min/max of strings and bytes checks the length, and the range of values ​​for number types.
• array().min/max checks the number of array items, so we write it after array().
• For date's min/max, use an ISO date string or millisecond timestamp.
• string().max(n) is also reflected in Prisma's hidden @db.VarChar(n).
• Other min/max are checked before overlapping with DB Client's create and update.
• min/max fields use sets or direct values ​​instead of relative updates like increment or push.
• Raw SQL bypasses this check, so use the db Client for regular CRUD.

export const Product = {
  name: z.string().min(2).max(100),
  stock: z.int().min(0).max(100000),
  price: z.decimal().min(0).max(999999.99),
  tags: z.string().array().min(1).max(10),
  openedAt: z.date().min("2025-01-01T00:00:00+09:00")
};

nullable, optional and default

• nullable() allows nulls, and Prisma's ? Convert to field.
• optional() only means undefined or input omitted like Zod and does not allow DB NULL.
• nullish() is a combination of optional and nullable, and in DB it is ? It becomes a field.
• If you need to create a DB value when you omit input, use default(), defaultNow(), id(), or Use updated().
• defaultRaw("uuid()") specifies the raw default value that Prisma will interpret.
• optional() itself does not create a DB default, so do not use it alone on required columns.

export const Account = {
  id: z.id(),
  nick: z.string().max(40).nullable(),
  bio: z.string().max(500).nullish(),
  role: z.string().default("member"),
  createdAt: z.date().defaultNow(),
  updatedAt: z.date().updated()
};

enum and view

• The enum is declared with export const Name = z.enum([...]) and referenced with z.ref.
• The view is declared as z.view({...}), and the actual view creation SQL is managed in migration.
• Grammarly, multiple models can be placed in one file, but for maintenance purposes, *.db.ts file is used as the basis. Enums used by multiple models are It can be separated into separate files.

export const State = z.enum(["draft", "published"]);

export const Post = {
  id: z.id(),
  state: z.ref("State").defaultRaw("draft")
};

export const PublishedPost = z.view({
  id: z.bigint().id(),
  state: z.ref("State")
});

Single Index

If you add index() to a field, it will be converted to @@index in the hidden Prisma schema.

• index() creates an index consisting of one field.
• unique() is not an index, but a unique constraint that prohibits duplicate values.
• Complex/advanced indexes are declared with attr("@@index([...])") after the model.

export const User = {
  id: z.id(),
  email: z.string().index()
};

relationship

• ref("Model") refers to another model or enum type.
• A ref with array() becomes a list relation field in Prisma.
• relation("authorId") produces fields: [authorId], references: [id].
• A complex relationship is written as relation(["localA", "localB"], ["remoteA", "remoteB"]).
• Advanced options such as relationship name and referential action are written with attr("@relation(...)").

export const User = {
  id: z.id(),
  posts: z.ref("Post").array()
};

export const Post = {
  id: z.id(),
  authorId: z.bigint(),
  author: z.ref("User").relation("authorId")
};

Advanced Prisma properties

• map("column_name") specifies the actual DB column name of the field.
• attr("@...") is a short method like a named relation or onDelete. Field properties that cannot be expressed are passed as is.
• attr("@@...") after the model passes a composite unique, composite index and table map.
• Cake20 automatically wraps generic model objects. The @@ attribute of the model itself is Use the existing z.model({...}).attr(...) format only in required high-level declarations.
• The attr string must be the current Prisma grammar and pass Prisma checks when saved.

export const Member = z.model({
  id: z.id(),
  tenantId: z.bigint(),
  email: z.string().max(200).map("email_address"),
  owner: z.ref("User").attr(
    '@relation("Owner", fields: [ownerId], references: [id], onDelete: Cascade)'
  ),
  ownerId: z.bigint()
})
  .attr("@@unique([tenantId, email])")
  .attr("@@index([tenantId, email])")
  .attr('@@map("members")');

DB usage

db is used in server/api, server/routes, server/hooks, server/tasks and shared functions. It can be used automatically. Other than nullable input normalization Types and query behavior follow Prisma and PostgreSQL conventions.

export default async () => {
  return db.note.findMany({
    orderBy: { id: "desc" }
  });
};

Handling empty values ​​and NULLs

• Any nullable scalar ? The field has the top value "", undefined, null without distinction Save as DB NULL.
• Required Json stores empty values ​​as {}, Json? like any other nullable field Save as DB NULL. {} entered in Json? is separated from NULL.
• Nullable fields omitted from create are initialized to NULL.
• If you omit the key in update, it will keep the existing value, but if you enter the key and leave it as undefined, When passed, it changes to NULL.
• When searching, null, undefined, and empty strings are not ORed together. where: { field: null } only one is used.
• NULL search in Json? also uses regular instead of Prisma.DbNull or Prisma.JsonNull Use null. In required Json, null condition matches empty object {}.
• It does not convert nested values ​​inside the Json object, only the values ​​of the Json field itself. Normalize. This rule does not apply to raw SQL.

export const Profile = {
  id: z.id(),
  nick: z.string().nullable(),
  score: z.int().nullable(),
  meta: z.json(),
  extra: z.json().nullable()
};

// Nick, score, and extra are saved as DB NULL, and meta is saved as {}.
await db.profile.create({
  data: { nick: "", score: undefined, meta: null, extra: "" }
});

// Empty values ​​in a nullable field are searched for as single null.
await db.profile.findMany({ where: { nick: null, extra: null } });

// In Json?, {} is the actual JSON value as distinct from DB NULL.
await db.profile.findMany({ where: { extra: {} } });

Migration and DB permissions

• When building, the difference between the current DB and Prisma model is converted into SQL and the checksum and application history are recorded in the internal cake20.migrations table.
• A dump is created only when the actual DB structure and the Prisma structure to be built are different or when there is migration to be applied. If the backup fails, the build aborts.
• The difference in the Prisma model is that changes that may result in data deletion are automatically applied as transactions.
• The DROP SQL you wrote yourself must have the -- cake20:allow-destructive marker to be executed.
• DB·schema initialization and TRUNCATE command are not allowed in migration.
• The server execution process uses a dedicated role that only allows CRUD of its own DB. local uses a separate PostgreSQL instance per website.