# v0.1 Specification

> MVP: Chat with Postgres

**Status**: Draft  
**Target**: First usable release

---

## Overview

v0.1 delivers the core value proposition: connect to a Postgres database and query it using natural language. The LLM generates SQL, safe queries execute automatically, and results display in a terminal UI.

---

## User Stories

### US-1: Connect to Database

> As a user, I want to connect to my Postgres database so I can start querying it.

**Acceptance Criteria:**

- User can provide connection details via command-line arguments
- User can provide connection details via config file
- Connection string format: `postgres://user:pass@host:port/database`
- Connection errors display clear, actionable messages
- Successful connection shows database name in UI header

### US-2: Ask Questions in Natural Language

> As a user, I want to ask questions about my data in plain English so I don't have to write SQL.

**Acceptance Criteria:**

- Text input field at bottom of screen accepts natural language
- Pressing Enter submits the question
- Question appears in chat history
- LLM response streams into chat panel
- Generated SQL is visible in sidebar

### US-3: View Query Results

> As a user, I want to see query results in a readable table format.

**Acceptance Criteria:**

- Results display as a formatted table in chat panel
- Columns have headers matching database column names
- Long values truncate with ellipsis
- Tables scroll horizontally if too wide
- Tables scroll vertically if too many rows
- Row count displays below table

### US-4: Safe Query Execution

> As a user, I want read queries to run automatically but write queries to require my approval.

**Acceptance Criteria:**

- SELECT, EXPLAIN, SHOW queries execute immediately
- INSERT, UPDATE queries prompt for confirmation
- DELETE, DROP, TRUNCATE, ALTER queries prompt with warning
- User can approve (y/Enter) or cancel (n/Esc) mutations
- Cancelled queries show "Query cancelled" message

### US-5: View Query History

> As a user, I want to see what SQL queries have been executed so I can learn and debug.

**Acceptance Criteria:**

- Sidebar shows list of executed queries
- Each entry shows: truncated SQL, execution time, row count
- Most recent query at top
- Clicking/selecting a query shows full SQL
- Failed queries show error indicator

### US-6: Write Raw SQL

> As a user, I want to write SQL directly when I know exactly what I need.

**Acceptance Criteria:**

- Prefix input with `/sql` to enter raw SQL mode
- Raw SQL bypasses LLM, goes directly to safety classifier
- Same safety rules apply (auto-execute reads, confirm writes)
- Results display same as LLM-generated queries

### US-7: Exit Application

> As a user, I want to exit the application cleanly.

**Acceptance Criteria:**

- `Ctrl+C` or `Ctrl+Q` exits application
- `/quit` or `/exit` command exits application
- Terminal state restored on exit (no artifacts)

---

## Functional Requirements

### FR-1: Connection Management

#### FR-1.1: Command-Line Connection

```bash
# Connection string
glance "postgres://user:pass@localhost:5432/mydb"

# Individual arguments
glance --host localhost --port 5432 --database mydb --user postgres

# With password prompt
glance --host localhost --database mydb --user postgres --password
```

#### FR-1.2: Config File Connection

Location: `~/.config/glance/config.toml`

```toml
[connections.default]
host = "localhost"
port = 5432
database = "mydb"
user = "postgres"
# password omitted = prompt on connect

[connections.prod]
host = "prod.example.com"
port = 5432
database = "myapp"
user = "readonly"
```

```bash
# Use default connection
glance

# Use named connection
glance --connection prod
```

#### FR-1.3: Password Handling

- Password can be in connection string (not recommended)
- Password can be in config file (not recommended)
- Password from `PGPASSWORD` environment variable
- Password prompted interactively if not provided
- Passwords never logged or displayed

#### FR-1.4: Connection Errors

| Error              | Message                                                              |
| ------------------ | -------------------------------------------------------------------- |
| Host unreachable   | "Cannot connect to {host}:{port}. Check that the server is running." |
| Auth failed        | "Authentication failed for user '{user}'. Check your credentials."   |
| Database not found | "Database '{database}' does not exist."                              |
| SSL required       | "Server requires SSL. Add '?sslmode=require' to connection string."  |

---

### FR-2: Schema Discovery

#### FR-2.1: Introspection Scope

On successful connection, automatically discover:

- All tables in `public` schema (configurable in future)
- For each table:
  - Column names and data types
  - Primary key columns
  - NOT NULL constraints
  - Default values
- Foreign key relationships between tables
- Indexes (name and columns)

#### FR-2.2: Schema Representation

Internal schema model:

```
Schema
├── tables: Vec<Table>
│   ├── name: String
│   ├── columns: Vec<Column>
│   │   ├── name: String
│   │   ├── data_type: String
│   │   ├── is_nullable: bool
│   │   ├── is_primary_key: bool
│   │   └── default: Option<String>
│   ├── primary_key: Vec<String>
│   └── indexes: Vec<Index>
└── foreign_keys: Vec<ForeignKey>
    ├── from_table: String
    ├── from_columns: Vec<String>
    ├── to_table: String
    └── to_columns: Vec<String>
```

