restxlsx/README.md

# restxlsx

A RESTful web service that exposes Microsoft Excel (.xlsx / .xlsm) tables as a live API. Supports REST CRUD, GraphQL, SQL queries, and DDL operations — all backed by an xlsx file as the data store.

---

## Quick Start

```bash
# Use the sample data
./restxlsx

# Or point at your own file
./restxlsx --file data/sample.xlsx

# Open http://localhost:3000/health
```

---

## Configuration

All settings can be provided via CLI flags **or** environment variables. Flags take precedence over env vars.

| Flag | Env Var | Default | Description |
|---|---|---|---|
| `--file` | `RESTXLSX_FILE` | `data/sample.xlsx` | Path to the xlsx/xlsm data file |
| `--host` | `RESTXLSX_HOST` | `localhost` | Interface to bind to (`0.0.0.0` for all) |
| `--port` | `RESTXLSX_PORT` | `3000` | TCP port |
| `--debug` | `RESTXLSX_DEBUG` | `false` | Enable OpenAPI spec, Swagger UI, GraphiQL |
| `--quiet` | `RESTXLSX_CONSOLE=quiet` | `false` | Suppress all stdout output |
| `--log-reads` | `RESTXLSX_LOG_READS` | `true` | Log GET/HEAD requests (`false` to skip) |
| `--log-file` | `RESTXLSX_LOG_FILE` | `""` | Optional path to append logs to file |
| `--auth` | `RESTXLSX_AUTH` | `writer` | Fallback auth role (see Authorization below) |

**Examples:**

```bash
# Listen on all interfaces, port 8080, reader-only
./restxlsx --host 0.0.0.0 --port 8080 --auth reader

# Using env vars
RESTXLSX_FILE=./myfile.xlsx RESTXLSX_DEBUG=true ./restxlsx

# Quiet mode, no read logging, log to file
./restxlsx --quiet --log-reads=false --log-file /var/log/restxlsx.log
```

---

## Endpoints

### Health

```
GET /health
```

### REST CRUD — `GET/POST/PUT/DELETE /api/{table}[/{id}]`

| Method | Path | Description | Auth |
|---|---|---|---|
| `GET` | `/api/tables` | List all discovered table names | reader+ |
| `GET` | `/api/{table}` | List all records in a table | reader+ |
| `GET` | `/api/{table}/{id}` | Get a single record by its first-column ID | reader+ |
| `POST` | `/api/{table}` | Create a new record | writer+ |
| `PUT` | `/api/{table}/{id}` | Update an existing record | writer+ |
| `DELETE` | `/api/{table}/{id}` | Delete a record | writer+ |

The first column of each table is treated as the unique ID column. If omitted on create, an auto-increment
value is assigned.

### GraphQL — `POST /graphql`

Query and mutate data via GraphQL. Dynamic schema is generated from the tables at startup.

```graphql
# Query
{ list_Employees { ID Name Salary } }

# Get single
{ get_Employees(id: "1") { Name Department } }

# Mutate
mutation { create_Employees(ID: "99", Name: "New", Department: "QA", Salary: "70000") { ID } }
```

When `--debug` is enabled, `GET /graphql` serves the GraphiQL IDE.

### SQL — `POST /api/sql`

Execute SQL statements against the data. Supports SELECT, INSERT, UPDATE, DELETE with optional WHERE.

```json
// SELECT
{"query": "SELECT Name, Salary FROM Employees WHERE ID = '1'"}

// SELECT all
{"query": "SELECT * FROM Employees"}

// INSERT
{"query": "INSERT INTO Employees (ID, Name, Department) VALUES ('42', 'Alice', 'Eng')"}

// UPDATE
{"query": "UPDATE Employees SET Salary = '100000' WHERE ID = '42'"}

// DELETE
{"query": "DELETE FROM Employees WHERE ID = '42'"}
```

The SQL dialect is intentionally minimal — only `=` comparisons in WHERE, string/number values,
and single statements per request. This maps directly to the in-memory table operations.

### DDL — `POST/DELETE /api/ddl/*`

Schema management (admin only).

| Method | Path | Description |
|---|---|---|
| `POST` | `/api/ddl/sheets` | Create a new sheet (empty, with a default `ID` column) |
| `DELETE` | `/api/ddl/sheets/{sheet}` | Delete a sheet and all its data |
| `POST` | `/api/ddl/tables` | Define columns on a sheet (effectively creates a table schema) |
| `DELETE` | `/api/ddl/tables/{table}` | Remove all data from a table (keeps the sheet) |

**Create sheet:**
```json
POST /api/ddl/sheets
{"name": "MyNewSheet"}
```

**Define table on a sheet:**
```json
POST /api/ddl/tables
{"sheet": "MyNewSheet", "name": "Projects", "columns": ["ID", "Name", "Budget"]}
```

### OpenAPI / Swagger (debug mode)

When `--debug` is set:

| Endpoint | Description |
|---|---|
| `GET /openapi.json` | Auto-generated OpenAPI 3.0.3 specification |
| `GET /swagger` | Swagger UI interactive documentation |

---

## Authorization

Authorization is implemented as a **role-based** system with three levels. The `--auth` flag (or `RESTXLSX_AUTH`
env var) sets the *fallback* role that applies to all requests. This is the effective authorization until
OIDC/OAuth2 integration is added (see below).

### Role hierarchy

