# GroveStreams ## Overview GroveStreams is a Temporal Intelligence Platform™ — a temporal relational database with built-in analytics, AI forecasting, and a unified query language (GS SQL™). It is the first platform where every property, every relationship, and every data point is a temporal stream. ## One-Liner One primitive. One query language. Every dimension of time. ## Core Architecture ### The Stream Primitive Everything in GroveStreams is a stream. A temperature reading every second, a rate schedule that changes once a year, a pump-to-tank relationship that changes twice a year — all are streams with identical query semantics. One data structure replaces data tables, history tables with triggers, materialized views for rollups, batch aggregation jobs, and junction tables for relationships. ### The Deep Cell Model Visualize GroveStreams as a table where each component (entity) is a row and each stream is a column — but under every cell is the complete versioned history of that value at precise timestamps. Every cell holds not just the current value, but every value it has ever had. ### Independent Time Axes Each stream has its own time axis. No shared clock forcing NULL-filling or interpolation. An energy meter reading every fifteen minutes and a contract status that changes quarterly coexist naturally. Sparse data is native. ### Link Streams (Temporal Relationships) Relationships between entities are streams. When Pump-A is reconnected from Tank-7 to Tank-12, a new data point is appended to the link stream. The old connection stays in the history. This is the key architectural differentiator — relationships have full temporal history, not just a current-state pointer. ### Roll-ups as Temporal Alignment When correlating streams with different time axes, rollups project both onto a common temporal grid. The same rollup infrastructure that powers analytics also solves cross-stream temporal correlation. ## Connecting to GroveStreams GroveStreams supports several connection paths. Pick by use case: - HTTP REST API — the native Temporal Wire ingestion path. Devices, applications, server-to-server. Full CRUD and feed-style sample ingestion. API key or session auth. https://www.grovestreams.com/developers/portal/api.html - ODBC/JDBC (Beta) — PostgreSQL wire-protocol compatible. Connect any standard Postgres driver. BI tools (Power BI, Tableau, DBeaver, DataGrip, Grafana, Excel), psql, JDBC apps. Saved GS SQL queries appear as VIEWs so BI tools get full time-series access. https://www.grovestreams.com/developers/odbc.html - OData v4 — Power BI Report Builder, SAP, IBM Cognos. OAuth 2.0 authenticated. https://www.grovestreams.com/developers/odata.html - MCP Server (Beta) — Model Context Protocol. Native channel for AI agents (Claude Desktop, Claude Code, Cursor, ChatGPT Desktop, any MCP-compatible client). OAuth 2.1 with PKCE and Dynamic Client Registration. Five tools (describe_org, list_orgs, run_gsql, ask_grovestreams, send_samples). Off by default; org admin enables it with per-tool exposure and an MCP SQL Policy fence. ODBC for your app, MCP for your AI. https://www.grovestreams.com/developers/mcp.html - User-defined connectors — pull from third-party systems on a schedule. https://www.grovestreams.com/developers/connectors.html - JDBC Import Wizard — import from PostgreSQL, MySQL, SQL Server, BigQuery, Snowflake, Redshift. Walks you through table selection, FK detection, templating; schedules incremental syncs. Components and streams register themselves. - File import — JSON, delimited (CSV). - RSS feed integration — pull from RSS sources. - AI Assistant (web UI) — natural-language query and DDL from the GroveStreams web UI; multi-provider (OpenAI, Anthropic, Gemini, xAI). - Web UI — direct dashboards, charts, GS SQL Workbench, GetData. All paths enforce the same role-based access control at the query layer — User A sees their 50 components; User B sees their 200 — consistent across REST, GS SQL, ODBC/JDBC, OData, MCP, AI Assistant, and dashboards. ## GS SQL GS SQL is GroveStreams' purpose-built temporal query language. Key features: - TEQ (Temporal Entity Queries): Use component template IDs as table names. Each template table has system columns (_component_name, _component_uid, _component_id, _component_created_date, _folder_path) plus stream ID columns. Supports FK JOINs between template tables and temporal FK resolution (showing how relationships changed over time). - TDQ (Temporal Deep Queries): Query the system Stream table directly for low-level access. - Full DDL: CREATE/ALTER/DROP for tables (templates), cycles, rollup calendars, stream groups, time filters, units, catalogs, delete profiles, runnables, connections, JDBC imports, views, materialized views, dashboards, and entity diagrams. IF [NOT] EXISTS clauses, FOLDER_PATH option, RECONCILE NOW for live schema changes. - External Views: CREATE VIEW ... CONNECTION for live JDBC queries. CREATE MATERIALIZED VIEW ... CONNECTION for cached result sets with REFRESH. - Sample parameters: Range(sd, ed) for time filtering, last=N, CycleId/Stat for interval aggregation, GapFill, TimeFilterId. - Built-in functions: String, date, math, JSON, aggregate, table functions (JSON_TABLE, CSV_TABLE), control flow. Standard SQL compatibility: NOW(), UNIX_TIMESTAMP(), FROM_UNIXTIME(), DATE_ADD/DATE_SUB with INTERVAL syntax. PG-flavored string/hash/format additions: MD5, SHA256, LPAD, RPAD, INITCAP, SPLIT_PART, REPEAT, CONCAT_WS, GEN_RANDOM_UUID / UUID_GENERATE_V4. PG-compat surface: standalone VALUES queries, bare OFFSET (no LIMIT required), and PG-style INSERT INTO <template> VALUES (...) with no column list (template stream order; PRIMARY KEY column doubles as row identifier). Window functions (v3): full PG OVER grammar — ranking (ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE), offset (LAG, LEAD), positional (FIRST_VALUE, LAST_VALUE, NTH_VALUE), and aggregate windows (SUM/AVG/COUNT/MIN/MAX/STDDEV/STDDEVP/VARIANCE/VARP/BOOL_OR/BOOL_AND OVER (...)). Supports PARTITION BY, ORDER BY with NULLS FIRST/LAST, named WINDOW w AS (...), both ROWS and RANGE frame modes with arbitrary-expression offsets (ROWS BETWEEN col_expr PRECEDING works), EXCLUDE {CURRENT ROW | GROUP | TIES | NO OTHERS}, and aggregate FILTER (WHERE pred). RANGE BETWEEN N PRECEDING is value-based (peer-aware) — on an ORDER BY time column it gives a true time-windowed rolling aggregate. Works on TDQ Stream samples, TEQ template tables, and ad-hoc rowsets. For rollup-backed time-series math (rolling averages over pre-computed rollups), use GS temporal parameters (sample(running='sum', cycleId='gs.day')) — distinct surface, distinct performance characteristic. - Pattern matching: LIKE (case-sensitive, ANSI/PG) and ILIKE (case-insensitive, PG). Both prefix patterns ('foo%') are accelerated by secondary indexes on indexed string columns. SIMILAR TO / NOT SIMILAR TO accepts string-literal patterns with LIKE wildcards + regex extras (|, *, +, ?, brackets); lowers to POSIX regex at AST build. Column-valued patterns: use `~` directly. - Named function arguments (PG-style): call by parameter name with `name => value` (modern) or `name := value` (legacy), e.g. `LPAD(string => '5', length => 3, fill => '0')`. Named args may be reordered and may follow positional args (never the reverse); names are case-insensitive; rewritten to positional order at parse time (identical result). Declared for built-in scalar functions — a superset of PG (whose built-ins mostly lack named params). Variadic/aggregate/window functions (CONCAT, COALESCE, GREATEST, SUM, ...) reject named args. - Typed string literals: `DATE '...'`, `TIME '...'`, `TIMESTAMP '...'`, `TIMESTAMPTZ '...'`, `INTERVAL '...'`, `UUID '...'`, `JSON '...'`, `JSONB '...'` work in expression context — equivalent to `CAST(string AS type)`. - PG date/time constructors: `make_date(y, m, d)`, `make_time(h, mi, s)`, `make_timestamp(y, mo, d, h, mi, s)`. `make_interval` deferred (INTERVAL3 shape needs its own runtime arm). - PG JSON aggregate: `json_agg(expr)` and `jsonb_agg(expr)` build a JSON array column-into-array; NULL inputs become JSON `null` elements (PG semantics — different from `string_agg` which skips nulls). Honors DISTINCT. Empty group returns SQL NULL. `json_object_agg` deferred. - PG function browser: `pg_catalog.pg_proc` is populated from the GS function registry — DBeaver / DataGrip / pgAdmin function-tree browsing now lists the ~160 registered GS SQL functions. Aggregates classified `prokind='a'`, window functions `'w'`, scalar `'f'`. Volatility marked: now/random/uuid generators are `'v'`, everything else `'s'`. - EXPLAIN: `EXPLAIN `, `EXPLAIN ANALYZE `, `EXPLAIN VERBOSE ` all build the GS plan tree without executing and return it as a single-column "QUERY PLAN" result set. Intercepted at the PG-wire layer (BI tools / DBeaver query-plan views work; native GS callers use `dumpTrees=true`). ANALYZE currently downgrades to plain EXPLAIN — no actual execution / row counts yet. - to_char(value, format): subset covering Power BI / Tableau export formats. Dates: YYYY / YY / MM / Mon / Month / DD / DDD / Day / Dy / HH24 / HH12 / HH / MI / SS / MS / US / AM/PM / TZ. Numbers: 9 / 0 / , / . / $ with FM (fill-mode) prefix. Unsupported tokens pass through verbatim. - jsonb_set (partial Tier 3 of pg-json-compat-plan): aliased to existing json_modify. @> / ? / ?| / ?& operator forms deferred (containment walker + lexer disambiguation needed). - DROP TABLE semantics aligned to standard SQL: DROP TABLE now always removes the template and all linked components — matches PostgreSQL, MySQL, SQLite, Oracle, SQL Server. Pre-change behavior required CASCADE just to delete linked components (non-standard). The CASCADE keyword is still accepted grammatically for compatibility but is a no-op until GS tracks cross-template dependent objects (views, FK constraints from other templates); at that point CASCADE will cascade through them and the no-flag form will refuse on dependencies. Org capability flag `gsqlAllowDropTable` (and its odbc/aiChat siblings) gates the operation; the `*DropTableCascade` flags are now vestigial. - CREATE TABLE accepts PG/CRDB compatibility no-ops so vendor schemas paste verbatim. Table-level: `PRIMARY KEY (col, ...)`, `UNIQUE (col, ...)`, `[UNIQUE] INDEX [name] (col, ...)`, CRDB `FAMILY [name] (col, ...)`. Column-level: `NOT NULL`, `UNIQUE`. All accepted and silently discarded — GS streams auto-index, components auto-assign identity, NOT NULL semantics live at the stream type. Use the canonical column-level `PRIMARY_KEY true` / two-token `PRIMARY KEY` and `INDEXED true` for actual index control. - Query cancellation: PG `CancelRequest` (out-of-band, sent on a second connection) now properly cancels the running query via a process-wide processId→Configuration registry. BackendKeyData sends a real random secretKey (was 0); CancelRequest verifies the secret before honoring the cancel. Operators that call `checkCancellation()` between rows unwind with SQLSTATE 57014 (query_canceled). Long catalog scans and TEQ queries from DBeaver / DataGrip / Power BI are cancellable mid-stream. GS SQL Workbench + GetData windows have Cancel buttons that POST to /api/gql/cancel?queryId= — same Configuration.cancel() mechanism, session-authenticated (user+org must match). - DISTINCT ON: `SELECT DISTINCT ON (k1, k2) ... FROM t [WHERE ...] ORDER BY k1, k2, ...` keeps the first row per unique key tuple. Common PG idiom for "latest row per group" (combine with ORDER BY desc on a timestamp). v1 constraint: DISTINCT ON keys must reference columns/expressions present in the SELECT projection. New DistinctOnOp dedupes by key bytes against last-emitted row. - generate_series in FROM: `SELECT * FROM generate_series(1, 10)` produces one row per integer (default step 1). 3-arg form `generate_series(start, stop, step)` supports negative or custom step; zero step rejected; direction-mismatched step yields zero rows. Streaming scanner — `generate_series(1, 1000000)` doesn't pre-materialize. Single output column `generate_series` of LONG. v1: integer variant only; timestamp+interval variant deferred. - json_object_agg / jsonb_object_agg (Phase 2 starter): `SELECT json_object_agg(k, v) FROM ...` builds a JSON object from key/value pairs. NULL keys raise (PG semantics); NULL values become JSON `null`. Last-write-wins on duplicate keys. Companion to the Phase 1 json_agg. - Phase 2 small wins: `generate_series` now accepts temporal-type args (TIMESTAMP, DATE, TIME, INTERVAL, DATETIME — all Shape.LONG) so `generate_series(TIMESTAMP '2024-01-01', TIMESTAMP '2024-12-31', INTERVAL '1 day')` works after typed-literal CAST lowering; new functions `chr(int)`, `ascii(text)`, `octet_length(text)`, `div(y, x)` (integer division), `gcd(a, b)` (greatest common divisor). `age(timestamp)` / `age(end, start)` returns a PG-style INTERVAL with the calendar-aware years/months/days/time breakdown; the 1-arg form anchors at current_date midnight (PostgreSQL semantics, matched 2026-06-10). - More Phase 2: `jsonb_strip_nulls(jsonb)` / `json_strip_nulls(json)` recursive null removal; `jsonb_pretty(jsonb)` Jackson-formatted indented text; `jsonb_contains(a, b)` and the `@>` operator (e.g. `WHERE jdata @> '{"k":"v"}'`) — full PG containment semantics (scalar deep-equal, object recursive subset, array set-containment). `date_bin(stride, source, origin)` PG-14 time bucketing — common for time-bucketed analytics, e.g. `date_bin(INTERVAL '15 minutes', t, TIMESTAMP '2024-01-01')`. `quote_ident(text)` and `quote_literal(text)` real implementations (was stubs) for SQL-safe identifier and literal escaping. - Even more Phase 2: `format(fmtstr, args...)` printf-style with `%s` (string), `%I` (identifier — quoted), `%L` (literal — quoted), `%%` literal `%`. `pg_size_pretty(bigint)` → human-readable `"1.5 MB"` / `"2 GB"` etc. `pg_size_bytes(text)` inverse — parses the same human-readable form back to bytes. `to_number(text, fmt)` inverse of numeric `to_char`. `lcm(a, b)` companion to `gcd`. Function-library coverage now ~70% (up from 64% mid-Phase-2). - Session settings: `SET name = value` / `SHOW name` round-trips for the standard PG GUCs (`search_path`, `statement_timeout`, `application_name`, `timezone`, `client_min_messages`, `intervalstyle`, etc.). `RESET`, `RESET ALL`, `DISCARD ALL` clear overrides. Wire-protocol Describe now emits ParameterDescription + NoData per PG spec, so JDBC PreparedStatement reuse and non-SELECT prepare both behave correctly. - Geospatial distance (PostgreSQL earthdistance parity): `earth_distance(ll_to_earth(lat1, lon1), ll_to_earth(lat2, lon2))` returns METERS exactly like PostgreSQL, and the convenience form `earth_distance(lat1, lon1, lat2, lon2)` returns the same meters. `earth_distance_km(lat1, lon1, lat2, lon2)` returns kilometers; for miles divide by 1609.344 (no converter function exists, same as PostgreSQL). Also available: `ll_to_earth(lat, lon)`, `latitude(point)`, `longitude(point)`, `earth()` (6378168), `sec_to_gc(m)`, `gc_to_sec(m)`. NULL on any NULL input. Also callable inside derived-stream expressions as `earthDistance(...)` / `earthDistanceKm(...)` (derivation engine uses camelCase; meters/km respectively). - Geospatial bearing + midpoint: `earth_bearing(lat1, lon1, lat2, lon2)` returns the initial compass bearing in degrees [0, 360) (0=N, 90=E, 180=S, 270=W); `earth_midpoint_lat(...)` and `earth_midpoint_lon(...)` return the latitude and longitude of the great-circle midpoint between two points. NULL on any NULL input. Also callable in derived-stream expressions as `earthBearing(...)`, `earthMidpointLat(...)`, `earthMidpointLon(...)`. - Derivation-engine carry-over of PG-compat scalars (2026-06-09): the Jep derivation engine now mirrors a subset of the SQL function library so derived-stream formulas can do the same per-sample math/string/date work as GS SQL. Convention: derivation uses **camelCase** identifiers; SQL keeps PG snake_case. Added (Jep name → SQL equivalent): `datePart(field, epochMs[, tz])` → `date_part`; `dateTrunc(field, epochMs[, tz])` → `date_trunc`; `dateBin(strideMs, epochMs, originMs)` → `date_bin`; `makeTimestamp(y, mo, d, h, mi, s[, tz])` → `make_timestamp`; `regexpMatch(str, pat[, flags])` → `regexp_match` (returns 1st capture group if any, else whole match); `regexpSubstr(str, pat[, start[, n[, flags]]])` → `regexp_substr` (n-th match); `lpad`/`rpad(str, len[, pad])`; `concatWs(sep, ...)` → `concat_ws` (skips NULL args, unlike `concat`); `splitPart(str, delim, n)` (negative n counts from end); `initcap(str)`; `strpos(haystack, needle)` (1-based, 0 on miss); `toChar(num, fmt)` → `to_char` numeric form (date formatting in derivation is still `dtFormatter`); `toNumber(str, fmt)` → `to_number` (returns BigDecimal, falls back to permissive strip-parse on format mismatch); `md5(str)`/`sha256(str)` UTF-8 → lowercase hex; `gcd(a, b)`/`lcm(a, b)`. All NULL-propagate on any NULL input. Existing replacement work via Jep `replaceAll` / `replaceFirst` (Java regex, supports inline `(?i)` etc.) so `regexp_replace` is intentionally not duplicated. - Derivation engine ValueType handling (2026-06-09): the Intvl / Pt / Rdm derivation engines all delegate to `UStreamDerivationRdm.setExpressionVariable` (input binding) and `UStreamDerivationRdm.encodeJepResult` (output encoding), so the three engines cannot drift. Inputs: numerics bind as numbers; BOOLEAN as boolean; STRING / FILE / JSON / JSONB as text; DATE / TIME / TIMESTAMP / DATETIME as epoch-ms (or µs-since-midnight for TIME) Long; UUID as canonical 8-4-4-4-12 lowercase hex string; LATITUDE / LONGITUDE / ELEVATION / DIRECTION360 as Double; INTERVAL and BYTE_ARRAY throw at evaluation. Outputs follow the inverse mapping; numeric Jep results are coerced via `Number.longValue()` / `.intValue()` / etc. so Integer-returning Jep fns (`len`, `length`) work for any numeric stream type. UUID output validates the result text — invalid UUID text yields a null sample for that interval (surrounding intervals still derive) instead of crashing the block. FILE output is blocked at validator (a derived stream cannot be a file). INTERVAL output is blocked at validator (no useful JEP semantics). Any unrecognized ValueType in either direction throws a clear "unsupported ValueType" error — this `default: throw` arm was added 2026-06-09 to replace the previous silent-drop failure mode that bit DATE/TIME/TIMESTAMP/JSON/JSONB/UUID in Intvl + Pt before the fix. Event detection (`ExecuteTriggerExpression`) uses the same binding contract; events on INTERVAL / BYTE_ARRAY monitored streams are blocked at config time by `UCompValidator`. - Regex operator: `~` is case-sensitive (PG semantics); use `SIMILAR TO` or `regexp_match` for portable patterns. - More numeric / math functions: `atan2(y, x)` two-arg arctangent; `cot(x)`; `cbrt(x)` cube root; `factorial(n)` n! (BIG_DECIMAL, capped at n=10000); `scale(numeric)` fractional-digit count; `min_scale(numeric)` minimum scale to represent exactly; `trim_scale(numeric)` strips trailing zeros; `width_bucket(operand, low, high, count)` histogram bucket index. - More string functions: `btrim(str [, chars])` alias of `trim`; `overlay(src, substr, start [, length])` replaces `length` chars of `src` starting at 1-based `start`; `regexp_substr(str, pattern [, start [, n [, flags]]])` n-th regex match; `regexp_split_to_array(str, pattern [, flags])` returns a JSON-array text (GS deviation — PG returns `text[]`, GS has no first-class ARRAY type). - More date/time functions: `clock_timestamp()` true wall-clock; `statement_timestamp()` wall-clock at statement start; `transaction_timestamp()` alias of `now()`; `isfinite(date|timestamp|interval)` TRUE for any non-NULL input; `justify_days(interval)` / `justify_hours(interval)` / `justify_interval(interval)` interval canonicalization. - Session / catalog scalars: `current_setting(name [, missing_ok])` / `set_config(name, value, is_local)` session GUC read/write (no-op writes today); `has_table_privilege([user,] table, priv)`, `has_column_privilege([user,] table, col, priv)`, `has_database_privilege([user,] db, priv)`, `has_schema_privilege([user,] schema, priv)`, `has_any_column_privilege([user,] table, priv)` all return TRUE in single-tenant — do not layer manual permission filters on top; `to_regclass(text)`, `to_regtype(text)`, `to_regrole(text)` name → OID or NULL; `pg_function_is_visible(oid)` / `pg_type_is_visible(oid)` always TRUE; `obj_description(oid [, catalog])` / `col_description(oid, col)` / `shobj_description(oid, catalog)` object comments; `version()` version banner; `pg_client_encoding()` returns `'UTF8'`. - jsonb-contained-in operator: `<@` is the inverse of `@>`. Both operators fully supported (`a @> b` true when `a` contains `b`; `a <@ b` true when `a` is contained in `b`). - Accepted no-op statements so PG/ANSI client scripts paste verbatim: `BEGIN` / `COMMIT` / `SAVEPOINT` / `RELEASE [SAVEPOINT]` / `ROLLBACK TO [SAVEPOINT] name`; `SELECT ... FOR UPDATE` / `FOR SHARE` (lock clause parsed, ignored); `CREATE EXTENSION [IF NOT EXISTS]` / `DROP EXTENSION`; `VACUUM` / `ANALYZE`; `COMMENT ON`; `LOCK TABLE`. (`GRANT` / `REVOKE` / `TRUNCATE` raise loud errors instead — see below.) - More accepted no-op statements so PG client scripts paste verbatim: `CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] name ON [ONLY] tbl (col [, ...]) [INCLUDE (...)] [WHERE pred] [WITH (...)]`; `DROP INDEX [CONCURRENTLY] [IF EXISTS] name [, ...] [CASCADE | RESTRICT]`; `REINDEX [VERBOSE] {INDEX | TABLE | SCHEMA | DATABASE | SYSTEM} name`; `CREATE SCHEMA [IF NOT EXISTS] name [AUTHORIZATION role]` / `DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT]` / `ALTER SCHEMA name {RENAME TO new_name | OWNER TO role}` (GS uses catalogs for namespacing); `CREATE ROLE` / `CREATE USER` / `DROP ROLE` / `DROP USER` (use GS RBAC); `SET SESSION AUTHORIZATION {DEFAULT | rolespec}` / `RESET SESSION AUTHORIZATION`; `DEALLOCATE [PREPARE] {ALL | name}`. - ROLLBACK raises an error: bare `ROLLBACK` is rejected — error wording: "ROLLBACK is not supported: transactions are not yet implemented. Prior statements have already committed and cannot be rolled back." Each statement is its own atomic unit; reverse mistakes with an explicit DELETE / UPDATE, not ROLLBACK. `ROLLBACK TO [SAVEPOINT] name` remains a no-op (see prior line). - GRANT / REVOKE / TRUNCATE raise loud errors: the previous silent `(0 rows)` was a privilege-confusion footgun. Verbatim error wording — GRANT/REVOKE: "GRANT/REVOKE is not supported via SQL — manage permissions via the org RBAC UI. The statement was rejected; permissions were NOT changed." TRUNCATE: "TRUNCATE is not supported. Use DELETE FROM ... instead. No rows were removed." Use the org RBAC surface for permissions; use `DELETE FROM template` for bulk row removal (cascades through component history). Still silently swallowed for tool compat (no error): BEGIN, COMMIT, SAVEPOINT, RELEASE [SAVEPOINT], LOCK, COMMENT ON, CREATE EXTENSION, VACUUM, CREATE INDEX (auto-indexed), REINDEX, SCHEMA, ROLE, USER, SESSION AUTHORIZATION, DEALLOCATE, DISCARD. - INTERVAL field qualifiers: `INTERVAL '1-2' YEAR TO MONTH`, `INTERVAL '1 day' DAY TO SECOND`. Accepted qualifier set: `YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | YEAR TO MONTH | DAY TO HOUR | DAY TO MINUTE | DAY TO SECOND | HOUR TO MINUTE | HOUR TO SECOND | MINUTE TO SECOND`. Parse-and-swallow: the qualifier is accepted but the interval-string parser is the authority — it determines the actual unit set and magnitude. The qualifier does not coerce, truncate, or validate. - BETWEEN SYMMETRIC / ASYMMETRIC: `x BETWEEN SYMMETRIC b AND c` lowers to `(x BETWEEN b AND c) OR (x BETWEEN c AND b)` so the bounds can be given in either order. `ASYMMETRIC` is the default and may be written explicitly (`x BETWEEN ASYMMETRIC b AND c`); composes with NOT (`NOT BETWEEN SYMMETRIC`). - IS NOT TRUE / IS NOT FALSE / IS NOT UNKNOWN: full three-valued-logic predicates. `NULL IS NOT TRUE` → TRUE; `NULL IS NOT FALSE` → TRUE; `NULL IS NOT UNKNOWN` → FALSE. Complement of the existing `IS TRUE / IS FALSE / IS UNKNOWN`. - VALUES as a table constructor in FROM: `SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(id, label)`. Without the `AS t(c1, c2, ...)` alias, columns default to `column1..columnN` (PG semantics). All rows must have equal width; mismatched-width VALUES raises a parse error. Composes anywhere a table reference fits (JOIN, subquery, CTE). - STRING_AGG with ORDER BY (per #104): `STRING_AGG(name, ',' ORDER BY priority DESC, name ASC)`. Multi-key ASC/DESC supported. Default null ordering: NULLS LAST on ASC, NULLS FIRST on DESC (PG semantics). Inner ORDER BY is the aggregate ordering, not the outer query ordering. - `pg_get_userbyid(oid)`: now does real OrgUser lookup (was a `"gs_user"` stub). Returns the username for a known user OID; falls back to `"unknown (OID=)"` for missing/bad UIDs. Used by pgAdmin / DBeaver Owner columns. - `pg_settings` expansion: 10 → 23 GUCs. Now includes `bytea_output=hex`, `search_path`, `server_version=14.0`, `server_version_num=140000`, `standard_conforming_strings=on`, `integer_datetimes=on`, `default_text_search_config`, `max_index_keys=32`, `max_function_args=100`, `max_identifier_length=63`, `datestyle=ISO, MDY`, `intervalstyle=postgres`, `lc_collate` / `lc_ctype` / `lc_messages` / `lc_monetary` / `lc_numeric` / `lc_time` (locale group), `client_encoding=UTF8`, `server_encoding=UTF8`, `timezone=UTC`, `application_name`. `current_setting('bytea_output')` now returns `'hex'` (was NULL). - Typmod persistence: `pg_catalog.pg_attribute.atttypmod` and `information_schema.columns.character_maximum_length` / `datetime_precision` / `interval_precision` now emit real values for VARCHAR(n) (n=255 default), TIMESTAMP (p=3), TIME (p=6), INTERVAL (p=3). `NUMERIC(p, s)` typmod still -1 — per-stream NUMERIC precision/scale fields are on the roadmap. - `is_superuser` per-org-owner: `current_setting('is_superuser')` now returns `'on'` only for the org owner, `'off'` for non-owner members. Was hardcoded `'on'` for everyone. - `has_table_privilege` / `has_column_privilege` / `has_database_privilege` / `has_schema_privilege` / `has_any_column_privilege` (security audit): org owners get TRUE for all privileges; non-owner members get TRUE only for SELECT and REFERENCES (returns FALSE for INSERT/UPDATE/DELETE/TRUNCATE/TRIGGER/etc.); unknown relation/column raises PG-shape error. Previously returned blanket TRUE for everyone — RBAC-bypass risk for non-owner sessions that asked these scalars before issuing a write. (Supersedes the prior "all return TRUE in single-tenant" guidance.) - `pg_stat_activity` privilege redaction: cross-user rows now redact `query` and `application_name` to `''` and `client_addr` to NULL, matching PG-13+ semantics. Org owners see all columns (admin exemption); a user's own row is never redacted. - Window-frame `EXCLUDE GROUP` / `EXCLUDE TIES` are supported — see the Window functions row above. - Secondary indexes for high-performance TEQ scans. - AI functions (inline LLM calls): call a model per row from any expression, or per sample from a derived-stream formula — same surface as Snowflake AI_COMPLETE/AI_FILTER/AI_AGG, Databricks ai_query, BigQuery AI.GENERATE_*. Return type is fixed by the function NAME (the planner needs it at plan time; there is no generic agent()): `agent_string` (STRING), `agent_boolean` (BOOLEAN — put in WHERE for the AI_FILTER pattern), `agent_double` (DOUBLE), `agent_long` (LONG), `agent_json`/`agent_jsonb` (structured). Aggregates `agent_string_agg`/`agent_json_agg` reduce over a GROUP BY (one call per group). Signature: required prompt, then one typed input, then PG-style named options (all optional, default NULL): `model =>` (stable tier alias 'Minion'|'Brain', or vendor-qualified e.g. 'xAI Brain', resolved through the org Default Vendor — users never see churning model ids), `timeout_ms =>` (default 60000), `labels =>` ('a,b,c' constrains agent_string to that enum = classification, no separate classify()), `schema =>` (a GS template id → structured output built from the template's streams), `agent =>` (a named OrgAgent id → runs the full tool-enabled agentic loop). FILE-typed inputs are fetched through the RBAC-checked GS file path and attached as image/PDF blocks (document/image understanding; e.g. `agent_string('Parse this contract', contract_file)`). Document→table without new grammar: `JSON_TABLE(agent_json(...), ...)`. The optimizer tags agent functions high-cost/volatile/non-foldable and runs cheap predicates first (the funnel), so an agent_boolean in WHERE is only evaluated on rows that survive the cheap predicates; identical calls are memoized within a query. Require the AI_ASSISTANT capability and honor org admin agent-access limits, same as the chat assistant; billed via the standard credit path; provenance records the concrete model+vendor that ran. Derived streams that use an AI function are moved to the throttled derivation lane on creation (the first, possibly slow, call never blocks derivation for other streams) and require a Run-As user on the derivation definition. JEP (derived-stream) surface uses camelCase positional forms: `agentString`/`agentBoolean`/`agentDouble`/`agentLong`/`agentJson` with a `labels('a','b','c')` marker for classification. ## Key Capabilities - Time-series storage: 100M+ data points per stream - GS SQL: Purpose-built temporal query language with DDL, TEQ, TDQ, external views, and time-series extensions - GetData: Template-scoped query surface with cell-level drill and write-back. Every cell in a GS SQL result carries server-stamped origin metadata (source template, stream ID, FK target, derivation expression, PG OIDs, writability). Hover shows a sparkline + recent-samples grid; right-click drills into the stream's full history (Quick View / Open Stream), opens derivation diagnostics for derived cells, or follows foreign-key cells to a new GetData tab on the target template. Set Value writes back to the underlying temporal stream at the cell's original sample time (replace semantics) or at the current clock (append semantics) — same Temporal Wire ingestion path, same RBAC, same audit, same derivation/rollup recalculation. Cells flow as context to the AI Assistant. - AI Forecasting: TFT, N-BEATS, ARIMA, Prophet, TCN, Transformer, Exponential Smoothing, RNN - Correlation Detection: Automatic relationship identification between streams - AI Assistant: Multi-provider support (OpenAI, Anthropic, Gemini, xAI) with 35+ tools, sub-agents, direct GS SQL execution including full DDL, and natural-language schema generation - AI Functions (inline LLM in SQL): agent_string / agent_boolean / agent_double / agent_long / agent_json(b) callable in any GS SQL expression (and agentString/... per sample in derived streams) — classify, score, and extract per row; agent_boolean in WHERE = AI_FILTER; agent_string_agg / agent_json_agg reduce over GROUP BY; structured output from template schemas; document/image file-stream inputs; cost-aware planner runs cheap predicates first. Same multi-provider profiles, Brain/Minion tiers, RBAC, and billing as the AI Assistant. (Snowflake AI_*/Databricks ai_query/BigQuery AI.GENERATE_* parity, over the temporal model.) - MCP Server (Model Context Protocol) [Beta]: Built-in server at /api/mcp?org= lets external AI agents (Claude Desktop, Claude Code, Cursor, ChatGPT Desktop) connect natively. The org UID on the URL binds the session to one organization for its entire life — sessions cannot switch orgs mid-stream (deliberate, for prompt-injection blast-radius containment and audit clarity). To work in multiple orgs, register one MCP entry per org. Five tools: describe_org (orientation + capability flags), list_orgs (returns the orgs the bearer can access — discovery aid only, does not switch the session), run_gsql (direct GS SQL execution; same engine as ODBC), ask_grovestreams (delegate to the in-app AI Assistant; sticky multi-turn), send_samples (Temporal Wire ingestion). Resources expose gs://help/{topic} curated docs plus per-org templates / dashboards / saved-queries so agents ground on real GroveStreams content. OAuth 2.1 with PKCE and Dynamic Client Registration; discovery documents live at host-root /.well-known/oauth-* per RFC 8414 / RFC 9728. Two gates apply: an org-level Enable MCP toggle and a user-level MCP Access RBAC capability on the user's group. Per-org admin also controls per-tool exposure and an MCP SQL Policy fence (Read / DDL / DML / System tables) that constrains all SQL routed through MCP including assistant delegation. Billing tracked as a separate line item. - Real-Time Rollups: Sum, time-weighted average, min, max, first, last — automatic from seconds through years - Derived Streams: Excel-style formula engine with cross-component references, FK-resolved dependencies (forward and fan-in), temporal segmentation - FK Dependencies: Derived streams can reference other entities via SQL-resolved foreign keys. Fan-in aggregation (SUM, AVG, MIN, MAX, COUNT) across multiple source entities. Temporal segmentation automatically handles relationship changes over time. - Entity Diagrams: Visual entity relationship diagrams with shared layouts. Both component templates and saved Views render as nodes (views shown with a dashed border and "V" badge). Manageable via DDL (PLACE NODE, PLACE VIEW, RELATIONSHIP, REMOVE NODE, REMOVE VIEW) - Data Ingestion: HTTP REST API, user-defined connectors, RSS, file import - Events and Alerts: Email, SMS, MMS, HTTP notifications - Dashboards: Drag-and-drop HTML, embeddable charts, mobile-responsive - OData Connector: Power BI, Tableau, SAP, IBM Cognos - Security: SOC2-certified, SSL/TLS, RBAC, 2FA - Custom Branding: Branded subdomains and custom logos with Powered by GroveStreams ## Key Differentiators vs. Multi-System Alternatives - Properties: Every property is a temporal stream (vs. static values) - Relationships: Link streams with full temporal history (vs. static pointers) - Query Language: Unified GS SQL with full DDL (vs. multiple languages and manual schema management) - FK Dependencies: Derived streams auto-resolve across entity relationships with temporal segmentation (vs. custom pipeline code) - AI Forecasting: Built-in with 8 model types (vs. external ML services) - Real-Time Rollups: Automatic and configurable (vs. manual Spark/Flink jobs) - Schema Evolution: Live reconciliation with RECONCILE NOW, zero downtime (vs. table locks) - Designed for AI Agents: Templates give agents a discoverable schema, GS SQL gives them one query language, DDL lets them create entire ontologies ## Announcements - An AI's Peek Under the Hood (Claude Opus independent analysis of the platform): https://www.grovestreams.com/whitepaper-ai-analysis.html - Why Temporal Intelligence (architecture deep dive): https://www.grovestreams.com/m2m.html ## Documentation - GS SQL Overview: https://www.grovestreams.com/developers/gsql.html - GS SQL Grammar: https://www.grovestreams.com/developers/gsqlgrammar.html - GS SQL Tables: https://www.grovestreams.com/developers/gsqltables.html - GS SQL TEQ Guide: https://www.grovestreams.com/developers/gsqlteq.html - GS SQL DDL: https://www.grovestreams.com/developers/gsqlddl.html - GS SQL LLM Reference (compact): https://www.grovestreams.com/developers/gsql_llm_reference.html - GS SQL DDL LLM Reference (compact): https://www.grovestreams.com/developers/gsql_llm_ddl_reference.html - AI Forecasting: https://www.grovestreams.com/developers/forecasting.html - Modeling Guide: https://www.grovestreams.com/developers/help_modeling_guide.html - Derived Streams: https://www.grovestreams.com/developers/help_derived_streams.html - FK Dependencies: https://www.grovestreams.com/developers/help_fk_deps.html - Developer Portal: https://www.grovestreams.com/developers/developers.html - REST API: https://www.grovestreams.com/developers/portal/api.html - OAuth: https://www.grovestreams.com/developers/oauth.html - OData: https://www.grovestreams.com/developers/odata.html - MCP Server (Beta): https://www.grovestreams.com/developers/mcp.html - GetData (drillable, editable query results): https://www.grovestreams.com/developers/getdata.html - FAQ: https://www.grovestreams.com/faq.html ## Getting Started - Free 90-day trial. No credit card required. Phone verification activates the trial. - Trial allotment: 10,000 components, 100M samples/month, 40,000 credits/month (refreshes on the 1st of each calendar month). - Trial AI is pinned to the system default profile (OpenAI). Paid plans unlock per-org profile selection across OpenAI, Anthropic, Gemini, and xAI brains. - During design and build, point your own coding AI (Claude Code, Cursor, ChatGPT Desktop) at the GroveStreams MCP server — those calls don't consume trial credits because the LLM runs on the user's side. Setup: https://www.grovestreams.com/developers/mcp.html - Paid plans: Growth ($599/mo, 60k credits), Business ($1,799/mo, 180k credits), Enterprise ($5,999/mo, 600k credits), Strategic (custom). See https://www.grovestreams.com/pricing.html ## Company - Name: Grove Streams LLC - Location: Minneapolis, MN, USA - Website: https://www.grovestreams.com - Contact: info@grovestreams.com - X / Twitter: https://x.com/grovestreams - LinkedIn: https://www.linkedin.com/company/grove-streams-llc