#### FR-2.3: Schema for LLM

Schema is formatted as context for the LLM system prompt:

```
Database Schema:

Table: users
  - id: integer (PK, NOT NULL)
  - email: varchar(255) (NOT NULL, UNIQUE)
  - name: varchar(100)
  - created_at: timestamp (NOT NULL, DEFAULT now())

Table: orders
  - id: integer (PK, NOT NULL)
  - user_id: integer (NOT NULL, FK -> users.id)
  - total: numeric(10,2) (NOT NULL)
  - status: varchar(20) (NOT NULL, DEFAULT 'pending')
  - created_at: timestamp (NOT NULL, DEFAULT now())

Foreign Keys:
  - orders.user_id -> users.id
```

---

### FR-3: LLM Integration

#### FR-3.1: Supported Providers

- OpenAI (gpt-4o, gpt-4o-mini)
- Anthropic (claude-3-5-sonnet-latest)

#### FR-3.2: Configuration

```toml
[llm]
provider = "openai"  # or "anthropic"
model = "gpt-4o"     # or "claude-3-5-sonnet-latest"
```

API keys via environment:

- `OPENAI_API_KEY` for OpenAI
- `ANTHROPIC_API_KEY` for Anthropic

#### FR-3.3: System Prompt

The LLM receives a system prompt containing:

1. Role definition (SQL assistant for Postgres)
2. Database schema (from FR-2.3)
3. Output format instructions
4. Safety guidelines

````
You are a SQL assistant for a PostgreSQL database. Generate SQL queries based on user questions.

DATABASE SCHEMA:
{schema}

INSTRUCTIONS:
- Generate only valid PostgreSQL SQL
- Return ONLY the SQL query, no explanations
- Use appropriate JOINs based on foreign keys
- Limit results to 100 rows unless user specifies otherwise
- Use parameterized-style queries (though parameters won't be used in v0.1)
- Never generate DROP DATABASE or similar destructive operations
- If the question cannot be answered with the schema, explain why

OUTPUT FORMAT:
Return the SQL query wrapped in ```sql code blocks.
If you need to explain something, put it before or after the code block.
````

#### FR-3.4: Conversation Context

- Maintain conversation history for context
- Include last 10 exchanges in API calls
- Each exchange = user message + assistant response
- Clear context with `/clear` command

#### FR-3.5: Response Parsing

1. Extract SQL from response (look for ```sql blocks)
2. If no SQL found, display response as plain text
3. If SQL found, pass to safety classifier
4. Display any non-SQL text as explanation

#### FR-3.6: Streaming

- Stream LLM responses token-by-token
- Update UI as tokens arrive
- SQL extraction happens after stream completes

#### FR-3.7: Error Handling

| Error           | Behavior                                              |
| --------------- | ----------------------------------------------------- |
| API key missing | Show error on startup, exit                           |
| Rate limited    | Show "Rate limited. Please wait." + retry after delay |
| API error       | Show "LLM error: {message}" in chat                   |
| Timeout (30s)   | Show "Request timed out. Try again."                  |

---

### FR-4: Query Safety Classification

#### FR-4.1: Classification Categories

| Category    | SQL Statements                                       | Behavior          |
| ----------- | ---------------------------------------------------- | ----------------- |
| Safe        | SELECT, EXPLAIN, SHOW, WITH (read-only CTE)          | Auto-execute      |
| Mutating    | INSERT, UPDATE, MERGE                                | Confirm           |
| Destructive | DELETE, DROP, TRUNCATE, ALTER, CREATE, GRANT, REVOKE | Confirm + warning |

#### FR-4.2: Classification Logic

1. Parse SQL using sqlparser-rs with PostgreSQL dialect
2. Extract statement type from AST
3. For multi-statement queries, use most dangerous classification
4. CTEs: classify based on the final statement

#### FR-4.3: Confirmation UI

For mutating queries:

```
┌─────────────────────────────────────────────────────────┐
│ ⚠ This query will modify data:                         │
│                                                         │
│   UPDATE users SET status = 'inactive'                  │
│   WHERE last_login < '2024-01-01'                       │
│                                                         │
│ Execute? [y/N]                                          │
└─────────────────────────────────────────────────────────┘
```

For destructive queries:

```
┌─────────────────────────────────────────────────────────┐
│ 🛑 WARNING: This query may cause data loss:             │
│                                                         │
│   DELETE FROM orders WHERE status = 'cancelled'         │
│                                                         │
│ This action cannot be undone. Execute? [y/N]            │
└─────────────────────────────────────────────────────────┘
```