| Role | Read | Write (POST/PUT/DELETE) | DDL (create/drop) |
|---|---|---|---|
| `reader` | ✓ | — | — |
| `writer` (default) | ✓ | ✓ | — |
| `admin` | ✓ | ✓ | ✓ |

### For unauthenticated / open access

To run the service with no authentication barrier (everyone can read and write):

```bash
./restxlsx --auth writer
```

This is **the default** — all callers are treated as `writer`. They can read and mutate data but
cannot modify the schema. If you also need DDL access:

```bash
./restxlsx --auth admin
```

For read-only public access:

```bash
./restxlsx --auth reader
```

### Future: OIDC / OAuth2 integration

The auth architecture (`internal/auth/`) is designed to be upgraded to token-based claims.
The current `--auth` fallback role will become the default for **unauthenticated** requests.

**Planned integration path:**

1. The `auth.Middleware` will first inspect the `Authorization` header for a Bearer JWT.
2. If a valid token is present, the `roles` claim (or a custom claim mapping) is extracted
   and the effective role becomes the token's role.
3. If no token is present (or the token is invalid/expired), the fallback `--auth` role applies.
4. A future `--oidc-issuer`, `--oidc-client-id`, and `--oidc-claim-roles` config will wire
   the validation.

**Middleware structure** (ready for extension):

```
internal/auth/
├── auth.go          # Role type, helpers, RequireWrite/RequireDDL middlewares
├── oidc.go          # (future) OIDC token validation, claim extraction
└── middleware.go     # (future) Combined middleware: try OIDC, fallback to role
```

The `auth.Middleware` currently injects a static role into the request context.
To add OIDC, you would:
1. Parse the `Bearer` token in the middleware
2. Validate against the OIDC issuer's JWKS
3. Map the `roles` claim to a `Role`
4. Store it in context (same `roleKey{}`)
5. All downstream `RequireWrite` / `RequireDDL` checks work unchanged

---

## Data Persistence & Concurrency

The xlsx file on disk is the persistent store. The workflow:

1. On startup, the xlsx file is read into memory
2. All operations read from the in-memory snapshot (fast, no file I/O on reads)
3. Every write operation mutates memory **and** immediately flushes to the xlsx file
4. The file is **not held open** between flushes — other applications (Excel, etc.)
   can freely edit the file while the service is running
5. During a flush, the service merges any new sheets that were added externally,
   so external additions are preserved
6. If the file cannot be opened for writing (e.g., locked by another process),
   the operation fails with `503 Service Unavailable`
7. In-memory data is protected by a `sync.Mutex`, so concurrent HTTP requests
   are serialized at the engine level — safe for concurrent access

> **Note:** Updates to existing rows from an external editor will be overwritten
> by the next flush from this service. For collaborative editing, keep the xlsx
> as the primary source and use this service as a proxy.

---

## Docker

```bash
# Build
docker build -t restxlsx .

# Run (default sample data included)
docker run -p 3000:3000 restxlsx

# With your own xlsx file
docker run -p 3000:3000 -v /path/to/your.xlsx:/data/sample.xlsx restxlsx
```

The Dockerfile uses a multi-stage build resulting in a ~15 MB scratch image.

---

## Development

### Prerequisites
- Go 1.24+
- Python 3.10+ (only needed for sample data generation)

### Regenerate sample data

```bash
python3 -m venv /tmp/venv && /tmp/venv/bin/pip install openpyxl
/tmp/venv/bin/python scripts/gen_data.py
```

### Project layout

```
restxlsx/
├── main.go                     # Entry point, router, graceful shutdown
├── Dockerfile                  # Multi-stage scratch build
├── internal/
│   ├── config/config.go        # CLI flags + env var configuration
│   ├── logger/logger.go        # Structured logging via slog
│   ├── excel/excel.go          # XLSX engine: read, write, flush, DDL
│   ├── auth/auth.go            # Role-based authorization middleware
│   ├── graphql/graphql.go      # Dynamic GraphQL schema & handler
│   └── api/
│       ├── rest.go             # REST CRUD handlers
│       ├── sql.go              # SQL endpoint + minimal SQL parser
│       ├── ddl.go              # DDL handlers (create/drop sheets/tables)
│       ├── openapi.go          # Dynamic OpenAPI spec generation
│       ├── middlewares.go      # JSON content-type middleware
│       └── swagger-ui.html     # Embedded Swagger UI
├── data/sample.xlsx            # Sample workbook (3 tables)
├── scripts/gen_data.py         # Python script to generate sample data
└── tasks.md                    # Architecture decisions & progress log
```

## Building from Source

### Cross-platform builds

The `scripts/build.sh` script cross-compiles binaries for Linux, Windows, and macOS:

```bash
chmod +x scripts/build.sh
./scripts/build.sh
```

Binaries are placed in the `dist/` directory:

| File | Platform |
|------|----------|
| `dist/restxlsx-linux-amd64` | Linux (x86_64) |
| `dist/restxlsx-windows-amd64.exe` | Windows (x86_64) |
| `dist/restxlsx-darwin-amd64` | macOS (Intel) |
| `dist/restxlsx-darwin-arm64` | macOS (Apple Silicon) |

### Manual build

```bash
go build -o restxlsx .
```

### Dependency rationale

| Library | Purpose |
|---|---|
| `github.com/go-chi/chi/v5` | Lightweight, idiomatic HTTP router |
| `github.com/xuri/excelize/v2` | Full xlsx read/write support |
| `github.com/graphql-go/graphql` | Dynamic schema generation from data |