Migrating Clawd.bot Rating Data to OpenClaw

1. Introduction

If you’re a developer or technical lead tasked with moving historic rating data from the now‑deprecated Clawd.bot system to OpenClaw, you’ve come to the right place. This guide walks you through every phase—export, transformation, import, and validation—while highlighting best‑practice tips that keep your migration smooth and error‑free.

OpenClaw, the next‑generation rating engine built on UBOS’s OpenClaw hosting guide, offers a modern API, scalable storage, and built‑in analytics. By following the steps below, you’ll preserve every rating record, maintain data integrity, and unlock the full power of OpenClaw’s advanced features.

2. Background on Clawd.bot → Moltbot → OpenClaw Name Transition

Understanding the naming evolution helps avoid confusion when locating legacy documentation or code references.

• Clawd.bot – The original rating micro‑service launched in 2018. It stored ratings in a custom NoSQL format and exposed a REST endpoint /ratings.
• Moltbot – A transitional fork introduced in 2020 to add basic analytics. Moltbot kept the same data model but added a metadata field.
• OpenClaw – The current, fully open‑source platform released in 2023. It uses a relational schema, supports GraphQL, and integrates with UBOS’s workflow automation studio.

All three systems share a core concept—each rating record contains a user_id, item_id, score, and timestamp. The migration focuses on normalizing these fields to OpenClaw’s ratings table while preserving any additional metadata introduced by Moltbot.

3. Exporting Legacy Rating Data from Clawd.bot

Before you can transform data, you need a clean, complete export. Follow these steps:

3.1. Prepare the Export Environment

1. Spin up a temporary UBOS instance or use your local development machine with node ≥ 18.
2. Install the official clawd-bot-cli package:

   npm i -g clawd-bot-cli

3. Authenticate against the legacy endpoint using the API key stored in .env:

   export CLawd_API_KEY=YOUR_LEGACY_KEY

3.2. Run the Export Command

The CLI streams data directly to a JSON Lines file, which is ideal for large datasets.

clawd-bot-cli export --format jsonl --output ./legacy-ratings.jsonl

Key flags:

• --format jsonl – One JSON object per line, easy to parse.
• --output – Destination path; keep it on a secure volume.

3.3. Verify Export Integrity

Run a quick line count and checksum comparison against the source database:

wc -l ./legacy-ratings.jsonl   # line count
sha256sum ./legacy-ratings.jsonl   # checksum

Store the checksum in a safe location; you’ll use it later to confirm a successful import.

4. Transforming Data for OpenClaw Compatibility

OpenClaw expects a relational schema with explicit column types. The transformation script will:

• Map legacy fields to OpenClaw columns.
• Convert timestamps to ISO‑8601 UTC.
• Flatten any metadata objects into JSONB columns.

4.1. Set Up the Transformation Toolkit

We recommend using Python with pandas and sqlalchemy for type safety.

pip install pandas sqlalchemy psycopg2-binary

4.2. Sample Transformation Script

Save the following as transform.py and adjust the DB connection string to your OpenClaw instance.

import json
import pandas as pd
from sqlalchemy import create_engine, Table, Column, Integer, String, Float, DateTime, MetaData, JSON

# 1️⃣ Load legacy JSONL
df = pd.read_json('legacy-ratings.jsonl', lines=True)

# 2️⃣ Rename columns to match OpenClaw schema
df = df.rename(columns={
    'user_id': 'user_id',
    'item_id': 'item_id',
    'score':   'rating',
    'timestamp': 'created_at',
    'metadata': 'extra_info'
})

# 3️⃣ Convert timestamps
df['created_at'] = pd.to_datetime(df['created_at'], utc=True).dt.isoformat()

# 4️⃣ Ensure correct dtypes
df['rating'] = df['rating'].astype(float)

# 5️⃣ Prepare SQLAlchemy engine (PostgreSQL example)
engine = create_engine('postgresql://openclaw_user:password@localhost:5432/openclaw')
metadata = MetaData()

