Client Rules Editor Kit: JSON Schemas, Approvals, and Audit Log (Retool + Softr)

Pick one path to start (you can support both if your clients run mixed stacks):

• Retool + Postgres: Best for B2B clients who already authenticate to a Retool portal and where you control a Postgres config store.
• Softr + Airtable/Softr DB: Best for lighter client portals or where the client insists on Airtable. Prefer Softr DB if you expect high volume.

What “done” looks like

• Clients edit rules via constrained forms (no free‑text JSON).
• Every submit runs server‑side JSON Schema validation and returns human‑readable errors in <2s.
• Drafts move through Pending → Approved/Rejected, with approver notes.
• Only approved JSON versions get written to the live Config store.
• An append‑only, hash‑chained audit_log records who/when/what (+ diffs).

Prereqs

• A Postgres instance (for Retool path and/or the audit log)
• An automation runner (n8n recommended here) with an HTTP endpoint
• Your portal (Retool or Softr) with end‑user auth and role scoping

Status states (unified for both variants)

• draft: created by end user but not submitted
• pending: submitted; awaiting approval
• approved: approved; frozen; written to Config store
• rejected: declined; stays readable with notes
• superseded: replaced by a newer approved ruleset

Tables (logical model)

• rule_drafts: proposals from clients; status lifecycle lives here
• config_rulesets: the approved, versioned JSON used in production
• approvals: who approved/rejected a draft and why
• audit_log: append‑only, hash‑chained ledger of all proposals, decisions, and writes

Use one schema per ruleset type. Keep them small, explicit, and friendly to error messages. These are 2020‑12 JSON Schemas.

Routing thresholds — schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Routing thresholds ruleset",
  "type": "object",
  "required": ["version", "rules"],
  "properties": {
    "version": {"type": "integer", "minimum": 1},
    "rules": {
      "type": "array",
      "minItems": 1,
      "items": {"$ref": "#/definitions/rule"}
    }
  },
  "definitions": {
    "rule": {
      "type": "object",
      "required": ["id", "metric", "op", "threshold", "route_to"],
      "properties": {
        "id": {"type": "string", "pattern": "^[a-z0-9_-]{3,32}$"},
        "metric": {"type": "string", "enum": ["lead_score", "aov_usd", "arr_usd", "ticket_age_mins"]},
        "op": {"type": "string", "enum": [">", ">=", "<", "<=", "==", "!="]},
        "threshold": {"type": "number"},
        "time_window_mins": {"type": "integer", "minimum": 1, "maximum": 10080},
        "route_to": {"type": "string", "enum": ["tier1", "tier2", "sales", "support", "vip"]},
        "effective_at": {"type": "string", "format": "date-time"}
      },
      "additionalProperties": false
    }
  },
  "additionalProperties": false
}

Routing thresholds — example pack

{
  "version": 3,
  "rules": [
    {"id": "vip_by_arr", "metric": "arr_usd", "op": ">=", "threshold": 50000, "route_to": "vip"},
    {"id": "old_tickets", "metric": "ticket_age_mins", "op": ">", "threshold": 240, "route_to": "tier2"}
  ]
}

SLA windows — schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "SLA windows by priority",
  "type": "object",
  "required": ["version", "priorities"],
  "properties": {
    "version": {"type": "integer", "minimum": 1},
    "priorities": {
      "type": "array",
      "minItems": 1,
      "items": {
        "type": "object",
        "required": ["name", "respond_within_mins"],
        "properties": {
          "name": {"type": "string", "enum": ["p0", "p1", "p2", "p3"]},
          "respond_within_mins": {"type": "integer", "minimum": 5, "maximum": 10080},
          "resolve_within_mins": {"type": "integer", "minimum": 5, "maximum": 10080},
          "business_hours": {"type": "boolean"},
          "timezone": {"type": "string", "pattern": "^[A-Za-z_]+\\/[A-Za-z_]+$"}
        },
        "additionalProperties": false
      }
    }
  },
  "additionalProperties": false
}

SLA windows — example pack

