openclaw-voc-skill/services/qiwei-relay/schema.ts

/**
 * Relay 数据库 Schema
 *
 * 表：
 * - Tenant：租户信息、API 凭证、RSA 公钥
 * - TenantDevice：租户下的设备 guid 映射、relaySecret
 * - WebhookRelayEvent：待投递事件队列
 * - UsageMetric：用量埋点
 * - AuditLog：租户自助注册/重置等审计日志
 */

import { createTable, initDb, getDb } from './lib/db.js';

export function initRelayDatabase(dbPath: string): void {
  initDb(dbPath);

  // 租户表
  createTable('Tenant', [
    { name: 'apiKey', type: 'TEXT', required: true },
    { name: 'apiSecretHash', type: 'TEXT', required: true },
    { name: 'publicKey', type: 'TEXT', required: true },
    { name: 'fmodeUserId', type: 'TEXT' },
    { name: 'name', type: 'TEXT' },
    { name: 'description', type: 'TEXT' },
    { name: 'plan', type: 'TEXT', defaultValue: 'free' },
    { name: 'status', type: 'TEXT', defaultValue: 'active' },
    { name: 'maxDevices', type: 'INTEGER', defaultValue: 10 },
    { name: 'dailyEventLimit', type: 'INTEGER', defaultValue: 100000 },
    { name: 'metadata', type: 'TEXT' },
  ]);

  // 租户设备表
  createTable('TenantDevice', [
    { name: 'tenantId', type: 'TEXT', required: true },
    { name: 'guid', type: 'TEXT', required: true },
    { name: 'relaySecret', type: 'TEXT', required: true },
    { name: 'deviceName', type: 'TEXT' },
    { name: 'lastOnlineAt', type: 'TEXT' },
    { name: 'status', type: 'TEXT', defaultValue: 'active' },
  ]);

  // Webhook 事件队列
  createTable('WebhookRelayEvent', [
    { name: 'tenantId', type: 'TEXT', required: true },
    { name: 'deviceGuid', type: 'TEXT', required: true },
    { name: 'eventId', type: 'TEXT', required: true },
    { name: 'status', type: 'TEXT', defaultValue: 'pending' }, // pending / delivered / failed / dropped
    { name: 'encryptedPayload', type: 'TEXT', required: true },
    { name: 'payloadHash', type: 'TEXT' },
    { name: 'retryCount', type: 'INTEGER', defaultValue: 0 },
    { name: 'deliveredAt', type: 'TEXT' },
    { name: 'expiresAt', type: 'TEXT' },
    { name: 'error', type: 'TEXT' },
  ]);

  // 用量埋点
  createTable('UsageMetric', [
    { name: 'tenantId', type: 'TEXT', required: true },
    { name: 'deviceGuid', type: 'TEXT' },
    { name: 'metricName', type: 'TEXT', required: true }, // webhook_event / message / group_created / device_online
    { name: 'metricValue', type: 'INTEGER', defaultValue: 1 },
    { name: 'metricDate', type: 'TEXT', required: true }, // YYYY-MM-DD
    { name: 'metadata', type: 'TEXT' },
  ]);

  // 审计日志
  createTable('AuditLog', [
    { name: 'action', type: 'TEXT', required: true }, // tenant.register / tenant.reset / tenant.delete
    { name: 'tenantId', type: 'TEXT' },
    { name: 'fmodeUserId', type: 'TEXT' },
    { name: 'ip', type: 'TEXT' },
    { name: 'details', type: 'TEXT' },
  ]);

  // 索引
  const db = getDb();
  db.exec(`CREATE INDEX IF NOT EXISTS idx_tenant_apiKey ON Tenant(apiKey)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_device_tenant_guid ON TenantDevice(tenantId, guid)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_device_guid ON TenantDevice(guid)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_event_tenant_status ON WebhookRelayEvent(tenantId, status)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_event_device_status ON WebhookRelayEvent(deviceGuid, status)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_event_expires ON WebhookRelayEvent(expiresAt)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_metric_tenant_date ON UsageMetric(tenantId, metricName, metricDate)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_auditlog_tenant ON AuditLog(tenantId)`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_auditlog_action ON AuditLog(action, createdAt)`);

  migrateRelayDatabase();

  console.log('[Relay Schema] 数据库表初始化完成');
}

/** 对已有数据库追加新列/索引（幂等） */
export function migrateRelayDatabase(): void {
  const db = getDb();

  const tableInfo = db.prepare(`PRAGMA table_info("Tenant")`).all() as Array<{ name: string }>;
  const hasColumn = (name: string) => tableInfo.some((col) => col.name === name);

  if (!hasColumn('fmodeUserId')) {
    db.exec(`ALTER TABLE "Tenant" ADD COLUMN "fmodeUserId" TEXT`);
  }
  if (!hasColumn('description')) {
    db.exec(`ALTER TABLE "Tenant" ADD COLUMN "description" TEXT`);
  }
  if (!hasColumn('maxDevices')) {
    db.exec(`ALTER TABLE "Tenant" ADD COLUMN "maxDevices" INTEGER DEFAULT 10`);
  }
  if (!hasColumn('dailyEventLimit')) {
    db.exec(`ALTER TABLE "Tenant" ADD COLUMN "dailyEventLimit" INTEGER DEFAULT 100000`);
  }

  db.exec(`CREATE INDEX IF NOT EXISTS idx_tenant_fmodeUserId ON Tenant(fmodeUserId)`);
}