Create analytics projections

Use a projection when analysts need approved fields from an entry or memory. A projection selects one exact entry contentType or memory kind, evaluates deployment-owned Rego, and writes typed rows for each matching resource. By default, a projection writes one row per resource. Set rows: many to write one row per item, such as each message or tool call in an entry.

Create a projection manifest

Create support-ticket-projection.yaml:

apiVersion: memory-service/v1alpha1
kind: AnalyticsProjection
metadata:
  name: support_ticket_v1
spec:
  resource: entry
  selector:
    contentType: support-ticket/v1
  columns:
    details:
      type: json
      nullable: false
      clickhouse:
        maxDynamicPaths: 256
        maxDynamicTypes: 16
    outcome:
      type: string
      nullable: false
    result:
      type: variant
      variants: [string, int64, bool]
      nullable: false
    latency_ms:
      type: uint64
      nullable: true
  projectionRego: |
    package memoryservice.analytics

    ticket := input.content[0]

    output := {
      "details": ticket.details,
      "outcome": ticket.outcome,
      "result": {"type": "string", "value": ticket.outcome},
      "latency_ms": object.get(ticket, "latencyMs", null),
    }

An entry’s content value is an array of content blocks. The projection above reads the first block and produces one row per entry. To produce a row for every block, or for every item nested inside a block, write several rows per resource.

Write several rows per resource

Some content holds a list that analysts want to filter, join, or aggregate item by item. Examples include the messages in a chat memory entry or the tool calls in a history entry. Set spec.rows to many and return an array from output. Each array element becomes one row.

Create support-ticket-steps-projection.yaml:

apiVersion: memory-service/v1alpha1
kind: AnalyticsProjection
metadata:
  name: support_ticket_steps_v1
spec:
  resource: entry
  selector:
    contentType: support-ticket/v1
  rows: many
  columns:
    action:
      type: string
      nullable: false
    agent:
      type: string
      nullable: true
    duration_ms:
      type: uint64
      nullable: true
  projectionRego: |
    package memoryservice.analytics

    ticket := input.content[0]

    output := [row |
      some step in object.get(ticket, "steps", [])
      row := {
        "action": object.get(step, "action", null),
        "agent": object.get(step, "agent", null),
        "duration_ms": object.get(step, "durationMs", null),
      }
    ]

This entry produces three rows in support_ticket_steps_v1:

{
  "contentType": "support-ticket/v1",
  "content": [
    {
      "outcome": "resolved",
      "steps": [
        { "action": "triage", "agent": "router", "durationMs": 120 },
        { "action": "lookup-order", "agent": "orders", "durationMs": 340 },
        { "action": "reply", "agent": "writer", "durationMs": 95 }
      ]
    }
  ]
}

The rows value accepts one (the default) or many. It is part of the immutable manifest, so switching an existing projection to many requires a new projection name.

Multi-row tables add two processor-owned columns:

row_index and row_count are reserved column names for every projection. Rows are keyed by (exporter_id, resource_id, row_index), so a query can order the rows for one resource by row_index. When the Rego skips items, row_index counts only the rows it returned. Declare a column when analysts need the item’s original position.

Every version of a resource replaces all of its earlier rows. The _current and _all views return the rows from the newest version only. If an update returns fewer rows than before, the extra rows from the earlier version disappear from both views. An empty array writes no visible rows and hides the rows of earlier versions. A deleted resource leaves a single tombstone row in the _all view.

Inside an array comprehension, a reference to a missing field such as step.action makes Rego skip that element without an error. Read fields with object.get(..., null) instead. A missing required value then fails validation rather than silently dropping a row.

The processor validates all rows before writing any of them. If one row has a missing column or an invalid value, the whole resource is recorded in projection_failures and none of its rows are written. A single resource can produce at most 1024 rows.

A memory projection that sets rows: many must define projectionRego.

Choose stable SQL names

metadata.name is the exact physical table name unless the manifest sets spec.table (see Share a table across content-type versions). Each key in spec.columns is the exact SQL column name. The processor does not sanitize or prefix either value.

Table and column names must match ^[a-z][a-z0-9_]{0,62}$. Projection table names cannot use these processor-owned names:

• schema_versions, lifecycle_events, resources, and deletion_fences
• deletion_fence_writer, conversations, conversation_lineage, entries, and memories
• projection_registry, retention_policy, and projection_failures
• purge_subjects and purge_queue

Names ending with _current or _all are also reserved for generated views. The processor validates these rules before creating any projection relations.

Use a visible version suffix when the schema changes in a way a projection chain can’t express. For example, replace support_ticket_v1 with support_ticket_v2. A manifest is immutable after the processor registers its digest.

