Lotus

The embeddable BI engine for Elixir apps — query editor, dashboards, visualizations, and AI-powered query generation that mount directly in your Phoenix app. SQL and non-SQL data sources behind one pluggable adapter contract. No Metabase. No Redash. No extra infrastructure.

Try the live demo

Why Lotus?

Every app eventually needs analytics, reporting, or an internal SQL tool. The usual options — Metabase, Redash, Grafana — mean another service to deploy, another auth system to sync, another thing to keep running.

Lotus takes a different approach: it mounts inside your Phoenix app. Add the dependency, run a migration, add one line to your router, and you have a full BI interface — query editor, charts, dashboards — running on your existing infrastructure. Read-only by design, production-safe from day one.

And it is not limited to SQL. Every data source is wrapped behind a uniform Lotus.Source.Adapter contract, so a Postgres repo, a ClickHouse HTTP endpoint, and an Elasticsearch cluster all run through the same pipeline, the same visibility rules, the same cache, and the same AI assistant.

See It in Action

Try the live demo — a full Lotus Web instance with sample data.

What you get out of the box:

• Ask your database questions in plain English — AI-powered query generation with multi-turn conversations, query explanations, and optimization suggestions (bring your own OpenAI, Anthropic, or Gemini key)
• Web-based query editor with syntax highlighting and autocomplete
• Interactive schema explorer for browsing tables and columns
• 5 chart types (bar, line, area, scatter, pie) saved per query
• Dashboards with grid layouts, auto-refresh, and public sharing

Lotus Web is the companion UI package — see lotus_web.

Quick Start

Get a fully working BI dashboard in your Phoenix app in under 5 minutes.

1. Add dependencies

# mix.exs
def deps do
  [
    {:lotus, "~> 1.0"},
    {:lotus_web, "~> 1.0"}
  ]
end

2. Configure Lotus

# config/config.exs
config :lotus,
  storage_repo: MyApp.Repo,
  default_source: "main",
  data_sources: %{
    "main" => MyApp.Repo
  }

3. Run the migration

mix ecto.gen.migration create_lotus_tables

defmodule MyApp.Repo.Migrations.CreateLotusTables do
  use Ecto.Migration

  def up, do: Lotus.Migrations.up()
  def down, do: Lotus.Migrations.down()
end

mix ecto.migrate

4. Mount in your router

# lib/my_app_web/router.ex
import Lotus.Web.Router

scope "/", MyAppWeb do
  pipe_through [:browser, :require_authenticated_user]

  lotus_dashboard "/lotus"
end

5. Visit /lotus in your browser

That's it. You have a full BI dashboard running inside your Phoenix app.

For the complete setup guide (caching, multiple databases, visibility controls), see the installation guide.

Features

