🛠️ feat: Escrow Reconciliation Service keep  trustless_work_escrows in Sync with Stellar Blockchain

<hr>
<h2>🧭 Issue Type</h2>
<p><strong>Architecture / Backend Feature/ Maintainer issue</strong>

Relates to: Contributing Guide · Git Guidelines</p>
<hr>
<h2>📋 Issue Summary</h2>
<p>SafeTrust relies on TrustlessWork webhooks to receive real-time escrow state changes from the Stellar blockchain. However, webhooks are not guaranteed — network timeouts, service restarts, or delivery failures can leave <code>public.trustless_work_escrows</code> out of sync with the actual on-chain state. This story defines the architecture for a <strong>periodic reconciliation service</strong> that acts as the safety net: a Hasura cron trigger that batch-fetches current escrow state from the TrustlessWork Indexer API and reconciles it against our local database.</p>
<hr>
<h2>🏛️ Architecture Context</h2>
<h3>The Problem</h3>
<pre><code>Stellar Blockchain (source of truth)
        │
        │ webhook (can fail / be missed)
        ▼
TrustlessWork API
        │
        │ ← THIS LINK CAN BREAK
        ▼
public.trustless_work_escrows   ← may be stale
        │
        ▼
Frontend (GraphQL subscriptions) ← shows wrong state to users
</code></pre>
<p>When a webhook is missed, a user's escrow status, balance, or milestone state in the SafeTrust UI becomes incorrect — with no automatic correction mechanism.</p>
<h3>The Solution</h3>
<p>A <strong>Hasura Cron Trigger</strong> running every 15 minutes calls a Node.js handler endpoint (<code>/reconciliation/sync-escrows</code>) that:</p>
<ol>
<li>Fetches all <code>contract_id</code> values from <code>public.trustless_work_escrows</code></li>
<li>Batches them in chunks of 50</li>
<li>Calls <code>GET /helper/get-escrows-by-contract-ids</code> on the TrustlessWork API for each batch</li>
<li>Compares the API response against local DB state</li>
<li>Upserts any records where <code>status</code>, <code>balance</code>, or <code>escrow_metadata</code> differ</li>
<li>Logs a summary of changes to <code>trustless_work_webhook_events</code> with <code>event_type = 'reconciliation.sync'</code></li>
</ol>
<hr>
<h2>🗄️ Database Impact</h2>
<h3>No new tables required.</h3>
<p>The existing schema fully supports this feature:</p>

Table | Role in Reconciliation
-- | --
public.trustless_work_escrows | Target of upsert — the table being reconciled
public.trustless_work_webhook_events | Receives a reconciliation audit log entry per run
public.escrow_milestones | Updated if milestone status differs from API response


<h3>Fields reconciled on <code>trustless_work_escrows</code></h3>
<pre><code class="language-sql">-- Fields compared and potentially updated per run:
status          -- Most critical: 'pending' → 'active' → 'completed' → 'disputed'
balance         -- Current on-chain balance (DECIMAL)
escrow_metadata -- Full JSONB blob from TrustlessWork API response
updated_at      -- Set to NOW() on any update
</code></pre>
<hr>
<h2>🛠️ Implementation Specification</h2>
<h3>1. Hasura Cron Trigger</h3>
<p><strong>File:</strong> <code>metadata/base/cron_triggers.yaml</code></p>
<pre><code class="language-yaml">- name: escrow_reconciliation_sync
  webhook: "{{WEBHOOK_BASE_URL}}/reconciliation/sync-escrows"
  schedule: "*/15 * * * *"   # every 15 minutes
  include_in_metadata: true
  payload:
    tenant_id: "safetrust"
  retry_conf:
    num_retries: 3
    timeout_seconds: 60
    tolerance_seconds: 21600
  comment: "Periodic reconciliation of trustless_work_escrows against TrustlessWork Indexer API"
</code></pre>
<hr>
<h3>2. Handler Endpoint</h3>
<p><strong>New file:</strong> <code>webhook/reconciliation/sync-escrows.js</code></p>
<pre><code class="language-js">const express = require('express');
const router = express.Router();
const { Pool } = require('pg');
const axios = require('axios');

const pool = new Pool({
  connectionString: process.env.PG_DATABASE_URL,
});

const TRUSTLESSWORK_API_URL = process.env.TRUSTLESSWORK_API_URL;
const TRUSTLESSWORK_API_KEY = process.env.TRUSTLESSWORK_API_KEY;
const BATCH_SIZE = 50;

