Record Public Indexes

Encrypted records can include explicit publicIndexes metadata. These fields are not plaintext payload data; they are caller-supplied public values intended for searching and filtering encrypted-record metadata without decrypting the payload.

Storage Model

Each record stores the original public index object in encrypted_records.public_indexes. The executor also normalizes each key into record_indexes:

Column	Meaning
record_id	The encrypted record that owns the index row.
bucket_id	The bucket containing the record.
key	The public index key.
value_text	String value for string indexes.
value_number	Numeric value for number, integer, and float indexes.
value_boolean	Boolean value for boolean indexes.
value_date	Temporal value for date and datetime indexes.

Buckets may define an index schema at creation time. When a schema exists, record writes and record searches both use that schema to validate key names and types. Unknown keys or invalid boolean/number/date/datetime values are rejected. Bucket administrators can later submit UPDATE_BUCKET_INDEX_SCHEMA for compatible schema evolution. The update changes validation for future record writes and searches; it does not rewrite historical records or re-normalize existing record_indexes rows.

Schema index types:

Type	Public index value
string	JSON string
number	Backward-compatible finite JSON number
integer	Finite JSON number with no fractional part
float	Finite JSON number, fractional values allowed
boolean	JSON boolean
date	Calendar date string, YYYY-MM-DD
datetime	Exact ISO-8601 UTC timestamp, for example 2026-05-26T14:30:00.000Z

date values are normalized to midnight UTC in record_indexes.value_date for indexed comparisons. datetime values are stored in the same temporal column with their exact timestamp.

number, integer, and float values are all stored in record_indexes.value_number. integer adds schema validation that rejects fractional values. float accepts any finite JSON number, including whole numbers. These are JSON numbers, so very large integers or high-precision decimals are still subject to JSON/JavaScript number precision before storage.

Buckets without an index schema still store each public index in the typed record_indexes columns based on the JSON value supplied by the record. Search filters for schema-less buckets are therefore interpreted as untyped query-string values and matched against compatible typed columns. For example, index.step=2 can match either the string index "2" or the numeric index 2, and index.reviewed=true can match either the string "true" or the boolean true. Schemas are still recommended for production buckets because they remove that ambiguity and reject invalid filters early.

Schema Updates

UPDATE_BUCKET_INDEX_SCHEMA replaces the bucket's active public-index schema with a compatible successor. The transaction requires bucket:admin and is part of consensus state through the bucket_index_schema state namespace. A bucket created with an index schema also writes a bucket_index_schema state leaf so replay and state roots include the schema used for validation.

The current protocol accepts compatible-only changes:

• adding optional fields;
• widening allowedValues;
• adding the first schema to a previously schemaless bucket, provided all fields are optional.

The current protocol rejects unsafe changes:

• removing a field;
• changing a field type;
• adding a required field;
• making an optional field required;
• making a required field optional;
• narrowing allowedValues.

These restrictions keep historical records valid without backfilling or versioning old records. A future versioned-schema design can loosen this by storing schema versions per record and optionally rebuilding normalized index rows.

Search API

GET /buckets/:id/records/search supports bucket-scoped typed filters with query keys shaped as:

index.<key>=<value>

Examples:

/buckets/bucket-project-data/records/search?index.reviewed=true
/buckets/bucket-project-data/records/search?index.projectId=project-a&index.reviewed=true
/buckets/bucket-project-data/records/search?index.score=9
/buckets/bucket-project-data/records/search?index.step=2
/buckets/bucket-project-data/records/search?index.score=9.75
/buckets/bucket-project-data/records/search?index.capturedDate=2026-05-26
/buckets/bucket-project-data/records/search?index.capturedAt=2026-05-26T14:30:00.000Z

The route remains bounded with limit and offset. limit defaults to 100 and is capped at 500. Including encrypted payload bytes still requires bucket:read_encrypted; normal searches require bucket:read_metadata.

For complex filters, use POST /buckets/:id/records/search. The POST body supports nested boolean logic, range comparisons, in-list filters, and existence checks:

{
  "where": {
    "and": [
      { "index": "score", "type": "number", "gte": 7 },
      {
        "or": [
          { "index": "reviewed", "eq": true },
          { "index": "status", "eq": "active" }
        ]
      }
    ]
  },
  "typeMode": "coerce",
  "includeEncryptedPayload": false,
  "limit": 50,
  "offset": 0
}

Supported logical nodes are and, or, and not. Supported index operators are eq, neq, gt, gte, lt, lte, in, notIn, exists, and notExists. neq means the key exists and its value differs; missing keys do not match neq.

Schema-backed buckets validate keys, value types, and allowed values against the bucket index schema. typeMode is not accepted for schema-backed searches.