• Query editor with syntax highlighting, autocomplete, and real-time execution
• Query management — save, organize, and reuse queries with descriptive names
• Smart variables — parameterize queries with {{variable}} syntax, configurable input widgets, and query-backed dropdown options. Each adapter owns its own substitution, so prepared-statement engines bind parameters while JSON/DSL engines inline properly escaped literals
• Visualizations — 5 chart types (bar, line, area, scatter, pie) with renderer-agnostic config DSL
• Dashboards — combine queries into interactive views with 12-column grid layouts, filters mapped onto query variables, auto-refresh, and public sharing via secure tokens
• Pluggable data sources — every source is wrapped in a uniform Lotus.Source.Adapter behaviour, so the execution pipeline is decoupled from Ecto and SQL. First-party adapters cover PostgreSQL, MySQL, and SQLite; external packages add ClickHouse and Elasticsearch. Custom adapters target anything else, and custom resolvers can load sources dynamically from a database or external service. See the source adapters guide
• Result caching — TTL-based caching with ETS or Cachex backends, cache profiles, a pluggable Lotus.Cache.KeyBuilder, and tag- and scope-based invalidation
• Exports — CSV, JSON, and JSONL, with streaming CSV for large result sets and a ZIP export of a whole dashboard
• Result filters — apply column-level filters on query results via Lotus.Query.Filter; multiple filters stack with AND, and each adapter declares which operators it supports
• Result sorting — apply column-level sorting on query results via Lotus.Query.Sort, injected by the adapter rather than concatenated
• Windowed pagination — window: [limit: _, offset: _, count: :exact | :none] on any query, with the exact total supplied either inline by the engine or by a separate count query
• Schema explorer — browse namespaces, tables, columns, and statistics interactively
• AI query generation — ask your database questions in plain English; schema-aware, multi-turn conversations using OpenAI, Anthropic, Gemini, or any other ReqLLM provider (BYOK)
• AI query explanation — get plain-language explanations of what a query does, including selected fragments; understands Lotus {{variable}} and [[optional]] syntax
• AI query optimization — get actionable optimization suggestions (indexes, rewrites, schema changes) powered by query-plan analysis
• Adapter-driven AI — each adapter describes its own query language, example query, syntax notes, and error patterns through ai_context/1, so the assistant speaks the engine's dialect instead of assuming SQL. Free-form adapter text only reaches the prompt for adapters you list in :trusted_source_adapters
• Three-level visibility — schema, table, and column rules, with schema taking precedence over table and per-column :omit / {:mask, _} / :error policies applied to result rows
• Middleware — a plug-style pipeline with :before_query, :after_query, and :after_list_* hooks for auditing, access control, and per-tenant statement rewriting
• Telemetry — [:lotus, ...] events for query execution, schema introspection, and cache hits and misses
• Read-only by default — all queries run in read-only transactions with automatic timeout controls and session state management (opt out per-query with read_only: false)

Production Ready

Lotus is built for production use from the ground up:

• Read-only execution — all queries run inside read-only transactions by default. No accidental writes. Pass read_only: false to enable writes.
• Session state management — connection pool state is automatically preserved and restored after each query, preventing pool pollution.
• Automatic type casting — query variables are cast to match column types (UUIDs, dates, numbers, booleans, enums) using schema metadata, with graceful fallbacks.
• Timeout controls — configurable per-query timeouts with sensible defaults.
• Defense-in-depth — preflight authorization, schema/table/column visibility controls, and built-in system table protection.
• No silent degradation — an adapter declares which filter operators it handles and whether it can enforce visibility. A filter it cannot express raises Lotus.UnsupportedOperatorError, and a source that cannot report which resources a statement touches is blocked until you opt in with :allow_unrestricted_resources.
• Language-aware saved queries — a saved query records the family:dialect it was written for (sql:postgres, json:elasticsearch). Repointing it at a source that speaks a different language returns an error instead of handing the statement to an engine that cannot parse it.

Using Lotus as a Library

Lotus works great as a standalone library without the web UI. Use it to run queries, manage saved queries, and build analytics features programmatically.

Configuration

config :lotus,
  storage_repo: MyApp.Repo,
  default_source: "main",
  data_sources: %{
    "main" => MyApp.Repo,
    "analytics" => MyApp.AnalyticsRepo
  }

# Optional: Configure caching
config :lotus,
  cache: %{
    adapter: Lotus.Cache.ETS,
    namespace: "myapp"
  }

A data source value is either an Ecto repo module (handled by the built-in Ecto adapter) or a config map naming a custom adapter:

config :lotus,
  storage_repo: MyApp.Repo,
  default_source: "main",
  data_sources: %{
    "main" => MyApp.Repo,
    "events" => %{adapter: MyApp.ElasticsearchAdapter, url: "http://localhost:9200"}
  },
  source_adapters: [MyApp.ElasticsearchAdapter]

Creating and Running Queries

# Create and save a query
{:ok, query} = Lotus.create_query(%{
  name: "Active Users",
  statement: "SELECT * FROM users WHERE active = true"
})

# Execute a saved query
{:ok, results} = Lotus.run_query(query)

# Execute a statement directly (read-only)
{:ok, results} = Lotus.run_statement("SELECT * FROM products WHERE price > $1", [100])

# Execute against a specific data source
{:ok, results} = Lotus.run_statement("SELECT COUNT(*) FROM events", [], repo: "analytics")

