# simple-db.pilely.app — hosted table/record database for your registered Pilely apps Base URL: `https://simple-db.pilely.app`. Every operation is a `POST` to a route below. This README is the only manual: `GET https://simple-db.pilely.app/README.md`, public, no credential needed. The platform directory of every service README is `https://pilely.app/README.md`. ## When to use - The user wants a hosted database for their Pilely app: storing and querying app records without running a server of their own. - One database per registered app, exposed as a table/record HTTP API. `{app_id}` in every path is the UUID the app got when it was registered on pilely.app; this service never creates apps, so the app must be registered before it can get a database here. - Files (images, attachments, documents) are a column type: a table declares a `file` column, a file is attached to a record when the record is created or updated, and the record's own access rules decide who uploads, downloads, replaces and deletes it. The bytes go straight between the caller and the storage host, never through this service. See `## Notes` → `### File columns`. - There is no hosted SQL engine and no Postgres to connect to; an app's own code never opens a database connection. An app that needs SQL, joins or transactions runs its own database elsewhere — pilely.app does not host one — and custom validation, computation or third-party calls mean a backend app. See `## Notes`. ## Auth - Every call needs a signed-in caller: send `Authorization: Bearer ` — your own token for this service (your owner surface), or the token the db's own app holds for the caller. A signed-out caller, an agent, and every caller a route does not serve get the route's uniform `404`. How to sign in and get a token: `https://pilely.app/README.md`. - ONE exception: a table marked `anon_read: true` answers `records/list` and `records/{record_id}/get` to SIGNED-OUT visitors too, through the db's own app (the app's anonymous credential). There is no anonymous write of any kind. - A **backend app** (an `@simple_server` box of the db's app, calling with its `PILELY_BACKEND_TOKEN`) is the app acting as itself, never its owner. It reaches a table only as an `app` member (`subject_type: "app"`, the app's uuid) of the group the action needs — `read_group` (or `mutate_group`) to read, `write_group` to create, `mutate_group` (or, under `mutate_scope: "own"`, `write_group` on its own rows) to change — and a `null` group admits no backend app. It never creates, deletes or lists databases, never changes a table, its access or its notifications, and is never exempt from `deny_group`. Its records carry the APP's id as `_submitter_user_id` (`### Submitter fields on every record`). - `apps/{app_id}/create` and `apps/{app_id}/delete` answer the owner with their own token for this service or through the db's own app, and refuse the owner with the uniform `404` through any OTHER app. `apps/list` through an app lists only that app's own database. - Every table-scoped route (`tables/*`, `records/*`) instead checks the calling app directly: the owner (and every non-owner) is refused the instant a call arrives through a DIFFERENT app than the db's own. Each route section states its exact axis. - One more caller through another app is served, internal to pilely.app: the support-ticket account, through the support staff's app, on the database of the customer-ticket app it owns (ticket images). Every other caller through another app is refused. - The owner is the account that owns the registered app. Owner-only routes answer every other caller, and every unknown or non-UUID id, with the same uniform `404`: a denial is indistinguishable from nonexistence. A `404` never tells a page why; see `## Notes` → `### The uniform 404`. ### Table access groups Access is decided per table by two `@simple_group` references — `read_group` gates get/list, `write_group` gates create; each a group nanoid or `null` — an optional `anon_read` boolean, and five optional row-level settings (`read_scope`, `audience_column`, `mutate_scope`, `mutate_group`, `deny_group`) whose defaults change nothing: - `null` — every logged-in user, but ONLY through this db's own app. In `simple_env` a `null` group means the opposite — nobody but the owner (`https://simple-env.pilely.app/README.md` → `## Auth`) — so set each service's groups on purpose; here, "only me" is an empty group (`### An empty group means "only me"`). - a group nanoid — only members of that group (an ACTIVE `@simple_group` owned by you; manage membership at `https://simple-group.pilely.app/README.md`), again only through the db's own app. - `anon_read: true` — get/list additionally open to SIGNED-OUT visitors, through the db's own app only. Live the moment you set it on any `public` app (on a `protected`/`private` app no anonymous credential can exist, so it is inert). Optional — omitting it means closed — and it cannot coexist with a non-null `read_group` or with `read_scope: "own"` (a `400`). Anonymous rows omit `_submitter_user_id`; `_submitter_handle` stays. - `read_scope` — `"all"` (default) or `"own"`. On `"all"` every caller `read_group` admits gets and lists every record. On `"own"` a non-owner `read_group` admits gets and lists only the records they submitted plus the records whose audience column holds their user id; any other record is the uniform `404` on get and is left out of every list. - `audience_column` — `null` (default) or the name of a declared `text` column holding the user id a record is addressed to. Set only at `tables/create`; it never changes (to address records on an existing table, create a new table). Being named in it grants read on a `read_scope: "own"` table, never update or delete. - **Audience rule**, on every table with an audience column whatever its `read_scope`: a value written to the column by `records/create` or `records/update` is `null` or a lowercase hyphenated user id (the form `_submitter_user_id` takes), else `400`. A non-owner — a `mutate_group` member included — may write only `null` or their own user id; any other user id is a `400`. You may name any user, and that user can then get and list the record on a `read_scope: "own"` table. A backend app names no user: it may write only `null`. - `mutate_scope` — `"owner"` (default) or `"own"`. On `"own"` a non-owner may update and delete the records they submitted, when `write_group` admits them at the time of the call (`null` or a member) and they are not in `deny_group`; any other record is the uniform `404` and stays unchanged. So setting `write_group` to an empty group also stops these edits. - `mutate_group` — `null` (default) or a group nanoid. Members, through the db's app, read every record whatever `read_group` and `read_scope` say, and update and delete any record without needing `write_group`. They create per `write_group`, and the audience rule applies to them. - `deny_group` — `null` (default) or a group nanoid. Members get the uniform `404` on `records/create`, `records/update` and `records/delete` of the table — `mutate_group` members included: deny wins. Their reads are unchanged. You are never denied (a backend app is, when its app is a member), and an archived or unknown group denies nobody. When membership cannot be checked, the write answers `500 internal` (retry); it is never let through. - **`null` polarity of the two new groups:** for `mutate_group` and `deny_group`, `null` means NO group — nobody extra may change records, nobody is denied. That is the opposite of `read_group` / `write_group`, where `null` means every logged-in user. A group value follows the same rule as the other two: an ACTIVE `@simple_group` you own, a nanoid of ASCII letters/digits, at most 64 chars; an empty string is a `400`. - You, the owner, always have full access to every record from your own surfaces (your own token for this service, or this app) — but NOT from inside other apps — and you are never denied. - Creating tables, changing schema, sorts (`sorts/set`), access or the new-row email (`notify/set`): owner only. Updating/deleting records: you, `mutate_group` members, and under `mutate_scope: "own"` each author on their own records. There is never an anonymous write. - A submitter `read_group` does not admit cannot read the created record back (unless they are in `mutate_group`). - A record a caller may not see or change is the uniform `404`: the same status and body as an unknown record id (`## Notes` → `### The uniform 404`). - `records/list` is eventually consistent: a record just created can take a moment (normally under a second) to appear in a list, a non-owner's own-rows list on a `read_scope: "own"` table and lists filtered on `_submitter_user_id` or on the audience column included. `records/{record_id}/get` with `consistent: true` sees it at once. - A non-owner's own-rows list on a `read_scope: "own"` table, like every `where` or `eq` condition (on `_submitter_user_id` and on the audience column too) other than a range on the sorted column, reads the table's records in order and returns the ones that match, so it is billed for every record it reads, and on a big table a page may hold fewer than `limit` records, or none, while `next_cursor` is non-null (`## Notes` → `### Filter` and `### Billing`). - Group MEMBERSHIP is managed in `@simple_group`, not here. Adding or removing members there changes who passes a table's gate with no call here — `read_group`, `write_group`, `mutate_group` and `deny_group` alike: normally on the very next request, and within 5 seconds at worst. So a ban (adding someone to `deny_group`) stops their writes within 5 seconds, and removing them lets them write again within the same delay. Access changes made with `access/set` take effect on the very next request, for records stored before the change too. - Until this host's table registry has its row-level access migration, `tables/create` and `access/set` accept the five row-level settings only at their defaults (`"all"`, `null`, `"owner"`, `null`, `null`), so a `tables/list` answer sent back works, and answer any other value with `400 bad_request` ("row-level access is not available yet"); `eq` on `_submitter_user_id` is then a `400` unknown filter column. Once the migration is live the settings are accepted and enforced, with no other step. Every table answer carries the five settings either way. ## Limits | Limit | Value | |---|---| | Serialized `fields` per record (create, update) | 16 KB (`400`) | | `records/list` page size | 1–100 rows, default 50 | | `tables/list` and `apps/list` page size | 1–100 rows, default 50 | | `records/list` condition (`where`, `eq` and the caller's own-rows rule together) | renders to at most 4 KB and 300 operators (DynamoDB's expression limits; over is a `400`) | | `records/list` `where` `in` | 1–100 values (`400`) | | Sorts per table (`sorts`) | 0–3, creation order included; each usable ascending and descending | | A `text` sort | orders by the value's first 900 bytes | | `records/create` `key` | 1–200 characters from `A–Z a–z 0–9 _ - . : @ / +` (`400`) | | `records/update` `inc` | up to 64 keys (`400`) | | `records/update` `if` | up to 64 keys and 16 KB serialized (`400`) | | Table and column names | `^[a-z][a-z0-9_]{0,63}$` | | Group nanoid | ASCII letters/digits, at most 64 chars | | File per `file` field | 2 GB (2,147,483,648 bytes) as the storage host received it (`413 file_too_large` at attach) | | `upload/start` `content_type` | 1–255 visible ASCII characters (`400`) | | Upload link (`upload_url`) | valid 5 minutes; an upload never attached is deleted 1 hour after its link expires, and its `upload_id` is then a `400` | | Download link (`url`) | valid 5 to 10 minutes | | Storage | every record counts toward the DB owner's account storage allowance by its billed size, `(1 + number of sorts) × (S + 100)` bytes for a record of stored size `S` (the record and one index copy per sort of its table, plus 100 bytes of overhead each; `2 × (S + 100)` on a table with only creation order, `S + 100` on a table with no sorts), weighed into pilely bytes by pilely.app's billing service; the allowance is in pilely GB, a pricing unit, not a file size; a `records/create`, or a `records/update` that grows its record, past the allowance is `402 storage_exceeded`. A file counts by the size the storage host holds for it, from its attach until it is removed; an upload never attached never counts | | Traffic | every call is charged to the DB owner's traffic balance; once it is used up every route answers the owner `503 out_of_traffic_credits` and everyone else the uniform `404` | | Over the storage allowance | once the DB owner's stored data is over their storage allowance, for 7 days every caller but the owner gets the uniform `404` and the owner may only list (`apps/list`, `tables/list`, `records/list`, `records/{record_id}/get`), download (`files/{column}/download`, and `records/list` or `records/{record_id}/get` with `file_urls: true`) and delete (`apps/{app_id}/delete`, `records/{record_id}/delete`); after the 7 days download is refused too. Every other owner call answers `402 storage_over_quota`, whose reason says what the owner may still do: during the 7 days it names the days left and list, download and delete; after them only list and delete. Nothing is deleted; it all clears as soon as the owner is back within the allowance | This service sets no request-rate limit of its own, for signed-in callers and signed-out visitors alike. To cap how often your app's database is called, add a `@simple_limiter` policy with `host: "simple-db.pilely.app"` and a `path` under `/apps//` (`https://simple-limiter.pilely.app/README.md`); root refuses a call over it with `429 rate_limited` and a `Retry-After` header before the call reaches this service. Such a policy counts only calls made through that app, and never you, the app's owner, whatever credential you use. ## Errors JSON errors are `{"ok": false, "code": "…", "reason": "…"}`; markdown errors are a short `# Request failed ()` page with the reason, carrying the same status. A `409` adds one field to the JSON where the table below names one, and its markdown reason names the same `id` or version. Codes shared across the routes; a row that names routes applies to those routes only: | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body, a missing `Content-Type: application/json` on a route that takes a body, or a top-level body field the route does not define on `records/create`, `records/get`, `records/update`, `records/delete`, `files/{column}/upload/start` or `records/{record_id}/files/{column}/download` (a misspelled option is refused, never ignored); the route lists its own field rules | | 401 | `bad_forwarded_identity` | the forwarded identity headers reached this service missing or malformed (always JSON) | | 402 | `storage_exceeded` | the DB owner's account storage allowance cannot hold what a `records/create`, or a `records/update` that grows its record, would add; for a file — `upload/start` with no room left, or attaching a file the allowance cannot hold — shown to the owner only, every other caller gets the uniform `404` | | 402 | `storage_over_quota` | the DB owner's stored data is over their storage allowance and this call is not a list, a delete or (during the first 7 days) a download — a `records/list` or `records/{record_id}/get` with `file_urls: true` signs download links, so it counts as a download — shown to the owner only, every other caller gets the uniform `404`. The reason names what the owner may still do: during the first 7 days the days left and list, download and delete; after them only list and delete. Delete data or buy storage to continue | | 404 | `not_found` / `app_not_found` | the uniform 404: unknown or non-UUID id, no database yet, not permitted (a record the table's row-level settings hide from you or do not let you change, a write by a `deny_group` member included), calling through another app, or a caller the route does not serve (an app's anonymous credential where anonymous callers are not admitted, an agent, a backend app outside its groups); also every caller but the owner while the owner has no traffic balance left or is over their storage allowance | | 409 | `key_exists` | `records/create` with a `key` the same submitter already holds in this table; nothing is created. Extra field `id`: the existing row's id | | 413 | `file_too_large` | `records/create` or `records/update` attaching an upload over 2 GB; nothing changes and the upload stays usable for another attempt | | 409 | `version_conflict` | `records/update` or `records/delete` with an `if_version` that is not the row's `_version`; nothing changes. Extra field `current_version`: the row's `_version` | | 409 | `condition_failed` | `records/update` whose `if` does not hold; nothing changes. No extra field: the answer names no column and no value | | 429 | `rate_limited` | over a `@simple_limiter` policy the app's owner set on this host, which counts only calls made through the app and never the owner; answered by root with a `Retry-After` header (see `## Limits`) | | 500 | `internal` | the operation failed, or a write's `deny_group` membership could not be checked — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner has no traffic balance left — shown to the owner only; top up to continue | | 503 | `quota_unavailable` | the storage allowance could not be checked before a `records/create`, a growing `records/update`, an `upload/start` or a file's attach — retry shortly | A `409` comes only after every check that answers `400`, `401`, `402`, `404` or `503` has passed. A caller the route refuses, a row the caller may not change, and a row that does not exist get the uniform `404` whatever well-formed conditions the call sends, never a `409` (a malformed body is a `400` before any access check); a call that would fail both the storage check and a condition answers `402`. A `GET` (or any method other than `POST`) on a route below, and any unknown path, answers an empty-body `404`. Who pays for each call: see `## Notes` → `### Billing`. ## Response format Make these calls with an HTTP client (`curl` or your runtime's equivalent), never a browser. Every route answers `POST` only. Markdown comes back by default; send `Accept: application/json` for the JSON envelope `{"ok": true, …}`. Both forms carry the same status code. In markdown answers a `null` `read_group` / `write_group` reads `everyone`, an `anon_read` table reads `anyone (anon_read)` on the read axis, and a `null` `mutate_group`, `deny_group` or `audience_column` reads `none` (never `everyone`). A markdown table answer follows its access line with `New-row email: off`, `each (at most every 10 minutes)` or `daily (UTC)`, and a `Sorts:` line naming each sort with its state (`none (stored order)` for a table with no sorts). ## Routes | Route | Purpose | Who may call | |---|---|---| | `POST /apps/list` | page through your apps that have a database here | a person (scoped to your apps; through an app, only that app's) | | `POST /apps/{app_id}/create` | create the app's database | the app owner, with their own token for this service or through the app (never through another app) | | `POST /apps/{app_id}/delete` | destroy the app's database and everything in it | the app owner only, with their own token for this service or through the app (never through another app); anyone else — whatever the body — gets `404` | | `POST /apps/{app_id}/tables/create` | create a table with typed columns and an access policy | the owner, with their own token for this service or through the app | | `POST /apps/{app_id}/tables/list` | page through the app's tables, schemas and access settings | the owner, with their own token for this service or through the app | | `POST /apps/{app_id}/tables/{table}/columns/add` | add a column; schema changes are add-only | the owner, with their own token for this service or through the app | | `POST /apps/{app_id}/tables/{table}/access/set` | set the table's read/write groups, `anon_read` and row-level settings | the owner, with their own token for this service or through the app | | `POST /apps/{app_id}/tables/{table}/notify/set` | email yourself when other people add rows to the table (`each`, `daily`) or stop (`off`) | the owner, with their own token for this service or through the app | | `POST /apps/{app_id}/tables/{table}/sorts/set` | replace the table's sorts — the orders `records/list` reads it in | the owner, with their own token for this service or through the app | | `POST /apps/{app_id}/tables/{table}/records/create` | insert a record, or create it once per `key` | `write_group` through the db's app (a person, or the app's backend as an `app` member), never `deny_group`; the owner | | `POST /apps/{app_id}/tables/{table}/records/list` | page through records in a declared sort (newest or oldest first by default), filtered with `where`, or count them | `read_group` through the db's app (on `read_scope: "own"` only the records they submitted and those addressed to them); `mutate_group` through the db's app; the app's backend as an `app` member of either; the owner; signed-out visitors on `anon_read` tables | | `POST /apps/{app_id}/tables/{table}/records/{record_id}/get` | read one record, strongly consistent on request | `read_group` through the db's app (on `read_scope: "own"` only the records they submitted and those addressed to them); `mutate_group` through the db's app; the app's backend as an `app` member of either; the owner; signed-out visitors on `anon_read` tables | | `POST /apps/{app_id}/tables/{table}/records/{record_id}/update` | update one record: set, increment, on condition of its version or column values | the owner, from their own surfaces; through the db's app, `mutate_group` on any record and, on `mutate_scope: "own"`, `write_group` on their own records; never `deny_group` | | `POST /apps/{app_id}/tables/{table}/records/{record_id}/delete` | delete one record, on condition of its version; its files are deleted with it | the owner, from their own surfaces; through the db's app, `mutate_group` on any record and, on `mutate_scope: "own"`, `write_group` on their own records; never `deny_group` | | `POST /apps/{app_id}/tables/{table}/files/{column}/upload/start` | get a link to upload one file straight to storage, for a `file` column | whoever may create records in the table or update records in it (`write_group` or `mutate_group` through the db's app, never `deny_group`); the owner | | `POST /apps/{app_id}/tables/{table}/records/{record_id}/files/{column}/download` | get a download link for the file a record holds | whoever may read the record (as `records/{record_id}/get`), signed-out visitors on `anon_read` tables included | ### POST /apps/list **Page through your apps that have a simple_db database**, newest first. #### Auth A person; the list is always scoped to the apps YOU own, so it never needs a group or an app id. With your own token for this service it lists every one of them. Through an app it lists only that app's own database — none when the app has no database of yours — and never has a `next_cursor`. #### Request Optional JSON body (`Content-Type: application/json`). No body at all, or `{}`, is the first page; every field inside it is optional: - `limit` — number, 1..100, default 50. Outside that range is a `400`. - `cursor` — string, the previous page's `next_cursor`, verbatim. A malformed value is a `400`. A body that is sent but does not parse, or is sent without `Content-Type: application/json`, is a `400`, never read as the first page. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps/list \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"limit": 50}' ``` #### Response `200`. Apps are ordered newest first (by `created_time_stamp`, then `app_id`). JSON: ```json { "ok": true, "apps": [ { "app_id": "", "owner_handle": "", "created_time_stamp": 1734112233445 } ], "next_cursor": "1734112233445:" } ``` - `app_id` — string UUID, the app's registered id; use it in every other URL. - `owner_handle` — string, your handle (JSON only). - `created_time_stamp` — number, epoch ms when the database was created. - `next_cursor` — string when more apps exist past this page, otherwise `null`. Pass it back as `cursor` for the next page; following it until it is `null` returns each of your apps exactly once. An empty list is a `200` with `"apps": []` and `"next_cursor": null`, never a 404. The markdown form lists one bullet per app with its `app_id` and creation timestamp (no `owner_handle`) and a pointer to its `tables/list`, then the next `cursor` when there is one, or a "no databases yet" line when you own none. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | a body that does not parse, or a body without `Content-Type: application/json`; `limit` outside 1..100; malformed `cursor` | | 404 | `app_not_found` | called with an app's anonymous credential | | 503 | `out_of_traffic_credits` | your account has no traffic balance left; top up | #### When to use The user asks which of their apps already have a hosted database here. Explore an app's tables next with `POST /apps/{app_id}/tables/list`. ### POST /apps/{app_id}/create Create the **hosted database for an app you own**. #### Auth You must be the app's owner, with your own token for this service or through this app itself — never from inside another app, and never as a backend app (a box of this app included). Anyone else, the owner through another app, and any unknown or non-UUID id all get the same `404 app_not_found`. #### Request No body. The only input is `{app_id}` in the path: the UUID the app got when it was registered on pilely.app. The app must already be registered **before** it can get a database here; this service never creates apps. - One database per app; a second create answers `409 db_already_exists`. Retrying after a failed create is safe — it finishes the job and answers `200`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//create \ -H "Authorization: Bearer " ``` #### Response `200`. JSON: ```json { "ok": true, "app": { "app_id": "", "owner_handle": "", "created_time_stamp": 1734112233445 } } ``` - `app_id` — string UUID, the app you just gave a database. - `owner_handle` — string, the owner's handle (JSON only). - `created_time_stamp` — number, epoch ms. The markdown form confirms the `app_id` and walks you through the first table and record (`tables/create`, `records/create`, `records/list`). #### Errors | Status | `code` | When | |---|---|---| | 404 | `app_not_found` | unknown or non-UUID `app_id`, not the owner, calling through another app, or called with an app's anonymous credential | | 409 | `db_already_exists` | this app already has a database | | 500 | `internal` | creation failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | your account has no traffic balance left; top up | #### When to use The user wants to attach a hosted database to their registered Pilely app. Next, create a table: `POST /apps/{app_id}/tables/create`. ### POST /apps/{app_id}/delete Delete the **hosted database of an app you own**, with everything in it. #### Auth You must be the app's owner, with your own token for this service or through this app itself — never from inside another app, and never as a backend app (a box of this app included). Anyone else, the owner through another app, and any unknown or non-UUID id all get the same `404 app_not_found` — whatever body they send. When an account is deleted, Pilely's own clean-up deletes that account's databases as them. #### Request `{app_id}` in the path is the app's UUID. The body is optional (`Content-Type: application/json` when sent): ```json { "pile_row_exists": true } ``` - No body at all is the defaults. A body sent without `Content-Type: application/json`, or that is not valid JSON, is a `400` (to the owner; anyone else gets the `404`). - A body over 2 MiB, or one that cannot be read, is refused with `404 app_not_found` — even for the owner — and nothing is deleted. - `pile_row_exists` — boolean, default `true`. Say `false` only when the app has ALREADY been unregistered on pilely.app, which is the case during an account deletion: with no pile row left, ownership is decided against this service's own record of who created the database instead of against the pilely.app registry. **This is irreversible.** Every table, every record and the database file itself are removed; nothing is archived and nothing can be restored. The app itself is untouched — only its database here goes, and the storage its records counted toward your allowance is freed. Every new-row email still waiting for its tables is dropped: none is sent afterwards. Deleting twice is safe: an app with no database answers `404`, and a delete that failed halfway finishes on a retry. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//delete \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"pile_row_exists": true}' ``` #### Response `200`. JSON: ```json { "ok": true, "app_id": "" } ``` - `app_id` — string UUID, the app whose database is now gone. The markdown form confirms the same thing and points back at `/apps/{app_id}/create` for starting over. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | you own the app and the body was sent but is not valid JSON, or was sent without `Content-Type: application/json` | | 404 | `app_not_found` | unknown or non-UUID `app_id`, no database for it, not the owner, calling through another app, called with an app's anonymous credential, or a body over 2 MiB or unreadable (the owner included) | | 500 | `internal` | deletion failed, or the database's own record was gone by the time it was removed (nothing is reported deleted) — retry in a few seconds | | 503 | `out_of_traffic_credits` | your account has no traffic balance left; top up | #### When to use The user wants to throw away an app's hosted database here, or an account is being erased. ### POST /apps/{app_id}/tables/create Create a **table** in your app's database. #### Auth You must be the app's owner, with your own token for this service or through this db's app — never from inside somebody else's app, and never as a backend app. Everyone else gets the uniform `404 not_found`. The app's database must already exist (`POST /apps/{app_id}/create`). #### Request JSON body (`Content-Type: application/json`). `table`, `columns`, `read_group` and `write_group` are required; every other key is optional: - `table` — string, lowercase snake_case (`^[a-z][a-z0-9_]{0,63}$`). Must not name an existing table. - `columns` — array of `{"name", "type"}`, at least one entry. `name` follows the same `^[a-z][a-z0-9_]{0,63}$` rule; `id`, `pk`, `created_sort` (storage attribute names) and `_`-prefixed names are reserved, and a name may not repeat. `type` is one of `text | integer | real | boolean | json | file` (`### File columns`). - `read_group` / `write_group` — string or `null`, who may read records vs create them. BOTH keys are REQUIRED (a missing key is a `400`): each is a `@simple_group` nanoid (ASCII letters/digits, at most 64 chars, naming an ACTIVE group you own — create and manage members at `simple_group`) or `null` = every logged-in user. Null is a deliberate choice, never a default. An empty string is a `400`, not a null. Split them for patterns like a blog (read `null`, write a small authors group) or an inbox (read a private group, write `null`). - `anon_read` — OPTIONAL boolean (omitted = `false`): `true` opens get/list to SIGNED-OUT visitors through this db's own app. Requires `read_group: null` (sending both is a `400`). It has an effect only on a `public` app — a `protected`/`private` app has no signed-out visitors. - `read_scope` — OPTIONAL, `"all"` (default) or `"own"`; `null` or omitted is the default, any other value is a `400`. `"own"` with `anon_read: true` is a `400`. - `audience_column` — OPTIONAL string or `null` (default `null`): the name of a column in `columns` with type `text`, otherwise a `400`. It can never be changed after this call. - `mutate_scope` — OPTIONAL, `"owner"` (default) or `"own"`; `null` or omitted is the default, any other value is a `400`. - `mutate_group` / `deny_group` — OPTIONAL, a `@simple_group` nanoid (ASCII letters/digits, at most 64 chars, naming an ACTIVE group you own) or `null` (default). Here `null` means NO group — the opposite of `read_group` / `write_group`. An empty string is a `400`. - `sorts` — OPTIONAL array of 0 to 3 names, the orders `records/list` can read the table in: each a `text`, `integer`, `real` or `boolean` column of `columns`, `"_created_at_ms"` (creation order) or `"_updated_at_ms"`, none repeated. Omitted or `null` means `["_created_at_ms"]`; `[]` declares no sort at all, creation order included. A `json` or `file` column, an unknown name, a repeat or a 4th entry is a `400`. Each sort is usable ascending and descending, and the first is the default order of `records/list` (descending). Choose them carefully when you create the table: each sort adds write units to every write and a copy of every record to storage (`## Notes` → `### Sorts`, `### Billing`); change them later with `sorts/set`. The group and row-level semantics are in `### Table access groups`. Every record automatically carries `id` (server-minted UUID), `_submitter_user_id`, `_submitter_handle`, `_created_at_ms`, `_updated_at_ms`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/create \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{ "table": "todos", "columns": [ {"name": "title", "type": "text"}, {"name": "done", "type": "boolean"} ], "read_group": null, "write_group": "", "sorts": ["_created_at_ms", "title"] }' ``` #### Response `200`. JSON: ```json { "ok": true, "table": { "name": "todos", "read_group": null, "write_group": "", "anon_read": false, "read_scope": "all", "audience_column": null, "mutate_scope": "owner", "mutate_group": null, "deny_group": null, "new_row_email": "off", "sorts": [ {"by": "_created_at_ms", "state": "ready"}, {"by": "title", "state": "ready"} ], "created_at_ms": 1734112233445, "columns": [ {"name": "title", "type": "text"}, {"name": "done", "type": "boolean"} ] } } ``` - `name` — string, the table you just created. - `read_group` / `write_group` — string or `null` (`null` = every logged-in user through this db's app). - `anon_read` — boolean. - `read_scope` — `"all"` or `"own"`; `audience_column` — string or `null`; `mutate_scope` — `"owner"` or `"own"`; `mutate_group` / `deny_group` — string or `null` (`null` = no group). - `new_row_email` — `"off"`, `"each"` or `"daily"`: whether you are emailed when other people add rows (`POST /apps/{app_id}/tables/{table}/notify/set`). A new table is always `"off"`; `tables/create` does not take it. - `sorts` — array of `{"by", "state"}` in declaration order; on a new table every sort is `"ready"`. - `created_at_ms` — number, epoch ms. - `columns` — array of `{"name", "type"}` in declaration order. The markdown form prints the same table as a heading plus an access line (the groups, `anon_read` and the five row-level settings), a `New-row email:` line, a `Sorts:` line (each sort with its state) and a column list, and lists the reserved record fields; there a `null` `read_group` / `write_group` reads `everyone`, an `anon_read` table reads `anyone (anon_read)` on the read axis, a `null` `mutate_group`, `deny_group` or `audience_column` reads `none`, and `created_at_ms` is not shown. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; bad/duplicate/reserved `table` or column name; no columns; unknown column type; a group key missing, empty, or not nanoid-shaped (`mutate_group` / `deny_group` included); a group that is not an ACTIVE group you own; `anon_read: true` with a non-null `read_group`; an unknown `read_scope` or `mutate_scope` value; `read_scope: "own"` with `anon_read: true`; an `audience_column` that is not a declared `text` column; a row-level setting other than its default while row-level access is not available yet; `sorts` that is not an array of names, more than 3 entries, a repeated entry, an unknown name, or a `json` or `file` column | | 404 | `not_found` | unknown or non-UUID `app_id`, no database yet, not the owner, calling through another app, or an app's anonymous credential | | 409 | `table_exists` | a table with this name already exists | | 500 | `internal` | creation failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user wants a new collection/table in their app's hosted database. Decide its `sorts` here, as part of the table's design: every order the app will list it in (a board by `position`, events by `starts_at`, a feed newest first), and no more, since each costs on every write and in storage. Set `read_scope`, `audience_column`, `mutate_scope`, `mutate_group` or `deny_group` here when each user must see only their own rows, rows are addressed to one person, authors edit their own rows, staff triage every row, or some users are banned from writing (`## Notes` → `### Access recipes`). ### POST /apps/{app_id}/tables/list **Page through your app's tables**, oldest first — each one's columns and its access settings. #### Auth You must be the app's owner, with your own token for this service or through this db's app — never from inside somebody else's app, and never as a backend app. Everyone else gets the uniform `404 not_found`. (Group MEMBERSHIP is listed in `@simple_group`, not here.) #### Request `{app_id}` in the path, plus an optional JSON body (`Content-Type: application/json`). No body at all, or `{}`, is the first page; every field inside it is optional: - `limit` — number, 1..100, default 50. Outside that range is a `400`. - `cursor` — string, the previous page's `next_cursor`, verbatim. A malformed value is a `400`. A body that is sent but does not parse, or is sent without `Content-Type: application/json`, is a `400`, never read as the first page. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/list \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"limit": 50}' ``` #### Response `200`. Tables are ordered oldest first (by `created_at_ms`, then `name`). JSON: ```json { "ok": true, "tables": [ { "name": "todos", "read_group": null, "write_group": "", "anon_read": false, "read_scope": "all", "audience_column": null, "mutate_scope": "owner", "mutate_group": null, "deny_group": null, "new_row_email": "off", "sorts": [ {"by": "_created_at_ms", "state": "ready"} ], "created_at_ms": 1734112233445, "columns": [ {"name": "title", "type": "text"} ] } ], "next_cursor": "1734112233445:todos" } ``` - `name` — string. - `read_group` / `write_group` — string or `null` (`null` = every logged-in user through this db's app). - `anon_read` — boolean. - `read_scope` — `"all"` or `"own"`; `audience_column` — string or `null`; `mutate_scope` — `"owner"` or `"own"`; `mutate_group` / `deny_group` — string or `null` (`null` = no group). A table created before these settings existed shows the defaults: `"all"`, `null`, `"owner"`, `null`, `null`. - `new_row_email` — `"off"`, `"each"` or `"daily"` (`notify/set`); a table that never had the setting reads `"off"`. - `sorts` — array of `{"by", "state"}` in declaration order: each sort `records/list` can read the table in, `"ready"`, or `"building"` while a sort added by `sorts/set` is being filled in on the existing records. A table created before sorts existed reads `[{"by": "_created_at_ms", "state": "ready"}]`. - `created_at_ms` — number, epoch ms. - `columns` — array of `{"name", "type"}` in declaration order. - `next_cursor` — string when more tables exist past this page, otherwise `null`. Pass it back as `cursor` for the next page; following it until it is `null` returns each of the app's tables exactly once. An app with no tables is a `200` with `"tables": []` and `"next_cursor": null`, never a 404. The markdown form prints one section per table with its access line (the groups, `anon_read` and the five row-level settings), its `New-row email:` line, its `Sorts:` line and columns, then the next `cursor` when there is one; there a `null` `read_group` / `write_group` reads `everyone`, an `anon_read` table reads `anyone (anon_read)` on the read axis, a `null` `mutate_group`, `deny_group` or `audience_column` reads `none`, and `created_at_ms` is not shown. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | a body that does not parse, or a body without `Content-Type: application/json`; `limit` outside 1..100; malformed `cursor` | | 404 | `not_found` | unknown or non-UUID `app_id`, no database yet, not the owner, calling through another app, or an app's anonymous credential | | 500 | `internal` | the lookup failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user asks what tables/schemas their app's database has. Related: create a table with `POST /apps/{app_id}/tables/create`; add a column with `POST /apps/{app_id}/tables/{table}/columns/add`. ### POST /apps/{app_id}/tables/{table}/columns/add **Add a column** to a table. Schema evolution here is **add-only**: no drop, no rename, no type change — those operations do not exist on this service. #### Auth You must be the app's owner, with your own token for this service or through this db's app — never from inside somebody else's app, and never as a backend app. Everyone else gets the uniform `404 not_found`. #### Request JSON body (`Content-Type: application/json`): - `name` — REQUIRED string, lowercase snake_case (`^[a-z][a-z0-9_]{0,63}$`); `id`, `pk`, `created_sort` (storage attribute names) and `_`-prefixed names are reserved, and the name must not already exist on the table. - `type` — REQUIRED, one of `text | integer | real | boolean | json | file` (`### File columns`). - `default` — OPTIONAL JSON value; must match `type`. With it, existing rows read that value; without it, existing rows read null. New records that omit the column take the same default. A `file` column has no default: only `null` (or none) is accepted. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables//columns/add \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"name": "priority", "type": "integer", "default": 0}' ``` #### Response `200`, carrying the WHOLE table as it now stands (not just the new column). JSON: ```json { "ok": true, "table": { "name": "todos", "read_group": null, "write_group": "", "anon_read": false, "read_scope": "all", "audience_column": null, "mutate_scope": "owner", "mutate_group": null, "deny_group": null, "new_row_email": "off", "sorts": [ {"by": "_created_at_ms", "state": "ready"} ], "created_at_ms": 1734112233445, "columns": [ {"name": "title", "type": "text"}, {"name": "priority", "type": "integer"} ] } } ``` The fields are those of `tables/create`'s answer, the five row-level settings, `new_row_email` and `sorts` included. A new column can be added to the table's sorts with `sorts/set`. The markdown form prints the same table as a heading plus an access line (the groups, `anon_read` and the five row-level settings), a `New-row email:` line, a `Sorts:` line and the full column list; there a `null` `read_group` / `write_group` reads `everyone`, an `anon_read` table reads `anyone (anon_read)` on the read axis, a `null` `mutate_group`, `deny_group` or `audience_column` reads `none`, and `created_at_ms` is not shown. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; bad or reserved `name`; unknown `type`; a `default` that does not match `type`; a non-null `default` on a `file` column | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not the owner, calling through another app, or an app's anonymous credential | | 409 | `column_exists` | a column with this name already exists | | 500 | `internal` | the change failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user wants to extend a table's schema with a new field. ### POST /apps/{app_id}/tables/{table}/access/set Change a table's **access**: who may read records (`read_group`) and who may create them (`write_group`), `anon_read`, and the five row-level settings (`read_scope`, `audience_column`, `mutate_scope`, `mutate_group`, `deny_group`). #### Auth You must be the app's owner, with your own token for this service or through this db's app — never from inside somebody else's app, and never as a backend app. Everyone else gets the uniform `404 not_found`. #### Request JSON body (`Content-Type: application/json`). The call REPLACES `read_group`, `write_group` and `anon_read` on every call; each row-level setting changes only when the call names it: - `read_group` / `write_group` — REQUIRED, string or `null` (a missing key is a `400`). Each is a `@simple_group` nanoid (ASCII letters/digits, at most 64 chars, naming an ACTIVE group you own — manage membership at `simple_group`) or `null` = every logged-in user. Null is a deliberate choice, never a default; an empty string is a `400`, not a null. - `anon_read` — OPTIONAL boolean (omitted = `false`, which the full-set rule makes meaningful: leaving it out on a table that had it on turns it off). `true` opens get/list to signed-out visitors through this db's own app, and requires `read_group: null` (sending both is a `400`) and a `read_scope` that is not `"own"` after the call. It has an effect only on a `public` app — a `protected`/`private` app has no signed-out visitors. - `read_scope` — OPTIONAL, `"all"` or `"own"`. Omitted keeps the table's current value; `null` resets it to `"all"`; any other value is a `400`. - `mutate_scope` — OPTIONAL, `"owner"` or `"own"`. Omitted keeps the current value; `null` resets it to `"owner"`; any other value is a `400`. - `mutate_group` / `deny_group` — OPTIONAL, a `@simple_group` nanoid (an ACTIVE group you own) or `null`. Omitted keeps the current value; `null` clears it to NO group (the opposite of `read_group` / `write_group`, where `null` is every logged-in user); an empty string is a `400`. - `audience_column` — OPTIONAL, accepted only when it equals the table's current value (string or `null`), so sending back a `tables/list` answer works; any other value is a `400`. To address records on an existing table, create a new table. The rules run on the settings the call leaves: `anon_read: true` on a table whose stored `read_scope` is `"own"` is a `400`, and a call that names no row-level key leaves all five unchanged, an `"own"` table and a `deny_group` included. The group and row-level semantics are in `### Table access groups`. The change takes effect on the very next request, for records stored before it too. Group MEMBERSHIP is managed in `@simple_group` — adding/removing members there (of any of the four groups) changes who passes this table's gate with no call here: normally on the very next request, and within 5 seconds at worst. Patterns: blog = read `null` / write an authors group · inbox = read a private group / write `null` · team wiki = the same team group on both · each user's own rows = `read_scope: "own"` · authors edit their posts = `mutate_scope: "own"` · staff triage = `mutate_group` · bans = `deny_group`. More recipes in `## Notes` → `### Access recipes`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/access/set \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"read_group": null, "write_group": null, "read_scope": "own", "mutate_scope": "own", "deny_group": ""}' ``` #### Response `200`. JSON — here `table` is the table's NAME, a plain string, and every setting is the table's value after the call: ```json { "ok": true, "table": "todos", "read_group": null, "write_group": null, "anon_read": false, "read_scope": "own", "audience_column": null, "mutate_scope": "own", "mutate_group": null, "deny_group": "", "sorts": [ {"by": "_created_at_ms", "state": "ready"} ] } ``` `sorts` — the table's sorts with their states, unchanged by this call (`sorts/set`). The markdown form confirms the table and its new settings (the groups, `anon_read` and the five row-level settings) and restates the group semantics; there a `null` `read_group` / `write_group` reads `everyone`, an `anon_read` table reads `anyone (anon_read)` on the read axis, and a `null` `mutate_group`, `deny_group` or `audience_column` reads `none`. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; a group key missing, empty, or not nanoid-shaped (`mutate_group` / `deny_group` included); a group that is not an ACTIVE group you own; `anon_read: true` with a non-null `read_group`; an unknown `read_scope` or `mutate_scope` value; `read_scope: "own"` with `anon_read: true` on the resulting settings; an `audience_column` other than the table's current value; a row-level setting other than its default while row-level access is not available yet | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not the owner, calling through another app, or an app's anonymous credential | | 500 | `internal` | the change failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user wants to open up or lock down who can read or write a table, show each user only their own rows, let authors or staff edit rows, or ban writers. ### POST /apps/{app_id}/tables/{table}/notify/set Choose whether you, the owner, get an email when people other than you add rows to the table: `each` (soon after, at most one email per table every 10 minutes), `daily` (one digest per UTC day) or `off`. #### Auth You must be the app's owner, with your own token for this service or through this db's app — never from inside somebody else's app, and never as a backend app. Everyone else, an app's anonymous credential included, gets the uniform `404 not_found`. #### Request JSON body (`Content-Type: application/json`): ```json { "new_row_email": "each" } ``` - `new_row_email` — REQUIRED, `"off"`, `"each"` or `"daily"`. A missing key, `null` or any other value is a `400`. `each` and `daily` need the app's `@simple_email` account to be able to send you a notification: the app has an email account and the account has a sending address (the app still has its subdomain label), exactly as `@simple_email` requires for any send (`https://simple-email.pilely.app/README.md`). This call checks that without sending anything and without costing you anything on `@simple_email`; when a check fails it answers `400 email_not_ready` naming the check, and the table keeps its previous value. `off` always succeeds. What follows from the setting: - **Counted rows.** A `records/create` that answers `200` for a caller other than you counts one row. Rows you create never count — with your own token for this service or through the app. Rows your app's backend creates do count: it is the app, not you. A refused create never counts. Rows created and then deleted still count. - **`each`.** The first counted row on a table that has sent no notification in the last 10 minutes sends one within about a minute. Rows counted in the 10 minutes after a notification wait, and one email reports all of them when those 10 minutes end (up to about a minute late), even if no further row arrives. A table never sends two notifications less than 10 minutes apart. - **`daily`.** Rows are counted per UTC day (00:00:00 to 23:59:59 UTC). A day with counted rows sends one digest with that day's count in the first hour of the next UTC day, counted against that next day's cap. A day without counted rows sends nothing. - **The email.** From the app's `@simple_email` address with the app's display name as the From name, to your own account email (read at send time; nobody else's), with the app's name, the table's name, the number of new rows, the period they cover and a link to the app at `https://
/notify/set \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"new_row_email": "each"}' ``` #### Response `200`. JSON — `table` is the table's NAME: ```json { "ok": true, "table": "inbox", "new_row_email": "each", "sorts": [ {"by": "_created_at_ms", "state": "ready"} ] } ``` `sorts` — the table's sorts with their states, unchanged by this call. From then on `tables/list` shows the value. The markdown form confirms the table and its new value and explains `off`, `each` and `daily` in one line each. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; `new_row_email` missing, `null`, not a string, or not one of `off`, `each`, `daily` | | 400 | `email_not_ready` | `each` or `daily`, and the app has no `@simple_email` account (`no_account`) or the account has no sending address because the app has no subdomain label (`no_address`). The `reason` names which and links `https://simple-email.pilely.app/README.md`; the table keeps its previous value | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not the owner, calling through another app, or an app's anonymous credential | | 500 | `internal` | the change failed — retry in a few seconds | | 503 | `email_check_unavailable` | the email-account check could not be completed; nothing changed — retry shortly | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use Visitors write to a table — an inbox, a private line, a submission queue, an open board — and you want to know when they do without opening the app or running a backend. Pick `each` to hear soon after, `daily` for one digest a day. ### POST /apps/{app_id}/tables/{table}/sorts/set **Replace a table's sorts** — the orders `records/list` reads it in — with a whole new list. #### Auth You must be the app's owner, with your own token for this service or through this db's app — never from inside somebody else's app, and never as a backend app. Everyone else, an app's anonymous credential included, gets the uniform `404 not_found`. #### Request JSON body (`Content-Type: application/json`): ```json { "sorts": ["_created_at_ms", "position"] } ``` - `sorts` — REQUIRED array of 0 to 3 names, the table's whole new list: each a `text`, `integer`, `real` or `boolean` column of the table, `"_created_at_ms"` or `"_updated_at_ms"`, none repeated. `[]` removes every sort, creation order included. A missing key, `null`, a `json` or `file` column, an unknown name, a repeat or a 4th entry is a `400`. The first sort is the default order of `records/list` (descending). What follows from the call: - A sort already declared keeps its state and keeps working throughout. - A sort added to a table that holds records reads `"building"` until every existing record has been filled in, then `"ready"`; until then `records/list` refuses it with a `400`. Filling in takes seconds to minutes, starting about half a minute after the call; poll `tables/list`. On a table with no records it is `"ready"` at once. - A sort removed stops working at once: `records/list` refuses it with a `400`. - Every cursor issued before the call is refused with a `400`; start each walk again without a cursor. - Filling in an added sort writes every existing record once, and removing a sort rewrites every record that held it; both are billed to you as DynamoDB write units and change your storage by the sort's copy of each record (`### Billing`). Each sort the table keeps adds write units to every later write and a copy of every record to storage, so add sorts carefully. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/sorts/set \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"sorts": ["_created_at_ms", "position"]}' ``` #### Response `200`. The table object, exactly as `tables/create` and `tables/list` answer it, with its new `sorts`: ```json { "ok": true, "table": { "name": "board", "…": "…", "sorts": [ {"by": "_created_at_ms", "state": "ready"}, {"by": "position", "state": "building"} ], "columns": [ {"name": "position", "type": "integer"} ] } } ``` The markdown form is the table answer of `tables/create`, its `Sorts:` line showing each sort with its state. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; `sorts` missing, `null`, not an array of names, more than 3 entries, a repeated entry, an unknown name, or a `json` or `file` column | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not the owner, calling through another app, or an app's anonymous credential | | 500 | `internal` | the change failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The app needs to list a table in an order it did not declare at `tables/create` (a new column to order by, or most recently changed first with `_updated_at_ms`), or no longer needs one: remove it to stop paying for it on every write and in storage. ### POST /apps/{app_id}/tables/{table}/records/create **Insert a record**, or with `key` create it only if the submitter has no row with that key yet. The server mints the record's `id` (UUID) and stamps `_submitter_user_id`, `_submitter_handle`, `_created_at_ms`, `_updated_at_ms`, `_version` (`1`) and `_key` (the `key`, or `null`). #### Auth Who may create follows the table's **write_group** (through the db's own app): `null` = every logged-in user, otherwise that group's members. A member of the table's `deny_group` gets the uniform `404`, whatever the body holds. The db's own app's backend creates only as an `app` member of `write_group` (a `null` write group admits no backend app). The owner may always create from their own surfaces, but not from inside somebody else's app, and is never denied. There is no anonymous write of any kind: a caller on an app's anonymous credential gets the uniform `404`. On a table whose `new_row_email` is `each` or `daily`, a create by anyone but you counts one row toward your new-row email (`POST /apps/{app_id}/tables/{table}/notify/set`); the answer, its status and its timing are the same either way. On a table with an audience column the audience rule applies (`### Table access groups`): a non-owner may set the column only to `null` or their own user id. A submitter `read_group` does not admit cannot read the created record back; on a `read_scope: "own"` table a submitter it admits reads back their own records. Who may later edit or delete the record: the owner, `mutate_group` members, and on a `mutate_scope: "own"` table its submitter. #### Request JSON body (`Content-Type: application/json`) — the body itself is required even when you set nothing: - `fields` — OPTIONAL object of `{ column: value }` (omitted or `{}` inserts a row that is null, or its declared `default`, in every column). Every key must be a column of the table, and each value must match that column's type: `text` → string, `integer` → whole number, `real` → number, `boolean` → `true`/`false`, `json` → any JSON value, `file` → `{"upload": ""}` (attach that upload, `### File columns`). `null` is accepted for every column. A `file` field accepts nothing else: a `file_id`, a link or any other value is a `400`. Reserved fields (`id`, `_submitter_*`, the timestamps, `_version`, `_key`) are not columns — sending one is a `400`. - `key` — OPTIONAL string, 1–200 characters from `A–Z a–z 0–9 _ - . : @ / +`; anything else is a `400`. Without it the create always inserts a new row. With it the create is create-if-absent: when the submitter has no row in this table with that `_key` it inserts the row (carrying `_key`), and when they have one it answers `409 key_exists` with that row's `id` and creates nothing. - The serialized `fields` object is limited to 16 KB. - Any other top-level field is a `400`. A key is unique per table PER SUBMITTER. The submitter is the caller: the owner on every surface, each signed-in non-owner, and the db's app's backend — whose rows carry the app's own id (`### Submitter fields on every record`), so every box of the app shares one key space, apart from the owner's. Each signed-in non-owner has their own, so two non-owners creating with the same key get two rows and never a `409`, and a `409 key_exists` only ever names a row the same submitter created, whether or not the caller may read it. Deleting the keyed row frees its key, and deleting the database frees every key. The key counts toward the record's stored size. Patterns: `## Notes` → `### Create once per external event`, `### Lease`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/records/create \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"fields": {"title": "first", "done": false}}' ``` #### Response `200`. Note the asymmetry: you SEND columns nested under `fields`, you GET them back FLAT, beside `id` and the reserved fields. JSON: ```json { "ok": true, "record": { "id": "", "title": "first", "done": false, "_submitter_user_id": "", "_submitter_handle": "", "_created_at_ms": 1734112233445, "_updated_at_ms": 1734112233445, "_version": 1, "_key": null } } ``` Every declared column is present (null when unset); values come back in the JSON shape their type declares — a `file` field as `{"file_id", "content_type", "size_bytes"}`, `size_bytes` being the size the storage host received. A keyed create's record carries its `key` as `_key`. The markdown form prints the same record as a pretty-printed JSON block. A `key` the submitter already holds answers `409`, and nothing is created: ```json { "ok": false, "code": "key_exists", "reason": "", "id": "" } ``` #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; a top-level field other than `fields` and `key`; a `key` that is not a string of 1–200 characters from `A–Z a–z 0–9 _ - . : @ / +`; unknown column; a value that does not match its column type; `fields` over 16 KB serialized; an audience column value that is not `null` or a lowercase hyphenated user id; a non-owner's audience column value naming another user; an upload that is not usable (unknown, used, expired, another caller's, or started for another table or column — one answer for all); an upload nothing was PUT to | | 402 | `storage_exceeded` | your (the DB owner's) storage allowance cannot hold the new record, or a file it attaches; for a file, every caller but you gets the uniform `404` | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not permitted by `write_group`, a member of the table's `deny_group`, calling through another app, or an app's anonymous credential; also a file the owner's storage cannot hold, to every caller but the owner | | 409 | `key_exists` | the submitter already has a row in this table with this `key`; the JSON carries that row's `id`, and nothing is created | | 413 | `file_too_large` | an attached upload is over 2 GB; nothing is created | | 500 | `internal` | the insert failed, or `deny_group` membership could not be checked — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | | 503 | `quota_unavailable` | the storage allowance could not be checked — retry shortly | #### When to use The user (or their app) wants to store a new record. Send a `key` when the same thing must be stored at most once: a webhook event that may be delivered twice, one row per user, a job run per period. ### POST /apps/{app_id}/tables/{table}/records/list **Page through records** in one of the table's declared sorts — by default its first sort, descending, which is newest first on every table that keeps creation order — keeping the records a `where` condition holds on, or **count** them. #### Auth Access follows the table's **read_group** (through the db's own app): `null` = every logged-in user, otherwise that group's members. On a `read_scope: "all"` table a permitted caller lists every record. On a `read_scope: "own"` table a permitted non-owner lists only the records they submitted plus the records whose audience column holds their user id, each once, in the requested sort and direction, filtered or counted alike; `where` and `eq` narrow within that set, and following `next_cursor` until `null` walks all of it. A `mutate_group` member lists every record whatever `read_group` and `read_scope` say. The owner, from their own surfaces, lists every record; never from inside somebody else's app. The db's app's backend lists as an `app` member of `read_group` (a `null` read group admits none) or `mutate_group`. Anyone else gets the same 404 as a nonexistent table. A list is eventually consistent: a record just created can take a moment (normally under a second) to appear, in an own-rows list and in lists filtered on `_submitter_user_id` or on the audience column too. An own-rows list reads the table's records in order and keeps the caller's, so it is billed for every record it reads and may answer short or empty pages with a non-null `next_cursor`. On a table with `anon_read: true` (never a `read_scope: "own"` table) a SIGNED-OUT visitor of the db's own app may list too; their rows come back without `_submitter_user_id`. A caller with no credential at all gets `401`. #### Request JSON body (`Content-Type: application/json`) — the body is required (send `{}` for the first page); every field inside it is optional: - `limit` — number, 1..100, default 50. Outside that range is a `400`. A count ignores it. - `sort` — `{"by": , "dir": "asc" | "desc"}`, both keys required, nothing else. `by` names one of the table's `sorts` (`tables/list`) that is `"ready"`. Left out or `null`: the table's first sort, descending (a `400` while that sort is still `"building"`). A name the table does not declare, a sort still `"building"`, or any other shape is a `400`. A table with no sorts lists in a stable but unspecified order, and takes no `sort` and no `order`. - `order` — string, `"newest"` or `"oldest"`: creation order, `_created_at_ms` descending or ascending — the same as `{"by": "_created_at_ms", "dir": "desc" | "asc"}`. It needs `_created_at_ms` among the table's sorts (a `400` otherwise). Values are case-sensitive; anything else is a `400`, and so is `sort` and `order` together. Absent or `null` means no `order`. - `where` — a condition object, ANDed with `eq` (`## Notes` → `### Filter`): `{"column": {"gte": 1, "lt": 5}}` (several operators on one column are ANDed), a bare value `{"column": value}` meaning `eq`, and `"and": [ … ]`, `"or": [ … ]` (arrays of condition objects) and `"not": { … }`. Operators: `eq`, `ne`, `lt`, `lte`, `gt`, `gte`, `between` (`[low, high]`, both ends included), `in` (1 to 100 values), `begins_with`, `contains`, `is_null` (`true` / `false`). A `text` column takes every operator, compared in byte order, case-sensitive (`contains` is a substring); `integer` and `real` take `eq ne lt lte gt gte between in is_null`; `boolean` takes `eq ne in is_null`; `json` takes only `eq` and `is_null`; `file` takes only `is_null`. Besides the columns, `where` accepts `_created_at_ms` and `_updated_at_ms` (integers) and `_submitter_user_id` (signed-in callers only); no other reserved field. Every value is compared as a read returns it: a column never written compares as its declared default, and `is_null: true` matches a column that reads `null`. A value of the wrong type, `null` as an operand (use `is_null`), an unknown column, a disallowed operator, an empty `in`, `and` or `or`, an empty condition `{}` inside `and`, `or` or `not`, or a `between` whose low is above its high is a `400`. A top-level `"where": {}` is no condition. The columns `and`, `or` and `not` cannot be named in `where` (use `eq`). - `eq` — object of equality filters `{ column: value }`, ANDed with each other and with `where`. Each key must be a column of the table (a `file` column is a `400`), and each value must be non-null and match that column's type. One reserved field is accepted besides the columns: `_submitter_user_id`, whose value is a user id (a UUID string), returns the records that user submitted; only signed-in callers may send it (a signed-out visitor gets a `400`), and it filters within what the caller may see. No other reserved field is filterable with `eq`. - `count` — boolean, default `false`. `true` answers the number of records the same list (same `sort`, `where`, `eq` and access) would return, instead of the records, in parts on a big table (see Response). `limit` and `file_urls` do not apply to a count. - `cursor` — string, the previous answer's `next_cursor`, verbatim. Every cursor is bound to the caller it was issued to — the same person, the same backend app, or any signed-out visitor for a cursor issued to one — whatever the table's read scope, and resumes only the walk that issued it: the same table, `sort` and direction (or `order`), the same read scope, on the table's sorts as they were when it was issued. Anything else is a `400` — a cursor sent by another caller (an owner's cursor sent by a member, a signed-out visitor's sent by a signed-in caller, and the reverse), under another read scope, for another sort or direction, after any `sorts/set` on the table since, a malformed or altered value, a cursor of another table, or a cursor in a format this service answered before — and the caller starts again without a cursor. A cursor is sealed: it shows nothing of the rows it passed. A cursor does not bind `where` or `eq`: send the same condition on every page of a walk. - `file_urls` — boolean, default `false`. `true` adds `url`, a download link valid 5 to 10 minutes, to every non-null `file` field of the page: `{"file_id", "content_type", "size_bytes", "url"}`. Each link is billed to the owner (`### Billing`). The whole condition — `where`, `eq` and, for a non-owner on a `read_scope: "own"` table, the own-rows rule — must fit DynamoDB's expression limits: at most 4 KB rendered and 300 operators. Over a limit is a `400`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/records/list \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"limit": 50, "sort": {"by": "position", "dir": "asc"}, "where": {"done": false, "position": {"gte": 10}}}' ``` #### Response `200`. Records follow the requested sort and direction: ascending by the sort's value, records whose value reads `null` before every value, ties by `id` ascending; descending is the exact mirror. A `text` sort orders by the value's first 900 bytes. `order: "newest"` lists newest first (by `_created_at_ms` descending, then `id` descending), `"oldest"` oldest first (by `_created_at_ms` ascending, then `id` ascending). Each record is FLAT — columns beside `id` and the reserved fields. JSON: ```json { "ok": true, "records": [ { "id": "", "title": "first", "done": false, "_submitter_user_id": "", "_submitter_handle": "", "_created_at_ms": 1734112233445, "_updated_at_ms": 1734112233445, "_version": 1, "_key": null } ], "next_cursor": "r2.…" } ``` - `records` — array of at most `limit` rows; empty when nothing matches (a `200`, never a 404). One call reads a bounded number of rows, so a page may hold fewer than `limit` rows, or none, while `next_cursor` is non-null — most often on a `where` or `eq` whose matches are sparse, or a non-owner's list on a `read_scope: "own"` table holding few of their records among many others. - `next_cursor` — string when more rows may exist past this page, whatever the page's length; `null` when none do. Pass it back as `cursor`, with the same `sort` (or `order`) and condition, for the next page. Only `null` ends a walk: following it until it is `null`, short and empty pages included, returns every matching row exactly once, in the requested order. A cursor handed to a non-owner on a `read_scope: "own"` table never names a record they may not see. With `count: true` the answer is a count instead: ```json { "ok": true, "count": 1234, "next_cursor": null } ``` - `count` — number of matching records this part of the count covered. - `next_cursor` — `null` when the count is complete. Otherwise one call stopped part way through a big table: pass it back as `cursor` (with `count: true` and the same `sort`, `where` and `eq`) and add up the parts until `next_cursor` is `null`. A part may count zero while `next_cursor` is non-null. Each part is billed for the rows it read; no count is stored. The markdown form prints the row count, the same records as a pretty-printed JSON array, and the next `cursor` whenever there is one, a zero-row page included; a count prints the number and, when there is one, the next `cursor`. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable/missing body or missing `Content-Type`; `limit` outside 1..100; a `sort` that is not `{"by", "dir"}`, names no sort of the table, or names a sort still `"building"`; an `order` that is not `"newest"`, `"oldest"` or `null`, or `order` on a table without `_created_at_ms` among its sorts; `sort` and `order` together; a malformed or altered `cursor`, a cursor of another table, a cursor issued to a different caller (whatever the read scope; signed-out visitors share one), under a different read scope, for another sort or direction (a `cursor` issued for the other `order` included), before any `sorts/set` on the table since, or in a format this service answered before; a `where` with an unknown field, a disallowed operator, a value of the wrong type or `null`, an empty `in`, `and` or `or`, an empty condition `{}` inside `and`, `or` or `not`, more than 100 `in` values, or a `between` low above its high; `where` or `eq` on `_submitter_user_id` from a signed-out visitor; an unknown `eq` column, a null / type-mismatched `eq` value, `eq` on a `file` column, or `eq` on `_submitter_user_id` with a value that is not a UUID string; a condition over 4 KB or 300 operators; `file_urls` or `count` that is not a boolean | | 401 | `unauthenticated` | no credential at all | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not permitted by `read_group`, calling through another app, or anonymous on a table without `anon_read` | | 500 | `internal` | the read failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user (or their app) wants to read a collection of records. List in a declared sort rather than sorting in the page, and narrow with `where` rather than listing everything: a `where` range on the column the list is sorted by reads, and is billed for, only the records in the range (`## Notes` → `### Filter`). Use `count: true` for "how many" instead of walking the records. On a `read_scope: "own"` table a signed-in user's list is "my records" with no filter needed; the owner's per-user views filter on `_submitter_user_id` or on the audience column. ### POST /apps/{app_id}/tables/{table}/records/{record_id}/get **Fetch one record** by id. #### Auth Visibility follows the table's **read_group** (through the db's own app): `null` = every logged-in user, otherwise that group's members. On a `read_scope: "own"` table a non-owner gets only the records they submitted and the records whose audience column holds their user id. A `mutate_group` member gets every record. The owner may always read every record from their own surfaces, but not from inside somebody else's app. The db's app's backend reads as an `app` member of `read_group` (a `null` read group admits none) or `mutate_group`. A record you may not see is indistinguishable from one that does not exist: the same `404` status and body as an unknown record id, with or without `consistent`. On a table with `anon_read: true` a SIGNED-OUT visitor of the db's own app may fetch too; their record comes back without `_submitter_user_id`. A caller with no credential at all gets `401`. #### Request The path segments `{app_id}`, `{table}`, and `{record_id}` — the server-minted `id` (a UUID string) from a create or list answer — and an OPTIONAL JSON body. No body, an empty body, or `{}` is the default read; a non-empty body needs `Content-Type: application/json`. - `consistent` — boolean, default `false`. `true` makes the read strongly consistent: the answer reflects every write that answered `200` before the read started. Without it a get may briefly return a record as it was before a write that just answered `200`. Any other type is a `400`. A consistent read costs about twice the read capacity (`## Notes` → `### Billing`). Whoever may get may send it, signed-out visitors of an `anon_read` table included. - `file_urls` — boolean, default `false`. `true` adds `url`, a download link valid 5 to 10 minutes, to every non-null `file` field: `{"file_id", "content_type", "size_bytes", "url"}`. Whoever may get may send it. Each link is billed to the owner (`### Billing`). - Any other top-level field is a `400`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/records//get \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"consistent": true}' ``` #### Response `200`. The record is FLAT — columns beside `id` and the reserved fields. JSON: ```json { "ok": true, "record": { "id": "", "title": "first", "done": false, "_submitter_user_id": "", "_submitter_handle": "", "_created_at_ms": 1734112233445, "_updated_at_ms": 1734112233445, "_version": 1, "_key": null } } ``` Every declared column is present (null when unset). The answer has the same shape with or without `consistent`. The markdown form prints the same record as a pretty-printed JSON block. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | a non-empty body without `Content-Type: application/json`; a body that does not parse; a top-level field other than `consistent` and `file_urls`; `consistent` that is not a boolean, or `file_urls` that is not a boolean | | 401 | `unauthenticated` | no credential at all | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}` or `{record_id}`, no database yet, not permitted by `read_group`, a record outside your own records on a `read_scope: "own"` table, calling through another app, or anonymous on a table without `anon_read` | | 500 | `internal` | the read failed — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user (or their app) needs one specific record. Send `consistent: true` when the next decision depends on a write that just answered `200`: the read step of `## Notes` → `### Compare-and-set loop`, or a page showing the result of its own update. ### POST /apps/{app_id}/tables/{table}/records/{record_id}/update **Update a record**. Partial: only the columns you send change. Set columns, add to number columns, or both, in one atomic step that can be made conditional on the record's `_version` or on column values; every successful update raises `_version` by exactly 1. #### Auth Who may update which record (`### Table access groups`): - the owner, any record, from your own surfaces — never from inside somebody else's app; - a `mutate_group` member, through the db's app, any record — the db's app's backend included, as an `app` member; - on a `mutate_scope: "own"` table, a non-owner through the db's app whom `write_group` admits at the time of the call (`null` or a member), the records they submitted; - never a `deny_group` member (`mutate_group` members included); the owner is never denied. Everyone else, and every record the caller may not change, gets the uniform `404` whatever well-formed `if_version`, `if` or `inc` they send, and the record is unchanged; a `409` only ever reaches a caller who may change the record. The submitter check is part of the write itself, so a record that is not yours is never changed, whatever write races yours. A non-owner (a `mutate_group` member included) setting the table's audience column follows the audience rule. #### Request JSON body (`Content-Type: application/json`): - `fields` — OPTIONAL object of `{ column: value }`. Every key must be a column of the table, and each value must match that column's type: `text` → string, `integer` → whole number, `real` → number, `boolean` → `true`/`false`, `json` → any JSON value, `file` → `{"upload": ""}`. `null` clears a column. On a `file` field an upload replaces the file it held and `null` clears it; either way the old file is deleted once the update commits (`### File columns`). Reserved fields (`id`, `_submitter_*`, the timestamps, `_version`, `_key`) are not columns and can never be written — sending one in `fields`, `inc` or `if` is a `400`. The serialized `fields` object is limited to 16 KB. - `inc` — OPTIONAL object of `{ column: number }`: atomically add the number to the column (never a `file` column). Each key must be an `integer` column (a whole number) or a `real` column (any finite number); `0` is allowed; at most 64 keys. A column may not be in both `fields` and `inc`. The addition starts from the column's value as a read returns it: its declared `default` when never written, and `0` when it reads `null`. An `inc` that would take an `integer` column outside the signed 64-bit range, or a `real` column outside the range a number can hold, is a `400` and changes nothing. Concurrent `inc`s never lose a count. - `if_version` — OPTIONAL integer ≥ 1: the update applies only when the record's `_version` equals it. Anything else (`0`, negative, a fraction, a string) is a `400`. - `if` — OPTIONAL object of `{ column: value | null }`: the update applies only when every named column currently reads that value. Each key must be a column of the table (a `file` column is a `400`) and each value must match the column's type by the same rules as `fields`, or be `null`; at most 64 keys and 16 KB serialized. A column may also be in `fields` or `inc`. Equality is the equality `records/list`'s `eq` uses: a column compares as a read returns it, so a column never written compares as its declared `default`, and `null` matches a column that reads null (never written and no default, or written as `null`). - `fields` and `inc` together must change at least one column, else `400`. - Any other top-level field is a `400`, so a misspelled option (`ifVersion`) never turns into an unconditional write. An option sent as `null` counts as left out. `if_version`, every `if` and the change are one atomic step: the record changes only if `if_version` matches and every `if` holds at the moment of the write. `_updated_at_ms` is restamped for you. Detect change with `_version`, never `_updated_at_ms`: two updates in one millisecond can share a stamp. A record stored before versions existed reads `_version: 1` and matches `if_version: 1` until its first update makes it `2`. Patterns: `## Notes` → `### Compare-and-set loop`, `### Claim a slot`, `### Counter`, `### Lease`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/records//update \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"fields": {"done": true}, "inc": {"edits": 1}, "if_version": 3}' ``` #### Response `200`, carrying the WHOLE stored record after the update — flat, columns beside `id` and the reserved fields, with its new `_version`. An `inc` column comes back as that increment left it. JSON: ```json { "ok": true, "record": { "id": "", "title": "first", "done": true, "edits": 4, "_submitter_user_id": "", "_submitter_handle": "", "_created_at_ms": 1734112233445, "_updated_at_ms": 1734112299887, "_version": 4, "_key": null } } ``` The markdown form prints the same record as a pretty-printed JSON block. A condition that does not hold answers `409`, and nothing changes. An `if_version` that is not the record's `_version`: ```json { "ok": false, "code": "version_conflict", "reason": "", "current_version": 5 } ``` `if_version` matches (or is absent) and an `if` does not hold: ```json { "ok": false, "code": "condition_failed", "reason": "" } ``` When both fail, the answer is `version_conflict`. A record that does not exist is the uniform `404`, never a `409`. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; a top-level field other than `fields`, `inc`, `if_version` and `if`; `if_version` that is not an integer ≥ 1; `fields` and `inc` changing no column; unknown column in `fields`, `inc` or `if`; a value that does not match its column type; an `inc` column that is not `integer` or `real`, or a non-whole number on an `integer` column; a column in both `fields` and `inc`; more than 64 `inc` or 64 `if` keys; `fields` or `if` over 16 KB serialized; an `inc` that would leave the column's range; an audience column value that is not `null` or a lowercase hyphenated user id; a non-owner's audience column value naming another user; a `file` column in `inc` or `if`; an upload that is not usable (one answer for every reason) or that nothing was PUT to | | 402 | `storage_exceeded` | the update grows the record past your storage allowance (an update that does not grow it is never refused for storage), or a file it attaches does not fit; for a file, every caller but you gets the uniform `404` | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}` or `{record_id}`, no database yet, a record you may not change (not the owner, not in `mutate_group`, and not its submitter on a `mutate_scope: "own"` table that `write_group` lets you write), a member of the table's `deny_group`, calling through another app, or an app's anonymous credential — whatever `if_version`, `if` or `inc` the call sends | | 409 | `version_conflict` | `if_version` is not the record's `_version`; the JSON carries `current_version`, and nothing changes | | 409 | `condition_failed` | an `if` does not hold (and `if_version`, when sent, matches); nothing changes | | 413 | `file_too_large` | an attached upload is over 2 GB; nothing changes | | 500 | `internal` | the update failed, or `deny_group` membership could not be checked — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | | 503 | `quota_unavailable` | the storage allowance could not be checked — retry shortly | #### When to use The user edits an existing record in their own app's database, an author edits their own record on a `mutate_scope: "own"` table, or staff in `mutate_group` triage any record. Send `if_version` whenever the new values were decided from a read, `if` to take something only while it is free, and `inc` for counts. ### POST /apps/{app_id}/tables/{table}/records/{record_id}/delete **Delete a record**, optionally only at a given `_version`. Deletion is permanent. #### Auth Who may delete follows the same rules as `records/{record_id}/update`: the owner (from your own surfaces, never from inside somebody else's app), a `mutate_group` member through the db's app on any record (the db's app's backend included, as an `app` member), and on a `mutate_scope: "own"` table a non-owner through the db's app whom `write_group` admits, on the records they submitted — never a `deny_group` member. Everyone else, and every record the caller may not delete, gets the uniform `404` whatever `if_version` they send, and the record is kept. #### Request The path segments `{app_id}`, `{table}`, and `{record_id}` — the server-minted `id` (a UUID string) — and an OPTIONAL JSON body. No body, an empty body, or `{}` is the unconditional delete; a non-empty body needs `Content-Type: application/json`. - `if_version` — integer ≥ 1, optional: the record is deleted only when its `_version` equals it. Anything else (`0`, negative, a fraction, a string) is a `400`. - Any other top-level field is a `400`. Deleting a keyed record frees its `key` for the same submitter (`records/create`). Deleting a record deletes every file it holds, once the delete commits; a refused delete deletes nothing. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/records//delete \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"if_version": 2}' ``` #### Response `200`. JSON: ```json { "ok": true, "deleted": "" } ``` - `deleted` — string, the id you asked for. The markdown form confirms the deleted id. There is no undo, and a second delete of the same id answers `404`. An `if_version` that is not the record's `_version` answers `409`, and the record is kept: ```json { "ok": false, "code": "version_conflict", "reason": "", "current_version": 3 } ``` A record that does not exist is the uniform `404`, never a `409`. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | a non-empty body without `Content-Type: application/json`; a body that does not parse; a top-level field other than `if_version`; `if_version` that is not an integer ≥ 1 | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}` or `{record_id}`, no database yet, a record you may not delete (not the owner, not in `mutate_group`, and not its submitter on a `mutate_scope: "own"` table that `write_group` lets you write), a member of the table's `deny_group`, calling through another app, or an app's anonymous credential — with or without `if_version` | | 409 | `version_conflict` | `if_version` is not the record's `_version`; the JSON carries `current_version`, and the record is kept | | 500 | `internal` | the delete failed, or `deny_group` membership could not be checked — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use The user (or their app) removes a record for good — an author withdrawing their own record on a `mutate_scope: "own"` table included. Send `if_version` when the delete was decided from a read, such as releasing a lease (`## Notes` → `### Lease`). ### POST /apps/{app_id}/tables/{table}/files/{column}/upload/start **Start an upload** into a `file` column: get a link to PUT one file straight to storage, and the `upload_id` that attaches it to a record. #### Auth Whoever may create records in the table or update records in it: through the db's own app, a caller `write_group` admits (`null` = every logged-in user; a backend app only as an `app` member) or a `mutate_group` member — never a `deny_group` member; the owner from their own surfaces, never from inside somebody else's app. A signed-out visitor, an agent and every caller through another app get the uniform `404`, as does an unknown app or table. #### Request JSON body (`Content-Type: application/json`): - `content_type` — REQUIRED string, 1–255 visible ASCII characters, the file's media type (`image/png`, `application/pdf`, ...). It is signed into the link: the PUT must send exactly this `Content-Type`, and downloads are served with it. - Any other top-level field is a `400`. The size is not sent: the size that counts is the one storage receives, read when the upload is attached. `{column}` must be a `file` column of the table (any other column is a `400`). #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/files//upload/start \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{"content_type": "image/png"}' # then PUT the bytes to upload_url, with no credential curl -X PUT "" -H "Content-Type: image/png" --data-binary @photo.png ``` #### Response `200`. JSON: ```json { "ok": true, "upload_id": "Qm8xT2abcdEFGH12345678", "upload_url": "https://.r2.cloudflarestorage.com//blobs/...", "expires_at": 1760400300000 } ``` - `upload_id` — 22 characters, ASCII letters and digits: attach it by sending `{"upload": ""}` as this column's value in `records/create` or `records/{record_id}/update`. It works once, only for you, only for this table and column. - `upload_url` — a presigned `PUT` link valid 5 minutes, for this one upload: PUT the file's bytes to it with the `Content-Type` you declared and nothing else (no `Authorization`). From a browser page it is a plain `fetch(upload_url, {method: "PUT", headers: {"Content-Type": ...}, body: file})`. It can read nothing and write nowhere else. PUT once: the bytes you PUT are the file you attach, so a PUT to the link after the attach replaces the attached file's bytes, and the storage it counts toward the owner's allowance is corrected to the new size after the link expires; the record's `size_bytes` keeps the size received at the attach. - `expires_at` — number, epoch milliseconds: the end of the link's validity. An upload still unattached 1 hour after it is deleted, and its `upload_id` is then a `400`; an upload that started in time can still be attached until then. The markdown form prints the same three values and the header to send. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | unparseable body or missing `Content-Type`; `content_type` missing, empty, over 255 characters or not visible ASCII; a top-level field other than `content_type`; `{column}` is not a `file` column of the table | | 402 | `storage_exceeded` | your (the DB owner's) storage allowance has no room left; every other caller gets the uniform `404` | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}`, no database yet, not permitted to create or update records in the table, a member of the table's `deny_group`, calling through another app, a signed-out visitor or an agent; also the owner's storage refusal, to every caller but the owner | | 500 | `internal` | the upload could not be started, or `deny_group` membership could not be checked — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | | 503 | `quota_unavailable` | the storage allowance could not be checked — retry shortly | #### When to use The user (or their app) wants to store a file (an image, an attachment) in a record: start the upload, PUT the bytes, then create or update the record with `{"upload": ""}` in the file field. The `@pilely/simple-db` SDK does all three in one call (`### File columns`). ### POST /apps/{app_id}/tables/{table}/records/{record_id}/files/{column}/download **Get a download link** for the file one record holds in a `file` column. #### Auth Whoever may read the record, exactly as `records/{record_id}/get`: `read_group` (and `read_scope`, the audience column) through the db's own app, `mutate_group` members, the db's app's backend as an `app` member, the owner from their own surfaces, and signed-out visitors of the db's app on an `anon_read` table. A record you may not read is the uniform `404`, the same as one that does not exist. #### Request The path segments `{app_id}`, `{table}`, `{record_id}` and `{column}` (a `file` column of the table), and no body — none, an empty body, or `{}`. Any field is a `400`. #### Example ```bash curl -X POST https://simple-db.pilely.app/apps//tables/
/records//files//download \ -H "Authorization: Bearer " ``` #### Response `200`. JSON: ```json { "ok": true, "url": "https://.r2.cloudflarestorage.com//blobs/...?X-Amz-...", "content_type": "image/png", "size_bytes": 48211 } ``` - `url` — a presigned `GET` of the file on the storage host, valid 5 to 10 minutes; use it directly (``, a download link). It is the same link within a 5-minute window, so a browser cache keeps working. - `content_type`, `size_bytes` — as the record's field holds them. The markdown form prints the same three values. A null field is the uniform `404`. #### Errors | Status | `code` | When | |---|---|---| | 400 | `bad_request` | a body that is not empty or `{}`, or one without `Content-Type: application/json`; `{column}` is not a `file` column of the table | | 401 | `unauthenticated` | no credential at all | | 404 | `not_found` | unknown or non-UUID `app_id`, unknown `{table}` or `{record_id}`, a record you may not read, the field holds no file, no database yet, calling through another app, or anonymous on a table without `anon_read` | | 500 | `internal` | the link could not be made — retry in a few seconds | | 503 | `out_of_traffic_credits` | the DB owner (you) has no traffic balance left; top up. Every other caller gets the uniform `404` | #### When to use A page needs one file's link. To show many records with their files, list or get them with `file_urls: true` instead of one call per file. ## Notes ### Other GETs on this host - `GET /README.md` — this page; public, the same bytes for every caller and every `Accept`. - `GET /` — `302` to `/README.md`. - `GET /healthz` — liveness probe, plain `ok`. ### Storage model simple_db is a table/record HTTP API under `https://simple-db.pilely.app/...`, never a database an app's own code opens a connection to. There is no hosted SQL engine and no Postgres to connect to: records live in one shared DynamoDB table, where each app/table pair is a partition (`pk = "#"`), not a schema object, and each of a table's sorts is backed by one of the shared table's three sort indexes (`### Sorts`). A table has no size cap. Column types are `text | integer | real | boolean | json | file` (a `file` column's bytes live in object storage, `### File columns`), and schema evolution is add-only — the table routes are `tables/create`, `tables/list`, `columns/add`, `access/set`, `notify/set` and `sorts/set`; there is no drop, rename or retype of a column. An app that needs SQL, joins or transactions runs its own database elsewhere; pilely.app does not host one, and custom validation, computation or third-party calls mean a backend app. ### Submitter fields on every record Every record carries server-minted fields that are never declared: `id` (the primary key), `_submitter_user_id`, `_submitter_handle`, `_created_at_ms`, `_updated_at_ms`, `_version` and `_key`, and they come back on every read. On a `read_group: null`, `read_scope: "all"` table every signed-in reader therefore sees each submitter's handle; anonymous submissions need a scoped read group or `read_scope: "own"`. Anonymous readers of an `anon_read` table get the rows without `_submitter_user_id`; `_submitter_handle`, `_version` and `_key` stay, and so does an audience column, so the user ids it names are public on an `anon_read` table. - `_version` — integer ≥ 1: `1` on create, one more on every successful `records/update` (set, increment or both, whatever its size); a refused or failed update leaves it unchanged. A record stored without a version reads `1`. Detect change with `_version`, never `_updated_at_ms`: two updates in one millisecond can share a stamp. Send it back as `if_version` (`### Compare-and-set loop`). - `_key` — string or `null`: the `key` a keyed `records/create` gave, verbatim; `null` for a record created without one. It never changes. Every caller who can read the record sees it, so never put private data (an email, a name, a secret) in a key on a table others read. - A keyed record's `id` is not random: it is derived from the submitter's id and the key, so anyone who knows both can compute it, and a key created again after its record was deleted gets the same `id` back. An id is never an access credential — the table's groups decide who reads it — but do not treat a keyed id as unguessable. A handle never changes: no route renames it, so `_submitter_handle` names the same account for as long as that account exists. Deleting an account releases its handle, and a later signup may be given the same handle, so after an account deletion a rule keyed on `_submitter_handle` (a ban list, for example) may match the new account. Key rules on `_submitter_user_id` wherever the reader gets it; only signed-out readers need the handle (`https://pilely.app/README.md` → `### POST /~/account_deletion/request`). A record created by an app's own backend server (an `@simple_server` box calling with its `PILELY_BACKEND_TOKEN`) carries the APP's own id as `_submitter_user_id` and an empty `_submitter_handle`, whichever user the server was acting for: the box's token is the app acting as itself, never its owner (`https://simple-server.pilely.app/README.md` → `### The box's backend token`). A server that writes on behalf of its users stores the acting user's id and handle in the table's own columns (for example `user_id` and `handle`) and shows those as the author, never `_submitter_*`. ### Sorts A table declares 0 to 3 sorts (`tables/create`, `sorts/set`): the orders `records/list` reads it in, each ascending or descending. Every table created before sorts existed, and every table created without `sorts`, has one: `_created_at_ms`, so it lists newest first by default. Choosing a table's sorts is part of designing the table, done when it is created: one per order the app actually lists in, and no more. - Each sort costs: every record is stored once more per sort (`### Billing`: `(1 + number of sorts) × (S + 100)` bytes), and every create, update and delete writes each sort's copy, so each sort adds write units to every write. A table with only creation order costs exactly what a table cost before sorts existed; a table with no sorts costs half in storage. - A `records/list` in a declared sort reads the records already in that order, so paging with `next_cursor` stays exact however big the table grows — unlike sorting in the page, which is correct up to one page of 100 records and silently wrong after. - Order: ascending by the sorted value as a read returns it (a column never written sorts as its declared default), records whose value is `null` before every value, ties by `id` ascending; descending is the exact mirror. Text sorts in byte order, case-sensitive, by its first 900 bytes; numbers numerically; `false` before `true`. - A sort added with `sorts/set` to a table that holds records is `"building"` until every existing record has been filled in (seconds to minutes), then `"ready"`; a removed sort stops working at once. ### Filter `records/list` takes `limit` 1–100 (default 50), `cursor` (the previous answer's `next_cursor`), `sort` or `order`, `where` and `eq`, and `count`. Listing everything and filtering in the browser is correct up to 100 rows and silently wrong after, because one page holds at most 100 records; "the comments on this article" is a `where` (or `eq`) condition, and "how many" is `count: true`. - `where` takes DynamoDB's full condition grammar (`eq ne lt lte gt gte between in begins_with contains is_null`, combined with `and`, `or`, `not`); `eq` is the shorthand for equality, ANDed with it, and filters on `_submitter_user_id` (a user id; signed-in callers only) as `where` does. Each value is compared as a read returns it: a column never written compares as its declared default. - A range on the column the list is sorted by — `eq`, `lt`, `lte`, `gt`, `gte`, `between` or, on a `text` sort, `begins_with`, ANDed at the top level of `where` — reads only the records in the range, and is billed only for them: "events of the next 7 days" sorted by `starts_at` with `{"starts_at": {"between": [now, now + 7 days]}}` reads those events and nothing else. `order` and the default order sort by `_created_at_ms`, so a `_created_at_ms` range narrows them the same way. - Every other condition — on another column, an `or`, a `not`, an `ne`, `eq` on `_submitter_user_id` or on the table's audience column — reads the records in order and drops the ones that do not match, and so does a non-owner's own-rows list on a `read_scope: "own"` table, which keeps the rows they submitted and the rows addressed to them. Each is billed for every row it reads, not only the rows it returns (`### Billing`). Either way the answer is only what the caller may see. A filtered page can hold fewer than `limit` rows, or none, while `next_cursor` is non-null: one call reads a bounded number of rows, and on a big table with few matches it returns the matches it found plus a cursor to keep reading. Never treat a short or empty page as the end, and never decide "no match exists" from one page: keep passing `next_cursor` back until it is `null`. A count works the same way: add up the parts until `next_cursor` is `null`. ### File columns A file is a value in a record: it always lives in exactly one record's `file` field, and there is no file outside a record, no list or search of files and no permission of its own — the record's rules decide everything. - **Declare** a `file` column with `tables/create` or `columns/add` (`"type": "file"`, no default); `tables/list` shows the type. - **Upload and attach** in three steps: `POST .../files/{column}/upload/start` with `{"content_type": "..."}` answers `upload_id` and `upload_url`; PUT the bytes straight to `upload_url` (with that `Content-Type`, no credential, within 5 minutes); then put `{"upload": ""}` as the field's value in `records/create` or `records/{record_id}/update`. That write is the completion: the record's field becomes `{"file_id", "content_type", "size_bytes"}`, where `size_bytes` is the size storage received. The bytes never pass through this service. - **One call with the SDK:** with `@pilely/simple-db`, pass a browser `File` or `Blob` as the field's value in `createRecord` / `updateRecord` (and their hooks); the SDK runs `upload/start`, the PUT and the record write for you. `null` clears the field. - **Who:** an upload can be started by whoever may create records in the table or update records in it; attaching follows the write it rides on (`records/create` or `records/update`); a file is readable by whoever may read its record (`file_urls` on `records/get` / `records/list`, or `.../files/{column}/download`); clearing or replacing it follows `records/update`, and deleting its record follows `records/delete`. Everyone else gets the uniform `404`, as for the record. - **A ticket** (`upload_id`) works once, only for the caller who started it, only for its own app, table and column. Every problem with it — unknown, already used, expired, another caller's, another table's or column's — is the same `400`; nothing PUT to its link yet is a `400` too, and a file over 2 GB is `413 file_too_large`. Any refused attach leaves the record unchanged and the ticket usable for another try until it expires. - **A field accepts only** `{"upload": ""}` or `null` from a caller: a `file_id`, a link or anything else is a `400`, so a record can never point at a file that was not uploaded into it. A `file` column is never an `eq` filter, an `if` condition or an `inc` target (`400`). - **Lifecycle:** a file exists from its attach until its record no longer holds it: the field set to `null`, a new upload attached in its place, the record deleted, or the database or app deleted. Its bytes are deleted only after that change commits, and a refused or failed write deletes nothing. Deleting the database or the app deletes every file in it, and the deletion is carried through to completion even if it is interrupted. An upload never attached is deleted 1 hour after its link expires and never counts toward storage. - **Links:** `upload_url` and every download `url` are signed links to the storage host (`.r2.cloudflarestorage.com`), never a pilely.app address; a download link is valid 5 to 10 minutes and reveals no `file_id`. Never store a link: ask again. - **Billing**, all on the DB owner: each `upload/start` is charged up front for every storage operation one upload can cause (the PUT, the size check at attach, the expiry check), whatever happens next; each download link (`download`, or each field `file_urls` signs) is charged as the storage reads it enables; an attached file counts toward the owner's storage by its size from attach to removal (`### Billing`). ### Compare-and-set loop Any rule decided from a record's current values ("at most 10 seats", "only if still open", "balance never below 0") is safe with any number of writers and any number of server boxes this way: 1. Read the record with `records/{record_id}/get` and `{"consistent": true}`. 2. Decide the new values from what you read. 3. Write them with `records/{record_id}/update`, sending `"if_version": `. 4. `200`: done; the answer is the record as you wrote it, with its new `_version`. `409 version_conflict`: someone else wrote in between and nothing changed; go back to step 1 and decide again. Stop after a few rounds and report the conflict. Never decide from a copy held in memory, and never "check then write" without `if_version`: another writer can land between the two. Use `_version`, never `_updated_at_ms`, to tell that a record changed. The loop runs wherever the caller may update the record: the owner's own surfaces, a `mutate_group` member (the app's backend server included, as an `app` member of that group), or, on a `mutate_scope: "own"` table, the record's submitter (`### Table access groups`). A server acting for its users stores the acting user's id in its own columns. ### Claim a slot To let exactly one party take something (a seat, a task, a username, a slot in a schedule), give it a nullable column such as `slot_holder` and claim it with one update: ```json { "if": { "slot_holder": null }, "fields": { "slot_holder": "" } } ``` `200`: the claim is yours. `409 condition_failed`: someone holds it already, and nothing changed. When many claims arrive at the same moment, exactly one answers `200`. Release it with `{"if": {"slot_holder": ""}, "fields": {"slot_holder": null}}`, so you never release a slot someone else has taken since. A column with a declared `default` compares as that default until written, so claim on its default value, or declare the column without one. Claims run wherever the caller may update the slot's record: the app's backend server, the owner's own surfaces, or a `mutate_group` member. A `mutate_scope: "own"` author may update only records they submitted, so a slot record shared by many users is claimed through the server or by `mutate_group` members. ### Counter To count (views, votes, uses, stock) with any number of writers and server boxes, keep one record per counter and add to it with `inc`; never read, add and write the value yourself. 1. Create the counter record once with `records/create`, `"key": ""` and `{"fields": {"total": 0}}` on an `integer` (or `real`) column. A `409 key_exists` means it exists already; its `id` is in the answer. 2. Count with `records/{record_id}/update` and `{"inc": {"total": 1}}` (a negative number subtracts). Concurrent increments never lose a count, and each answer carries the record as that increment left it, `total` and `_version` included. A column that reads `null` starts from `0`, a column never written from its declared `default`. An `inc` that would leave the column's range is a `400` and changes nothing. Add `if` to the same update to count only while a column holds a value, for example `{"inc": {"total": 1}, "if": {"open": true}}`; for a limit on the total itself, use `### Compare-and-set loop`. Counts run wherever the caller may update the counter record: the owner's own surfaces, or a `mutate_group` member (the app's backend server included, as an `app` member of that group); a counter shared by many users is never one user's own record, so `mutate_scope: "own"` does not open it to them. For one vote per user, have each user create their own keyed record (`"key": "vote"`) instead and count the records. ### Create once per external event A webhook sender (Stripe, GitHub, a payment or mail provider) retries, so the same event can arrive twice, on two boxes at once. Record each event with `records/create` and `"key": ""`: - `200`: first delivery; act on it. - `409 key_exists`: already recorded (the answer's `id` is that record); act on nothing and answer the sender with success. Exactly one of several simultaneous creates with one key answers `200`. Keys are unique per submitter: every box of the app shares the app's one key space, so every box sees the same events (the owner's own creates hold their own). A key is 1–200 characters from `A–Z a–z 0–9 _ - . : @ / +`; prefix it to keep senders apart (`stripe:evt_123`), and turn an id outside that set into one inside it the same way every time (its SHA-256 hex, for example). The same pattern keeps one row per user (`"key": ""`) or one run per job and period (`"key": "nightly:2026-10-07"`). ### Lease A lease lets one box at a time run a job (a scheduled run, a queue drain), and lets another box take over when the holder dies. It is a pattern on records, not a feature: a record keyed by the job's name with a `holder` (text) and an `expires_at_ms` (integer) column, written by the app's backend server — an `app` member of the table's `write_group` (to take) and of its `mutate_group` (to take over, renew and release; or, on a `mutate_scope: "own"` table, the write group alone, since every box shares the app's own records). 1. **Take:** `records/create` with `"key": ""` and `{"fields": {"holder": "", "expires_at_ms": }}`. `200`: you hold it; remember the answer's `_version`. 2. **Busy or expired:** a `409 key_exists` gives the record's `id`; read it with `{"consistent": true}`. If `expires_at_ms` is still in the future, someone holds it: try again later. If it has passed, take it over with `records/{record_id}/update`, `"if_version": ` and your own `holder` and `expires_at_ms`. `409 version_conflict` means another box took or renewed it first: back off. 3. **Renew** well before it expires: update with `"if_version": ` and a new `expires_at_ms`. A `409` means you lost the lease: stop the job. 4. **Release** when done: `records/{record_id}/delete` with `"if_version": `. A `409` means another box took it after your lease expired: leave it. Expiry compares each box's own clock, so make the lease much longer than the renewal interval plus any clock difference between boxes. Work a box does after its lease ran out is not protected: make each write the job does conditional (`if_version`, `if`) or keyed, so a late write from an old holder fails instead of landing. ### An empty group means "only me" A group is created at simple_group's `groups/create` with nothing but an optional display name, and creating it does not make the creator a member: the insert writes the group row only. The owner of the db always passes regardless of the groups, from their own surfaces (the app, the simple_db UI, their agent) but never through another app. So a group nobody is added to denies everyone else and leaves the owner full access. As `mutate_group` or `deny_group` an empty group changes nothing; there `null` already means no group. simple_group: `https://simple-group.pilely.app/README.md`. ### Access recipes `read_group` and `write_group` are both required per table (`null` = every signed-in user, through the db's app; a group nanoid = that group's members), and `anon_read: true` is optional and only valid with `read_group: null` and `read_scope: "all"`. The row-level settings are optional; a row that leaves them out keeps the defaults (`read_scope: "all"`, `audience_column: null`, `mutate_scope: "owner"`, `mutate_group: null`, `deny_group: null`), and `null` for `mutate_group` / `deny_group` means no group: | What the app needs | `read_group` | `write_group` | Row-level settings | | --- | --- | --- | --- | | Everyone signed in reads and posts (potluck board, shared list) | `null` | `null` | — | | Blog or notice board: all read, a few authors write | `null` | an authors group | — | | Suggestion box or inbox: anyone submits, only the owner reads | an empty group | `null` | — | | Team space: one group, both directions | the team group | the team group | — | | Private to the owner (notes, config, owner-only data) | an empty group | an empty group | — | | Public leaderboard or feed: signed-out visitors read, members post | `null` + `anon_read: true` | the members group | — | | Each user sees their own rows (my notes, my orders, my submissions), and edits them | `null` | `null` | `read_scope: "own"`, `mutate_scope: "own"` | | Owner to one person (a reply, a per-person status or line): each person reads only the rows they wrote and the rows the owner addressed to them | `null` | `null` | `read_scope: "own"`, `audience_column`: a `text` column such as `to_user_id`; the owner writes rows with it set to the person's user id | | Authors edit their posts: everyone reads, each author edits and deletes their own posts | `null` | `null` | `mutate_scope: "own"` | | Staff triage: anyone submits, staff read, mark, edit and delete every row | an empty group | `null` | `mutate_group`: the staff group | | Bans: banned users keep reading but can no longer create, edit or delete | any | any | `deny_group`: the banned group (adding or removing a member takes effect within 5 seconds) | User-to-user messages (a row one user addresses to another) are not possible with these settings: a non-owner may address a row only to themselves, and a backend app to nobody. Only you, the owner, address a row to another person. ### The uniform 404 Every no-access case answers the same `404`: a table, record or database that does not exist, a caller who is not in the table's group or has been removed from it, a record the table's `read_scope: "own"` hides from the caller, an update or delete of a record the caller may not change, a write by a `deny_group` member, a call through another app, an anonymous credential where anonymous callers are not admitted, and every caller but the owner while the owner has no traffic balance left (`## Errors`). A hidden record's `404` has the same status and body as an unknown record id's, and a list just leaves the record out. This is by design: a denial must never reveal that a table, a record or an app exists. A `404` alone therefore never means "not approved yet", "request pending" or "access ended". A page that must tell those apart reads a status the owner keeps in a table the person can read — on a `read_scope: "all"` table every reader sees every row, so keep in it only what all its readers may see, or address the status row to the one person through an audience column on a `read_scope: "own"` table; otherwise it shows one neutral message that fits every case, such as "nothing here for you yet". ### `anon_read` on the app's pilely host and on a bound custom domain `anon_read` is live on any `public` app; the app's `access_mode` is the only other ingredient, and there is no separate anonymous switch. `client.js` mints the anonymous credential from the apex with `POST /~/mint/anon` and `{"target": location.host}` on every app origin, a registry subdomain or a bound custom domain alike, and the mint accepts a bound custom domain as `target` and pins the token's `aud` to it exactly as it does for a registry subdomain. The mint answers one uniform `404` for an invalid target, an unknown app, an official pile, an unbound custom host and a protected or private app, so the app must still be `public`; only a public app can be bound to a custom domain in the first place. The page's browser calls to `simple-db.pilely.app` work from either origin: root grants a bound custom-domain origin the same non-credentialed CORS answer it grants a `