Projections

A projection maps dataset rows to objects, relationships, or time-series readings in your domain model. It runs automatically when the source dataset updates.

Define a projection

Export a projection from your project's projections/ folder. Choose its dataset and object type, then map object properties to dataset columns with .properties(...).

Each row represents one object. Map its primary property to a column with a unique, nonblank ID.

import { defineProjection } from "@sixb/core"
import { customers } from "../datasets/customers"
import { Customer } from "../ontology/customer"

export const customerProjection = defineProjection("customers", Customer)
  .fromDataset(customers)
  .properties({
    id: "customer_id",
    name: "contact_name",
    email: "contact_email",
  })

Use a pipeline first if the rows need cleaning or reshaping. Removing a dataset row withdraws its projected values and links, but does not delete the object.

If the object type defines vector search profiles, projections also refresh their embeddings in the background. Objects become searchable once the embeddings are ready; generation counts toward the project's AI limits.

Add relationships

Use .withLinks(...) when a row contains another object's ID. Here, customer_id identifies the customer linked to each invoice through the customer link defined on Invoice. A blank foreign key creates no link.

import { defineProjection } from "@sixb/core"
import { invoices } from "../datasets/invoices"
import { Customer } from "../ontology/customer"
import { Invoice } from "../ontology/invoice"

export const invoiceProjection = defineProjection("invoices", Invoice)
  .fromDataset(invoices)
  .properties({
    id: "invoice_id",
    number: "invoice_number",
  })
  .withLinks({
    customer: {
      link: Invoice.l.customer,
      sourceField: "customer_id",
      target: Customer,
    },
  })

Point to members

When a column names a project member by email, map a ref.user() property with userRef.fromEmail(column). The property holds that member's user reference, so queries can match it with currentUser() and expand it into the member's profile.

import { defineProjection, userRef } from "@sixb/core"
import { calendarEvents } from "../datasets/calendar-events"
import { CalendarEvent } from "../ontology/calendar-event"

export const calendarEventProjection = defineProjection("calendar-events", CalendarEvent)
  .fromDataset(calendarEvents)
  .properties({
    id: "event_id",
    organizerEmail: "organizer_email",
    organizer: userRef.fromEmail("organizer_email"),
  })

• The column must be a string column, and the property a single ref.user() property that is not required.
• Emails match regardless of case. A suspended member still matches.
• A row whose email no member has leaves the property empty. Map the column to a string property as well, like organizerEmail here, to keep the address.
• A row is matched when a run projects it. Someone who joins later is matched the next time a dataset version changes their row.

When the column holds member user ids instead, such as the owner of a personal connection, map it with userRef.fromId(column). The same rules apply, and an id no member has leaves the property empty.

Project relationships from a separate dataset

For many-to-many relationships, target a link instead of an object type. Each row identifies the two objects to connect. The source and target columns must contain their primary IDs as strings.

import { defineProjection } from "@sixb/core"
import { projectMembers } from "../datasets/project-members"
import { Project } from "../ontology/project"

export const projectMembersProjection = defineProjection(
  "project-members",
  Project.l.members
)
  .fromDataset(projectMembers)
  .sourceField("project_id")
  .targetField("employee_id")

Change the source dataset

To read a projection from a different dataset, point .fromDataset(...) at it and keep the projection ID. The next run rebuilds the projection from the new dataset: values from rows it no longer contains are withdrawn, and app edits are kept.

Source updates and app edits

By default, an app edit to a projected property takes precedence over later source updates until the app resets that edit.

To use the most recent value instead, append .resolveConflicts(...) to the object projection:

.resolveConflicts({
  strategy: "mostRecent",
  sourceTimestamp: "updated_at",
})

updated_at must be a non-null timestamp column containing the source record's update time, not its ingestion time. For each property, a source value replaces an app edit when its timestamp is equal to or newer than the edit's timestamp.

For timestamped readings, see Telemetry projections.

Runs

Each committed dataset version starts one run per projection that reads it. A run that fails for a transient reason, such as a lost database connection, is retried. While it retries, the run is running and its error says why the last attempt failed. The error is cleared if the run succeeds.

A run usually applies only the rows that changed since the version the projection last applied. On DuckLake, an object projection whose primary property maps to the dataset's primary key reads just those changes, at a cost that follows their number; other projections compare both versions in full.

A run that would give one unique value to two of its objects, or to an object while another holds it, fails without writing anything: objects keep their previous values. With the default conflict strategy, a value an app edit set stays with its object until the edit is reset.

If a newer dataset version is committed before an older run starts or retries, the older run ends superseded without writing anything, because its output would be stale. This applies to object and link projections only: telemetry runs append readings, so every version is projected. The latest run shown for a projection is never a superseded run.