Sequin database triggers

// Upsert the embedding in the database. See utils.ts for the implementation -> ->
await upsertEmbedding(embedding, payload.record.id);

// Return the updated record
return {
  ...payload.record,
  embedding: JSON.stringify(embedding),
};

```ts utils.ts
import pg from "pg";

export async function upsertEmbedding(embedding: number[], id: number) {
  const client = new pg.Client({
    connectionString: process.env.DATABASE_URL,
  });
  await client.connect();

  try {
    const query = `
      INSERT INTO post_embeddings (id, embedding)
      VALUES ($2, $1)
      ON CONFLICT (id)
      DO UPDATE SET embedding = $1
    `;
    const values = [JSON.stringify(embedding), id];

    const result = await client.query(query, values);
    console.log(`Updated record in database. Rows affected: ${result.rowCount}`);

    return result.rowCount;
  } catch (error) {
    console.error("Error updating record in database:", error);
    throw error;
  } finally {
    await client.end();
  }
}

npx trigger.dev@latest dev

import type { createEmbeddingForPost } from "@/trigger/create-embedding-for-post";
import { tasks } from "@trigger.dev/sdk";
import { NextResponse } from "next/server";

export async function POST(req: Request) {
  const authHeader = req.headers.get("authorization");
  if (!authHeader || authHeader !== `Bearer ${process.env.SEQUIN_WEBHOOK_SECRET}`) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }
  const payload = await req.json();
  const handle = await tasks.trigger<typeof createEmbeddingForPost>(
    "create-embedding-for-post",
    payload
  );

  return NextResponse.json(handle);
}

SEQUIN_WEBHOOK_SECRET=your-secret-key
TRIGGER_SECRET_KEY=secret-from-trigger-dev
OPENAI_API_KEY=sk-proj-asdfasdfasdf
DATABASE_URL=postgresql://

    ```sql
    create publication sequin_pub for all tables;
    select pg_create_logical_replication_slot('sequin_slot', 'pgoutput');
    ```

    4. Name your database and click the **Connect Database** button.

    Sequin will connect to your database and ensure that it's configured properly.

    <Note>
    If you need step-by-step connection instructions to connect Sequin to your database, check out our [quickstart guide](https://sequinstream.com/docs/quickstart).
    </Note>
</Step>
<Step title="Tunnel to your local endpoint">
Now, create a tunnel to your local endpoint so Sequin can deliver change payloads to your local API:

1. In the Sequin console, open the **HTTP Endpoint** tab and click the **Create HTTP Endpoint** button.
2. Enter a name for your endpoint (i.e. `local_endpoint`) and flip the **Use localhost** switch. Follow the instructions in the Sequin console to [install the Sequin CLI](https://sequinstream.com/docs/cli), then run:

  ```bash
  sequin tunnel --ports=3001:local_endpoint
  ```

3. Now, click **Add encryption header** and set the key to `Authorization` and the value to `Bearer SEQUIN_WEBHOOK_SECRET`.
4. Click **Create HTTP Endpoint**.
</Step>
<Step title="Create a Push Consumer">
Create a push consumer that will capture posts from your database and deliver them to your local endpoint:

1. Navigate to the **Consumers** tab and click the **Create Consumer** button.
2. Select your `posts` table (i.e `public.posts`).
3. You want to ensure that every post receives an embedding - and that embeddings are updated as posts are updated. To do this, select to process **Rows** and click **Continue**.
<Note>
You can also use **changes** for this particular use case, but **rows** comes with some nice replay and backfill features.
</Note>
4. You'll now set the sort and filter for the consumer. For this guide, we'll sort by `updated_at` and start at the beginning of the table. We won't apply any filters:
  <Frame>
    
  </Frame>
5. On the next screen, select **Push** to have Sequin send the events to your webhook URL. Click **Continue**.
6. Now, give your consumer a name (i.e. `posts_push_consumer`) and in the **HTTP Endpoint** section select the `local_endpoint` you created above. Add the exact API route you created in the previous step (i.e. `/api/create-embedding-for-post`):
  <Frame>
    
  </Frame>
7. Click the **Create Consumer** button.
</Step>

```sql
insert into
posts (title, body, author)
values
  (
    'The Future of AI',
    'An insightful look into how artificial intelligence is shaping the future of technology and society.',
    'Alice H Johnson'
  );
```
</Step>
<Step title="Trace the change in the Sequin dashboard">
In the Sequin console, navigate to the [**Trace**](https://console.sequinstream.com/trace) tab and confirm that Sequin delivered the event to your local endpoint:
<Frame>
  
</Frame>
</Step>

<Step title="Confirm the event was received by your endpoint">
In your local terminal, you should see a `200` response in your Next.js app:

```bash
POST /api/create-embedding-for-post 200 in 262ms
```
</Step>
<Step title="Observe the task run in the Trigger.dev dashboard">
Finally, in the [**Trigger.dev dashboard**](https://cloud.trigger.dev/), navigate to the Runs page and confirm that the task run completed successfully:

<Frame>
    
</Frame>
</Step>