#### FR-4.4: Parse Failures

If SQL cannot be parsed:

- Treat as destructive (require confirmation)
- Show warning: "Could not parse SQL. Please review carefully."

---

### FR-5: Query Execution

#### FR-5.1: Execution Flow

1. Receive SQL string
2. Execute against database connection
3. Capture results or error
4. Record execution time
5. Update query log
6. Display results in chat

#### FR-5.2: Result Handling

- Fetch all rows (up to 1000 row limit for v0.1)
- Preserve column order from query
- Preserve data types for display formatting
- NULL values display as `NULL` (styled differently)

#### FR-5.3: Execution Errors

Display errors inline in chat:

```
Error executing query:
  ERROR: column "emal" does not exist
  HINT: Perhaps you meant "email"
```

#### FR-5.4: Timeout

- Query timeout: 30 seconds
- Show "Query timed out after 30 seconds" on timeout
- Connection remains open for subsequent queries

---

### FR-6: Terminal User Interface

#### FR-6.1: Layout

```
┌─────────────────────────────────────────────────────────────┐
│ Glance v0.1                           [db: myapp @ localhost]│
├─────────────────────────────────────────┬───────────────────┤
│                                         │ Query Log         │
│  Chat Panel                             │                   │
│  (scrollable)                           │ ▸ SELECT * FRO... │
│                                         │   ✓ 23ms, 47 rows │
│                                         │                   │
│                                         │ ▸ SELECT COUNT... │
│                                         │   ✓ 8ms, 1 row    │
│                                         │                   │
│                                         │                   │
│                                         │                   │
├─────────────────────────────────────────┴───────────────────┤
│ > _                                                         │
└─────────────────────────────────────────────────────────────┘
```

#### FR-6.2: Panels

**Header Bar**

- Application name and version (left)
- Connected database and host (right)

**Chat Panel** (left, ~70% width)

- Scrollable message history
- User messages right-aligned or prefixed with "You:"
- Assistant messages left-aligned or prefixed with "Glance:"
- Result tables inline
- Scroll with arrow keys, Page Up/Down, mouse wheel

**Query Sidebar** (right, ~30% width)

- List of executed queries
- Most recent at top
- Each entry: truncated SQL (first 30 chars), status icon, time, row count
- Selectable with arrow keys
- Enter on selected query shows full SQL in modal

**Input Bar** (bottom)

- Single-line text input
- Prompt character: `>`
- Cursor visible
- Standard text editing (backspace, delete, home, end, arrows)

#### FR-6.3: Keyboard Shortcuts

| Key          | Action                                     |
| ------------ | ------------------------------------------ |
| Enter        | Submit input                               |
| Ctrl+C       | Exit application                           |
| Ctrl+Q       | Exit application                           |
| Ctrl+L       | Clear chat history                         |
| Tab          | Switch focus between chat and sidebar      |
| ↑/↓          | Scroll chat (when chat focused)            |
| ↑/↓          | Navigate query list (when sidebar focused) |
| Page Up/Down | Scroll chat by page                        |
| Home/End     | Scroll to top/bottom of chat               |
| Esc          | Cancel current operation / close modal     |

#### FR-6.4: Commands

| Command            | Action                             |
| ------------------ | ---------------------------------- |
| `/sql <query>`     | Execute raw SQL                    |
| `/clear`           | Clear chat history and LLM context |
| `/schema`          | Display schema summary             |
| `/quit` or `/exit` | Exit application                   |
| `/help`            | Show available commands            |

#### FR-6.5: Result Table Rendering

```
┌────┬────────────────┬────────────┬─────────────────────┐
│ id │ email          │ name       │ created_at          │
├────┼────────────────┼────────────┼─────────────────────┤
│ 1  │ alice@test.com │ Alice      │ 2024-01-15 10:30:00 │
│ 2  │ bob@test.com   │ Bob        │ 2024-01-16 14:22:00 │
│ 3  │ carol@test.com │ NULL       │ 2024-01-17 09:15:00 │
└────┴────────────────┴────────────┴─────────────────────┘
3 rows returned (23ms)
```

- Column widths auto-sized based on content (up to max)
- Max column width: 40 characters
- Values exceeding max truncate with `...`
- NULL values styled distinctly (dim or italic)
- Borders using Unicode box-drawing characters

#### FR-6.6: Colors and Styling

| Element           | Style                         |
| ----------------- | ----------------------------- |
| Header            | Bold, accent color background |
| User message      | Default text                  |
| Assistant message | Default text                  |
| SQL in sidebar    | Monospace, dim                |
| Success indicator | Green ✓                       |
| Error indicator   | Red ✗                         |
| Warning text      | Yellow                        |
| NULL values       | Dim/italic                    |
| Table borders     | Dim                           |
| Input prompt      | Bold                          |

