Database Browser & SQL

ControlBird exposes its in-memory entity store as a queryable database, giving you two complementary ways to explore live data. The Database Browser app provides a visual, columnar view for navigating entity relationships and inspecting field metadata. The SQL endpoint offers PostgreSQL-compatible SQL access, so any PostgreSQL client can run standard SQL against your running system. Both surfaces read and write the same live store, so a query reflects the state of your data model at the moment it executes.

Every entity type defined in your data model is automatically exposed as a table. There is no separate schema to maintain or sync: the table list is derived directly from the schema, and queries are evaluated against the live store on the fly.

The Two Surfaces

Database Browser App

Open the Database Browser from the control plane UI. The app is registered with the ID database-browser and requires the app.database-browser permission.

• Browse columns. The left column lists entity types. Selecting an entity populates the next column with its children, letting you drill down through the hierarchy one level at a time.
• Inspect details. The right panel shows the selected entity's fields along with metadata values such as $WriteTime (when the field was last written) and $Writer (which entity wrote it).
• Navigate relationships. Follow Parent references to move up the tree and entity reference fields to jump across the model.

Columns are resizable via a drag handle, with a minimum width of 150px and a maximum of 400px. On small screens the UI switches to a split-pane layout with a dedicated button to open the detail view.

Connecting via SQL

The SQL endpoint listens on 0.0.0.0:5432 by default. The port is configurable through the CB_SQL_PORT environment variable. It connects to the kernel at 127.0.0.1:9100 (overridable with KERNEL_ADDRESS).

Authentication

Authentication uses a session token supplied as the PostgreSQL password field. Use a token issued by the control plane for your account.

# Connect with psql, passing your session token as the password
PGPASSWORD="<your-session-token>" psql \
  --host=your-node-hostname \
  --port=5432 \
  --username=controlbird \
  --dbname=controlbird

Querying Entities

Any entity type can be queried as a table by its name, for example Service, Task, or SensorData. Each row corresponds to one entity instance.

Selecting Fields

-- Select specific fields
SELECT Name, Mode FROM Service;

-- Expand all fields from the entity type schema
SELECT * FROM Service;

SELECT * expands to every field defined for the entity type. Each entity also carries a synthetic entity_id column (a 64-bit integer, type INT8). Because ControlBird values span many types, all field values are returned as TEXT: client-side code can infer the intended type from the field definition.

Filtering with WHERE

WHERE clauses are evaluated against each entity. The following operators are supported:

-- Equality filter
SELECT Name FROM Service WHERE Mode = 'Active';

-- IN list
SELECT Name FROM Service WHERE Mode IN ('Active', 'Standby');

-- Pattern match
SELECT Name FROM Service WHERE Name LIKE 'test%';

Sorting and Limiting

ORDER BY sorts by the first specified column, ascending or descending, and LIMIT caps the number of rows returned.

SELECT Name, Mode FROM Service ORDER BY Name LIMIT 10;

Field Indirection

ControlBird has no SQL JOIN. Instead, you traverse entity relationships inline using the -> operator, reading fields from referenced entities in the same query. This is the same field-indirection mechanism used throughout the data model.

-- Read the parent entity's Name alongside the service Name
SELECT Name, Parent->Name FROM Service;

-- Chain references to walk further up the tree
SELECT Name, Parent->Parent->Name FROM Service;

Both -> and . are accepted as path delimiters; this documentation uses -> for clarity.

Aggregation

Standard SQL aggregate functions are supported: COUNT, SUM, AVG, MIN, and MAX. Use GROUP BY to bucket results and HAVING to filter aggregated output.

-- Count all entities of a type
SELECT COUNT(*) FROM Service;

-- Count grouped by a field
SELECT Mode, COUNT(*) FROM Service GROUP BY Mode;

When you mix an aggregation with other columns, those columns must appear in the GROUP BY clause. The HAVING clause is limited to simple comparisons and cannot reference fields that are neither grouped nor aggregated.

Field Metadata Columns

Every field carries metadata describing when and by whom it was last written. Append a metadata suffix to a field name to read it:

SELECT Name, Name$WriteTime, Name$Writer FROM Service;

Querying Historical Data

Time-series and audit data is reachable through the History$ table prefix. A query against History$EntityType is routed to the Historian service, which must be running and reachable via the HISTORIAN_ADDRESS configuration.

SELECT * FROM History$SensorData LIMIT 100;

Historian rows use a fixed column layout:

Writing Data

The SQL endpoint is not read-only. INSERT, UPDATE, and DELETE statements write directly to the live store.

INSERT

Creating an entity assigns its entity_id automatically. Provide a Name column; if you omit it, a UUID-based name is generated.

INSERT INTO Service (Name, Mode) VALUES ('my-service', 'Active');

UPDATE and DELETE

Both UPDATE and DELETE require a WHERE clause. For DELETE this is enforced as a hard safety guard to prevent accidental bulk deletion of an entire entity type.

-- Modify an existing entity
UPDATE Service SET Mode = 'Standby' WHERE Name = 'my-service';

-- Remove an entity (WHERE clause is mandatory)
DELETE FROM Service WHERE Name = 'my-service';

Limitations

The SQL endpoint targets practical querying of the entity store rather than full SQL parity. Keep these constraints in mind:

• No JOIN across entity types; use field indirection with -> instead.
• Only the standard aggregates (COUNT, SUM, AVG, MIN, MAX) are available; custom aggregate functions are not supported.
• WHERE supports comparison and logical operators only, no sub-queries.
• No UNION, INTERSECT, or EXCEPT set operations.
• GROUP BY ALL is not supported.
• All result values are serialized as TEXT; infer the concrete type from the field definition on the client.
• Historian queries require the Historian service to be running with HISTORIAN_ADDRESS configured.
• No transactions or multi-statement atomicity.

Configuration Reference

For details on the underlying data model and how entity types and fields are defined, see the Model Builder & Data Modeling guide.