Lens

Lens is a governed business-intelligence service: a semantic model (tables, joins, dimensions, measures) over a SQL database, a query planner that is structurally incapable of double-counting across joins, and a dashboard layer that renders results as self-contained HTML/SVG. It plays the role Google Looker plays, in a single small service with no external dependencies beyond a SQL Server connection.

This repository is the deployable .NET edition of Lens. The service core is generated code: it is transpiled from the Codex sources in the NewRepository depot (apps/lens/ and apps/data/) by the Codex C# plug. See Provenance before editing anything under src/.

Quick start

Prerequisites: .NET SDK 9+, SQL Server (LocalDB is fine), sqlcmd.

.\setup-db.ps1        # create + seed the demo database (LocalDB lensdemo)
.\run.ps1             # build and launch on http://localhost:8080

Then open http://localhost:8080/dashboard in a browser, or run the sample queries from a second terminal:

.\samples\queries.ps1

To point at a real SQL Server instead of LocalDB:

.\setup-db.ps1 -Server myhost -Database lens_prod
.\run.ps1 -ConnectionString "Server=myhost;Database=lens_prod;Integrated Security=true;TrustServerCertificate=true"

The query protocol

POST /query (any non-dashboard request takes this path) with a plain text body, one directive per line:

dimensions: city, order-month
measures: revenue, order-count
limit: 10

The reply is a JSON array of rows, each row an array of strings, in dimension-then-measure column order, sorted ascending by the dimensions:

[["Capital City","202606","5000","1"],["Capital City","202607","1500","1"], ...]

Planner and validation errors come back as a JSON array of diagnostic strings (HTTP 200 — the protocol reports analysis failures as data):

["diag 22: unknown measure profit"]

GET /dashboard returns the demo dashboard as one self-contained HTML page (inline SVG, inline CSS, zero external references).

Why "grain-correct" matters

Join a user table (one row per user) to an order table (many rows per user) and then SUM(credit-limit), and every user's credit limit is counted once per order — the classic BI fan-out bug. With the demo data a naive plan reports Springfield's total credit as 80000; the true answer is 40000.

Lens never produces the naive plan. Every measure is aggregated at the grain of its own home table before any join, then the pre-aggregated rows are joined and combined. Fan-out cannot reach an aggregate because nothing is summed after a row can be duplicated. Looker solves this with a SQL trick (symmetric aggregates); Lens solves it in the planner's structure. docs/DESIGN.md walks the exact SQL emitted.

Repository layout

run.ps1               build + launch the service
setup-db.ps1          create + seed the demo database
db/seed.sql           demo star schema (users/orders), idempotent
src/LensService/      the service (GENERATED C# + csproj)
docs/DESIGN.md        architecture, planner, protocol, SQL shapes
docs/CODE-STANDARDS.md  what generated vs authored code may look like
docs/EXTENDING.md     new database backends, auth, new routes/charts
samples/queries.ps1   scripted requests with expected results
samples/dashboard.html  a captured /dashboard page

Configuration

Setting	How	Default
SQL connection	LENS_SQL_CONN environment variable (set by run.ps1)	LocalDB lensdemo
Listen address	baked into the generated code	http://localhost:8080/
Request budget	baked into the generated code	1024 requests, then exit

The listener binds localhost only and the service has no authentication out of the box — put a reverse proxy in front for anything beyond local use. docs/EXTENDING.md covers both in-process and proxy-based auth.

Provenance

src/LensService/LensService.cs is emitted by the Codex→C# transpiler from the Codex sources of Lens (the semantic model, planner, T-SQL emitter, dashboard renderer, and service loop are all written in Codex; the same sources also run natively on the Codex bare-metal target with no SQL Server at all). Do not hand-edit the generated file — change the Codex sources and re-emit, or follow the divergence policy in CLAUDE.md. The generated file's structure and its deliberate extension choke points (_LensSql, _LensHttp) are documented in docs/DESIGN.md and docs/EXTENDING.md.

Known limitations (v1)

• Average measures work natively but the SQL service path returns their undivided __sum/__cnt column pair; the demo model avoids averages. Measure math is integer arithmetic throughout.
• One SQL dialect (T-SQL). See docs/EXTENDING.md for the dialect porting story.
• The HTTP loop serves one request at a time (strict accept/reply pairing) and exits after its request budget.
• Date bucketing covers year/quarter/month/day over Unix-second columns; no week buckets, no time zones (UTC only).