router.post('/sync-escrows', async (req, res) =&gt; {
  const startedAt = new Date();
  let totalUpdated = 0;
  let totalFailed = 0;

  try {
    // Step 1: Fetch all contract_ids from local DB
    const { rows } = await pool.query(
      `SELECT contract_id FROM public.trustless_work_escrows
       WHERE tenant_id = $1 AND contract_id IS NOT NULL`,
      ['safetrust']
    );

    const contractIds = rows.map(r =&gt; r.contract_id);
    if (contractIds.length === 0) {
      return res.json({ message: 'No escrows to reconcile', updated: 0 });
    }

    // Step 2: Batch into chunks of BATCH_SIZE
    const batches = [];
    for (let i = 0; i &lt; contractIds.length; i += BATCH_SIZE) {
      batches.push(contractIds.slice(i, i + BATCH_SIZE));
    }

    // Step 3: Process each batch
    for (const batch of batches) {
      try {
        const response = await axios.get(
          `${TRUSTLESSWORK_API_URL}/helper/get-escrows-by-contract-ids`,
          {
            params: { contractIds: batch.join(',') },
            headers: { 'x-api-key': TRUSTLESSWORK_API_KEY },
            timeout: 30000,
          }
        );

        const apiEscrows = response.data?.escrows || [];

        // Step 4: Upsert each escrow if state differs
        for (const apiEscrow of apiEscrows) {
          try {
            const result = await pool.query(
              `UPDATE public.trustless_work_escrows
               SET
                 status         = $1,
                 balance        = $2,
                 escrow_metadata = $3,
                 updated_at     = NOW()
               WHERE contract_id = $4
                 AND (
                   status          IS DISTINCT FROM $1 OR
                   balance         IS DISTINCT FROM $2 OR
                   escrow_metadata IS DISTINCT FROM $3
                 )
               RETURNING id`,
              [
                apiEscrow.status,
                apiEscrow.balance,
                JSON.stringify(apiEscrow),
                apiEscrow.contractId,
              ]
            );
            if (result.rowCount &gt; 0) totalUpdated++;
          } catch (rowErr) {
            console.error(`Failed to update ${apiEscrow.contractId}:`, rowErr.message);
            totalFailed++;
          }
        }
      } catch (batchErr) {
        console.error('Batch fetch failed:', batchErr.message);
        totalFailed += batch.length;
      }
    }

    // Step 5: Write reconciliation audit log
    await pool.query(
      `INSERT INTO public.trustless_work_webhook_events
         (contract_id, event_type, payload, processed, processed_at, tenant_id)
       VALUES
         (NULL, 'reconciliation.sync', $1, TRUE, NOW(), 'safetrust')`,
      [
        JSON.stringify({
          started_at: startedAt,
          completed_at: new Date(),
          total_contracts: contractIds.length,
          total_updated: totalUpdated,
          total_failed: totalFailed,
        }),
      ]
    );

    res.json({
      message: 'Reconciliation complete',
      total_contracts: contractIds.length,
      total_updated: totalUpdated,
      total_failed: totalFailed,
    });

  } catch (err) {
    console.error('Reconciliation sync failed:', err);
    res.status(500).json({ error: 'Reconciliation failed', details: err.message });
  }
});

