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.

Important Notice

⚠️ Deprecation Notice - Wednesday, January 7th, 2025

Fields without aliases (typically prefixed with key_ or using internal naming conventions) will be deprecated on Wednesday, January 7th, 2025.

Please migrate your queries to use the recommended field names as documented in each table's documentation. Since metadata is not available through the SQL API, this documentation is the primary reference for field naming.

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

Active Fields (Recommended)

• 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)

Deprecated Fields (Do Not Use After January 7th, 2025)

• Fields with key_ prefix (e.g., key_project_id)
• Fields using internal naming (e.g., employee_name instead of employee)