Lens (phoenix_lens)

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.

Install

def deps do

  [{:phoenix_lens, "~> 0.1.1"}]

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; see docs/Phoenix.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.1"},

    {: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.

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