{
  "version": 1,
  "priorities": [
    {"name": "p0", "respond_within_mins": 15, "resolve_within_mins": 240, "business_hours": false, "timezone": "America/Chicago"},
    {"name": "p2", "respond_within_mins": 240, "resolve_within_mins": 2880, "business_hours": true, "timezone": "America/Chicago"}
  ]
}

PII masking — schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "PII masking rules",
  "type": "object",
  "required": ["version", "fields"],
  "properties": {
    "version": {"type": "integer", "minimum": 1},
    "fields": {
      "type": "array",
      "items": {
        "type": "object",
        "required": ["field", "mask"],
        "properties": {
          "field": {"type": "string", "pattern": "^[a-zA-Z0-9_]{2,64}$"},
          "mask": {"type": "string", "enum": ["full", "partial", "hash"]},
          "reveal_for_roles": {"type": "array", "items": {"type": "string", "enum": ["admin", "ops", "auditor"]}},
          "partial_keep_last": {"type": "integer", "minimum": 1, "maximum": 10}
        },
        "allOf": [
          {"if": {"properties": {"mask": {"const": "partial"}}},
           "then": {"required": ["partial_keep_last"]}}
        ],
        "additionalProperties": false
      }
    }
  },
  "additionalProperties": false
}

PII masking — example pack

{
  "version": 2,
  "fields": [
    {"field": "email", "mask": "partial", "partial_keep_last": 3, "reveal_for_roles": ["admin", "auditor"]},
    {"field": "ssn", "mask": "hash"}
  ]
}

Use Retool’s JSON‑Schema‑driven form (or standard Form with mirrored constraints) and a Workflow/API call that re‑validates with the same schema before any write. Below is a minimal, copy‑paste starting point.

Postgres DDL — drafts and live rulesets

create table if not exists rule_drafts (
  id uuid primary key default gen_random_uuid(),
  client_id text not null,
  type text not null check (type in ('routing','sla','pii')),
  proposed_json jsonb not null,
  status text not null check (status in ('draft','pending','approved','rejected','superseded')) default 'draft',
  submitted_by text,
  submitted_at timestamptz,
  reviewed_by text,
  reviewed_at timestamptz,
  rejection_reason text,
  created_at timestamptz not null default now()
);

create table if not exists config_rulesets (
  id uuid primary key default gen_random_uuid(),
  client_id text not null,
  type text not null check (type in ('routing','sla','pii')),
  version int not null,
  rules jsonb not null,
  status text not null check (status in ('active','superseded')) default 'active',
  created_by text not null,
  created_at timestamptz not null default now()
);

create unique index on config_rulesets (client_id, type, status) where status='active';

Retool app — minimal JSON (component + events)

{
  "version": 1,
  "components": [
    {
      "type": "JSONSchemaForm",
      "name": "rulesForm",
      "schema": "{{ resources.schemas.value[tabType.value] }}",
      "uiSchema": {"ui:submitButtonOptions": {"norender": false, "submitText": "Submit for approval"}},
      "onSubmit": [
        {"type": "run", "query": "validateAndStage"}
      ]
    },
    {"type": "Table", "name": "approvalQueue", "data": "{{ getPendingDrafts.data }}"}
  ],
  "queries": [
    {"name": "getPendingDrafts", "resource": "postgres", "type": "sql", "text": "select * from rule_drafts where status='pending' order by submitted_at desc"},
    {"name": "validateAndStage", "type": "restapi", "resource": "validator_api", "method": "POST", "url": "/validate",
     "body": {"type": "{{ tabType.value }}", "payload": "{{ rulesForm.data }}", "client_id": "{{ currentUser.client_id }}"},
     "onSuccess": [
       {"type": "run", "query": "upsertDraft"},
       {"type": "notification", "message": "Draft submitted for approval"}
     ],
     "onFailure": [
       {"type": "notification", "message": "{{ formatErrors.data }}", "notificationType": "error"}
     ]
    },
    {"name": "upsertDraft", "resource": "postgres", "type": "sql",
     "text": "insert into rule_drafts (client_id, type, proposed_json, status, submitted_by, submitted_at) values ({{ currentUser.client_id }}, {{ tabType.value }}, {{ validateAndStage.data.sanitized_json }}, 'pending', {{ currentUser.email }}, now()) returning id"}
  ]
}

