lens-tool

Query any spreadsheet by meaning.

Take a CSV file — a music collection, a client list, a Kaggle dataset, five years of bank transactions — map what the columns actually mean, and query it like this:

WHO.artist = "Miles Davis" AND WHEN.year BETWEEN "1955" AND "1965"

No SQL. No database setup. No schema to learn. Your data, queryable by meaning in about ten minutes.

Install

Requires Node.js 16+

npm install -g lens-tool

Or try without installing:

npx lens-tool ui

Quick start

lens-tool ui

Your browser opens. Drop in a CSV. Map your fields. Done.

How it works

Step 1 — describe your data

Upload a CSV. The tool reads your column names and example values and suggests how each column maps to one of five questions:

| | | |---|---| | WHO | A person, organisation, or agent | | WHAT | The content, subject, type, or identity | | WHEN | A date, year, or time period | | WHERE | A place, location, or jurisdiction | | HOW | A process, method, or procedure |

Review the suggestions, correct anything wrong, fill in any blanks. The tool saves your decisions as a lens file — a portable, reusable mapping.

Step 2 — compile your data

lens-tool ingest \
  --input  my_data.csv \
  --lens   my_lens.json \
  --into   csv://my_spoke_dir

Every row becomes a set of semantic facts. Artist becomes WHO|artist|Miles Davis. Released becomes WHEN|released|1959.

Step 3 — query by meaning

lens-tool query --interactive --substrate csv://my_spoke_dir

Then type queries at the prompt:

peirce> WHO.artist = "Miles Davis"
peirce> WHO.artist = "Miles Davis" AND WHEN.released = "1959"
peirce> WHEN.released BETWEEN "1955" AND "1965"
peirce> WHO.label CONTAINS "Blue Note"
peirce> exit

Commands

lens-tool ui

Open a browser-based wizard. Drop in a CSV, map your fields, download a lens file or SQL views. No command line required after this point.

lens-tool ui
lens-tool ui --port 4000

lens-tool suggest

Read a CSV and suggest field mappings for human review. Produces a declaration CSV pre-filled with suggested dimension and semantic key for each field.

lens-tool suggest --input my_data.csv --output declaration.csv
lens-tool suggest --input my_data.csv --output declaration.csv --sample 20

Open the output in Excel, Google Sheets, or LibreOffice. Review suggestions, correct anything wrong, fill in blanks. Then pass to generate.

lens-tool generate

Turn a reviewed declaration CSV into a formal lens artifact JSON.

lens-tool generate \
  --declaration declaration.csv \
  --meta        lens_meta.csv \
  --output      my_lens.json

Meta CSV format (two columns: key, value):

key,value
lens_id,my_collection_v1
lens_version,1.0
why|intent,collection_field_mapping
why|authority,My Organisation
why|scope,music_collection
created_by,Your Name

lens-tool ingest

Compile a CSV into SNF spoke tables using a lens.

# To CSV spoke files (works everywhere, no extra install)
lens-tool ingest --input my_data.csv --lens my_lens.json --into csv://my_spoke_dir

# To DuckDB (generates spoke CSVs + a ready-to-run load script)
lens-tool ingest --input my_data.csv --lens my_lens.json --into duckdb://my_data.duckdb

# To SQL INSERT statements (runs against any SQL database)
lens-tool ingest --input my_data.csv --lens my_lens.json --into sql://my_data.sql

Rows with missing or empty nucleus fields are rejected and reported. Nothing is silently skipped.

lens-tool query

Run a Peirce query against compiled SNF data. Shows full human-readable results.

# Interactive mode — type queries at a prompt (recommended on Windows)
lens-tool query --interactive --substrate csv://my_spoke_dir

# Single query from a file
lens-tool query --file my_query.peirce --substrate csv://my_spoke_dir

# Show more results (default is 20)
lens-tool query --file my_query.peirce --substrate csv://my_spoke_dir --limit 100

Query syntax (Peirce):

# Equality
WHO.artist = "Miles Davis"

# AND across dimensions — intersection
WHO.artist = "Miles Davis" AND WHEN.released = "1959"
WHO.attorney = "Smith" AND WHERE.office = "Seattle"

# OR within a dimension — union
WHO.artist = "Miles Davis" OR WHO.artist = "John Coltrane"

# Range
WHEN.released BETWEEN "1955" AND "1965"

# Text search
WHAT.title CONTAINS "love"
WHO.label CONTAINS "Blue Note"
WHO.artist PREFIX "John"

# Combined
WHO.label CONTAINS "Blue Note" AND WHEN.released BETWEEN "1955" AND "1970"

# DNF — OR at top level
(WHO.artist = "Miles Davis" AND WHEN.released = "1959") OR
(WHO.artist = "John Coltrane" AND WHEN.released = "1964")

lens-tool benchmark

Prove substrate neutrality. Same data, same Peirce queries, multiple databases — verifies identical results and compares timing.

lens-tool benchmark \
  --input   my_data.csv \
  --lens    my_lens.json \
  --queries queries.txt \
  --against csv://substrate_a \
  --against csv://substrate_b \
  --runs    5

Query file — one Peirce query per line, # for comments:

# queries.txt
WHO.artist = "Miles Davis"
WHO.artist = "Miles Davis" AND WHEN.released = "1959"
WHEN.released BETWEEN "1955" AND "1965"
WHO.label CONTAINS "Blue Note"

Expected output:

✓ ALL QUERIES RETURN IDENTICAL RESULTS ACROSS ALL SUBSTRATES
Substrate neutrality confirmed on your data.

Try the built-in example

node examples/discogs/run.js

Runs the full pipeline on a sample music collection — compiles the data, proves substrate neutrality, runs a live Peirce query. Takes about 5 seconds.

Then query it interactively:

lens-tool query --interactive --substrate csv://examples/discogs/.tmp/substrate_a

SQL views — zero migration path

Not ready to compile your data? Generate SQL views instead:

lens-tool ui
# Step 3: choose "Generate SQL views — PostgreSQL"
# Download the SQL, replace YOUR_TABLE_NAME, run it in your database

The views present your existing tables in SNF coordinate format without touching your data. Delete them if you change your mind.

Composite nucleus

Some records are only uniquely identified by combining two fields. In legal billing, neither client_id nor matter_id alone is sufficient — you need both.

Set this up in the UI by choosing Composite nucleus and selecting both fields. The tool joins them: billing:cm:CL001-MAT042

Any row missing either component is rejected at ingest time with a clear error.

The lens file

A lens is a JSON file that declares what your columns mean:

{
  "lens_id": "spotify_v1",
  "nucleus": {
    "type": "single",
    "field": "track_id",
    "prefix": "spotify:track"
  },
  "coordinate_map": {
    "Artist":   { "dimension": "who",  "semantic_key": "artist" },
    "Released": { "dimension": "when", "semantic_key": "released" },
    "Title":    { "dimension": "what", "semantic_key": "title" }
  }
}

The lens is portable. The same lens file works with any SNF-compliant substrate. Share it with others working on the same kind of data.

Python library

The lens format and spoke table schema are language-neutral. A Python implementation that reads the same lens JSON and writes the same spoke table structure is a conformant implementation.

Contributions welcome. A conformant Python library passes the benchmark correctness check: same queries, same data, identical results against the Node.js implementation.

License

MIT — https://github.com/peirce-lang