🏗System Architecture

API Contract — all providers use the same 4 endpoints

🗄Database Schema

All tables use a simple structure: each row is one JSON record, keyed by its id field. This makes the sync logic trivial — upsert by ID on push, merge by ID on pull.

SQL Schema (MySQL / PostgreSQL / Azure SQL / RDS)

-- Run this once to create all GilRobo sync tables
-- Works on MySQL 5.7+, PostgreSQL 12+, Azure SQL, Amazon RDS

CREATE TABLE IF NOT EXISTS gilrobo_sync (
    table_name   VARCHAR(80)  NOT NULL,
    record_id    VARCHAR(120) NOT NULL,
    data         JSON         NOT NULL,
    pushed_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    pulled_at    DATETIME     NULL,
    PRIMARY KEY (table_name, record_id)
);

-- Optional: per-table views for readability
CREATE OR REPLACE VIEW vw_gs_students AS
  SELECT record_id AS id, data, pushed_at FROM gilrobo_sync WHERE table_name='gs_students';

CREATE OR REPLACE VIEW vw_qm_quiz_history AS
  SELECT record_id AS id, data, pushed_at FROM gilrobo_sync WHERE table_name='qm_quiz_history';

-- Index for fast table scans
CREATE INDEX idx_gilrobo_table ON gilrobo_sync(table_name, pushed_at DESC);
SQLCopy

DynamoDB Schema (AWS)

{
  "TableName": "gilrobo_sync",
  "BillingMode": "PAY_PER_REQUEST",
  "KeySchema": [
    { "AttributeName": "pk",  "KeyType": "HASH"  },
    { "AttributeName": "sk",  "KeyType": "RANGE" }
  ],
  "AttributeDefinitions": [
    { "AttributeName": "pk", "AttributeType": "S" },
    { "AttributeName": "sk", "AttributeType": "S" }
  ]
}
-- pk = table_name (e.g. "gs_students")
-- sk = record_id  (e.g. "s_1234567890")
-- Additional attributes: data (Map), pushed_at (String ISO date)
JSONCopy

Cosmos DB Schema (Azure)

// Container: gilrobo_sync
// Partition key: /table_name
// Each document:
{
  "id": "{table_name}#{record_id}",   // Cosmos document ID
  "table_name": "gs_students",
  "record_id": "s_1234567890",
  "data": { ... },                    // Full GilRobo record
  "pushed_at": "2025-03-13T10:00:00Z"
}
JSONCopy

Key Data Structures

🔵WordPress Setup

Step 1 — Install the GilRobo Sync Plugin

Create a file gilrobo-sync/gilrobo-sync.php in your WordPress wp-content/plugins/ folder with this code, then activate it in WP Admin → Plugins.

<?php
/**
 * Plugin Name: GilRobo Sync
 * Description: Bidirectional sync endpoint for GilRobo AI Learning Family app.
 * Version: 1.0
 * Author: GilRobo
 */
if(!defined('ABSPATH')) exit;