Approver action (Retool Table row action → Workflow/API)

• approveDraft(draft_id, comment?) → validates again (belt‑and‑suspenders), writes to config_rulesets with version = last_version+1, flips previous active to superseded, marks draft approved, appends to audit_log.
• rejectDraft(draft_id, reason) → marks rejected, appends to audit_log.

Security notes

• Use Retool External Users or SSO; assign roles “client_editor” (create/submit) and “client_approver” (approve). Keep “approver” internal if your contract requires.

Pattern: Softr page writes the client’s JSON into a long‑text field on rule_drafts only after a synchronous Workflow → Webhook to your validator returns ok. If validation fails, you show the error toast and never persist.

Page structure

• Rules Editor page (visible to role: client_editor): segmented by type (routing | sla | pii) with guided controls (no free JSON textarea).
• Approval Queue page (role: client_approver): list of pending drafts with Approve/Reject actions.

Field map (Airtable or Softr DB)

• rule_drafts
  • id (UUID or record id)
  • client_id (text)
  • type (single select: routing | sla | pii)
  • proposed_json (long text)
  • status (single select: draft | pending | approved | rejected | superseded)
  • submitted_by (email)
  • submitted_at (datetime)
  • reviewed_by (email)
  • reviewed_at (datetime)
  • rejection_reason (long text)

Synchronous Workflow (submit button → Workflow → Webhook)

1. Build JSON from form widgets into a clean object.
2. Call Webhook: POST /validate with { type, payload, client_id }.
3. If 200: create/update rule_drafts row with status=pending and proposed_json from sanitized response. Show success toast.
4. If 4xx: show first 3 error messages; keep user on page.

Example webhook request/response

// Request
{
  "type": "routing",
  "client_id": "acme",
  "payload": {"version": 3, "rules": [{"id":"vip_by_arr","metric":"arr_usd","op":">=","threshold":50000,"route_to":"vip"}]}
}

// 200 OK response (sanitized)
{
  "ok": true,
  "sanitized_json": {"version":3, "rules":[{"id":"vip_by_arr","metric":"arr_usd","op":">=","threshold":50000,"route_to":"vip"}]}
}

// 422 Unprocessable Entity
{
  "ok": false,
  "errors": ["rules[0].op must be one of: >, >=, <, <=, ==, !="],
  "path": ["rules",0,"op"]
}

Throughput tips

• Airtable: watch ~5 req/s/base ceilings; batch reads/writes and cache reference lists client‑side. If you hit limits, move the tables to Softr DB.
• Keep synchronous validations under ~2s; kick off heavy cross‑system checks async after the draft is created.

Drop this in n8n as a subworkflow called validate_rules_payload. It validates against one of the three schemas, returns sanitized JSON on success, or a 422 with error messages on failure.

n8n workflow export (trimmed)

{
  "name": "validate_rules_payload",
  "nodes": [
    {"parameters": {"path": "validate", "options": {"responseCode": 200}}, "id": "Webhook", "name": "Webhook", "type": "n8n-nodes-base.webhook", "typeVersion": 1, "position": [200, 200] },
    {"parameters": {"functionCode": "const Ajv = require('ajv');\nconst ajv = new Ajv({allErrors:true, strict:true});\nconst body = items[0].json;\nconst type = body.type;\nconst payload = body.payload;\nconst schemas = {\n  routing: $json.schemas.routing,\n  sla: $json.schemas.sla,\n  pii: $json.schemas.pii\n};\nif(!schemas[type]) {throw new Error('Unknown schema type');}\nconst validate = ajv.compile(schemas[type]);\nconst ok = validate(payload);\nif(!ok){\n  return [{json:{status:422, ok:false, errors:validate.errors.map(e=>`${e.instancePath||'(root)'} ${e.message}`)}}];\n}\nreturn [{json:{status:200, ok:true, sanitized_json:payload}}];"}, "id": "Code", "name": "Code (AJV)", "type": "n8n-nodes-base.code", "typeVersion": 2, "position": [460, 200] },
    {"parameters": {"responseBody": "={{$json}}", "responseCode": "={{$json.status}}"}, "id": "Respond", "name": "Respond", "type": "n8n-nodes-base.respondToWebhook", "typeVersion": 1, "position": [720, 200] }
  ],
  "connections": {"Webhook": {"main": [[{"node": "Code (AJV)", "type": "main", "index": 0}]]}, "Code (AJV)": {"main": [[{"node": "Respond", "type": "main", "index": 0}]]}},
  "meta": {"schemas": {"routing": {/* paste routing schema */}, "sla": {/* paste SLA schema */}, "pii": {/* paste PII schema */}}}
}