Multiple projections can select the same contentType or kind. Give each projection a different table name.

Choose column types

Portable types work across analytics providers that advertise the base projection capability.

ClickHouse projections also support these provider-specific types:

A Variant must declare at least two portable alternatives. The alternatives must map to distinct ClickHouse types and cannot include more than one numeric type. A Variant cannot contain another Variant.

Native JSON supports arbitrary nesting. Use clickhouse.maxDynamicPaths and clickhouse.maxDynamicTypes to bound changing paths and types. The defaults are 256 paths and 16 types. The processor accepts at most 1024 paths and 32 types.

Understand the Rego contract

Entry projections receive these input fields:

• resource
• contentType
• channel
• content

Memory projections receive these input fields:

• resource
• kind
• revision
• value
• attributes

The output rule must return an object whose keys are columns of the projection’s table. For a rows: many projection, output must return an array of such objects. The processor rejects unknown keys, invalid types, and missing or null values for columns with nullable: false. It writes NULL for an omitted column with nullable: true.

A memory projection can omit projectionRego. In that case, the processor maps persisted MemoryKindVersion attributes to columns with matching names.

Load projection manifests

The distributed image includes individual example-app projection files under /etc/memory-service/clickhouse/projections. Load the whole directory or select only the files needed by your deployment:

The message tables store one row for each message in a chat-memory entry, in content order. spring_ai_messages_v1 stores each message’s role and text. lc4j_messages_v1 stores the LangChain4j message_type. vercelai_messages_v1 stores the model message role. Both of those tables also store the message text, the tool_names that the message calls or answers, and the complete message as native JSON.

The history event tables store one row for each recorded event of an agent response. Each row includes the entry role, event_type, streamed text, and, for tool events, tool_call_id, tool_name, tool_input, and tool_output. tool_input and tool_output hold canonical JSON strings; the LangChain4j table decodes tool arguments and results that contain JSON. Completion events add model_name, finish_reason, input_tokens, output_tokens, and total_tokens. In the Vercel AI table, step events also report usage, so filter on event_type = 'ChatCompleted' before you sum tokens. The per-entry history tables remain available for entry-level counts.

The repository’s compose.yaml loads the entire directory in projected mode. For another container deployment, set MEMORY_SERVICE_CLICKHOUSE_PAYLOAD_MODE=projected and MEMORY_SERVICE_CLICKHOUSE_PROJECTION_PATH=/etc/memory-service/clickhouse/projections on the processor.

To load selected bundled projections, provide their file paths as a comma-separated environment value. This example enables the LangChain4j history and cognition memory projections:

MEMORY_SERVICE_CLICKHOUSE_PAYLOAD_MODE=projected
MEMORY_SERVICE_CLICKHOUSE_PROJECTION_PATH=/etc/memory-service/clickhouse/projections/history-lc4j.yaml,/etc/memory-service/clickhouse/projections/cognition-memory.yaml

To load your own projection directory, create compose.analytics-projected.yaml next to it:

services:
  clickhouse-processor:
    environment:
      MEMORY_SERVICE_CLICKHOUSE_PAYLOAD_MODE: projected
      MEMORY_SERVICE_CLICKHOUSE_PROJECTION_PATH: /etc/memory-service/projections
    volumes:
      - ./projections:/etc/memory-service/projections:ro

Apply the override:

docker compose \
  -f compose.yaml \
  -f compose.analytics-projected.yaml \
  up -d --force-recreate clickhouse-processor

Projected mode requires at least one kind: AnalyticsProjection resource. A directory path loads the *.yaml and *.yml files directly inside that directory. Other resource kinds and subdirectories are ignored.

To load multiple files or directories from the CLI, repeat --projection-path:

memory-service process clickhouse \
  --projection-path /etc/memory-service/projections/support-ticket.yaml \
  --projection-path /etc/memory-service/projections/chat-message.yaml

For the environment variable, use a comma-separated list:

MEMORY_SERVICE_CLICKHOUSE_PROJECTION_PATH=/etc/memory-service/projections/support-ticket.yaml,/etc/memory-service/projections/chat-message.yaml

Write a matching entry

Create a conversation and capture its ID:

CONVERSATION_ID=$(curl -fsS -X POST http://localhost:8082/v1/conversations \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: agent-api-key-1' \
  -H 'X-User-ID: analytics-projection' \
  --data '{"title":"Projection example"}' | jq -r .id)

Append a matching journal entry:

