Concepts

This page lists what relstore enforces today. Where something is missing or partial, it says “Not yet”.

Databases and clients

A database is your own namespace of tables. Stackwell creates it and binds two ChirpAuth clients to it:

• One client, one database, one role. A client is bound to exactly one database as either its agent or its operator, never both.
• The token decides the database. No URL or body names a database, so nothing you send can point a request at another one.
• Isolation is enforced by Postgres. Each database is a separate Postgres schema with its own role, and your requests run as that role. Postgres refuses that role any access to another database’s tables.

Tables

POST /v1/tables creates a table with a name and at least one typed column:

Every column also accepts null. Set "indexed": true on a column you filter on often to give it an index.

• Names. Table and column names start with a letter and use only ASCII letters, digits and _, up to 51 characters for a table and 63 for a column. A longer name is refused with 400. These column names are reserved: rid, version, valid_from, deleted, env, updated_ts, written_by, written_as.
• Created once. Creating a table that exists returns 409 and changes nothing.
• Not yet: changing a table. Columns can’t be added, removed or retyped, and a table can’t be renamed or dropped.

Rows and versions

A row is identified by its rid, a string you choose. Don’t put personal data in a rid: when a row is permanently removed, the record of its removal, which names the rid, is kept. Each write to a rid appends a new version:

• Whole values. A write sets every column, so fields must name every column of the table: send null to clear one. A write that leaves a column out, or names a key that is not a column, is refused with 400 and writes nothing, so a typo cannot blank the row.
• relstore numbers the versions. Versions of a rid count up from 1. A write can’t set version, valid_from, written_by or any other reserved column (400). Concurrent writes to one rid each get their own version, in the order they commit; the last to commit is the current row.
• No rewrites. No request can change or remove an existing version. The Postgres role your requests run as may only insert into and read a table’s version log.
• Upsert or conditional. POST /v1/rows appends whatever the row’s state. Three writes append only from the state they need, and otherwise are refused and write nothing: an insert (409 if the rid was ever written), a patch (sends only the columns to change; the rest keep their current values; 404 with no row, 409 if it is soft-deleted) and a restore (below). Each still appends one whole version.

Soft delete

POST /v1/rows/{rid}/soft-delete appends a tombstone version: every column is null and deleted is true. The row disappears from queries, and its history keeps every version, the tombstone included. POST /v1/tables/{table}/rows/{rid}/restore brings it back with the values of its last live version, as the next version; writing the rid again with POST /v1/rows brings it back with the values you send.

A soft delete of a rid that was never written still succeeds and records a tombstone as version 1.

Who wrote each version

Every version records:

• written_by: the ChirpAuth client id of the caller, taken from its verified token;
• written_as: that client’s role, agent or operator.

The request can’t set either. Today only agent clients can write, so written_as is always agent.

Queries

POST /v1/query reads the current version of each row that is not soft deleted. A query names one table and may add:

• select: the columns to return. Without it you get rid, updated_ts (when the current version was written) and every column.
• filter: one condition, which can nest; see API examples for every operator.
• order_by: columns, each ascending unless "descending": true. Without it the order is not defined.
• limit and offset, for paging.

Values are always passed to Postgres as parameters, never pasted into SQL. begins_with and contains match literally: % and _ have no special meaning.

A query that matches more than 10,000 rows is refused (422) rather than cut short. Set a limit of at most 10,000 to page through more.

Not yet: aggregates (count, sum, …), joins, and queries over past versions. To read past versions, use a row’s history.

History

GET /v1/rows/{rid}/history returns every version of one row, oldest first, including tombstones, with version, valid_from (when it was written), deleted, env, written_by and written_as beside its columns. relstore assigns version (1, 2, 3, … per row) and valid_from; no caller can set them. A rid that was never written returns [].

It returns at most the first 10,000 versions of a row. Not yet: reading versions past the first 10,000.

Removing data

Not yet. relstore has routes to request that a row, a table or a whole database be permanently removed, or that every row of a database be erased (its tables and clients stay), after a person approves it. This is not enabled on the live service: submitting a request returns 503. Until it is, nothing written to relstore can be removed through the API. See Data and privacy.