Use terminal default colors where possible for theme compatibility.

---

### FR-7: Configuration

#### FR-7.1: Config File Location

- Linux/macOS: `~/.config/glance/config.toml`
- Windows: `%APPDATA%\glance\config.toml`

Create directory and default config if not exists on first run.

#### FR-7.2: Config Schema

```toml
# LLM Configuration
[llm]
provider = "openai"           # "openai" | "anthropic"
model = "gpt-4o"              # Model name

# Default connection (used when no --connection specified)
[connections.default]
host = "localhost"
port = 5432
database = "postgres"
user = "postgres"
# password = "secret"         # Optional, not recommended

# Additional named connections
[connections.prod]
host = "prod.example.com"
port = 5432
database = "myapp"
user = "readonly"
```

#### FR-7.3: Config Validation

On startup:

- Validate TOML syntax
- Validate required fields present
- Validate port is valid number
- Warn on unknown fields (don't fail)

#### FR-7.4: Config Errors

```
Configuration error in ~/.config/glance/config.toml:
  Line 5: missing field 'database' in connections.default
```

---

### FR-8: CLI Interface

#### FR-8.1: Usage

```
glance [OPTIONS] [CONNECTION_STRING]

Arguments:
  [CONNECTION_STRING]  PostgreSQL connection string

Options:
  -h, --host <HOST>          Database host
  -p, --port <PORT>          Database port [default: 5432]
  -d, --database <DATABASE>  Database name
  -U, --user <USER>          Database user
  -W, --password             Prompt for password
  -c, --connection <NAME>    Use named connection from config
      --config <PATH>        Config file path
  -v, --version              Print version
      --help                 Print help
```

#### FR-8.2: Argument Precedence

1. Command-line arguments (highest)
2. Connection string
3. Named connection from config
4. Default connection from config
5. Environment variables (PGHOST, PGPORT, etc.)

---

### FR-9: Error Handling

#### FR-9.1: Error Display

All errors display in chat panel with clear formatting:

```
Error: {category}
  {message}
  {hint if available}
```

#### FR-9.2: Error Categories

- **Connection Error**: Database connectivity issues
- **Query Error**: SQL execution failures
- **LLM Error**: API communication issues
- **Config Error**: Configuration problems
- **Internal Error**: Unexpected application errors

#### FR-9.3: Graceful Degradation

- LLM failure: Allow raw SQL mode to continue working
- Connection lost: Attempt reconnect, show status
- Partial results: Display what was received before error

---

## Non-Functional Requirements

### NFR-1: Performance

- Startup time: < 500ms to interactive prompt
- Schema introspection: < 2s for databases with < 100 tables
- UI responsiveness: < 16ms frame time (60fps)
- Query results: Stream first rows within 100ms of execution

### NFR-2: Resource Usage

- Memory: < 100MB baseline, < 500MB with large result sets
- CPU: Minimal when idle (< 1%)
- No background processes when not in use

### NFR-3: Compatibility

- Linux: Ubuntu 20.04+, Debian 10+, Fedora 35+
- macOS: 11.0+ (Big Sur)
- Windows: 10+
- Terminal: Any terminal supporting ANSI escape codes
- Postgres: 12+

### NFR-4: Security

- No credentials in logs
- No credentials in error messages
- Secure connection support (SSL/TLS)
- API keys only from environment or config file

---

## Out of Scope for v0.1

Explicitly not included:

- Query history persistence (in-memory only)
- Multiple simultaneous connections
- Copy to clipboard
- Export to file
- Ollama/local LLM support
- SQLite/MySQL support
- Schema modification tools
- Query performance analysis
- Custom themes
- Mouse support (beyond scroll)
- Tab completion
- Query templates/bookmarks

---

## Open Questions

1. **Large result sets**: Should we paginate in UI, or limit to 1000 rows?
   - _Decision_: Limit to 1000 rows, show warning if truncated

2. **Schema size**: What if schema is too large for LLM context?
   - _Decision_: Truncate with warning, prioritize tables by recent usage (future: let user specify focus tables)

3. **Multi-statement queries**: Allow or reject?
   - _Decision_: Allow, classify by most dangerous statement

4. **Transaction support**: Should mutations be wrapped in transactions?
   - _Decision_: No implicit transactions in v0.1, user can use explicit BEGIN/COMMIT

---

## Glossary

| Term              | Definition                                                  |
| ----------------- | ----------------------------------------------------------- |
| Chat Panel        | Main area showing conversation history and results          |
| Query Sidebar     | Right panel showing executed SQL queries                    |
| Safety Classifier | Component that categorizes SQL as safe/mutating/destructive |
| Schema Discovery  | Process of introspecting database structure on connect      |
| Streaming         | Displaying LLM response tokens as they arrive               |
