@sapporta/server

@sapporta/server

Usage

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

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.

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:

# 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

--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)

# 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)

# 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

# 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

# List all actions
sapporta actions

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

View Commands

# 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:

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).

// 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:

action({ name: "log_meal", label: "Log Meal", input: z.object({...}), run: ... })