@lndrstrll/pomuku-server

pomuku's backend core: the part every app's backend is built from. an app declares its schema and gets a hono app with sign-in, access tokens, a users allowlist, a history log, rest routes for every table, and a google sheet kept in sync both ways. it runs on cloudflare workers with a d1 database; the database sits behind a small adapter, so the same app runs on node with sqlite.

the api description and the mcp endpoint build on this and aren't here yet.

an app in two files

// schema.ts — also imported by the web app, for its row types and form validation
import { defineSchema, integer, list, table, text, threeOf, type RowOf } from '@lndrstrll/pomuku-server/schema'

const books = table({
  tab: 'Catalog',                       // the sheet tab this table mirrors
  columns: {
    title: text('Title', { required: true }),   // 'Title' is the sheet's header
    author: text('Author'),
    year: integer('Year'),
    languages: list('Language', { oneOf: ['English', 'Italian', 'Spanish'] }),
  },
  id: ({ row }) => `${threeOf(row.author ?? '')}-${threeOf(row.title)}`,  // minted once, never changed
  label: (row) => row.title,            // what the history log calls a row
  read: 'public',                       // public | member | admin
  write: 'member',                      // member | admin
})

export const schema = defineSchema({ tables: { books } })
export type Book = RowOf<typeof books>

// worker.ts
import { createApp } from '@lndrstrll/pomuku-server'
import { schema } from './schema.js'

export default createApp({
  name: 'library',
  schema,
  // the app's own endpoints, next to the ones its tables get
  routes: (api, { store, allow }) => {
    api.get('/overview', async (c) => {
      allow(c, 'public', 'read')
      return c.json({ ok: true, books: (await store(c, 'books').list()).length })
    })
  },
  // runs once the answer has gone out, after any request that changed rows
  afterWrite: async ({ changes }) => {},
})

examples/library is this app in full, with its tests.

defineSchema takes two more things, for a sheet that was there before the app:

export const schema = defineSchema({
  tables: { books },
  // the users tab's own header for name, language or role
  userHeaders: { name: 'Owner' },
  // tabs that aren't tables (no id column: option lists, a grouped outline), kept whole
  tabs: { zones: 'Zones', lists: 'Lists' },
})

a tab kept whole is only ever read from the sheet, in its turn of a pull, and an endpoint of the app's own makes sense of it: await tab(c, 'zones') gives its rows of cells, the header row first, as the last pull found them.

what the app answers

everything is under /api/v1, answers json shaped { ok: true, … } or { ok: false, error, details? }, and uses real status codes: 400 a body that isn't json, 401 not signed in, 403 not allowed, 404 no such row, 409 an outdated save, 422 a row that doesn't validate.

| route | who | what | | --- | --- | --- | | GET /health | anyone | the app's name and version; doesn't touch the database | | POST /session | anyone | { credential }, a google id token → { token, expiresAt, user } | | GET /session | signed in | who the token belongs to | | PATCH /session | a person | { language }: the language their account opens in | | DELETE /session | a person | signs out | | GET /<table> | the table's read | every row | | GET /<table>/:id | the table's read | one row | | POST /<table> | the table's write | adds a row; the id is minted. with an x-request-id header, the same request sent again adds nothing and gets the row it made | | PATCH /<table>/:id | the table's write | changes the fields sent; with rev, refused if the row was saved since | | DELETE /<table>/:id | the table's write | removes the row; ?rev= as above | | GET /history | members | the log, newest first; ?limit= and ?before=<seq>. changes to the users table, which name people by email, are shown to admins only | | GET POST /users, PATCH DELETE /users/:email | admins | the allowlist | | GET POST /tokens, DELETE /tokens/:id | admins | access tokens for machines | | POST /sync/visit | anyone | looks at the sheet if the last look is a few minutes old; the web app calls it when someone opens it | | POST /sync | the sync secret, or an admin | the sheet's "sync now": sends what's waiting and pulls the next tab; asked again until done |

