API Reference
RESTBI exposes a comprehensive REST API for dataset management, querying, access control, and tool discovery. All endpoints use JSON and require JWT authentication.
Authentication & User Management
GET/meGet current user information from JWT
JWT Token Structure
Authentication is handled via JWT tokens. The token contains user information and is sent in the Authorization header for all API requests.
{
"sub": "user@example.com",
"email": "user@example.com",
"name": "user",
"role": "data_admin" | "super_admin",
"groups": ["demo_users"],
"exp": 1234567890,
"iss": "restbi",
"aud": "restbi-api"
}Database Connections
GET/connectionsList all accessible database connections
POST/connectionsCreate a new database connection
GET/connections/:idGet a specific connection by ID
PATCH/connections/:idUpdate a connection
DELETE/connections/:idDelete a connection
GET/connections/:connectionId/metadataGet all tables/columns in a connection
Datasets
GET/datasetsList all accessible datasets
POST/datasetsCreate a new dataset
GET/datasets/:idGet a dataset with full schema
PATCH/datasets/:idUpdate a dataset
DELETE/datasets/:idDelete a dataset
Tables
GET/datasets/:datasetId/tablesList all tables in a dataset
POST/datasets/:datasetId/tablesAdd a new table to a dataset
PATCH/datasets/:datasetId/tables/:tableIdUpdate a table
DELETE/datasets/:datasetId/tables/:tableIdDelete a table
Columns
GET/datasets/:datasetId/tables/:tableId/columnsList all columns in a table
POST/datasets/:datasetId/tables/:tableId/columnsAdd a new column to a table
PATCH/datasets/:datasetId/tables/:tableId/columns/:columnIdUpdate a column
DELETE/datasets/:datasetId/tables/:tableId/columns/:columnIdDelete a column
Joins
GET/datasets/:datasetId/joinsList all joins for a dataset
POST/datasets/:datasetId/joinsCreate a new join
PATCH/datasets/:datasetId/joins/:joinIdUpdate a join
DELETE/datasets/:datasetId/joins/:joinIdDelete a join
Queries
POST/queryExecute an ad-hoc query against a dataset
POST/queriesSave a query for later use
GET/queriesList all saved queries you have access to
GET/queries/:idGet a specific saved query by ID
PATCH/queries/:idUpdate a saved query
DELETE/queries/:idDelete a saved query
POST/queries/:id/executeExecute a saved query
Validation & Instructions
POST/validateValidate a semantic dataset
POST/instructionsGet dynamic, context-aware instructions for building queries
Groups
GET/groupsList groups you belong to (or all, if super admin)
GET/groups/:idGet group details (requires membership)
POST/groupsCreate new group (super admin only)
PATCH/groups/:idUpdate group (requires membership)
DELETE/groups/:idDelete group (requires membership)
POST/groups/:id/usersAdd user to group
DELETE/groups/:id/users/:userIdRemove user from group
Access Control
POST/access-controlGrant a user access to a resource (super admin only)
GET/access-controlList all access control entries (super admin only)
GET/access-control/:idGet a specific access control entry (super admin only)
PATCH/access-control/:idUpdate an access control entry (super admin only)
DELETE/access-control/:idDelete an access control entry (super admin only)
User Management
GET/usersList all users in system (super admin only)
GET/user-rolesList all user roles and permissions (super admin only)
User Auto-Sync
Users are automatically created and synced from JWT tokens in real time. When a user authenticates with a valid JWT, their information (email, name, role, groups) is automatically synchronized with the backend user database. No manual user creation is required - the system handles user provisioning automatically.
Tools
GET/toolsGet comprehensive API tooling overview
Examples
Create a Database Connection
POST /connections
{
"name": "Production Database",
"type": "POSTGRES",
"host": "db.example.com",
"port": 5432,
"username": "user",
"password": "password",
"database": "analytics"
}Create a Dataset
POST /datasets
{
"displayName": "Sales Analytics",
"connectionId": "conn_123",
"tables": [
{
"displayName": "Orders",
"dbName": "orders",
"columns": [
{ "displayName": "Order ID", "dbName": "id", "type": "DIMENSION" },
{ "displayName": "Total", "dbName": "total", "type": "MEASURE" }
]
}
]
}Run a Query
POST /query
{
"datasetId": "dataset_123",
"query": {
"columns": [
{ "name": "Products.ProductName", "alias": "Product" },
{ "name": "Orders.Total", "aggregation": "SUM", "alias": "Total Sales" },
{ "expression": "SUM({{Orders.Total}} * {{Orders.TaxRate}})", "alias": "Total Tax" }
],
"filters": [
{ "column": "Orders.Date", "operator": ">=", "value": "2024-01-01" }
],
"sortBy": { "name": "Total Sales", "direction": "DESC" },
"limit": 10
}
}Save a Query
POST /queries
{
"name": "Top Products by Sales",
"description": "Shows top 10 products by total sales",
"datasetId": "dataset_123",
"query": {
"columns": [
{ "name": "Products.ProductName", "alias": "Product" },
{ "name": "Orders.Total", "aggregation": "SUM", "alias": "Total Sales" }
],
"sortBy": { "name": "Total Sales", "direction": "DESC" },
"limit": 10
},
"isPublic": false
}Execute a Saved Query
POST /queries/query_456/execute
{}Share Resource with User
POST /share
{
"resource": "dataset",
"resourceId": "dataset_123",
"userId": "user_456",
"canEdit": true
}Share Resource with Group
POST /share
{
"resource": "dataset",
"resourceId": "dataset_123",
"groupId": "group_789",
"canEdit": false
}Create Group
POST /groups
{
"name": "Analytics Team"
}Get Context-Aware Instructions
POST /instructions
{
"datasetId": "dataset_123",
"query": {
"columns": ["Products.ProductName", "Orders.Total"]
},
"userId": "user_456",
"roleId": "role_789"
}