# Getting Started

This guide covers the basics of using LotusWeb to run SQL queries and manage your data.

## Prerequisites

- LotusWeb installed and mounted (see [Installation](installation.md))
- At least one database configured in Lotus

## Accessing the Dashboard

Visit the mounted path in your browser (e.g., `/lotus`). You should see:

- **Query Editor** - Write and run SQL queries
- **Queries List** - View saved queries
- **Schema Explorer** - Browse database tables

## Writing Your First Query

1. **Open the Query Editor** - Click "New Query" or navigate to the editor
2. **Select Database** - Choose from your configured repositories
3. **Write SQL** - Enter a simple query like:
   ```sql
   SELECT COUNT(*) as total_users FROM users;
   ```
4. **Run Query** - Click the play button or press Cmd+Enter
5. **View Results** - See the results displayed in a table

## Quick Filters

After running a query, you can quickly filter results without rewriting SQL — just right-click any cell value in the results table.

- **Right-click a cell** to open a context menu with filter operators (`=`, `≠`, `>`, `<`, `≥`, `≤`, `LIKE`, `IS NULL`, `IS NOT NULL`)
- **Choose an operator** to add a filter and re-run the query instantly
- **NULL cells** only show `IS NULL` and `IS NOT NULL` operators
- **LIKE filters** automatically wrap the value with `%…%` wildcards
- **Multiple filters** stack with `AND`, displayed as dismissible chips above the results
- **Remove a filter** by clicking the `×` on its chip, or click "Clear all" to remove all filters
- **Run the query manually** (play button or Cmd+Enter) to clear all filters and start fresh

Filters wrap the original query in a CTE, so they work safely with any SQL complexity (joins, subqueries, unions, etc.).

## Sorting Results

You can sort query results by any column without modifying your SQL — just right-click a column header.

- **Right-click a column header** to open a context menu with "Sort ascending" and "Sort descending" options
- **Sort indicator** — the active sort column displays a chevron icon (up for ascending, down for descending)
- **Active sorts** are shown as purple chips above the results table
- **Remove a sort** by clicking the `×` on its chip, or click "Clear all" to remove all sorts
- **Running the query manually** (play button or Cmd+Enter) clears all sorts and starts fresh

Like filters, sorting wraps the original query in a CTE, so it works safely with any SQL complexity.

## Column Statistics

Hover over any column header to see a statistics popover with information about that column's data:

- **Numeric columns** — count, nulls, distinct values, min, max, avg, median, sum, and a distribution histogram
- **String columns** — count, nulls, distinct values, min/max length, and top values with frequency bars
- **Temporal columns** — count, nulls, distinct values, earliest, latest, and a time distribution chart

The popover appears after a short delay and dismisses when you move your mouse away.

## Using the AI Query Assistant (Optional)

> ⚠️ **Experimental Feature**: The AI Query Assistant is optional and requires setup. See the [AI Assistant Guide](ai-assistant.md) for details.

If AI features are enabled, you can generate and refine SQL through a conversational interface:

1. **Click the robot icon** in the query editor toolbar to open the AI chat drawer
2. **Describe what you want** - e.g., "Show users who signed up in the last 7 days"
3. **Review the generated SQL** - The AI responds in a chat bubble with the query
4. **Click "Use this query"** to insert the generated SQL into the editor
5. **Iterate naturally** - Send follow-up messages to refine ("Add a LIMIT 100", "Group by month instead")
6. **Fix errors automatically** - If a query fails while the AI drawer is open, the error appears in the conversation — click "Ask AI to fix this" to get a corrected query

The AI is schema-aware and will explore your database structure to generate accurate queries. The full conversation context is sent with each request for iterative refinement.

**Note:** AI features are disabled by default and require you to provide your own API key (BYOK) from OpenAI, Anthropic, or Google. See the [AI Assistant Guide](ai-assistant.md) for setup instructions.

## Visualizing Query Results

LotusWeb includes built-in charting to visualize your query results.

### Switching Between Views
- **Table View** - Default tabular display (Cmd/Ctrl+1)
- **Chart View** - Interactive visualization (Cmd/Ctrl+2)

### Available Chart Types
- **Bar Chart** - Compare categorical data
- **Line Chart** - Show trends over time
- **Area Chart** - Visualize cumulative trends
- **Scatter Plot** - Explore relationships between variables
- **Pie Chart** - Display proportions of a whole

