{"id":"836e9056-ccf9-49ec-aa75-46d2879f8bea","entityType":"agent","slug":"clawhub-skills-1kalin-afrexai-database-engineer","name":"afrexai-database-engineer","canonicalUrl":"https://www.xpersona.co/agent/clawhub-skills-1kalin-afrexai-database-engineer","canonicalPath":"/agent/clawhub-skills-1kalin-afrexai-database-engineer","generatedAt":"2026-10-09T11:45:16.628Z","source":"CLAWHUB","claimStatus":"UNCLAIMED","verificationTier":"NONE","summary":{"evidence":{"source":"editorial-content","verified":true,"confidence":"high","updatedAt":"2026-04-15T00:45:39.800Z","emptyReason":null},"description":"Database Engineering Mastery Database Engineering Mastery Complete database design, optimization, migration, and operations system. From schema design to production monitoring — covers PostgreSQL, MySQL, SQLite, and general SQL patterns. Phase 1 — Schema Design Design Brief Before writing any DDL, fill this out: Normalization Decision Framework | Form | Rule | When to Denormalize | |------|------|---------------------| | 1NF | No repeating group","descriptionLabel":"Technical summary","evidenceSummary":"Capability contract not published. No trust telemetry is available yet. Last updated 4/15/2026.","installCommand":"clawhub skill install skills:1kalin:afrexai-database-engineer","sourceUrl":"https://github.com/openclaw/skills/tree/main/skills/1kalin/afrexai-database-engineer","homepage":null,"primaryLinks":[{"label":"View on ClawHub","url":"https://github.com/openclaw/skills/tree/main/skills/1kalin/afrexai-database-engineer","kind":"source"}],"safetyScore":84,"overallRank":62,"popularityScore":50,"trustScore":null,"claimedByName":null,"isOwner":false,"seoDescription":"Database Engineering Mastery Database Engineering Mastery Complete database design, optimization, migration, and operations system. From schema design to produc"},"coverage":{"evidence":{"source":"public-profile","verified":false,"confidence":"medium","updatedAt":"2026-04-15T00:45:39.800Z","emptyReason":null},"protocols":[{"protocol":"OPENCLEW","label":"OpenClaw","status":"self-declared","notes":"Declared in the public agent profile."}],"capabilities":[{"label":"from","status":"self-declared"},{"label":"on","status":"self-declared"},{"label":"with","status":"self-declared"},{"label":"uses","status":"self-declared"},{"label":"where","status":"self-declared"},{"label":"select","status":"self-declared"},{"label":"connect","status":"self-declared"}],"verifiedCount":0,"selfDeclaredCount":8,"capabilityMatrix":{"rows":[{"key":"OPENCLEW","type":"protocol","support":"unknown","confidenceSource":"profile","notes":"Listed on profile"},{"key":"from","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"},{"key":"on","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"},{"key":"with","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"},{"key":"uses","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"},{"key":"where","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"},{"key":"select","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"},{"key":"connect","type":"capability","support":"supported","confidenceSource":"profile","notes":"Declared in agent profile metadata"}],"flattenedTokens":"protocol:OPENCLEW|unknown|profile capability:from|supported|profile capability:on|supported|profile capability:with|supported|profile capability:uses|supported|profile capability:where|supported|profile capability:select|supported|profile capability:connect|supported|profile"}},"adoption":{"evidence":{"source":"no-adoption-signals","verified":false,"confidence":"low","updatedAt":"2026-04-15T00:45:39.800Z","emptyReason":"No source adoption metrics were available."},"stars":null,"forks":null,"downloads":null,"packageName":null,"latestVersion":null,"tractionLabel":null},"release":{"evidence":{"source":"agent-index","verified":false,"confidence":"medium","updatedAt":"2026-02-25T06:17:16.018Z","emptyReason":null},"lastUpdatedAt":"2026-04-15T00:45:39.800Z","lastCrawledAt":"2026-02-25T06:17:16.018Z","lastIndexedAt":null,"nextCrawlAt":"2026-02-26T06:17:16.018Z","lastVerifiedAt":null,"highlights":[]},"execution":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No published capability contract is available yet."},"installCommand":"clawhub skill install skills:1kalin:afrexai-database-engineer","setupComplexity":"low","setupSteps":["Setup complexity is LOW. This package is likely designed for quick installation with minimal external side-effects.","Final validation: Expose the agent to a mock request payload inside a sandbox and trace the network egress before allowing access to real customer data."],"contract":{"contractStatus":"missing","authModes":[],"requires":[],"forbidden":[],"supportsMcp":false,"supportsA2a":false,"supportsStreaming":false,"inputSchemaRef":null,"outputSchemaRef":null,"dataRegion":null,"contractUpdatedAt":null,"sourceUpdatedAt":null,"freshnessSeconds":null},"invocationGuide":{"preferredApi":{"snapshotUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/snapshot","contractUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/contract","trustUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/trust"},"curlExamples":["curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/snapshot\"","curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/contract\"","curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/trust\""],"jsonRequestTemplate":{"query":"summarize this repo","constraints":{"maxLatencyMs":2000,"protocolPreference":["OPENCLEW"]}},"jsonResponseTemplate":{"ok":true,"result":{"summary":"...","confidence":0.9},"meta":{"source":"CLAWHUB","generatedAt":"2026-10-09T11:45:16.628Z"}},"retryPolicy":{"maxAttempts":3,"backoffMs":[500,1500,3500],"retryableConditions":["HTTP_429","HTTP_503","NETWORK_TIMEOUT"]}},"endpoints":{"dossierUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/dossier","snapshotUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/snapshot","contractUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/contract","trustUrl":"https://www.xpersona.co/api/v1/agents/clawhub-skills-1kalin-afrexai-database-engineer/trust"}},"reliability":{"evidence":{"source":"runtime-metrics","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No trust, reliability, or runtime telemetry is available."},"trust":{"status":"unavailable","handshakeStatus":"UNKNOWN","verificationFreshnessHours":null,"reputationScore":null,"p95LatencyMs":null,"successRate30d":null,"fallbackRate":null,"attempts30d":null,"trustUpdatedAt":null,"trustConfidence":"unknown","sourceUpdatedAt":null,"freshnessSeconds":null},"decisionGuardrails":{"doNotUseIf":["Contract metadata is missing or unavailable for deterministic execution."],"safeUseWhen":[],"riskFlags":["missing_or_unavailable_contract","trust_data_unavailable","schema_references_missing"],"operationalConfidence":"low"},"executionMetrics":{"observedLatencyMsP50":null,"observedLatencyMsP95":null,"estimatedCostUsd":null,"uptime30d":null,"rateLimitRpm":null,"rateLimitBurst":null,"lastVerifiedAt":null,"verificationSource":null},"runtimeMetrics":{"successRate":null,"avgLatencyMs":null,"avgCostUsd":null,"hallucinationRate":null,"retryRate":null,"disputeRate":null,"p50Latency":null,"p95Latency":null,"lastUpdated":null}},"benchmarks":{"evidence":{"source":"no-benchmark-data","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No benchmark suites or observed failure patterns are available."},"suites":[],"failurePatterns":[]},"artifacts":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"high","updatedAt":"2026-04-15T00:45:39.800Z","emptyReason":null},"readme":"# Database Engineering Mastery\n\n> Complete database design, optimization, migration, and operations system. From schema design to production monitoring — covers PostgreSQL, MySQL, SQLite, and general SQL patterns.\n\n## Phase 1 — Schema Design\n\n### Design Brief\n\nBefore writing any DDL, fill this out:\n\n```yaml\nproject: \"\"\ndomain: \"\"\nprimary_use_case: \"OLTP | OLAP | mixed\"\nexpected_scale:\n  rows_year_1: \"\"\n  rows_year_3: \"\"\n  concurrent_users: \"\"\n  read_write_ratio: \"80:20 | 50:50 | 20:80\"\ncompliance: [] # GDPR, HIPAA, PCI-DSS, SOX\nmulti_tenancy: \"none | schema-per-tenant | row-level | database-per-tenant\"\n```\n\n### Normalization Decision Framework\n\n| Form | Rule | When to Denormalize |\n|------|------|---------------------|\n| 1NF | No repeating groups, atomic values | Never skip |\n| 2NF | No partial dependencies on composite keys | Never skip |\n| 3NF | No transitive dependencies | Reporting tables, read-heavy aggregations |\n| BCNF | Every determinant is a candidate key | Rarely needed unless complex key relationships |\n\n**Denormalization triggers:**\n- Query joins > 4 tables consistently\n- Read latency > 100ms on indexed queries\n- Cache invalidation complexity exceeds denormalization maintenance\n- Reporting queries block OLTP workloads\n\n### Naming Conventions\n\n```\nTables:      snake_case, plural (users, order_items, payment_methods)\nColumns:     snake_case, singular (first_name, created_at, is_active)\nPKs:         id (bigint/uuid) or {table_singular}_id\nFKs:         {referenced_table_singular}_id\nIndexes:     idx_{table}_{columns}\nConstraints: chk_{table}_{rule}, uq_{table}_{columns}, fk_{table}_{ref}\nEnums:       Use VARCHAR + CHECK, not DB enums (easier to migrate)\nBooleans:    is_, has_, can_ prefix (is_active, has_subscription)\nTimestamps:  _at suffix (created_at, updated_at, deleted_at)\n```\n\n### Column Type Decision Tree\n\n```\nText < 255 chars, fixed set?     → VARCHAR(N) + CHECK\nText < 255 chars, variable?      → VARCHAR(255)\nText > 255 chars?                → TEXT\nWhole numbers < 2B?              → INTEGER\nWhole numbers > 2B?              → BIGINT\nMoney/financial?                 → NUMERIC(precision, scale) — NEVER float\nTrue/false?                      → BOOLEAN\nDate only?                       → DATE\nDate + time?                     → TIMESTAMPTZ (always with timezone)\nUnique identifier?               → UUID (distributed) or BIGSERIAL (single DB)\nJSON/flexible schema?            → JSONB (Postgres) or JSON (MySQL)\nBinary/file?                     → Store in object storage, reference by URL\nIP address?                      → INET (Postgres) or VARCHAR(45)\nGeospatial?                      → PostGIS geometry/geography types\n```\n\n### Essential Table Template\n\n```sql\nCREATE TABLE {table_name} (\n    id          BIGSERIAL PRIMARY KEY,\n    -- domain columns here --\n    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    created_by  BIGINT REFERENCES users(id),\n    version     INTEGER NOT NULL DEFAULT 1,  -- optimistic locking\n    \n    -- soft delete (optional)\n    deleted_at  TIMESTAMPTZ,\n    \n    -- multi-tenant (optional)  \n    tenant_id   BIGINT NOT NULL REFERENCES tenants(id)\n);\n\n-- Updated_at trigger (PostgreSQL)\nCREATE OR REPLACE FUNCTION update_modified_column()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    NEW.version = OLD.version + 1;\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER trg_{table_name}_updated\n    BEFORE UPDATE ON {table_name}\n    FOR EACH ROW\n    EXECUTE FUNCTION update_modified_column();\n```\n\n### Relationship Patterns\n\n**One-to-Many:**\n```sql\n-- Parent\nCREATE TABLE departments (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);\n-- Child  \nCREATE TABLE employees (\n    id BIGSERIAL PRIMARY KEY,\n    department_id BIGINT NOT NULL REFERENCES departments(id) ON DELETE RESTRICT,\n    -- ON DELETE options: RESTRICT (safe default), CASCADE (children die), SET NULL\n);\nCREATE INDEX idx_employees_department_id ON employees(department_id);\n```\n\n**Many-to-Many:**\n```sql\nCREATE TABLE user_roles (\n    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,\n    granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    granted_by BIGINT REFERENCES users(id),\n    PRIMARY KEY (user_id, role_id)\n);\n```\n\n**Self-Referencing (hierarchy):**\n```sql\nCREATE TABLE categories (\n    id BIGSERIAL PRIMARY KEY,\n    parent_id BIGINT REFERENCES categories(id) ON DELETE CASCADE,\n    name VARCHAR(100) NOT NULL,\n    depth INTEGER NOT NULL DEFAULT 0,\n    path TEXT NOT NULL DEFAULT ''  -- materialized path: '/1/5/12/'\n);\nCREATE INDEX idx_categories_parent ON categories(parent_id);\nCREATE INDEX idx_categories_path ON categories(path text_pattern_ops);\n```\n\n**Polymorphic (avoid if possible, use if you must):**\n```sql\n-- Preferred: separate FKs\nCREATE TABLE comments (\n    id BIGSERIAL PRIMARY KEY,\n    post_id BIGINT REFERENCES posts(id),\n    ticket_id BIGINT REFERENCES tickets(id),\n    body TEXT NOT NULL,\n    CONSTRAINT chk_one_parent CHECK (\n        (post_id IS NOT NULL)::int + (ticket_id IS NOT NULL)::int = 1\n    )\n);\n```\n\n---\n\n## Phase 2 — Indexing Strategy\n\n### Index Type Selection\n\n| Index Type | Use When | Example |\n|-----------|----------|---------|\n| B-tree (default) | Equality, range, sorting, LIKE 'prefix%' | `CREATE INDEX idx_users_email ON users(email)` |\n| Hash | Equality only, no range | `CREATE INDEX idx_sessions_token ON sessions USING hash(token)` |\n| GIN | JSONB, full-text search, arrays, tsvector | `CREATE INDEX idx_products_tags ON products USING gin(tags)` |\n| GiST | Geospatial, range types, nearest-neighbor | `CREATE INDEX idx_locations_geom ON locations USING gist(geom)` |\n| BRIN | Very large tables with natural ordering (time-series) | `CREATE INDEX idx_events_created ON events USING brin(created_at)` |\n| Partial | Subset of rows | `CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending'` |\n| Covering | Include columns to avoid table lookup | `CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (status, total)` |\n\n### Indexing Rules\n\n1. **Always index:** Foreign keys, columns in WHERE/JOIN/ORDER BY\n2. **Never index:** Low-cardinality columns alone (boolean, status with 3 values) — combine in composite\n3. **Composite order:** Most selective column first, then left-to-right matches query patterns\n4. **Watch write overhead:** Each index slows INSERT/UPDATE. >8 indexes on a write-heavy table = review\n5. **Unused index audit:** Run monthly — drop indexes with 0 scans\n\n### Find Unused Indexes (PostgreSQL)\n\n```sql\nSELECT schemaname, tablename, indexname, idx_scan, \n       pg_size_pretty(pg_relation_size(indexrelid)) as size\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0 AND indexrelid NOT IN (\n    SELECT conindid FROM pg_constraint WHERE contype IN ('p', 'u')\n)\nORDER BY pg_relation_size(indexrelid) DESC;\n```\n\n### Find Missing Indexes (PostgreSQL)\n\n```sql\nSELECT relname, seq_scan, seq_tup_read, \n       idx_scan, seq_tup_read / GREATEST(seq_scan, 1) as avg_tuples_per_scan\nFROM pg_stat_user_tables\nWHERE seq_scan > 100 AND seq_tup_read > 10000\nORDER BY seq_tup_read DESC;\n-- High seq_scan + high seq_tup_read = missing index candidate\n```\n\n---\n\n## Phase 3 — Query Optimization\n\n### EXPLAIN Interpretation\n\n```sql\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;\n```\n\n**Red flags in query plans:**\n| Pattern | Problem | Fix |\n|---------|---------|-----|\n| Seq Scan on large table | Missing index | Add appropriate index |\n| Nested Loop with large outer | O(n×m) join | Add index on join column, consider Hash Join |\n| Sort with high cost | Missing index for ORDER BY | Add index matching sort order |\n| Hash Join spilling to disk | work_mem too low | Increase work_mem or reduce result set |\n| Bitmap Heap Scan with many recheck | Low selectivity index | More selective index or partial index |\n| SubPlan (correlated subquery) | Executes per row | Rewrite as JOIN or lateral |\n| Rows estimate wildly wrong | Stale statistics | ANALYZE table |\n\n### Query Anti-Patterns & Fixes\n\n**1. SELECT * in production:**\n```sql\n-- Bad: fetches all columns, breaks covering indexes\nSELECT * FROM orders WHERE user_id = 123;\n-- Good: explicit columns\nSELECT id, status, total, created_at FROM orders WHERE user_id = 123;\n```\n\n**2. N+1 queries:**\n```sql\n-- Bad: 1 query for users + N queries for orders\nSELECT id FROM users WHERE active = true;  -- returns 100 rows\nSELECT * FROM orders WHERE user_id = ?;     -- called 100 times\n\n-- Good: single JOIN or IN\nSELECT u.id, o.id, o.total \nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE u.active = true;\n```\n\n**3. Functions on indexed columns:**\n```sql\n-- Bad: can't use index on created_at\nWHERE EXTRACT(YEAR FROM created_at) = 2025\n-- Good: range scan uses index\nWHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'\n\n-- Bad: can't use index on email  \nWHERE LOWER(email) = 'user@example.com'\n-- Good: expression index\nCREATE INDEX idx_users_email_lower ON users(LOWER(email));\n```\n\n**4. OR conditions killing indexes:**\n```sql\n-- Bad: often causes Seq Scan\nWHERE status = 'pending' OR status = 'processing'\n-- Good: IN uses index\nWHERE status IN ('pending', 'processing')\n```\n\n**5. Pagination with OFFSET:**\n```sql\n-- Bad: OFFSET 10000 scans and discards 10000 rows\nSELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;\n-- Good: keyset pagination\nSELECT * FROM products WHERE id > :last_seen_id ORDER BY id LIMIT 20;\n```\n\n**6. COUNT(*) on large tables:**\n```sql\n-- Bad: full table scan\nSELECT COUNT(*) FROM events;\n-- Good: approximate count (PostgreSQL)\nSELECT reltuples::bigint FROM pg_class WHERE relname = 'events';\n-- Or maintain a counter cache table\n```\n\n### Window Functions Reference\n\n```sql\n-- Running total\nSELECT id, amount, SUM(amount) OVER (ORDER BY created_at) as running_total FROM payments;\n\n-- Rank within group\nSELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank FROM employees;\n\n-- Previous/next row\nSELECT *, LAG(amount) OVER (ORDER BY created_at) as prev_amount,\n          LEAD(amount) OVER (ORDER BY created_at) as next_amount FROM payments;\n\n-- Moving average\nSELECT *, AVG(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7 FROM daily_sales;\n\n-- Percent of total\nSELECT *, amount / SUM(amount) OVER () * 100 as pct_of_total FROM line_items WHERE order_id = 1;\n```\n\n### CTE Patterns\n\n```sql\n-- Recursive: org chart traversal\nWITH RECURSIVE org AS (\n    SELECT id, name, manager_id, 1 as depth FROM employees WHERE manager_id IS NULL\n    UNION ALL\n    SELECT e.id, e.name, e.manager_id, o.depth + 1\n    FROM employees e JOIN org o ON e.manager_id = o.id\n    WHERE o.depth < 10  -- safety limit\n)\nSELECT * FROM org ORDER BY depth, name;\n\n-- Data pipeline: clean → transform → aggregate\nWITH cleaned AS (\n    SELECT *, TRIM(LOWER(email)) as clean_email FROM raw_signups WHERE email IS NOT NULL\n),\ndeduped AS (\n    SELECT DISTINCT ON (clean_email) * FROM cleaned ORDER BY clean_email, created_at DESC\n)\nSELECT DATE_TRUNC('week', created_at) as week, COUNT(*) FROM deduped GROUP BY 1 ORDER BY 1;\n```\n\n---\n\n## Phase 4 — Migrations\n\n### Migration Safety Rules\n\n1. **Never** rename columns/tables in production without a multi-step process\n2. **Never** add NOT NULL without a DEFAULT on existing tables with data\n3. **Never** drop columns that application code still references\n4. **Always** test migrations on a copy of production data first\n5. **Always** have a rollback plan (down migration)\n6. **Always** take a backup before schema changes in production\n\n### Safe Migration Patterns\n\n**Add column (safe):**\n```sql\n-- Step 1: Add nullable column\nALTER TABLE users ADD COLUMN phone VARCHAR(20);\n-- Step 2: Backfill (in batches!)\nUPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN 1 AND 10000;\n-- Step 3: Add NOT NULL after backfill\nALTER TABLE users ALTER COLUMN phone SET NOT NULL;\nALTER TABLE users ALTER COLUMN phone SET DEFAULT '';\n```\n\n**Rename column (safe multi-step):**\n```sql\n-- Step 1: Add new column\nALTER TABLE users ADD COLUMN full_name VARCHAR(200);\n-- Step 2: Dual-write in application code (write to both old + new)\n-- Step 3: Backfill\nUPDATE users SET full_name = name WHERE full_name IS NULL;\n-- Step 4: Switch application to read from new column\n-- Step 5: Drop old column (after confirming no reads)\nALTER TABLE users DROP COLUMN name;\n```\n\n**Add index without locking (PostgreSQL):**\n```sql\nCREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);\n-- Takes longer but doesn't lock the table\n```\n\n**Large table backfill (batched):**\n```sql\n-- Don't: UPDATE millions of rows in one transaction\n-- Do: batch it\nDO $$\nDECLARE\n    batch_size INT := 5000;\n    affected INT;\nBEGIN\n    LOOP\n        UPDATE users SET normalized_email = LOWER(email)\n        WHERE normalized_email IS NULL AND id IN (\n            SELECT id FROM users WHERE normalized_email IS NULL LIMIT batch_size\n        );\n        GET DIAGNOSTICS affected = ROW_COUNT;\n        RAISE NOTICE 'Updated % rows', affected;\n        EXIT WHEN affected = 0;\n        COMMIT;\n    END LOOP;\nEND $$;\n```\n\n### Migration File Template\n\n```sql\n-- Migration: YYYYMMDDHHMMSS_description.sql\n-- Author: [name]\n-- Ticket: [JIRA/Linear ID]\n-- Risk: low|medium|high\n-- Rollback: see DOWN section\n-- Estimated time: [for production data volume]\n-- Requires: [prerequisite migrations]\n\n-- ========== UP ==========\nBEGIN;\n\n-- [DDL/DML here]\n\nCOMMIT;\n\n-- ========== DOWN ==========\n-- BEGIN;\n-- [Rollback DDL/DML here]\n-- COMMIT;\n\n-- ========== VERIFY ==========\n-- [Queries to confirm migration succeeded]\n-- SELECT COUNT(*) FROM ... WHERE ...;\n```\n\n---\n\n## Phase 5 — Performance Monitoring\n\n### Key Metrics Dashboard\n\n```yaml\nhealth_metrics:\n  connections:\n    active: \"SELECT count(*) FROM pg_stat_activity WHERE state = 'active'\"\n    idle: \"SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'\"\n    max: \"SHOW max_connections\"\n    threshold: \"active > 80% of max = ALERT\"\n    \n  cache_hit_ratio:\n    query: |\n      SELECT ROUND(100.0 * sum(heap_blks_hit) / \n             NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) as ratio\n      FROM pg_statio_user_tables\n    healthy: \"> 99%\"\n    warning: \"< 95%\"\n    critical: \"< 90%\"\n    \n  index_hit_ratio:\n    query: |\n      SELECT ROUND(100.0 * sum(idx_blks_hit) / \n             NULLIF(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) as ratio\n      FROM pg_statio_user_indexes\n    healthy: \"> 99%\"\n    \n  table_bloat:\n    query: |\n      SELECT relname, n_dead_tup, n_live_tup,\n             ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) as dead_pct\n      FROM pg_stat_user_tables WHERE n_dead_tup > 10000\n      ORDER BY n_dead_tup DESC LIMIT 10\n    action: \"VACUUM ANALYZE {table} when dead_pct > 20%\"\n    \n  slow_queries:\n    query: |\n      SELECT query, calls, mean_exec_time, total_exec_time\n      FROM pg_stat_statements\n      ORDER BY mean_exec_time DESC LIMIT 20\n    action: \"Optimize top 5 by total_exec_time first\"\n    \n  replication_lag:\n    query: |\n      SELECT EXTRACT(EPOCH FROM replay_lag) as lag_seconds\n      FROM pg_stat_replication\n    warning: \"> 5 seconds\"\n    critical: \"> 30 seconds\"\n```\n\n### Table Size Analysis\n\n```sql\nSELECT \n    relname as table,\n    pg_size_pretty(pg_total_relation_size(relid)) as total_size,\n    pg_size_pretty(pg_relation_size(relid)) as table_size,\n    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as index_size,\n    n_live_tup as row_count\nFROM pg_stat_user_tables\nORDER BY pg_total_relation_size(relid) DESC\nLIMIT 20;\n```\n\n### Lock Monitoring\n\n```sql\n-- Find blocking queries\nSELECT \n    blocked.pid as blocked_pid,\n    blocked.query as blocked_query,\n    blocking.pid as blocking_pid,\n    blocking.query as blocking_query,\n    NOW() - blocked.query_start as blocked_duration\nFROM pg_stat_activity blocked\nJOIN pg_locks bl ON bl.pid = blocked.pid\nJOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.relation = bl.relation AND kl.pid != bl.pid\nJOIN pg_stat_activity blocking ON blocking.pid = kl.pid\nWHERE NOT bl.granted;\n```\n\n---\n\n## Phase 6 — Backup & Recovery\n\n### Backup Strategy Decision\n\n| Method | RPO | Speed | Use When |\n|--------|-----|-------|----------|\n| pg_dump (logical) | Point-in-time | Slow for >50GB | Small-medium DBs, cross-version migration |\n| pg_basebackup (physical) | Continuous (with WAL) | Fast | Large DBs, same-version restore |\n| WAL archiving (PITR) | Seconds | N/A (continuous) | Production with near-zero RPO |\n| Replica promotion | Seconds | Instant | HA failover |\n\n### Backup Commands\n\n```bash\n# Logical backup (compressed)\npg_dump -Fc -Z 9 -j 4 -d mydb -f backup_$(date +%Y%m%d_%H%M%S).dump\n\n# Restore\npg_restore -d mydb -j 4 --clean --if-exists backup_20260216.dump\n\n# Schema only\npg_dump -s -d mydb -f schema.sql\n\n# Single table\npg_dump -t orders -d mydb -f orders_backup.dump\n\n# Physical backup\npg_basebackup -D /backup/base -Ft -z -P -X stream\n```\n\n### Backup Verification Checklist\n\n- [ ] Backup completes without errors\n- [ ] Backup file size is within expected range (not suspiciously small)\n- [ ] Restore to a test database succeeds\n- [ ] Row counts match production (spot check 5 tables)\n- [ ] Application can connect and query the restored database\n- [ ] Run automated test suite against restored backup\n- [ ] Backup encryption verified (if required)\n- [ ] Offsite copy confirmed\n\n---\n\n## Phase 7 — Security\n\n### Access Control Checklist\n\n```sql\n-- Create application role (least privilege)\nCREATE ROLE app_user LOGIN PASSWORD 'use-vault-not-plaintext';\nGRANT CONNECT ON DATABASE mydb TO app_user;\nGRANT USAGE ON SCHEMA public TO app_user;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;\n-- NO: GRANT ALL, superuser, CREATE, DROP\n\n-- Read-only role for analytics\nCREATE ROLE analyst LOGIN PASSWORD 'use-vault';\nGRANT CONNECT ON DATABASE mydb TO analyst;\nGRANT USAGE ON SCHEMA public TO analyst;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;\n\n-- Row-Level Security (multi-tenant)\nALTER TABLE orders ENABLE ROW LEVEL SECURITY;\nCREATE POLICY tenant_isolation ON orders\n    USING (tenant_id = current_setting('app.tenant_id')::bigint);\n```\n\n### SQL Injection Prevention\n\n```\nRULE 1: NEVER concatenate user input into SQL strings\nRULE 2: Always use parameterized queries / prepared statements\nRULE 3: Validate and whitelist table/column names if dynamic\nRULE 4: Use ORMs for CRUD, raw SQL only for complex queries\nRULE 5: Audit logs for unusual query patterns (UNION, DROP, --)\n```\n\n### Data Protection\n\n```sql\n-- Encrypt sensitive columns (application-level)\n-- Store: pgp_sym_encrypt(data, key) \n-- Read: pgp_sym_decrypt(encrypted_col, key)\n\n-- Audit trail table\nCREATE TABLE audit_log (\n    id BIGSERIAL PRIMARY KEY,\n    table_name VARCHAR(100) NOT NULL,\n    record_id BIGINT NOT NULL,\n    action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE\n    old_data JSONB,\n    new_data JSONB,\n    changed_by BIGINT REFERENCES users(id),\n    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    ip_address INET\n);\n\n-- Generic audit trigger\nCREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$\nBEGIN\n    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)\n    VALUES (\n        TG_TABLE_NAME,\n        COALESCE(NEW.id, OLD.id),\n        TG_OP,\n        CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,\n        CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END,\n        current_setting('app.user_id', true)::bigint\n    );\n    RETURN COALESCE(NEW, OLD);\nEND;\n$$ LANGUAGE plpgsql;\n```\n\n---\n\n## Phase 8 — PostgreSQL Configuration Tuning\n\n### Essential Settings by Server Size\n\n| Setting | Small (4GB RAM) | Medium (16GB) | Large (64GB+) |\n|---------|-----------------|---------------|---------------|\n| shared_buffers | 1GB | 4GB | 16GB |\n| effective_cache_size | 3GB | 12GB | 48GB |\n| work_mem | 16MB | 64MB | 256MB |\n| maintenance_work_mem | 256MB | 1GB | 2GB |\n| max_connections | 100 | 200 | 300 |\n| wal_buffers | 64MB | 128MB | 256MB |\n| random_page_cost | 1.1 (SSD) | 1.1 (SSD) | 1.1 (SSD) |\n| effective_io_concurrency | 200 (SSD) | 200 (SSD) | 200 (SSD) |\n| max_parallel_workers_per_gather | 2 | 4 | 8 |\n\n### Connection Pooling (PgBouncer)\n\n```ini\n[databases]\nmydb = host=127.0.0.1 port=5432 dbname=mydb\n\n[pgbouncer]\npool_mode = transaction          # transaction pooling (best for most apps)\nmax_client_conn = 1000           # accept up to 1000 app connections\ndefault_pool_size = 25           # 25 actual DB connections per database\nreserve_pool_size = 5            # extra connections for burst\nreserve_pool_timeout = 3         # seconds before using reserve\nserver_idle_timeout = 300        # close idle server connections after 5 min\n```\n\n---\n\n## Phase 9 — Common Patterns\n\n### Soft Delete\n\n```sql\n-- Add to table\nALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;\nCREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;\n\n-- Application queries always filter\nSELECT * FROM users WHERE deleted_at IS NULL AND ...;\n\n-- Or use a view\nCREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;\n```\n\n### Optimistic Locking\n\n```sql\nUPDATE products SET \n    price = 29.99, \n    version = version + 1, \n    updated_at = NOW()\nWHERE id = 123 AND version = 5;  -- expected version\n-- If 0 rows affected → concurrent modification → retry or error\n```\n\n### Event Sourcing Table\n\n```sql\nCREATE TABLE events (\n    id BIGSERIAL PRIMARY KEY,\n    aggregate_type VARCHAR(50) NOT NULL,\n    aggregate_id UUID NOT NULL,\n    event_type VARCHAR(100) NOT NULL,\n    event_data JSONB NOT NULL,\n    metadata JSONB DEFAULT '{}',\n    version INTEGER NOT NULL,\n    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    UNIQUE (aggregate_id, version)\n);\nCREATE INDEX idx_events_aggregate ON events(aggregate_id, version);\nCREATE INDEX idx_events_type ON events(event_type, created_at);\n```\n\n### Time-Series Optimization\n\n```sql\n-- Partitioned by month\nCREATE TABLE metrics (\n    id BIGSERIAL,\n    sensor_id INTEGER NOT NULL,\n    value NUMERIC(12,4) NOT NULL,\n    recorded_at TIMESTAMPTZ NOT NULL\n) PARTITION BY RANGE (recorded_at);\n\nCREATE TABLE metrics_2026_01 PARTITION OF metrics\n    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');\nCREATE TABLE metrics_2026_02 PARTITION OF metrics\n    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');\n\n-- Auto-create future partitions via cron or pg_partman\n-- Use BRIN index for time-series\nCREATE INDEX idx_metrics_time ON metrics USING brin(recorded_at);\n```\n\n### Full-Text Search (PostgreSQL)\n\n```sql\n-- Add search column\nALTER TABLE articles ADD COLUMN search_vector tsvector;\nCREATE INDEX idx_articles_search ON articles USING gin(search_vector);\n\n-- Populate\nUPDATE articles SET search_vector = \n    setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||\n    setweight(to_tsvector('english', COALESCE(body, '')), 'B');\n\n-- Search with ranking\nSELECT id, title, ts_rank(search_vector, query) as rank\nFROM articles, plainto_tsquery('english', 'database optimization') query\nWHERE search_vector @@ query\nORDER BY rank DESC LIMIT 20;\n```\n\n### JSONB Patterns\n\n```sql\n-- Store flexible attributes\nCREATE TABLE products (\n    id BIGSERIAL PRIMARY KEY,\n    name VARCHAR(200) NOT NULL,\n    attributes JSONB NOT NULL DEFAULT '{}',\n    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n);\n\n-- Index specific JSON paths\nCREATE INDEX idx_products_color ON products((attributes->>'color'));\n-- Or GIN for any key lookups\nCREATE INDEX idx_products_attrs ON products USING gin(attributes);\n\n-- Query patterns\nSELECT * FROM products WHERE attributes->>'color' = 'red';\nSELECT * FROM products WHERE attributes @> '{\"size\": \"large\"}';\nSELECT * FROM products WHERE attributes ? 'warranty';\n```\n\n---\n\n## Phase 10 — Operational Runbooks\n\n### Emergency: Database Overloaded\n\n```sql\n-- 1. Find and kill long-running queries\nSELECT pid, NOW() - query_start as duration, query \nFROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 minutes'\nORDER BY duration DESC;\n\n-- Kill a specific query\nSELECT pg_cancel_backend(pid);    -- graceful\nSELECT pg_terminate_backend(pid); -- force\n\n-- 2. Check for lock contention (see Phase 5)\n\n-- 3. Reduce max connections temporarily\n-- In pgbouncer: pause database, reduce pool, resume\n\n-- 4. Check if VACUUM is needed\nSELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables \nWHERE n_dead_tup > 100000 ORDER BY n_dead_tup DESC;\n```\n\n### Emergency: Disk Full\n\n```bash\n# 1. Check what's consuming space\ndu -sh /var/lib/postgresql/*/main/ 2>/dev/null || du -sh /var/lib/mysql/\n\n# 2. Clean up WAL files (PostgreSQL) — CAREFUL\n# Check replication slot status first\nSELECT slot_name, active FROM pg_replication_slots;\n# Drop inactive slots consuming WAL\nSELECT pg_drop_replication_slot('unused_slot');\n\n# 3. VACUUM FULL largest tables (locks table!)\nVACUUM FULL large_table;\n\n# 4. Remove old backups / logs\nfind /backups -name \"*.dump\" -mtime +7 -delete\n```\n\n### Weekly Maintenance Checklist\n\n- [ ] Review slow query log (top 10 by total time)\n- [ ] Check index usage stats — drop unused, add missing\n- [ ] Verify backup success and test restore\n- [ ] Check table bloat — schedule VACUUM where needed\n- [ ] Review connection count trends\n- [ ] Check disk space trajectory\n- [ ] Review replication lag\n- [ ] Update table statistics: `ANALYZE;`\n\n---\n\n## Phase 11 — Database Comparison Quick Reference\n\n| Feature | PostgreSQL | MySQL (InnoDB) | SQLite |\n|---------|-----------|----------------|--------|\n| Best for | Complex queries, extensions | Web apps, read-heavy | Embedded, dev, small apps |\n| Max size | Unlimited (practical) | Unlimited (practical) | 281 TB (practical ~1TB) |\n| JSON support | JSONB (indexable, fast) | JSON (limited indexing) | JSON1 extension |\n| Full-text search | Built-in (tsvector) | Built-in (FULLTEXT) | FTS5 extension |\n| Window functions | Full support | Full support (8.0+) | Full support (3.25+) |\n| CTEs | Recursive + materialized | Recursive (8.0+) | Recursive (3.8+) |\n| Partitioning | Declarative + list/range/hash | Range/list/hash/key | None |\n| Row-level security | Yes | No (use views) | No |\n| Replication | Streaming + logical | Binary log | None (use Litestream) |\n| Connection model | Process per connection | Thread per connection | In-process |\n\n---\n\n## Quality Scoring Rubric (0-100)\n\n| Dimension | Weight | 0 (Poor) | 5 (Good) | 10 (Excellent) |\n|-----------|--------|----------|----------|-----------------|\n| Schema Design | 20% | No normalization, no constraints | 3NF, FKs, proper types | Optimal normal form, all constraints, audit fields |\n| Indexing | 15% | No indexes beyond PK | Indexes on FKs and common queries | Covering indexes, partials, no unused indexes |\n| Query Quality | 20% | SELECT *, N+1, no EXPLAIN | Specific columns, JOINs, basic optimization | Keyset pagination, window functions, optimized plans |\n| Migration Safety | 10% | Raw DDL, no rollback | Versioned files, up/down | Zero-downtime, batched backfills, concurrent indexes |\n| Security | 15% | Superuser access, no audit | Least privilege, parameterized queries | RLS, encryption, audit triggers, regular access review |\n| Monitoring | 10% | No monitoring | Basic alerts on connections/disk | Full dashboard, slow query analysis, proactive tuning |\n| Backup/Recovery | 10% | No backups | Daily dumps | PITR, tested restores, offsite copies |\n\n**Score interpretation:** <40 = Critical risk | 40-60 = Needs work | 60-80 = Solid | 80-90 = Professional | 90+ = Expert\n\n---\n\n## Natural Language Commands\n\n- \"Design a schema for [domain]\" → Phase 1 full design process\n- \"Optimize this query: [SQL]\" → EXPLAIN analysis + rewrite\n- \"Add an index for [query pattern]\" → Index type selection + creation\n- \"Write a migration to [change]\" → Safe migration with rollback\n- \"Audit this database\" → Full scoring across all dimensions\n- \"Set up monitoring for [database]\" → Phase 5 dashboard queries\n- \"Review this schema\" → Naming, types, constraints, relationships check\n- \"Help me with [PostgreSQL/MySQL/SQLite] [topic]\" → Platform-specific guidance\n- \"Troubleshoot slow queries\" → pg_stat_statements analysis + top fixes\n- \"Plan a backup strategy\" → Phase 6 decision framework\n- \"Make this table multi-tenant\" → RLS + tenant_id pattern\n- \"Convert this to use partitioning\" → Phase 9 time-series pattern\n","readmeExcerpt":"Database Engineering Mastery Complete database design, optimization, migration, and operations system. From schema design to production monitoring — covers PostgreSQL, MySQL, SQLite, and general SQL patterns. Phase 1 — Schema Design Design Brief Before writing any DDL, fill this out: Normalization Decision Framework | Form | Rule | When to Denormalize | |------|------|---------------------| | 1NF | No repeating group","codeSnippets":[],"executableExamples":[{"language":"yaml","snippet":"project: \"\"\ndomain: \"\"\nprimary_use_case: \"OLTP | OLAP | mixed\"\nexpected_scale:\n  rows_year_1: \"\"\n  rows_year_3: \"\"\n  concurrent_users: \"\"\n  read_write_ratio: \"80:20 | 50:50 | 20:80\"\ncompliance: [] # GDPR, HIPAA, PCI-DSS, SOX\nmulti_tenancy: \"none | schema-per-tenant | row-level | database-per-tenant\""},{"language":"text","snippet":"Tables:      snake_case, plural (users, order_items, payment_methods)\nColumns:     snake_case, singular (first_name, created_at, is_active)\nPKs:         id (bigint/uuid) or {table_singular}_id\nFKs:         {referenced_table_singular}_id\nIndexes:     idx_{table}_{columns}\nConstraints: chk_{table}_{rule}, uq_{table}_{columns}, fk_{table}_{ref}\nEnums:       Use VARCHAR + CHECK, not DB enums (easier to migrate)\nBooleans:    is_, has_, can_ prefix (is_active, has_subscription)\nTimestamps:  _at suffix (created_at, updated_at, deleted_at)"},{"language":"text","snippet":"Text < 255 chars, fixed set?     → VARCHAR(N) + CHECK\nText < 255 chars, variable?      → VARCHAR(255)\nText > 255 chars?                → TEXT\nWhole numbers < 2B?              → INTEGER\nWhole numbers > 2B?              → BIGINT\nMoney/financial?                 → NUMERIC(precision, scale) — NEVER float\nTrue/false?                      → BOOLEAN\nDate only?                       → DATE\nDate + time?                     → TIMESTAMPTZ (always with timezone)\nUnique identifier?               → UUID (distributed) or BIGSERIAL (single DB)\nJSON/flexible schema?            → JSONB (Postgres) or JSON (MySQL)\nBinary/file?                     → Store in object storage, reference by URL\nIP address?                      → INET (Postgres) or VARCHAR(45)\nGeospatial?                      → PostGIS geometry/geography types"},{"language":"sql","snippet":"CREATE TABLE {table_name} (\n    id          BIGSERIAL PRIMARY KEY,\n    -- domain columns here --\n    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    created_by  BIGINT REFERENCES users(id),\n    version     INTEGER NOT NULL DEFAULT 1,  -- optimistic locking\n    \n    -- soft delete (optional)\n    deleted_at  TIMESTAMPTZ,\n    \n    -- multi-tenant (optional)  \n    tenant_id   BIGINT NOT NULL REFERENCES tenants(id)\n);\n\n-- Updated_at trigger (PostgreSQL)\nCREATE OR REPLACE FUNCTION update_modified_column()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    NEW.version = OLD.version + 1;\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER trg_{table_name}_updated\n    BEFORE UPDATE ON {table_name}\n    FOR EACH ROW\n    EXECUTE FUNCTION update_modified_column();"},{"language":"sql","snippet":"-- Parent\nCREATE TABLE departments (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);\n-- Child  \nCREATE TABLE employees (\n    id BIGSERIAL PRIMARY KEY,\n    department_id BIGINT NOT NULL REFERENCES departments(id) ON DELETE RESTRICT,\n    -- ON DELETE options: RESTRICT (safe default), CASCADE (children die), SET NULL\n);\nCREATE INDEX idx_employees_department_id ON employees(department_id);"},{"language":"sql","snippet":"CREATE TABLE user_roles (\n    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,\n    granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    granted_by BIGINT REFERENCES users(id),\n    PRIMARY KEY (user_id, role_id)\n);"}],"parameters":{},"dependencies":[],"permissions":[],"extractedFiles":[],"languages":["typescript"],"docsSourceLabel":"CLAWHUB","editorialOverview":"Database Engineering Mastery Database Engineering Mastery Complete database design, optimization, migration, and operations system. From schema design to production monitoring — covers PostgreSQL, MySQL, SQLite, and general SQL patterns. Phase 1 — Schema Design Design Brief Before writing any DDL, fill this out: Normalization Decision Framework | Form | Rule | When to Denormalize | |------|------|---------------------| | 1NF | No repeating group","editorialQuality":{"score":100,"threshold":65,"status":"ready","wordCount":366,"uniquenessScore":67,"reasons":[]}},"media":{"evidence":{"source":"no-media","verified":false,"confidence":"low","updatedAt":"2026-04-15T00:45:39.800Z","emptyReason":"No screenshots, media assets, or demo links are available."},"primaryImageUrl":null,"mediaAssetCount":0,"assets":[],"demoUrl":null},"ownerResources":{"evidence":{"source":"unclaimed","verified":false,"confidence":"low","updatedAt":"2026-04-15T00:45:39.800Z","emptyReason":"This page has not been claimed by the agent owner."},"hasCustomPage":false,"customPageUpdatedAt":null,"customLinks":[],"structuredLinks":{"docsUrl":null,"demoUrl":null,"supportUrl":null,"pricingUrl":null,"statusUrl":null},"customPage":null},"relatedAgents":{"evidence":{"source":"protocol-neighbors","verified":false,"confidence":"medium","updatedAt":"2026-10-09T11:45:16.628Z","emptyReason":null},"items":[{"id":"b917f68a-ebff-438e-84f8-3f4b2494c0bc","entityType":"agent","canonicalPath":"/agent/activepieces-activepieces","slug":"activepieces-activepieces","name":"activepieces","description":"AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents","url":"https://github.com/activepieces/activepieces","homepage":"https://www.activepieces.com","source":"GITHUB_REPOS","protocols":["OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-04-15T02:22:12.426Z","createdAt":"2026-02-25T03:38:12.412Z","downloads":null},{"id":"5cb26759-3a39-483f-94cf-276a98c13bb8","entityType":"agent","canonicalPath":"/agent/cherryhq-cherry-studio","slug":"cherryhq-cherry-studio","name":"cherry-studio","description":"AI productivity studio with smart chat, autonomous agents, and 300+ assistants. Unified access to frontier LLMs","url":"https://github.com/CherryHQ/cherry-studio","homepage":"https://cherry-ai.com","source":"GITHUB_REPOS","protocols":["MCP","OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-04-11T14:38:40.986Z","createdAt":"2026-02-25T03:38:19.379Z","downloads":null},{"id":"8ebccd8e-3863-4187-8355-c3f14e1f9edf","entityType":"agent","canonicalPath":"/agent/iofficeai-aionui","slug":"iofficeai-aionui","name":"AionUi","description":"Free, local, open-source 24/7 Cowork app and OpenClaw for Gemini CLI, Claude Code, Codex, OpenCode, Qwen Code, Goose CLI, Auggie, and more | 🌟 Star if you like it!","url":"https://github.com/iOfficeAI/AionUi","homepage":"https://www.aionui.com","source":"GITHUB_REPOS","protocols":["MCP","OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-04-10T18:48:31.762Z","createdAt":"2026-02-25T03:38:16.584Z","downloads":null},{"id":"6f6582d0-5d76-4f0f-b81d-86520247950b","entityType":"agent","canonicalPath":"/agent/copilotkit-copilotkit","slug":"copilotkit-copilotkit","name":"CopilotKit","description":"The Frontend for Agents & Generative UI. React + Angular","url":"https://github.com/CopilotKit/CopilotKit","homepage":"https://docs.copilotkit.ai","source":"GITHUB_REPOS","protocols":["OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-03-25T09:50:57.846Z","createdAt":"2026-02-25T03:39:14.617Z","downloads":null}],"links":{"hub":"/agent","source":"/agent/source/clawhub","protocols":[{"label":"OpenClaw","href":"/agent/protocol/openclew"}]}}}