BI SQL API Developer Guide

Note: The BI SQL API is accessible only to accounts on the Pro Plan

Overview

The BI SQL API provides a PostgreSQL-compatible interface to query Boxly's semantic layer. This guide covers everything you need to get started, including connection setup, query patterns, supported operations, and best practices specific to your analytics infrastructure.

Important: The data that you receive is not live. It is refreshed daily around 1 - 2 AM UTC.

Note: The SQL API is provided as a convenience for BI tools that cannot use the HTTP Query API. If possible, we recommend using the Query API.

The Query API is more natural and well documented for Cube.js - the engine that powers Boxly’s semantic layer.

Connection Details

For connection instructions, please log into app.boxly.ai and view the “SQL Data Access” section in “Settings”.

SQL Commands Reference

SELECT - Retrieve Tables Information

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

SELECT - Retrieve Data

Syntax

SELECT select_expr [, ...]
FROM from_item
[CROSS JOIN join_item ON join_criteria]*
[WHERE where_condition]
[GROUP BY grouping_expression]
[HAVING having_expression]
[LIMIT number] [OFFSET number];

Example 1 - Count Users by Account

SELECT
COUNT(*) as total_users, account_id
FROM
eb_pub_users
GROUP BY
account_id
ORDER BY
total_users DESC;

Example 2 - Active Users with Details

SELECT
id, username, email, created_at
FROM
eb_pub_users
WHERE
is_active = true
AND is_deleted = false
LIMIT 100;

Example 3 - Get Leads from Last 120 Days

SELECT
id, name, email, phone_number, created_at
FROM
eb_pub_leads
WHERE
created_at >= NOW() - INTERVAL '30 days'
ORDER BY
created_at DESC;

JOINS

Cube SQL API does NOT support traditional ON clause joins. Use CROSS JOIN with table names.

Correct Pattern

In the Query we add in names of the tables we want to join along with alias, Our data model will take care on which column it needs to join and will provide the results. Only Cross Join is supported. The Join will always be applied as Left Join.

SELECT leads.id, leads.name, accounts.account_name
FROM eb_pub_leads as leads
CROSS JOIN eb_pub_accounts as accounts;

Wrong Pattern

In the Query if we add in the traditional way of joining using ON clause, the query will fail. You cannot use any other join apart from Cross Join.

SELECT leads.id, leads.name, accounts.account_name
FROM eb_pub_leads as leads
Inner JOIN eb_pub_accounts as accounts on leads.iaccount_id = accounts.id;

Example 1 - Leads with Box Information

SELECT
leads.name,
leads.email,
boxes.box_name,
boxes.box_id,
leads.created_at
FROM eb_pub_leads as leads
CROSS JOIN eb_pub_boxes as boxes
ORDER BY leads.created_at DESC
LIMIT 50;

Example 2 - Multiple Joins

select leads_milestone.lead_id, leads.name, milestones.milestone_name, leads_milestone.value
from eb_pub_lead_milestones as leads_milestone
cross join eb_pub_leads as leads
cross join eb_pub_milestones as milestones
cross join eb_pub_boxes as box
cross join eb_pub_box_stages as stages;

Best For

Works seamlessly with BI tools like Tableau, Superset, Metabase, Power BI.
Provides consistent metrics access for any SQL client, ideal for metrics stores serving diverse consumers.
Facilitates self-service analytics for business users.

Limitations

Only cross joins are supported; other join types (inner, left, right) are not supported in SQL API queries.
You cannot query beyond 15 dimensions at a single point in a SQL SELECT statement.
The SQL API does not support arbitrary raw SQL; queries must map to Cube's cube model and measures.
Query pushdown is limited and not all SQL features or complex queries are supported.

SQL Functions & Operators

COMPARISON OPERATORS

Function	Function Description
<	Returns TRUE if the first value is less than the second
>	Returns TRUE if the first value is greater than the second
<=	Returns TRUE if the first value is less than or equal to the second
>=	Returns TRUE if the first value is greater than or equal to the second
=	Returns TRUE if the first value is equal to the second
<> or !=	Returns TRUE if the first value is not equal to the second

COMPARISON PREDICATES

Function	Function Description
BETWEEN	Returns TRUE if the first value is between the second and the third
IS NULL	Test whether value is NULL
IS NOT NULL	Test whether value is not NULL

MATHEMATICAL FUNCTIONS