# Page through results with an exact total
{:ok, results} = Lotus.run_statement("SELECT * FROM orders", [],
  window: [limit: 50, offset: 100, count: :exact]
)
results.meta.total_count

Exploring the Schema

Lotus.list_data_source_names()
# => ["main", "analytics"]

{:ok, tables}  = Lotus.list_tables("main")
{:ok, columns} = Lotus.describe_table("main", "users")
{:ok, stats}   = Lotus.get_table_stats("main", "users")

AI Query Generation

Ask your database questions in plain English. The AI assistant discovers your schema, respects visibility rules, and generates an accurate, schema-qualified statement in the language the source actually speaks — the adapter supplies its own example query, syntax notes, and error patterns. Supports multi-turn conversations for iterative refinement — no other embeddable BI tool does this.

{:ok, result} = Lotus.AI.generate_query(
  prompt: "Show all customers with unpaid invoices",
  data_source: "main"
)

result.statement
#=> "SELECT c.id, c.name FROM reporting.customers c ..."

result.model
#=> "openai:gpt-4o"

Get a plain-language explanation of any query (or a selected fragment):

{:ok, result} = Lotus.AI.explain_query(
  statement: "SELECT d.name, COUNT(o.id) FROM departments d LEFT JOIN orders o ...",
  data_source: "main"
)

result.explanation
#=> "This query shows departments ranked by total order count..."

# Explain just a highlighted fragment
{:ok, result} = Lotus.AI.explain_query(
  statement: "SELECT d.name FROM departments d LEFT JOIN employees e ON e.department_id = d.id",
  fragment: "LEFT JOIN employees e ON e.department_id = d.id",
  data_source: "main"
)

Get optimization suggestions for existing queries. suggest_optimizations/1 takes a %Lotus.Query.Statement{} so it works for non-SQL engines too:

statement = Lotus.Query.Statement.new("SELECT * FROM orders WHERE created_at > $1", ["2024-01-01"])

{:ok, result} = Lotus.AI.suggest_optimizations(
  statement: statement,
  data_source: "main"
)

result.suggestions
#=> [%{"type" => "index", "impact" => "high",
#=>    "title" => "Add index on orders.created_at", ...}]

Bring your own API key for OpenAI, Anthropic, Gemini, or any other provider ReqLLM supports. A source whose adapter opts out of AI returns {:error, :ai_not_supported_for_source}. See the AI query generation guide for setup, multi-turn conversation support, and query optimization.

Configuration

See the configuration guide for all options including:

• Data source setup (single source, multi-database, and non-Ecto adapters)
• Registering custom adapters (:source_adapters) and trusting their AI context (:trusted_source_adapters)
• Custom source and visibility resolvers (:source_resolver, :visibility_resolver)
• Schema, table, and column visibility controls
• Cache backends, TTL profiles, and key builders
• Middleware pipelines
• AI configuration
• Query execution options (timeouts, search paths, read-only)

Upgrading

Upgrading from Lotus v0.x? See the upgrading to v1.0 guide for the full list of config renames, DB column renames, middleware/telemetry payload changes, and adapter-contract updates — plus a step-by-step upgrade checklist.

Data Sources

Ecto-backed engines only need a dialect module (use Lotus.Source.Adapters.Ecto, dialect: MyDialect). Non-SQL engines implement Lotus.Source.Adapter directly and carry a native payload — a JSON map, a DSL AST — through the pipeline without ever serializing it to a string. See the source adapters guide.

How Lotus Compares

Development Setup

Prerequisites

• Elixir 1.18+ / OTP 27+
• Docker (the repo ships a docker-compose.yml with PostgreSQL 15 on port 2345 and MySQL 8.0 on port 3307)
• SQLite 3

Setup

git clone https://github.com/elixir-lotus/lotus.git
cd lotus
mix deps.get

# Start PostgreSQL and MySQL
docker compose up -d

mix ecto.setup

Running tests

mix test.setup   # drop, create, and migrate the test databases
mix test

See the contribution guide for the full workflow.

Contributing

See the contribution guide for details on how to contribute to Lotus.

License

This project is licensed under the MIT License - see the LICENSE file for details.