ratings = Table('ratings', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', String, nullable=False),
    Column('item_id', String, nullable=False),
    Column('rating', Float, nullable=False),
    Column('created_at', DateTime, nullable=False),
    Column('extra_info', JSON, nullable=True)
)

metadata.create_all(engine)  # creates table if not exists

# 6️⃣ Insert transformed rows
with engine.begin() as conn:
    for _, row in df.iterrows():
        insert_stmt = ratings.insert().values(
            user_id=row['user_id'],
            item_id=row['item_id'],
            rating=row['rating'],
            created_at=row['created_at'],
            extra_info=row.get('extra_info')
        )
        conn.execute(insert_stmt)

print('✅ Transformation and load complete')

4.3. Run the Script and Capture Logs

Execute the script and redirect output to a log file for later audit:

python transform.py &> migration.log

Review migration.log for any rows that failed validation; the script will abort on the first error to preserve atomicity.

5. Importing Data into OpenClaw

With the transformed data already loaded via the script above, the import step is essentially complete. However, you may need to trigger OpenClaw’s internal indexing and cache warm‑up.

5.1. Refresh Materialized Views

OpenClaw uses materialized views for fast rating aggregation. Run the following SQL command through the UBOS Workflow automation studio or any SQL client:

REFRESH MATERIALIZED VIEW CONCURRENTLY rating_summary;

5.2. Warm‑up the Cache

Issue a lightweight API call that forces OpenClaw to load recent ratings into Redis:

curl -X GET "https://api.openclaw.io/v1/ratings?limit=10" -H "Authorization: Bearer YOUR_TOKEN"

5.3. Record the New Checksum

After import, compute a checksum of the ratings table and compare it to the legacy checksum captured earlier. This sanity check ensures no rows were lost.

SELECT md5(string_agg(id::text, ',' ORDER BY id)) AS table_checksum FROM ratings;

6. Verification and Validation Steps

Verification is the final safety net. Follow the checklist below to confirm a successful migration.

6.1. Row Count Match

Compare the line count from the export with the row count in OpenClaw:

# Legacy count (saved earlier)
echo "Legacy rows: $(wc -l ./legacy-ratings.jsonl | awk '{print $1}')"

# OpenClaw count
psql -d openclaw -c "SELECT COUNT(*) FROM ratings;"

6.2. Spot‑Check Random Samples

Pick 5 random IDs and verify field‑by‑field equality.

SELECT * FROM ratings WHERE id IN (SELECT id FROM ratings ORDER BY random() LIMIT 5);

6.3. Integrity Constraints

Run OpenClaw’s built‑in health check endpoint:

curl -X GET "https://api.openclaw.io/v1/health" -H "Authorization: Bearer YOUR_TOKEN"

A response of {"status":"ok"} indicates that foreign keys, unique indexes, and JSONB validation passed.

6.4. Business‑Logic Validation

Run a quick aggregation query that mirrors a core KPI (e.g., average rating per item) and compare it to the legacy analytics export.

SELECT item_id, AVG(rating) AS avg_rating FROM ratings GROUP BY item_id ORDER BY avg_rating DESC LIMIT 10;

7. Common Pitfalls and Troubleshooting

Even with a solid plan, migrations can hit snags. Below are the most frequent issues and how to resolve them.

8. Conclusion and Next Steps

By exporting, transforming, importing, and rigorously validating your data, you can retire Clawd.bot with confidence and start leveraging OpenClaw’s modern analytics, real‑time scoring, and seamless integration with UBOS’s AI marketing agents.

Next actions you might consider:

• Enable OpenClaw’s Enterprise AI platform features for predictive rating models.
• Set up automated nightly migrations for any residual legacy data using the Workflow automation studio.
• Explore the UBOS template marketplace for pre‑built dashboards that visualize rating trends out‑of‑the‑box.

Remember, a successful migration is not just about moving rows—it’s about preserving business logic, ensuring data quality, and unlocking new capabilities for your product teams.