Lotus

The embeddable BI engine for Elixir apps — SQL editor, dashboards, visualizations, and AI-powered query generation that mount directly in your Phoenix app. 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 — SQL editor, charts, dashboards — running on your existing infrastructure. Read-only by design, production-safe from day one.

We're running Lotus in production at Accomplish.

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 (bring your own OpenAI, Anthropic, or Gemini key)
• Web-based SQL 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, "~> 0.14.0"},
    {:lotus_web, "~> 0.12.0"}
  ]
end

2. Configure Lotus

# config/config.exs
config :lotus,
  ecto_repo: MyApp.Repo,
  default_repo: "main",
  data_repos: %{
    "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

• SQL 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 SQL-backed dropdown options
• 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, auto-refresh, and public sharing via secure tokens
• Multi-database support — PostgreSQL, MySQL, and SQLite with per-query repo selection
• Result caching — TTL-based caching with ETS backend, cache profiles, and tag-based invalidation
• CSV export — download query results with streaming support for large datasets
• Schema explorer — browse tables, columns, and statistics interactively
• AI query generation — ask your database questions in plain English; schema-aware, multi-turn conversations using OpenAI, Anthropic, or Gemini (BYOK)
• Read-only by design — all queries run in read-only transactions with automatic timeout controls and session state management

Production Ready

Lotus is built for production use from the ground up:

• Read-only execution — all queries run inside read-only transactions. No accidental 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.

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,
  ecto_repo: MyApp.Repo,
  default_repo: "main",
  data_repos: %{
    "main" => MyApp.Repo,
    "analytics" => MyApp.AnalyticsRepo
  }

# Optional: Configure caching
config :lotus,
  cache: [
    adapter: Lotus.Cache.ETS,
    profiles: %{
      results: [ttl: 60_000],
      schema: [ttl: 3_600_000],
      options: [ttl: 300_000]
    }
  ]

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 SQL directly (read-only)
{:ok, results} = Lotus.run_sql("SELECT * FROM products WHERE price > $1", [100])

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

AI Query Generation

Ask your database questions in plain English. The AI assistant discovers your schema, respects visibility rules, and generates accurate, schema-qualified SQL. 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: "my_repo"
)

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

Bring your own OpenAI, Anthropic, or Gemini API key. See the AI query generation guide for setup and multi-turn conversation support.

Configuration

See the configuration guide for all options including:

• Data repository setup (single and multi-database)
• Schema, table, and column visibility controls
• Cache backends and TTL profiles
• AI provider configuration
• Query execution options (timeouts, search paths)

How Lotus Compares

Development Setup

Prerequisites

• Elixir 1.17+ / OTP 25+
• PostgreSQL 14+, MySQL 8.0+, SQLite 3

Setup

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

# Optional: Start MySQL with Docker Compose
docker compose up -d mysql

mix ecto.create && mix ecto.migrate

Running tests

mix test

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.