# How to sync transactions to your database
URL: https://www.quiltt.dev/get-started/tutorials/transaction-sync
Description: Keep a local copy of your users' transactions up to date with Quiltt webhooks and the GraphQL API. Server-side Node.js guide with code examples.
Navigation: get-started → tutorials → transaction-sync
Tags: Tutorials, Transactions, Webhooks
Content Length: 12k characters

This guide shows you how to keep your database in sync with each user's transactions. Quiltt sends a webhook whenever a Connection syncs; your handler fetches the new transactions and stores them.

The handler you build:

1. Receives `connection.synced.successful` webhooks when transaction data changes
2. Authenticates server-to-server with Basic Auth (no Session token rate limits)
3. Fetches transactions from the GraphQL API, paging through every result
4. Uses the event's date-range metadata to fetch only what changed
5. Stores transactions with a mark-and-sweep pass, so upserts stay idempotent and removed transactions are reconciled

The examples use Node.js 18+, but the patterns apply to any backend.

## How transaction sync works

Quiltt syncs financial data in the background after a user connects an account. Rather than poll the API on a timer, you subscribe to webhooks and react when data is ready:

1. A user connects an account through the [Connector](/connector).
2. Quiltt syncs the Connection and sends a `connection.synced.successful` webhook.
3. Your handler verifies the webhook, then queries the [Transactions API](/api/transactions) for that Profile.
4. You upsert the results into your own store.

The `connection.synced.successful` event family tells you what changed:

| Event | When it fires | What to fetch |
| ----- | ------------- | ------------- |
| `connection.synced.successful.initial` | The first sync after a user connects | Every transaction for the Profile |
| `connection.synced.successful.historical` | Older transactions arrive after the initial sync | Transactions in the event's date range |

When an event carries `metadata.startDate` and `metadata.endDate`, fetch only that range. When it doesn't, the Connection is already up to date and no transactions need fetching.

## Prerequisites

