vibe-coding-cn/tg-live-game-hono/d1-database.canvas

{
  "nodes": [
    {
      "id": "title",
      "type": "text",
      "text": "# D1 数据库设计\n\n**数据库名**: tg_live_game\n**引擎**: Cloudflare D1 (SQLite)\n**状态**: 📋 设计中\n\n设计原则:\n- Cloudflare Stream 为主数据源\n- D1 存储业务扩展元数据\n- 支持离线查询和统计",
      "x": -100,
      "y": -600,
      "width": 380,
      "height": 180,
      "color": "6"
    },
    {
      "id": "table_cameras",
      "type": "text",
      "text": "**cameras** 摄像头管理\n\n```sql\nid          TEXT PRIMARY KEY\nname        TEXT NOT NULL\ntype        TEXT -- 'mac'|'ip'|'rtsp'\nprotocol    TEXT -- 'rtmps'|'srt'|'whip'\nrtsp_url    TEXT\nlocation    TEXT\nstatus      TEXT -- 'online'|'offline'\nlive_input_id TEXT -- CF Stream ID\ncreated_at  INTEGER\nupdated_at  INTEGER\n```\n\n索引: status, type",
      "x": -500,
      "y": -340,
      "width": 300,
      "height": 280,
      "color": "4"
    },
    {
      "id": "table_live_sessions",
      "type": "text",
      "text": "**live_sessions** 直播会话\n\n```sql\nid          TEXT PRIMARY KEY\ncamera_id   TEXT REFERENCES cameras\nlive_input_id TEXT -- CF Stream ID\nstarted_at  INTEGER NOT NULL\nended_at    INTEGER\nduration    INTEGER -- 秒\nstatus      TEXT -- 'live'|'ended'|'error'\nviewer_count INTEGER DEFAULT 0\nrecording_id TEXT -- 录像视频ID\ncreated_at  INTEGER\n```\n\n索引: camera_id, status, started_at",
      "x": -130,
      "y": -340,
      "width": 320,
      "height": 280,
      "color": "2"
    },
    {
      "id": "table_videos",
      "type": "text",
      "text": "**videos** 视频元数据\n\n```sql\nid          TEXT PRIMARY KEY\ncf_uid      TEXT UNIQUE -- CF Stream UID\ncamera_id   TEXT REFERENCES cameras\nsession_id  TEXT REFERENCES live_sessions\ntitle       TEXT\ndescription TEXT\ntags        TEXT -- JSON array\nthumbnail   TEXT\nduration    INTEGER\nsize        INTEGER\nstatus      TEXT -- 'ready'|'processing'\nis_public   INTEGER DEFAULT 0\nview_count  INTEGER DEFAULT 0\ncreated_at  INTEGER\nupdated_at  INTEGER\n```\n\n索引: cf_uid, camera_id, status",
      "x": 260,
      "y": -340,
      "width": 320,
      "height": 340,
      "color": "3"
    },
    {
      "id": "table_users",
      "type": "text",
      "text": "**users** 用户管理\n\n```sql\nid          TEXT PRIMARY KEY\nusername    TEXT UNIQUE\nemail       TEXT UNIQUE\npassword_hash TEXT\nrole        TEXT -- 'admin'|'operator'|'viewer'\nstatus      TEXT -- 'active'|'disabled'\nlast_login  INTEGER\ncreated_at  INTEGER\nupdated_at  INTEGER\n```\n\n索引: username, email, role",
      "x": -500,
      "y": 0,
      "width": 300,
      "height": 240,
      "color": "5"
    },
    {
      "id": "table_permissions",
      "type": "text",
      "text": "**user_permissions** 权限\n\n```sql\nid          TEXT PRIMARY KEY\nuser_id     TEXT REFERENCES users\ncamera_id   TEXT REFERENCES cameras\npermission  TEXT -- 'view'|'control'|'manage'\ngranted_at  INTEGER\ngranted_by  TEXT REFERENCES users\n```\n\n索引: user_id, camera_id",
      "x": -130,
      "y": 0,
      "width": 320,
      "height": 200,
      "color": "5"
    },
    {
      "id": "table_view_stats",
      "type": "text",
      "text": "**view_stats** 观看统计\n\n```sql\nid          TEXT PRIMARY KEY\nvideo_id    TEXT REFERENCES videos\nsession_id  TEXT REFERENCES live_sessions\nuser_id     TEXT -- 可为空(匿名)\nip_address  TEXT\nuser_agent  TEXT\nwatch_duration INTEGER -- 秒\nstarted_at  INTEGER\nended_at    INTEGER\ncountry     TEXT\ncity        TEXT\n```\n\n索引: video_id, session_id, started_at",
      "x": 260,
      "y": 60,
      "width": 320,
      "height": 260,
      "color": "1"
    },
    {
      "id": "table_audit_logs",
      "type": "text",
      "text": "**audit_logs** 操作日志\n\n```sql\nid          TEXT PRIMARY KEY\nuser_id     TEXT REFERENCES users\naction      TEXT -- 'create'|'update'|'delete'\nresource    TEXT -- 'camera'|'video'|'user'\nresource_id TEXT\ndetails     TEXT -- JSON\nip_address  TEXT\ncreated_at  INTEGER\n```\n\n索引: user_id, action, created_at",
      "x": -500,
      "y": 300,
      "width": 300,
      "height": 220,
      "color": "1"
    },
    {
      "id": "er_diagram",
      "type": "text",
      "text": "## ER 关系图\n\n```\nusers ──1:N──> user_permissions\n  │                  │\n  │                  v\n  │            cameras ──1:N──> live_sessions\n  │               │                   │\n  │               │                   v\n  └──1:N──> audit_logs         videos <──1:N── view_stats\n```\n\n核心关系:\n- 摄像头 → 多个直播会话\n- 直播会话 → 录像视频\n- 用户 → 摄像头权限",
      "x": -130,
      "y": 260,
      "width": 320,
      "height": 260,
      "color": "6"
    },
    {
      "id": "wrangler_config",
      "type": "text",
      "text": "## wrangler.toml 配置\n\n```toml\n[[d1_databases]]\nbinding = \"DB\"\ndatabase_name = \"tg_live_game\"\ndatabase_id = \"<your-database-id>\"\n```\n\n## 创建命令\n\n```bash\nwrangler d1 create tg_live_game\nwrangler d1 execute tg_live_game \\\n  --file=./schema.sql\n```",
      "x": 260,
      "y": 380,
      "width": 320,
      "height": 240,
      "color": "4"
    }
  ],
  "edges": [
    {
      "id": "edge_camera_session",
      "fromNode": "table_cameras",
      "fromSide": "right",
      "toNode": "table_live_sessions",
      "toSide": "left",
      "label": "1:N"
    },
    {
      "id": "edge_session_video",
      "fromNode": "table_live_sessions",
      "fromSide": "right",
      "toNode": "table_videos",
      "toSide": "left",
      "label": "1:1"
    },
    {
      "id": "edge_user_perm",
      "fromNode": "table_users",
      "fromSide": "right",
      "toNode": "table_permissions",
      "toSide": "left",
      "label": "1:N"
    },
    {
      "id": "edge_perm_camera",
      "fromNode": "table_permissions",
      "fromSide": "top",
      "toNode": "table_cameras",
      "toSide": "bottom",
      "label": "N:1"
    },
    {
      "id": "edge_video_stats",
      "fromNode": "table_videos",
      "fromSide": "bottom",
      "toNode": "table_view_stats",
      "toSide": "top",
      "label": "1:N"
    },
    {
      "id": "edge_user_audit",
      "fromNode": "table_users",
      "fromSide": "bottom",
      "toNode": "table_audit_logs",
      "toSide": "top",
      "label": "1:N"
    }
  ]
}