# custom-snowflake-syncer

A Strapi 5 plugin that automatically synchronises content-type data to Snowflake using the official **`snowflake-sdk`** Node.js driver. It intercepts document write and delete operations and inserts event rows directly into a raw Snowflake table, with no admin UI and no intermediate REST API.

---

## Overview

The plugin hooks into Strapi's document-service middleware layer. Every time a content-type document is created, updated, or deleted — and that content-type is listed in `contentsToSync` — the plugin inserts a row into the configured Snowflake table using the event-log pattern:

| Column | Type | Description |
|---|---|---|
| `USER_ACTION` | `VARCHAR NOT NULL` | `POST` \| `PUT` \| `DELETE` |
| `CONTENT_TYPE_UID` | `VARCHAR NOT NULL` | Strapi content-type UID |
| `DOCUMENT_ID` | `VARCHAR` | Strapi `documentId` |
| `PAYLOAD` | `VARIANT` | Full JSON document |
| `SYNCED_AT` | `TIMESTAMP_NTZ NOT NULL` | UTC timestamp of the sync event |

A manual full-sync endpoint is also available to push all existing records for all configured content-types.

---

## Required Snowflake DDL

Create the target table before starting the plugin:

```sql
CREATE TABLE RAW_ESG_EVENTS (
  USER_ACTION      VARCHAR       NOT NULL,
  CONTENT_TYPE_UID VARCHAR       NOT NULL,
  DOCUMENT_ID      VARCHAR,
  PAYLOAD          VARIANT,
  SYNCED_AT        TIMESTAMP_NTZ NOT NULL
);
```

---

## Directory structure

```
custom-snowflake-syncer/
├── package.json
├── README.md
├── admin/src/index.js                          # Registers plugin (no UI, no menu)
└── server/src/
    ├── index.js
    ├── register.js                             # Document-service middleware (core sync logic)
    ├── bootstrap.js
    ├── destroy.js
    ├── config/index.js                         # Config schema, defaults, validator
    ├── content-types/index.js
    ├── controllers/
    │   ├── index.js
    │   └── controller.js                       # triggerSync · getStatus
    ├── services/
    │   ├── index.js
    │   └── service.js                          # pushToSnowflake · sync · getStatus
    ├── routes/
    │   ├── index.js
    │   └── content-api.js                      # POST /sync  ·  GET /status
    ├── middlewares/
    │   ├── index.js
    │   └── snowflake-sync/index.js             # Strapi HTTP middleware (passthrough)
    └── policies/index.js
```

---

## How the sync works

The document middleware registered in `register.js` intercepts every document-service operation after it completes:

| Strapi action | Event type inserted |
|---|---|
| `create`, `createMany` | `POST` |
| `update`, `updateMany`, `publish`, `unpublish`, `publishMany`, `unpublishMany` | `PUT` |
| `delete`, `deleteMany` | `DELETE` |

For each operation the middleware:
1. Checks whether the content-type UID is listed in `contentsToSync`.
2. Checks whether the mapped event type is enabled for that entry.
3. Calls `service.pushToSnowflake(...)` which inserts one row into the Snowflake target table.
4. **Never throws** — if the insert fails, the error is logged but the original Strapi operation is unaffected.

A Koa HTTP middleware (`plugin::custom-snowflake-syncer.snowflake-sync`) is also registered in `config/middlewares.js` to intercept Content Manager HTTP calls (create/update/delete triggered via the Strapi admin panel), ensuring those operations are captured even when they do not pass through the document-service layer.

---

## Activation in `config/middlewares.js`

The HTTP middleware must be explicitly listed in the global Strapi middleware stack:

```js
// config/middlewares.js
module.exports = [
  // ... other middlewares ...
  'plugin::custom-snowflake-syncer.snowflake-sync',
  // ...
];
```

Without this entry the document-service middleware (`register.js`) still fires for all programmatic operations, but Content Manager calls originating from the Strapi admin panel would not be intercepted by the HTTP layer.

