Advanced custom entities

What an advanced custom entity is

A custom entity comes in two shapes. A simple one maps a single table. An advanced one spans several, so a record that only exists as a join can be modelled at all.

The dashboard calls these simple and advanced custom entities. Internally — in clivly.config.ts, in validation errors and in the SDK — the mechanism is a projection, and that is the word you will see in code.

• A projection derives an advanced custom entity from a base table plus explicit joins — an Enrollment built from enrollments, students and courses, or an account whose display name lives on the client row.
• Four parts: a base table, the joins that reach the rest, the field bindings that pick columns, and an identity that makes each output row addressable.
• Refer to the base table as base and every join by its key. Two bindings can pull the same column name from different tables, so references are always qualified.
• Custom entities only — Contacts, Companies and Deals stay one-table entities.

Defining one

Declared in clivly.config.ts like any other entity. clivly push-schema creates the object type and its mapping.

// An advanced custom entity is a `custom` entity with a `projection`.
// `source` is the base table; `fields` is derived from the bindings.
client_accounts: {
concept: "custom",
source: "accounts",              // === projection.baseTable
objectType: { name: "Client account" },
projection: {
  version: 1,
  kind: "projection",
  entityKey: "client_accounts",
  baseTable: "accounts",
  joins: [
    {
      key: "cl",
      table: "clients",
      type: "inner",
      on: [
        {
          left: { table: "cl", column: "id" },
          op: "eq",
          right: { table: "base", column: "client_id" },
        },
      ],
    },
  ],
  fieldBindings: {
    client_name: { from: { table: "cl", column: "full_name" } },
    currency: { from: { table: "base", column: "currency" } },
  },
  identity: { kind: "base_pk" },
},
}

• fields still exists on the parsed config, but you no longer write it — defineClivlyConfig fills it in from fieldBindings, qualified as ref.column. Supply a conflicting fields and it throws rather than silently ignoring your copy.
• Declaring objectType means the custom entity is created for you; you never paste a generated id back into config.

Choosing an identity

Every output row needs a stable id. Picking the wrong one is how two records silently collapse into one.

Base primary key — { kind: 'base_pk' } — the default. One record per base row. Rejected if the base table's primary key was not discovered, or if it is composite.

A single column — { kind: 'column', from: … } — when some other column is already unique per row.

Composite — { kind: 'composite', parts: [...] } — when the unique thing is a combination, typically after a fan-out join. Needs at least two parts.

Composite parts are concatenated with CONCAT_WS('::', …), which skips nulls — so a nullable part would let two different rows collapse onto the same id. Every part must be NOT NULL and non-text; a text part containing :: could collide with a different pair. Both are rejected up front rather than at sync time.

Fan-out

A join that does not cover a unique key of the joined table matches many rows per base row. That is allowed, within limits.

• Once a join fans out, the base table's primary key is no longer unique per output row — so base_pk identity is rejected on a fanning join, and the identity must include a unique key of the fanning table.
• At most one fan-out join per object.
• Validation is fail-closed: a table whose discovery payload lacks columnsMeta or uniqueConstraints is rejected rather than assumed safe, because identity uniqueness cannot be proven without them.

Validate in CI

A projection is checked against the last reported schema, so a config that looks fine locally can still be wrong after a migration.

import { validateEntitiesConfig } from "clivly/sdk";
import { discoverFromDrizzle } from "clivly/drizzle";
import * as schema from "./lib/schema";
import entities from "./clivly.config";

const { valid, errors } = validateEntitiesConfig(
entities,
discoverFromDrizzle(schema)
);

if (!valid) {
for (const e of errors) console.error(`${e.path}: ${e.message}`);
process.exit(1);
}

validateEntitiesConfig is Tier 1, exported from clivly/sdk. It reports every error at once rather than stopping at the first, and covers join refs, identity safety and fan-out.

Current limits

Worth knowing before you model around them.

• Authoring is behind a server flag. Set CLIVLY_PROJECTION_MAPPINGS=true to create one. Reading and syncing an existing advanced custom entity is never gated — only creating one.
• inner and left joins only, and = is the only ON operator.
• No aggregation — a field is one column, never a COUNT or SUM.
• Filters apply to the base table.
• Saving is not syncing. Your app runs the queries, so a saved entity stays empty until its source.custom entry is in your clivly.config.ts. The builder shows you the exact snippet, with the right slug, right after you save.
• Preview in cloud mode needs introspector: drizzleIntrospector(db) — your app runs the query, because Clivly never connects to your database. Embedded apps need no setup.