Function	Function Description
ABS	Absolute value
CEIL	Nearest integer greater than or equal to argument
DEGREES	Converts radians to degrees
EXP	Exponential (e raised to the given power)
FLOOR	Nearest integer less than or equal to argument
LN	Natural logarithm
LOG	Base 10 logarithm
LOG10	Base 10 logarithm (same as LOG)
PI	Approximate value of π
POWER	a raised to the power of b
RADIANS	Converts degrees to radians
ROUND	Rounds v to s decimal places
SIGN	Sign of the argument (-1, 0, or +1)
SQRT	Square root
TRUNC	Truncates to integer (towards zero)

STRING FUNCTIONS AND OPERATORS

Function	Function Description
\|\|	Concatenates two strings
BTRIM	Removes the longest string containing only characters in characters from the start and end of string
BIT_LENGTH	Returns number of bits in the string (8 times the OCTET_LENGTH)
CHAR_LENGTH or CHARACTER_LENGTH	Returns number of characters in the string
LOWER	Converts the string to all lower case
LTRIM	Removes the longest string containing only characters in characters from the start of string
OCTET_LENGTH	Returns number of bytes in the string
POSITION	Returns first starting index of the specified substring within string, or zero if it's not present
RTRIM	Removes the longest string containing only characters in characters from the end of string
SUBSTRING	Extracts the substring of string
TRIM	Removes the longest string containing only characters in characters from the start, end, or both ends of string
UPPER	Converts the string to all upper case

OTHER STRING FUNCTIONS

Function	Function Description
ASCII	Returns the numeric code of the first character of the argument
CONCAT	Concatenates the text representations of all the arguments
LEFT	Returns first n characters in the string, or when n is negative, returns all but last ABS(n) characters
REPEAT	Repeats string the specified number of times
REPLACE	Replaces all occurrences in string of substring from with substring to
RIGHT	Returns last n characters in the string, or when n is negative, returns all but first ABS(n) characters
STARTS_WITH	Returns TRUE if string starts with prefix

PATTERN MATCHING

Function	Function Description
LIKE	Returns TRUE if the string matches the supplied pattern
REGEXP_SUBSTR	Returns the substring that matches a POSIX regular expression pattern

DATA TYPE FORMATTING FUNCTIONS

Function	Function Description
TO_CHAR	Converts a timestamp to string according to the given format

DATE/TIME FUNCTIONS

Function	Function Description
DATE_ADD	Add an interval to a timestamp with time zone
DATE_TRUNC	Truncate a timestamp to specified precision
DATEDIFF	Returns the difference between the date parts of two date or time expressions
EXTRACT	Retrieves subfields such as year or hour from date/time values
LOCALTIMESTAMP	Returns the current date and time without time zone
NOW	Returns the current date and time with time zone

CONDITIONAL EXPRESSIONS

Function	Function Description
CASE	Generic conditional expression
COALESCE	Returns the first of its arguments that is not NULL
NULLIF	Returns NULL if both arguments are equal, otherwise returns the first argument
GREATEST	Select the largest value from a list of expressions
LEAST	Select the smallest value from a list of expressions

GENERAL-PURPOSE AGGREGATE FUNCTIONS

Function	Function Description
AVG	Computes the average (arithmetic mean) of all the non-NULL input values
COUNT	Computes the number of input rows in which the input value is not NULL
MAX	Computes the maximum of the non-NULL input values
MIN	Computes the minimum of the non-NULL input values
SUM	Computes the sum of the non-NULL input values

ROW AND ARRAY COMPARISONS

Function	Function Description
IN	Returns TRUE if a left-side value matches any of right-side values
NOT IN	Returns TRUE if a left-side value matches none of right-side values

ER Diagram

Tables Information

eb_pub_accounts
Stores account (sub-organisation) information including industry type, location settings, and organisation configuration.

name	type	description
id	UUID	Primary key. Unique identifier for the account record.
created_at	TIMESTAMP	Timestamp when the account was created.
updated_at	TIMESTAMP	Timestamp when the account was last updated.
account_id	UUID	Account ID
account_name	VARCHAR(255)	Account's name

eb_pub_box_stages
Stores stage definitions within boxes that represent different steps in the lead pipeline.

name	type	description
id	UUID	Primary key. Unique identifier for the stage.
created_at	TIMESTAMP	Timestamp when the stage was created.
updated_at	TIMESTAMP	Timestamp when the stage was last updated.
account_id	UUID	Account ID for user
stage_id	UUID	Stage ID
box_id	UUID	ID of the associated box
stage_name	VARCHAR(255)	Name of the box stage
milestone_id	UUID	Associated milestone ID
is_milestone_activated	BOOLEAN	Flag if the milestone for this stage is activated