module.exports = router;
</code></pre>
<hr>
<h3>3. Register the Route</h3>
<p><strong>File:</strong> <code>webhook/index.js</code> — add one line:</p>
<pre><code class="language-js">const reconciliationRoutes = require('./reconciliation/sync-escrows');
app.use('/reconciliation', reconciliationRoutes);
</code></pre>
<hr>
<h3>4. Required Environment Variables</h3>
<p>Add to <code>.env.example</code>:</p>
<pre><code class="language-bash"># TrustlessWork API
TRUSTLESSWORK_API_URL=https://dev.api.trustlesswork.com   # or mainnet
TRUSTLESSWORK_API_KEY=your_api_key_here
</code></pre>
<hr>
<h2>✅ Acceptance Criteria</h2>
<ul>
<li>[ ] Hasura cron trigger <code>escrow_reconciliation_sync</code> is defined in <code>metadata/base/cron_triggers.yaml</code> and fires every 15 minutes</li>
<li>[ ] <code>POST /reconciliation/sync-escrows</code> endpoint exists and responds with <code>200</code> on success</li>
<li>[ ] Endpoint fetches all <code>contract_id</code> values from <code>public.trustless_work_escrows</code> where <code>tenant_id = 'safetrust'</code></li>
<li>[ ] Contract IDs are batched in chunks of <strong>50</strong> before calling the TrustlessWork API</li>
<li>[ ] Only records where <code>status</code>, <code>balance</code>, or <code>escrow_metadata</code> differ from the API response are updated (<code>IS DISTINCT FROM</code>)</li>
<li>[ ] <code>updated_at</code> is set to <code>NOW()</code> on every updated row</li>
<li>[ ] A <code>reconciliation.sync</code> event is inserted into <code>public.trustless_work_webhook_events</code> after every run, with <code>payload</code> containing <code>total_contracts</code>, <code>total_updated</code>, <code>total_failed</code>, <code>started_at</code>, <code>completed_at</code></li>
<li>[ ] If the TrustlessWork API returns an error for a batch, that batch is counted as <code>total_failed</code> and the run continues (no full abort)</li>
<li>[ ] <code>TRUSTLESSWORK_API_URL</code> and <code>TRUSTLESSWORK_API_KEY</code> are documented in <code>.env.example</code></li>
<li>[ ] The route is registered in <code>webhook/index.js</code> under <code>/reconciliation</code></li>
<li>[ ] Karate test covers: successful sync with changes, successful sync with no changes, batch API failure resilience</li>
</ul>
<hr>
<h2>🔗 Relationships</h2>
<ul>
<li><strong>Depends on:</strong> <code>public.trustless_work_escrows</code> (canonical escrow table)</li>
<li><strong>Writes to:</strong> <code>public.trustless_work_webhook_events</code> (audit log)</li>
<li><strong>Triggered by:</strong> Hasura cron (<code>metadata/base/cron_triggers.yaml</code>)</li>
<li><strong>Complements:</strong> TrustlessWork webhook handler (inbound real-time events)</li>
<li><strong>TrustlessWork endpoint used:</strong> <code>GET /helper/get-escrows-by-contract-ids</code></li>
</ul>
<hr>
<h2>📎 References</h2>
<ul>
<li><a href="https://github.com/safetrustcr/Frontend/issues/34">Contributing Guide</a></li>
<li><a href="https://github.com/safetrustcr/Frontend/issues/35">Git Guidelines</a></li>
<li><a href="https://docs.trustlesswork.com/trustless-work/api-rest/helpers">TrustlessWork API Docs — Helpers</a></li>
<li><a href="https://docs.trustlesswork.com/trustless-work/api-rest/indexer">TrustlessWork API Docs — Indexer</a></li>
</ul></body></html># BMAD Architecture Story
## feat: Escrow Reconciliation Service — Keep `trustless_work_escrows` in Sync with Stellar Blockchain

---

## 🧭 Story Type
**Architecture / Backend Feature**
Relates to: Contributing Guide · Git Guidelines

---

## 📋 Issue Summary

SafeTrust relies on TrustlessWork webhooks to receive real-time escrow state changes from the Stellar blockchain. However, webhooks are not guaranteed — network timeouts, service restarts, or delivery failures can leave `public.trustless_work_escrows` out of sync with the actual on-chain state. This story defines the architecture for a **periodic reconciliation service** that acts as the safety net: a Hasura cron trigger that batch-fetches current escrow state from the TrustlessWork Indexer API and reconciles it against our local database.

---

## 🏛️ Architecture Context

### The Problem

```
Stellar Blockchain (source of truth)
        │
        │ webhook (can fail / be missed)
        ▼
TrustlessWork API
        │
        │ ← THIS LINK CAN BREAK
        ▼
public.trustless_work_escrows   ← may be stale
        │
        ▼
Frontend (GraphQL subscriptions) ← shows wrong state to users
```

When a webhook is missed, a user's escrow status, balance, or milestone state in the SafeTrust UI becomes incorrect — with no automatic correction mechanism.

### The Solution

A **Hasura Cron Trigger** running every 15 minutes calls a Node.js handler endpoint (`/reconciliation/sync-escrows`) that:

1. Fetches all `contract_id` values from `public.trustless_work_escrows`
2. Batches them in chunks of 50
3. Calls `GET /helper/get-escrows-by-contract-ids` on the TrustlessWork API for each batch
4. Compares the API response against local DB state
5. Upserts any records where `status`, `balance`, or `escrow_metadata` differ
6. Logs a summary of changes to `trustless_work_webhook_events` with `event_type = 'reconciliation.sync'`

---

## 🗄️ Database Impact

### No new tables required.

The existing schema fully supports this feature:

| Table | Role in Reconciliation |
|---|---|
| `public.trustless_work_escrows` | Target of upsert — the table being reconciled |
| `public.trustless_work_webhook_events` | Receives a reconciliation audit log entry per run |
| `public.escrow_milestones` | Updated if milestone status differs from API response |

### Fields reconciled on `trustless_work_escrows`

