# Store

A site with code has its own database, the store. The site's code reaches
it as `env.DB` (Cloudflare D1, which is SQLite). These calls let you read
it, repair it, and take a copy.

The store is not in git, and it is not part of a version. Restoring an
old version brings back code, and the data stays as it is.

## Make the store

Write the schema as migrations in the site's repository:

```
migrations/0001_create_orders.sql
migrations/0002_add_stock.sql
```

Name each file with four digits, an underscore, and lowercase words. The
number sets the order. On publish, Clave runs each new migration once, in
order. If one fails, nothing is published, and the error names the file.
Never edit a migration that ran. Add a new one.

The first publish with a migration makes the store. The draft has a store
of its own, and it keeps its data from draft to draft.

Clave keeps the hash of each migration that ran:

- **On the live site**, a migration that was changed after it ran, or a
  new one whose name sorts before one that ran, stops the publish. The
  error names the file.
- **On the draft**, either of those, or a migration that was removed,
  empties the draft's store and runs every migration again. Draft data is
  test data.
- **A draft that changes a migration the live site ran** is refused, since
  approving it would fail on the live site.

## Seeds

Seeds put rows in. Migrations make the tables. Two folders sit next to
`migrations/`, with the same file names:

```
seeds/draft/0001_test_products.sql   the draft's store only
seeds/live/0001_first_products.sql   the live store only
```

After the migrations of a publish, Clave runs each seed of that kind that
the store has not run, in order, one time. A seed that fails stops the
publish, and the error names the file. Clave never copies live data into
a draft, or the other way.

`POST /v1/previews/{id}/reset-store` empties the draft's store and runs the
migrations and the draft seeds again, so a test starts from the seeds.

## Read

`POST /v1/sites/{id}/store/query`

```json
{ "sql": "SELECT id, status FROM orders WHERE status = ?", "params": ["paid"] }
```

One statement that only reads. The answer holds at most 1000 rows, and
`truncated` is true when there were more. Add `"slot": 1` to read a
draft's store.

## Repair

`POST /v1/sites/{id}/store/execute` takes one statement that can change
rows. Use it for a repair that no action of the site fits.

A raw write skips the site's own rules. For example, it marks an order
shipped and sends no mail. For daily work, call the site's action.

It never changes the schema. The answer holds `rows_changed` and a
`bookmark`. Clave keeps a record of each write. An account makes at most
20 writes a minute; more answers `429 rate_limited`.

## Undo

`POST /v1/sites/{id}/store/restore` with `{ "bookmark": "..." }` puts the
store back to that bookmark. Every write after it is undone, the site's
own writes too.

## Take a copy

`GET /v1/sites/{id}/store/export` answers with an SQL dump of the store.
A big store can take a minute. If the answer is `export_pending`, ask
again.

## One statement is one step

D1 has no transaction that stays open across a read in code. So "check
the stock, then take one" must be one statement:

```sql
UPDATE stock SET n = n - 1 WHERE id = ? AND n >= 1
```

Then check that one row changed. Two statements, a read and then a write,
can sell the last item twice.

---

## Where this page sits

This is one page of the Clave documentation.
Read it as a web page at https://docs.heyclave.com/api/store.
Every page has a `.md` address, and https://docs.heyclave.com/llms.txt lists them all.

- Previous: [Egress](https://docs.heyclave.com/api/egress.md)
- Next: [Runtime](https://docs.heyclave.com/api/runtime.md)
- Index: [Overview](https://docs.heyclave.com/index.md)