Note Regarding BriteCore Support

BriteCore will not teach SQL to carriers as part of standard support. We will assist with using the feature, and the views, but debugging SQL is the responsibility of the user. Please review the article on SQL References for online learning materials.

Overview

The SQL Editor is a powerful feature within the BriteCore ecosystem, designed to empower expert users at insurance carriers to take full control of their analytics and reporting capabilities. It is part of our broader Self-Service Reporting initiative, enabling business intelligence teams, IT specialists, and data-driven professionals to query their datasets directly, safely, and efficiently.

Traditionally, carriers have had to rely heavily on BriteCore support or engineering teams to generate custom reports or extract critical insights. With the SQL Editor, this dependency is removed. Advanced users can now write, run, and refine their own SQL queries in real time, reducing turnaround times and improving data-driven decision-making. Reports created with the SQL Editor can be made available in the Report List, like any other report type.

The feature offers direct access to carrier-specific datasets while maintaining security and stability by exposing curated views instead of raw database tables. This strikes a balance between power and protection, allowing unrestricted exploration within safe boundaries.

Value Proposition

The SQL Editor was built to help carriers put data in the hands of the people who need it most: the experts within their own organization. Whether it’s claims management, underwriting optimization, or profitability analysis, having direct access to real-time, structured data opens up new opportunities for responsiveness and insight.

Benefits:

• Empowers carriers to be data-driven: Analysts, actuaries, and developers can write meaningful reports that drive strategic decision-making.
• Reduces dependency on vendor support: No need to wait days for a custom report; internal users can develop queries on the fly.
• High performance and safety: Uses indexed and optimized read-only views (e.g., v_claims, v_payments) to balance power with security.
• Abstracts away complexity: The underlying BriteCore schema is hidden behind well-named views, making it easier to write queries without understanding the full backend.
• Supports collaboration: Saved queries can be shared and reused across departments, promoting alignment.
• Security built-in: Access is strictly read-only and controlled by role-based permissions.

Ultimately, this feature helps carriers become more self-sufficient, more agile, and more capable of extracting meaningful insights from their data.

Target Users

The SQL Editor is intended for:

Access to the SQL Editor is permission-controlled. Only users granted access by an administrator will see the feature in their interface.

Getting Started

Accessing the SQL Editor

From the main BriteCore interface:

• Navigate to the Reports section in the left-hand navigation menu.

• Locate and select on SQL Editor to launch it.

Interface Overview

Once inside the SQL Editor, you’ll see a development environment tailored for querying structured data. The m_inforce_policies view is a materialized view for inforce policies. Upon your first access to a particular date range, the table is generated and stored for quick reuse in future queries. In this way, queries get faster as the system materializes more data.

Left Sidebar: Shows all available views (e.g., m_inforce_policies, v_claims). Selecting a view reveals its fields.

Main Editor Panel: Where you write and execute your SQL. Syntax highlighting and formatting assist query composition.

Results Grid: Displays the output below the editor pane, tabular-style.

Toolbar:

• 🆕 Create a new report tab
• 📂 Open an existing saved query
• 💾 Save your query (only after running it successfully)
• ▶️ Execute (Run) the query
• ⚙️ Modify the output destination or attach the report to saved dashboards
• 🔄 Export the query to JSON for transfer between environments
• 📥 Execute and immediately download the resulting data
• Parameter Input: Specify required variables like AsOfDate for dynamic queries

Notes:

• Use -- or /* comment */ to add comments in your SQL code
• Queries executed interactively are limited to 10,000 rows, while background jobs have no row limits

Here’s an example of a multi-view join showing policy and address data in action:

In this query, UUIDs are resolved through joins to retrieve readable fields like policy_number, term_effective_date, and full_address.

Starting a new or opening an existing report

In the upper left, select the folder with the + on it to create a new report or tab on your existing report. Alternatively, select on the file folder icon to open a file selector for reports on the existing site. Select the report name and Select Report to get that report into the editor.

Creating the SQL for the Report

BriteCore is creating a set of curated example reports from which to pull when you are getting started. Those are found in this documentation in Curated SQL Templates documentation. The first step is to figure out what type of report you want. Is your report related to policies, claims, premiums, accounting, or payments?

Let’s start with a policy based report. The basis for policies is the materialized view m_inforce_policies. When you click on the left available views, you can see what fields make up m_inforce_policies. These include revision_id, policy_type_id, primary_insured_id, agency_id and inforce_premium. A quick select command shows the contents of the first few rows of the result. They are mostly IDs, which are great for computers but hard for humans to read. Let’s see how we can find out the data being referenced.

Let’s see how using the right JOIN statements can find the records we want and allow we humans to read the report. Here is the first curated SQL template in our list, Simple Policies in Force.

 -- pick ONE address per policy (here: the first alphabetically)
SELECT
    t.state,
    t.policy_type,
    r.policy_number,
    MIN(l.full_address) AS full_address
FROM m_inforce_policies  AS i
JOIN v_policy_types      AS t  ON t.policy_type_id = i.policy_type_id
JOIN v_revisions         AS r  ON r.revision_id    = i.revision_id
JOIN v_properties        AS l  ON l.revision_id    = i.revision_id
GROUP BY
    t.state,
    t.policy_type,
    r.policy_number;

Here is the report in the SQL Editor, and the result after running it. See that the joins have chosen the rows we care about, the select statements are which fields to include in the report, and the GROUP BY statement aggregates multiple rows with the same field values. You can see that we have a nice listing of states, policy types, policy numbers and the full address.

Modifying a curated example report

What other information is available in the views I have present? How can I enhance the curated example? This method is powerful because you don’t have to start from scratch. In the left column, i can see the v_properties, that I have aliased as l, has latitude and longitude among other fields. All I have to do to add this information is to add l.latitude and l.longitude to the select statement.

Running a Report

With a report in the editor, when you execute the query with the ▶️ Execute (Run) control, the results will appear in the bottom pane of the screen. During interactive runs, there is an implicit limit of 10,000 rows. If the report exceeds 180 seconds, it will automatically continue running in the background, and the user interface will indicate elapsed time.

Saving a report

To save the query for your use only, choose a name in the naming box, select the gear icon and make sure the format is what you want (excel or PDF) and that the pulldown says unpublished. Once the query runs successfully you can publish the report, making it available to others in your organization in the report list area. After that successful run on all tabs, push the floppy drive icon to save the file to the location specified in the settings earlier.

Moving a report

If you have created a report on one system that you wish to move to another system, use the export JSON function. By choosing export and keeping track of the created JSON file, you can import that file and thus the report into a different system easily.