npm package: @sapporta/server
Description: From a Sapporta project directory (containing `sapporta.json`):
Author: Jasim A Basheer
Latest version: 0.2.0

# @sapporta/server

## Usage

From a Sapporta project directory (containing `sapporta.json`):

```bash
sapporta serve          # API server on :3000
sapporta ui             # UI dev server on :5173 (run in a separate terminal)
```

Both commands auto-detect the project by walking up from the current directory to find `sapporta.json`. Use `--sapporta-project-dir <path>` to override.

```bash
sapporta serve --port 4000          # custom API port
sapporta ui --port 8080             # custom UI port
sapporta ui --api-url http://localhost:4000  # point UI at non-default API
```

## Environment variables

| Variable | Purpose |
|---|---|
| `SAPPORTA_DEV_MODE_PACKAGE_ROOT` | Monorepo root. When set, `create-project` uses `file:` links instead of published versions. Set automatically by `bin/sapporta`. |
| `SAPPORTA_PROJECT_DIR` | Resolved project directory. |
| `SAPPORTA_DATA_DIR` | Data directory. Default: `{projectDir}/data`. |
| `SAPPORTA_CODE_DIR` | Code directory. Default: `{projectDir}/code`. |
| `SAPPORTA_API_URL` | Server URL for CLI commands. Default: `http://localhost:3000`. |
| `SAPPORTA_API_TOKEN` | Auth token for CLI commands (future). |
| `SAPPORTA_OUTPUT_FORMAT` | Default CLI output format: `json` or `table`. |
| `PROJECT_ID` | Project identifier fallback for `serve-single`. |
| `PORT` | Server port. Default: `3000`. |
| `LOG_FORMAT` | Set to `json` for structured logging. |
| `LOG_LEVEL` | Log level. Default: `debug`. |

## Sapporta Code Project Layout

A Sapporta code project (created by `sapporta init`) has this structure:

```
<project_root>/
  sapporta.json         Project marker (name, config)
  package.json          Dependencies (installed by sapporta init)
  data/
    sqlite.db           SQLite database
  code/src/
    schema/             Table definitions (Drizzle + Sapporta wrapper)
    actions/            Transactional operations
    reports/            Report definitions
    views/              Custom view definitions
```

`packages/core/src/integration/fixtures/` contains example schema/actions/reports/views files used by integration tests. These are the same files that would go under `code/src/` in a real project.

## CLI Commands

### CLI Architecture

The CLI mirrors the API namespace structure and routes all data commands through the HTTP API server. This ensures a single authorization enforcement point — the CLI is a regular API consumer, not a privileged path.

**Requires a running server** (`pnpm dev` or `sapporta serve`) for all API commands, including project management (project list/add/remove). Local commands (init, check, serve, describe) work without a server.

### CLI Self-Introspection

**The CLI is self-describing.** Use `describe` to discover commands, their HTTP endpoints, and input schemas:

```bash
# List ALL available commands with HTTP method + path
sapporta describe

# Get full input schema (JSON Schema) for any command
sapporta describe "meta sql query"
sapporta describe "tables create"
```

### Global Flags

```bash
--output json          # Structured JSON output (auto-detected when stdout is not a TTY)
--output table         # Human-readable table output (default in terminal)
--json '{...}'         # Pass input as a JSON object (agent-friendly)
```

### Meta Commands (schema introspection, DB inspection, SQL proxy)

```bash
# List all tables with schema metadata and row counts
sapporta meta tables

# Show table structure (columns, types, constraints, foreign keys)
sapporta meta tables show <name>

# Show indexes on a table
sapporta meta tables indexes <name>

# Show sample rows from a table
sapporta meta tables sample <name> --limit 10 --fields name,type

# Update table properties
sapporta meta tables update <name> --data '{"label":"New Label"}'

# Rename a table
sapporta meta tables update <name> --data '{"name":"new_name"}'

# Drop a UI-managed table
sapporta meta tables drop <name> --confirm true

# Run a read-only SQL query
sapporta meta sql query --json '{"sql": "SELECT * FROM accounts", "limit": 50}'

# Run a mutating SQL statement
sapporta meta sql exec --json '{"sql": "DELETE FROM accounts WHERE id = 5"}'

# Sync schema files to database
sapporta meta schema sync
```

### Table Commands (CRUD operations)

```bash
# List rows (with filters, sort, pagination)
sapporta tables list <table> --limit 50 --page 2 --sort name --order asc

# Get a single row by ID
sapporta tables get <table> <id>

# Insert a single row
sapporta tables create <table> --data '{"name":"Cash","type":"asset"}'

# Insert multiple rows (batch)
sapporta tables create <table> --data '[{"name":"Cash"},{"name":"Revenue"}]'

# Insert master + detail records atomically
sapporta tables create orders --data '{"customer":"Alice","$details":{"table":"order_items","fk":"order_id","rows":[{"product":"Widget","quantity":3}]}}'

# Update a row
sapporta tables update <table> <id> --data '{"name":"Updated"}'

# Delete a row
sapporta tables delete <table> <id>
```

