# Lotus ![Lotus](https://raw.githubusercontent.com/elixir-lotus/lotus/main/media/banner.png)

Hex Version HexDocs CI Status

**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](https://lotus.typhoon.works/) ## 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](https://lotus.typhoon.works/) — 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](https://github.com/elixir-lotus/lotus_web). ## Quick Start Get a fully working BI dashboard in your Phoenix app in under 5 minutes. ### 1. Add dependencies ```elixir # mix.exs def deps do [ {:lotus, "~> 1.0"}, {:lotus_web, "~> 1.0"} ] end ``` ### 2. Configure Lotus ```elixir # config/config.exs config :lotus, storage_repo: MyApp.Repo, default_source: "main", data_sources: %{ "main" => MyApp.Repo } ``` ### 3. Run the migration ```bash mix ecto.gen.migration create_lotus_tables ``` ```elixir defmodule MyApp.Repo.Migrations.CreateLotusTables do use Ecto.Migration def up, do: Lotus.Migrations.up() def down, do: Lotus.Migrations.down() end ``` ```bash mix ecto.migrate ``` ### 4. Mount in your router ```elixir # 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](guides/installation.md). ## 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](https://github.com/elixir-lotus/lotus_clickhouse) and [Elasticsearch](https://github.com/elixir-lotus/lotus_elasticsearch). Custom adapters target anything else, and custom resolvers can load sources dynamically from a database or external service. See the [source adapters guide](guides/source-adapters.md) - **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 ```elixir 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: ```elixir 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 ```elixir # 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 ```elixir 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. ```elixir {: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): ```elixir {: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: ```elixir 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](guides/ai_query_generation.md) for setup, multi-turn conversation support, and query optimization. ## Configuration See the [configuration guide](guides/configuration.md) 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](guides/upgrading-to-v1.md) 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 | Source | Package | Query language | |---|---|---| | **PostgreSQL** | built in (`Lotus.Source.Adapters.Postgres`) | `sql:postgres` | | **MySQL** | built in (`Lotus.Source.Adapters.MySQL`) | `sql:mysql` | | **SQLite** | built in (`Lotus.Source.Adapters.SQLite3`) | `sql:sqlite` | | **Any other Ecto repo** | built in (`Lotus.Source.Adapters.Ecto` fallback) | `sql` | | **ClickHouse** | [lotus_clickhouse](https://github.com/elixir-lotus/lotus_clickhouse) | `sql:clickhouse` | | **Elasticsearch** | [lotus_elasticsearch](https://github.com/elixir-lotus/lotus_elasticsearch) | `json:elasticsearch` | | **Anything else** | your own `Lotus.Source.Adapter` | whatever you declare | 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](guides/source-adapters.md). ## How Lotus Compares | | Lotus | Metabase | Redash | Blazer (Rails) | Livebook | |---|---|---|---|---|---| | **Deployment** | Mounts in your app | Separate service | Separate service | Mounts in your app | Separate service | | **Extra infra** | None | Java + DB | Python + Redis + DB | None | None | | **Auth** | Uses your app's auth | Separate auth system | Separate auth system | Uses your app's auth | Token-based | | **Language** | Elixir | Java/Clojure | Python | Ruby | Elixir | | **Query editor** | Yes | Yes | Yes | Yes | Yes (in code cells) | | **Non-SQL sources** | Yes (pluggable adapters) | Yes | Yes | No | Yes (any Elixir client) | | **Dashboards** | Yes | Yes | Yes | No | No | | **Charts** | 5 types | Many | Many | 3 types | Via libraries | | **AI query gen** | Yes (BYOK) | No | No | No | No | | **Read-only** | By design | Configurable | Configurable | Configurable | No | | **Cost** | Free | Free/Paid | Free | Free | Free | ## 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 ```bash 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 ```bash mix test.setup # drop, create, and migrate the test databases mix test ``` See the [contribution guide](guides/contributing.md) for the full workflow. ## Contributing See the [contribution guide](guides/contributing.md) for details on how to contribute to Lotus. ## License This project is licensed under the MIT License - see the LICENSE file for details.