eb_pub_boxes
Stores boxes (lead containers/pipelines) that organise leads within an account.

name	type	description
id	UUID	Primary key. Unique identifier for the box.
created_at	TIMESTAMP	Timestamp when the box was created.
updated_at	TIMESTAMP	Timestamp when the box was last updated.
box_id	UUID	Unique identifier for the box
box_name	VARCHAR(255)	Name of the box
account_id	UUID	Account ID for user

eb_pub_call_log
Stores call log records for leads including call duration, outcome, and notes.

name	type	description
id	UUID	Primary key. Unique identifier for the call log
created_at	TIMESTAMP	Timestamp when the call log was created
updated_at	TIMESTAMP	Timestamp when the call log was last updated
account_id	UUID	Account ID for user
date	TIMESTAMP	Date of the call
duration	VARCHAR(50)	Duration of the call
note	TEXT	Call note
created_by_id	UUID	User who created the call log
lead_id	UUID	Lead associated with the call log
outcome_id	UUID	Outcome ID of the call
outcome_title	VARCHAR(255)	Title of the outcome
outcome_is_active	BOOLEAN	Is the outcome active?
outcome_is_default	BOOLEAN	Is the outcome default?

eb_pub_connected_channels
Stores connected channel integrations (Facebook, Instagram, WhatsApp, SMS, etc.) for accounts.

name	type	description
id	UUID	Primary key. Unique identifier for the connected channel
created_at	TIMESTAMP	Timestamp when the channel was connected
updated_at	TIMESTAMP	Timestamp when the channel connection was last updated
name	VARCHAR(255)	Name of the connected channel instance
is_connected	BOOLEAN	Indicates if the channel integration is currently active/connected
connected_at	TIMESTAMP	Timestamp when the channel was connected/authorized
channel_id	UUID	Reference UUID for the main Channel entity
api_channel_type	VARCHAR(50)	A human-readable label for the API Channel Type field
only_phone_registry	BOOLEAN	Indicates if only phone registry is used for this integration
deleted_at	TIMESTAMP	Timestamp the connected channel was marked as deleted
channel_type	VARCHAR(100)	A human-readable label for the channel_type field
account_id	UUID	Account ID

eb_pub_labels
Stores label definitions that can be applied to leads for categorization and value tracking.

name	type	description
id	UUID	Primary key. Unique identifier for the label
created_at	TIMESTAMP	Timestamp when the label was created
updated_at	TIMESTAMP	Timestamp when the label was last updated
label_name	VARCHAR(255)	Label name
account_id	UUID	Account ID for user
category_name	VARCHAR(255)	Category name for this label
type	VARCHAR(50)	Label type (choice mapped from ENUM)
value	DECIMAL(10,2)	Default value assigned to the label

eb_pub_lead_labels
Junction table mapping labels to leads with specific values for each assignment.

name	type	description
id	UUID	Primary key. Unique identifier for the lead-label mapping
created_at	TIMESTAMP	Timestamp when the label was assigned to the lead
updated_at	TIMESTAMP	Timestamp when the mapping was last updated
label_id	UUID	Label ID for this mapping
lead_id	UUID	Lead ID for this mapping
label_value	DECIMAL(10,2)	Label value for this lead
account_id	UUID	Account ID alias

eb_pub_lead_lost_reasons
Stores lost reasons assigned to leads when they are marked as lost, with optional additional information.

name	type	description
id	UUID	Primary key. Unique identifier for the lead lost reason assignment
created_at	TIMESTAMP	Timestamp when the lost reason was assigned
updated_at	TIMESTAMP	Timestamp when the assignment was last updated
additional_info	TEXT	Any free-text extra information user entered for this lost lead
lead_id	UUID	Lead ID for which this lost reason was applied
lost_reason_id	UUID	Lost reason ID associated with this lost lead
reason	VARCHAR(255)	Lost reason lost lead
account_id	UUID	Account ID for user

eb_pub_lead_lost_sub_reasons
Junction table mapping sub-reasons to lead lost reason assignments for more detailed categorisation.

name	type	description
id	INTEGER	Primary key. Unique identifier for the mapping
leadlostreason_id	UUID	ID for the lost reason instance selected for a lead
subreason_id	UUID	Subreason ID (box_subreason) picked by user
sub_reason	VARCHAR(255)	Subreason picked by user for this lost lead
lead_id	UUID	ID of the affected lead
lost_reason_id	UUID	Lost reason ID that this subreason is tied under
account_id	UUID	Account ID for user

