Build on blindrange — SQL

1Install and connect

Python 3.10+. Upgrade pip inside the venv first — anything older than pip 21 fails on this project with a misleading message about setup.py.

git clone https://github.com/alviso/blindrange && cd blindrange
python3 -m venv .venv
.venv/bin/pip install --upgrade pip && .venv/bin/pip install -e .

.venv/bin/python -m blindrange.sql        # interactive REPL

Or from code — one connection is a directory of tables, one passphrase, one network:

from blindrange.sql import connect

con = connect(
    "~/data/shop",                        # tables live under this dir — back it up
    "correct horse battery staple",       # unlocks local keys; never sent anywhere
    ["seed.blindrange.dev:7501"],         # any one live peer; gossip finds the rest
    network_secret="blindrange-public",   # which network (anti-vandal, not access control)
)
rows = con.execute("SELECT ...")          # every statement returns a list of dicts

2CREATE TABLE — BLUR is the privacy budget

CREATE TABLE orders (
  amount   INT BITS 20 BLUR 64,
  day      INT BITS 16 BLUR 16,
  status   TEXT(6) BLUR 16,
  customer STORED
)

3INSERT

INSERT INTO orders (amount, day, status, customer) VALUES
  (450, 201, 'paid', 'cust-001'),
  (120, 205, 'refunded', 'cust-002'),
  (720, 210, 'paid', 'cust-003'),
  (455, 202, 'paid', 'cust-004')

The column list is mandatory, every indexed column must be provided, and out-of-range values are refused — each of those is a way a row could otherwise become silently unfindable by your own queries, which is the worst failure a blind index can have. You never call drain(): reads always see your own writes.

4SELECT

SELECT * FROM orders WHERE amount BETWEEN 300 AND 500

SELECT customer, amount FROM orders
  WHERE amount BETWEEN 300 AND 500 AND day <= 201

SELECT * FROM orders WHERE status LIKE 'ref%'

SELECT * FROM orders ORDER BY amount DESC LIMIT 2

Conditions combine with AND and narrow each other. Text matching is = or a trailing-% LIKE — the index is a prefix index, and equality still checks the full value on the decrypted row, so 'paid' never matches 'paidX'.

ORDER BY … DESC LIMIT n is how you ask for the newest n — ascending with a limit answers with the oldest matches, which is a different question.

5COUNT and APPROX SUM — nothing decrypted

SELECT COUNT(*) FROM orders WHERE amount BETWEEN 0 AND 1000

{'count': 4, 'basis': 'exact-to-leaf'}

SELECT APPROX SUM(amount) FROM orders

{'sum': 1790.0, 'plus_minus': 128.0, 'rows': 4}

Both are answered from index metadata: nothing fetched, nothing decrypted, cost independent of how many rows match. The answers say what they are — basis tells you a count is exact to BLUR granularity (deleted rows still count until compaction runs), and a sum arrives with its error bar, because the resolution you traded for privacy is exactly the uncertainty. Plain SUM() is refused rather than dressed up as exact.

6UPDATE and DELETE — what happens underneath

UPDATE orders SET status = 'shipped' WHERE amount = 450

DELETE FROM orders WHERE day > 208

There is no in-place edit of a sealed record — a node cannot modify ciphertext it cannot read. UPDATE is delete-then-insert, in that order on purpose: a crash between the two leaves a duplicate you can see, never a hole you cannot. There are no transactions, and pretending otherwise would be worse than saying so.

DELETE removes rows from every subsequent read immediately and leaves tombstones behind; compaction reclaims them automatically in the background once enough pile up (PRAGMA AUTOCOMPACT OFF if you want manual control, COMPACT orders to force one). Automating this is recent and deliberate: compaction now survives its own crash and resumes, so a process dying mid-compaction no longer wedges anything.

One rule from the field: after any integrity incident, converge by wiping with integrity enforced — a wipe under a compromised view deletes only what that view could see, and leaves the rest as orphans.

7Documents and counters

Two things applications always need, built in so nobody reinvents them:

CREATE TABLE docs (
  id     KEY,
  body   STORED
)

INSERT INTO docs (id, body) VALUES ('doc-1', 'hello')

SELECT * FROM docs WHERE id = 'doc-1'

A KEY is an opaque handle: exact-match lookup, no BLUR decision to make, auto-generated (and returned) when you omit it. The handle itself lives only inside the sealed record — the network stores a pseudorandom bucket, so the most it can ever tell is that two rows share a handle. It has no order, and asking for one is refused rather than answered with hash order.

SELECT NEXT VALUE FOR invoice_no

{'value': 1}   ·   then {'value': 2} — from any writer, never a duplicate

Sequences are network-atomic: each value is a slot claimed by insert-if-absent, arbitrated by the replicas — the same mechanism that elects one compactor per epoch. Unique and monotonic across writers; gaps are possible (a crash after claiming spends the number), which is exactly how auditors expect invoice numbers to behave.

8What is refused, and why

This table is the honest half of the product. Nothing in it is a missing feature that is coming later — each row is an architectural boundary, and the refusal text in the REPL says the same thing.

9Going further

The Python guide is the same system with the full API: streaming reads in bounded memory, histograms, multi-device invites, and sharding across master keys when one client's compaction memory becomes the ceiling. Everything there composes with tables created here — a table is an Owner underneath.

And step 8 of either quickstart prints what a node actually holds for the rows you just wrote:

I:0003444dfc39de14496d98436571a361  →  S/4cP9LvVoA=
# no table names, no column names, no values, no ordering —
# and 'cust-001' appears nowhere on any node

Read the threat model before trusting it with anything: it is explicit that this is not for healthcare or high-stakes PII, and why.