Schemaless buckets use bounded best-effort typed matching over the materialized record_indexes columns. By default, typeMode: "coerce" allows compatible equality matches such as text "2" matching numeric 2, text "true" matching boolean true, and date/datetime strings matching temporal values when a temporal column exists. typeMode: "strict" matches only the JSON value's natural type or the explicit type hint. Range filters on schemaless string values require an explicit type: "number", type: "integer", type: "float", type: "date", or type: "datetime"; lexicographic string ranges are not supported.

Schemaless complex searches use stricter bounds:

Limit	Value
Maximum nesting depth	3
Maximum predicates	30
Maximum OR branches per node	10
Maximum IN values	50
Default limit	50
Limit cap	300

Responses can include warnings when a schemaless query shape may require more database work, such as broad OR, negative filters, or coerced comparisons across multiple typed columns.

Indexes

Migration 028_record_index_search_indexes.sql adds partial composite indexes for the common bucket-scoped predicates:

• (bucket_id, key, value_text) for string indexes.
• (bucket_id, key, value_number) for number, integer, and float indexes.
• (bucket_id, key, value_boolean) for boolean indexes.
• (bucket_id, key, value_date) for date and datetime indexes.

It also adds global boolean/date key-value indexes because the initial schema already had global text and number key-value indexes.

Migration 039_record_search_ordering_index.sql adds encrypted_records(bucket_id, created_at desc) for the route's bucket filter and newest-first pagination order.

These indexes avoid scanning every row with the same key when a bucket has many records. They do not change consensus state and do not affect replay.

Cardinality And Normalized Keys

Low-cardinality fields, such as reviewed=true, can still match many rows. Indexes help PostgreSQL find the matching rows, but operators should expect the result set itself to dominate runtime when millions of records match.

Normalizing repeated key strings into a separate key table can reduce storage and index width for very large installations, but it also adds joins and more write complexity. NOOSChain keeps that as a measured future optimization rather than a default production migration.

Use the benchmark harness before changing the schema:

npm run benchmark:record-index-search

For a quick local smoke:

$env:NOOS_RECORD_INDEX_BENCH_RECORDS="1000"
$env:NOOS_RECORD_INDEX_BENCH_ITERATIONS="3"
$env:NOOS_RECORD_INDEX_BENCH_WRITE_RECORDS="500"
npm run benchmark:record-index-search

For a more useful decision run:

$env:NOOS_RECORD_INDEX_BENCH_RECORDS="100000"
$env:NOOS_RECORD_INDEX_BENCH_ITERATIONS="30"
$env:NOOS_RECORD_INDEX_BENCH_WRITE_RECORDS="10000"
$env:NOOS_RECORD_INDEX_BENCH_PROFILE="mixed_realistic"
npm run benchmark:record-index-search

Useful environment variables:

Variable	Default	Meaning
NOOS_RECORD_INDEX_BENCH_RECORDS	10000	Number of synthetic records.
NOOS_RECORD_INDEX_BENCH_ITERATIONS	20	Query repetitions per strategy.
NOOS_RECORD_INDEX_BENCH_PROFILE	mixed_realistic	low_cardinality_boolean, high_cardinality_string, mixed_realistic, many_keys, or many_buckets.
NOOS_RECORD_INDEX_BENCH_RESULT_LIMIT	100	Rows returned by each benchmark query.
NOOS_RECORD_INDEX_BENCH_RESULT_OFFSET	0	Offset applied to benchmark queries, useful for pagination-cost checks.
NOOS_RECORD_INDEX_BENCH_WRITE_RECORDS	5000	Synthetic records used for write-cost comparison.
NOOS_RECORD_INDEX_BENCH_LOG_DIR	./benchmark-results/record-index-search	Report directory.
NOOS_RECORD_INDEX_BENCH_KEEP_DB	false	Keep the isolated benchmark schema for manual inspection.

The report compares the original baseline indexes, the targeted production indexes, the bucket ordering index, benchmark-only record_id covering variants, and a normalized-key prototype. The normalized prototype and record_id covering variants are benchmark-only; they do not alter the production schema unless a later migration promotes one.

The benchmark writes final-report.json with:

• results: p50/p95/min/max/avg latency for boolean, high-cardinality string, low-cardinality string, number, and compound predicates.
• plan: PostgreSQL EXPLAIN (ANALYZE, BUFFERS) summary, including planning time, execution time, buffer hits/reads, and the index or scan node selected.
• storage: table and index sizes for record_indexes and the normalized-key prototype, plus calculated normalized storage savings.
• writeResults: insert throughput for denormalized rows versus normalized key-id rows.
• indexVariantWriteResults: insert throughput and index bytes for current targeted indexes versus record_id key-column and include (record_id) variants.
• recommendation: an automatic conservative decision using these thresholds: at least 15% p95 improvement for the ordering and record_id variants, no record_id write slowdown above 10%, and no record_id index storage growth above 25%. Normalized-key metrics are retained for comparison but excluded from this recommendation.

Treat the recommendation as evidence, not law. A production decision should use the deployment's expected bucket count, index count per record, cardinality, and record volume. A small 1,000-record smoke run is useful only to verify the harness.