Cloud Sync API

Cloud Sync API is a lightweight Node.js + MySQL backend for syncing offline device data with a central server.

It provides a universal sync protocol with separate endpoints for:

• Push local changes to the server
• Pull data from server tables
• Check sync service/database status

What This Project Is About

This project is designed for multi-device systems (for example POS, inventory, or field apps) where each client can:

• Collect data locally while offline
• Push batched changes when online
• Pull fresh server state by table

Security note: table names are protected by an ALLOWED_TABLES allowlist to prevent SQL injection through dynamic table interpolation.

Tech Stack

• Node.js
• Express
• MySQL (mysql2/promise)
• dotenv

Usage

1) Install Dependencies

npm install

2) Configure Environment

Copy sample.env to .env and set real values:

DB_HOST=localhost
DB_USER=root
DB_PASS=your_password
DB_NAME=your_database_name
ALLOWED_TABLES=sales,products,customers,item_sales,product_stock

3) Start the Server

node server.js

Default port is 4000 unless PORT is set.

4) Health Check

GET /health

Example response:

{
  "status": "Sync API running"
}

API Wiki

Base URL examples:

• http://localhost:4000/sync
• http://localhost:4000/sync/push
• http://localhost:4000/sync/pull
• http://localhost:4000/sync/status

1) Push Sync

Endpoint:

POST /sync/push

Backward compatibility endpoint:

POST /sync

Description:

• Accepts a batch of operations (insert, update, delete)
• Executes them in a single database transaction
• Rejects operations targeting tables not listed in ALLOWED_TABLES

Request body example:

{
  "device_id": "shop_01",
  "operations": [
    {
      "table": "sales",
      "action": "insert",
      "data": {
        "id": "123",
        "customer_id": "C001",
        "total": 1000
      }
    },
    {
      "table": "product_stock",
      "action": "update",
      "data": {
        "id": "P001",
        "quantity": 20
      }
    }
  ]
}

Success response example:

{
  "success": true
}

Common error responses:

{
  "success": false,
  "error": "Invalid table"
}

{
  "success": false,
  "error": "Sync failed"
}

2) Pull Sync

Endpoint:

POST /sync/pull

Description:

• Pulls rows from selected allowed tables
• If tables is omitted, all allowed tables are queried
• limit defaults to 1000 and is capped at 5000

Request body example:

{
  "tables": ["sales", "product_stock"],
  "limit": 500
}

Success response example:

{
  "success": true,
  "data": {
    "sales": [],
    "product_stock": []
  },
  "limit": 500
}

Common error responses:

{
  "success": false,
  "error": "Invalid table"
}

{
  "success": false,
  "error": "No tables configured"
}

{
  "success": false,
  "error": "Pull sync failed"
}

3) Sync Status

Endpoint:

GET /sync/status

Description:

• Verifies database availability
• Returns sync service status and active allowed table list

Success response example:

{
  "success": true,
  "status": "ok",
  "allowed_tables": [
    "sales",
    "products",
    "customers",
    "item_sales",
    "product_stock"
  ]
}

Failure response example:

{
  "success": false,
  "status": "error",
  "error": "Database unavailable"
}

Notes

• device_id is accepted in push payloads and reserved for auditing/tracking workflows.
• Ensure all client-side table values match names in ALLOWED_TABLES exactly.