> ## Documentation Index
> Fetch the complete documentation index at: https://trytilde.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Sources and models

> Feed an ontology with pipelines, connections, custom streams, and Tilde's own chat events, then shape the raw records into tables with SQL models.

Data reaches an ontology in two steps. **Pipelines** bring raw records in, one table per stream. **Models** are SQL that turns those records into the tables your objects read.

## Declare pipelines

Declare pipelines once, in `ducktail.toml` at the root of the project repository. Each `[[pipeline]]` has a `name`, a `kind`, and the streams it writes.

```toml ducktail.toml theme={"system"}
# Poll Linear through the environment's `linear` connection.
[[pipeline]]
name = "linear"
kind = "linear"
connection = "linear"
streams = ["issues", "comments", "projects"]
interval = "60s"

# GitHub's webhooks, delivered through the `github` connection, plus a history backfill.
[[pipeline]]
name = "github"
kind = "webhook"
connection = "github"
streams = ["pull_requests", "other"]
key_header = "x-github-delivery"
route = { header = "x-github-event", streams = { pull_request = "pull_requests" }, fallback = "other" }
backfill = { provider = "github", repositories = ["acme/app"], start = "2025-01-01" }

# Your own PostgreSQL database, by logical replication. The password is an environment secret.
[[pipeline]]
name = "app"
kind = "postgres"
connection = "host=db.example.com user=repl password=${APP_DB_PASSWORD} dbname=app sslmode=require"
tables = ["public.users", "public.orders"]

[backfill]
start = "90d"    # where first runs start: a date, an RFC 3339 time, or a duration before now
```

| Kind | Brings in |
| - | - |
| `linear`, `sentry`, `cloudwatch` | Polled API records. |
| `plaid`, `quickbooks`, `xero` | Bank accounts and transactions, and accounting records. |
| `gmail`, `google_calendar`, `google_contacts` | A person's mail, calendars, and contacts, through one Google connection. |
| `slack` | The messages one Slack user can see. |
| `webhook` | Provider webhooks, such as GitHub, Linear, Stripe, or Sentry, and Tilde's chat events. |
| `postgres` | Row changes from your PostgreSQL database. |
| `kafka` | Messages from Kafka topics. |