```sql
-- Fields compared and potentially updated per run:
status          -- Most critical: 'pending' → 'active' → 'completed' → 'disputed'
balance         -- Current on-chain balance (DECIMAL)
escrow_metadata -- Full JSONB blob from TrustlessWork API response
updated_at      -- Set to NOW() on any update
```

---

## 🛠️ Implementation Specification

### 1. Hasura Cron Trigger

**File:** `metadata/base/cron_triggers.yaml`

```yaml
- name: escrow_reconciliation_sync
  webhook: "{{WEBHOOK_BASE_URL}}/reconciliation/sync-escrows"
  schedule: "*/15 * * * *"   # every 15 minutes
  include_in_metadata: true
  payload:
    tenant_id: "safetrust"
  retry_conf:
    num_retries: 3
    timeout_seconds: 60
    tolerance_seconds: 21600
  comment: "Periodic reconciliation of trustless_work_escrows against TrustlessWork Indexer API"
```

---

### 2. Handler Endpoint

**New file:** `webhook/reconciliation/sync-escrows.js`

```js
const express = require('express');
const router = express.Router();
const { Pool } = require('pg');
const axios = require('axios');

const pool = new Pool({
  connectionString: process.env.PG_DATABASE_URL,
});

const TRUSTLESSWORK_API_URL = process.env.TRUSTLESSWORK_API_URL;
const TRUSTLESSWORK_API_KEY = process.env.TRUSTLESSWORK_API_KEY;
const BATCH_SIZE = 50;

router.post('/sync-escrows', async (req, res) => {
  const startedAt = new Date();
  let totalUpdated = 0;
  let totalFailed = 0;

  try {
    // Step 1: Fetch all contract_ids from local DB
    const { rows } = await pool.query(
      `SELECT contract_id FROM public.trustless_work_escrows
       WHERE tenant_id = $1 AND contract_id IS NOT NULL`,
      ['safetrust']
    );

    const contractIds = rows.map(r => r.contract_id);
    if (contractIds.length === 0) {
      return res.json({ message: 'No escrows to reconcile', updated: 0 });
    }

    // Step 2: Batch into chunks of BATCH_SIZE
    const batches = [];
    for (let i = 0; i < contractIds.length; i += BATCH_SIZE) {
      batches.push(contractIds.slice(i, i + BATCH_SIZE));
    }

    // Step 3: Process each batch
    for (const batch of batches) {
      try {
        const response = await axios.get(
          `${TRUSTLESSWORK_API_URL}/helper/get-escrows-by-contract-ids`,
          {
            params: { contractIds: batch.join(',') },
            headers: { 'x-api-key': TRUSTLESSWORK_API_KEY },
            timeout: 30000,
          }
        );

        const apiEscrows = response.data?.escrows || [];

        // Step 4: Upsert each escrow if state differs
        for (const apiEscrow of apiEscrows) {
          try {
            const result = await pool.query(
              `UPDATE public.trustless_work_escrows
               SET
                 status         = $1,
                 balance        = $2,
                 escrow_metadata = $3,
                 updated_at     = NOW()
               WHERE contract_id = $4
                 AND (
                   status          IS DISTINCT FROM $1 OR
                   balance         IS DISTINCT FROM $2 OR
                   escrow_metadata IS DISTINCT FROM $3
                 )
               RETURNING id`,
              [
                apiEscrow.status,
                apiEscrow.balance,
                JSON.stringify(apiEscrow),
                apiEscrow.contractId,
              ]
            );
            if (result.rowCount > 0) totalUpdated++;
          } catch (rowErr) {
            console.error(`Failed to update ${apiEscrow.contractId}:`, rowErr.message);
            totalFailed++;
          }
        }
      } catch (batchErr) {
        console.error('Batch fetch failed:', batchErr.message);
        totalFailed += batch.length;
      }
    }

    // Step 5: Write reconciliation audit log
    await pool.query(
      `INSERT INTO public.trustless_work_webhook_events
         (contract_id, event_type, payload, processed, processed_at, tenant_id)
       VALUES
         (NULL, 'reconciliation.sync', $1, TRUE, NOW(), 'safetrust')`,
      [
        JSON.stringify({
          started_at: startedAt,
          completed_at: new Date(),
          total_contracts: contractIds.length,
          total_updated: totalUpdated,
          total_failed: totalFailed,
        }),
      ]
    );

    res.json({
      message: 'Reconciliation complete',
      total_contracts: contractIds.length,
      total_updated: totalUpdated,
      total_failed: totalFailed,
    });

  } catch (err) {
    console.error('Reconciliation sync failed:', err);
    res.status(500).json({ error: 'Reconciliation failed', details: err.message });
  }
});

module.exports = router;
```