eb_pub_lead_milestones
Tracks when leads reach specific milestones in their journey with associated values and stage information.

name	type	description
id	UUID	Primary key. Unique identifier for the milestone history record
created_at	TIMESTAMP	Timestamp when the record was created
updated_at	TIMESTAMP	Timestamp when the record was last updated
value	DECIMAL(10,2)	Numeric milestone value (10,2 precision)
milestone_entered_at	TIMESTAMP	Timestamp when the lead entered this milestone
box_id	UUID	Reference to Box entity (UUID)
lead_id	UUID	Reference to Lead entity (UUID)
milestone_id	UUID	Associated milestone (UUID)
stage_id	UUID	Lead stage for this milestone history (UUID)
lead_assigned_to	UUID	User/Entity assigned to the lead at this milestone
account_id	UUID	Account ID for user

eb_pub_lead_sources
Stores lead source definitions that categorise where leads originated from.

name	type	description
id	UUID	Primary key. Unique identifier for the lead source
created_at	TIMESTAMP	Timestamp when the lead source was created
updated_at	TIMESTAMP	Timestamp when the lead source was last updated
name	VARCHAR(255)	Source display name
type	VARCHAR(50)	Source type (integer enum)
account_id	UUID	Account identifier

eb_pub_leads
Core table storing all lead information including contact details, status, channel, assignments, and classification.

name	type	description
id	UUID	Primary key. Unique identifier for the lead
created_at	TIMESTAMP	Timestamp when the lead was created
updated_at	TIMESTAMP	Timestamp when the lead was last updated
name	VARCHAR(255)	Customer full name
first_name	VARCHAR(100)	Customer first name
last_name	VARCHAR(100)	Customer last name
phone_number_prefix	VARCHAR(10)	Customer phone prefix
phone_number	VARCHAR(20)	Customer phone number
email	VARCHAR(255)	Customer email address
channel	VARCHAR(100)	Source channel label
assigned_to_id	UUID	Assigned user ID
box_id	UUID	Box (container) ID
box_stage_id	UUID	Stage ID within the box
paid_add	BOOLEAN	Flag for paid leads
organic_add	BOOLEAN	Flag for organic leads
in_business_hours	BOOLEAN	Within business hours flag
lead_source_id	UUID	Lead source FK
location_id	UUID	Location FK
channel_id	UUID	Connected channel FK
is_opened	BOOLEAN	Lead opened flag
lead_summary	TEXT	Lead summary text
status	VARCHAR(50)	Status label
practitioner_id	UUID	Practitioner ID
contract_value	DECIMAL(10,2)	Lead contract value (denormalized)
is_closed	BOOLEAN	If the lead is closed
is_spam	BOOLEAN	Is spam flag
classification	VARCHAR(50)	Readable classification label
referring_practitioner_id	UUID	Referring practitioner
referring_practice_id	UUID	Referring practice
call_log_count	INTEGER	Number of call logs
sub_channel_name	VARCHAR(255)	Sub channel name
sub_channel_id	VARCHAR(255)	Sub channel id
account_id	UUID	Alias for account joins

eb_pub_locations
Stores location definitions for accounts that have multi-location features enabled.

name	type	description
id	UUID	Primary key. Unique identifier for the location
created_at	TIMESTAMP	Timestamp when the location was created
updated_at	TIMESTAMP	Timestamp when the location was last updated
location_name	VARCHAR(255)	Name of the location
account_id	UUID	Alias for sub-organization location constraint id, for analytics/account join

eb_pub_milestones
Stores milestone definitions that track key events in the lead journey (e.g., Won, Lost, Appointment Booked).

name	type	description
id	UUID	Primary key. Unique identifier for the milestone
created_at	TIMESTAMP	Timestamp when the milestone was created
updated_at	TIMESTAMP	Timestamp when the milestone was last updated
milestone_name	VARCHAR(255)	Name of the milestone
has_revenue	BOOLEAN	Indicates if the milestone involves revenue
color	VARCHAR(20)	Color code for milestone display
milestone_position	VARCHAR(50)	Ordered position within sub-organization
account_id	UUID	Account ID for user
kind	VARCHAR(50)	Kind/status of milestone (Other, Won, Lost etc.)

eb_pub_practitioner_locations
Junction table mapping practitioners to locations they serve.

name	type	description
id	INTEGER	Primary key ID (autoincrement)
account_id	UUID	Account ID
practitioner_id	UUID	Practitioner UUID
locations_id	UUID	ID referencing location (UUID)