Set `connection = "<slot>"` on a pipeline to use a Tilde [connection](https://trytilde.ai/docs/connections.md). The pipeline then gets the account's current credentials, and refreshed OAuth tokens, from Tilde. A webhook pipeline with a connection receives that provider's verified webhooks. Every slot a pipeline names must be declared in the ontology. See [Connection slots](#connection-slots).

Write any other secret as `${NAME}`, and set it on the environment. See [Secrets](https://trytilde.ai/docs/ontology/deploying.md#secrets-and-settings).

## Connection slots

A connection slot is a third-party account the project needs. Declare it in the ontology, and bind it to a connection in each environment.

```ts ontology/connections.ts theme={"system"}
import { connection } from "@tilde/ontology";

export const linear = connection("linear");                                  // provider defaults to the name
export const github = connection("github");
export const slack = connection("slack", { provider: "slack", type: "user_oauth" });
```

List the slots in `ontology({ connections: [linear, github, slack] })`. Handlers use the same slots to call the provider. See [Call third parties](https://trytilde.ai/docs/ontology/views-and-actions.md#call-third-parties).

## Custom streams

A custom stream takes records from your own code. Each record is validated against a zod schema, and keyed, so a newer record of a key replaces the older one.

```ts ontology/sources.ts theme={"system"}
import { stream } from "@tilde/ontology";
import { z } from "zod";

export const feedback = stream("feedback", {
  schema: z.object({ id: z.string(), customerId: z.string(), score: z.number().int().min(1).max(5) }),
  key: "id",
});
```

List streams in `ontology({ streams: [feedback] })`, and write records with the management API. Use a [management API key](https://trytilde.ai/docs/operator-access.md#management-api-keys) with `write_streams` on the project. At most 500 records go in one request.

```bash theme={"system"}
curl -X POST "$TILDE_URL/tilde.ontology_control.v1.IngressService/WriteRecords" \
  -H "Authorization: Bearer $TILDE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"environmentId": "<environment-id>", "stream": "feedback",
       "records": [{"id": "f-1", "customerId": "acme", "score": 5}]}'
```

### Reference data

A stream can also ship static records with the project, such as configuration or lookup tables. Tilde validates them at build time, and loads them on every deploy. A redeploy replaces changed records. A record you remove from the list is not deleted.

```ts theme={"system"}
export const tiers = stream("tiers", {
  schema: z.object({ id: z.string(), label: z.string(), creditLimit: z.number() }),
  key: "id",
  reference: [
    { id: "gold", label: "Gold", creditLimit: 5000 },
    { id: "silver", label: "Silver", creditLimit: 1000 },
  ],
});
```

## Tilde chat events

An ontology can read your organisation's own Tilde data: chat threads, messages, participants, channel identities, runs, and inference usage. Declare a `tilde` webhook pipeline with the streams you want. Message bodies reach the ontology only if you list `public_chat_messages`.

```toml ducktail.toml theme={"system"}
[[pipeline]]
name = "tilde"
kind = "webhook"
streams = ["public_chat_threads", "public_chat_messages", "public_chat_participants", "public_chat_channel_identities"]
key_header = "x-tilde-key"
```

Then turn on **Registry events** in the environment's settings. Tilde delivers existing rows first, then every change. Agents' conversations become data the ontology can join with everything else, for example to link a WhatsApp thread to a customer.

## Models

A model is SQL over sources or other models, in dbt's vocabulary. Read sources with `source(pipeline, stream)`, or use a custom stream directly.

```ts ontology/models.ts theme={"system"}
import { cdcCurrent, model, source, sql, t, tests } from "@tilde/ontology";

// The latest row per key of a stream, with deletes applied.
export const stripeCustomers = model({ name: "stripe_customers", schema: "current", ...cdcCurrent(source("stripe", "customers")) });

export const customers = model({
  name: "customers",
  schema: "staging",
  materialized: "incremental",
  uniqueKey: "customer_id",
  tests: [tests.notNull("customer_id"), tests.unique("customer_id")],
  sql: ({ ref, changes, isIncremental }) => sql`
    SELECT payload->>'id' AS customer_id,
           payload->>'name' AS name,
           coalesce(payload->>'$.metadata.tier', 'bronze') AS tier
    FROM ${isIncremental ? changes(stripeCustomers) : ref(stripeCustomers)}`,
});
```

| Option | Meaning |
| - | - |
| `materialized` | `view`, `table`, `incremental`, or `cell`. A `cell` model is a table of a [transactional view](https://trytilde.ai/docs/ontology/views-and-actions.md#transactional-views). |
| `uniqueKey` | The key incremental runs upsert on. |
| `sql` | A builder, called once for the full build and once for incremental runs. Use `ref()` for models and objects, `src()` for sources, and `changes()` for the rows that changed since the last run. |
| `tests` | `unique`, `notNull`, `acceptedValues`, and `relationships`. |
| `preHooks`, `postHooks` | Statements run before or after the model's write. |

You can also keep SQL in a file with `sql: sqlFile("./customers.sql")`, using `{{ ref('model') }}`, `{{ src('pipeline', 'stream') }}`, and `{% if is_incremental() %}`.

Models read upstream tables only through `ref`, `src`, and `changes`, so Tilde always knows a model's dependencies. The build rejects SQL that names a raw table directly.

### SQL dialect

Models and handler SQL use DuckDB's SQL dialect. JSON payloads read with `->>`, including paths such as `payload->>'$.address.country'`.

> [!NOTE]
>
> Ontologies can also use PostgreSQL storage, with `storage: "postgres"` in `ontology()`. All SQL is then PostgreSQL, and models can declare `indexes`. An environment runs only ontologies of its own storage, so talk to us before you switch.

### When a deploy rebuilds a model

Some changes make a deployed model rebuild from scratch: new inputs to `changes()`, a new schema, or a new dependency. A deploy that would rebuild a model is refused, and names each model. Deploy again with `--accept-rebuild model:<name>` when the rebuild is what you want. See [Deploy](https://trytilde.ai/docs/ontology/deploying.md#deploy).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.