--- title: "BI SQL API Developer Guide" description: "Boxly BI SQL API Developer Guide" url: https://help.boxly.ai/en/articles/10182774-bi-sql-api-developer-guide collection: "API" updated: 2026-10-23 language: en --- # BI SQL API Developer Guide Boxly BI SQL API Developer Guide > **Info:** **Note:** The BI SQL API is accessible only to accounts on the [Pro Plan](https://www.boxly.ai/pricing) ## 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. > **Info:** **Important**: The data that you receive is not live. It is refreshed daily around 1 - 2 AM UTC. > **Success:** **_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](http://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**](http://leads.id)**,** [**leads.name**](http://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 * * * ![](https://staticfiles.gleap.io/ghelparticle/K1qYLnQ1PeuAnDWd6KawNc1FJ6OyoM4N8Mk1laHVeti6T0CbjH7BOtiHFy0YUx8JndVw3lLfptA.png) ## 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!