- [Quiltt Dashboard](https://dashboard.quiltt.dev) account ([sign up](https://dashboard.quiltt.dev/signup))
- A Connector with at least one connected account (see the [Connectors tutorial](/get-started/tutorials/connectors))
- An API Key secret from the [Dashboard](https://dashboard.quiltt.dev)
- Node.js 18 or higher
- A publicly reachable URL for local testing (use a tunneling tool such as [ngrok](https://ngrok.com))

Add your credentials to a `.env` file:

```bash
# .env
QUILTT_API_KEY_SECRET=your_api_key_secret_here
QUILTT_WEBHOOK_SECRET=your_webhook_subscription_secret_here
```

Warning:
Keep `QUILTT_API_KEY_SECRET` and `QUILTT_WEBHOOK_SECRET` server-side only. Never expose them in client code or commit them to version control.

## 1. Subscribe to sync events

Create a webhook subscription that points at your handler. In the [Dashboard](https://dashboard.quiltt.dev):

1. Open the **Webhooks** link within your Environment.
2. Click **Create Webhook Subscription**.
3. Set the target URL to your handler endpoint (for example, your ngrok URL plus `/quiltt_webhook`).
4. Subscribe to `connection.synced.successful`. This automatically includes every `connection.synced.successful.*` event.
5. Copy the subscription secret into `QUILTT_WEBHOOK_SECRET`.

For programmatic setup, see the [Webhooks setup guide](/webhooks/setup).

## 2. Authenticate server-side

Server-to-server requests to a Profile's data use Basic Auth, which is not subject to the per-Profile Session token rate limits. Encode `profileId:API_KEY_SECRET` as Base64 and send it as the `Authorization` header:

```typescript
// quiltt.ts
const GRAPHQL_ENDPOINT = 'https://api.quiltt.io/v1/graphql'

function authHeader(profileId: string): string {
  const encoded = Buffer.from(
    `${profileId}:$[process.env.QUILTT_API_KEY_SECRET]`
  ).toString('base64')

  return `Basic ${encoded}`
}
```

See the [Authentication guide](/authentication) for the full list of scopes and headers.

## 3. Fetch transactions with pagination

The `transactions` query uses [cursor-based pagination](/api/graphql/pagination), capped at 100 records per page. Page through the results with `pageInfo.endCursor` until `hasNextPage` is `false`.

This function fetches every transaction for a Profile within an optional date range:

```typescript
// quiltt.ts (continued)
const TRANSACTIONS_QUERY = `
  query SyncTransactions($after: String, $filter: TransactionFilter) {
    transactions(first: 100, after: $after, sort: DATE_DESC, filter: $filter) {
      edges {
        node {
          id
          date
          description
          amount
          entryType
          status
          account [id
            name]
        }
      }
      pageInfo [hasNextPage
        endCursor]
    }
  }
`

  const filter =
    range.startDate && range.endDate
      ? [date_gte: range.startDate, date_lte: range.endDate]
      : undefined

  const transactions = []
  let after: string | null = null

  do {
    const response = await fetch(GRAPHQL_ENDPOINT, {
      method: 'POST',
      headers: [Authorization: authHeader(profileId),
        'Content-Type': 'application/json',],
      body: JSON.stringify({
        query: TRANSACTIONS_QUERY,
        variables: [after, filter],
      }),
    })

    const {data} = await response.json()
    const page = data.transactions

    transactions.push(...page.edges.map((edge) => edge.node))
    after = page.pageInfo.hasNextPage ? page.pageInfo.endCursor : null
  } while (after)

  return transactions
}
```

## 4. Store transactions with mark and sweep

Upserting new and changed transactions is only half the job. A paginated query tells you what currently exists upstream, but it can't tell you what *disappeared* — a pending transaction that was reversed.

Reconcile removals with a **mark-and-sweep** pass keyed on a per-run timestamp:

1. **Mark** — record a sync timestamp when the run starts, and write it to each transaction's `last_seen_at` field as you upsert it.
2. **Sweep** — after the run, find stored transactions in the same scoped range whose `last_seen_at` is older than this run's timestamp. The current sync didn't return them, so they're gone upstream. Remove or archive them to match your data model.

Each transaction has a stable `id`, so upserting on it is also idempotent — Quiltt retries failed webhook deliveries up to 20 times, and a repeated delivery refreshes `last_seen_at` instead of creating a duplicate.

```typescript
// store.ts

  // Mark: stamp every transaction this run touches with one timestamp.
  const syncedAt = new Date().toISOString()
  const transactions = await fetchTransactions(profileId, range)

  for (const transaction of transactions) {
    // Upserting on transaction.id is idempotent; last_seen_at records
    // that this run saw the transaction.
    await db.transactions.upsert({
      where: [id: transaction.id],
      update: [...transaction, lastSeenAt: syncedAt],
      create: [...transaction, profileId, lastSeenAt: syncedAt],
    })
  }

  // Sweep: anything in the synced range we did NOT just mark is gone upstream.
  const swept = await db.transactions.updateMany({
    where: {
      profileId,
      lastSeenAt: [lt: syncedAt],
      // Scope the sweep to the range you fetched (see callout below).
      ...(range.startDate && range.endDate
        ? { date: [gte: range.startDate, lte: range.endDate] }
        : {}),
    },
    data: [status: 'REMOVED'],
  })

  return [synced: transactions.length, swept: swept.count]
}
```

Warning:
Always scope the sweep to the range you just fetched. An incremental sync returns only the transactions in the event's date range, so a sweep without that date filter would mark every transaction outside the range as removed. With no date range (a full sync), the scope is the whole Profile, which is correct.

## 5. Handle the webhook

Verify every incoming webhook before acting on it, then route `connection.synced.successful` events to your sync function. Pass the event's date metadata through so historical syncs fetch only the affected range:

```typescript
// server.ts

const app = express()
const PORT = 3000

const QUILTT_WEBHOOK_SECRET = process.env.QUILTT_WEBHOOK_SECRET
const QUILTT_WEBHOOK_VERSION = 1
const QUILTT_WEBHOOK_WINDOW = 300 // Five minutes

const processedEvents = new Set<string>()

// Capture the raw request body — the signature is computed over the exact
// bytes Quiltt sent, so re-serializing the parsed object would not match.
app.use(express.json({ verify: (req, _res, buf) => [(req as any).rawBody = buf] }))

app.post('/quiltt_webhook', async (req: Request, res: Response) => {
  const timestamp = req.header('Quiltt-Timestamp')
  if (!timestamp || Date.now() / 1000 - Number(timestamp) > QUILTT_WEBHOOK_WINDOW) [return res.status(204).send()]

  const payload = (req as any).rawBody.toString('utf8')
  const signature = crypto
    .createHmac('sha256', QUILTT_WEBHOOK_SECRET)
    .update(`${QUILTT_WEBHOOK_VERSION}${timestamp}${payload}`)
    .digest('base64')

  if (req.header('Quiltt-Signature') !== signature) [return res.status(204).send()]

  // Acknowledge within 20 seconds, then process.
  res.status(204).send()

  for (const event of req.body.events) {
    // Skip events you've already processed (Quiltt retries deliveries).
    if (processedEvents.has(event.id)) continue
    processedEvents.add(event.id)

    if (event.type.startsWith('connection.synced.successful')) {
      const profileId = event.profile.id
      const range = event.metadata ?? {}

      const [synced, swept] = await syncTransactions(profileId, range)
      console.log(`Synced ${synced} and swept ${swept} transactions for ${profileId}`)
    }
  }
})

app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`)
})
```

Info:
Return a `2xx` response by acknowledging and storing the webhook for processing. Then run the sync, so a slow query never causes Quiltt to retry a delivery you already received.

## Verification

Test the full flow end to end:

1. Start your server and expose it with a tunnel:

   ```bash
   node server.js
   ngrok http 3000
   ```

2. Confirm the subscription's target URL points at your tunnel URL.
3. Connect an account in the [Dashboard](https://dashboard.quiltt.dev) Connector preview. In a `SANDBOX` Environment, use the **Mock** provider for guaranteed data.
4. Watch your server logs. Within a few seconds you should see a `connection.synced.successful.initial` event and a count of synced and swept transactions.
5. Query your database to confirm the transactions were stored and each row has a `last_seen_at` value.

## Troubleshooting

| Problem | Cause | Fix |
| ------- | ----- | --- |
| Signature check fails | The raw request body changed before verification | Verify against the exact bytes Quiltt sent. Some frameworks rewrite the body—disable that for this route. |
| `401 Unauthorized` from GraphQL | Wrong Basic Auth encoding | Encode `profileId:API_KEY_SECRET`, not the API key alone. |
| No webhook arrives | Subscription URL unreachable or wrong event type | Confirm the tunnel is running and the subscription includes `connection.synced.successful`. |
| Duplicate rows | Insert instead of upsert | Key your write on `transaction.id`. |
| Sweep removes valid transactions | The sweep isn't scoped to the synced range | Filter the sweep by the event's `startDate` and `endDate`, matching the range you fetched. |

## Next steps

You now sync transactions automatically whenever a user's data changes. The same pattern works for other Profile data—swap the query and the event type.

### Related tutorials

- [Authentication Tutorial](/get-started/tutorials/authentication) - Session token setup and management
- [Multi-Aggregator Connectors Tutorial](/get-started/tutorials/connectors) - Build a full connection flow
- [Account Statements Tutorial](/get-started/tutorials/statements) - Fetch and display account statements

### Reference documentation

- [Transactions API reference](/api/transactions) - Complete transactions documentation
- [Pagination guide](/api/graphql/pagination) - Cursor-based connections in depth
- [Webhooks guide](/webhooks) - Event types, payloads, and verification
- [Authentication guide](/authentication) - Scopes, Basic Auth, and Session tokens

### Questions?

[Contact support](mailto:support@quiltt.io)