a row comes back with its fields plus id, rev (how many times it's been saved) and updatedAt.

signing in

the web app is served from another origin, so nothing uses cookies: every request carries authorization: bearer <token>.

• people sign in with google. the browser sends google's id token once; its signature is checked here against google's published keys, which are fetched rarely and kept. the email has to be on the users table. the answer is a session token (pms_…, 30 days), and the email itself never goes back to the browser.
• machines use access tokens (pmt_…) that an admin makes, with a name and a read or write scope. the token is shown once; only its hash is kept. revoking one stops it at once. a token can read and write tables open to members, never what's kept for admins.
• admins are users whose role is admin, plus the emails in ADMIN_EMAILS, who get in even with an empty users table. that's how the first person signs in.

every write

a write is one transaction of three statements: the row, a history entry (who, what, which row, a field: before → after diff in plain text) and an outbox entry for the sheet push. a save that changes nothing writes nothing. a save made from an outdated copy is refused with 409 and the current row.

the sheet

each table mirrors a tab of the app's google sheet, column by column, found by header name: columns can be reordered and others added freely. the users table is the Users tab. nothing runs on a timetable.

a save pushes. once the answer has gone out, the rows waiting in the outbox go to the sheet. only the cells the app changed are written, so an edit made in the sheet to another cell of the same row isn't lost. a new row fills only the table's own columns: a formula written once in a header cell to fill its column (={"total"; ARRAYFORMULA(…)}) goes on filling it, and a new row takes the look of the row above. a day is written as a date cell, which a person can sort, filter and reckon with, shown in the format the column already has (2026-10-02 where it has none); a cell holding a day as text is still read. where the sheet changed the same cell too, the app wins and the sheet's value goes to history as a conflict. a new table's tab is created with its headers. a push that fails leaves the outbox as it was, and goes out with the next save, visit or "sync now". pushes take turns: of two saves made a moment apart, the first one's push sends both, so no row is ever added to the sheet twice.

a pull runs when asked. by the sheet's "sync now" menu, or by POST /sync/visit, which the web app calls when someone opens it and which does nothing if the last look was under five minutes ago. a pull first asks google whether the sheet changed since the last one, and stops there if not. otherwise it looks at every tab, one tab per request, a hundred changed rows at most per request, comparing each row with the snapshot kept from the last sync:

| in the sheet | what happens | | --- | --- | | a cell changed | applied to the database, logged in history as made by sheet; the row's rev goes up, so a copy opened before is refused | | a cell changed that the app changed too | the app wins when its change is pushed; the sheet's value is kept in history | | a new row without an id | added, and its id written back to the sheet | | a new row with an id | added under that id; this is how a first import reads an existing sheet | | a row deleted | archived, if the table has a boolean archived column; otherwise removed, its fields kept in history | | a value that doesn't validate | skipped, and listed on the sheet's Validation tab until it's fixed | | a tab that lost more than half its rows | kept as it was, and reported; POST /sync?missing=remove says it was meant | | a tab or an id column missing | nothing is synced from that tab, and it's reported | | a tab kept whole changed | what the app keeps of it is replaced; if the tab goes missing, the last copy stays and it's reported |

a first import is the same pull: with an empty database and an existing sheet, "sync now" adds every row, a hundred per request, until it's done.

a scheduled trigger isn't needed, but an app that wants one as a safety net can add it: scheduled: (_event, env, ctx) => ctx.waitUntil(app.sync(env)).

connecting a sheet

1. in google cloud, a project with the sheets api and the drive api enabled (drive is only asked when the sheet last changed), and a service account with a json key
2. share the sheet with the service account's email, as an editor
3. the worker's settings: SHEET_ID (from the sheet's address), and two secrets, wrangler secret put GOOGLE_SERVICE_ACCOUNT (the key file's whole json) and wrangler secret put SYNC_SECRET (a long random text)
4. in the sheet, Extensions → Apps Script: paste sheet-script/Code.js, and set its two script properties, SYNC_URL (https://<the worker's address>/api/v1/sync) and SYNC_SECRET. reload the sheet: a Sync menu appears
5. Sync → Sync now. with an existing sheet this is the first import

the database

migrate(database, schema) creates what the schema needs and is safe to run again; tests and a node host use it. for d1, pomuku-migrations --schema src/schema.ts writes the next numbered .sql file into migrations/ for wrangler d1 migrations apply.

migrations only add. a table or column the schema no longer has is left in place and reported, never dropped.

settings

| name | what | | --- | --- | | DB | the d1 binding | | GOOGLE_CLIENT_ID | the google sign-in client id the web app uses | | ALLOWED_ORIGINS | the web app's origins, comma-separated | | ADMIN_EMAILS | emails that are always admins, comma-separated | | SHEET_ID | the google sheet kept in sync; without it nothing is synced | | GOOGLE_SERVICE_ACCOUNT | secret: the service account's key file, as json | | SYNC_SECRET | secret: what the sheet's script sends to start a sync |

what it costs on the free plan

d1 counts rows read and written, and caps both by the day. measured by the example app's tests, which fail if an endpoint starts costing more:

| request | rows read | rows written | | --- | --- | --- | | health | 0 | 0 | | a public read of one row | 1 | 0 | | a public read of a table | one per row | 0 | | any signed-in request | 2 more (the session and its user) | 0 | | sign in | 4 | 3 | | add a row | 2 | 4 | | change a row | 4 | 3 | | remove a row | 4 | 3 |

every answer that used the database says what it cost in its server-timing header.

the sync adds its own, after the answer:

| step | rows read | rows written | | --- | --- | --- | | the push after a save | 8–10 | 6–7 | | a visit, the last look recent | 0 | 0 | | a visit, the sheet unchanged | 4 | 1 | | a pull of one tab | two per row of the table (the row and its snapshot) | 3 per row changed | | a pull of a tab kept whole | 2 | 1, or 2 if it changed |

so a save costs about 10 rows written in all, and the free plan's 100,000 a day allow some 10,000 saves.

the plan also allows 10 ms of cpu per request. measured on 2 october 2026 on the deployed example app, from cloudflare's own logs, with 1000 books in the table (median and highest of 4 to 8 requests each):

| request | cpu, median | cpu, highest | | --- | --- | --- | | health | 0 ms | 0 ms | | a sign-in's check (google's keys imported, the signature checked) | 0 ms | 1 ms | | one row | 1 ms | 1 ms | | a signed-in read of a small table | 1 ms | 2 ms | | add, change or remove a row | 1–2 ms | 4 ms | | the history log, 100 entries | 2 ms | 2 ms | | a whole table of 1000 rows | 4 ms | 5 ms | | 1000 rows read and counted by an endpoint of the app's own | 3 ms | 8 ms |

the sync was measured on 3 october 2026 on the first real app, against google itself: a sheet whose main tab has 380 rows of 20 columns (about 240 KB as json):

| request | cpu | rows read | rows written | | --- | --- | --- | --- | | a save with the push that follows it | 9 ms, 12 at most | 3, then 8–10 | 3, then 6–7 | | a visit, the last look recent | 0 ms | 0 | 0 | | a pull of the 380-row tab, nothing changed | 20–23 ms | 764 | 1 | | a pull of a small tab, or of a tab kept whole | 3–9 ms | 4–7 | 1 | | a first import, 100 rows added per request | 18–38 ms | up to 603 | 5 per row |

a pull of the large tab takes twice the 10 ms the plan allows. cloudflare let every one of them finish (it tolerates occasional overruns, and ends workers that go over consistently), and a pull only runs when the sheet has changed; but an app whose sheet is edited often, or whose tabs are larger, should expect to need the paid plan or a pull that compares fewer rows per request. a first import is five requests of that size, once.

a whole-table read is the one that grows with the app's data: about 4 ms per 1000 rows of 8 columns, so a table several times that size, or much wider, needs measuring before it's read in one request. examples/library/scripts/cpu.mjs repeats the measurement.

testing an app

@lndrstrll/pomuku-server/testing has what an app's tests need to run its backend for real: testD1(), an in-memory d1 database from wrangler's local runtime; testGoogle(), a pretend google whose id tokens the app accepts; and memorySheets(), a spreadsheet kept in memory that a test can edit as a person would.