Data tables

Tables a site declares and holds - a product list, an events calendar, a directory - read on a page like any other variable.

Overview

A data table is a set of records the site owns: a product list, an events calendar, a staff directory. You describe its shape in a small YAML file, the engine keeps the records, and a page reads them through the same page-variable mechanism it uses for everything else.

Tables hold site data. Per-visitor state -- a session, a shopping basket, somebody's profile -- is an application, and deliberately out of scope.

The feature ships disabled. Enable Data tables on the Extensions page before anything below will answer.

The three steps

1. Declare the table

A table is described by YAML. You do not write that file directly: it lives under lazysite/, which holds the account store and the session secret, and every general write channel refuses that area on purpose. Use the door built for it, which also checks the descriptor before storing it:

title: Products
key: code
fields:
  code:
    type: text
    required: true
    max: 20
  name:
    type: text
  price:
    type: decimal
    digits: 8
    places: 2
  in_stock:
    type: boolean
    default: true

The table name becomes the filename and must be lower-case letters, digits and underscores. A descriptor that does not load is refused with the field and the reason, and nothing is written -- so a refusal here is information rather than a failure.

timestamps: true adds created_at and updated_at (UTC, to the second) and created_by and updated_by (the signed-in account's login; empty for a public form), stamped by the extension on every insert and update and refused from any writer -- the one provenance a reader can trust, because no caller can supply it. A field you declare yourself named like a stamp (added_at, added_by) is whatever the writer sends, and the table's description says so. A table that had the flag before the author columns existed gains them as an additive migration: data-migrate-plan lists them, data-migrate adds them.

2. Create or update the stored table

Declaring a table does not create it. Run the migration once:

It is safe to re-run: a table already in line is a no-op. It applies what is safe and reports what it refuses, and the refused list is the useful half -- it is the account of why a column is not there yet.

To see that account before anything is applied, ask for the plan:

It runs the same planner the migration does and changes nothing, so the preview and the action cannot disagree. To edit a descriptor as the text you wrote it -- comments, key order and spacing intact -- read it back with read_data_table_source (MCP) or data-table-source (Control API), change it, and save it again.

3. Put rows in, and read them out

A save has two shapes, and the difference is where the key goes:

On a page:

---
title: Our products
tt_page_var:
  products: db:products(order=name,limit=20)
  total:    db:products.count()
---

and in the layout:

[% total %] products
[% FOREACH p IN products %]<li>[% p.name %] — [% p.price %]</li>[% END %]

Values are escaped for you - do not add | html

From 0.13.0, every value read with db: is HTML-escaped as it reaches the page. [% p.name %] prints a row holding <b> or <script> as the text it is, never as markup, so a table anybody can write to cannot put script on a page that displays it.

So do not add | html. On 0.13 it escapes a second time, and a value containing &, <, >, " or ' shows as &amp;lt; or &#39; - quietly, and only for the rows that happen to contain one, which is how it goes unnoticed until somebody adds Smith & Jones.

On 0.12 and earlier it was the other way round: values were printed raw, and | html was the correct advice. A template written correctly for 0.12 is therefore wrong on 0.13, and the upgrade does not say so. lazysite check lists every page that binds a table with db: and also uses | html, so those templates can be found before a visitor finds them. On a site still on 0.12, either upgrade, or keep markup out of any table a page renders.

There is no switch that turns the escaping off. db_render_raw, which did, shipped deprecated and was removed in 0.13.13 - no site was found to need it - and an upgrade deletes the key.

A value used as a link target - add | url_safe

Escaping keeps a value inside its attribute; it does not change what the value says. A row whose url is javascript:alert(1) is still a script link when it is written straight into an href. Wherever a value is the WHOLE href, src or action, write it through the filter:

<a href="[% r.url | url_safe %]">[% r.label %]</a>
<img src="[% r.photo | url_safe %]" alt="[% r.label %]">

url_safe passes a value with no scheme (a path, ?query, #fragment or //host) or one starting http:, https:, mailto: or tel: unchanged, and turns anything else - javascript:, data:, vbscript:, file: - into #. It reads the scheme through character references and percent-encoding and ignores case and leading spaces, and it does not escape the value a second time. A value with fixed text before it (href="/people/[% r.id %]") takes its scheme from that text and does not need it.

What you may ask for

columns: Written | Means
widths: 7.4cm | X
bold: 1
tone: medium
---
`db:products` | the whole table, declared order
`db:products(featured=true,limit=4)` | filtered, AND-combined
`db:tasks(order=due)` / `(order=-due)` | ordered, ascending or descending
`db:tasks.count(done=false)` | a number, not a list
`db:products.field(price,code=SKU1)` | one value out of one row
`db:applications(mine,status=open)` | the signed-in viewer's own rows -- see *A page that shows each viewer their own rows*

The older spacing -- db:products sort=name asc limit=20 -- still means exactly the same thing.

You may filter or order on any declared field. Filtering on a field with no index means the whole table is examined, which for the sizes these tables are for costs about as much as nothing: measured at 100,000 rows, an unindexed order by ... limit 10 takes 5.6 ms against 0.03 ms indexed, and a filter on a common value costs nothing either way because the limit stops it early.

If a read does turn out slow, the log says which binding, how long, and which field to index:

indexes:
  - [name]

A compound index [area, street] makes area= cheap and leaves street= a scan -- an index can only be entered at its first column.

If a table is big enough that an index does not settle it, it has outgrown SQLite rather than outgrown the query, and the answer is a different engine.

Filter values are checked against the field's declared type, so done=perhaps on a boolean is refused and says so rather than quietly matching nothing. limit= is capped at 500: ask for more and the binding clamps to 500 and warns in the render log, naming the page -- serving what it can beats rendering nothing. With no limit= at all the default is 200, so a list that outgrows 200 renders short; the render log says so, and the page can too: every list binding gets a companion <var>_total variable carrying the true count, so a template can write showing [% items.size %] of [% items_total %]. .count() is the true count as well -- it ignores any limit= beside it.

Anything this grammar will not express -- joins, ranges, OR -- is deliberate. Front matter is not a place for a query language.

The data endpoint takes order_by, order, limit and offset in its query string and runs them through this same grammar, so a page and its own script cannot be told different things about one table.

When the rows are read

columns: Mode | When
widths: 3cm | X
bold: 1
tone: medium
---
`snapshot` | **the default.** Read at render. The page is served from cache for its `ttl:`, and re-rendered on every request if it declares none
`live` | as `snapshot` today -- see the note below. Intended for stock levels and queue lengths
`client` | as `snapshot` today: the rows are still read at render. What makes a page client-side is a `data-ls-db` region (below), which the page's own script fills from the endpoint

  stock: db:stock(mode=live)

live and snapshot currently behave identically, and this table used to say otherwise. It said live was "read on every request, the page is never cached". That was wrong in a way worth spelling out, because an agent read it, reasoned correctly from it, and reached a false conclusion.

A table has no timestamp that can prove a cached page still current, so a binding withdraws the mtime proof of freshness. It does not touch the page's ttl:, which is a separate mechanism. So a page carrying ttl: 300 is served from cache for five minutes whatever its mode, unless the table changes (below), and a page carrying no ttl: is re-rendered every request whatever its mode. "Never cached" was only ever true of the second case.

Since SM604 every db: binding withdraws that proof - it had to, because otherwise one json: binding on the same page vouched for the table's freshness, which nothing can do. That is what leaves the two modes with no observable difference. mode=live is accepted and parses; it does not change behaviour today. Do not choose between them on the strength of caching: choose a ttl:.

A row you change refreshes the pages that show it, at once (from 0.16.8). Every write the engine makes to a table - a row saved, deleted or imported, on any door - touches the table's change stamp (lazysite/db/stamps/<table>), and a page that read the table is rendered again on its next request, whatever its ttl:: the same as saving the page itself. Nothing depends on the database file's timestamp, because the store is written through WAL and a row can change without that timestamp moving. A row changed outside the engine (an sqlite3 shell on the store) touches no stamp, and such a page refreshes when its ttl: runs out, as before.

Available today: a switch per row

A list that changes day to day -- a menu's dishes, the services on this week -- is kept once, and each item is switched on or off. Name the table's boolean field that says so:

title: Menu
key: slug
public: true
availability: today
fields:
  slug:  { type: text, required: true }
  name:  { type: text, required: true }
  price: { type: decimal, digits: 6, places: 2 }
  today: { type: boolean, default: false }

availability must name a declared boolean field; anything else is refused when the descriptor is saved.

tt_page_var:
  dishes: db:menu(available,order=name)
  menu:   db:menu(order=name)

[% FOREACH d IN dishes %]<li>[% d.name %] — [% d.price %]</li>[% END %]
[% FOREACH d IN menu %]<li[% IF NOT d.today %] class="off"[% END %]>[% d.name %]</li>[% END %]

(available) on a table that declares no availability field renders nothing and the log says why, rather than giving every row.

Every row change is recorded, and can be undone

Each row saved, deleted or switched -- in the manager, over the control API, over MCP, through the data endpoint or by a wizard -- is recorded as one change: when, who, from where (manager, api, mcp, endpoint, wizard:<name>), what (insert, update, delete, switch, undo) and the row before and after. The answer to the write names the change (change).

The changes are kept in the data store itself, in a table named _row_changes beside the rows. A bulk import, a package restore and a form handler's rows are not recorded one by one: each has its own record.

How long the history is kept

A table's changes are kept for 90 days by default. A table sets its own period in its descriptor, in whole days:

title: Specials
key: slug
row_history_days: 30
fields:
  # ...

row_history_days is a positive whole number (at most 36500); anything else is refused by name when the descriptor is saved. describe_data_table and the history listing show the period in force.

A change older than its table's period is removed, and a deleted row's changes go the same way, the delete among them. Removal happens in two places, across every table: when the store is written (each recorded change removes those past their period), and once a day in the daemon's row-history-prune job, so a store nobody writes to is cleared too. The job runs as the daemon's job account (daemon_job_user) and needs nothing beyond run_jobs, so no missing grant stops it; each run writes one audit row (row-history-prune) saying how many tables the history held and how many changes went. Reading the history removes nothing, so a change past its period can still be listed until one of the two runs; it is shown without an Undo (undoable false) and undoing it is refused as expired-change.

Wizards: build a row one question at a time

A wizard asks one question per step and builds, or edits, one row. It is a file, lazysite/db/wizards/<name>.yaml, saved with MCP save_wizard or control API data-wizard-save (manage_data), and checked against its table before it is stored:

title: Add a dish
table: menu
steps:
  - field: slug
    ask: A short name for its address
    type: text
    required: true
  - field: name
    ask: What is the dish called?
    type: text
    required: true
    help: As it should appear on the menu.
  - field: price
    ask: How much is it?
    type: price
  - field: spicy
    ask: Is it spicy?
    type: yesno
  - field: heat
    ask: How hot?
    type: choice
    options: [mild, medium, hot]
    when: { field: spicy, is: true }

columns: Type | Writes | Takes
widths: 2.6cm | 4cm | X
bold: 1
tone: medium
---
`text` | a text field | the answer as written
`price` | a decimal field | an amount; a currency sign and thousands commas are taken off
`choice` | an enum field, or a text field with `options` | one of the options (an enum's values by default)
`choices` | a text field with `options` | several options, stored comma-separated in the listed order
`yesno` | a boolean field | yes or no
`date` | a date field | a date, `YYYY-MM-DD`

A step may be required, may carry help, and may depend on an earlier answer with when: { field, is } (is may be a list). A field the table requires must be asked by a required step with no when, and the table's key must be asked unless the store assigns it: required facts are always asked, never guessed.

Running one is stateless: send the answers so far and get the next question, or complete with the row. MCP run_wizard {wizard, key, answers, save}; control API POST ?action=data-wizard-run; in the manager, the wizard's button above the rows (a new row) or Ask on a row (that row, each question showing what it holds). A required question cannot be skipped; an optional one is skipped with an empty answer. With save: true and every question answered, the row is written through the ordinary row save, recorded as from wizard:<name>; it needs what writing the row needs. list_wizards / data-wizards lists them.

Removing a table

A table can be removed, and until 0.10.27 it could not be -- declaring one was reachable from three surfaces and removing one from none, so a table made by mistake, or renamed, or created for a single test was permanent.

Every drop, and every rebuild that loses a column, writes a safety export first under lazysite/db/rebuilds/ -- the only copy of the rows it removed. list_data_safety_exports (control API data-safety-exports) lists them with their table, kind and stamp; delete_data_safety_export (data-safety-export-delete) clears one by its exact file name, audited. read_data_safety_export (data-safety-export-read) opens one -- table, key, fields, rows -- and the listing carries each export's row count and a key sample, so it can be judged first. restore_data_safety_export (data-safety-export-restore) offers the rows back to their table: a plan without apply, a write with it; columns the table no longer has are reported, not refused -- re-declare them and restore again. Read an export, or know it came from a throwaway, before clearing it.

It takes everything: the descriptor, the stored table and every row. So it asks first, and the confirmation is the table's own name rather than a yes:

drop_data_table { "table": "old_prices" }
-> needs_confirmation, naming what would be lost

drop_data_table { "table": "old_prices", "confirm": "old_prices" }
-> dropped

A safety export is written before anything is dropped, beside the rebuild exports in lazysite/db/rebuilds/, and its path is returned. The table is not recoverable; the data is.

If the export cannot be written, nothing is dropped.

Field types

text
Any string. max: limits its length; widget: textarea (or input) says how it should be edited -- a 500-character description and a short title are the same kind of value and differ only in how you type them.
integer
A whole number. min: and max: bound it.
decimal
A fixed-point number, for money and anything else that must not drift. Requires digits: and places: -- a decimal without them is a float wearing a name. Stored and returned as a string so it never passes through a floating-point value: 120.00 comes back as 120.00.
boolean
Yes or no. Accepts 1/0, true/false, yes/no, and stores 0 or 1.
date
YYYY-MM-DD, checked against the calendar -- 2025-02-30 is refused.
datetime
YYYY-MM-DD HH:MM:SS, UTC, normalised to one spelling so string ordering and chronology agree.
enum
One of a declared list. Requires values:.

Every field may carry required: true and a default:.

What a write is refused for, and why

The store checks rather than repairs. Each of these is refused with the field named:

An empty value means absence, not zero: clearing a number stores nothing rather than 0, since 0 would be data the author did not enter. A text field keeps its empty string, which is a real value.

Changing a table

Editing the descriptor and re-running the migration handles anything additive -- a new field, a new index, a default filled in on the rows that predate it.

Three changes are refused and reported instead: changing a field's type, tightening one to required, and removing one. All three rewrite the whole table, which is not something to do because a descriptor changed.

When you do want one, use rebuild_data_table (MCP) or data-rebuild (API). It asks you to name each column whose data will be lost -- call it without the list first to be told which those are. A list rather than a yes/no on purpose: agreeing to lose one column you read about should not agree to a second you did not notice. Every row is exported to lazysite/db/rebuilds/ before anything is dropped, and the path comes back with the result.

Backups and handing a site over

A backup carries table data. A site package carries it only when you ask, naming the tables:

{"host": "clients.example", "data_tables": ["products"]}

Opt-in because a package is a portable hand-over artefact: shipping table contents by default would mean handing a third party whatever is in a directory or a contact table. The tables are named rather than switched on, because the store is instance-wide -- there is no such thing as this domain's data, and a flag would sweep another site's tables into the package.

A package says what it left behind: data_omitted counts declared tables it does not carry, so whoever receives it learns the tables exist. On apply, a table that already holds rows is refused, not overwritten -- restoring over a live product list would replace it with a snapshot from whenever the package was built.

A restored row keeps what the package says about it:

The account that applied the package is recorded in the log and the audit trail, and data_restored says how many rows kept each (keys_kept, authors_kept, times_kept, policies_kept). A package built before 0.15.5 carries no authors, times or policies: its rows are recorded as the applier's, at the time of the restore, with no policy.

Access

Reading and writing tables through the manager, the API or MCP needs the manage_data capability, granted like any other through a group.

A page binding is not a capability question: it renders as part of the page, so a gated section's table is as reachable as the section is.

On an instance hosting several domains

manage_data is an instance capability, and a table name is instance-wide -- there is one lazysite/db/tables/ for the whole instance, not one per domain. On a single-site instance that is all there is to know.

Where one instance serves several domains belonging to different people, say which domain a table belongs to:

domain: shop.example.com

A caller whose grant confines it to one domain's content then reaches that domain's tables and no others -- and is not told that the rest exist, because a table name is itself a disclosure. That is why an unpublished table is invisible to a visitor in the first place.

A table that names no domain is reachable by any manage_data holder on the instance. That is deliberate, so an upgrade takes nothing away from a running application, and it means the protection is opt-in: until you write domain: on a table, a partner scoped to a neighbouring domain can read it.

Two things follow, and both matter on a shared instance:

The older mitigation still works and composes with this: ACL lookup takes the longest matching prefix, so a restrictive rule on lazysite/db/tables plus a per-table rule for each site's own people closes the same gap by hand.

The order the table is in

A gallery is an ordered list -- somebody chose the sequence. Say so once, on the table, rather than repeating order= in every binding and getting it wrong in one of them:

default_order: position     # or -position, for descending

A binding that names its own order still wins; this fills the gap when one does not.

A table that numbers its own rows

Leave key: out (or write key: id) and the table numbers its rows itself: an insert carries no key, and the store gives it the next integer as id. That is the shape for a table a form writes to - one row per enquiry - where the only value a visitor brings that could identify a row is their email, and a table keyed on that keeps only each person's latest. id is then reserved: no field may be called that.

Saying a value is unique

key: says which field identifies a row. To say that another field must also be unique -- a slug, a reference, an email -- mark the field:

fields:
  slug:
    type: text
    unique: true

Two rows cannot then share a value. Empty values are exempt: any number of rows may leave it blank, which is what "unique" means everywhere else and is worth saying because the opposite is a fair guess.

Adding unique: true to a field that already holds duplicates is reported, not attempted -- the migration tells you which value appears twice, and you make the existing rows distinct before migrating again.

A table is closed until you publish it

A page's own JavaScript reads rows from /cgi-bin/lazysite-data.pl?table=<name>, and that address is reached directly -- it inherits nothing from any page. Putting a table behind a gated page gates the page, not the table.

The endpoint reads four query parameters and no others: order_by, order, limit, offset. Anything else is refused - ?table=t&chunk=AAA answers 400 with kind: unknown-parameter, naming chunk and the four it reads, rather than every row (SM1377; before 0.16.6 it was ignored, SM606). The one exception is _, the usual cache-buster, which is accepted and left unread. Filter on the page binding (db:t(chunk=AAA)), which does support conditions, or filter the rows in your own script after reading them.

So publication is declared on the table itself:

public: true
key: code
fields:
  code:
    type: text

public (like timestamps, required and unique) accepts true/false, yes/no, on/off or 1/0 in any case; any other word is refused when the descriptor loads, so a flag never silently reads as the opposite of what you wrote.

The default is false, and a table is a store rather than a published artefact -- a file is under the docroot because you put it there to be served, and a table is not. Until you publish it, an anonymous visitor sees nothing: not the rows, and not that the table exists at all. It answers exactly as a table that was never declared does, so nobody can discover your table names by guessing at them.

public is about anonymous visitors. Any signed-in account may read an unpublished table unless an ACL says otherwise, which is the next section.

Showing only some of the rows

Publishing a table publishes every row in it. To keep some back - rows a moderator has marked hidden or spam - give the table a row condition:

public: true
where: status = ok

or, to show everything but the rows marked one way,

where: status != spam

The condition is applied wherever a visitor can see rows: page bindings (db:<table>, .count, .field) and the data endpoint. It is added to any filter the binding asks for, so a binding cannot reach the rows it hides, and a count counts only what is shown. The manager, the API and MCP -- the surfaces behind manage_data -- still see every row, which is how a moderator finds the ones that are held back.

It is one comparison and nothing more: a declared field, = or !=, and a value, read as that field's type would read it (an enum value must be one of its values). Anything else -- another operator, AND/OR, a field the table does not declare -- is refused when the descriptor loads. With !=, a row whose field is empty is shown: it has not been marked.

Narrowing to accounts and groups

Tables use the same read/write lists as files, in the same store, with the same meaning: entries are a username or @group, and no entry means no restriction. A table's ACL path is its descriptor's own path:

lazysite/db/tables/<name>

Because the matcher takes the longest matching prefix, a rule on lazysite/db/tables governs every table at once, and a site-wide private rule covers tables exactly as it covers pages.

A read list and public: true compose rather than compete: a published table that also carries a read list still refuses an anonymous visitor, because an anonymous visitor matches no entry in a list.

The lists bind a manage_data holder too (SM982). manage_data says an account may work with tables; a table's own rule says who may read its rows and who may change them, and the manager, the API, MCP and the data endpoint all ask it: a read list the account is not in refuses its row reads and downloads, and a write list it is not in refuses its row saves, deletes, imports, restores, rebuilds and drops, with a reply naming the table's rule. Only the operator -- the command line as local, an unsecured site, or a signed-in member of the operator group -- is not asked; manage_users alone is asked like anyone. A page is never treated as an operator -- it renders the same rows for whoever is looking at it, so what you see while signed in is what your visitors see. The one exception is a binding that says (mine), which asks for the viewer's own rows -- see A page that shows each viewer their own rows, below.

Collecting data from visitors

A visitor cannot write to a table directly, and that is deliberate: the data endpoint refuses an anonymous write. Use a form. A form has rate limits, spam assessment, quarantine and an audit trail, and a data binding taking anonymous writes would rebuild that surface without any of it.

Point a form at a table through a table handler - created on the Handlers page, with save_handler / handler-save, or with lazysite-handlers.pl, by an account holding manage_data (the destination decides who may configure a handler; see Forms). It lands in lazysite/forms/handlers.conf:

handlers:
  - id: store-enquiries
    type: table
    name: Enquiries
    table: enquiries
    fields: name=name,email=email,message=body
    keep_copy: false

Binding a form to it then needs only manage_forms. The schedule can call the same handler with fixed fields, as the daemon's job account.

fields reads form field = column, and it is required. A form field nobody maps is dropped, so a form gaining a field cannot start writing a column, and a visitor cannot choose where their data goes by naming a field after one.

Values go through the same checks as any other write, so a submission that does not fit the declared types is refused rather than stored wrong -- the visitor is told the submission failed instead of being thanked for one that was quietly lost.

The data extension must be enabled; a form pointed at a table while it is switched off refuses and says so.

With keep_copy: true - the default - the handler also keeps the JSONL submissions store written alongside the row -- the Submissions page, read_form_submissions, exports and bulk delete keep working exactly as for a file handler. A submission the table's types refuse leaves no row and the stored copy is marked _row_refused, the same shape as a rejected import row. Reading the table's rows is governed by the table's own declaration, like any other table: manage_data on the operator surfaces, page bindings only what the descriptor declares readable, and public defaults to closed -- a form never publishes its submissions by landing them in a table. The JSONL copy stays under read_submissions.

files_column: names a column of the table to receive the submission's uploaded files, as site-relative paths, comma separated -- one path per file, in the order the browser sent them. Without it the files are reachable only from the submissions record, so a lead worked from the table has no photograph. A submission with no attachment leaves the column empty rather than writing an empty string. It needs keep_copy on, because the copy is what stores the files, and the column name is checked against the table's own descriptor when the handler is saved rather than at a visitor's submission. A file that could not be written is named on the stored record and in the delivery's audit line: the submission still succeeds, and does not read as a visitor who attached nothing.

A row can belong to the account that wrote it

write_data is the grant for an app's own users -- a learner writing their own submissions, a member updating their own record. It is a grant over a table, so without the flag below, ten applicants holding it on one applications table may each amend and delete the other nine's rows.

row_policy: true gives every row in the table its own disposition:

row_policy Who may write the row
personal the account in created_by, and an operator
shared anyone the table already admits
empty the same as shared

The policy is set by whatever creates the row, at the moment of writing -- pass row_policy in the row like any other value. Nothing infers it: an empty created_by already means "written anonymously by a public form", and reading shared out of that absence would give one representation two meanings.

A form handler says what its rows are. A table handler takes row_policy: personal, and each row it writes then belongs to the account that submitted it -- so one applicant maintains their own application and nobody else's. Leave it out and its rows are shared, which is how a handler behaved before this existed, so nothing changes for a handler that says nothing. personal needs the table to declare row_policy: true (refused when it does not) and it needs the submitter to be signed in: a public form records no account, so its rows would belong to nobody and only an operator could amend them. The row is still stored in that case -- losing a submission to a misconfiguration the visitor cannot see would be worse -- and the delivery's audit line says what happened.

Reads are not confined by this -- row_policy decides who may write a row. To show each person their own rows, bind with (mine), which is the next section.

A page that shows each viewer their own rows

tt_page_var:
  applications: db:applications(mine,order=-created_at)
  mine_open:    db:applications.count(mine,status=open)

mine adds created_by = <the signed-in viewer> to the binding, and the engine writes that value -- the page never does, and nothing a visitor sends can change it. The viewer is the account the sign-in verified (or the one an auth proxy names, where the operator has set auth_proxy_trusted: true); an identity header a visitor sends is discarded before the page renders. mine combines with the other filters (AND), with order=, limit= and offset=, and with .count() and .field(). It is the bare word: mine=x is an ordinary filter on a field called mine, and a literal created_by= beside mine is refused.

Who may write

A write through the manager, the API, MCP or the data endpoint needs all three:

Quietening a table's row audit

Every row write is recorded in the audit trail with the row's key, so an auditor can tell which row changed rather than only that the table did. On a table with thousands of rows that is thousands of lines.

audit_rows: off in the descriptor turns the per-row lines off for that table only. Nothing else turns them off: an absent key, a typo, or any value other than off / false / 0 leaves the table audited, because a misspelling must not quietly stop recording who changed what.

key: code
audit_rows: off
fields:
  code:
    type: text

The table-level events are unaffected. Declaring, altering, migrating, dropping and importing each remain one audited event whatever this says - so quietening a noisy table never silences the record that it was restructured, emptied or bulk-loaded.

Before reaching for it: a bulk load belongs in data-import, which is already one event for the whole file. The volume this addresses is an application writing rows one at a time, where each really is a separate act.

writable_by only ever takes access away. It cannot grant a write to an account without manage_data, because the descriptor is a file an agent can write -- if the list could widen, an agent could hand write access to a group it chose.

writable_by means two different things, depending on which capability the caller holds.

For manage_data it only ever takes access away: a holder writes every table, except those naming groups it is not in.

For write_data it is an allow-list: a holder writes only the tables that name one of its groups, and a table naming nobody is closed to it entirely.

Caller holds no writable_by writable_by: [secretaries]
manage_data writes writes only if in secretaries
write_data refused writes only if in secretaries

That asymmetry is what keeps the descriptor from being a grant. The file is writable by any agent holding manage_data, so if the list could hand write access to an account that had none, an agent could give it to a group it chose. It cannot: write_data comes from the group store, where an operator granted it, and the list only says which tables that grant reaches.

write_data is the grant for an app's own users -- a learner writing their own submissions, a member updating their own record. It permits row insert, update and delete and nothing else: no declaring, altering, migrating or dropping a table, and no reach into a table that does not name them. Where manage_data would mean handing instance-wide data administration to your least-trusted user class, this is what to grant instead.

key: code
writable_by:
  - secretaries
fields:
  code:
    type: text

Leave it out and any manage_data holder may write.

An import is a row write (SM1052). data-import follows the same rule: on a table with writable_by, an account the list names imports, and one it does not name is refused. To bulk-load such a table, add your group to writable_by - never clear the list for the load and restore it after. The table's owner may also import past the list: the owner of its access rule (the account that first set it with data-table-acl-set, or the one the operator named). A table with no access rule has no owner.

writable= on a page binding is not a permission. It tells the page's own script whether to offer editing controls. The endpoint cannot see which page called it, so a marker in front matter could never gate anything.

Calling the endpoint

/cgi-bin/lazysite-data.pl -- reached directly, so it is available to a page's JavaScript wherever that page lives.

columns: Call | Answers
widths: 8cm | X
bold: 1
tone: medium
---
`GET ?table=notes` | `{ok, table, rows}`
`GET ?table=notes&order_by=code&order=desc&limit=20&offset=40` | the same, ordered and paged
`GET ?csrf=1` | `{ok, token}` for a signed-in caller, and nothing for anyone else
`POST ?table=notes` with `X-CSRF-Token` | `{"row": {...}}` to insert, `{"key": "...", "row": {...}}` to update
`POST ?table=notes` with `{"key": "...", "delete": 1}` | removes that row

A write that is refused says which of the three gates stopped it -- kind is anonymous, csrf or forbidden -- so a page can tell "sign in" from "your token went stale" from "this is not yours to edit" and say the right thing.

Fetch the token once per page and reuse it; fetch a fresh one and retry if a write comes back csrf.

Filling a region from the page itself

You do not have to write the fetching. Declare a region and what one row looks like, and the shipped helper does the rest:

<ul data-ls-db="products" data-ls-db-order="-price" data-ls-db-limit="10">
  <template>
    <li><span data-ls-field="name"></span> —
        <em data-ls-field="price"></em></li>
  </template>
  <p data-ls-empty>Nothing here yet.</p>
</ul>

columns: Attribute | Means
widths: 6.4cm | X
bold: 1
tone: medium
---
`data-ls-db` | the table to read
`data-ls-db-order` | a field, `-field` for descending
`data-ls-db-limit` / `-offset` | how many, from where
`data-ls-db-every` | refresh every N seconds -- **opt in**, minimum 5
`data-ls-field` | on any element inside the template: put this column's value here
`data-ls-empty` | shown when there are no rows, hidden when there are

The script is added to a page only when that page has a region, so a site that never uses one ships nothing extra.

Values are inserted as text, never as markup. A row containing <script> renders as those characters. This matters because rows can arrive from a public form, and a helper that treated them as markup would turn a contact form into a way to run script on every visitor's page.

A refresh that fails leaves what is already on the page. Content a minute old beats an empty list because one request timed out.

Regions load when they come into view, so a table below the fold on a page nobody scrolls costs nothing. Nothing polls unless you set data-ls-db-every.

The helper reads through the same endpoint and the same rules as anything else: an unpublished table returns nothing to it, exactly as it does to anyone.

Rows that show between two dates

A table may declare shows_from and shows_until (either or both, each a date or a datetime). A row outside its window is not shown - by a page binding, its _total and .count(), and the data endpoint - with no update needed when the date comes:

fields:
  title:
    type: text
  shows_from:
    type: date
  shows_until:
    type: date

A page showing such a table expires when the next row enters or leaves its window, so the offer that ends tonight is gone from the cached page at midnight. The manager's data page, the control API and MCP - the manage_data surfaces - list every row, so an editor finds the one that has not started yet.

Schedules

A schedule is the site's opening hours: weekly periods (several a day if it closes for lunch) and exceptions (a date or a date range, closed or with other hours). It is a table whose descriptor says schedule: true - the engine gives it its fields, so every schedule reads the same way:

title: Opening hours
schedule: true
public: true

columns: Field | Holds
widths: 3.2cm | X
bold: 1
tone: medium
---
`kind` | `period` or `exception`
`day` | a period's day: `mon` ... `sun`
`opens`, `closes` | `HH:MM`, 00:00 to 24:00; a period closing before it opens runs past midnight (`18:00` to `01:00`)
`date_from`, `date_until` | an exception's date, or its range (`date_until` empty for one day)
`closed` | an exception that closes the day; otherwise its `opens` and `closes` replace the week's hours for those days
`note` | an exception's note ("Stocktake")

An owner adds a period or an exception one question at a time with this wizard (see "Wizards" above), saved as opening_hours over the table - here opening - with MCP save_wizard or control API data-wizard-save:

title: Add opening hours or an exception
table: opening
steps:
  - field: kind
    ask: Is this the weekly hours for a day, or an exception for a date?
    type: choice
    required: true
    help: period is a day of the usual week; exception is a date or dates that differ.
  - field: day
    ask: Which day of the week?
    type: choice
    required: true
    when: { field: kind, is: period }
  - field: date_from
    ask: On what date does it start?
    type: date
    required: true
    when: { field: kind, is: exception }
  - field: date_until
    ask: Until what date?
    type: date
    help: Leave it empty for a single day.
    when: { field: kind, is: exception }
  - field: closed
    ask: Is it closed all day?
    type: yesno
    when: { field: kind, is: exception }
  - field: opens
    ask: When does it open?
    type: text
    help: HH:MM, such as 09:00. Leave it empty for a day it is closed.
  - field: closes
    ask: When does it close?
    type: text
    help: HH:MM, such as 17:30; 24:00 is midnight. Leave it empty for a day it is closed.
  - field: note
    ask: A note to show with it?
    type: text
    help: Such as Stocktake. Leave it empty for none.
    when: { field: kind, is: exception }

The times are asked of every row and may be left empty: a period without them, or an exception neither closed nor with hours, is refused when the row is saved, as below, and nothing is written.

The rows are written through the doors every table has - the manager's data page, save_data_row (MCP), data-row-save (control API), the data endpoint - and a row the schedule could not read (a time that is not HH:MM, a period with no day, an exception neither closed nor with hours) is refused at the write, by name. The descriptor may declare fields of its own beside these; it may not redeclare them. Times are in the site's zone (time_zone:), summer time included: the zone's own rules decide when 09:00 is.

A page shows it with schedule::

tt_page_var:
  hours: schedule:opening

and the layout has these blocks to render:

columns: Variable | Is
widths: 5.2cm | X
bold: 1
tone: medium
---
`hours.label` | "Open now until 17:00", "Opens at 12:00", "Opens tomorrow at 09:00", "Opens Monday at 09:00", or "Closed"
`hours.open`, `hours.until` | 1 when open now, and until when (`HH:MM`)
`hours.next_open` | `date`, `day`, `time` and `when` (`today`, `tomorrow`, `later`) of the next opening
`hours.week` | seven days from Monday: `name`, `periods` (each `opens`, `closes`), `closed`, `today`
`hours.today` | today's `periods` with any exception applied, `closed`, `exception`
`hours.next_exception` | the next exception still in force: `label` ("Closed Friday 25 December"), `date_from`, `date_until`, `closed`, `opens`, `closes`, `note`
`hours.exceptions` | every exception still to come, in date order, the same shape

<p class="status">[% hours.label %]</p>
<table>[% FOREACH d IN hours.week %]
  <tr[% IF d.today %] class="today"[% END %]><th>[% d.name %]</th>
  <td>[% IF d.closed %]Closed[% ELSE %][% FOREACH p IN d.periods %][% p.opens %]-[% p.closes %] [% END %][% END %]</td></tr>
[% END %]</table>
[% IF hours.next_exception %]<p>[% hours.next_exception.label %][% IF hours.next_exception.note %] - [% hours.next_exception.note %][% END %]</p>[% END %]

Every value is escaped as a db: value is. A page showing "open now" is cached only until the next change of state - the next opening or closing, or the next local midnight, whichever is sooner - and a row changed refreshes it at once: it never serves a stale "open now", and it does not re-render in between. Its ttl: and the response's max-age never reach past that moment.

A schedule: line in lazysite.conf puts the same blocks on every page for a layout to use, at the cost of a read of the table on every request; on the page that shows the hours it costs nothing between changes.

An exception is removed once its date has passed (the release manager's ruling). The daemon's schedule-exceptions job does it hourly, under the job account's manage_data, and writes each removal to the audit trail with the exception it removed (action schedule-exception-removed, the row as JSON in the line's detail), so the history reads back. With the audit trail off (audit_trail: off) nothing is removed: the removal is kept only where the trail can hold it. A passed exception changes nothing a visitor sees either way - the schedule reads from today on.

Structured data for search

A table can be published to search engines and AI search as schema.org JSON-LD: its descriptor says what the rows are and which pages carry them, under structured_data:, and the engine adds the JSON-LD to those pages from the rows an anonymous visitor may read now. The engine ships no mapping for any market; an app ships the mapping for its own with its tables.

title: Events
public: true
fields:
  title:    { type: text }
  starts:   { type: date }
  venue:    { type: text }
structured_data:
  type: Event                   # a schema.org type
  pages: [/events]              # the pages that carry it
  properties:
    name: title                 # property: a field of the table
    startDate: starts
    eventStatus: "=https://schema.org/EventScheduled"   # =text is literal
    location:                   # a nested object
      type: Place
      properties:
        name: venue

The mapping is checked when the descriptor is saved, and refused by name: a type or property that is not a schema.org name, a field the table does not have, a page that is not a site path. On a schedule: true table it describes one place - its properties are literals - and the engine adds openingHoursSpecification from the schedule.

What is published is what the site already serves publicly. The rows are read as an anonymous visitor reads them: a table that is not public: true publishes nothing, a where: row condition and shows_from/shows_until apply, and a link property (url, image, logo ...) whose value is not a web or mail address is left out. Every value is encoded so it cannot leave the <script> element that holds it. A row changed refreshes the pages carrying it, as for any page that shows a table.

The organisation entry is the site's own: its name and address from lazysite.conf (site_name, site_url, and site_type, site_address, site_phone, site_email, site_hours naming a schedule), on the home page, published once one of the details is set. See Configuration.

The sitemap and the AI-readable summary list every public page. A page with no register: key is in sitemap.xml and llms.txt; a page with one keeps what it says. The two list the same pages, from one page set: a page registered for either is in both. Left out whatever they register: a protected or draft page, a page copy, an api: or raw: page, a page that says search: false, a redirect, a starter demonstration page and the engine's sign-in and error pages, and a folder that looks like another site's content (under sites/, or with its own lazysite.conf) and is not a declared content root. The summary (llms.txt) carries each page's title and description and the organisation entry; llms_txt: off turns it off.

lazysite check names a public page with no title or no description, a title two pages share, and every finding in the JSON-LD the mappings emit - a row missing what its type needs (an Event's name and startDate), a date that is not ISO 8601, a page the mapping names that the site does not have, a table no visitor may read. A type it does not know is emitted as mapped and said to be unchecked.

A table that reads its rows from another system

Where another system already holds a fact - a booking platform's bookings, a directory's listings - a table can read it from there instead of keeping a second copy someone enters twice. Its descriptor names a source:: a GET connector, the schedule entry that refreshes it, and which field of each item fills which column.

title: Bookings
key: ref                        # the source's own identifier, never the automatic id
fields:
  ref:   { type: text, required: true }
  guest: { type: text }
  seats: { type: integer }
  note:  { type: text }         # not mapped: the site's own column
source:
  connector: bookings           # a connector with method GET
  schedule: bookings-refresh    # the schedule.conf entry that refreshes it
  rows: data.items              # where the list is in the answer (absent: the answer itself)
  map:                          # column: field of each item (a dotted path reaches inside)
    ref: id
    guest: guest.name
    seats: seats

The parts are the live-data chain's. The operator saves the connector on the Connectors page with method: GET, modes.scheduled on; it needs no answer_table - the descriptor's source.connector is the one link, and every table that names the connector is refreshed by its answer (a connector that already names this table as its answer_table works the same). Its URL, its credential (the connector's secret), the egress bounds and the call record are the connector's, and nothing in the descriptor reaches the network. A connector handler names the connector, and the schedule entry the descriptor names calls the handler with its fixed fields:

schedule:
  - id: bookings-refresh
    handler: bookings-pull
    every: 900
    payload: {"venue":"main"}

A sourced table's answer is not appended as a call row: each item becomes a row, by map. A row the source sends is updated - its mapped columns only, so the site's own columns keep their values - and an item the table has not seen is inserted. A row the source stops sending is removed, as a recorded change in the row history (list_row_changes, from refresh:<table>), so undo_row_change puts it back - until a later refresh that still does not send it removes it again. Only an answer that is a list with at least one item removes anything: an empty list, or a refresh that fails, leaves every row where it is.

Refresh now with refresh_data_table (MCP) or data-table-refresh (API), both needing manage_data, or Refresh now on the table's rows in the manager's Data page, which also shows how old the rows are and how the last refresh went. It is the schedule entry's call, made as you: the connector's callers groups (or manage_connectors) decide whether you may cause it, and it is recorded against the entry like a scheduled run.

A failed refresh changes nothing. The remote down, an answer that is not JSON, a list that is not where rows says, an item without the key, a value a column refuses - each leaves the table exactly as it was, so its rows are the last good copy. The failure is in the connector's call record, the event log and the schedule's run record, and the schedule lists the entry as failed with the reason.

How old the rows are is in describe_data_table / data-table, under source: refreshed_at and age_seconds count from the last refresh that delivered, which a failure does not move; last_attempt, outcome and reason say what happened since. They are read from the schedule's run record (lazysite/daemon/schedule-runs.json).

Mapped columns are read-only. source.read_only lists them. A write to one is refused on every door - the manager, the API, MCP, the data endpoint, a wizard, a switch and a CSV import - naming the column and the source, because a local edit would be undone by the next refresh: change it at the source. A new row is refused too, since its key is the source's. An unmapped column is written as on any table.

The descriptor is refused when saved for a key that is the automatic id, a map that does not fill the key, a column it does not declare, or a missing schedule.

Where things live

lazysite/db/tables/<name>.yaml
One descriptor per table.
lazysite/db/data.sqlite
The store. One file, so a backup is a copy. Its directory must be writable by whoever reads it -- the store uses WAL journalling, and a WAL reader creates a file beside the database.
lazysite/db/rebuilds/
Safety exports written before a destructive rebuild.
lazysite/db/stamps/<table>
Each table's change stamp, touched by every write; a page that showed the table is rendered again when it is newer than the render.