Lens (phoenix_lens)

Copy Markdown View Source

Mountable SQL notebook for Phoenix. Point it at your Ecto Repo, save questions, pin dashboards, and mask PII/PHI in every output.

This is not Metabase. It is the PgHero-shaped tool you mount at /lens so you can stop giving a sidecar JVM a god-mode DB user.

UI

Dummy app on http://localhost:4000/lens. Full tour: docs/Guide.md.

Home

Home: table x-rays and pinned dashboards.

Native SQL with masked cells

Native query: email AS contact is still [redacted].

Notebook

Notebook: filters, metrics, grouping, then a chart.

Dashboard

Dashboard: cards on a 12-column board. Edit to drag and resize.

Catalog

Data catalog, with protected columns marked.

Settings

Settings: PostgreSQL or DuckDB, extra sources, audit retention.

Column protection

Column protection on top of masked_fields and Ecto redact: true.

MCP tokens

MCP project tokens for agents.

Alerts

Alerts on a saved question (email or webhook).

Security

Optional authenticator app and passkeys.

Install

def deps do
  [{:phoenix_lens, "~> 0.1.4"}]
end
config :phoenix_lens,
  repo: MyApp.Repo,
  masked_fields: [:email, :first_name, :last_name, :phone]

Ecto fields marked redact: true are protected automatically.

import PhoenixLensWeb.Router

scope "/" do
  pipe_through [:browser, :require_admin]
  lens "/lens"
end
defmodule MyApp.Repo.Migrations.AddPhoenixLens do
  use Ecto.Migration
  def up, do: PhoenixLens.Migrations.up()
  def down, do: PhoenixLens.Migrations.down()
end

Then open /lens. Prefer a read replica. Product tour, query design, and dummy-app screenshots: docs/Guide.md. Mount details: docs/Phoenix.md. MCP server (agents): docs/MCP.md.

DuckDB engine

Default queries go straight to PostgreSQL. In /lens/settings you can switch the query engine to DuckDB. Lens then:

  1. Starts an in-process DuckDB
  2. Attaches the host Ecto Repo read-only as repo (INSTALL postgres; ATTACH … (TYPE postgres, READ_ONLY))
  3. Lets you attach extra Postgres URLs, SQLite, DuckDB files, Parquet, CSV, or JSON to the same engine

Host tables stay queryable as users or repo.users. Extra databases are alias.table. File sources become a view named after the alias.

Join across sources in one question, for example Postgres + MySQL + a remote CSV:

SELECT u.id, b.sku, p.plan
FROM repo.users u
JOIN billing.orders b ON b.user_id = u.id
JOIN plans p ON p.user_id = u.id

DuckDB is optional. Add the NIF to the host app (the dummy app already does):

def deps do
  [
    {:phoenix_lens, "~> 0.1.4"},
    {:duckdbex, "~> 0.4"}
  ]
end

Questions, dashboards, settings, and the audit log still live on the host Repo. Field policy still runs on every DuckDB result.

MCP server

Agents can drive the same notebook over Model Context Protocol at /lens/mcp. After Plug.Parsers in the host endpoint:

plug PhoenixLensWeb.Plugs.MCP, path: "/lens/mcp"

Settings → MCP issues a project token id (plt_…) and a one-time secret (lns_…). Clients send Authorization: Bearer <secret>. Tools cover catalog, SQL, questions, dashboards, audit, engines, sources, and column protection. Masked cells stay [redacted].

{
  "mcpServers": {
    "phoenix-lens": {
      "url": "http://localhost:4000/lens/mcp",
      "headers": {
        "Authorization": "Bearer lns_…"
      }
    }
  }
}

Details: docs/MCP.md.

Alerts

Saved questions can notify email or a webhook when they return rows, return none, or cross a numeric goal. Configure SMTP and webhook URLs in Settings → Integrations, then click Alert on a question.

Payloads are field-policy masked. Email sending needs {:gen_smtp, "~> 1.2"} in the host app. See docs/Alerts.md.

Security lock

Settings → Security can turn on an authenticator app (TOTP) and register passkeys. After either is enabled, the UI asks for that factor at /lens/unlock. This is an extra lock on the notebook, not a replacement for the host pipeline. MCP tokens skip it. If you lock yourself out: PhoenixLens.Auth.reset!() in IEx.

Dummy app

cd dummy
docker compose up -d
mix setup
mix phx.server

Open http://localhost:4000/lens and run:

SELECT id, email AS contact, first_name FROM users

contact and first_name render as [redacted].

Compliance posture

Designed so the dashboard path does not emit configured PII/PHI (grid, CSV, embeds, logs, audit).

This library is not HIPAA or GDPR certified. You are the operator. Mount behind your own auth, prefer a read replica, and do not confuse IEx/Repo.get with this dashboard.

Threat model: docs/Policy.md.

Standalone

DATABASE_URL=postgres://user:pass@localhost/dbname mix phoenix_lens.server

Then visit http://localhost:8080/lens.

Docs