### Quick Configuration
1. **Open Settings** - Click the chart icon or press Cmd/Ctrl+G
2. **Select Chart Type** - Choose from the Chart Type tab
3. **Configure Axes** - Set X-Axis, Y-Axis, and optional Color/Series fields
4. **View Chart** - Press Cmd/Ctrl+2 to see your visualization

For detailed chart configuration, best practices, and troubleshooting, see the [Visualizations Guide](visualizations.md).

## Exploring Your Schema

1. **Open Schema Explorer** - Click the tables icon in the editor
2. **Browse Tables** - See all available tables and schemas
3. **View Column Details** - Click on tables to see column information
4. **Insert Names** - Click to insert table/column names into your query (coming soon)

## Saving Queries

1. **Write a Query** - Create a useful query in the editor
2. **Click Save** - Use the Save button in the editor
3. **Add Details** - Provide a name and optional description
4. **Save** - Your query is now saved and can be reused

## Managing Saved Queries

- **View All Queries** - Visit the Queries page to see all saved queries
- **Edit Queries** - Click on a query name to open and modify it
- **Delete Queries** - Use the Delete button when editing a query

## Working with Multiple Databases

If you have multiple repositories configured:

1. **Switch Databases** - Use the database dropdown in the editor
2. **Repository-Specific Queries** - Saved queries remember their database
3. **Cross-Database Analysis** - Save different queries for different data sources

## Security Features

LotusWeb inherits Lotus's security features:

- **Read-Only Queries** - Only SELECT statements are allowed by default (set `read_only: false` in Lotus config to allow writes)
- **Table Visibility** - Some tables may be hidden based on configuration
- **Safe Parameters** - All queries use parameterized execution
- **Timeouts** - Long-running queries timeout automatically (default: 5 seconds)

### Configuring Query Timeouts

By default, queries timeout after 5 seconds. If you need to support long-running queries, enable the `:timeout_options` feature in your router:

```elixir
lotus_dashboard "/lotus",
  features: [:timeout_options]
```

This adds a timeout selector to the query editor toolbar where users can choose from preset durations (5s, 15s, 30s, 60s, 2m, 5m) or disable the timeout entirely on a per-query basis. Both the Lotus client timeout and the database statement timeout are set to the selected value.

## Using Variables in Queries

LotusWeb supports dynamic variables in your SQL queries using the `{{variable_name}}` syntax:

### Adding Variables
1. **Type Variables** - In your SQL query, use `{{variable_name}}` syntax:
   ```sql
   SELECT * FROM orders 
   WHERE status = {{status}} 
     AND created_at >= {{start_date}}
   ```
2. **Automatic Detection** - Variables appear automatically in the toolbar
3. **Configure Variables** - Click the "Variable settings" {x} icon in the toolbar to configure types and widgets

### Variable Types
- **Text** - Plain strings (automatically quoted for safety)
- **Number** - Integers and decimals  
- **Date** - Date picker with ISO format output

### Widget Types
- **Input** - Free text/number entry fields
- **Dropdown** - Select from predefined options (one option per line)
- **Date Picker** - Calendar interface for date variables
- **Tag Input** - Chip-style multi-value entry (when "Allow multiple values" is enabled)
- **Multiselect** - Multi-value dropdown (when "Allow multiple values" is enabled with dropdown options)

### Variable Settings
- **Access Settings** - Click the "Variable settings" {x} icon in the toolbar to open variable settings
- **Help Tab** - Contains detailed usage examples and syntax help
- **Settings Tab** - Configure labels, default values, and widget types

Variables are always sent as prepared parameters, preventing SQL injection attacks.

**Important**: When you save a query, all variable configurations (types, widgets, labels, defaults) are saved with it. However, the actual values users enter are NOT saved - widgets start empty each time unless you set default values in the settings.

## Tips for Success

- **Start Simple** - Begin with basic SELECT queries
- **Use Descriptive Names** - Give your saved queries clear, meaningful names
- **Test First** - Run queries before saving them
- **Check Results** - Always verify your query results make sense
- **Use Variables** - Make queries reusable with `{{variable}}` syntax

## What's Next?

- Explore your database schema using the Schema Explorer
- Create useful reports by saving commonly-used queries
- Visualize your data with the built-in charting - see [Visualizations Guide](visualizations.md)
- Combine queries into interactive dashboards - see [Dashboards Guide](dashboards.md)
- Use variables to make your queries dynamic and reusable
- Read the [Variables and Widgets Guide](variables-and-widgets.md) for advanced variable usage