curl -fsS -X POST "http://localhost:8082/v1/conversations/${CONVERSATION_ID}/entries" \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: agent-api-key-1' \
  -H 'X-User-ID: analytics-projection' \
  --data '{
    "channel": "journal",
    "contentType": "support-ticket/v1",
    "content": [{
      "outcome": "resolved",
      "latencyMs": 17,
      "details": {"context": {"region": "us-east"}}
    }]
  }'

The processor creates support_ticket_v1, support_ticket_v1_all, and support_ticket_v1_current. Continue with Query analytics data to inspect the row.

Share a table across content-type versions

When a producer bumps its content-type version, you don’t need a new table. Projection manifests can form a chain, like database migrations. The first manifest defines the table. Each later manifest sets spec.previous to the name of the manifest before it and writes to the same table, so queries keep reading one table and its _current and _all views.

This chain starts a version-neutral support_ticket_steps table, adds the support-ticket/v2 content type with the same logic, and then replaces the logic for both versions to fill a new column:

apiVersion: memory-service/v1alpha1
kind: AnalyticsProjection
metadata:
  name: support_ticket_v1_steps
spec:
  resource: entry
  table: support_ticket_steps
  selector:
    contentType: support-ticket/v1
  rows: many
  columns:
    step: { type: string, nullable: false }
  projectionRego: |
    package memoryservice.analytics
    output := [{"step": s.name} | some s in input.content]
---
apiVersion: memory-service/v1alpha1
kind: AnalyticsProjection
metadata:
  name: support_ticket_v2_steps
spec:
  resource: entry
  previous: support_ticket_v1_steps
  projectionRegoFrom: support_ticket_v1_steps
  selector:
    contentType: support-ticket/v2
---
apiVersion: memory-service/v1alpha1
kind: AnalyticsProjection
metadata:
  name: support_ticket_steps_duration
spec:
  resource: entry
  previous: support_ticket_v2_steps
  addColumns:
    duration_ms: { type: uint64, nullable: true }
  projectionRegoReplaces: support_ticket_v1_steps
  projectionRego: |
    package memoryservice.analytics
    output := [{"step": s.name, "duration_ms": object.get(s, "durationMs", null)} | some s in input.content]

The first manifest is an ordinary projection. Set spec.table to give the table a version-neutral name; without it, the table is metadata.name. A later manifest inherits the table, columns, and rows setting, so it must not declare table, columns, or rows. Each later manifest does at least one of these things:

Follow these rules:

• Keep every manifest of the chain configured. A fresh install builds the table by applying the chain in order. Logic for content types that are no longer produced stays idle.
• Keep the chain linear. Two manifests can’t name the same previous manifest.
• projectionRegoFrom and projectionRegoReplaces must name an earlier manifest in the same chain. projectionRegoFrom shares that manifest’s logic rather than copying it. projectionRegoReplaces replaces the logic that manifest uses. If that manifest reuses another manifest’s logic through projectionRegoFrom, every content type sharing that logic switches unless you add a selector.
• A content type can use only one logic in a chain. Use projectionRegoReplaces to change it.
• Never edit, reorder, or remove a manifest after the processor registers it. The processor refuses to start if the registered manifests aren’t the beginning of the configured chain.
• Upgrade every processor that writes to the same ClickHouse database before you add a manifest to a chain. Processors from releases without projection chains refuse to start once a table they write has more than one registered manifest, with an error like projection table "support_ticket_v1" is registered with a different immutable manifest. They also can’t read chain manifests registered under their own exporter ID.

To keep an existing table, add a manifest whose spec.previous names its projection. The table keeps its name and rows. To move to a version-neutral name, start a new chain with spec.table and run a projection replay to fill it. The old table keeps working until readers switch, and you drop it manually afterward.

With --schema-mode=manage, the processor adds new columns with ALTER TABLE ... ADD COLUMN when it starts and recreates the table’s views. Existing rows keep NULL in a new column, and rows written by earlier logic keep working, until you run a projection replay with logic that fills it. Changing a column type, renaming or dropping a column, or changing rows still requires a new table.

To see each table’s chain, its combined columns, and which manifest’s logic projects each content type, run:

memory-service process clickhouse-projections --projection-path /etc/memory-service/projections

Add --format json for machine-readable output.

Handle failures and schema changes

Invalid projection output writes a payload-free row to projection_failures. The default continue-generic policy keeps the generic export moving. Set --projection-failure-policy=stop when any projection failure must stop the processor.

To rescan existing entries and memories after adding a new projection version or a manifest that replaces logic, set a new --projection-replay-id. The replay ID must change for each requested rescan.

For deployment settings, see the ClickHouse processor configuration reference.