id: design: Querying local and remote ID servers v1 #5675

Summary

ID servers hold data in tables; there should be a first-class, designed way to query those tables — on a local server and on remote servers — from scripts, from agents, and from the human CLI, with the same semantics in all three. Remote queries are filtered by the data's sensitivity classification; the owner of a server keeps a deliberate override for their own server's unencrypted data. This design defines the query surfaces, the filtering model and its hard boundary, and result shaping.

Relates: #2048 (ID data/zettelkasten layer — the tables being queried), and composes with the selective-sharing grants model (grants decide whether a peer may reach data; sensitivity filtering decides what a query returns — the design must state how the two compose).

Feature set (expanded)

1. Query surfaces

• Local queries: querying tables on a local ID server — in-process/embedded where possible, no network round trip, full local performance.
• Remote queries: querying tables on a remote ID server over ID transport (including over fallback transports), with the remote server executing the query — not "replicate everything and query locally" as the only option.
• Query language/shape: define what a query is in v1 (table selection, filters, projections, ordering, joins or explicitly no joins) — powerful enough for real use, constrained enough to be safe to execute on someone else's server.
• The same query, unchanged, should be runnable against local and remote targets; only the target and the filtering outcome differ.

2. Sensitivity classification & filtered remote queries

• Every table/collection (and where needed, object/row or field) carries a sensitivity classification; the design defines the classification levels, who assigns them, and the default for unclassified data (default must be safe, not permissive).
• A remote query executes under the requester's clearance: results are filtered to what the requester's identity is cleared for, server-side, before anything leaves the server. Filtering is not client-side cosmetics.
• Filtered-result honesty: does the requester learn that results were withheld (count of withheld rows, existence of withheld fields) or not? Pick per classification level and state it — silent filtering and existence leaks are both failure modes.
• Field-level behavior: redaction vs. whole-row exclusion vs. query rejection, per classification and query shape.
• Audit: remote queries (who, what query, what classification levels were touched/withheld) are logged on the serving server, visible to its owner.

3. The owner override — and its hard boundary

• The local owner may override sensitivity filtering for data on their OWN server: when querying their own server, the owner can see past classification filtering.
• Boundary (state it exactly): the override applies only when the data is not encrypted. Encryption defeats the override — if data is encrypted under keys the querying identity does not hold, no override, admin flag, or local access returns plaintext. Classification is a policy filter; encryption is a cryptographic boundary, and policy never outranks cryptography.
• Override mechanics: explicit, deliberate, and audited (an override flag/mode, never a silent default; override use appears in the server's audit log). Override is per-query or per-session, not a permanent server setting that quietly stays on.
• Override scope: own server only. Being owner of server A grants no override on server B, even for data A originally shared to B.
• Multi-principal servers: if a server hosts data for more than one owner, whose override applies to whose data must be defined — an operator must not gain override over other people's unencrypted data merely by hosting it, if the model can prevent it; where it cannot, say so honestly.

4. Parity: scripts, agents, humans

• One query model, three front doors: a scripting/API surface, an agent-facing surface (structured in/out, machine-readable errors, stable result schema), and the human CLI — all executing the same queries with the same filtering.
• No privileged back door: agents and scripts get no broader access than their identity's clearance; the CLI gets no magic local bypass except the explicit owner override above.
• Non-interactive safety: scripts/agents can run read-only queries without prompts; mutating operations (below) require explicit intent that is visible in audit.

5. Read-only vs. mutating surfaces

• The query surface is read-only by default and by design; v1 query language must not be able to mutate data as a side effect.
• Mutating operations, if exposed at all, are a separate surface (distinct commands/endpoints, distinct permissions, distinct audit), designed explicitly — not "query language plus write verbs when you're local."
• Remote mutation: whether a remote principal may ever mutate via this surface, and under what grants (compose with selective sharing), is decided here even if the v1 answer is "no."

6. Result shaping & pagination

• Projection (choose fields), ordering, limits — pushed to the executing server, not done client-side over a full dump.
• Pagination: stable cursor-based pagination for large results (no offset drift under concurrent writes), with page-size limits enforced server-side.
• Result formats: structured (JSON/rows) for scripts/agents; human-readable tables for the CLI; a SQLite-shaped result is a candidate format for composability (see the foreign-adapter design's query-Redis-get-SQLite pattern).
• Truncation honesty: when a result is cut (page size, server cap, sensitivity filtering), the result says so and how to continue — never a silent partial answer presented as complete.
• Cost limits: server-side guardrails for expensive queries (timeout, scan cap) so a remote query cannot DoS the serving server.

Non-goals (v1)

• A general SQL engine over arbitrary foreign stores (adapters are a separate design; this covers ID tables).
• Analytics/aggregation beyond what the v1 query shape explicitly includes.
• Weakening encryption for operability — the override boundary above is absolute.

Acceptance for this design

A follow-on reader can break this into the decomposition tickets below without re-deciding: the filtering model, the override boundary, surface parity, or pagination/result rules.

Decomposition sketch

1. Design: ID query language/shape v1 (local + remote execution model, local/remote parity).
2. Design: sensitivity classification model (levels, assignment, defaults, field vs. row behavior).
3. Design: server-side filtered remote query execution + withheld-result honesty + audit.
4. Design: owner override mechanics (scope, audit, multi-principal servers) and the encryption boundary.
5. Design: client surfaces parity — scripting/API, agent, and CLI front doors over one model.
6. Design: result shaping & cursor pagination (formats, truncation honesty, server cost limits).
7. Design: mutating surface separation (permissions, audit) — or an explicit v1 "read-only only" decision record.

Open questions

• Classification levels: how many, named how, and can owners define their own?
• Withheld-result signaling: counts, yes/no, or nothing — per level?
• Is the query language a new ID-specific shape, a SQL subset, or both (SQL compiled to the ID shape)?
• How do grants (selective sharing) and classification compose when they disagree — deny-wins?
• On multi-principal servers, can the model technically separate operator from data owner for override purposes?
• Cursor stability: snapshot semantics per query, or best-effort with documented drift?
• Should a SQLite-formatted result be a v1 format or a follow-on?