Usage from Retool/Softr

• POST /validate with { type, payload, client_id }
• Treat any 4xx as blocking; never persist until you get ok:true.
• Optionally add a second step that normalizes keys, sorts arrays, or injects defaults before returning sanitized_json.

This pattern makes tampering obvious and prevents UPDATE/DELETE on the ledger.

Enable crypto and table

create extension if not exists pgcrypto;

create table if not exists audit_log (
  id bigserial primary key,
  occurred_at timestamptz not null default now(),
  actor text not null,
  action text not null, -- e.g., 'draft_submitted','draft_approved','ruleset_activated'
  target_table text not null,
  target_id text not null,
  diff jsonb, -- patch or summary
  prev_hash bytea,
  row_hash bytea not null
);

Hash function + trigger

create or replace function audit_row_hash(ts timestamptz, actor text, action text, target_table text, target_id text, diff jsonb, prev bytea)
returns bytea language sql immutable as $$
  select digest(coalesce(to_jsonb($1)::text,'') || '|' || $2 || '|' || $3 || '|' || $4 || '|' || $5 || '|' || coalesce($6::text,'') || '|' || encode(coalesce($7,''), 'hex'), 'sha256');
$$;

create or replace function audit_log_before_insert()
returns trigger language plpgsql as $$
declare last_hash bytea;
begin
  select row_hash into last_hash from audit_log order by id desc limit 1;
  new.prev_hash := last_hash;
  new.row_hash := audit_row_hash(new.occurred_at, new.actor, new.action, new.target_table, new.target_id, new.diff, new.prev_hash);
  return new;
end; $$;

create trigger audit_log_bi before insert on audit_log
for each row execute function audit_log_before_insert();

-- Hard block updates/deletes
create or replace function audit_log_no_change()
returns trigger language plpgsql as $$ begin raise exception 'audit_log is append-only'; end; $$;
create trigger audit_log_bu before update on audit_log for each row execute function audit_log_no_change();
create trigger audit_log_bd before delete on audit_log for each row execute function audit_log_no_change();

Sample writes

-- on submit
insert into audit_log (actor, action, target_table, target_id, diff)
values ('user:alice@client.com','draft_submitted','rule_drafts','<draft_id>', jsonb_build_object('type','routing'));

-- on approve + activate
insert into audit_log (actor, action, target_table, target_id, diff)
values ('user:ops@you.com','ruleset_activated','config_rulesets','<ruleset_id>', jsonb_build_object('version', 4, 'client_id','acme','type','routing'));

Chain verification (periodic job)

with chained as (
  select id, row_hash, prev_hash,
         lag(row_hash) over (order by id) as should_equal
  from audit_log
)
select * from chained where coalesce(prev_hash,'') <> coalesce(should_equal,'');

Tip: Also enable pgAudit (SQL‑level evidence) if your platform allows extensions.

Keep the sync path snappy and shift heavy checks to async.

Target budgets (good UX on typical SMB portals)

• Client submit → server‑side validate → toast: under 2s
• Approve → write to Config + audit append: under 1s
• Async enrichment (e.g., cross‑system dry‑run): any length, but update a “check_status” field and notify on completion

Timeouts and limits to design around

• Retool synchronous response blocks: hard cap around 120s per block; keep validate/write in the fast path, move enrichment to async.
• Airtable API: roughly 5 req/s per base; batch operations and leverage Softr DB if you outgrow Airtable.

Rollback and safety rails

• Version every approved ruleset; never mutate in place.
• Keep a “dry‑run_config_id” toggle in your app so clients can preview routing/SLA effects before promotion.
• Always re‑validate on Approve even if you validated on Submit (defense in depth).