---

## Configuration

Add the following to `config/plugins.js`:

```js
'custom-snowflake-syncer': {
  enabled: true,
  resolve: './src/plugins/custom-snowflake-syncer',
  config: {
    account:      process.env.SNOWFLAKE_ACCOUNT,
    username:     process.env.SNOWFLAKE_USERNAME,
    password:     process.env.SNOWFLAKE_PASSWORD,
    warehouse:    process.env.SNOWFLAKE_WAREHOUSE,
    database:     process.env.SNOWFLAKE_DATABASE,
    schema:       process.env.SNOWFLAKE_SCHEMA    || 'RAW',
    role:         process.env.SNOWFLAKE_ROLE      || 'ESG_INGESTOR_ROLE',
    targetTable:  process.env.SNOWFLAKE_TABLE     || 'RAW_ESG_EVENTS',
    contentsToSync: [
      { element: 'api::article.article', actions: ['POST', 'PUT', 'DELETE'] },
      { element: 'api::company.company', actions: ['POST', 'PUT'] },
    ],
  },
},
```

### Configuration reference

| Key | Type | Required | Default | Description |
|---|---|---|---|---|
| `account` | string | ✅ | — | Snowflake account identifier (e.g. `LTNNOPK-YR34861`) |
| `username` | string | ✅ | — | Snowflake user |
| `password` | string | ✅ | — | Snowflake password |
| `warehouse` | string | ✅ | — | Compute warehouse name (e.g. `COMPUTE_WH`) |
| `database` | string | ✅ | — | Target Snowflake database (e.g. `ESG_PLATFORM`) |
| `schema` | string | | `'RAW'` | Target Snowflake schema |
| `role` | string | | `''` | Snowflake role to assume (e.g. `ESG_INGESTOR_ROLE`) |
| `targetTable` | string | | `'RAW_ESG_EVENTS'` | Table name where events are inserted |
| `contentsToSync` | array | | `[]` | List of content-types and operations to sync |

### `contentsToSync` entry schema

```jsonc
{
  "element": "api::article.article",    // Strapi content-type UID (required)
  "actions": ["POST", "PUT", "DELETE"]  // One or more: POST | PUT | DELETE
}
```

---

## Environment variables

| Variable | Description |
|---|---|
| `SNOWFLAKE_ACCOUNT` | Snowflake account identifier |
| `SNOWFLAKE_USERNAME` | Snowflake user |
| `SNOWFLAKE_PASSWORD` | Snowflake password |
| `SNOWFLAKE_WAREHOUSE` | Compute warehouse name |
| `SNOWFLAKE_DATABASE` | Target database |
| `SNOWFLAKE_SCHEMA` | Target schema (default: `RAW`) |
| `SNOWFLAKE_ROLE` | Snowflake role (default: `ESG_INGESTOR_ROLE`) |
| `SNOWFLAKE_TABLE` | Target table name (default: `RAW_ESG_EVENTS`) |

---

## Inbound API routes

| Method | Path | Description |
|---|---|---|
| `POST` | `/api/snowflake-syncer/sync` | Trigger a manual full sync (POST events for all configured content-types) |
| `GET` | `/api/snowflake-syncer/status` | Return last sync status |

---

## Development

```bash
npm run build
npm run watch
```

Run together with the main application (from `Backend/`):

```bash
npm run develop:all
```

---

## Known issues / TODOs

| Area | Issue |
|---|---|
| Route auth | Inbound routes (`/sync`, `/status`) have no authentication — secure before production. |
| Sync status | `getStatus()` always returns `{ status: 'unknown' }`. Implement persistence if needed. |
| Full sync | Only sends `POST` events during a full sync; no deduplication or conflict resolution. |
| Bulk ops | `createMany` / `deleteMany` insert one row per document; consider batching for large datasets. |
| Connection resilience | Connection auto-reconnects on next call but has no explicit keep-alive or exponential back-off. |