---

### 3. Register the Route

**File:** `webhook/index.js` — add one line:

```js
const reconciliationRoutes = require('./reconciliation/sync-escrows');
app.use('/reconciliation', reconciliationRoutes);
```

---

### 4. Required Environment Variables

Add to `.env.example`:

```bash
# TrustlessWork API
TRUSTLESSWORK_API_URL=https://dev.api.trustlesswork.com   # or mainnet
TRUSTLESSWORK_API_KEY=your_api_key_here
```

---

## ✅ Acceptance Criteria

- [ ] Hasura cron trigger `escrow_reconciliation_sync` is defined in `metadata/base/cron_triggers.yaml` and fires every 15 minutes
- [ ] `POST /reconciliation/sync-escrows` endpoint exists and responds with `200` on success
- [ ] Endpoint fetches all `contract_id` values from `public.trustless_work_escrows` where `tenant_id = 'safetrust'`
- [ ] Contract IDs are batched in chunks of **50** before calling the TrustlessWork API
- [ ] Only records where `status`, `balance`, or `escrow_metadata` differ from the API response are updated (`IS DISTINCT FROM`)
- [ ] `updated_at` is set to `NOW()` on every updated row
- [ ] A `reconciliation.sync` event is inserted into `public.trustless_work_webhook_events` after every run, with `payload` containing `total_contracts`, `total_updated`, `total_failed`, `started_at`, `completed_at`
- [ ] If the TrustlessWork API returns an error for a batch, that batch is counted as `total_failed` and the run continues (no full abort)
- [ ] `TRUSTLESSWORK_API_URL` and `TRUSTLESSWORK_API_KEY` are documented in `.env.example`
- [ ] The route is registered in `webhook/index.js` under `/reconciliation`
- [ ] Karate test covers: successful sync with changes, successful sync with no changes, batch API failure resilience

---

## 🔗 Relationships

- **Depends on:** `public.trustless_work_escrows` (canonical escrow table)
- **Writes to:** `public.trustless_work_webhook_events` (audit log)
- **Triggered by:** Hasura cron (`metadata/base/cron_triggers.yaml`)
- **Complements:** TrustlessWork webhook handler (inbound real-time events)
- **TrustlessWork endpoint used:** `GET /helper/get-escrows-by-contract-ids`

---

## 📎 References

- [[Contributing Guide](https://github.com/safetrustcr/Frontend/issues/34)](https://github.com/safetrustcr/Frontend/issues/34)
- [[Git Guidelines](https://github.com/safetrustcr/Frontend/issues/35)](https://github.com/safetrustcr/Frontend/issues/35)
- [[TrustlessWork API Docs — Helpers](https://docs.trustlesswork.com/trustless-work/api-rest/helpers)](https://docs.trustlesswork.com/trustless-work/api-rest/helpers)
- [[TrustlessWork API Docs — Indexer](https://docs.trustlesswork.com/trustless-work/api-rest/indexer)](https://docs.trustlesswork.com/trustless-work/api-rest/indexer)

Table	Role in Reconciliation
public.trustless_work_escrows	Target of upsert — the table being reconciled
public.trustless_work_webhook_events	Receives a reconciliation audit log entry per run
public.escrow_milestones	Updated if milestone status differs from API response

Table	Role in Reconciliation
`public.trustless_work_escrows`	Target of upsert — the table being reconciled
`public.trustless_work_webhook_events`	Receives a reconciliation audit log entry per run
`public.escrow_milestones`	Updated if milestone status differs from API response

🛠️ feat: Escrow Reconciliation Service keep trustless_work_escrows in Sync with Stellar Blockchain #309

Description

🧭 Issue Type

📋 Issue Summary

🏛️ Architecture Context

The Problem

The Solution

🗄️ Database Impact

No new tables required.

Fields reconciled on trustless_work_escrows

🛠️ Implementation Specification

1. Hasura Cron Trigger

2. Handler Endpoint

3. Register the Route

4. Required Environment Variables

✅ Acceptance Criteria

🔗 Relationships

📎 References

🧭 Story Type

📋 Issue Summary

🏛️ Architecture Context

The Problem

The Solution

🗄️ Database Impact

No new tables required.

Fields reconciled on trustless_work_escrows

🛠️ Implementation Specification

1. Hasura Cron Trigger

2. Handler Endpoint

3. Register the Route

4. Required Environment Variables

✅ Acceptance Criteria

🔗 Relationships

📎 References

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Fields reconciled on `trustless_work_escrows`

Fields reconciled on `trustless_work_escrows`