Schema & database

Your data model is the foundation of a Benmore app. You declare tables in a Prisma schema; the platform creates the database, keeps it in sync as you change the schema, and generates a REST API for every table.

Define your models

Tables live in schema.prisma. Each model becomes a SQLite table with a full REST endpoint. The column names you map with @map are the names used everywhere else — in the API, in flows, and in hooks.

model Contact {
  id        Int      @id @default(autoincrement())
  name      String
  email     String
  status    String   @default("lead")
  companyId Int?     @map("company_id")
  createdAt DateTime @default(now()) @map("created_at")
  userId    Int      @map("user_id")
}

model Company {
  id   Int    @id @default(autoincrement())
  name String
}

That gives you /api/contacts and /api/companies immediately — list, create, read, update, delete, plus pagination, filtering, and batch operations — with no backend code.

Primary keys

The default is an auto-incrementing integer: id Int @id @default(autoincrement()) — fast and small, right for most tables.

For an ID exposed externally (shareable links, public tokens, anything where a sequential number would leak how many rows exist), use a UUID. Declare id String @id @default(uuid()) and the platform emits a TEXT key, generates the UUID server-side on insert, returns the real UUID from your POST, and makes any foreign key that references the table TEXT automatically.

Ownership and timestamps

A user_id column makes a table owner-scoped: it's set automatically to the creating user, and reads, updates, and deletes are filtered to the owner unless you open the table up. A created_at / updated_at pair is maintained for you. Add a deleted_at column and deletes become soft deletes — the row is hidden from reads instead of destroyed, and can be restored.

Querying through the API

Every table endpoint supports filtering, ordering, pagination, and full-text search out of the box:

GET /api/contacts?status=lead            # equality filter
GET /api/contacts?status__in=lead,won    # set membership
GET /api/contacts?orderBy=created_at:desc # ordering
GET /api/contacts?page=2&per_page=50      # offset pagination
GET /api/contacts?cursor=...&limit=50     # keyset pagination
GET /api/contacts?count=true              # true row count, no LIMIT cap
GET /api/contacts?q=acme                  # full-text search (@@fulltext tables)

A POST returns the full inserted row, not just an ID. Optimistic concurrency is built in: send _expected_updated_at on a PATCH and you get a 409 if the row changed since you read it.

Migrations

When you change schema.prisma and push, the platform reconciles your live database to match — adding tables and columns automatically. Migrations are additive and idempotent: existing rows are preserved, and a destructive change (dropping a column) is refused unless you explicitly force it, so you can't lose data by accident.