Overview
HTQL is the one language your application speaks to HTQLDB.
What HTQL gives you
Most applications spread one piece of work across several layers: a query language for the data, application code for the rules, a separate service for permissions, another for calculations, and glue to keep them in step. HTQL brings those into one statement that HTQLDB carries out for you.
- One statement, one answer. Every statement answers in the same envelope, so the code that calls HTQLDB handles reads, writes, calculations and refusals the same way.
- Permissions applied before anything runs. Who is calling, and through which application, is established before a statement does any work, and a call that is not allowed is refused with a code that says why.
- Money and percentages written plainly. You write an amount as you would say it,
12.50, and HTQLDB stores and returns it exactly, with its currency. - More than queries. The same language reads and writes records, aggregates them, walks graphs of related records, calls business functions for finance, forecasting, risk and more, and starts FLOWs that carry work to completion.
How a statement runs
Each call follows the same path, and each step is there so the next one can trust what it receives.
| Step | What happens | Why it matters to you |
|---|---|---|
| 1. Your application calls | Your application sends the statement to the HTQLDB gateway with its own credential and a greeting that names it | HTQLDB knows which application is calling, not only which person |
| 2. The caller is proven | HTQLDB checks the application's credential against the one it registered, and a person's live session when a person is acting | A greeting alone proves nothing; a credential does |
| 4. The statement is read | HTQLDB parses the statement, resolves the fields of the platform's tables and records every function and governed statement it will need | A misspelt field of a platform table is refused before any data moves |
| 5. It is authorised | Before anything runs, each function and governed statement is checked against what your application may do and what the person may do | A statement is refused whole, never half run |
| 6. It runs | The work happens in the database, close to the data | Your code receives only what it asked for |
| 7. You get the answer | One envelope, with your rows in a shape ready for your code | The same handling for every statement |
Reads and writes of tables are confined by your business's row security at step 3; functions and governed statements are also authorised at step 5. The Security & governance part of this guide explains each layer.
The answer you receive
Every statement, whatever it does, answers with the same four parts.
| Part | What it holds |
|---|---|
success | true when the statement ran, false when it was refused |
data | The result: a list of rows for a read, or the value a statement such as RETURN builds |
error | When a statement is refused, a message that begins with a code, such as [HTQL-1003] |
info | What happened: how long it took, whether data is a list of rows, notices, and the counts of records written, changed and removed |
Rows are shaped for your code before they reach you:
- Field names are camelCase, in and out. Write
contentTypein a FILTER and readcontentTypein the answer. - Money is an object with its currency. An amount arrives as
{ "amount": "12.50", "currency": "AUD" }, already in whole units of its currency, so you never divide by 100 yourself. - Percentages are plain numbers. A margin of 43.6% arrives as
43.6. - A value that cannot be read is
null, with a notice. HTQLDB never turns an unreadable amount into a silent zero.
The next chapters explain each part: The answer every statement returns, what info shows, how cost is charged and paging.
The answer every statement returns
Every statement answers with one object. Read it in this order: success first, then data or error, then info when you want to know what the statement did and what it cost.
| Key | When it appears | What it holds | Example |
|---|---|---|---|
success | Always | true when the statement ran, false when it was refused | true |
data | When success is true | The result. A read gives a list of rows, even for one row or none. A function or a RETURN gives the value it built, usually an object. A write gives what the write returned | [{"id": "sgAboutPage", "slug": "about"}] |
error | When success is false | One text that begins with a code in square brackets, then a sentence that says what to change | [HTQL-1003] Unsupported syntax near '...' |
info | When success is true | What the statement did and what it cost. The next chapters explain each field | see below |
A refused answer has success and error only, with no data and no info. Nothing the statement asked for was done.
The code in error is always [HTQL- and four digits. Error codes lists each one with how to recover. Branch on the code, not on the sentence after it.
The shape of data depends on what the statement is:
| Statement | data is | info.isRecordset |
|---|---|---|
| A read such as LOOKUP | A list of rows. Zero rows is [], never null | true |
| A function call or RETURN | The value it built, such as {"a": 4.0, "b": "2026-11-20"} | false |
| A write | What the write returned. info carries the ids it touched when there are any | false |
The examples on the right show a read, a function call and two refusals, each captured from a real call.
What info tells you
info is the receipt for the statement. It is present on every answer that succeeds, with every field below, so your code can read any of them without checking that it exists.
| Field | What it shows | Unit | Example |
|---|---|---|---|
ok | The statement finished without a refusal | true or false | true |
expired | A value the statement read had passed its time to live | true or false | false |
isRecordset | data is a list of rows | true or false | true |
errors | Problems the statement reported and carried on past | list of text | [] |
notices | Things worth knowing, such as a write that matched no rows | list of text | [] |
executionTime | How long HTQLDB took to run the statement | seconds | 0.0000016 |
queryCost | The statement's cost in points. See Cost | points | 50 |
hydroFees | A cost estimate for a write, taken from the database's own plan of the work | plan cost units | 0.0 |
scannedFull | Rows fetched by the read helpers that count them | rows | 0 |
scannedIndex | Rows found through an index | rows | 0 |
fullCount | The full number of matching rows | rows | 0 |
filtered | Rows a filter left out | rows | 0 |
writesExecuted | Writes the statement made | writes | 0 |
writesBytes | Bytes those writes carried | bytes | 0 |
deletedCount | Rows removed | rows | 0 |
modifiedCount | Rows changed | rows | 0 |
httpRequests | Calls to other systems the statement made | calls | 0 |
httpFailures | Those calls that failed | calls | 0 |
httpHydroFees | Cost charged for those calls | points | 0 |
hasNextPage | More rows were found than the page holds. See Paging | true or false | false |
pageSize | The page size the statement ran with | rows | 250 |
skip | Rows skipped before the page began | rows | 0 |
Some fields appear only when they apply: insertedIds and updatedIds list each row a write inserted or updated, as its gid (or key), its pool number id and its replicaId; upsertedIds lists the ids an upsert touched, and lastKey and currentKey carry a position when a statement pages by key.
To know how many rows a read returned, take the length of data. The example on the right is the full info of the read in the previous chapter.
Cost: what a statement is charged
Every statement carries a cost, so a caller that sends many heavy statements can be slowed before it harms the others. The cost is a number of points in info.queryCost. It is a weight set by what the statement does, not a measure of time.
HTQLDB adds up the weights of everything the statement runs:
| What runs | Points |
|---|---|
| LOOKUP | 50 |
| INSERT | 200 |
| UPDATE, UPSERT, DELETE | 300 each |
| INSERT of an edge | 250 |
| UPDATE or REMOVE of an edge | 300 |
| TRUNCATE or DROP | 800 for each table named |
| REFRESH | 100 for each view named |
| REPORT | 200 |
| FOREACH | 500, plus the cost of everything inside it |
| A FLOW started or triggered | 5000 |
| A FLOW amendment request, a draft or an acceptance | 3000 |
| A function such as SQRT, TODAY or LENGTH | 0 |
| A business command such as ProcessOrder or ProcessRefund | its own weight, for example 1000 and 800. A command with no weight of its own costs 5 |
| LEARN and RETURN themselves | 0, plus the functions they call |
Costs add across the statements and functions in one call. A FOREACH that runs a LOOKUP three times reports 500 plus 150, plus anything else inside.
hydroFees is a second, separate number. On an INSERT, UPDATE or DELETE of a table, the database plans the work and reports its estimated cost, and HTQLDB returns that estimate in hydroFees. A read leaves it at 0. httpHydroFees is the matching charge for calls a FLOW makes to other systems; it is 0 when the statement made none.
What the admin shows. The spreadsheet's status bar in the admin prints values taken from info after each statement:
| Shown | Taken from | Conversion |
|---|---|---|
1.6ms | executionTime | Seconds times 1000, shown with one decimal |
0 scanned | scannedFull and scannedIndex | The two added together |
cost 50 | queryCost | Shown as it is |
burn 0.0000 | hydroFees | Shown with four decimals, and only when it is above 0 |
3 writes | writesExecuted | Shown only when it is above 0 |
The admin does not turn these into a currency or a credit balance. What it shows as cost is queryCost, and what it shows as burn is hydroFees. Both come straight from the answer, so you can compute the same figures in your own code.
How the rate limit uses the cost. Calls that go through the API workers are metered with a leaky bucket. The bucket holds at most 20,000 points and refills at 1,000 points every second. Each call first takes 1 point to check that the bucket is not empty. When the call finishes, the workers subtract the total queryCost of its statements. The bucket may go below zero, and the next call is then refused with HTTP status 429, the code [HTQL-4001] Throttled: rate limit exceeded and a Retry-After header. The header holds the seconds to wait, which is the points short divided by 1,000, rounded up.
In plain terms, a caller can spend 20,000 points at once and then about 1,000 points of work every second.
Paging: hasNextPage, pageSize and skip
A read gives you the rows you asked for with TAKE. To move through a long list, ask for a count and a number of rows to skip: TAKE(3, 2) skips two rows and returns the next three.
| Field | What it tells you |
|---|---|
pageSize | The page size the statement ran with. It is 250 unless the statement sets another |
skip | How many rows were skipped before the page. It is 0 unless the statement skips |
hasNextPage | true when the read found more rows than it returned |
Read hasNextPage only on the reads that set it. A read that fetches one row more than the limit, to see whether another page exists, sets it to true and drops the extra row. A LOOKUP with TAKE returns exactly the rows you asked for and leaves hasNextPage at false even when more rows remain, so do not stop a LOOKUP loop on it. Page a LOOKUP by asking for the next block with TAKE(count, skip) and stop when the rows returned are fewer than count.
Notices and errors
info.notices and info.errors are lists of text, and both are always present. They are empty when nothing needs saying.
| List | What it holds | What to do |
|---|---|---|
notices | The statement ran, and something is worth knowing. A REMOVE that matched no rows says so. A key that had passed its time to live says its data may be stale. An amount that could not be read arrives as null with a notice that names it | Show or log them. The result is still good |
errors | Problems the statement reported and carried past | Treat the result with care and log them |
These lists are about a statement that ran. A statement that was refused has no info at all; its reason is the error key at the top of the answer, as in the refusals in The answer every statement returns.
A write that changes nothing is not a refusal. It answers success: true with a notice such as REMOVE matched 0 rows, so read notices after a write as well as success.
The language at a glance
The guide is organised the way you will use the language. Each part opens from the menu on the left.
| Part | What it covers | Start with |
|---|---|---|
| Language reference | Reading and changing records, expressions, variables, loops, analytics, graphs and the key-value store | LOOKUP, FILTER, LEARN |
| Function reference | The functions you call inside a statement: mathematics, statistics, text, dates, arrays, finance, accounting, forecasting, planning, risk and commercial operations | The category nearest your task |
| FLOW reference | Defining work that runs over time: steps, triggers, waiting, human verification, AI and the commitment of each step | FLOW definition and lifecycle |
| Finance protocol | Contracts, assets, transfers, escrow and signatures, settled with exact amounts | Contracts and terms |
| AOS language reference | Personas, Guilds, DNA, semantic graph queries, context and what-if analysis | Persona and Guild |
| Security & governance | How calls are authenticated, what a caller may do, clearance, secrets, consent and tenant isolation | Calling HTQLDB |
| Integration reference | Reaching other systems: HTTP from a FLOW, channels, broadcasts and notifications | HTTP calls from a FLOW |
A statement is built from a verb and its clauses. Three short examples show the shape, each beside the SQL it stands for.
Writing values
You write values the way a person reads them, and HTQLDB converts them for storage.
- Text in double quotes:
"paid". - Numbers as written:
42,12.5. - Money in whole units:
12.50for twelve dollars fifty in a money field. HTQLDB stores it exactly in the currency's smallest unit, using your business's currency. - Dates as ISO text:
"2026-11-19". - Records as JSON:
{"title": "Garden tools"}. - Records' identities as GIDs, such as
gid://identity/Business/…. The GIDs entry explains them.
Security in brief
Security is built into how a statement runs, not added by your code.
- Applications prove themselves with their own credential. A greeting that names an application without its credential is treated as no application at all.
- People act through a live session of their own. Signing out, or a change of PIN, ends that session's access at once.
- Businesses are isolated by the database's row security. Every statement runs inside one business's records.
- Sensitive fields are shown only to callers with the clearance for them; anyone else receives the row with that field withheld. An AI agent never receives restricted values.
- Refusals explain themselves. A refused statement returns a code and the reason, and nothing it asked for is done.
Beyond queries
HTQL is also how work happens over time and how value moves:
- FLOW carries work from start to finish: it defines steps, waits for events or people, calls AI or other systems, and commits each step only when it is complete.
- The Finance protocol records contracts, assets, transfers and escrow with exact amounts and signatures, so a payment is settled once and can be traced.
- AOS gives Personas and Guilds their knowledge: DNA, the semantic graph that connects what they know, and what-if analysis before a decision.
Each has its own part in this guide, written the same way as the language reference.
How to use this guide
Every statement and function is documented in the same order, so once you have read one entry you know where to look in the next: its syntax, its parameters, what it returns and how it behaves, examples, the permissions it needs and the errors it can return.
- Examples show the HTQL and, for comparison, the equivalent SQL. Results marked as captured were produced by running the statement against the site's own content store; results marked Sample dataset illustrate the shape with sample data.
- Run and Ask Sigmar AOS are shown on each page and are being switched on: running an example live needs the guide's sandbox, and asking Sigmar AOS needs you to sign in. Until then the page shows the sample result, and Sigmar AOS asks you to sign in.
- Soon in the menu marks a topic whose entry is being written.
- Error codes lists every code with what happened and how to recover.
Start with Getting started, then read LOOKUP and FILTER.
Examples
Three pages of the site. This is the `data` part of the answer.