timeplus-app-builder

SKILL.md

---
name: timeplus-app-builder
description: Use when creating, packaging, or installing a Timeplus app (.tpapp) — converting existing SQL resources and dashboards into an installable app package, writing manifests, applying template variables, building dashboard JSON, or debugging install failures.
license: Apache-2.0
compatibility: >
  Requires curl and zip. Installs against a Timeplus (neutron) server's app
  install API (default http://localhost:8000/<tenant>/api/v1beta2/apps/install).
metadata:
  author: timeplus-io
  version: "2.0.0"
  docs: https://docs.timeplus.com
  github: https://github.com/timeplus-io/proton
  tags:
    - timeplus
    - tpapp
    - app-packaging
    - manifest
    - dashboard
    - ddl
---

# Timeplus App Builder

## Overview

A Timeplus app (`.tpapp`) is a zip archive that bundles a streaming data pipeline — DDL resources (streams, views, materialized views) plus dashboards — into a single installable unit. The installer provisions everything in order and rolls back on failure.

## Quick Reference

| Task | Reference |
|------|-----------|
| Dashboard panel spec — all chart types, `viz_config`, controls, position grid | `references/dashboard-spec.md` |

## Directory Structure

```
my-app/
├── manifest.yaml          # required
├── ddl/
│   ├── 001_first.sql      # executed in filename order
│   ├── 002_second.sql
│   └── ...
└── dashboards/
    └── main.json          # array of panel objects
```

Package it:
```bash
cd my-app && zip -r ../my-app.tpapp manifest.yaml ddl/ dashboards/
```

Install via API:
```bash
curl -X POST http://localhost:8000/default/api/v1beta2/apps/install \
  -F "file=@my-app.tpapp"
```

Override `config:` values at install time with `config[<key>]=<value>` form fields (multipart) — the neutron handler parses any form field matching `config[*]` into the rendered config map:

```bash
curl -X POST http://localhost:8000/default/api/v1beta2/apps/install \
  -F "file=@my-app.tpapp" \
  -F "config[strategy]=sign" \
  -F "config[num_stocks]=5"
```

For JSON-body installs (URL fetch), use `{"url": "...", "config": {"strategy": "sign"}}`.

## manifest.yaml

```yaml
package_format_version: 1          # must be 1
id: io.example.my-app              # reverse-domain, unique
name: My App
version: 1.0.0
author: Acme
description: What this app does.
icon: "data:image/png;base64,..."  # optional — base64 data URI; frontend shows default when absent
categories:                        # optional — free-form tags for discovery/filtering
  - security
  - observability
db_name: my_app                    # ^[a-z][a-z0-9_]{0,31}$, used as-is

config:                            # optional — user-supplied parameters
  - key: websocket_url
    type: string
    required: true
    description: WebSocket feed URL
  - key: api_key
    type: string
    required: true
    secret: true                   # mask value in UI; stored as IsSecret
    description: API key
  - key: timeout
    type: integer
    required: false
    default: "30"
    description: Connection timeout in seconds
  - key: tls_enabled
    type: bool
    required: false
    default: "false"
    description: Enable TLS
  - key: topics
    type: list
    required: true
    description: Kafka topics (JSON array of strings, e.g. '["a","b"]')
  - key: broker_type
    type: choice
    required: true
    description: Message broker
    options:
      - kafka
      - pulsar
      - redpanda
  - key: features
    type: multi_choice
    required: false
    default: '["metrics"]'
    description: Features to enable
    options:
      - metrics
      - tracing
      - alerting

python_packages:                   # optional — installed before any DDL runs
  - json5>=0.9.6
  - websocket-client>=1.4.0

resources:                         # executed in listed order
  - file: ddl/001_source.sql
    type: external_stream
    name: raw_feed
  - file: ddl/002_events.sql
    type: stream
    name: events
  - file: ddl/003_mv.sql
    type: materialized_view
    name: mv_events

dashboards:
  - file: dashboards/main.json
    name: My Dashboard
    description: Real-time view
```

### Resource types

| type | DDL verb | idempotent form | rolled back with |
|---|---|---|---|
| `stream` | `CREATE STREAM` | `CREATE STREAM IF NOT EXISTS` | `DROP STREAM` |
| `external_stream` | `CREATE EXTERNAL STREAM` | `CREATE EXTERNAL STREAM IF NOT EXISTS` | `DROP STREAM` |
| `mutable_stream` | `CREATE MUTABLE STREAM` | `CREATE MUTABLE STREAM IF NOT EXISTS` | `DROP STREAM` |
| `materialized_view` | `CREATE MATERIALIZED VIEW` | `CREATE MATERIALIZED VIEW IF NOT EXISTS` | `DROP VIEW` |
| `view` | `CREATE VIEW` | `CREATE VIEW IF NOT EXISTS` | `DROP VIEW` |
| `external_table` | `CREATE TABLE` | `CREATE TABLE IF NOT EXISTS` | `DROP TABLE` |
| `udf` | `CREATE FUNCTION` | `CREATE OR REPLACE FUNCTION` (see below) | `DROP FUNCTION` |
| `task` | `CREATE TASK` | `CREATE TASK IF NOT EXISTS` | `DROP TASK` |
| `alert` | `CREATE ALERT` | `CREATE ALERT IF NOT EXISTS` | `DROP ALERT` |
| `input` | `CREATE INPUT` | `CREATE INPUT IF NOT EXISTS` | `DROP INPUT` |
| `dictionary` | `CREATE DICTIONARY` | `CREATE DICTIONARY IF NOT EXISTS` | `DROP DICTIONARY` |
| `format_schema` | `CREATE FORMAT SCHEMA` | `CREATE FORMAT SCHEMA IF NOT EXISTS` | `DROP FORMAT SCHEMA` |
| `named_collection` | `CREATE NAMED COLLECTION` | `CREATE NAMED COLLECTION IF NOT EXISTS` | `DROP NAMED COLLECTION` |

## DDL Template Variables

DDL files are rendered with Go `text/template` using `{{ }}` delimiters.

| Expression | Expands to |
|---|---|
| `{{ .DB }}` | The resolved database name (value of `db_name`) |
| `{{ .Config.key_name }}` | Value of config key (after defaults applied) |

**Always use dot notation for config values** — `{{ .Config.my_key }}`, never `{{ index .Config "my_key" }}`.

```sql
-- ddl/002_events.sql
CREATE STREAM IF NOT EXISTS {{ .DB }}.events (
  id       string,
  payload  string
)
TTL to_datetime(_tp_time) + INTERVAL 24 HOUR;
```

```sql
-- ddl/001_source.sql
CREATE EXTERNAL STREAM IF NOT EXISTS {{ .DB }}.raw_feed (msg string)
SETTINGS url='{{ .Config.websocket_url }}', type='websocket';
```

## Idempotency: every `CREATE` must be re-runnable

App upgrades re-run every DDL file against the existing database — the installer does **not** drop resources first. A `CREATE` that fails on the second run breaks upgrade. Every DDL file in `apps/*/ddl/` must use one of the following forms:

| Resource | Required form | Why |
|---|---|---|
| Everything except `udf` | `CREATE … IF NOT EXISTS <name>` | Re-running is a no-op. Existing rows, downstream consumers, and the resource UUID are preserved — critical for `view` and `materialized_view`, which are referenced by name from other resources and from dashboards. |
| `udf` | `CREATE OR REPLACE FUNCTION <name>` | UDFs are global (no database qualifier — see [udf](#udf)) and their body is typically what changes in an upgrade. `OR REPLACE` hot-swaps the implementation; `IF NOT EXISTS` would silently keep the old code on upgrade. |

**Do not use `CREATE OR REPLACE VIEW`.** A view is a dependency for downstream MVs, queries, and dashboards. `OR REPLACE` would drop and recreate it, breaking anything referencing the view during the brief recreation window and on schema changes. Use `CREATE VIEW IF NOT EXISTS` — if the view definition needs to change, bump the app version and treat it as a migration (drop + recreate explicitly, or version the view name).

**Forms that do NOT accept `IF NOT EXISTS`:**

- `SYSTEM INSTALL PYTHON PACKAGE` — parser rejects `IF NOT EXISTS`. Don't put this in DDL; declare packages in `manifest.yaml` under `python_packages` (see [Python packages](#python-packages-not-available-at-ddl-time)).
- `CREATE FUNCTION IF NOT EXISTS db.fn` and `CREATE FORMAT SCHEMA IF NOT EXISTS db.fs` — both reject the `db.` prefix. UDFs and format schemas live in a global namespace; never qualify them with `{{ .DB }}.`.

## Dashboard Template Variables

Dashboard JSON is rendered with `[[ ]]` delimiters (to avoid collision with the frontend's `{{filter_*}}` runtime variables).

```json
{
  "viz_content": "SELECT * FROM [[ .DB ]].events WHERE _tp_time > now() - {{filter_time_range}}"
}
```

| Expression | Expands to |
|---|---|
| `[[ .DB ]]` | Database name |
| `[[ .Config.key ]]` | Config value |
| `{{filter_*}}` | Left as-is — resolved by the frontend at query time |

**Template processing runs before JSON parsing.** This means template expressions inside JSON string values may contain unescaped `"` characters — the file does not need to be valid JSON before substitution.

## Template Functions (Sprig)

Both DDL (`{{ }}`) and dashboard (`[[ ]]`) templates have the full [Sprig](https://masterminds.github.io/sprig/) function library available — the same library used by Helm. Use these to manipulate config values at install time.

### Working with `list` config values

Config keys of type `list` are stored as a JSON array string (e.g. `["BTC-USD","ETH-USD","SOL-USD"]`). Use `fromJson` to parse them before passing to other functions.

**Render as comma-separated string** (e.g. for dashboard selector `inlineValues`):
```json
"inlineValues": "[[ join "," (fromJson .Config.product_ids) ]]"
```
→ `"inlineValues": "BTC-USD,ETH-USD,SOL-USD"`

**Embed directly as JSON array** (e.g. in a DDL Python string):
```sql
product_ids = '{{ .Config.product_ids }}'
```
→ `product_ids = '["BTC-USD","ETH-USD","SOL-USD"]'`

**Get the first element** (e.g. for a selector `defaultValue`):
```json
"defaultValue": "[[ index (fromJson .Config.product_ids) 0 ]]"
```
→ `"defaultValue": "BTC-USD"`

### Commonly used functions

| Function | Example | Result |
|---|---|---|
| `join sep list` | `join "," (fromJson .Config.topics)` | `a,b,c` |
| `fromJson s` | `fromJson .Config.product_ids` | parsed slice |
| `default val s` | `default "30" .Config.timeout` | config value or fallback |
| `upper s` | `upper .Config.env` | `PRODUCTION` |
| `lower s` | `lower .Config.env` | `production` |
| `trim s` | `trim .Config.url` | strips whitespace |
| `replace old new s` | `replace "-" "_" .Config.id` | `BTC_USD` |
| `splitList sep s` | `splitList "," .Config.tags` | `["a","b","c"]` |
| `first list` | `first (fromJson .Config.ids)` | first element |
| `last list` | `last (fromJson .Config.ids)` | last element |
| `len list` | `len (fromJson .Config.ids)` | count |

Full function reference: https://masterminds.github.io/sprig/

## Dashboard JSON Reference

For the full dashboard panel specification — all chart types, `viz_config` fields, control panels, position grid, update modes, and working examples — see:

**`references/dashboard-spec.md`**

This covers:
- Panel structure (`id`, `title`, `position`, `viz_type`, `viz_content`, `viz_config`)
- 12-column position grid and common width/height values
- Template variables (`[[ .DB ]]` vs `{{filter_*}}`)
- Control panels: `selector` (dropdown) and `text_input`
- Chart types: `line`, `area`, `bar`, `column`, `singleValue`, `table`, `ohlc`, `geo`, `md`, `grammar` (3.2+ — generic Vistral-grammar-driven viz: scatter, layered marks, band-axis bars, stacked area, custom transforms, etc.). For the underlying `VistralSpec` grammar (marks, transforms, scales, encode channels), see the Vistral skill: [`vistral/agentskill/SKILL.md`](https://github.com/timeplus-io/vistral/blob/main/agentskill/SKILL.md).
- All `viz_config.config` fields per chart type with defaults
- `updateMode` (`"all"` / `"key"` / `"time"`) — when to use each
- Default color palette
- Common mistakes

## Timeplus SQL Reference

For writing correct Timeplus streaming SQL in DDL files, refer to the Timeplus SQL skill:
https://github.com/timeplus-io/AgentSkills/tree/main/timeplus-sql-guide

This covers streaming query syntax, window functions, tumble/hop aggregations, `_tp_time` semantics, and other Timeplus-specific SQL features used in streams, views, and materialized views.

## File Ordering and Dependencies

Name DDL files with a numeric prefix so they execute in dependency order:

```
001_source_stream.sql            ← external streams / sources
002_target_stream.sql            ← destination streams
003_mv_extract.sql               ← materialized views (depend on streams)
004_v_aggregated.sql             ← views (depend on streams/MVs)
```

## Config Types

Seven types are supported. Omitting `type` defaults to `string`.

| Type | Stored as | Valid example | Notes |
|------|-----------|---------------|-------|
| `string` | plain string | `"localhost:9092"` | Default type |
| `integer` | decimal string | `"30"`, `"-5"` | Must be a whole number |
| `float` | decimal string | `"3.14"`, `"30"` | Decimal or whole |
| `bool` | `"true"` or `"false"` | `"true"` | No other values accepted |
| `list` | JSON array of strings | `["a","b"]` | Comma-separated strings NOT accepted |
| `choice` | string matching one option | `"kafka"` | Requires `options:` list |
| `multi_choice` | JSON array of strings, each matching an option | `["kafka","pulsar"]` | Requires `options:` list |

**`options`** — required for `choice` and `multi_choice`; lists the allowed values:

```yaml
  - key: broker_type
    type: choice
    required: true
    options:
      - kafka
      - pulsar
```

**`secret`** — only valid on `string` type; marks the value as sensitive (masked in UI, stored with `IsSecret: true`):

```yaml
  - key: api_key
    type: string
    required: true
    secret: true
    description: API secret key
```

**`default`** — always a string regardless of type, and must be a valid encoding for the declared type:

```yaml
  - key: timeout
    type: integer
    default: "30"         # valid — "30" is a legal integer encoding
  - key: features
    type: multi_choice
    default: '["metrics"]'
    options: [metrics, tracing]
```

## Icon

The `icon` field in `manifest.yaml` sets the app's icon in the UI. It is optional — when absent, the frontend displays a default icon.

**Format:** base64 data URI with an image MIME type.

```yaml
icon: "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg=="
```

Valid MIME types: `image/png`, `image/jpeg`, `image/svg+xml`, `image/gif`, etc.

**Rules enforced by the installer:**
- Must start with `data:image/`
- Must contain `;base64,` followed by a non-empty payload
- An invalid icon causes the install to fail (same as a malformed manifest field)

**Generating a data URI:**
```bash
# PNG file → data URI
echo "data:image/png;base64,$(base64 -i icon.png | tr -d '\n')"

# SVG file → data URI
echo "data:image/svg+xml;base64,$(base64 -i icon.svg | tr -d '\n')"
```

### Designing icons that match the Timeplus UI

The Timeplus frontend (`AppCard.tsx`) renders app icons as rounded squares. When no icon is provided it shows a default: a white outline box on a `#D53F8C → #9F2BC0` diagonal gradient. Custom icons should be consistent with this style.

**SVG is the best format** — small, scalable, no raster artifacts.

**Canonical icon template (48×48 viewBox):**

```svg
<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 48 48">
  <defs>
    <linearGradient id="bg" x1="0" y1="0" x2="48" y2="48" gradientUnits="userSpaceOnUse">
      <stop offset="0%" stop-color="#D53F8C"/>
      <stop offset="100%" stop-color="#9F2BC0"/>
    </linearGradient>
  </defs>
  <rect width="48" height="48" rx="11" fill="url(#bg)"/>
  <!-- white stroke icon centered in the ~12–36 x/y region -->
</svg>
```

**Icon style rules:**
- Background: rounded square `rx="11"` with the pink→purple gradient (`#D53F8C` → `#9F2BC0`), matching the default icon's `bg-gradient-to-br from-[#D53F8C] to-[#9F2BC0]`
- Icon symbol: white, flat, thin-stroke outline (`stroke="white" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round" fill="none"`)
- Icon occupies roughly the inner 24×24 area (coordinates 12–36 on the 48×48 canvas)
- Use `fill="white"` for solid accent shapes (e.g. a lightning bolt inside a shield)

**Example icons for reference apps:**

| App | Symbol | SVG elements |
|-----|--------|--------------|
| Crypto market data | 3-candle OHLC chart | `<line>` wicks + `<rect>` bodies (center candle filled) |
| GitHub activity | `</>` code brackets | Two `<path>` chevrons + a diagonal slash `<line>` |
| Complex event processing | 3 nodes in a triangle | Three `<circle>` + connecting `<line>`/`<path>` |
| News feed | Newspaper | `<rect>` border + `<line>` rows |
| Trading / P&L | Trending-up chart | `<polyline>` with arrowhead |
| Security / DDoS | Shield + lightning bolt | Shield `<path>` (stroke) + bolt `<path>` (fill white) |
| Game analytics | Gamepad | Body `<path>` + D-pad `<line>` cross + button `<circle>` |

**Generating the data URI from an inline SVG string (Python):**

```python
import base64

svg = '<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 48 48">...</svg>'
b64 = base64.b64encode(svg.encode()).decode()
data_uri = f'data:image/svg+xml;base64,{b64}'
# paste into manifest.yaml as:  icon: ""
```

## Categories

The `categories` field in `manifest.yaml` assigns free-form tags to the app for discovery and filtering in the UI. It is optional — when absent the app has no categories.

**Format:** a YAML list of strings. Values are unrestricted.

```yaml
categories:
  - security
  - observability
```

Categories are surfaced in:
- `GET /v1beta2/apps` — each `AppInstance` includes a `categories` array
- `GET /v1beta2/apps/available` — each `CatalogEntry` includes a `categories` array

An app can belong to any number of categories. An empty or absent field is omitted from the JSON response (`omitempty`).

## Config Defaults

Declared `default` values are applied automatically before template rendering. Users only need to supply required keys or keys they want to override.

```yaml
config:
  - key: retention_hours
    type: integer
    required: false
    default: "24"
    description: Stream retention in hours
```

```sql
TTL to_datetime(_tp_time) + INTERVAL {{ .Config.retention_hours }} HOUR
```

## Hiding Secrets in Python External Streams

Marking a config key `secret: true` only masks it **in the install UI**. Once the value is rendered into a DDL file via `{{ .Config.<key> }}`, it is stored verbatim in the resource definition — anyone with `SHOW CREATE EXTERNAL STREAM` privilege then sees it in cleartext. This matters most for Python external streams, where the `$$ ... $$` body is the natural place to put credentials but is also the most exposed surface.

**Pattern:** keep the secret out of the Python body by putting it in a `named_collection`, then have Proton inject it into the stream's `init_function_parameters` setting at runtime. A small `_tp_init()` hook parses the JSON and stashes the values in module globals that the read function reads.

### 1. Declare the named collection (a DDL resource)

```sql
-- ddl/000_creds_nc.sql
CREATE NAMED COLLECTION IF NOT EXISTS aws_cost_creds AS
  init_function_parameters = '{"access_key_id":{{ .Config.aws_access_key_id | quote }},"secret_access_key":{{ .Config.aws_secret_access_key | quote }}}'
  NOT OVERRIDABLE;
```

- **`{{ ... | quote }}`** is the sprig `quote` function — it wraps the value in double quotes and escapes any internal `"` or `\`. Use it for every interpolated secret to keep the resulting blob valid JSON regardless of the raw value.
- **`NOT OVERRIDABLE`** prevents a caller from passing a different value at query time.
- **Named collections are global** — they live outside any database. Pick a name that includes your app's `db_name` (e.g. `aws_cost_creds`) so two apps on the same cluster don't collide. **Do not template it with `{{ .DB }}`** — the manifest's `name:` field is not template-rendered, so the literal SQL identifier must match the literal manifest `name:`. (This is the same convention as UDFs.)

Manifest entry — must be ordered **before** any stream that references it. The `name:` must match the literal SQL identifier exactly:

```yaml
resources:
  - file: ddl/000_creds_nc.sql
    type: named_collection
    name: aws_cost_creds
  - file: ddl/001_poller.sql
    type: external_stream
    name: poller
```

### 2. Reference the collection from the Python external stream

```sql
-- ddl/001_poller.sql
CREATE EXTERNAL STREAM IF NOT EXISTS {{ .DB }}.poller (...)
AS $$
import json

# Populated by _tp_init() at session start. Leaving the literals empty here
# is what keeps secrets out of SHOW CREATE EXTERNAL STREAM.
AWS_ACCESS_KEY_ID = ""
AWS_SECRET_ACCESS_KEY = ""

def _tp_init(params):
    global AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY
    cfg = json.loads(params)
    AWS_ACCESS_KEY_ID = cfg["access_key_id"]
    AWS_SECRET_ACCESS_KEY = cfg["secret_access_key"]

def poll():
    # AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY are available here
    ...
$$
SETTINGS type='python', mode='streaming', read_function_name='poll',
         init_function_name='_tp_init', named_collection='aws_cost_creds';
```

### How it works

1. The installer creates the named collection with the JSON blob. The collection lives outside the stream definition.
2. At storage construction time, Proton's `updateSettingsByNamedCollection` merges the collection's `init_function_parameters` into the in-memory `ExternalStreamSettings`, but `SHOW CREATE EXTERNAL STREAM` re-serializes the **original** parsed AST — so all it shows is `SETTINGS … named_collection='<db>_creds'`. The keys never appear.
3. When a query starts a read session, Proton calls `_tp_init(params)` once with the JSON string from the collection. The init function shares the module's global namespace with the read function, so the read function picks up the credentials.

### Caveats

- **`system.named_collections` still exposes the blob.** Reading the `collection` map or `create_query` column from `system.named_collections` returns the raw JSON, since Proton only auto-masks the literal key `password`. Restrict that privilege to operators. Selecting just the `name` column is safe and works for discovery (`SELECT name FROM system.named_collections`).
- **`init_function_parameters` is one string.** Pack multiple secrets as JSON (as above). For a single value, a plain string is fine.
- **`init_function_name` and `init_function_parameters` must both be set** — Proton throws `INVALID_SETTING_VALUE` if you set parameters without a function name.
- **Don't put non-secret config in the collection.** Keep things like region lists, intervals, and toggles as ordinary `{{ .Config.* }}` template substitutions — they are easier to read in the DDL and contribute nothing to the SHOW CREATE risk.
- **The init hook runs per session, not once globally.** It's cheap (one JSON parse), but if you do anything expensive there, scope it behind a `if not _ready: …` guard.
- **Named-collection values are snapshotted at stream-create time.** `ALTER NAMED COLLECTION` updates `system.named_collections`, but an *existing* external stream keeps using the value it captured at `CREATE EXTERNAL STREAM` time — proton calls `updateSettingsByNamedCollection` only during storage construction. `ALTER STREAM … MODIFY SETTING` on an external stream is accepted by the engine, but the Python body (the `$$ … $$` script) is bound to the storage via `exec_script` at create time, *not* via `SETTINGS`, so no `ALTER … MODIFY SETTING` can rewrite it. To rotate credentials or change the body you must `DROP STREAM` and re-create. Plan for that in your install/upgrade flow.

## Common Mistakes

### `CREATE` without `IF NOT EXISTS` (breaks upgrade)
**Cause:** App upgrades re-run every DDL file. A `CREATE STREAM …` (no guard) succeeds on first install and fails on every upgrade with `Code: 57 ... already exists`.
**Fix:** Use `CREATE … IF NOT EXISTS` on every resource type except `udf`. For UDFs, use `CREATE OR REPLACE FUNCTION` (the body is what typically changes on upgrade). For views specifically: **never use `CREATE OR REPLACE VIEW`** — drop+recreate breaks dependents (downstream MVs, dashboards, other views). See [Idempotency](#idempotency-every-create-must-be-re-runnable).

### Multi-statement SQL files
**Error:** `Syntax error: Multi-statements are not allowed`  
**Fix:** One SQL statement per file.

### Reserved column names
**Error:** `Column window_start is reserved`  
**Fix:** `window_start` (and `window_end`) are generated by tumble/hop. Name stream columns `time` or `ts` instead, and alias in the MV:
```sql
-- stream column: `time`
-- MV select:
SELECT window_start AS time, product_id, ...
```

### Python packages not available at DDL time
**Fix:** Declare packages in `python_packages` in the manifest — the installer installs them and waits for completion before running any DDL. Do not use `SYSTEM INSTALL PYTHON PACKAGE` as a DDL resource; it is not needed and has no rollback.

### Wrong template delimiter in dashboards
**Fix:** Use `[[ .DB ]]` in dashboard JSON, not `{{ .DB }}`. The `{{ }}` delimiter is reserved for frontend filter variables like `{{filter_product}}`.

### `chartType: "grammar"` temporal sliding window appears empty / spans years
**Cause:** `temporalRange` is in **minutes**, not milliseconds. Setting it to `60000` (intending 1 minute) actually gives ~41 days.
**Fix:** Use minute values directly: `"temporalRange": 1` for a 60-second window, `"temporalRange": 5` for 5 minutes. See `references/dashboard-spec.md` → "grammar".

### `chartType: "grammar"` advancedSpec marks override drops the form's encode
**Cause:** The advancedSpec JSON is deep-merged via `mergeDeepRight`, which **replaces arrays atomically**. Supplying `marks` in advancedSpec discards the entire form-built mark (and its encode).
**Fix:** When using `advancedSpec` to layer marks (e.g. line + point), declare every mark explicitly in the JSON — don't expect the form's mark to be preserved underneath. See the "Layered line + points" example in the grammar reference.

### `chartType: "grammar"` legend won't hide with `legendPosition: "none"`
**Cause:** The shared Selector engine reserves the literal value `'none'` for its placeholder item and coerces it to `''`.
**Fix:** Use `"legendPosition": "hidden"` to suppress the legend.

### Multi-series line/area chart renders as a single overlapping line
**Cause:** `viz_config.config.color` left at `""`. The chart treats the result as one series and draws every point on the same line.
**Fix:** Set `"color"` to the column that distinguishes series (e.g. `"color": "stock_id"` for `SELECT time, stock_id, close FROM ...`). The reference doc lists this as the required key for multi-series; see `references/dashboard-spec.md` → "line and area".

### Dashboard WHERE filter fails with `Missing columns: '_tp_time'`
**Cause:** Views that alias `window_start AS time` (typical for tumble bars) do not propagate `_tp_time` to consumers.
**Fix:** Filter on the exposed `time` column instead — `WHERE time > now() - 5m`.

### Dashboard / resource name silently truncated at `#`
**Cause:** YAML treats `#` after whitespace as the start of a comment. `name: Alpha #1 Backtest` is parsed as `name: Alpha`.
**Fix:** Quote any manifest value that contains `#` — `name: "Alpha #1 Backtest"`, `description: "Live prices and Alpha #1 leaderboard"`. Folded block scalars (`description: > ...`) treat `#` literally and are safe.

### `Unknown function nullif. Maybe you meant: ['null_if','null_in']`
**Cause:** Timeplus uses snake_case for ClickHouse-derived functions (`array_element`, `count_if`, `null_if`, …). The bare ClickHouse name `nullif` *appears* to work in ad-hoc HTTP `SELECT nullif(...)` queries but is rejected by both the `.tpapp` install validator and the dashboard panel query path.
**Fix:** Write `null_if(x, y)` in every SQL string — DDL files, dashboard `viz_content`, README snippets. Don't trust a green ad-hoc `curl` test; live-validate via the install path or the dashboard render if the query will live there.

### Sprig template in dashboard JSON gets replaced by its rendered value after a UI edit
**Cause:** The Timeplus dashboard UI loads dashboards in their *resolved* form (e.g. `inlineValues: "STOCK_0,STOCK_1,STOCK_2"`), and when the user saves a layout tweak the UI writes back the resolved string — overwriting the source template (e.g. `inlineValues: "[[ range $i, $_ := until (int .Config.num_stocks) ]]…[[ end ]]"`). Auto-scaling behavior tied to `.Config.*` then silently breaks on next reinstall with a different config value.
**Fix:** After any user-side UI edit to a dashboard, diff the file against HEAD before committing. If any `[[ ]]` Sprig expressions disappeared, restore them. Other formatting changes (sorted keys, per-line objects, layout `x`/`y` tweaks) are usually fine to keep.

## Resource Type Reference

### stream

A `stream` is the core storage primitive — an append-only event log with optional TTL. Use it as the destination for materialized views or external stream ingestion.

```sql
-- ddl/002_events.sql
CREATE STREAM IF NOT EXISTS {{ .DB }}.events (
  id        string,
  product   string,
  price     float64,
  _tp_time  datetime64(3) DEFAULT now64(3)
)
TTL to_datetime(_tp_time) + INTERVAL 24 HOUR;
```

Manifest entry:
```yaml
  - file: ddl/002_events.sql
    type: stream
    name: events
```

### external_stream

An `external_stream` connects to an outside data source (Kafka, WebSocket, Pulsar, etc.) without storing data locally. Queries read directly from the external system.

```sql
-- ddl/001_source.sql
CREATE EXTERNAL STREAM IF NOT EXISTS {{ .DB }}.raw_feed (msg string)
SETTINGS
  type='websocket',
  url='{{ .Config.websocket_url }}';
```

Manifest entry:
```yaml
  - file: ddl/001_source.sql
    type: external_stream
    name: raw_feed
```

Common `type` values: `kafka`, `websocket`, `pulsar`, `redpanda`, `confluent`.

### mutable_stream

A `mutable_stream` is like a stream but supports upserts — rows with the same primary key overwrite each other. Use it for keyed state (e.g., latest price per symbol).

```sql
-- ddl/005_ohlc.sql
CREATE MUTABLE STREAM IF NOT EXISTS {{ .DB }}.ohlc_1m (
  time      datetime64(3),
  symbol    string,
  open      float64,
  high      float64,
  low       float64,
  close     float64,
  PRIMARY KEY (time, symbol)
);
```

Manifest entry:
```yaml
  - file: ddl/005_ohlc.sql
    type: mutable_stream
    name: ohlc_1m
```

### materialized_view

A `materialized_view` continuously reads from a source stream, transforms the data, and writes results into a target stream. It runs as a persistent background query.

```sql
-- ddl/003_mv_parse.sql
CREATE MATERIALIZED VIEW IF NOT EXISTS {{ .DB }}.mv_parse
INTO {{ .DB }}.events
AS SELECT
  json_value(msg, '$.id')    AS id,
  json_value(msg, '$.price') AS price,
  now64(3)                   AS _tp_time
FROM {{ .DB }}.raw_feed;
```

Manifest entry:
```yaml
  - file: ddl/003_mv_parse.sql
    type: materialized_view
    name: mv_parse
```

The target stream (`INTO`) must be declared before the MV in the manifest.

### view

A `view` is a saved streaming query with no storage of its own. Every query against the view re-executes the underlying SELECT in real time.

```sql
-- ddl/004_v_btc.sql
CREATE VIEW IF NOT EXISTS {{ .DB }}.v_btc
AS SELECT * FROM {{ .DB }}.events WHERE product = 'BTC-USD';
```

**Never use `CREATE OR REPLACE VIEW`.** A view is a stable dependency for downstream MVs, dashboards, and other views. `OR REPLACE` drops and recreates the view, breaking dependents — use `IF NOT EXISTS` and treat definition changes as explicit migrations (bump app version, drop and recreate, or version the view name).

Manifest entry:
```yaml
  - file: ddl/004_v_btc.sql
    type: view
    name: v_btc
```

### external_table

An `external_table` maps to external storage (S3, ClickHouse, etc.) for historical (batch) queries. Unlike external streams, it is not suited for real-time streaming reads.

```sql
-- ddl/006_s3_archive.sql
CREATE TABLE IF NOT EXISTS {{ .DB }}.s3_archive (
  event_time datetime,
  payload    string
) SETTINGS
  type='s3',
  url='{{ .Config.s3_url }}',
  format='JSONEachRow';
```

Manifest entry:
```yaml
  - file: ddl/006_s3_archive.sql
    type: external_table
    name: s3_archive
```

### udf

A `udf` registers a Python function for use in SQL queries. The function body is embedded directly in the DDL.

**UDFs are global** — they do not belong to a database. Never prefix the function name with `{{ .DB }}.` in `CREATE FUNCTION` or `CALL`; doing so causes a syntax error (`failed at position N ('.')`).

```sql
-- ddl/007_notify_slack.sql
CREATE OR REPLACE FUNCTION notify_slack(channel string, message string)
RETURNS bool
LANGUAGE PYTHON AS $$
import requests
def notify_slack(channel, message):
    url = '{{ .Config.slack_webhook_url }}'
    requests.post(url, json={'channel': channel, 'text': message})
    return [True] * len(channel)
$$;
```

Manifest entry:
```yaml
  - file: ddl/007_notify_slack.sql
    type: udf
    name: notify_slack
```

## Tasks, Alerts, and Inputs

### Task

A `task` runs a historical (batch) query on a schedule and writes results to a target stream. It complements materialized views for periodic aggregations or snapshots.

```sql
-- ddl/010_hourly_summary.sql
CREATE TASK IF NOT EXISTS {{ .DB }}.hourly_summary
SCHEDULE INTERVAL 1 HOUR
TIMEOUT INTERVAL 5 MINUTE
INTO {{ .DB }}.summary_stream
AS SELECT product_id, avg(price) AS avg_price, count() AS trades
   FROM {{ .DB }}.coinbase_tickers
   WHERE _tp_time > now() - INTERVAL 1 HOUR;
```

Manifest entry:
```yaml
  - file: ddl/010_hourly_summary.sql
    type: task
    name: hourly_summary
```

Key clauses:
- `SCHEDULE INTERVAL <n> <unit>` — how often to run; next run begins only after previous completes
- `TIMEOUT INTERVAL <n> <unit>` — aborts the run if it exceeds this duration
- `INTO <target_stream>` — destination stream for results

### Alert

An `alert` monitors a streaming query and calls a Python UDF when the condition is met. Use it to send notifications (Slack, email, webhook) or trigger external actions.

```sql
-- ddl/011_price_alert.sql
CREATE ALERT IF NOT EXISTS {{ .DB }}.price_spike_alert
BATCH 10 EVENTS WITH TIMEOUT 5s
LIMIT 1 ALERTS PER 10s
CALL {{ .DB }}.notify_slack
AS SELECT product_id, price, _tp_time
   FROM {{ .DB }}.coinbase_tickers
   WHERE price > {{ .Config.alert_threshold }};
```

Manifest entry:
```yaml
  - file: ddl/011_price_alert.sql
    type: alert
    name: price_spike_alert
```

Key clauses:
- `BATCH <N> EVENTS WITH TIMEOUT <interval>` — fires the UDF after N events accumulate or the timeout elapses, whichever comes first
- `LIMIT <M> ALERTS PER <interval>` — rate-limits to prevent alert storms
- `CALL <python_udf>` — the Python UDF to invoke; its signature must match the SELECT projection
- The query must be a simple SELECT — no joins or aggregations (use a materialized view upstream for complex logic)
- Only Python UDFs are supported

### Input

An `input` starts a long-running server (TCP, UDP, HTTP, or gRPC) that accepts data pushed by external clients and writes it to a target stream. Supported protocols: `splunk-s2s`, `splunk-hec`, `datadog`, `elastic`, `otel`, `netflow`, `syslog`.

```sql
-- ddl/001_syslog_input.sql
CREATE INPUT IF NOT EXISTS {{ .DB }}.syslog_in
SETTINGS
  type='syslog',
  target_stream='{{ .DB }}.raw_logs',
  tcp_port={{ .Config.syslog_port }},
  listen_host='0.0.0.0'
COMMENT 'Syslog receiver';
```

Manifest entry:
```yaml
  - file: ddl/001_syslog_input.sql
    type: input
    name: syslog_in
```

Key settings:
- `type` — protocol (`splunk-s2s`, `splunk-hec`, `datadog`, `elastic`, `otel`, `netflow`, `syslog`)
- `target_stream` — destination stream (must exist before the input is created)
- `tcp_port` — port to bind
- `listen_host` — address to bind (use `'0.0.0.0'` for all interfaces)

## Error Messages Include Resource Name

Install errors are wrapped with the resource name:
```
provision mv_coinbase_1s: code: 44, message: Column window_start is reserved...
```
The prefix (`provision <name>:`) tells you exactly which DDL file failed.

## Makefile Shortcut

```makefile
APP_DIR  ?= my-app
OUT      ?= $(APP_DIR).tpapp
NEUTRON_URL ?= http://localhost:8000
TENANT      ?= default

build:
	cd $(APP_DIR) && zip -r ../$(OUT) manifest.yaml ddl/ dashboards/

install: build
	curl -X POST $(NEUTRON_URL)/$(TENANT)/api/v1beta2/apps/install -F "file=@$(OUT)"
```