---
title: Collections and rules
description: Real tables, and rules compiled into the SQL that reads them.
section: Data
order: 1
---

# Collections and rules

<p class="lead">A collection is a real SQLite table. Its rules are compiled into the same query that fetches its rows, not checked afterwards.</p>

## Fields

The field types are `text`, `number`, `bool`, `email`, `url`, `date`, `json`, `select`, `relation` and `file`. A collection may declare composite and unique indexes. A collection with `"extends": "_users"` holds app-specific profile fields, keyed by the same id as the user.

## Five rules

```json title="schema.json"
"rules": {
  "list":   "@request.auth.id != null",
  "view":   "@request.auth.id != null",
  "create": "author = @request.auth.id",
  "update": "author = @request.auth.id",
  "delete": "author = @request.auth.id || @request.auth.staff = true"
}
```

- No rule means superusers only; `""` means anyone.
- A list rule filters: callers get only the rows they may see, and totals stay correct.
- View, update and delete return 404 rather than 403, so rules can't be used to probe whether a row exists.
- After a write, the row is re-checked inside the transaction. A write that would put a row out of the writer's own reach is rolled back.

Fields can have their own rules too, e.g. who may read a grade or change a status. They're enforced in the same SQL.

## The API

```http title="HTTP"
GET    /api/collections/todos/records?filter=done=false&sort=-created
POST   /api/collections/todos/records
PATCH  /api/collections/todos/records/:id
DELETE /api/collections/todos/records/:id

// server-sent events, filtered by your rules
GET    /api/realtime?subscribe=todos
```

```ts title="Browser"
import { Sluurp } from "sluurp";
const sluurp = new Sluurp();
await sluurp.collection("todos").create({ title: "Milk", author: sluurp.auth.id });
const open = await sluurp.collection("todos").list({ filter: "done = false" });
```

Filters are parsed and compiled to SQL with bound parameters. A filter referencing an unknown field is rejected before any SQL is built.

## Views

A view collection's rows come from a SQL `SELECT`: a report that joins or groups other collections, read like any collection. You can list, filter, sort, `expand` and aggregate it, and its own list and view rules decide who sees what. It's read-only: creating, updating or deleting its records is refused.

```json title="schema.json"
{
  "name": "absence_counts",
  "type": "view",
  "query": "SELECT user AS id, count(*) AS absences FROM absences GROUP BY user",
  "rules": { "list": "@request.auth.staff = true", "view": "@request.auth.staff = true" }
}
```

- Write collection names in the query; Sluurp maps them to their tables.
- The query must be one `SELECT` that only reads, and each row needs an `id`.
- Fields come from the columns the query returns: numbers where the values are numbers, text otherwise. Declare a field in `schema` to give it a type of your own, such as a relation, so `expand` works through it.
- The query reads the underlying tables directly, not through their rules. The view's own rules are what apply, so write them for whoever the view is meant for.
- A collection a view reads can't be deleted while the view exists. To change a view's query, delete the view and make it again.
- A view isn't kept current by sync: a synced list of it is its rows as they were when read.

## Projects

Each project is a separate database file, and no query spans two. An app is pinned to one project, so its functions can't touch another app's data.