Insights API Datamodel

This documentation covers all tables available through the Simplicate SQL API, which is accessible via a PostgreSQL protocol compatible interface.

Purpose

These tables are designed to connect Simplicate data to BI tooling such as Power BI, Tableau, and other analytics platforms.

Available Tables

Dimension Tables

Table	Description
api_administrations	Company entities/business units
api_employees	Employee data
api_teams	Team data
api_teams_to_employees_bridge	Team-Employee relationships
api_relations	Customers and contacts
api_projects	Project data
api_project_services	Project service data
api_project_assignments	Project assignments
api_sales	Sales/opportunities
api_invoices	Invoice headers
api_invoicelines	Invoice line items
api_item_types	Hour types and cost types
api_revenuegroups	Revenue group categories
api_workflows	Workflow tasks

Fact Tables

Table	Description
api_time_registrations	Individual time entries
api_employee_timesheet	Daily timesheet aggregates
api_project_assignments_facts	Assignment hours and values over time
api_project_service_date_facts	Daily service financials
api_project_service_costs	Cost registrations
api_project_service_hour_budgets	Hour budgets per service
api_project_service_cost_budgets	Cost budgets per service

Custom Fields Tables

Table	Description
api_relation_custom_fields	Relation custom fields
api_project_custom_fields	Project custom fields
api_sale_custom_fields	Sale custom fields
api_employee_custom_fields	Employee custom fields

Field Naming Conventions

• Use descriptive names without key_ prefix
• Primary keys: {entity}_id (e.g., project_id, employee_id)
• Foreign keys: {related_entity}_id (e.g., relation_id, administration_id)

Data types

Integer columns — including all ID columns (primary keys and foreign keys) — are stored as integers in the Simplicate database, but the Insights API exposes them over the PostgreSQL wire protocol as numeric. Depending on your client or BI tool, these values may be displayed with a decimal point (for example as a float or fixed-precision decimal) rather than as a plain integer. The underlying value is identical — only the display differs.

If your tool requires an integer type, cast the column explicitly:

SELECT administration_id::bigint FROM api_administrations;