### Report Commands

```bash
# List all reports
sapporta reports

# Get report metadata
sapporta reports show <name>

# Execute a report with parameters
sapporta reports run <name> --year 2024 --month 1
```

### Action Commands

```bash
# List all actions
sapporta actions

# Execute an action
sapporta actions run <name> --data '{"field":"value"}'
```

### View Commands

```bash
# List all custom views
sapporta views
```

## Global CLI Installation

The `sapporta` command can be made available system-wide by symlinking `bin/sapporta` into your PATH:

```bash
ln -sf /Users/jasim/m/a/code/sapporta/bin/sapporta /usr/local/bin/sapporta
# or
ln -sf /Users/jasim/m/a/code/sapporta/bin/sapporta ~/bin/sapporta
```

This uses `tsx` to run the CLI entry point directly from the source tree — no build step required.

## Project Context

The CLI auto-detects the project by walking up from `cwd` looking for `sapporta.json` (created by `sapporta init`). Override with `--sapporta-project-dir <path>` when running from outside the project tree.

## API Namespaces (Per-Project)

Routes are organized into five namespaces per project under the prefix `/p/{slug}`:

```
/p/{slug}/meta/...                     System metadata, introspection, admin
/p/{slug}/tables/...                   CRUD operations on table data
/p/{slug}/reports/...                  Report listing and execution
/p/{slug}/actions/...                  Transactional operations
/p/{slug}/views/...                    Custom view metadata
```

Each namespace has a distinct prefix — route ordering no longer matters.

### Key Route Details

- **Schema introspection**: `GET /p/{slug}/meta/tables` — lists all tables with structure + UI metadata
- **Single table schema**: `GET /p/{slug}/meta/tables/{name}` — one table's schema
- **Schema mutations**: `POST/PATCH/DELETE /p/{slug}/meta/tables/...` — create/modify/drop UI-managed tables and columns
- **DB introspection**: `GET /p/{slug}/meta/tables/{name}/indexes`, `.../tables/{name}/sample`
- **SQL proxy**: `POST /p/{slug}/meta/sql/query`, `POST /p/{slug}/meta/sql/exec`
- **Schema sync**: `POST /p/{slug}/meta/schema/sync`
- **CRUD**: `GET/POST /p/{slug}/tables/{table}`, `GET/PUT/DELETE /p/{slug}/tables/{table}/{id}`
- **Lookup**: `GET /p/{slug}/tables/{table}/_lookup`
- **Reports**: `GET /p/{slug}/reports`, `GET /p/{slug}/reports/{name}/results?params`
- **Actions**: `GET /p/{slug}/actions`, `POST /p/{slug}/actions/{name}`
- **Views**: `GET /p/{slug}/views`

## Core Modules

- **Table definition**: `table()` in `src/schema/table.ts` wraps Drizzle `sqliteTable` with Sapporta metadata
- **Schema loading**: `loadSchemas()` dynamically imports all `.ts` files from a schema directory
- **Migrations**: `migrateSchemas()` uses `drizzle-kit/api` `pushSchema()` programmatically
- **Meta API**: `meta-api.ts` is a thin composition layer that mounts schema introspection, DB introspection, schema mutations (from `metadata-api.ts`), SQL proxy, and schema sync
- **Tables API**: `tables-api.ts` — parametric `/:tableName` CRUD routing with runtime table registration
- **Actions API**: `action-api.ts` — single parametric `/:name` route with Map lookup
- **Enums**: SQLite has no native enum type. Use `text()` columns with `meta.selects` for dropdown/validation support.
- **Imports**: `@sapporta/server/table`, `@sapporta/server/runtime`, `@sapporta/server/view`, etc. (via package.json exports)

## Custom Views — Backend

Views are React components defined as `.tsx` files in the project's `views/` directory. Each file exports a `meta` object (for sidebar/routing) and a default React component (the screen itself).

```tsx
// views/dashboard.tsx
export const meta = {
  name: "dashboard",
  label: "Dashboard",
  icon: "layout-dashboard",  // Lucide icon name, optional
};

export default function DashboardView() {
  return <div className="p-6">...</div>;
}
```

**Discovery:** The backend reads only `meta` for the API; the component runs in the browser.

**API endpoints:**
- `GET /views` — list views with metadata
- `GET /views/:name` — view metadata

**Action labels:** Actions can have an optional `label` property for display in the UI:
```ts
action({ name: "log_meal", label: "Log Meal", input: z.object({...}), run: ... })
```