eb_pub_practitioners
Stores practitioner information for accounts (e.g., dentists, doctors, service providers).

name	type	description
id	UUID	Primary key. Unique identifier for the practitioner
created_at	TIMESTAMP	Timestamp when the practitioner was created
updated_at	TIMESTAMP	Timestamp when the practitioner was last updated
practitioner_id	UUID	Practitioner UUID (primary key)
name	VARCHAR(255)	Practitioner name
account_id	UUID	Account ID
is_deleted	BOOLEAN	If practitioner is marked as deleted

eb_pub_referral_practices
Stores referring practice information for referral tracking.

name	type	description
id	UUID	Primary key. Unique identifier for the referring practice
created_at	TIMESTAMP	Timestamp when the referring practice was created
updated_at	TIMESTAMP	Timestamp when the referring practice was last updated
deleted_at	TIMESTAMP	Timestamp when the practice record was deleted, if applicable
name	VARCHAR(255)	Name of the referring practice
account_id	UUID	Account ID for user

eb_pub_referral_practitioner_practices
Junction table mapping referring practitioners to their associated practices.

name	type	description
id	UUID	Primary key. Unique identifier for the mapping
created_at	TIMESTAMP	Timestamp when the mapping was created
updated_at	TIMESTAMP	Timestamp when the mapping was last updated
deleted_at	TIMESTAMP	Timestamp when the association was deleted, if applicable
is_approved	BOOLEAN	If the practitioner-practice association is approved
referring_practice_id	UUID	UUID of the referred practice
ref_practitioner_id	UUID	UUID of the referred practitioner
account_id	UUID	Account ID for user

eb_pub_referral_practitioners
Stores referring practitioner information for referral tracking.

name	type	description
id	UUID	Primary key. Unique identifier for the referring practitioner
created_at	TIMESTAMP	Timestamp when the referring practitioner was created
updated_at	TIMESTAMP	Timestamp when the referring practitioner was last updated
deleted_at	TIMESTAMP	Timestamp when the practitioner record was deleted, if applicable
name	VARCHAR(255)	Name of the referring practitioner
account_id	UUID	Account ID for user

eb_pub_referral_treatments
Stores treatment types that can be associated with referrals.

name	type	description
id	UUID	Primary key. Unique identifier for the referral treatment
created_at	TIMESTAMP	Timestamp when the treatment was created
updated_at	TIMESTAMP	Timestamp when the treatment was last updated
deleted_at	TIMESTAMP	Timestamp when the treatment record was deleted, if applicable
treatment_name	VARCHAR(255)	Name of the treatment being referred
is_active	BOOLEAN	Marks if the treatment is currently active
treatment_label_id	UUID	Label/category for the treatment
account_id	UUID	Account ID for user

eb_pub_users
Stores user information and their access/role within accounts.

name	type	description
id	UUID	Primary key. Unique identifier for the user access record
created_at	TIMESTAMP	Timestamp when the user access was created
updated_at	TIMESTAMP	Timestamp when the user access was last updated
access_id	UUID	Access ID for the user
account_id	UUID	Account ID for user
user_id	UUID	User ID
username	VARCHAR(150)	User's username
email	VARCHAR(255)	User's email address
first_name	VARCHAR(100)	User's first name
middle_name	VARCHAR(100)	User's middle name
last_name	VARCHAR(100)	User's last name
full_name	VARCHAR(300)	User's full name
is_deleted	BOOLEAN	Flag indicating if the user is deleted
is_active	BOOLEAN	Flag indicating if the user is active
role	VARCHAR(50)	Role for the organization user

Boxly is here to help you work smarter, not harder - let’s keep streamlining your lead management together!

Help center

Help center

BI SQL API Developer Guide

Boxly BI SQL API Developer Guide

Overview

Connection Details

SQL Commands Reference

SELECT - Retrieve Tables Information

SELECT - Retrieve Data

JOINS

Correct Pattern

Wrong Pattern

Best For

Limitations

SQL Functions & Operators

COMPARISON OPERATORS

COMPARISON PREDICATES

MATHEMATICAL FUNCTIONS

STRING FUNCTIONS AND OPERATORS

Function

Function Description

OTHER STRING FUNCTIONS

PATTERN MATCHING

DATA TYPE FORMATTING FUNCTIONS

DATE/TIME FUNCTIONS

CONDITIONAL EXPRESSIONS

GENERAL-PURPOSE AGGREGATE FUNCTIONS

ROW AND ARRAY COMPARISONS

ER Diagram

Tables Information