register_activation_hook(__FILE__, 'gilrobo_create_table');
function gilrobo_create_table(){
    global $wpdb;
    $table = $wpdb->prefix.'gilrobo_sync';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE IF NOT EXISTS $table (
        table_name  VARCHAR(80)  NOT NULL,
        record_id   VARCHAR(120) NOT NULL,
        data        LONGTEXT     NOT NULL,
        pushed_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (table_name, record_id),
        INDEX idx_table (table_name, pushed_at DESC)
    ) $charset;";
    require_once ABSPATH.'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

add_action('rest_api_init', 'gilrobo_register_routes');
function gilrobo_register_routes(){
    $ns = 'gilrobo/v1';
    // Health check
    register_rest_route($ns, '/ping', ['methods'=>'GET','callback'=>fn()=>['status'=>'ok','time'=>current_time('c')],'permission_callback'=>'gilrobo_auth']);
    // Create table (no-op — table is created on activation, kept for API parity)
    register_rest_route($ns, '/tables', ['methods'=>'POST','callback'=>fn($r)=>['created'=>true,'table'=>sanitize_text_field($r['table'])],'permission_callback'=>'gilrobo_auth']);
    // Push (upsert)
    register_rest_route($ns, '/sync/(?P<table>[a-z0-9_]+)', ['methods'=>'PUT','callback'=>'gilrobo_push','permission_callback'=>'gilrobo_auth']);
    // Pull
    register_rest_route($ns, '/sync/(?P<table>[a-z0-9_]+)', ['methods'=>'GET','callback'=>'gilrobo_pull','permission_callback'=>'gilrobo_auth']);
}

function gilrobo_auth(WP_REST_Request $request){
    // Accepts: Application Password (Basic Auth) or custom header X-GilRobo-Key
    $custom_key = get_option('gilrobo_api_key','');
    if($custom_key && $request->get_header('X-GilRobo-Key')===$custom_key) return true;
    return current_user_can('manage_options') || is_user_logged_in();
}

function gilrobo_push(WP_REST_Request $request){
    global $wpdb;
    $table_name = $wpdb->prefix.'gilrobo_sync';
    $gr_table   = sanitize_text_field($request['table']);
    $body       = $request->get_json_params();
    $records    = $body['data'] ?? [];
    if(!is_array($records)) return new WP_Error('invalid','data must be array',['status'=>400]);
    $count = 0;
    foreach($records as $rec){
        $id = sanitize_text_field($rec['id'] ?? uniqid('r_'));
        $wpdb->replace($table_name,[
            'table_name' => $gr_table,
            'record_id'  => $id,
            'data'       => wp_json_encode($rec),
            'pushed_at'  => current_time('mysql'),
        ]);
        $count++;
    }
    return ['ok'=>true,'count'=>$count,'table'=>$gr_table];
}

function gilrobo_pull(WP_REST_Request $request){
    global $wpdb;
    $table_name = $wpdb->prefix.'gilrobo_sync';
    $gr_table   = sanitize_text_field($request['table']);
    $rows = $wpdb->get_results(
        $wpdb->prepare("SELECT data FROM $table_name WHERE table_name=%s ORDER BY pushed_at DESC",$gr_table)
    );
    $data = array_map(fn($r)=>json_decode($r->data,true),$rows);
    return ['ok'=>true,'data'=>$data,'count'=>count($data),'table'=>$gr_table];
}

// Admin settings page to set custom API key
add_action('admin_menu','gilrobo_admin_menu');
function gilrobo_admin_menu(){add_options_page('GilRobo Sync','GilRobo Sync','manage_options','gilrobo-sync','gilrobo_settings_page');}
function gilrobo_settings_page(){
    if(isset($_POST['gilrobo_key'])){update_option('gilrobo_api_key',sanitize_text_field($_POST['gilrobo_key']));echo'<div class="updated"><p>Saved!</p></div>';}
    $key=get_option('gilrobo_api_key','');
    echo '<div class="wrap"><h1>GilRobo Sync Settings</h1>
    <form method="post">
    <table class="form-table">
    <tr><th>Custom API Key (X-GilRobo-Key)</th>
    <td><input name="gilrobo_key" value="'.esc_attr($key).'" style="width:400px"/></td></tr>
    <tr><th>REST Base URL</th>
    <td><code>'.get_rest_url(null,"gilrobo/v1").'</code> — use this in the GilRobo app.</td></tr>
    </table>
    <p class="submit"><button class="button-primary">Save</button></p>
    </form></div>';
}
PHPCopy

Step 2 — Get your REST URL

After activating, go to WP Admin → Settings → GilRobo Sync. Copy the REST Base URL shown there. It looks like:

https://yourschool.com/wp-json/gilrobo/v1Copy

Step 3 — Configure the app

1. Click ☁️ (floating button, bottom-right of GilRobo app)
2. Select provider: WordPress REST API
3. Paste the REST URL into WordPress Site URL
4. In WP Admin → Users → your user → Application Passwords, create a password and paste it (format: base64(username:app-password))
5. Click 🔌 Test Connection — should say "Connected ✅"
6. Click 🗄️ Create Tables, then 🔁 Two-Way Sync

add_action('rest_api_init',function(){
  header('Access-Control-Allow-Origin: *');
  header('Access-Control-Allow-Headers: Authorization, Content-Type, X-GilRobo-Key, X-WP-Nonce');
});

☁️Microsoft Azure Setup

Option A — Azure SQL + Azure Functions (Node.js)

// Azure Function: gilrobo-sync/index.js
// Set these Application Settings in Azure Portal:
//   SQL_CONN  = your Azure SQL connection string
//   GILROBO_KEY = your chosen API key

const sql = require('mssql');
const crypto = require('crypto');

module.exports = async function(context, req){
  // Auth
  const key = req.headers['x-api-key'] || req.headers['x-gilrobo-key'] || '';
  if(key !== process.env.GILROBO_KEY)
    return context.res = {status:401, body:{error:'Unauthorized'}};

  const path = req.params.route || '';    // e.g. "sync/gs_students"
  const [,action,table] = path.match(/^(ping|tables|sync\/([a-z0-9_]+))$/) || [];

  await sql.connect(process.env.SQL_CONN);

  if(path === 'ping'){
    return context.res = {body:{status:'ok',time:new Date().toISOString()}};
  }

  if(path === 'tables'){
    await sql.query\`
      IF NOT EXISTS(SELECT * FROM sys.tables WHERE name='gilrobo_sync')
      CREATE TABLE gilrobo_sync(
        table_name  NVARCHAR(80)  NOT NULL,
        record_id   NVARCHAR(120) NOT NULL,
        data        NVARCHAR(MAX) NOT NULL,
        pushed_at   DATETIME2     NOT NULL DEFAULT GETUTCDATE(),
        CONSTRAINT pk_gr PRIMARY KEY(table_name,record_id)
      )\`;
    return context.res = {body:{created:true}};
  }

  if(req.method === 'PUT' && table){  // Push
    const records = (req.body && req.body.data) || [];
    let count = 0;
    for(const rec of records){
      const id = rec.id || crypto.randomUUID();
      await sql.query\`
        MERGE gilrobo_sync AS t
        USING (VALUES(${table},${id},${JSON.stringify(rec)},GETUTCDATE()))
          AS s(table_name,record_id,data,pushed_at)
        ON t.table_name=s.table_name AND t.record_id=s.record_id
        WHEN MATCHED THEN UPDATE SET t.data=s.data, t.pushed_at=s.pushed_at
        WHEN NOT MATCHED THEN INSERT(table_name,record_id,data,pushed_at) VALUES(s.table_name,s.record_id,s.data,s.pushed_at);\`;
      count++;
    }
    return context.res = {body:{ok:true,count,table}};
  }

  if(req.method === 'GET' && table){  // Pull
    const result = await sql.query\`
      SELECT data FROM gilrobo_sync WHERE table_name=${table} ORDER BY pushed_at DESC\`;
    const data = result.recordset.map(r => JSON.parse(r.data));
    return context.res = {body:{ok:true,data,count:data.length}};
  }

  context.res = {status:404,body:{error:'Not found'}};
};
Node.jsCopy

function.json (routing)

{
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "route": "gilrobo/{*route}",
      "methods": ["GET","PUT","POST"]
    },
    { "type": "http", "direction": "out", "name": "res" }
  ]
}
JSONCopy

Configure in GilRobo app

1. Deploy the function app to Azure. Copy the Function URL (e.g. https://myapp.azurewebsites.net/api)
2. In GilRobo → ☁️ → Provider: Microsoft Azure
3. Paste the function base URL (up to /api) into Azure API Endpoint
4. Paste your Function Key (from Azure Portal → Function → Keys) into Azure Function Key
5. Test, Create Tables, Sync

🟠Amazon AWS Setup

Lambda Function (Node.js 20)

// lambda/index.mjs — deploy as a Lambda function
// Environment variables:
//   GILROBO_KEY  = your chosen API key
//   AWS_REGION   = your region (set automatically)

import {DynamoDBClient,PutItemCommand,ScanCommand,CreateTableCommand} from '@aws-sdk/client-dynamodb';
import {marshall,unmarshall} from '@aws-sdk/util-dynamodb';

const ddb = new DynamoDBClient({});
const TABLE = 'gilrobo_sync';

export const handler = async(event) => {
  const key = event.headers?.['x-api-key'] || event.headers?.['X-API-Key'] || '';
  if(key !== process.env.GILROBO_KEY)
    return {statusCode:401,body:JSON.stringify({error:'Unauthorized'})};

  const method = event.httpMethod;
  const path   = (event.pathParameters?.proxy || '').toLowerCase();
  const cors   = {'Access-Control-Allow-Origin':'*','Content-Type':'application/json'};

  if(path === 'ping')
    return {statusCode:200,headers:cors,body:JSON.stringify({status:'ok',time:new Date().toISOString()})};

  if(path === 'tables' && method==='POST'){
    try{
      await ddb.send(new CreateTableCommand({
        TableName: TABLE,
        BillingMode: 'PAY_PER_REQUEST',
        KeySchema: [{AttributeName:'pk',KeyType:'HASH'},{AttributeName:'sk',KeyType:'RANGE'}],
        AttributeDefinitions: [{AttributeName:'pk',AttributeType:'S'},{AttributeName:'sk',AttributeType:'S'}],
      }));
    }catch(e){ if(e.name!=='ResourceInUseException') throw e; }
    return {statusCode:200,headers:cors,body:JSON.stringify({created:true})};
  }

  const match = path.match(/^sync\/([a-z0-9_]+)$/);
  if(!match) return {statusCode:404,headers:cors,body:JSON.stringify({error:'Not found'})};
  const grTable = match[1];

  if(method==='PUT'){  // Push
    const {data=[]} = JSON.parse(event.body||'{}');
    let count=0;
    for(const rec of data){
      const id = rec.id||`r_${Date.now()}_${count}`;
      await ddb.send(new PutItemCommand({
        TableName: TABLE,
        Item: marshall({pk:grTable,sk:id,data:rec,pushed_at:new Date().toISOString()},{removeUndefinedValues:true})
      }));
      count++;
    }
    return {statusCode:200,headers:cors,body:JSON.stringify({ok:true,count,table:grTable})};
  }

  if(method==='GET'){  // Pull
    const result = await ddb.send(new ScanCommand({
      TableName: TABLE,
      FilterExpression: 'pk = :t',
      ExpressionAttributeValues: marshall({':t':grTable})
    }));
    const data = (result.Items||[]).map(i=>unmarshall(i).data).filter(Boolean);
    return {statusCode:200,headers:cors,body:JSON.stringify({ok:true,data,count:data.length})};
  }

  return {statusCode:405,headers:cors,body:JSON.stringify({error:'Method not allowed'})};
};
Node.jsCopy

API Gateway setup

1. Create a REST API in API Gateway
2. Create resource /gilrobo/{proxy+} with Lambda proxy integration
3. Enable API Key Required on the method and create an API key + usage plan
4. Deploy to stage prod
5. Copy the Invoke URL (e.g. https://xxxx.execute-api.eu-west-2.amazonaws.com/prod)

Configure in GilRobo app

1. Provider: Amazon AWS
2. Paste invoke URL into API Gateway Endpoint
3. Paste API Gateway key into API Gateway Key
4. Test Connection → Create Tables → Sync

💜Microsoft Dataverse Setup

Architecture

Step 1 — Register an Azure App

Step 2 — Create custom tables in Power Apps

Dataverse tables for GilRobo follow the naming convention cr_gilrobo_{tablename} where {tablename} replaces underscores with the letter x:

For each table, create these columns in Power Apps → Tables → New table:

Step 3 — Configure in GilRobo app

Provider:         💜 Microsoft Dataverse
Environment URL:  https://yourorg.crm11.dynamics.com
                  (find in Power Platform Admin Center → Environments → your env → Details)
Tenant ID:        from Azure Portal → Azure AD → Overview
App (Client) ID:  from Azure Portal → App registration → Overview
Client Secret:    value you copied when creating the secret
ConfigCopy

Click 🔌 Test Connection — it will request an OAuth2 token and call the Dataverse metadata endpoint. A successful response means the app registration is correctly configured.

Step 4 — Create tables, then sync

1. Click 🗄️ Create Tables — this verifies each table entity is accessible
2. Click 🔁 Two-Way Sync to do an initial full sync
3. Data now lives in your Dataverse environment and can be viewed/edited in Power Apps, built into Power BI dashboards, triggered in Power Automate flows, or used in Dynamics 365

Power BI Integration

Once data is in Dataverse, connect Power BI Desktop to it: Get Data → Dataverse → select your environment → load the cr_gilrobo_* tables. Build dashboards showing quiz trends, student performance, parent consultation history, and more — all refreshed automatically on each GilRobo sync.

Power Automate Integration

Create flows triggered by Dataverse row changes: e.g. "When a new row is added to cr_gilrobogs_students → send a welcome email via Outlook". Or "When a student's quiz average drops below 60% → notify the teacher in Teams".