wufengping/koc-loop
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

Compare commits

base: wufengping:codex/sites-release-controls
Branches Tags
wufengping:main wufengping:feat/account-tags wufengping:codex/self-hosted-mysql wufengping:codex/sites-release-controls
...
compare: wufengping:ad3dbdcc8648c46bd58a9391804fd8ce5967cdfb
Branches Tags
wufengping:main wufengping:feat/account-tags wufengping:codex/self-hosted-mysql wufengping:codex/sites-release-controls

6 Commits
codex/site ... ad3dbdcc86

Author SHA1 Message Date
巫凤萍
ad3dbdcc86 feat: collapse mobile note after first backfill 2026-08-12 20:44:57 +08:00
巫凤萍
ee6caaf9e5 fix: type resource import enrichment fallback 2026-08-12 19:59:38 +08:00
巫凤萍
e05041e037 fix: preserve KOC portal path in delegation links 2026-08-12 19:53:19 +08:00
巫凤萍
51934b0638 feat: simplify KOC resource imports 2026-08-12 17:51:57 +08:00
巫凤萍
7ef150e08b feat: add distribution release and recovery controls 2026-08-12 11:12:23 +08:00
巫凤萍
ddad4b7659 feat: ship self-hosted KOC LOOP workflows 2026-08-11 23:07:26 +08:00
94 changed files with 7019 additions and 8967 deletions
Expand all files Collapse all files

12
.dockerignore Normal file
View File

@@ -0,0 +1,12 @@
.git
.next
dist
node_modules
koc-portal/.next
koc-portal/out
koc-portal/dist
koc-portal/node_modules
.data
.env*
!.env.self-hosted.example
npm-debug.log*

30
.env.self-hosted.example Normal file
View File

@@ -0,0 +1,30 @@
# 复制为 .env.self-hosted 后填写。不要把真实密钥提交到 Git。
MYSQL_ROOT_PASSWORD=replace-with-strong-root-password
MYSQL_DATABASE=koc_loop
MYSQL_USER=koc
MYSQL_PASSWORD=replace-with-strong-app-password
# 对外地址。部署域名确定后替换。
APP_ORIGIN=https://koc.example.com
KOC_PORTAL_URL=https://koc.example.com/koc/
# 后台首次启动会创建唯一超级管理员；账号创建后建议移除明文密码并重启。
SUPER_ADMIN_USERNAME=admin
SUPER_ADMIN_PASSWORD=replace-with-strong-admin-password
ADMIN_INTERNAL_TOKEN=replace-with-random-internal-token
# KOC LOOP 自身 MCP 的访问密钥。
KOC_MCP_API_KEY=replace-with-random-mcp-key
# 飞书内容表读取。
FEISHU_APP_ID=
FEISHU_APP_SECRET=
# 小红书公开数据采集 MCP。
AI_TOOL_CENTER_MCP_URL=
AI_TOOL_CENTER_MCP_KEY=
# 每天北京时间 09:00 自动执行采集计划。
ENABLE_SCHEDULER=true
SEED_DEMO_DATA=false
HTTP_PORT=80

1
.gitignore vendored
View File

@@ -31,6 +31,7 @@ yarn-error.log*
# env files (can opt-in for committing if needed)
.env*
!.env.self-hosted.example
.dev.vars
# vercel

31
Dockerfile Normal file
View File

@@ -0,0 +1,31 @@
FROM node:22.18.0-bookworm-slim AS dependencies
WORKDIR /app
COPY package.json package-lock.json ./
RUN npm ci
FROM node:22.18.0-bookworm-slim AS builder
WORKDIR /app
ENV NEXT_TELEMETRY_DISABLED=1
COPY --from=dependencies /app/node_modules ./node_modules
COPY . .
RUN npm run build
FROM node:22.18.0-bookworm-slim AS runner
WORKDIR /app
ENV NODE_ENV=production \
NEXT_TELEMETRY_DISABLED=1 \
PORT=3000 \
HOSTNAME=0.0.0.0 \
UPLOAD_DIR=/data/koc/uploads
RUN groupadd --system --gid 1001 nodejs \
&& useradd --system --uid 1001 --gid nodejs nextjs \
&& mkdir -p /data/koc/uploads \
&& chown -R nextjs:nodejs /data/koc
COPY --from=builder --chown=nextjs:nodejs /app/.next/standalone ./
COPY --from=builder --chown=nextjs:nodejs /app/.next/static ./.next/static
COPY --from=builder --chown=nextjs:nodejs /app/public ./public
COPY --from=builder --chown=nextjs:nodejs /app/mysql ./mysql
COPY --from=builder --chown=nextjs:nodejs /app/scripts ./scripts
USER nextjs
EXPOSE 3000
CMD ["sh", "-c", "node scripts/migrate-mysql.mjs && node server.js"]

39
README.md
View File

@@ -1,20 +1,22 @@
# KOC LOOP
KOC 内容分发与数据回收闭环，运行于 vinext、Cloudflare D1 和 R2。
KOC 内容分发与数据回收闭环。当前私有化分支运行于标准 Next.js Node.js、MySQL 8、Nginx 和本地持久化文件存储。
## Prerequisites
- Node.js `>=22.13.0`
- MySQL `>=8.0`（本地完整运行）
## Quick Start
```bash
npm install
npm run db:migrate
npm run dev
npm run build
```
复制 `.dev.vars.example` 为 `.dev.vars` 并配置运行时变量。飞书动态导入需要：
复制 `.env.self-hosted.example` 为 `.env.self-hosted`，设置 `DATABASE_URL` 或 `MYSQL_*` 连接信息。飞书动态导入需要：
- `FEISHU_APP_ID`
- `FEISHU_APP_SECRET`
@@ -31,16 +33,7 @@ npm run build
系统首次登录时创建唯一的超级管理员。后续管理员和普通用户均由“用户管理”页面创建，普通用户不能访问 KOC 资源库。
This starter does not use `wrangler.jsonc`.
## Included Shape
- edit site code under `app/`
- `.openai/hosting.json` declares optional Sites D1 and R2 bindings
- `vite.config.ts` simulates declared bindings for local development
- `db/schema.ts` starts intentionally empty
- `examples/d1/` contains an optional D1 example surface
- `drizzle.config.ts` supports local migration generation when needed
完整私有化部署请看 [KOC LOOP 私有化部署指南](docs/KOC%20LOOP%20私有化部署指南.md)。Docker Compose 会启动 Nginx、KOC 服务和 MySQL；外部领取页与后台使用同一域名下的 `/koc/` 路径。
## 后台账号与角色
@@ -50,6 +43,16 @@ This starter does not use `wrangler.jsonc`.
后台不提供注册、找回密码和普通用户个人改密。密码重置统一由管理员在后台完成。
## KOC 资源导入
超级管理员和管理员可在“KOC资源”页面下载标准 Excel 模板，批量导入已有资源。模板只需填写小红书账号主页，合作来源可选填；上传后系统自动解析账号名称、小红书号、IP属地和粉丝数。
- 单次最多导入 100 个账号，支持 `.xlsx` 和 `.csv`，文件不超过 5MB。
- 当前自动解析支持小红书账号主页；上传后先展示新增、更新和异常数据，存在异常时不会写入数据库。
- 按“平台 + 账号主页”去重；解析出相同小红书号时也会更新已有账号。
- 重复账号更新公开资料和合作来源，不产生两份资源。
- 导入的合作来源会进入现有资源搜索、筛选和导出结果。
## Agent MCP
生产地址：
@@ -110,11 +113,15 @@ Authorization: Bearer <KOC_MCP_API_KEY>
## Useful Commands
- `npm run dev`: start local development
- `npm run build`: verify the vinext build output
- `npm test`: build the starter and verify its rendered loading skeleton
- `npm run build`: 验证标准 Next.js Node.js 生产构建
- `npm test`: 构建并执行业务与私有化架构测试
- `npm run db:generate`: generate Drizzle migrations after schema changes
- `npm run db:migrate`: 应用 MySQL 增量迁移
- `npm run db:check`: 检查 MySQL 连接
- `npm run db:import-json -- <file>`: 导入 D1 JSON 数据
- `npm run storage:import -- <dir>`: 导入 R2 对象目录
## Learn More
- [vinext Documentation](https://github.com/cloudflare/vinext)
- [Drizzle D1 Guide](https://orm.drizzle.team/docs/get-started/d1-new)
- [Next.js Self-Hosting](https://nextjs.org/docs/app/guides/self-hosting)
- [Drizzle MySQL Guide](https://orm.drizzle.team/docs/get-started-mysql)

1050
app/admin-app.tsx
View File

File diff suppressed because it is too large Load Diff

103
app/api/action/route.ts
View File

@@ -1,5 +1,7 @@
import { env } from "cloudflare:workers";
import { getRequestExecutionContext } from "vinext/shims/request-context";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import { runInBackground } from "../../../lib/background";
const env = getRuntimeEnv();
import { backfillAccountProfiles } from "../../../lib/account-enrichment-service";
import {
ensureSchema,
@@ -24,9 +26,15 @@ import {
readFeishuSource,
type FeishuBindings,
} from "../../../lib/feishu-client";
import { createDistributionTask } from "../../../lib/task-service";
export const runtime = "edge";
import {
createDistributionTask,
createScreenshotTask,
} from "../../../lib/task-service";
import {
DistributionReleaseError,
releaseUnfinishedDistribution,
} from "../../../lib/distribution-release-service";
import { isManagerRequest } from "../../../lib/user-auth";
type ActionBody = {
action?: string;
@@ -79,6 +87,16 @@ export async function POST(request: Request) {
},
env as unknown as FeishuBindings,
);
} else if (body.action === "create_screenshot_task") {
await createScreenshotTask({
name: String(body.name ?? "").trim(),
brand: String(body.brand ?? "").trim(),
dueAt: String(body.dueAt ?? "").trim(),
keyword: String(body.keyword ?? "").trim(),
instructions: String(body.instructions ?? "").trim(),
quantity: numberValue(body.quantity),
exampleImageKey: String(body.exampleImageKey ?? "").trim(),
});
} else if (body.action === "claim") {
const partnerId = String(body.partnerId ?? "");
const taskId = String(body.taskId ?? "");
@@ -88,9 +106,9 @@ export async function POST(request: Request) {
`SELECT id FROM contents
WHERE task_id = ? AND status = 'available'
ORDER BY created_at, id
LIMIT ?`,
LIMIT ${quantity}`,
)
.bind(taskId, quantity)
.bind(taskId)
.all<{ id: string }>();
if (available.results.length === 0) {
return Response.json({ error: "当前没有可领取内容" }, { status: 409 });
@@ -122,6 +140,12 @@ export async function POST(request: Request) {
.bind(available.results.length, partnerId),
);
await db.batch(statements);
} else if (body.action === "release_distribution") {
if (!(await isManagerRequest(request))) return adminForbidden();
await releaseUnfinishedDistribution(
db,
String(body.distributionId ?? "").trim(),
);
} else if (body.action === "save_collection_schedule") {
const taskId = String(body.taskId ?? "").trim();
const startDate = String(body.startDate ?? "").trim();
@@ -146,12 +170,18 @@ export async function POST(request: Request) {
);
}
const task = await db
.prepare("SELECT id FROM tasks WHERE id = ?")
.prepare("SELECT id, task_type FROM tasks WHERE id = ?")
.bind(taskId)
.first<{ id: string }>();
.first<{ id: string; task_type?: string | null }>();
if (!task) {
return Response.json({ error: "任务不存在" }, { status: 404 });
}
if (task.task_type === "screenshot_collect") {
return Response.json(
{ error: "截图回收任务不需要设置数据采集计划" },
{ status: 400 },
);
}
await db.batch([
db
.prepare(
@@ -179,7 +209,7 @@ export async function POST(request: Request) {
AND publish_url != ''`,
)
.bind(
`已安排${days.length}个采集日，每日10:00执行`,
`已安排${days.length}个采集日，每日09:00执行`,
taskId,
),
]);
@@ -193,12 +223,7 @@ export async function POST(request: Request) {
"catchup",
taskId,
).catch(() => undefined);
const executionContext = getRequestExecutionContext();
if (executionContext) {
executionContext.waitUntil(catchup);
} else {
await catchup;
}
runInBackground(catchup, "collection catchup");
} else if (body.action === "run_due_collections") {
await runDueScheduledCollections(
db,
@@ -224,6 +249,24 @@ export async function POST(request: Request) {
if (body.action === "collect" && day === null) {
return Response.json({ error: "采集周期无效" }, { status: 400 });
}
const distribution = await db
.prepare(
`SELECT t.task_type
FROM distributions d
JOIN tasks t ON t.id = d.task_id
WHERE d.id = ?`,
)
.bind(distributionId)
.first<{ task_type?: string | null }>();
if (!distribution) {
return Response.json({ error: "笔记记录不存在" }, { status: 404 });
}
if (distribution.task_type === "screenshot_collect") {
return Response.json(
{ error: "截图回收任务不支持公开数据采集" },
{ status: 400 },
);
}
await collectDistributionMetrics(
db,
distributionId,
@@ -239,6 +282,19 @@ export async function POST(request: Request) {
if (!taskId) {
return Response.json({ error: "请选择需要补采的任务" }, { status: 400 });
}
const task = await db
.prepare("SELECT task_type FROM tasks WHERE id = ?")
.bind(taskId)
.first<{ task_type?: string | null }>();
if (!task) {
return Response.json({ error: "任务不存在" }, { status: 404 });
}
if (task.task_type === "screenshot_collect") {
return Response.json(
{ error: "截图回收任务没有需要补采的公开数据" },
{ status: 400 },
);
}
await retryFailedCollections(
db,
taskId,
@@ -254,12 +310,7 @@ export async function POST(request: Request) {
),
Math.max(1, Math.min(25, numberValue(body.limit, 10))),
).catch(() => undefined);
const executionContext = getRequestExecutionContext();
if (executionContext) {
executionContext.waitUntil(backfill);
} else {
await backfill;
}
runInBackground(backfill, "account profile backfill");
} else if (body.action === "set_public_account_ids") {
const items = Array.isArray(body.items) ? body.items.slice(0, 50) : [];
const normalized = items
@@ -322,7 +373,13 @@ export async function POST(request: Request) {
} catch (error) {
return Response.json(
{ error: error instanceof Error ? error.message : "操作失败" },
{ status: error instanceof FeishuSourceError ? error.status : 500 },
{
status:
error instanceof FeishuSourceError ||
error instanceof DistributionReleaseError
? error.status
: 500,
},
);
}
}

5
app/api/auth/login/route.ts
View File

@@ -3,12 +3,11 @@ import {
createSessionCookie,
ensureInitialSuperAdmin,
normalizeUsername,
requestUsesHttps,
verifyPassword,
} from "../../../../lib/user-auth";
import { getRawDb } from "../../../../lib/mvp-db";
export const runtime = "edge";
export async function POST(request: Request) {
try {
await ensureInitialSuperAdmin();
@@ -33,7 +32,7 @@ export async function POST(request: Request) {
return Response.json({ error: "账号或密码错误" }, { status: 401 });
}
const token = await createSession(user.id);
const secure = new URL(request.url).protocol === "https:";
const secure = requestUsesHttps(request);
return Response.json(
{ user: { id: user.id, username: user.username, role: user.role } },
{ headers: { "Set-Cookie": createSessionCookie(token, secure) } },

5
app/api/auth/logout/route.ts
View File

@@ -1,15 +1,14 @@
import {
clearSessionCookie,
deleteSession,
requestUsesHttps,
sessionCookieFromHeader,
} from "../../../../lib/user-auth";
export const runtime = "edge";
export async function POST(request: Request) {
const token = sessionCookieFromHeader(request.headers.get("cookie"));
await deleteSession(token);
const secure = new URL(request.url).protocol === "https:";
const secure = requestUsesHttps(request);
return Response.json(
{ loggedOut: true },
{ headers: { "Set-Cookie": clearSessionCookie(secure) } },

15
app/api/bootstrap/route.ts
View File

@@ -1,5 +1,7 @@
import { env } from "cloudflare:workers";
import { getRequestExecutionContext } from "vinext/shims/request-context";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import { runInBackground } from "../../../lib/background";
const env = getRuntimeEnv();
import { backfillAccountProfiles } from "../../../lib/account-enrichment-service";
import { runDueScheduledCollections } from "../../../lib/collection-service";
import {
@@ -14,8 +16,6 @@ import {
} from "../../../lib/mvp-db";
import { authForbidden, getRequestPrincipal } from "../../../lib/user-auth";
export const runtime = "edge";
export async function GET(request: Request) {
const principal = await getRequestPrincipal(request);
if (!principal) return authForbidden();
@@ -55,12 +55,7 @@ export async function GET(request: Request) {
collectionCatchup,
accountBackfill,
]);
const executionContext = getRequestExecutionContext();
if (executionContext) {
executionContext.waitUntil(catchup);
} else {
await catchup;
}
runInBackground(catchup, "bootstrap catchup");
const dashboard = await getDashboardData();
return Response.json(
principal.kind === "user" && principal.user.role === "user"

2
app/api/content-image-upload/route.ts
View File

@@ -2,8 +2,6 @@ import feishuSnapshot from "../../../lib/feishu-source-snapshot.json";
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
import { ensureSchema, getUploadBucket } from "../../../lib/mvp-db";
export const runtime = "edge";
export async function POST(request: Request) {
if (!(await isAdminRequest(request))) return adminForbidden();
try {

4
app/api/creator-screenshot/route.ts
View File

@@ -5,8 +5,6 @@ import {
} from "../../../lib/mvp-db";
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
export const runtime = "edge";
export async function GET(request: Request) {
if (!(await isAdminRequest(request))) return adminForbidden();
try {
@@ -43,7 +41,7 @@ export async function GET(request: Request) {
if (!headers.get("Content-Type")) {
headers.set("Content-Type", "image/jpeg");
}
return new Response(object.body, { headers });
return new Response(await object.arrayBuffer(), { headers });
} catch (error) {
return Response.json(
{ error: error instanceof Error ? error.message : "截图读取失败" },

24
app/api/health/route.ts Normal file
View File

@@ -0,0 +1,24 @@
import { checkDatabaseConnection } from "../../../lib/database";
import { getObjectStore } from "../../../lib/object-store";
export async function GET() {
try {
const database = await checkDatabaseConnection();
return Response.json({
status: database ? "ok" : "degraded",
database,
storage: getObjectStore().root,
scheduler: process.env.ENABLE_SCHEDULER !== "false",
timestamp: new Date().toISOString(),
}, { status: database ? 200 : 503 });
} catch (error) {
return Response.json(
{
status: "error",
database: false,
error: error instanceof Error ? error.message : "health check failed",
},
{ status: 503 },
);
}
}

10
app/api/mcp/route.ts
View File

@@ -1,4 +1,4 @@
import { env } from "cloudflare:workers";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import {
createMcpHandler,
McpServer,
@@ -16,10 +16,11 @@ import {
import { registerMcpOperationTools } from "../../../lib/mcp-tools";
import type { CollectionMcpBindings } from "../../../lib/mcp-collection-client";
export const runtime = "edge";
const env = getRuntimeEnv();
type McpBindings = FeishuBindings & CollectionMcpBindings & {
KOC_MCP_API_KEY?: string;
KOC_LOOP_MCP_API_KEY?: string;
KOC_PORTAL_URL?: string;
};
@@ -190,7 +191,10 @@ function originRejected(request: Request) {
}
async function authorize(request: Request) {
const expected = String(getBindings().KOC_MCP_API_KEY ?? "").trim();
const bindings = getBindings();
const expected = String(
bindings.KOC_MCP_API_KEY ?? bindings.KOC_LOOP_MCP_API_KEY ?? "",
).trim();
const authorization = request.headers.get("Authorization") ?? "";
const match = authorization.match(/^Bearer\s+(.+)$/i);
if (!expected || !match || !(await secretsMatch(match[1].trim(), expected))) {

61
app/api/partner-image/route.ts
View File

@@ -1,4 +1,4 @@
import { env } from "cloudflare:workers";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import {
ensureSchema,
getRawDb,
@@ -12,8 +12,9 @@ import {
partnerOptions,
withPartnerCors,
} from "../../../lib/partner-cors";
import { parseResultScreenshotKeys } from "../../../lib/result-screenshots";
export const runtime = "edge";
const env = getRuntimeEnv();
type StoredAsset = {
index: number;
@@ -33,7 +34,8 @@ function findAsset(value: string, imageIndex: number) {
(asset) =>
asset.index === imageIndex &&
typeof asset.key === "string" &&
asset.key.startsWith("content-assets/") &&
(asset.key.startsWith("content-assets/") ||
asset.key.startsWith("task-assets/")) &&
(asset.fileToken === undefined ||
typeof asset.fileToken === "string"),
)
@@ -52,6 +54,7 @@ async function handleGet(request: Request) {
const delegationToken = textValue(url.searchParams.get("share"));
const distributionId = textValue(url.searchParams.get("distribution"));
const imageIndex = Number(url.searchParams.get("index"));
const imageKind = textValue(url.searchParams.get("kind"), 20);
if (
(!delegationToken && (!taskToken || !claimToken)) ||
!distributionId ||
@@ -63,7 +66,10 @@ async function handleGet(request: Request) {
const row = delegationToken
? await getRawDb()
.prepare(
`SELECT c.image_assets
`SELECT c.image_assets,
d.result_screenshot_key,
d.publish_screenshot_key,
d.screenshot_key
FROM distributions d
JOIN contents c ON c.id = d.content_id
JOIN delegation_bundles b ON b.id = d.delegation_bundle_id
@@ -72,10 +78,18 @@ async function handleGet(request: Request) {
AND b.status = 'active'`,
)
.bind(distributionId, delegationToken)
.first<{ image_assets: string }>()
.first<{
image_assets: string;
result_screenshot_key: string | null;
publish_screenshot_key: string | null;
screenshot_key: string | null;
}>()
: await getRawDb()
.prepare(
`SELECT c.image_assets
`SELECT c.image_assets,
d.result_screenshot_key,
d.publish_screenshot_key,
d.screenshot_key
FROM distributions d
JOIN contents c ON c.id = d.content_id
JOIN claims cl ON cl.id = d.claim_id
@@ -86,10 +100,35 @@ async function handleGet(request: Request) {
AND cl.task_id = t.id`,
)
.bind(distributionId, claimToken, taskToken)
.first<{ image_assets: string }>();
const asset = row ? findAsset(row.image_assets, imageIndex) : undefined;
if (!asset) {
return Response.json({ error: "没有找到这张笔记图片" }, { status: 404 });
.first<{
image_assets: string;
result_screenshot_key: string | null;
publish_screenshot_key: string | null;
screenshot_key: string | null;
}>();
const asset =
imageKind === "result"
? row
? {
index: imageIndex,
key: parseResultScreenshotKeys(row.result_screenshot_key)[
imageIndex - 1
],
}
: undefined
: imageKind === "publish"
? row?.publish_screenshot_key?.startsWith("publish-evidence/")
? { index: 1, key: row.publish_screenshot_key }
: undefined
: imageKind === "creator"
? row?.screenshot_key?.startsWith("creator-center/")
? { index: 1, key: row.screenshot_key }
: undefined
: row
? findAsset(row.image_assets, imageIndex)
: undefined;
if (!asset?.key) {
return Response.json({ error: "没有找到这张图片" }, { status: 404 });
}
const bucket = getUploadBucket();
let object = await bucket.get(asset.key);
@@ -111,7 +150,7 @@ async function handleGet(request: Request) {
object.writeHttpMetadata(headers);
headers.set("Cache-Control", "private, max-age=3600");
headers.set("Content-Disposition", `inline; filename="note-${imageIndex}"`);
return new Response(object.body, { headers });
return new Response(await object.arrayBuffer(), { headers });
} catch (error) {
return Response.json(
{ error: error instanceof Error ? error.message : "图片读取失败" },

63
app/api/partner-upload/route.ts
View File

@@ -8,8 +8,11 @@ import {
partnerOptions,
withPartnerCors,
} from "../../../lib/partner-cors";
export const runtime = "edge";
import {
MAX_RESULT_SCREENSHOTS,
parseResultScreenshotKeys,
serializeResultScreenshotKeys,
} from "../../../lib/result-screenshots";
async function readUpload(request: Request) {
const contentType = request.headers.get("content-type") ?? "";
@@ -64,7 +67,7 @@ async function handlePost(request: Request) {
!upload.distributionId ||
upload.fileBytes.byteLength === 0
) {
return Response.json({ error: "请选择发布截图" }, { status: 400 });
return Response.json({ error: "请选择需要上传的截图" }, { status: 400 });
}
if (
!upload.fileType.startsWith("image/") ||
@@ -76,21 +79,26 @@ async function handlePost(request: Request) {
);
}
const isCreatorCenter = upload.uploadKind === "creator-center";
const isTaskResult = upload.uploadKind === "task-result";
if (!["publish", "creator-center", "task-result"].includes(upload.uploadKind)) {
return Response.json({ error: "不支持的截图类型" }, { status: 400 });
}
const assignment = upload.delegationToken
? await getRawDb()
.prepare(
`SELECT d.id, d.publish_url
`SELECT d.id, d.publish_url, d.result_screenshot_key, t.task_type
FROM distributions d
JOIN delegation_bundles b ON b.id = d.delegation_bundle_id
JOIN tasks t ON t.id = d.task_id
WHERE d.id = ?
AND b.share_token = ?
AND b.status = 'active'`,
)
.bind(upload.distributionId, upload.delegationToken)
.first<{ id: string; publish_url: string | null }>()
.first<{ id: string; publish_url: string | null; result_screenshot_key: string | null; task_type: string }>()
: await getRawDb()
.prepare(
`SELECT d.id, d.publish_url
`SELECT d.id, d.publish_url, d.result_screenshot_key, t.task_type
FROM distributions d
JOIN claims c ON c.id = d.claim_id
JOIN tasks t ON t.id = d.task_id
@@ -100,7 +108,7 @@ async function handlePost(request: Request) {
AND c.task_id = t.id`,
)
.bind(upload.distributionId, upload.claimToken, upload.taskToken)
.first<{ id: string; publish_url: string | null }>();
.first<{ id: string; publish_url: string | null; result_screenshot_key: string | null; task_type: string }>();
if (!assignment) {
return Response.json({ error: "笔记与领取凭证不匹配" }, { status: 403 });
}
@@ -110,15 +118,45 @@ async function handlePost(request: Request) {
{ status: 409 },
);
}
if (isTaskResult && assignment.task_type !== "screenshot_collect") {
return Response.json({ error: "当前任务不支持截图结果回填" }, { status: 400 });
}
if (!isTaskResult && assignment.task_type === "screenshot_collect") {
return Response.json({ error: "截图回收任务请上传任务结果截图" }, { status: 400 });
}
const existingResultKeys = isTaskResult
? parseResultScreenshotKeys(assignment.result_screenshot_key)
: [];
if (isTaskResult && existingResultKeys.length >= MAX_RESULT_SCREENSHOTS) {
return Response.json(
{ error: `每份任务最多上传${MAX_RESULT_SCREENSHOTS}张截图` },
{ status: 409 },
);
}
const extension =
upload.fileName.split(".").at(-1)?.replace(/[^a-zA-Z0-9]/g, "") || "jpg";
const key = isCreatorCenter
const key = isTaskResult
? `task-results/${upload.distributionId}/${uid("shot")}.${extension}`
: isCreatorCenter
? `creator-center/${upload.distributionId}/${uid("shot")}.${extension}`
: `publish-evidence/${upload.distributionId}/${uid("shot")}.${extension}`;
await getUploadBucket().put(key, upload.fileBytes, {
httpMetadata: { contentType: upload.fileType },
});
if (isCreatorCenter) {
if (isTaskResult) {
await getRawDb()
.prepare(
`UPDATE distributions SET
result_screenshot_key = ?,
updated_at = CURRENT_TIMESTAMP
WHERE id = ?`,
)
.bind(
serializeResultScreenshotKeys([...existingResultKeys, key]),
upload.distributionId,
)
.run();
} else if (isCreatorCenter) {
await getRawDb()
.prepare(
`UPDATE distributions SET
@@ -145,7 +183,12 @@ async function handlePost(request: Request) {
}
return Response.json({
uploaded: true,
kind: isCreatorCenter ? "creator-center" : "publish",
screenshotCount: isTaskResult ? existingResultKeys.length + 1 : undefined,
kind: isTaskResult
? "task-result"
: isCreatorCenter
? "creator-center"
: "publish",
});
} catch (error) {
return Response.json(

135
app/api/partner/route.ts
View File

@@ -1,5 +1,8 @@
import { env } from "cloudflare:workers";
import { getRequestExecutionContext } from "vinext/shims/request-context";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import { runInBackground } from "../../../lib/background";
import type { DatabaseStatement } from "../../../lib/database";
const env = getRuntimeEnv();
import { enrichDistributionAccount } from "../../../lib/account-enrichment-service";
import { createCollectionRunTasks } from "../../../lib/collection-service";
import {
@@ -22,8 +25,6 @@ import {
withPartnerCors,
} from "../../../lib/partner-cors";
export const runtime = "edge";
type PartnerBody = {
action?: string;
taskToken?: string;
@@ -39,6 +40,7 @@ type PartnerBody = {
publishUrl?: string;
exposure?: number | string;
views?: number | string;
resultScreenshotKey?: string;
};
type ImageAsset = {
@@ -102,7 +104,7 @@ function publicImageAssets(value: unknown): ImageAsset[] {
async function findTask(taskToken: string) {
return getRawDb()
.prepare(
`SELECT id, name, brand, quantity, claimed_quantity, due_at, status
`SELECT id, name, brand, quantity, claimed_quantity, due_at, status, task_type
FROM tasks WHERE share_token = ?`,
)
.bind(taskToken)
@@ -114,6 +116,7 @@ async function findTask(taskToken: string) {
claimed_quantity: number;
due_at: string;
status: string;
task_type: string;
}>();
}
@@ -128,6 +131,7 @@ async function findDelegationAccess(delegationToken: string) {
t.claimed_quantity,
t.due_at,
t.status,
t.task_type,
b.id AS bundle_id,
b.label AS bundle_label,
b.quantity AS bundle_quantity,
@@ -145,6 +149,7 @@ async function findDelegationAccess(delegationToken: string) {
claimed_quantity: number;
due_at: string;
status: string;
task_type: string;
bundle_id: string;
bundle_label: string;
bundle_quantity: number;
@@ -165,7 +170,9 @@ async function findAccessibleAssignment(
d.account_id,
d.publish_url,
d.publish_screenshot_key,
d.screenshot_key`;
d.screenshot_key,
d.result_screenshot_key,
d.result_submitted_at`;
if (delegationToken) {
return db
.prepare(
@@ -185,6 +192,8 @@ async function findAccessibleAssignment(
publish_url: string | null;
publish_screenshot_key: string | null;
screenshot_key: string | null;
result_screenshot_key: string | null;
result_submitted_at: string | null;
}>();
}
if (!claimToken) return null;
@@ -203,6 +212,8 @@ async function findAccessibleAssignment(
publish_url: string | null;
publish_screenshot_key: string | null;
screenshot_key: string | null;
result_screenshot_key: string | null;
result_submitted_at: string | null;
}>();
}
@@ -263,6 +274,8 @@ async function handleGet(request: Request) {
d.publish_url,
d.publish_time,
d.publish_screenshot_key,
d.result_screenshot_key,
d.result_submitted_at,
d.screenshot_key AS creator_screenshot_key,
d.ocr_status,
d.exposure,
@@ -295,6 +308,7 @@ async function handleGet(request: Request) {
b.created_at,
b.revoked_at,
SUM(CASE WHEN d.publish_url IS NOT NULL AND d.publish_url != '' THEN 1 ELSE 0 END) AS completed_count,
SUM(CASE WHEN d.result_submitted_at IS NOT NULL THEN 1 ELSE 0 END) AS result_completed_count,
SUM(CASE WHEN d.screenshot_key IS NOT NULL AND d.exposure IS NOT NULL AND d.views IS NOT NULL THEN 1 ELSE 0 END) AS creator_completed_count
FROM delegation_bundles b
LEFT JOIN distributions d ON d.delegation_bundle_id = b.id
@@ -325,6 +339,8 @@ async function handleGet(request: Request) {
d.publish_url,
d.publish_time,
d.publish_screenshot_key,
d.result_screenshot_key,
d.result_submitted_at,
d.screenshot_key AS creator_screenshot_key,
d.ocr_status,
d.exposure,
@@ -344,7 +360,7 @@ async function handleGet(request: Request) {
.all();
delegation = {
id: delegationAccess.bundle_id,
label: "转派发布包",
label: task.task_type === "screenshot_collect" ? "转派截图任务包" : "转派发布包",
quantity: delegationAccess.bundle_quantity,
createdAt: delegationAccess.bundle_created_at,
assignments: assignments.results.map((assignment) => ({
@@ -362,6 +378,7 @@ async function handleGet(request: Request) {
brand: task.brand,
dueAt: task.due_at,
status: task.status,
type: task.task_type,
}
: {
name: task.name,
@@ -370,6 +387,7 @@ async function handleGet(request: Request) {
claimedQuantity: task.claimed_quantity,
dueAt: task.due_at,
status: task.status,
type: task.task_type,
availableQuantity: available?.count ?? 0,
},
claim,
@@ -402,10 +420,11 @@ async function handlePost(request: Request) {
if (
delegationToken &&
body.action !== "submit" &&
body.action !== "submit_creator_metrics"
body.action !== "submit_creator_metrics" &&
body.action !== "submit_screenshot_result"
) {
return Response.json(
{ error: "分享链接只能用于查看和回填包内笔记" },
{ error: "分享链接只能用于查看和回填包内任务" },
{ status: 403 },
);
}
@@ -438,7 +457,10 @@ async function handlePost(request: Request) {
c.quantity,
c.created_at,
COUNT(d.id) AS note_count,
SUM(CASE WHEN d.publish_url IS NOT NULL AND d.publish_url != '' THEN 1 ELSE 0 END) AS completed_count,
SUM(CASE
WHEN ? = 'screenshot_collect' AND d.result_submitted_at IS NOT NULL THEN 1
WHEN ? != 'screenshot_collect' AND d.publish_url IS NOT NULL AND d.publish_url != '' THEN 1
ELSE 0 END) AS completed_count,
MIN(co.title) AS first_title
FROM claims c
LEFT JOIN distributions d ON d.claim_id = c.id
@@ -448,7 +470,7 @@ async function handlePost(request: Request) {
ORDER BY c.created_at DESC, c.id DESC
LIMIT 20`,
)
.bind(task.id, partnerId, legacyPartnerId)
.bind(task.task_type, task.task_type, task.id, partnerId, legacyPartnerId)
.all<{
claim_token: string;
quantity: number;
@@ -469,7 +491,7 @@ async function handlePost(request: Request) {
quantity: claim.note_count || claim.quantity,
completedCount: claim.completed_count || 0,
createdAt: claim.created_at,
firstTitle: claim.first_title || "领取的笔记",
firstTitle: claim.first_title || (task.task_type === "screenshot_collect" ? "领取的截图任务" : "领取的笔记"),
})),
});
}
@@ -495,9 +517,9 @@ async function handlePost(request: Request) {
`SELECT id FROM contents
WHERE task_id = ? AND status = 'available'
ORDER BY COALESCE(source_row, 999999), created_at, id
LIMIT ?`,
LIMIT ${quantity}`,
)
.bind(task.id, quantity)
.bind(task.id)
.all<{ id: string }>();
if (available.results.length === 0) {
return Response.json({ error: "当前任务已领完" }, { status: 409 });
@@ -508,7 +530,7 @@ async function handlePost(request: Request) {
const claimantName = claimantIdentifier.display;
const claimId = uid("claim");
const claimToken = crypto.randomUUID().replaceAll("-", "");
const statements: D1PreparedStatement[] = [
const statements: DatabaseStatement[] = [
db
.prepare(
`INSERT INTO partners
@@ -584,7 +606,7 @@ async function handlePost(request: Request) {
}
if (distributionIds.length === 0) {
return Response.json(
{ error: "请至少选择一篇待发布笔记" },
{ error: task.task_type === "screenshot_collect" ? "请至少选择一份待提交任务" : "请至少选择一篇待发布笔记" },
{ status: 400 },
);
}
@@ -602,7 +624,7 @@ async function handlePost(request: Request) {
const placeholders = distributionIds.map(() => "?").join(", ");
const selected = await db
.prepare(
`SELECT id, publish_url, delegation_bundle_id
`SELECT id, publish_url, result_submitted_at, delegation_bundle_id
FROM distributions
WHERE claim_id = ? AND id IN (${placeholders})`,
)
@@ -610,6 +632,7 @@ async function handlePost(request: Request) {
.all<{
id: string;
publish_url: string | null;
result_submitted_at: string | null;
delegation_bundle_id: string | null;
}>();
if (selected.results.length !== distributionIds.length) {
@@ -618,9 +641,13 @@ async function handlePost(request: Request) {
{ status: 403 },
);
}
if (selected.results.some((item) => item.publish_url)) {
if (selected.results.some((item) =>
task.task_type === "screenshot_collect"
? item.result_submitted_at
: item.publish_url,
)) {
return Response.json(
{ error: "已发布的笔记不能再次转派" },
{ error: task.task_type === "screenshot_collect" ? "已提交的截图任务不能再次转派" : "已发布的笔记不能再次转派" },
{ status: 409 },
);
}
@@ -632,7 +659,7 @@ async function handlePost(request: Request) {
}
const bundleId = uid("delegate");
const shareToken = crypto.randomUUID().replaceAll("-", "");
const statements: D1PreparedStatement[] = [
const statements: DatabaseStatement[] = [
db
.prepare(
`INSERT INTO delegation_bundles
@@ -658,6 +685,7 @@ async function handlePost(request: Request) {
WHERE id = ?
AND claim_id = ?
AND publish_url IS NULL
AND result_submitted_at IS NULL
AND delegation_bundle_id IS NULL`,
)
.bind(bundleId, distributionId, claimRow.id),
@@ -721,19 +749,18 @@ async function handlePost(request: Request) {
{ status: 404 },
);
}
const published = await db
const completed = await db
.prepare(
`SELECT COUNT(*) AS count
FROM distributions
WHERE delegation_bundle_id = ?
AND publish_url IS NOT NULL
AND publish_url != ''`,
AND (publish_url IS NOT NULL AND publish_url != '' OR result_submitted_at IS NOT NULL)`,
)
.bind(bundle.id)
.first<{ count: number }>();
if ((published?.count ?? 0) > 0) {
if ((completed?.count ?? 0) > 0) {
return Response.json(
{ error: "该分享包已有笔记发布，需保留链接继续完成第7天数据回收" },
{ error: task.task_type === "screenshot_collect" ? "该分享包已有截图提交，不能撤销" : "该分享包已有笔记发布，需保留链接继续完成第7天数据回收" },
{ status: 409 },
);
}
@@ -759,6 +786,9 @@ async function handlePost(request: Request) {
}
if (body.action === "submit") {
if (task.task_type === "screenshot_collect") {
return Response.json({ error: "截图回收任务无需填写发布链接" }, { status: 400 });
}
const claimToken = textValue(body.claimToken, 80);
const distributionId = textValue(body.distributionId, 80);
const publishInput = textValue(body.publishUrl, 5000);
@@ -796,7 +826,7 @@ async function handlePost(request: Request) {
const accountId =
reuseExistingAccount ||
`account-${hashText(`${account.platform}:${account.platformUid}`)}`;
const statements: D1PreparedStatement[] = [];
const statements: DatabaseStatement[] = [];
if (!reuseExistingAccount) {
statements.push(
db
@@ -884,17 +914,15 @@ async function handlePost(request: Request) {
env as unknown as CollectionMcpBindings,
),
).catch(() => undefined);
const executionContext = getRequestExecutionContext();
if (executionContext) {
executionContext.waitUntil(enrichment);
} else {
await enrichment;
}
runInBackground(enrichment, "distribution account enrichment");
}
return Response.json({ ok: true });
}
if (body.action === "submit_creator_metrics") {
if (task.task_type === "screenshot_collect") {
return Response.json({ error: "截图回收任务不需要创作者数据" }, { status: 400 });
}
const claimToken = textValue(body.claimToken, 80);
const distributionId = textValue(body.distributionId, 80);
const exposure = creatorMetricValue(body.exposure);
@@ -941,6 +969,49 @@ async function handlePost(request: Request) {
return Response.json({ submitted: true });
}
if (body.action === "submit_screenshot_result") {
if (task.task_type !== "screenshot_collect") {
return Response.json({ error: "当前任务不支持这种回填方式" }, { status: 400 });
}
const claimToken = textValue(body.claimToken, 80);
const distributionId = textValue(body.distributionId, 80);
const assignment = await findAccessibleAssignment(
task.id,
distributionId,
claimToken,
delegationToken,
);
if (!assignment) {
return Response.json({ error: "任务与领取凭证不匹配" }, { status: 403 });
}
if (!assignment.result_screenshot_key) {
return Response.json({ error: "请先上传任务截图" }, { status: 400 });
}
const statements: DatabaseStatement[] = [
db
.prepare(
`UPDATE distributions SET
result_submitted_at = CURRENT_TIMESTAMP,
status = 'complete',
updated_at = CURRENT_TIMESTAMP
WHERE id = ?`,
)
.bind(assignment.id),
];
if (!assignment.result_submitted_at) {
statements.push(
db
.prepare(
`UPDATE partners SET completed_total = completed_total + 1
WHERE id = ?`,
)
.bind(assignment.partner_id),
);
}
await db.batch(statements);
return Response.json({ submitted: true });
}
return Response.json({ error: "不支持的操作" }, { status: 400 });
} catch (error) {
return Response.json(

7
app/api/recovery-export/route.ts
View File

@@ -1,4 +1,5 @@
import { env } from "cloudflare:workers";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import type { StoredObjectBody } from "../../../lib/object-store";
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
import { parseStoredDate } from "../../../lib/date-utils";
import {
@@ -17,7 +18,7 @@ import {
} from "../../../lib/recovery-workbook";
import { consumeMcpExportToken } from "../../../lib/mcp-export-token";
export const runtime = "edge";
const env = getRuntimeEnv();
type StoredAsset = {
index: number;
@@ -159,7 +160,7 @@ function collectionLabel(row: ExportRow) {
: label;
}
function contentTypeFromObject(object: R2ObjectBody) {
function contentTypeFromObject(object: StoredObjectBody) {
const headers = new Headers();
object.writeHttpMetadata(headers);
return headers.get("Content-Type") || "application/octet-stream";

13
app/api/resources-export/route.ts
View File

@@ -7,8 +7,6 @@ import {
} from "../../../lib/recovery-workbook";
import { consumeMcpExportToken } from "../../../lib/mcp-export-token";
export const runtime = "edge";
type AccountRow = {
id: string;
platform: string;
@@ -18,6 +16,7 @@ type AccountRow = {
ip_location: string;
followers: number;
post_count: number;
cooperation_source: string;
first_seen_at: string;
last_seen_at: string;
};
@@ -118,7 +117,15 @@ async function exportAccounts(accountIds: string[]) {
];
const rows: RecoveryWorkbookRow[] = accounts.map((account, index) => {
const cooperation = cooperationByAccount.get(account.id) ?? [];
const sources = [...new Set(cooperation.map((item) => item.partner_name))];
const sources = [
...new Set([
...cooperation.map((item) => item.partner_name),
...(account.cooperation_source || "")
.split(/[、,，;；|]/)
.map((item) => item.trim())
.filter(Boolean),
]),
];
const partnerManagedOnly =
cooperation.some((item) => item.delegation_bundle_id) &&
cooperation.every((item) => item.delegation_bundle_id);

324
app/api/resources-import/route.ts Normal file
View File

@@ -0,0 +1,324 @@
import { isManagerRequest, managerForbidden } from "../../../lib/user-auth";
import { ensureSchema, getRawDb } from "../../../lib/mvp-db";
import {
resolveCollectionMcpConfig,
resolveXhsProfileDetailsFromMcp,
resolveXhsPublicAccountDetails,
type CollectionMcpBindings,
} from "../../../lib/mcp-collection-client";
import { getRuntimeEnv } from "../../../lib/runtime-env";
import {
mergeCooperationSources,
normalizeProfileUrl,
parseResourceImportFile,
RESOURCE_IMPORT_MAX_BYTES,
resourcePlatformUid,
resourceImportMissingFields,
type ResourceImportRow,
} from "../../../lib/resource-import";
type AccountRow = {
id: string;
platform: string;
platform_uid: string;
public_account_id: string;
nickname: string;
profile_url: string;
ip_location: string;
followers: number;
cooperation_source: string;
};
type AnalyzedRow = ResourceImportRow & {
action: "create" | "update" | "error";
accountId: string;
platformUid: string;
cooperationSource: string;
};
function identityKey(platform: string, value: string) {
return `${platform.trim().toLocaleLowerCase("zh-CN")}|${value
.trim()
.toLocaleLowerCase("zh-CN")}`;
}
async function loadAccounts() {
return getRawDb()
.prepare(
`SELECT id, platform, platform_uid, public_account_id, nickname,
profile_url, ip_location, followers, cooperation_source
FROM accounts`,
)
.all<AccountRow>();
}
async function mapConcurrent<T, R>(
items: T[],
limit: number,
worker: (item: T) => Promise<R>,
) {
const results = new Array<R>(items.length);
let cursor = 0;
await Promise.all(
Array.from({ length: Math.min(limit, items.length) }, async () => {
while (cursor < items.length) {
const index = cursor;
cursor += 1;
results[index] = await worker(items[index]);
}
}),
);
return results;
}
async function enrichRows(rows: ResourceImportRow[], accounts: AccountRow[]) {
const existingByProfile = new Map<string, AccountRow>();
for (const account of accounts) {
const profileUrl = normalizeProfileUrl(account.profile_url || "");
if (profileUrl) {
existingByProfile.set(identityKey(account.platform, profileUrl), account);
}
}
const mcpConfig = resolveCollectionMcpConfig(
getRuntimeEnv() as unknown as CollectionMcpBindings,
);
return mapConcurrent(rows, 4, async (row) => {
if (row.errors.length > 0 || !row.profileUrl || row.platform !== "小红书") {
return row;
}
const existing = existingByProfile.get(identityKey(row.platform, row.profileUrl));
const existingIpLocation =
existing?.ip_location && existing.ip_location !== "待识别"
? existing.ip_location
: "";
const baseline: ResourceImportRow = {
...row,
nickname: row.nickname || existing?.nickname || "",
publicAccountId: row.publicAccountId || existing?.public_account_id || "",
ipLocation: row.ipLocation || existingIpLocation,
followers: row.followersResolved
? row.followers
: Number(existing?.followers || 0),
followersResolved:
row.followersResolved || Number(existing?.followers || 0) > 0,
};
if (resourceImportMissingFields(baseline).length === 0) {
return baseline;
}
let details: {
nickname: string | null;
redId: string | null;
followers: number | null;
ipLocation: string | null;
} = await resolveXhsProfileDetailsFromMcp(row.profileUrl, mcpConfig).catch(
() => ({ nickname: null, redId: null, followers: null, ipLocation: null }),
);
const mcpResult = {
nickname: baseline.nickname || details.nickname?.trim() || "",
publicAccountId: baseline.publicAccountId || details.redId?.trim() || "",
ipLocation: baseline.ipLocation || details.ipLocation?.trim() || "",
followersResolved: baseline.followersResolved || details.followers !== null,
};
if (resourceImportMissingFields(mcpResult).length > 0) {
const publicDetails = await resolveXhsPublicAccountDetails(row.profileUrl).catch(
() => ({ nickname: null, redId: null, followers: null, ipLocation: null }),
);
details = {
nickname: details.nickname || publicDetails.nickname,
redId: details.redId || publicDetails.redId,
followers: details.followers ?? publicDetails.followers,
ipLocation: details.ipLocation || publicDetails.ipLocation,
};
}
return {
...baseline,
nickname: baseline.nickname || details.nickname?.trim() || "待识别账号",
publicAccountId: baseline.publicAccountId || details.redId?.trim() || "",
ipLocation: baseline.ipLocation || details.ipLocation?.trim() || "待识别",
followers: baseline.followersResolved
? baseline.followers
: (details.followers ?? 0),
followersResolved:
baseline.followersResolved || details.followers !== null,
};
});
}
function analyzeRows(rows: ResourceImportRow[], accountRows: AccountRow[]) {
const profileMap = new Map<string, AccountRow>();
const publicIdMap = new Map<string, AccountRow>();
const platformUidMap = new Map<string, AccountRow>();
for (const account of accountRows) {
const profileUrl = normalizeProfileUrl(account.profile_url || "");
if (profileUrl) profileMap.set(identityKey(account.platform, profileUrl), account);
if (account.public_account_id) {
publicIdMap.set(identityKey(account.platform, account.public_account_id), account);
}
platformUidMap.set(identityKey(account.platform, account.platform_uid), account);
}
return rows.map<AnalyzedRow>((row) => {
const platformUid = resourcePlatformUid(row);
const profileMatch = row.profileUrl
? profileMap.get(identityKey(row.platform, row.profileUrl))
: undefined;
const publicIdMatch = row.publicAccountId
? publicIdMap.get(identityKey(row.platform, row.publicAccountId))
: undefined;
const uidMatch = platformUidMap.get(identityKey(row.platform, platformUid));
const matches = [profileMatch, publicIdMatch, uidMatch].filter(
(account): account is AccountRow => Boolean(account),
);
const matchedIds = [...new Set(matches.map((account) => account.id))];
const errors = [...row.errors];
if (matchedIds.length > 1) {
errors.push("账号主页和账号ID匹配到不同的现有账号，请先核对");
}
const existing = matchedIds.length === 1 ? matches[0] : undefined;
const accountId = existing?.id ?? `account-${crypto.randomUUID().slice(0, 12)}`;
const analyzed: AnalyzedRow = {
...row,
errors,
action: errors.length > 0 ? "error" : existing ? "update" : "create",
accountId,
platformUid: existing?.platform_uid ?? platformUid,
cooperationSource: mergeCooperationSources(
existing?.cooperation_source ?? "",
row.cooperationSource,
),
};
if (analyzed.action !== "error") {
const virtual: AccountRow = {
id: accountId,
platform: row.platform,
platform_uid: analyzed.platformUid,
public_account_id: row.publicAccountId || existing?.public_account_id || "",
nickname: row.nickname,
profile_url: row.profileUrl || existing?.profile_url || "",
ip_location: row.ipLocation || existing?.ip_location || "待识别",
followers: row.followers || existing?.followers || 0,
cooperation_source: analyzed.cooperationSource,
};
if (row.profileUrl) profileMap.set(identityKey(row.platform, row.profileUrl), virtual);
if (row.publicAccountId) {
publicIdMap.set(identityKey(row.platform, row.publicAccountId), virtual);
}
platformUidMap.set(identityKey(row.platform, analyzed.platformUid), virtual);
}
return analyzed;
});
}
function summarize(rows: AnalyzedRow[]) {
return {
total: rows.length,
create: rows.filter((row) => row.action === "create").length,
update: rows.filter((row) => row.action === "update").length,
error: rows.filter((row) => row.action === "error").length,
};
}
export async function POST(request: Request) {
if (!(await isManagerRequest(request))) return managerForbidden();
try {
await ensureSchema();
const form = await request.formData();
const file = form.get("file");
const mode = String(form.get("mode") ?? "preview");
if (!(file instanceof File)) {
return Response.json({ error: "请选择需要导入的 Excel 文件" }, { status: 400 });
}
if (file.size <= 0 || file.size > RESOURCE_IMPORT_MAX_BYTES) {
return Response.json({ error: "文件不能为空，且不能超过 5MB" }, { status: 400 });
}
const rows = parseResourceImportFile(file.name, new Uint8Array(await file.arrayBuffer()));
const accounts = await loadAccounts();
const enriched = await enrichRows(rows, accounts.results);
const analyzed = analyzeRows(enriched, accounts.results);
const summary = summarize(analyzed);
if (mode !== "commit") {
return Response.json({
summary,
rows: analyzed.slice(0, 100).map((row) => ({
rowNumber: row.rowNumber,
platform: row.platform,
nickname: row.nickname,
publicAccountId: row.publicAccountId,
profileUrl: row.profileUrl,
ipLocation: row.ipLocation,
followers: row.followers,
cooperationSource: row.cooperationSource,
action: row.action,
errors: row.errors,
})),
truncated: analyzed.length > 100,
});
}
if (summary.error > 0) {
return Response.json(
{ error: `有 ${summary.error} 行数据未通过校验，请修正后重新上传`, summary },
{ status: 400 },
);
}
const db = getRawDb();
const statements = analyzed.map((row) =>
row.action === "update"
? db
.prepare(
`UPDATE accounts SET
nickname = ?,
public_account_id = CASE WHEN ? != '' THEN ? ELSE public_account_id END,
profile_url = CASE WHEN ? != '' THEN ? ELSE profile_url END,
ip_location = CASE
WHEN ? != '' AND ? != '待识别' THEN ? ELSE ip_location END,
followers = CASE WHEN ? = 1 THEN ? ELSE followers END,
cooperation_source = ?,
last_seen_at = CURRENT_TIMESTAMP
WHERE id = ?`,
)
.bind(
row.nickname,
row.publicAccountId,
row.publicAccountId,
row.profileUrl,
row.profileUrl,
row.ipLocation,
row.ipLocation,
row.ipLocation,
row.followersResolved ? 1 : 0,
row.followers,
row.cooperationSource,
row.accountId,
)
: db
.prepare(
`INSERT INTO accounts
(id, platform, platform_uid, public_account_id, nickname,
profile_url, ip_location, followers, post_count, avg_views,
cooperation_source)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, 0, ?)`,
)
.bind(
row.accountId,
row.platform,
row.platformUid,
row.publicAccountId,
row.nickname,
row.profileUrl,
row.ipLocation,
row.followers,
row.cooperationSource,
),
);
if (statements.length > 0) await db.batch(statements);
return Response.json({
summary,
message: `已导入 ${summary.create} 个新账号，更新 ${summary.update} 个已有账号`,
});
} catch (error) {
const message = error instanceof Error ? error.message : "导入失败";
return Response.json({ error: message }, { status: 400 });
}
}

102
app/api/screenshot-task-export/route.ts Normal file
View File

@@ -0,0 +1,102 @@
import { zipSync, strToU8 } from "fflate";
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
import { ensureSchema, getRawDb, getUploadBucket } from "../../../lib/mvp-db";
import { parseResultScreenshotKeys } from "../../../lib/result-screenshots";
function safeName(value: string) {
return value.replace(/[\\/:*?"<>|\r\n]/g, "_").trim().slice(0, 60) || "KOC";
}
function csvCell(value: unknown) {
const text = String(value ?? "");
return `"${text.replaceAll('"', '""')}"`;
}
export async function GET(request: Request) {
if (!(await isAdminRequest(request))) return adminForbidden();
try {
await ensureSchema();
const taskId = new URL(request.url).searchParams.get("task")?.trim();
if (!taskId) {
return Response.json({ error: "缺少任务参数" }, { status: 400 });
}
const task = await getRawDb()
.prepare("SELECT name, task_type FROM tasks WHERE id = ?")
.bind(taskId)
.first<{ name: string; task_type: string }>();
if (!task || task.task_type !== "screenshot_collect") {
return Response.json({ error: "没有找到截图回收任务" }, { status: 404 });
}
const rows = await getRawDb()
.prepare(
`SELECT d.id, d.result_screenshot_key, d.result_submitted_at,
d.claimed_at, c.source_row, c.title, p.name AS partner_name,
cl.claimant_name
FROM distributions d
JOIN contents c ON c.id = d.content_id
JOIN partners p ON p.id = d.partner_id
LEFT JOIN claims cl ON cl.id = d.claim_id
WHERE d.task_id = ?
ORDER BY COALESCE(c.source_row, 999999), d.claimed_at, d.id`,
)
.bind(taskId)
.all<{
id: string;
result_screenshot_key: string | null;
result_submitted_at: string | null;
claimed_at: string;
source_row: number | null;
title: string;
partner_name: string;
claimant_name: string | null;
}>();
const files: Record<string, Uint8Array> = {};
const manifest = [
["序号", "搜索关键词", "领取人", "领取时间", "提交时间", "文件名"],
];
let exported = 0;
for (const [rowIndex, row] of rows.results.entries()) {
const fileNames: string[] = [];
const screenshotKeys = row.result_submitted_at
? parseResultScreenshotKeys(row.result_screenshot_key)
: [];
for (const [imageIndex, screenshotKey] of screenshotKeys.entries()) {
const object = await getUploadBucket().get(screenshotKey);
if (!object) continue;
const extension = screenshotKey.split(".").at(-1) || "jpg";
const fileName = `${String(row.source_row ?? rowIndex + 1).padStart(3, "0")}-${safeName(row.claimant_name || row.partner_name)}-${imageIndex + 1}.${extension}`;
files[fileName] = new Uint8Array(await object.arrayBuffer());
fileNames.push(fileName);
exported += 1;
}
manifest.push([
String(row.source_row ?? ""),
row.title,
row.claimant_name || row.partner_name,
row.claimed_at,
row.result_submitted_at || "",
fileNames.join("；"),
]);
}
files["回收清单.csv"] = strToU8(
`\uFEFF${manifest.map((cells) => cells.map(csvCell).join(",")).join("\r\n")}`,
);
const archive = zipSync(files, { level: 0 });
const headers = new Headers({
"Content-Type": "application/zip",
"Content-Disposition": `attachment; filename*=UTF-8''${encodeURIComponent(`${safeName(task.name)}-截图回收.zip`)}`,
"Cache-Control": "private, no-store",
"X-KOC-Exported-Count": String(exported),
});
const body = archive.buffer.slice(
archive.byteOffset,
archive.byteOffset + archive.byteLength,
) as ArrayBuffer;
return new Response(body, { headers });
} catch (error) {
return Response.json(
{ error: error instanceof Error ? error.message : "截图打包失败" },
{ status: 500 },
);
}
}

30
app/api/task-example-upload/route.ts Normal file
View File

@@ -0,0 +1,30 @@
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
import { ensureSchema, getUploadBucket, uid } from "../../../lib/mvp-db";
export async function POST(request: Request) {
if (!(await isAdminRequest(request))) return adminForbidden();
try {
await ensureSchema();
const form = await request.formData();
const file = form.get("file");
if (!(file instanceof File) || file.size === 0) {
return Response.json({ error: "请选择示例截图" }, { status: 400 });
}
if (!file.type.startsWith("image/") || file.size > 8_000_000) {
return Response.json({ error: "仅支持8MB以内的图片" }, { status: 400 });
}
const extension =
file.name.split(".").at(-1)?.replace(/[^a-zA-Z0-9]/g, "") || "jpg";
const key = `task-assets/${uid("example")}.${extension}`;
await getUploadBucket().put(key, await file.arrayBuffer(), {
httpMetadata: { contentType: file.type },
customMetadata: { kind: "screenshot-task-example" },
});
return Response.json({ uploaded: true, key });
} catch (error) {
return Response.json(
{ error: error instanceof Error ? error.message : "示例截图上传失败" },
{ status: 500 },
);
}
}

49
app/api/task-result-image/route.ts Normal file
View File

@@ -0,0 +1,49 @@
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
import { ensureSchema, getRawDb, getUploadBucket } from "../../../lib/mvp-db";
import { parseResultScreenshotKeys } from "../../../lib/result-screenshots";
export async function GET(request: Request) {
if (!(await isAdminRequest(request))) return adminForbidden();
try {
await ensureSchema();
const distributionId = new URL(request.url).searchParams
.get("distribution")
?.trim();
const imageIndex = Math.max(
1,
Number(new URL(request.url).searchParams.get("index") || 1),
);
if (!distributionId) {
return Response.json({ error: "缺少任务记录" }, { status: 400 });
}
const row = await getRawDb()
.prepare(
`SELECT result_screenshot_key FROM distributions
WHERE id = ?`,
)
.bind(distributionId)
.first<{ result_screenshot_key: string | null }>();
const screenshotKey = row
? parseResultScreenshotKeys(row.result_screenshot_key)[imageIndex - 1]
: undefined;
if (!screenshotKey) {
return Response.json({ error: "任务截图不存在" }, { status: 404 });
}
const object = await getUploadBucket().get(screenshotKey);
if (!object) {
return Response.json({ error: "任务截图不存在" }, { status: 404 });
}
const headers = new Headers({
"Cache-Control": "private, no-store",
"Content-Disposition": 'inline; filename="task-result-screenshot"',
"X-Content-Type-Options": "nosniff",
});
object.writeHttpMetadata(headers);
return new Response(await object.arrayBuffer(), { headers });
} catch (error) {
return Response.json(
{ error: error instanceof Error ? error.message : "截图读取失败" },
{ status: 500 },
);
}
}

2
app/api/upload/route.ts
View File

@@ -7,8 +7,6 @@ import {
} from "../../../lib/mvp-db";
import { adminForbidden, isAdminRequest } from "../../../lib/admin-auth";
export const runtime = "edge";
export async function POST(request: Request) {
if (!(await isAdminRequest(request))) return adminForbidden();
try {

2
app/api/users/route.ts
View File

@@ -9,8 +9,6 @@ import {
} from "../../../lib/user-auth";
import { ensureSchema, getRawDb } from "../../../lib/mvp-db";
export const runtime = "edge";
type UserRow = {
id: string;
username: string;

698
app/globals.css
View File

@@ -1,6 +1,8 @@
@import "tailwindcss";
:root {
--font-geist-sans: -apple-system, BlinkMacSystemFont, "Segoe UI", "PingFang SC", "Microsoft YaHei", sans-serif;
--font-geist-mono: "SFMono-Regular", Consolas, "Liberation Mono", monospace;
--ink: #172321;
--ink-soft: #32423f;
--nav: #13201e;
@@ -242,6 +244,146 @@ a {
min-height: 100vh;
}
.task-type-picker {
display: grid;
grid-template-columns: 1fr 1fr;
gap: 12px;
margin: 22px 0 6px;
}
.task-type-picker button {
display: flex;
min-height: 82px;
flex-direction: column;
align-items: flex-start;
justify-content: center;
gap: 7px;
padding: 16px;
border: 1px solid #dbe4df;
border-radius: 14px;
color: #53625d;
background: #fafcfb;
text-align: left;
}
.task-type-picker button.active {
border-color: #278b69;
color: #123f31;
background: #edf8f3;
box-shadow: 0 0 0 3px rgb(39 139 105 / 0.08);
}
.task-type-picker b {
font-size: 15px;
}
.task-type-picker span {
color: #87938f;
font-size: 11px;
line-height: 1.5;
}
.screenshot-task-fields textarea {
width: 100%;
min-height: 108px;
resize: vertical;
padding: 13px 14px;
border: 1px solid #d8e0dc;
border-radius: 12px;
outline: none;
font: inherit;
line-height: 1.65;
}
.screenshot-task-fields textarea:focus {
border-color: #63a88e;
box-shadow: 0 0 0 3px rgb(39 139 105 / 0.08);
}
.task-example-picker {
position: relative;
display: flex;
min-height: 82px;
flex-direction: column;
align-items: center;
justify-content: center;
gap: 5px;
padding: 14px;
border: 1px dashed #bdccc5;
border-radius: 12px;
background: #fafcfb;
}
.task-example-picker.has-file {
border-style: solid;
border-color: #8ec3ae;
background: #f0f8f4;
}
.task-example-picker input {
position: absolute;
inset: 0;
width: 100%;
height: 100%;
opacity: 0;
cursor: pointer;
}
.task-example-picker strong {
max-width: 100%;
overflow: hidden;
text-overflow: ellipsis;
white-space: nowrap;
}
.task-example-picker small {
color: #8c9994;
}
.screenshot-task-table .creator-screenshot-link {
white-space: nowrap;
}
.admin-result-gallery {
display: flex;
min-width: 150px;
align-items: center;
flex-wrap: wrap;
gap: 5px;
}
.admin-result-gallery a,
.admin-result-gallery button {
display: block;
width: 42px;
height: 42px;
padding: 0;
overflow: hidden;
border: 1px solid #dce5e0;
border-radius: 8px;
background: #f4f7f5;
cursor: zoom-in;
}
.admin-result-gallery img {
width: 100%;
height: 100%;
display: block;
object-fit: cover;
}
.admin-result-gallery > span {
color: #74817c;
font-size: 8px;
white-space: nowrap;
}
@media (max-width: 760px) {
.task-type-picker {
grid-template-columns: 1fr;
}
}
.sidebar {
position: fixed;
inset: 0 auto 0 0;
@@ -1566,6 +1708,22 @@ a {
font-size: 11px;
}
.resource-heading-actions {
display: flex;
align-items: center;
justify-content: flex-end;
flex-wrap: wrap;
gap: 8px;
}
.resource-heading-actions > a {
text-decoration: none;
}
.resource-heading-actions .filter-chips {
margin-left: 4px;
}
.resource-toolbar {
display: flex;
align-items: center;
@@ -1658,6 +1816,224 @@ a {
font-size: 11px;
}
.resource-import-modal {
width: min(780px, calc(100vw - 32px));
max-height: min(86vh, 820px);
overflow-y: auto;
}
.import-guide-strip {
display: grid;
grid-template-columns: auto auto 1fr auto auto 1fr auto auto;
align-items: center;
gap: 9px;
margin-bottom: 18px;
padding: 12px 14px;
border-radius: 11px;
color: #687773;
background: #f4f8f5;
}
.import-guide-strip > span {
display: grid;
width: 24px;
height: 24px;
place-items: center;
border-radius: 8px;
color: var(--green-deep);
background: #dff0e9;
font-size: 10px;
font-weight: 750;
}
.import-guide-strip p {
margin: 0;
font-size: 10px;
white-space: nowrap;
}
.import-guide-strip i {
height: 1px;
background: #d5e0db;
}
.resource-import-dropzone {
position: relative;
display: flex;
min-height: 116px;
align-items: center;
justify-content: center;
flex-direction: column;
gap: 7px;
border: 1px dashed #9dc9b9;
border-radius: 13px;
color: var(--green-deep);
background: #f8fcfa;
cursor: pointer;
}
.resource-import-dropzone:hover {
border-color: var(--green);
background: #f2faf6;
}
.resource-import-dropzone input {
position: absolute;
width: 1px;
height: 1px;
opacity: 0;
pointer-events: none;
}
.resource-import-dropzone strong {
max-width: 88%;
overflow: hidden;
font-size: 13px;
text-overflow: ellipsis;
white-space: nowrap;
}
.resource-import-dropzone span {
color: #8a9893;
font-size: 9px;
}
.import-template-note {
display: flex;
align-items: center;
justify-content: space-between;
gap: 18px;
margin-top: 12px;
padding: 12px 14px;
border: 1px solid #edf0ee;
border-radius: 11px;
}
.import-template-note > div {
display: flex;
flex-direction: column;
gap: 4px;
}
.import-template-note strong {
font-size: 10px;
}
.import-template-note span {
color: #8b9692;
font-size: 9px;
}
.import-template-note a {
flex: 0 0 auto;
text-decoration: none;
}
.resource-import-preview {
margin-top: 14px;
}
.import-summary-grid {
display: grid;
grid-template-columns: repeat(4, 1fr);
gap: 8px;
}
.import-summary-grid > div {
display: flex;
min-height: 58px;
align-items: center;
justify-content: space-between;
padding: 0 14px;
border: 1px solid #e7ece9;
border-radius: 10px;
background: #fafbf9;
}
.import-summary-grid span {
color: #87938f;
font-size: 9px;
}
.import-summary-grid strong {
font-size: 19px;
}
.import-summary-grid .create strong { color: var(--green); }
.import-summary-grid .update strong { color: #4c7890; }
.import-summary-grid .error strong { color: #c86548; }
.import-preview-table {
margin-top: 12px;
overflow: hidden;
border: 1px solid #e8ece9;
border-radius: 11px;
}
.import-preview-row {
display: grid;
grid-template-columns: 48px 1.4fr 1fr 1.15fr 1.15fr;
min-height: 50px;
align-items: center;
border-top: 1px solid #edf0ee;
font-size: 9px;
}
.import-preview-row.head {
min-height: 36px;
border-top: 0;
color: #8c9793;
background: #f6f8f6;
font-weight: 650;
}
.import-preview-row > span {
min-width: 0;
padding: 8px 10px;
overflow-wrap: anywhere;
}
.import-preview-row > span:not(:last-child) {
border-right: 1px solid #f0f2f1;
}
.import-preview-row strong,
.import-preview-row small {
display: block;
}
.import-preview-row small {
margin-top: 3px;
color: #9aa39f;
font-size: 8px;
}
.import-result {
color: #557269;
font-weight: 650;
}
.import-result.create { color: var(--green); }
.import-result.update { color: #47778d; }
.import-result.error { color: #bd644b; font-weight: 520; }
.import-preview-more {
margin: 8px 0 0;
color: #919c98;
font-size: 9px;
text-align: right;
}
.form-error {
margin-top: 12px;
padding: 10px 12px;
border: 1px solid #f0d5cc;
border-radius: 9px;
color: #a95037;
background: #fff7f3;
font-size: 10px;
}
.resource-grid {
display: grid;
grid-template-columns: repeat(3, minmax(0, 1fr));
@@ -1888,6 +2264,41 @@ a {
white-space: nowrap;
}
.recovery-sort-button {
display: inline-flex;
min-width: 100%;
align-items: center;
justify-content: flex-start;
gap: 4px;
padding: 8px 0;
border: 0;
color: inherit;
background: transparent;
font: inherit;
cursor: pointer;
}
.recovery-sort-button b {
color: #aab2af;
font-size: 11px;
line-height: 1;
}
.recovery-sort-button:hover,
.recovery-sort-button.active {
color: #168565;
}
.recovery-sort-button.active b {
color: #168565;
}
.recovery-sort-button:focus-visible {
border-radius: 4px;
outline: 2px solid rgba(22, 133, 101, 0.3);
outline-offset: 2px;
}
.recovery-content {
max-width: 360px;
}
@@ -1905,27 +2316,19 @@ a {
.recovery-title-link {
display: inline-flex;
align-items: center;
gap: 5px;
max-width: 310px;
color: #182822;
color: #168565;
text-decoration: none;
vertical-align: top;
cursor: pointer;
}
.recovery-title-link strong {
min-width: 0;
}
.recovery-title-link::after {
content: "↗";
flex: none;
color: #2b9875;
font-size: 10px;
font-weight: 700;
}
.recovery-title-link:hover strong {
color: #168565;
color: #116d54;
text-decoration: underline;
text-underline-offset: 3px;
}
@@ -2038,15 +2441,178 @@ a {
}
.creator-screenshot-link {
position: relative;
width: 72px;
height: 56px;
padding: 0;
overflow: hidden;
border: 1px solid #dce5e0;
border-radius: 8px;
color: var(--green-deep);
background: #f4f7f5;
font-size: 8px;
font-weight: 700;
text-decoration: none;
cursor: zoom-in;
}
.creator-screenshot-link:hover {
text-decoration: underline;
text-underline-offset: 3px;
border-color: #9fc9b8;
}
.creator-screenshot-link img {
width: 100%;
height: 100%;
display: block;
object-fit: cover;
}
.creator-screenshot-link span {
position: absolute;
right: 4px;
bottom: 4px;
padding: 3px 5px;
border-radius: 5px;
color: white;
background: rgb(17 48 39 / 0.76);
font-size: 7px;
}
.admin-image-lightbox {
position: fixed;
z-index: 2000;
inset: 0;
display: grid;
place-items: center;
padding: 22px;
background: rgb(7 24 19 / 0.78);
}
.admin-image-lightbox-card {
width: min(980px, 100%);
max-height: calc(100vh - 44px);
display: flex;
overflow: hidden;
border: 1px solid rgb(255 255 255 / 0.18);
border-radius: 16px;
background: #f7faf8;
box-shadow: 0 28px 90px rgb(0 0 0 / 0.34);
flex-direction: column;
}
.admin-image-lightbox-heading {
display: flex;
min-height: 52px;
align-items: center;
justify-content: space-between;
gap: 18px;
padding: 10px 16px;
border-bottom: 1px solid #dce5e0;
}
.admin-image-lightbox-heading > div {
display: flex;
min-width: 0;
align-items: center;
gap: 10px;
}
.admin-image-lightbox-heading strong {
overflow: hidden;
color: var(--green-deep);
font-size: 10px;
text-overflow: ellipsis;
white-space: nowrap;
}
.admin-image-lightbox-heading span {
flex: 0 0 auto;
padding: 3px 7px;
border-radius: 999px;
color: #5f746b;
background: #e9f1ed;
font-size: 8px;
font-weight: 700;
}
.admin-image-lightbox-heading button {
flex: 0 0 auto;
height: 32px;
padding: 0 13px;
border: 1px solid #c7d8d0;
border-radius: 8px;
color: var(--green-deep);
background: white;
font-size: 8px;
font-weight: 700;
}
.admin-image-lightbox-stage {
position: relative;
min-height: 0;
display: grid;
flex: 1;
place-items: center;
overflow: hidden;
background: #14231e;
}
.admin-image-lightbox-stage > img {
width: 100%;
height: 100%;
min-height: 0;
max-height: calc(100vh - 108px);
display: block;
object-fit: contain;
}
.admin-image-lightbox-stage > button {
position: absolute;
z-index: 1;
top: 50%;
min-width: 58px;
height: 36px;
padding: 0 10px;
border: 1px solid rgb(255 255 255 / 0.42);
border-radius: 999px;
color: white;
background: rgb(8 31 24 / 0.78);
box-shadow: 0 6px 20px rgb(0 0 0 / 0.24);
font-size: 9px;
font-weight: 700;
transform: translateY(-50%);
transition: background 160ms ease, transform 160ms ease;
}
.admin-image-lightbox-stage > button:hover,
.admin-image-lightbox-stage > button:focus-visible {
background: rgb(23 112 83 / 0.94);
transform: translateY(-50%) scale(1.03);
}
.admin-image-lightbox-stage > button.previous {
left: 14px;
}
.admin-image-lightbox-stage > button.next {
right: 14px;
}
@media (max-width: 640px) {
.admin-image-lightbox-stage > button {
min-width: 52px;
height: 32px;
padding: 0 8px;
font-size: 8px;
}
.admin-image-lightbox-stage > button.previous {
left: 8px;
}
.admin-image-lightbox-stage > button.next {
right: 8px;
}
}
.creator-screenshot-pending,
@@ -3041,6 +3607,81 @@ label small {
color: #b04b40;
}
.eyebrow.warning {
color: #b06d24;
}
.release-distribution-button {
min-height: 30px;
padding: 0 11px;
border: 1px solid #efd9bd;
border-radius: 8px;
color: #95601f;
background: #fffaf2;
font-size: 9px;
font-weight: 650;
white-space: nowrap;
}
.release-distribution-button:hover {
border-color: #dfbd91;
background: #fff5e5;
}
.release-distribution-summary {
display: grid;
gap: 6px;
padding: 13px 14px;
border: 1px solid var(--line);
border-radius: 10px;
background: #f8faf8;
}
.release-distribution-summary strong {
color: var(--ink);
font-size: 11px;
line-height: 1.5;
}
.release-distribution-summary span {
color: #7d8985;
font-size: 9px;
}
.release-distribution-warning {
margin: 0;
padding: 13px 14px;
border: 1px solid #f1dfc8;
border-radius: 10px;
color: #805e35;
background: #fffaf3;
font-size: 10px;
line-height: 1.65;
}
.release-confirm-button {
display: inline-flex;
min-height: 38px;
align-items: center;
justify-content: center;
padding: 0 15px;
border: 1px solid #a56a26;
border-radius: 9px;
color: white;
background: #b9792f;
font-size: 11px;
font-weight: 650;
}
.release-confirm-button:hover {
background: #9d6425;
}
.release-confirm-button:disabled {
cursor: wait;
opacity: 0.62;
}
.user-delete-warning {
margin: 0;
padding: 13px 14px;
@@ -3388,6 +4029,37 @@ label small {
justify-content: space-between;
}
.resource-heading-actions {
width: 100%;
justify-content: flex-start;
}
.resource-heading-actions .filter-chips {
width: 100%;
margin: 4px 0 0;
overflow-x: auto;
}
.import-guide-strip {
grid-template-columns: auto 1fr;
}
.import-guide-strip i {
display: none;
}
.import-summary-grid {
grid-template-columns: repeat(2, 1fr);
}
.import-preview-table {
overflow-x: auto;
}
.import-preview-row {
min-width: 620px;
}
.collection-schedule-panel {
padding: 18px;
}

15
app/layout.tsx
View File

@@ -1,18 +1,7 @@
import type { Metadata } from "next";
import { Geist, Geist_Mono } from "next/font/google";
import { headers } from "next/headers";
import "./globals.css";
const geistSans = Geist({
variable: "--font-geist-sans",
subsets: ["latin"],
});
const geistMono = Geist_Mono({
variable: "--font-geist-mono",
subsets: ["latin"],
});
export async function generateMetadata(): Promise<Metadata> {
const requestHeaders = await headers();
const host = requestHeaders.get("x-forwarded-host") || requestHeaders.get("host");
@@ -60,9 +49,7 @@ export default function RootLayout({
}: Readonly<{ children: React.ReactNode }>) {
return (
<html lang="zh-CN">
<body className={`${geistSans.variable} ${geistMono.variable}`}>
{children}
</body>
<body>{children}</body>
</html>
);
}

45
build/sites-vite-plugin.ts
View File

@@ -1,45 +0,0 @@
import { access, cp, mkdir, rm } from "node:fs/promises";
import { resolve } from "node:path";
import type { Plugin } from "vite";
async function exists(path: string): Promise<boolean> {
try {
await access(path);
return true;
} catch (error) {
if ((error as NodeJS.ErrnoException).code === "ENOENT") {
return false;
}
throw error;
}
}
// Packages Sites metadata and migrations after Vite finishes compiling.
export function sites(): Plugin {
let root = process.cwd();
return {
name: "sites",
apply: "build",
configResolved(config) {
root = config.root;
},
async closeBundle() {
const outputDirectory = resolve(root, "dist", ".openai");
const hostingConfig = resolve(root, ".openai", "hosting.json");
const drizzleSource = resolve(root, "drizzle");
await rm(outputDirectory, { recursive: true, force: true });
await mkdir(outputDirectory, { recursive: true });
if (await exists(hostingConfig)) {
await cp(hostingConfig, resolve(outputDirectory, "hosting.json"));
}
if (await exists(drizzleSource)) {
await cp(drizzleSource, resolve(outputDirectory, "drizzle"), {
recursive: true,
});
}
},
};
}

12
db/index.ts
View File

@@ -1,13 +1,7 @@
import { env } from "cloudflare:workers";
import { drizzle } from "drizzle-orm/d1";
import { drizzle } from "drizzle-orm/mysql2";
import { getPool } from "../lib/database";
import * as schema from "./schema";
export function getDb() {
if (!env.DB) {
throw new Error(
"Cloudflare D1 binding `DB` is unavailable. Set the `d1` field in .openai/hosting.json to `DB` or let your control plane inject the real binding values before using the database."
);
}
return drizzle(env.DB, { schema });
return drizzle({ client: getPool(), schema, mode: "default" });
}

330
db/schema.ts
View File

@@ -1,91 +1,104 @@
import { sql } from "drizzle-orm";
import {
datetime,
index,
integer,
sqliteTable,
int,
mysqlTable,
text,
uniqueIndex,
} from "drizzle-orm/sqlite-core";
varchar,
} from "drizzle-orm/mysql-core";
export const partners = sqliteTable("partners", {
id: text("id").primaryKey(),
name: text("name").notNull(),
wecomName: text("wecom_name").notNull(),
owner: text("owner").notNull().default("运营组"),
claimedTotal: integer("claimed_total").notNull().default(0),
completedTotal: integer("completed_total").notNull().default(0),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
const timestamp = (name: string) =>
datetime(name, { mode: "string", fsp: 3 })
.notNull()
.default(sql`CURRENT_TIMESTAMP(3)`);
export const partners = mysqlTable("partners", {
id: varchar("id", { length: 64 }).primaryKey(),
name: varchar("name", { length: 255 }).notNull(),
wecomName: varchar("wecom_name", { length: 255 }).notNull(),
owner: varchar("owner", { length: 255 }).notNull().default("运营组"),
claimedTotal: int("claimed_total").notNull().default(0),
completedTotal: int("completed_total").notNull().default(0),
createdAt: timestamp("created_at"),
});
export const tasks = sqliteTable(
export const tasks = mysqlTable(
"tasks",
{
id: text("id").primaryKey(),
name: text("name").notNull(),
brand: text("brand").notNull(),
quantity: integer("quantity").notNull(),
claimedQuantity: integer("claimed_quantity").notNull().default(0),
dueAt: text("due_at").notNull(),
status: text("status").notNull().default("active"),
sourceUrl: text("source_url").notNull().default(""),
sourceSheetId: text("source_sheet_id").notNull().default(""),
sourceSheetName: text("source_sheet_name").notNull().default(""),
sourceSyncedAt: text("source_synced_at"),
shareToken: text("share_token"),
collectionStartDate: text("collection_start_date"),
collectionDays: text("collection_days").notNull().default("[]"),
collectionScheduleUpdatedAt: text("collection_schedule_updated_at"),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
id: varchar("id", { length: 64 }).primaryKey(),
name: varchar("name", { length: 255 }).notNull(),
brand: varchar("brand", { length: 255 }).notNull(),
quantity: int("quantity").notNull(),
claimedQuantity: int("claimed_quantity").notNull().default(0),
dueAt: varchar("due_at", { length: 32 }).notNull(),
status: varchar("status", { length: 32 }).notNull().default("active"),
taskType: varchar("task_type", { length: 32 })
.notNull()
.default("content_publish"),
sourceUrl: text("source_url").notNull(),
sourceSheetId: varchar("source_sheet_id", { length: 255 }).notNull().default(""),
sourceSheetName: varchar("source_sheet_name", { length: 255 }).notNull().default(""),
sourceSyncedAt: datetime("source_synced_at", { mode: "string", fsp: 3 }),
shareToken: varchar("share_token", { length: 128 }),
collectionStartDate: varchar("collection_start_date", { length: 32 }),
collectionDays: text("collection_days").notNull(),
collectionScheduleUpdatedAt: datetime("collection_schedule_updated_at", {
mode: "string",
fsp: 3,
}),
createdAt: timestamp("created_at"),
},
(table) => [uniqueIndex("tasks_share_token_idx").on(table.shareToken)],
);
export const contents = sqliteTable("contents", {
id: text("id").primaryKey(),
taskId: text("task_id").notNull(),
export const contents = mysqlTable("contents", {
id: varchar("id", { length: 64 }).primaryKey(),
taskId: varchar("task_id", { length: 64 }).notNull(),
title: text("title").notNull(),
body: text("body").notNull().default(""),
imageAssets: text("image_assets").notNull().default("[]"),
status: text("status").notNull().default("available"),
source: text("source").notNull().default("飞书内容表"),
sourceRow: integer("source_row"),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
body: text("body").notNull(),
imageAssets: text("image_assets").notNull(),
status: varchar("status", { length: 32 }).notNull().default("available"),
source: varchar("source", { length: 255 }).notNull().default("飞书内容表"),
sourceRow: int("source_row"),
createdAt: timestamp("created_at"),
});
export const accounts = sqliteTable(
export const accounts = mysqlTable(
"accounts",
{
id: text("id").primaryKey(),
platform: text("platform").notNull().default("小红书"),
platformUid: text("platform_uid").notNull(),
publicAccountId: text("public_account_id").notNull().default(""),
nickname: text("nickname").notNull(),
profileUrl: text("profile_url").notNull().default(""),
ipLocation: text("ip_location").notNull().default("待识别"),
followers: integer("followers").notNull().default(0),
postCount: integer("post_count").notNull().default(0),
avgViews: integer("avg_views").notNull().default(0),
firstSeenAt: text("first_seen_at").notNull().default(sql`CURRENT_TIMESTAMP`),
lastSeenAt: text("last_seen_at").notNull().default(sql`CURRENT_TIMESTAMP`),
id: varchar("id", { length: 64 }).primaryKey(),
platform: varchar("platform", { length: 32 }).notNull().default("小红书"),
platformUid: varchar("platform_uid", { length: 255 }).notNull(),
publicAccountId: varchar("public_account_id", { length: 255 }).notNull().default(""),
nickname: varchar("nickname", { length: 255 }).notNull(),
profileUrl: text("profile_url").notNull(),
ipLocation: varchar("ip_location", { length: 255 }).notNull().default("待识别"),
followers: int("followers").notNull().default(0),
postCount: int("post_count").notNull().default(0),
avgViews: int("avg_views").notNull().default(0),
cooperationSource: varchar("cooperation_source", { length: 500 })
.notNull()
.default(""),
firstSeenAt: timestamp("first_seen_at"),
lastSeenAt: timestamp("last_seen_at"),
},
(table) => [
uniqueIndex("accounts_platform_uid_idx").on(
table.platform,
table.platformUid,
),
uniqueIndex("accounts_platform_uid_idx").on(table.platform, table.platformUid),
],
);
export const claims = sqliteTable(
export const claims = mysqlTable(
"claims",
{
id: text("id").primaryKey(),
taskId: text("task_id").notNull(),
partnerId: text("partner_id").notNull(),
claimantName: text("claimant_name").notNull(),
claimToken: text("claim_token").notNull(),
quantity: integer("quantity").notNull(),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
id: varchar("id", { length: 64 }).primaryKey(),
taskId: varchar("task_id", { length: 64 }).notNull(),
partnerId: varchar("partner_id", { length: 64 }).notNull(),
claimantName: varchar("claimant_name", { length: 255 }).notNull(),
claimToken: varchar("claim_token", { length: 128 }).notNull(),
quantity: int("quantity").notNull(),
createdAt: timestamp("created_at"),
},
(table) => [
uniqueIndex("claims_claim_token_idx").on(table.claimToken),
@@ -97,123 +110,116 @@ export const claims = sqliteTable(
],
);
export const delegationBundles = sqliteTable(
export const delegationBundles = mysqlTable(
"delegation_bundles",
{
id: text("id").primaryKey(),
taskId: text("task_id").notNull(),
claimId: text("claim_id").notNull(),
partnerId: text("partner_id").notNull(),
label: text("label").notNull(),
shareToken: text("share_token").notNull(),
quantity: integer("quantity").notNull(),
status: text("status").notNull().default("active"),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
revokedAt: text("revoked_at"),
id: varchar("id", { length: 64 }).primaryKey(),
taskId: varchar("task_id", { length: 64 }).notNull(),
claimId: varchar("claim_id", { length: 64 }).notNull(),
partnerId: varchar("partner_id", { length: 64 }).notNull(),
label: varchar("label", { length: 255 }).notNull(),
shareToken: varchar("share_token", { length: 128 }).notNull(),
quantity: int("quantity").notNull(),
status: varchar("status", { length: 32 }).notNull().default("active"),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
revokedAt: datetime("revoked_at", { mode: "string", fsp: 3 }),
},
(table) => [
uniqueIndex("delegation_bundles_share_token_idx").on(table.shareToken),
index("delegation_bundles_claim_created_idx").on(
table.claimId,
table.createdAt,
),
index("delegation_bundles_claim_created_idx").on(table.claimId, table.createdAt),
],
);
export const distributions = sqliteTable("distributions", {
id: text("id").primaryKey(),
taskId: text("task_id").notNull(),
contentId: text("content_id").notNull(),
partnerId: text("partner_id").notNull(),
claimId: text("claim_id"),
delegationBundleId: text("delegation_bundle_id"),
accountId: text("account_id"),
export const distributions = mysqlTable("distributions", {
id: varchar("id", { length: 64 }).primaryKey(),
taskId: varchar("task_id", { length: 64 }).notNull(),
contentId: varchar("content_id", { length: 64 }).notNull(),
partnerId: varchar("partner_id", { length: 64 }).notNull(),
claimId: varchar("claim_id", { length: 64 }),
delegationBundleId: varchar("delegation_bundle_id", { length: 64 }),
accountId: varchar("account_id", { length: 64 }),
publishUrl: text("publish_url"),
publishTime: text("publish_time"),
publishScreenshotKey: text("publish_screenshot_key"),
status: text("status").notNull().default("claimed"),
claimedAt: text("claimed_at").notNull().default(sql`CURRENT_TIMESTAMP`),
screenshotKey: text("screenshot_key"),
ocrStatus: text("ocr_status").notNull().default("none"),
exposure: integer("exposure"),
views: integer("views"),
d2Likes: integer("d2_likes"),
d2Comments: integer("d2_comments"),
d2Collects: integer("d2_collects"),
d5Likes: integer("d5_likes"),
d5Comments: integer("d5_comments"),
d5Collects: integer("d5_collects"),
d7Likes: integer("d7_likes"),
d7Comments: integer("d7_comments"),
d7Collects: integer("d7_collects"),
latestLikes: integer("latest_likes"),
latestComments: integer("latest_comments"),
latestCollects: integer("latest_collects"),
collectionStatus: text("collection_status").notNull().default("pending"),
publishTime: datetime("publish_time", { mode: "string", fsp: 3 }),
publishScreenshotKey: varchar("publish_screenshot_key", { length: 512 }),
resultScreenshotKey: text("result_screenshot_key"),
resultSubmittedAt: datetime("result_submitted_at", { mode: "string", fsp: 3 }),
status: varchar("status", { length: 32 }).notNull().default("claimed"),
claimedAt: timestamp("claimed_at"),
screenshotKey: varchar("screenshot_key", { length: 512 }),
ocrStatus: varchar("ocr_status", { length: 32 }).notNull().default("none"),
exposure: int("exposure"),
views: int("views"),
d2Likes: int("d2_likes"),
d2Comments: int("d2_comments"),
d2Collects: int("d2_collects"),
d5Likes: int("d5_likes"),
d5Comments: int("d5_comments"),
d5Collects: int("d5_collects"),
d7Likes: int("d7_likes"),
d7Comments: int("d7_comments"),
d7Collects: int("d7_collects"),
latestLikes: int("latest_likes"),
latestComments: int("latest_comments"),
latestCollects: int("latest_collects"),
collectionStatus: varchar("collection_status", { length: 32 })
.notNull()
.default("pending"),
collectionStatusDescription: text("collection_status_description"),
collectionUpdatedAt: text("collection_updated_at"),
lastCollectionDay: integer("last_collection_day"),
updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
collectionUpdatedAt: datetime("collection_updated_at", { mode: "string", fsp: 3 }),
lastCollectionDay: int("last_collection_day"),
updatedAt: timestamp("updated_at"),
});
export const collectionRuns = sqliteTable(
export const collectionRuns = mysqlTable(
"collection_runs",
{
id: text("id").primaryKey(),
taskId: text("task_id").notNull(),
distributionId: text("distribution_id").notNull(),
scheduledDate: text("scheduled_date").notNull(),
scheduleDay: integer("schedule_day"),
scheduledAt: text("scheduled_at").notNull(),
status: text("status").notNull().default("pending"),
likes: integer("likes"),
comments: integer("comments"),
collects: integer("collects"),
id: varchar("id", { length: 64 }).primaryKey(),
taskId: varchar("task_id", { length: 64 }).notNull(),
distributionId: varchar("distribution_id", { length: 64 }).notNull(),
scheduledDate: varchar("scheduled_date", { length: 32 }).notNull(),
scheduleDay: int("schedule_day"),
scheduledAt: datetime("scheduled_at", { mode: "string", fsp: 3 }).notNull(),
status: varchar("status", { length: 32 }).notNull().default("pending"),
likes: int("likes"),
comments: int("comments"),
collects: int("collects"),
statusDescription: text("status_description"),
startedAt: text("started_at"),
completedAt: text("completed_at"),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
startedAt: datetime("started_at", { mode: "string", fsp: 3 }),
completedAt: datetime("completed_at", { mode: "string", fsp: 3 }),
createdAt: timestamp("created_at"),
},
(table) => [
uniqueIndex("collection_runs_distribution_date_idx").on(
table.distributionId,
table.scheduledDate,
),
index("collection_runs_task_date_idx").on(
table.taskId,
table.scheduledDate,
),
index("collection_runs_task_date_idx").on(table.taskId, table.scheduledDate),
],
);
export const users = sqliteTable(
export const users = mysqlTable(
"users",
{
id: text("id").primaryKey(),
username: text("username").notNull(),
passwordHash: text("password_hash").notNull(),
passwordSalt: text("password_salt").notNull(),
passwordIterations: integer("password_iterations").notNull(),
role: text("role").notNull(),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
id: varchar("id", { length: 64 }).primaryKey(),
username: varchar("username", { length: 255 }).notNull(),
passwordHash: varchar("password_hash", { length: 255 }).notNull(),
passwordSalt: varchar("password_salt", { length: 255 }).notNull(),
passwordIterations: int("password_iterations").notNull(),
role: varchar("role", { length: 32 }).notNull(),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
},
(table) => [
uniqueIndex("users_username_idx").on(table.username),
uniqueIndex("users_single_super_admin_idx")
.on(table.role)
.where(sql`${table.role} = 'super_admin'`),
],
(table) => [uniqueIndex("users_username_idx").on(table.username)],
);
export const authSessions = sqliteTable(
export const authSessions = mysqlTable(
"auth_sessions",
{
tokenHash: text("token_hash").primaryKey(),
userId: text("user_id").notNull(),
expiresAt: text("expires_at").notNull(),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
tokenHash: varchar("token_hash", { length: 128 }).primaryKey(),
userId: varchar("user_id", { length: 64 }).notNull(),
expiresAt: datetime("expires_at", { mode: "string", fsp: 3 }).notNull(),
createdAt: timestamp("created_at"),
},
(table) => [
index("auth_sessions_user_id_idx").on(table.userId),
@@ -221,14 +227,32 @@ export const authSessions = sqliteTable(
],
);
export const mcpExportTokens = sqliteTable(
export const mcpExportTokens = mysqlTable(
"mcp_export_tokens",
{
tokenHash: text("token_hash").primaryKey(),
kind: text("kind").notNull(),
tokenHash: varchar("token_hash", { length: 128 }).primaryKey(),
kind: varchar("kind", { length: 64 }).notNull(),
payload: text("payload").notNull(),
expiresAt: text("expires_at").notNull(),
createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
expiresAt: datetime("expires_at", { mode: "string", fsp: 3 }).notNull(),
createdAt: timestamp("created_at"),
},
(table) => [index("mcp_export_tokens_expires_at_idx").on(table.expiresAt)],
);
export const backgroundJobs = mysqlTable(
"background_jobs",
{
id: varchar("id", { length: 64 }).primaryKey(),
type: varchar("type", { length: 64 }).notNull(),
payload: text("payload").notNull(),
status: varchar("status", { length: 32 }).notNull().default("pending"),
attempts: int("attempts").notNull().default(0),
availableAt: datetime("available_at", { mode: "string", fsp: 3 }).notNull(),
lockedAt: datetime("locked_at", { mode: "string", fsp: 3 }),
completedAt: datetime("completed_at", { mode: "string", fsp: 3 }),
lastError: text("last_error"),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
},
(table) => [index("background_jobs_pending_idx").on(table.status, table.availableAt)],
);

11
deploy/nginx/Dockerfile Normal file
View File

@@ -0,0 +1,11 @@
FROM node:22.18.0-bookworm-slim AS portal-builder
WORKDIR /portal
ENV NEXT_TELEMETRY_DISABLED=1
COPY koc-portal/package.json koc-portal/package-lock.json ./
RUN npm ci
COPY koc-portal/ ./
RUN npm run build
FROM nginx:1.27-alpine
COPY deploy/nginx/koc-loop.conf /etc/nginx/conf.d/default.conf
COPY --from=portal-builder /portal/out /usr/share/nginx/html/koc

39
deploy/nginx/koc-loop.conf Normal file
View File

@@ -0,0 +1,39 @@
server {
listen 80;
server_name _;
client_max_body_size 10m;
location = /koc {
return 301 /koc/$is_args$args;
}
location /koc/ {
alias /usr/share/nginx/html/koc/;
try_files $uri $uri/ /koc/index.html;
add_header X-Robots-Tag "noindex, nofollow, noarchive" always;
add_header Referrer-Policy "no-referrer" always;
}
location /api/mcp {
proxy_pass http://app:3000;
proxy_http_version 1.1;
proxy_buffering off;
proxy_read_timeout 3600s;
proxy_send_timeout 3600s;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
}
location / {
proxy_pass http://app:3000;
proxy_http_version 1.1;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_read_timeout 180s;
}
}

69
design-qa.md
View File

@@ -1,50 +1,47 @@
# KOC LOOP 用户管理页设计 QA
# KOC LOOP 数据回收标题跳转设计 QA
- Source visual truth: `/var/folders/0g/8yrmts9s7v5g_xc60sxx0__80000gn/T/codex-clipboard-d06a2293-9e7d-4471-b4ae-5eee942b68e9.png`
- Implementation screenshot: `/private/tmp/koc-user-management-final.jpg`
- Delete confirmation screenshot: `/private/tmp/koc-user-delete-modal.jpg`
- Viewport: desktop `1280 × 720` CSS px; responsive check `680 × 900` CSS px
- Pixels and density: source `2738 × 1382`; implementation `1280 × 720`; browser reported `devicePixelRatio = 2`; comparison used the visible layout and computed CSS geometry rather than pixel-perfect scaling because the requested result intentionally changes the source from side-by-side to stacked sections.
- State: logged-in super administrator on the user-management screen
## 验证对象
## Full-view comparison evidence
- 用户参考截图：`/var/folders/0g/8yrmts9s7v5g_xc60sxx0__80000gn/T/codex-clipboard-2296f03e-e79b-494c-b745-08e939fc20c6.png`
- 浏览器实现截图：`/private/tmp/koc-loop-recovery-title-link-focused.png`
- 参考与实现并排对照：`/private/tmp/koc-loop-recovery-title-link-comparison.png`
- 本地页面：`http://localhost:8080/`
The source places account creation and the account list side by side, forcing a tall narrow form and compressing the list. The implementation intentionally stacks the sections: the account-creation card spans the page and uses one compact horizontal row on desktop; the account list spans the full row below it. Existing KOC LOOP navigation, typography, colors, border treatment, and panel radius remain unchanged.
## 环境与状态
## Focused-region comparison evidence
- CSS 视口：1278 × 692，桌面布局
- 用户参考截图：2556 × 1384，按 2:1 密度归一为 1278 × 692
- 实现截图：1278 × 692
- 页面状态：数据回收 → 美团医美-备婚 → 数据回收队列
- 交互状态：已回填短链的第一条标题获得键盘焦点；两条未回填记录保持普通文本
The delete confirmation modal was checked separately. It uses the existing modal shell, a restrained destructive color, explicit irreversible-action copy, cancel and confirm actions, and a disabled working state. No new image assets are present on this screen.
## 完整画面对比
## Required fidelity surfaces
- 信息架构、侧栏、采集计划和数据回收表格继续沿用现有界面，没有新增路由或改变表格列宽。
- 已回填标题使用现有绿色交互色，聚焦时显示清晰但克制的描边；未回填标题仍为黑色普通文本。
- 参考图中的目标区域与实现截图在同一张并排对照图中检查，未发现遮挡、换行异常或列错位。
- Fonts and typography: existing product font stack, heading hierarchy, weights, and field labels are preserved; passed.
- Spacing and layout rhythm: panel padding, 16 px vertical section gap, 12 px form gap, and 14 px table rows establish a clearer rhythm; passed after responsive overflow fix.
- Colors and visual tokens: existing green, canvas, line, and panel tokens are preserved; destructive actions use a muted red semantic treatment; passed.
- Image quality and asset fidelity: this screen has no content imagery or custom visual assets; not applicable.
- Copy and content: creation, reset, and deletion copy is concise; deletion clearly states immediate sign-out and irreversibility; passed.
## 聚焦区域检查
## Interaction verification
- 字体与层级：标题字号、字重和省略规则保持不变，仅为可点击标题增加语义色和 hover/focus 状态。
- 间距与布局：链接仍受原有 310px 最大宽度约束，头像、账号副标题和相邻数据列未发生位移。
- 颜色与状态：绿色与现有按钮、成功状态色一致；键盘焦点轮廓可见。
- 图片质量：创作者截图缩略图未受本次改动影响，仍保持原比例显示。
- 文案内容：标题原文、账号名称、平台信息和采集数据均保持不变。
- Created a local ordinary test account.
- Opened the row-level delete confirmation.
- Confirmed deletion and verified the row disappeared.
- Verified the current super-administrator row has no delete action.
- Verified browser console errors: none.
- Verified desktop page horizontal overflow: none (`scrollWidth = innerWidth = 1280`).
- Verified 680 px responsive page horizontal overflow: none (`scrollWidth = innerWidth = 680`); the table scrolls inside its own container.
## 功能验证
## Comparison history
- 当前任务中识别到 1 条可点击标题，`href` 为已回填的 `http://xhslink.cn/o/9qOYiD3Iu8K`，`target=_blank`。
- 点击标题后短链成功跳转并解析为小红书笔记详情页。
- 另外 2 条没有发布链接的标题不是链接，避免误导点击。
- 链接仅允许小红书正式域名和 `xhslink.cn` / `xhslink.com` 短链域名，其他协议或域名不会渲染为链接。
- 后台浏览器控制台无 warning/error。
- Docker 生产构建通过；相关静态验收测试 12 项全部通过。
1. Initial responsive pass found the account table's minimum width expanding the parent grid at 680 px.
2. Added `min-width: 0` to the stacked layout and panels, and constrained the table to its panel.
3. Post-fix evidence: page `scrollWidth` reduced from `714` to `680`, matching the viewport; the table retains an internal `660` px scroll surface.
## 迭代记录
## Findings
No actionable P0, P1, or P2 issues remain.
## Follow-up polish
No blocking polish items. A future iteration may add search when the account count grows substantially.
1. 初次浏览器验收发现第一条发布链接为 `xhslink.cn`，而前端白名单只包含 `xhslink.com`，因此标题仍是普通文本。
2. 补充 `xhslink.cn` 及其子域名白名单，保留 HTTP/HTTPS 与小红书域名边界。
3. 重新构建后，浏览器识别到 1 条可点击标题；点击实际打开对应小红书页面，未回填行仍不可点击。
final result: passed

74
docker-compose.self-hosted.yml Normal file
View File

@@ -0,0 +1,74 @@
services:
mysql:
image: mysql:8.4
restart: unless-stopped
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?请在环境文件中设置 MYSQL_ROOT_PASSWORD}
MYSQL_DATABASE: ${MYSQL_DATABASE:-koc_loop}
MYSQL_USER: ${MYSQL_USER:-koc}
MYSQL_PASSWORD: ${MYSQL_PASSWORD:?请在环境文件中设置 MYSQL_PASSWORD}
TZ: Asia/Shanghai
command:
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_0900_ai_ci
- --default-time-zone=+00:00
volumes:
- mysql_data:/var/lib/mysql
healthcheck:
test: ["CMD-SHELL", "mysqladmin ping -h 127.0.0.1 -u root -p$$MYSQL_ROOT_PASSWORD --silent"]
interval: 10s
timeout: 5s
retries: 20
start_period: 30s
app:
build:
context: .
dockerfile: Dockerfile
restart: unless-stopped
environment:
MYSQL_HOST: mysql
MYSQL_PORT: 3306
MYSQL_DATABASE: ${MYSQL_DATABASE:-koc_loop}
MYSQL_USER: ${MYSQL_USER:-koc}
MYSQL_PASSWORD: ${MYSQL_PASSWORD}
UPLOAD_DIR: /data/koc/uploads
APP_ORIGIN: ${APP_ORIGIN}
KOC_PORTAL_URL: ${KOC_PORTAL_URL}
SUPER_ADMIN_USERNAME: ${SUPER_ADMIN_USERNAME}
SUPER_ADMIN_PASSWORD: ${SUPER_ADMIN_PASSWORD}
ADMIN_INTERNAL_TOKEN: ${ADMIN_INTERNAL_TOKEN}
KOC_MCP_API_KEY: ${KOC_MCP_API_KEY}
FEISHU_APP_ID: ${FEISHU_APP_ID:-}
FEISHU_APP_SECRET: ${FEISHU_APP_SECRET:-}
AI_TOOL_CENTER_MCP_URL: ${AI_TOOL_CENTER_MCP_URL:-}
AI_TOOL_CENTER_MCP_KEY: ${AI_TOOL_CENTER_MCP_KEY:-}
ENABLE_SCHEDULER: ${ENABLE_SCHEDULER:-true}
SEED_DEMO_DATA: ${SEED_DEMO_DATA:-false}
TZ: Asia/Shanghai
volumes:
- upload_data:/data/koc/uploads
depends_on:
mysql:
condition: service_healthy
healthcheck:
test: ["CMD-SHELL", "node -e \"fetch('http://127.0.0.1:3000/api/health').then(r=>{if(!r.ok)process.exit(1)}).catch(()=>process.exit(1))\""]
interval: 15s
timeout: 5s
retries: 10
start_period: 45s
nginx:
build:
context: .
dockerfile: deploy/nginx/Dockerfile
restart: unless-stopped
ports:
- "${HTTP_PORT:-80}:80"
depends_on:
app:
condition: service_healthy
volumes:
mysql_data:
upload_data:

196
docs/KOC LOOP 私有化部署指南.md Normal file
View File

@@ -0,0 +1,196 @@
# KOC LOOP 私有化部署指南
本文适用于 `codex/self-hosted-mysql` 分支。目标架构是运维提出的：Nginx 代理 + KOC 服务 + MySQL 数据库，并让后台、外部 KOC 领取页和 Agent MCP 都能通过一个公网域名访问。
## 1. 部署形态
| 组件 | 容器 | 作用 | 持久化 |
| --- | --- | --- | --- |
| Nginx | `nginx` | 公网入口、反向代理、托管 KOC 领取页 | 配置随镜像 |
| KOC 服务 | `app` | Next.js 后台、API、MCP、每天 09:00 自动采集 | 上传目录挂载卷 |
| MySQL 8 | `mysql` | 任务、笔记、领取、回填、账号、采集和用户数据 | MySQL 数据卷 |
访问路径：
- `https://你的域名/`：运营后台；
- `https://你的域名/koc/`：外部 KOC 领取和回填；
- `https://你的域名/api/mcp`：Agent MCP；
- `https://你的域名/api/health`：服务健康检查。
第一版按单实例 KOC 服务设计。MySQL 和上传目录均持久化。以后需要横向扩容时，可以把上传卷换成共享 NAS；对象存储接口已与业务代码分离。
## 2. 服务器要求
- Linux 服务器一台，建议至少 4 核、8 GB 内存、100 GB 数据盘；
- Docker Engine 24+；
- Docker Compose v2；
- 可解析到服务器或负载均衡的公网域名；
- HTTPS 证书由公司网关、负载均衡或 Nginx 统一终止；
- 服务器可以访问飞书 OpenAPI 和正式数据采集 MCP。
服务器只需对公网开放 80/443。MySQL 不映射公网端口。
## 3. 准备代码与环境变量
```bash
git clone ssh://git@gta.gbotai.cn:42001/wufengping/koc-loop.git
cd koc-loop
git checkout codex/self-hosted-mysql
cp .env.self-hosted.example .env.self-hosted
```
编辑 `.env.self-hosted`。必须替换所有 `replace-with-*` 占位值：
| 变量 | 用途 |
| --- | --- |
| `MYSQL_ROOT_PASSWORD` | MySQL root 密码，仅数据库容器使用 |
| `MYSQL_PASSWORD` | KOC 服务的数据库密码 |
| `APP_ORIGIN` | 后台公网地址，如 `https://koc.example.com` |
| `KOC_PORTAL_URL` | 领取页完整地址，如 `https://koc.example.com/koc/` |
| `SUPER_ADMIN_USERNAME` | 首次启动创建唯一超级管理员 |
| `SUPER_ADMIN_PASSWORD` | 首次启动的超级管理员初始密码，至少 8 位 |
| `ADMIN_INTERNAL_TOKEN` | 内部管理调用密钥 |
| `KOC_MCP_API_KEY` | Agent 调用 KOC LOOP MCP 的独立 Bearer 密钥 |
| `FEISHU_APP_ID` / `FEISHU_APP_SECRET` | 读取飞书内容表和配图 |
| `AI_TOOL_CENTER_MCP_URL` / `AI_TOOL_CENTER_MCP_KEY` | 小红书公开数据采集服务 |
| `ENABLE_SCHEDULER` | 是否启用每天 09:00 自动采集，生产保持 `true` |
密钥必须由密码管理器生成，禁止提交到 Git、聊天、部署日志或 URL。三个业务密钥 `ADMIN_INTERNAL_TOKEN`、`KOC_MCP_API_KEY`、`AI_TOOL_CENTER_MCP_KEY` 不得复用。
## 4. 首次启动
```bash
docker compose --env-file .env.self-hosted \
-f docker-compose.self-hosted.yml up -d --build
```
应用容器启动时会先执行同一套数据库迁移脚本，成功后才启动 KOC 服务。查看状态：
```bash
docker compose --env-file .env.self-hosted \
-f docker-compose.self-hosted.yml ps
curl -fsS http://127.0.0.1:${HTTP_PORT:-80}/api/health
```
健康检查应返回 `status: ok` 和 `database: true`。
首次打开后台登录页时，系统会根据环境变量创建唯一超级管理员。创建成功后，可从运行环境移除 `SUPER_ADMIN_PASSWORD` 的明文值并重启应用；后续账号与密码统一在“用户管理”中维护。
## 5. Nginx 与 HTTPS
仓库内 `deploy/nginx/koc-loop.conf` 默认监听容器 80 端口，适合前置公司网关或负载均衡终止 HTTPS。
如果证书直接挂在本机 Nginx：
1. 把证书和私钥以只读卷挂载进 `nginx` 容器；
2. 增加 443 `listen ... ssl` 配置；
3. 80 端口只做 301 跳转；
4. 保留 `/koc/` 静态规则、`/api/mcp` 长连接规则和 `/` 反向代理规则。
MCP 路由已经关闭代理缓冲并将超时时间延长到 1 小时，避免 Agent 的长调用被 Nginx 提前截断。
## 6. 迁移原 Sites 数据
迁移分为数据库与图片两部分。先在原生产站点保持只读窗口，完成导出后再切换域名，避免新旧系统同时写入。
### 6.1 D1 数据导入 MySQL
把 D1 各表导出成一个 JSON 文件，结构如下：
```json
{
"tables": {
"partners": [{ "id": "...", "name": "..." }],
"tasks": [{ "id": "...", "name": "..." }],
"contents": [],
"accounts": [],
"claims": [],
"delegation_bundles": [],
"distributions": [],
"collection_runs": [],
"users": [],
"auth_sessions": [],
"mcp_export_tokens": []
}
}
```
先确保 MySQL 迁移已完成，再在应用环境中运行：
```bash
npm run db:import-json -- /backup/koc-d1-export.json
```
导入脚本按业务依赖顺序写入，并使用主键/唯一键安全更新已有记录。正式迁移前先在测试库演练并核对任务数、笔记数、领取数、发布数和账号数。
### 6.2 R2 图片导入
把 R2 按原对象 key 导出到一个目录，目录层级必须保留，例如：
```text
content-assets/...
publish-evidence/...
creator-center/...
```
运行：
```bash
UPLOAD_DIR=/data/koc/uploads \
npm run storage:import -- /backup/koc-r2-export
```
脚本会复制文件，并为缺少元数据的图片生成 Content-Type 元数据。容器部署时也可以在宿主机临时挂载 `upload_data` 卷后执行。
## 7. 上线验收
必须逐项验证：
1. 超级管理员可以登录，普通用户看不到 KOC 资源模块；
2. 用真实飞书表格创建一个 1 篇测试任务；
3. 返回的领取链接以 `/koc/?task=` 开头；
4. 手机公网打开领取页，能查看正文与配图；
5. 回填短链/长链、上传发布截图、刷新后记录仍在；
6. 上传创作者截图并填写曝光量、阅读量；
7. 后台立即采集一篇笔记成功；
8. 保存次日采集计划，确认数据库产生 `collection_runs`；
9. 导出的 Excel 内能直接看到原图和截图；
10. Agent 用 `KOC_MCP_API_KEY` 调用 `/api/mcp` 能发现全部工具；
11. 重启全部容器后数据与图片不丢失。
## 8. 备份与恢复
每天至少备份：
- MySQL：`mysqldump --single-transaction`；
- `mysql_data` 卷；
- `upload_data` 卷；
- 当前 Git 提交号和脱敏后的环境变量清单。
备份必须复制到另一台机器或对象存储，不能只保存在部署服务器。恢复演练至少每季度一次。
## 9. 升级与回滚
升级前先备份数据库和上传卷：
```bash
git pull
docker compose --env-file .env.self-hosted \
-f docker-compose.self-hosted.yml up -d --build
```
数据库迁移只允许向前追加新的 `mysql/*.sql` 文件，禁止修改已经在生产执行过的迁移。应用回滚到旧镜像前，要确认旧代码兼容当前数据库结构；涉及不可逆结构变化时，必须同时准备数据库恢复方案。
## 10. 运维排查
| 现象 | 处理 |
| --- | --- |
| `/api/health` 返回 503 | 查看 MySQL 容器健康状态与应用数据库变量 |
| 登录页可开但登录失败 | 确认迁移完成、超级管理员变量仅用于初始化 |
| 配图或截图 404 | 检查 `upload_data` 卷和 `UPLOAD_DIR=/data/koc/uploads` |
| 09:00 未自动采集 | 检查 `ENABLE_SCHEDULER=true`、服务器日志和采集 MCP 网络 |
| MCP 401 | 检查请求头是否为 `Authorization: Bearer <KOC_MCP_API_KEY>` |
| 飞书读取失败 | 检查应用权限、文档授权和服务器到飞书 OpenAPI 的网络 |
生产日志不得打印数据库密码、飞书 Secret、MCP key 或完整带 key 的采集服务 URL。

4
docs/KOC LOOP 部署指南.md
View File

@@ -237,10 +237,10 @@ npm run db:generate
运营后台 Worker 配置了 Cloudflare Cron：
```text
0 2 * * *
0 1 * * *
```
Cloudflare Cron 使用 UTC，`02:00 UTC` 对应北京时间每天 `10:00`。定时任务会：
Cloudflare Cron 使用 UTC，`01:00 UTC` 对应北京时间每天 `09:00`。定时任务会：
1. 确认数据库结构；
2. 执行当天已创建的笔记数据采集任务；

5
drizzle.config.ts
View File

@@ -3,5 +3,8 @@ import { defineConfig } from "drizzle-kit";
export default defineConfig({
out: "./drizzle",
schema: "./db/schema.ts",
dialect: "sqlite",
dialect: "mysql",
dbCredentials: {
url: process.env.DATABASE_URL ?? "mysql://koc:koc@127.0.0.1:3306/koc_loop",
},
});

5
instrumentation.ts Normal file
View File

@@ -0,0 +1,5 @@
export async function register() {
if (process.env.NEXT_RUNTIME !== "nodejs") return;
const { startScheduler } = await import("./lib/scheduler");
startScheduler();
}

351
koc-portal/app/globals.css
View File

@@ -1,6 +1,7 @@
@import "tailwindcss";
:root {
--font-geist-sans: -apple-system, BlinkMacSystemFont, "Segoe UI", "PingFang SC", "Microsoft YaHei", sans-serif;
--ink: #15221f;
--ink-soft: #354640;
--green: #1e8d68;
@@ -173,6 +174,140 @@ button:disabled {
box-shadow: 0 1px 2px rgb(15 31 26 / 0.03);
}
.screenshot-task-detail .document-label:first-of-type {
margin-top: 26px;
}
.screenshot-task-detail .note-title-row h1 {
color: var(--green-deep);
}
.task-result-picker {
min-height: 160px;
}
.task-result-label-row {
display: flex;
align-items: center;
justify-content: space-between;
gap: 12px;
}
.task-result-label-row small {
color: #7f8b87;
font-size: 8px;
font-weight: 500;
}
.task-result-gallery {
display: grid;
grid-template-columns: repeat(3, minmax(0, 1fr));
gap: 9px;
margin-top: 8px;
}
.task-result-thumb,
.task-result-add {
position: relative;
min-height: 112px;
overflow: hidden;
border: 1px solid #d8e2dd;
border-radius: 11px;
background: #f6f9f7;
}
.task-result-thumb {
display: block;
width: 100%;
padding: 0;
border: 1px solid #d8e2dd;
color: inherit;
cursor: zoom-in;
}
.task-result-thumb img {
width: 100%;
height: 112px;
display: block;
object-fit: cover;
}
.task-result-thumb > span {
position: absolute;
right: 6px;
bottom: 6px;
padding: 4px 6px;
border-radius: 7px;
color: #267257;
background: rgb(238 249 244 / 0.94);
font-size: 7px;
font-weight: 700;
}
.task-result-thumb.pending-upload > span {
color: #8a643f;
background: rgb(255 245 233 / 0.94);
}
.task-result-thumb button {
position: absolute;
top: 6px;
right: 6px;
display: grid;
width: 24px;
height: 24px;
place-items: center;
padding: 0;
border: 0;
border-radius: 50%;
color: white;
background: rgb(17 48 39 / 0.78);
font-size: 15px;
cursor: pointer;
}
.task-result-add {
display: flex;
align-items: center;
justify-content: center;
flex-direction: column;
border-style: dashed;
cursor: pointer;
}
.task-result-add input {
position: absolute;
inset: 0;
width: 100%;
height: 100%;
opacity: 0;
cursor: pointer;
}
.task-result-add b {
display: grid;
width: 30px;
height: 30px;
place-items: center;
border-radius: 50%;
color: var(--green);
background: var(--green-soft);
font-size: 16px;
}
.task-result-add strong {
margin-top: 8px;
font-size: 8px;
}
.task-result-hint {
display: block;
margin-top: 8px;
color: #8c9793;
font-size: 7px;
line-height: 1.55;
}
.claim-card {
min-height: 430px;
padding: 28px;
@@ -915,6 +1050,11 @@ footer {
padding: 34px 38px;
}
.mobile-note-summary,
.mobile-note-collapse-trigger {
display: none;
}
.note-document-meta {
display: flex;
gap: 8px;
@@ -1171,6 +1311,19 @@ footer {
margin-top: 18px;
}
.evidence-field {
display: block;
margin-top: 18px;
}
.evidence-field > span {
display: block;
margin-bottom: 7px;
color: #52605b;
font-size: 9px;
font-weight: 700;
}
.field-hint {
color: #98a29e;
font-size: 8px;
@@ -1286,6 +1439,142 @@ footer {
min-height: 96px;
}
.screenshot-picker.preview-mode {
align-items: stretch;
justify-content: flex-start;
padding: 8px;
background: #f7faf8;
}
.evidence-empty-state {
display: flex;
min-height: 82px;
align-items: center;
justify-content: center;
flex-direction: column;
}
.evidence-preview-button {
position: relative;
width: 100%;
padding: 0;
overflow: hidden;
border: 0;
border-radius: 9px;
background: #eaf1ed;
cursor: zoom-in;
}
.evidence-preview-button img {
width: 100%;
max-height: 260px;
display: block;
object-fit: contain;
}
.evidence-preview-button.compact-preview img {
max-height: 210px;
}
.evidence-preview-button > span {
position: absolute;
right: 8px;
bottom: 8px;
padding: 5px 8px;
border-radius: 8px;
color: white;
background: rgb(17 48 39 / 0.78);
font-size: 7px;
font-weight: 700;
}
.evidence-upload-action {
position: relative;
width: 100%;
height: 34px;
display: grid;
margin-top: 8px !important;
place-items: center;
overflow: hidden;
border: 1px solid #c5dcd2;
border-radius: 8px;
color: var(--green-deep);
background: #eef7f3;
font-size: 8px;
font-weight: 700;
cursor: pointer;
}
.evidence-upload-action input {
position: absolute;
inset: 0;
width: 100%;
height: 100%;
opacity: 0;
cursor: pointer;
}
.image-lightbox {
position: fixed;
z-index: 1000;
inset: 0;
display: grid;
place-items: center;
padding: 18px;
background: rgb(9 25 20 / 0.78);
}
.image-lightbox-card {
width: min(920px, 100%);
max-height: calc(100vh - 36px);
display: flex;
overflow: hidden;
border: 1px solid rgb(255 255 255 / 0.2);
border-radius: 16px;
background: #f7faf8;
box-shadow: 0 24px 80px rgb(0 0 0 / 0.32);
flex-direction: column;
}
.image-lightbox-heading {
display: flex;
min-height: 48px;
align-items: center;
justify-content: space-between;
gap: 16px;
padding: 10px 14px;
border-bottom: 1px solid #dce5e0;
}
.image-lightbox-heading strong {
overflow: hidden;
color: var(--green-deep);
font-size: 10px;
text-overflow: ellipsis;
white-space: nowrap;
}
.image-lightbox-heading button {
flex: 0 0 auto;
height: 30px;
padding: 0 12px;
border: 1px solid #c7d8d0;
border-radius: 8px;
color: var(--green-deep);
background: white;
font-size: 8px;
font-weight: 700;
}
.image-lightbox-card > img {
width: 100%;
min-height: 0;
max-height: calc(100vh - 102px);
display: block;
object-fit: contain;
background: #14231e;
}
.creator-metric-grid {
display: grid;
grid-template-columns: repeat(2, minmax(0, 1fr));
@@ -1318,6 +1607,10 @@ footer {
}
@media (width <= 520px) {
.task-result-gallery {
grid-template-columns: repeat(2, minmax(0, 1fr));
}
.creator-metric-grid {
grid-template-columns: 1fr;
gap: 0;
@@ -1522,6 +1815,64 @@ footer {
border-radius: 16px;
}
.note-document.mobile-collapsed {
padding: 16px;
}
.note-document.mobile-collapsed .note-document-content {
display: none;
}
.note-document.mobile-collapsed .mobile-note-summary {
display: flex;
align-items: center;
justify-content: space-between;
gap: 14px;
}
.mobile-note-summary > div {
display: grid;
min-width: 0;
gap: 4px;
}
.mobile-note-summary span {
color: var(--green);
font-size: 9px;
font-weight: 750;
}
.mobile-note-summary strong {
overflow: hidden;
color: var(--ink);
font-size: 13px;
text-overflow: ellipsis;
white-space: nowrap;
}
.mobile-note-summary small {
color: #899590;
font-size: 8px;
}
.mobile-note-summary button,
.mobile-note-collapse-trigger {
flex: 0 0 auto;
min-height: 34px;
padding: 0 12px;
border: 1px solid #cfe1d9;
border-radius: 9px;
color: var(--green-deep);
background: #f2f8f5;
font-size: 9px;
font-weight: 700;
}
.mobile-note-collapse-trigger {
display: block;
margin: 14px 0 0 auto;
}
.note-document h1 {
font-size: 28px;
}

75
koc-portal/app/layout.tsx
View File

@@ -1,66 +1,23 @@
import type { Metadata } from "next";
import { Geist, Geist_Mono } from "next/font/google";
import { headers } from "next/headers";
import "./globals.css";
const geistSans = Geist({
variable: "--font-geist-sans",
subsets: ["latin"],
});
const description =
"领取KOC内容任务，逐篇查看笔记详情并一一回填发布账号、链接与截图。";
const geistMono = Geist_Mono({
variable: "--font-geist-mono",
subsets: ["latin"],
});
export async function generateMetadata(): Promise<Metadata> {
const requestHeaders = await headers();
const host = requestHeaders.get("x-forwarded-host") || requestHeaders.get("host");
const protocol =
requestHeaders.get("x-forwarded-proto") ||
(host?.startsWith("localhost") ? "http" : "https");
const metadataBase = host
? new URL(`${protocol}://${host}`)
: new URL("https://koc-task.example.com");
const description =
"领取KOC内容任务，逐篇查看笔记详情并一一回填发布账号、链接与截图。";
return {
metadataBase,
export const metadata: Metadata = {
metadataBase: new URL("https://koc-loop.example.com"),
title: "KOC LOOP｜外部任务领取",
description,
robots: { index: false, follow: false, noarchive: true, nosnippet: true },
referrer: "no-referrer",
icons: { icon: "/koc/favicon.svg", shortcut: "/koc/favicon.svg" },
openGraph: {
title: "KOC LOOP｜外部任务领取",
description,
robots: {
index: false,
follow: false,
noarchive: true,
nosnippet: true,
},
referrer: "no-referrer",
icons: {
icon: "/favicon.svg",
shortcut: "/favicon.svg",
},
openGraph: {
title: "KOC LOOP｜外部任务领取",
description,
type: "website",
images: [
{
url: new URL("/og.png", metadataBase).toString(),
width: 1200,
height: 630,
alt: "KOC LOOP 外部任务领取与逐篇回填",
},
],
},
twitter: {
card: "summary_large_image",
title: "KOC LOOP｜外部任务领取",
description,
images: [new URL("/og.png", metadataBase).toString()],
},
};
}
type: "website",
images: [{ url: "/koc/og.png", width: 1200, height: 630 }],
},
};
export default function RootLayout({
children,
@@ -69,9 +26,7 @@ export default function RootLayout({
}>) {
return (
<html lang="zh-CN">
<body className={`${geistSans.variable} ${geistMono.variable}`}>
{children}
</body>
<body>{children}</body>
</html>
);
}

682
koc-portal/app/page.tsx
View File

File diff suppressed because it is too large Load Diff

45
koc-portal/build/sites-vite-plugin.ts
View File

@@ -1,45 +0,0 @@
import { access, cp, mkdir, rm } from "node:fs/promises";
import { resolve } from "node:path";
import type { Plugin } from "vite";
async function exists(path: string): Promise<boolean> {
try {
await access(path);
return true;
} catch (error) {
if ((error as NodeJS.ErrnoException).code === "ENOENT") {
return false;
}
throw error;
}
}
// Packages Sites metadata and migrations after Vite finishes compiling.
export function sites(): Plugin {
let root = process.cwd();
return {
name: "sites",
apply: "build",
configResolved(config) {
root = config.root;
},
async closeBundle() {
const outputDirectory = resolve(root, "dist", ".openai");
const hostingConfig = resolve(root, ".openai", "hosting.json");
const drizzleSource = resolve(root, "drizzle");
await rm(outputDirectory, { recursive: true, force: true });
await mkdir(outputDirectory, { recursive: true });
if (await exists(hostingConfig)) {
await cp(hostingConfig, resolve(outputDirectory, "hosting.json"));
}
if (await exists(drizzleSource)) {
await cp(drizzleSource, resolve(outputDirectory, "drizzle"), {
recursive: true,
});
}
},
};
}

13
koc-portal/db/index.ts
View File

@@ -1,13 +0,0 @@
import { env } from "cloudflare:workers";
import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";
export function getDb() {
if (!env.DB) {
throw new Error(
"Cloudflare D1 binding `DB` is unavailable. Set the `d1` field in .openai/hosting.json to `DB` or let your control plane inject the real binding values before using the database."
);
}
return drizzle(env.DB, { schema });
}

4
koc-portal/db/schema.ts
View File

@@ -1,4 +0,0 @@
// Intentionally empty by default.
// Add Drizzle tables here when the site actually needs a database.
// See examples/d1/db/schema.ts for an opt-in example.
export {};

7
koc-portal/drizzle.config.ts
View File

@@ -1,7 +0,0 @@
import { defineConfig } from "drizzle-kit";
export default defineConfig({
out: "./drizzle",
schema: "./db/schema.ts",
dialect: "sqlite",
});

5
koc-portal/drizzle/meta/_journal.json
View File

@@ -1,5 +0,0 @@
{
"version": "7",
"dialect": "sqlite",
"entries": []
}

8
koc-portal/next.config.ts
View File

@@ -1,7 +1,13 @@
import type { NextConfig } from "next";
import path from "node:path";
const nextConfig: NextConfig = {
/* config options here */
output: "export",
basePath: "/koc",
assetPrefix: "/koc",
trailingSlash: true,
images: { unoptimized: true },
turbopack: { root: path.resolve(process.cwd()) },
};
export default nextConfig;

4959
koc-portal/package-lock.json generated
View File

File diff suppressed because it is too large Load Diff

22
koc-portal/package.json
View File

@@ -6,37 +6,27 @@
"node": ">=22.13.0"
},
"scripts": {
"dev": "WRANGLER_LOG_PATH=.wrangler/wrangler.log vinext dev",
"build": "WRANGLER_LOG_PATH=.wrangler/wrangler.log vinext build",
"start": "WRANGLER_LOG_PATH=.wrangler/wrangler.log vinext start",
"dev": "next dev -p 3001",
"build": "next build",
"start": "npx serve out -l 3001",
"test": "npm run build && node --test tests/rendered-html.test.mjs",
"lint": "eslint . --ignore-pattern dist --ignore-pattern .next",
"db:generate": "drizzle-kit generate"
"lint": "eslint . --ignore-pattern dist --ignore-pattern .next --ignore-pattern out"
},
"dependencies": {
"drizzle-orm": "0.45.2",
"fflate": "0.7.4",
"next": "16.2.6",
"next": "^16.3.0",
"react": "19.2.6",
"react-dom": "19.2.6"
},
"devDependencies": {
"@cloudflare/vite-plugin": "1.37.1",
"@tailwindcss/postcss": "4.2.1",
"@types/node": "22.19.19",
"@types/react": "19.2.14",
"@types/react-dom": "19.2.3",
"@vitejs/plugin-react": "6.0.2",
"@vitejs/plugin-rsc": "0.5.26",
"drizzle-kit": "0.31.10",
"eslint": "9.39.4",
"eslint-config-next": "16.2.6",
"react-server-dom-webpack": "19.2.6",
"tailwindcss": "4.2.1",
"typescript": "5.9.3",
"vinext": "0.0.50",
"vite": "8.0.13",
"wrangler": "4.92.0"
"typescript": "5.9.3"
},
"type": "module"
}

56
koc-portal/tests/rendered-html.test.mjs
View File

@@ -14,16 +14,17 @@ test("builds the branded external task shell", async () => {
assert.match(layout, /KOC LOOP｜外部任务领取/);
assert.match(layout, /og\.png/);
assert.match(page, /正在打开任务/);
await access(new URL("../dist/server/index.js", import.meta.url));
await access(new URL("../out/index.html", import.meta.url));
});
test("keeps claiming minimal and backfill one-to-one", async () => {
const [page, layout, packageJson, hosting] =
const [page, layout, packageJson, nextConfig, styles] =
await Promise.all([
readFile(new URL("../app/page.tsx", import.meta.url), "utf8"),
readFile(new URL("../app/layout.tsx", import.meta.url), "utf8"),
readFile(new URL("../package.json", import.meta.url), "utf8"),
readFile(new URL("../.openai/hosting.json", import.meta.url), "utf8"),
readFile(new URL("../next.config.ts", import.meta.url), "utf8"),
readFile(new URL("../app/globals.css", import.meta.url), "utf8"),
]);
assert.match(page, /微信号\s*\/\s*手机号/);
@@ -53,9 +54,17 @@ test("keeps claiming minimal and backfill one-to-one", async () => {
assert.match(page, /action:\s*"recover"/);
assert.match(page, /同一任务多次领取会分批展示/);
assert.match(page, /这篇笔记已回填，不会与其他笔记错配/);
assert.match(page, /笔记内容已收起/);
assert.match(page, /展开笔记内容/);
assert.match(page, /收起笔记内容/);
assert.match(page, /isFirstBackfill/);
assert.match(page, /scrollIntoView/);
assert.match(page, /matchMedia\("\(max-width: 620px\)"\)/);
assert.match(styles, /\.note-document\.mobile-collapsed/);
assert.doesNotMatch(page, /批量回填/);
assert.doesNotMatch(page, /复制标题和正文/);
assert.match(page, /PRODUCTION_ADMIN_ORIGIN/);
assert.match(page, /CONFIGURED_ADMIN_ORIGIN/);
assert.match(page, /window\.location\.origin/);
assert.match(page, /\/api\/partner-upload/);
assert.match(page, /"X-KOC-Distribution":\s*selectedItem\.id/);
assert.match(page, /"X-KOC-Upload-Kind":\s*kind/);
@@ -65,22 +74,50 @@ test("keeps claiming minimal and backfill one-to-one", async () => {
assert.match(page, /creatorExposure/);
assert.match(page, /creatorViews/);
assert.match(page, /截图仅用于运营核对，不再自动OCR/);
assert.match(page, /evidenceImageUrl/);
assert.match(page, /evidenceImageUrl\(selected, "publish"\)/);
assert.match(page, /evidenceImageUrl\(selected, "creator"\)/);
assert.match(page, /ImageLightbox/);
assert.match(page, /点击查看大图/);
assert.match(page, /publishScreenshotPreview/);
assert.match(page, /creatorScreenshotPreview/);
assert.match(page, /曝光量/);
assert.match(page, /阅读量/);
assert.doesNotMatch(page, /recognizeCreatorMetrics/);
assert.match(page, /note-index \$\{item\.publish_url \? "done" : ""\}/);
assert.match(page, /note-index \$\{\(isScreenshotTask \? item\.result_submitted_at : item\.publish_url\) \? "done" : ""\}/);
assert.match(packageJson, /"fflate":\s*"0\.7\.4"/);
assert.doesNotMatch(packageJson, /tesseract\.js/);
assert.match(layout, /逐篇查看笔记详情并一一回填/);
assert.doesNotMatch(packageJson, /react-loading-skeleton/);
const hostingConfig = JSON.parse(hosting);
assert.equal(hostingConfig.d1, null);
assert.equal(hostingConfig.r2, null);
assert.match(nextConfig, /output: "export"/);
assert.match(nextConfig, /basePath: "\/koc"/);
await access(new URL("../public/og.png", import.meta.url));
await access(new URL("../public/favicon.svg", import.meta.url));
});
test("renders screenshot-only tasks with anonymous delegation and multi-image uploads", async () => {
const [page, styles] = await Promise.all([
readFile(new URL("../app/page.tsx", import.meta.url), "utf8"),
readFile(new URL("../app/globals.css", import.meta.url), "utf8"),
]);
assert.match(page, /screenshot_collect/);
assert.match(page, /小红书搜索关键词/);
assert.match(page, /上传小红书 AI 总结截图/);
assert.match(page, /submit_screenshot_result/);
assert.match(page, /"task-result"/);
assert.match(page, /不需要填写发布链接或其他数据/);
assert.match(page, /multiple/);
assert.match(page, /taskResultScreenshots/);
assert.match(page, /task-result-thumb/);
assert.match(page, /最多9张/);
assert.doesNotMatch(page, /target="_blank"[\s\S]{0,200}task-result-thumb uploaded/);
assert.match(styles, /\.task-result-gallery/);
assert.match(styles, /\.image-lightbox/);
assert.match(styles, /\.evidence-preview-button/);
});
test("shows D1 timestamps in Beijing time", () => {
const stored = "2026-07-29 05:36:00";
assert.equal(parseStoredDate(stored).toISOString(), "2026-07-29T05:36:00.000Z");
@@ -100,6 +137,9 @@ test("creates anonymous delegation bundles and reuses one-to-one backfill", asyn
assert.match(page, /action:\s*"revoke_delegation"/);
assert.match(page, /合作社转派 · 无需登录/);
assert.match(page, /"X-KOC-Delegation"/);
assert.match(page, /const url = new URL\(window\.location\.href\)/);
assert.match(page, /url\.search = ""/);
assert.doesNotMatch(page, /const url = new URL\(window\.location\.origin\)/);
assert.match(page, /url\.searchParams\.set\("share", shareToken\)/);
assert.match(page, /请保存当前分享链接/);
assert.doesNotMatch(page, /底层KOC手机号|底层KOC企微|底层KOC微信/);

2
koc-portal/tsconfig.json
View File

@@ -30,5 +30,5 @@
".next/dev/types/**/*.ts",
"**/*.mts"
],
"exclude": ["node_modules"]
"exclude": ["node_modules", "build", "worker", "examples"]
}

59
koc-portal/vite.config.ts
View File

@@ -1,59 +0,0 @@
import vinext from "vinext";
import { defineConfig } from "vite";
import hostingConfig from "./.openai/hosting.json";
import { sites } from "./build/sites-vite-plugin";
const SITE_CREATOR_PLACEHOLDER_DATABASE_ID =
"00000000-0000-4000-8000-000000000000";
const { d1, r2 } = hostingConfig;
// macOS Seatbelt blocks FSEvents, so Codex previews need polling for HMR.
const isCodexSeatbeltSandbox = process.env.CODEX_SANDBOX === "seatbelt";
const localBindingConfig = {
main: "./worker/index.ts",
compatibility_flags: ["nodejs_compat"],
d1_databases: d1
? [
{
binding: d1,
database_name: "site-creator-d1",
database_id: SITE_CREATOR_PLACEHOLDER_DATABASE_ID,
},
]
: [],
r2_buckets: r2
? [
{
binding: r2,
bucket_name: "site-creator-r2",
},
]
: [],
};
export default defineConfig(async () => {
// Keep Wrangler and Miniflare state project-local. These are non-secret tool
// settings; application environment belongs in ignored `.env*` files.
process.env.WRANGLER_WRITE_LOGS ??= "false";
process.env.WRANGLER_LOG_PATH ??= ".wrangler/logs";
process.env.MINIFLARE_REGISTRY_PATH ??= ".wrangler/registry";
// Wrangler snapshots its log path while the Cloudflare plugin is imported.
const { cloudflare } = await import("@cloudflare/vite-plugin");
return {
server: isCodexSeatbeltSandbox
? { watch: { useFsEvents: false, usePolling: true } }
: undefined,
plugins: [
vinext(),
sites(),
cloudflare({
viteEnvironment: { name: "rsc", childEnvironments: ["ssr"] },
config: localBindingConfig,
}),
],
};
});

47
koc-portal/worker/index.ts
View File

@@ -1,47 +0,0 @@
/** Cloudflare Worker entry point for the vinext-starter template. */
import { handleImageOptimization, DEFAULT_DEVICE_SIZES, DEFAULT_IMAGE_SIZES } from "vinext/server/image-optimization";
import handler from "vinext/server/app-router-entry";
interface Env {
ASSETS: Fetcher;
DB: D1Database;
IMAGES: {
input(stream: ReadableStream): {
transform(options: Record<string, unknown>): {
output(options: { format: string; quality: number }): Promise<{ response(): Response }>;
};
};
};
}
interface ExecutionContext {
waitUntil(promise: Promise<unknown>): void;
passThroughOnException(): void;
}
// Image security config. SVG sources with .svg extension auto-skip the
// optimization endpoint on the client side (served directly, no proxy).
// To route SVGs through the optimizer (with security headers), set
// dangerouslyAllowSVG: true in next.config.js and uncomment below:
// const imageConfig: ImageConfig = { dangerouslyAllowSVG: true };
const worker = {
async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
const url = new URL(request.url);
if (url.pathname === "/_vinext/image") {
const allowedWidths = [...DEFAULT_DEVICE_SIZES, ...DEFAULT_IMAGE_SIZES];
return handleImageOptimization(request, {
fetchAsset: (path) => env.ASSETS.fetch(new Request(new URL(path, request.url))),
transformImage: async (body, { width, format, quality }) => {
const result = await env.IMAGES.input(body).transform(width > 0 ? { width } : {}).output({ format, quality });
return result.response();
},
}, allowedWidths);
}
return handler.fetch(request, env, ctx);
},
};
export default worker;

5
lib/account-enrichment-service.ts
View File

@@ -5,6 +5,7 @@ import {
type CollectionMcpConfig,
} from "./mcp-collection-client";
import { hashText } from "./mvp-db";
import type { DatabaseClient } from "./database";
type DistributionAccountRow = {
id: string;
@@ -37,7 +38,7 @@ function isVerifiedXhsProfileUrl(value: string | null) {
}
export async function enrichDistributionAccount(
db: D1Database,
db: DatabaseClient,
distributionId: string,
publishUrl: string,
fallbackNickname: string,
@@ -177,7 +178,7 @@ export async function enrichDistributionAccount(
}
export async function backfillAccountProfiles(
db: D1Database,
db: DatabaseClient,
mcpConfig: CollectionMcpConfig,
limit = 10,
) {

11
lib/background.ts Normal file
View File

@@ -0,0 +1,11 @@
export function runInBackground(
operation: Promise<unknown>,
label: string,
) {
void operation.catch((error) => {
console.error(
`[KOC LOOP] ${label} failed`,
error instanceof Error ? error.message : error,
);
});
}

38
lib/collection-service.ts
View File

@@ -3,6 +3,7 @@ import {
type CollectionMcpConfig,
} from "./mcp-collection-client";
import { uid } from "./mvp-db";
import type { DatabaseClient, DatabaseStatement } from "./database";
type DistributionForCollection = {
id: string;
@@ -59,13 +60,23 @@ function shanghaiHourFromTimestamp(timestamp: number) {
);
}
export function isCollectionScheduleDue(
scheduledDate: string,
timestamp: number,
) {
const currentDate = shanghaiDateFromTimestamp(timestamp);
const currentHour = shanghaiHourFromTimestamp(timestamp);
return (
scheduledDate < currentDate ||
(scheduledDate === currentDate && currentHour >= 9)
);
}
function dueSchedules(
startDate: string,
days: number[],
timestamp: number,
) {
const currentDate = shanghaiDateFromTimestamp(timestamp);
const currentHour = shanghaiHourFromTimestamp(timestamp);
return days
.map((scheduleDay) => ({
scheduleDay,
@@ -77,14 +88,13 @@ function dueSchedules(
): value is { scheduleDay: number; scheduledDate: string } =>
Boolean(
value.scheduledDate &&
(value.scheduledDate < currentDate ||
(value.scheduledDate === currentDate && currentHour >= 10)),
isCollectionScheduleDue(value.scheduledDate, timestamp),
),
);
}
export async function createCollectionRunTasks(
db: D1Database,
db: DatabaseClient,
taskId: string,
startDate: string,
days: number[],
@@ -111,7 +121,7 @@ export async function createCollectionRunTasks(
.bind(taskId)
.run();
const statements: D1PreparedStatement[] = [];
const statements: DatabaseStatement[] = [];
for (const distribution of distributions.results) {
for (const scheduleDay of normalizedDays) {
const scheduledDate = dateForScheduleDay(startDate, scheduleDay);
@@ -130,8 +140,8 @@ export async function createCollectionRunTasks(
distribution.id,
scheduledDate,
scheduleDay,
`${scheduledDate}T10:00:00+08:00`,
`等待第${scheduleDay}天 10:00自动采集`,
`${scheduledDate}T09:00:00+08:00`,
`等待第${scheduleDay}天 09:00自动采集`,
),
);
}
@@ -147,7 +157,7 @@ export async function createCollectionRunTasks(
}
export async function collectDistributionMetrics(
db: D1Database,
db: DatabaseClient,
distributionId: string,
scheduledDate: string,
scheduleDay: number | null,
@@ -161,7 +171,7 @@ export async function collectDistributionMetrics(
if (!current) throw new Error("分发记录不存在");
if (!current.publish_url) throw new Error("笔记尚未回填发布链接");
const scheduledAt = `${scheduledDate}T10:00:00+08:00`;
const scheduledAt = `${scheduledDate}T09:00:00+08:00`;
const runId = uid("run");
await db
.prepare(
@@ -230,7 +240,7 @@ export async function collectDistributionMetrics(
const dayWeight = scheduleDay ?? 1;
const successDescription =
source === "automatic"
? `成功 · 第${dayWeight}天 10:00自动采集`
? `成功 · 第${dayWeight}天 09:00自动采集`
: source === "catchup"
? `成功 · 第${dayWeight}天自动追采`
: "成功 · 手动采集";
@@ -310,7 +320,7 @@ export async function collectDistributionMetrics(
}
export async function runScheduledCollections(
db: D1Database,
db: DatabaseClient,
scheduledTimestamp: number,
mcpConfig: CollectionMcpConfig,
) {
@@ -323,7 +333,7 @@ export async function runScheduledCollections(
}
export async function runDueScheduledCollections(
db: D1Database,
db: DatabaseClient,
timestamp: number,
mcpConfig: CollectionMcpConfig,
source: Extract<CollectionSource, "automatic" | "catchup"> = "catchup",
@@ -404,7 +414,7 @@ export async function runDueScheduledCollections(
}
export async function retryFailedCollections(
db: D1Database,
db: DatabaseClient,
taskId: string,
mcpConfig: CollectionMcpConfig,
) {

227
lib/database.ts Normal file
View File

@@ -0,0 +1,227 @@
import mysql, {
type Pool,
type PoolConnection,
type ResultSetHeader,
type RowDataPacket,
} from "mysql2/promise";
import { getDatabaseUrl } from "./runtime-env";
export type DatabaseResult<T = Record<string, unknown>> = {
results: T[];
success: boolean;
meta: {
changes: number;
last_row_id: number;
};
};
type Executor = Pool | PoolConnection;
type MysqlBindValue =
| string
| number
| bigint
| boolean
| Date
| null
| Blob
| Buffer
| Uint8Array
| MysqlBindValue[]
| { [key: string]: MysqlBindValue };
export function normalizeSqlForMysql(input: string) {
let sql = input.trim();
sql = sql.replace(/^INSERT\s+OR\s+IGNORE\s+INTO\b/i, "INSERT IGNORE INTO");
sql = sql.replace(/\s+ESCAPE\s+'\\\\'/gi, "");
sql = sql.replace(
/datetime\(\s*'now'\s*,\s*'-([0-9]+)\s+minutes?'\s*\)/gi,
"DATE_SUB(UTC_TIMESTAMP(), INTERVAL $1 MINUTE)",
);
sql = sql.replace(
/datetime\(\s*([^,]+?)\s*,\s*'\+([0-9]+)\s+days?'\s*\)/gi,
"DATE_ADD($1, INTERVAL $2 DAY)",
);
const conflict = sql.match(
/\s+ON\s+CONFLICT\s*\(([^)]+)\)\s+DO\s+UPDATE\s+SET\s+([\s\S]+)$/i,
);
if (conflict) {
const assignments = conflict[2].replace(
/\bexcluded\.([a-zA-Z_][a-zA-Z0-9_]*)\b/g,
"VALUES($1)",
);
sql = `${sql.slice(0, conflict.index)} ON DUPLICATE KEY UPDATE ${assignments}`;
}
return sql;
}
function normalizeBindValue(value: unknown): MysqlBindValue {
if (value === undefined) return null;
if (
typeof value === "string" &&
/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d{1,3})?Z$/.test(value)
) {
return value.replace("T", " ").replace(/Z$/, "");
}
return value as MysqlBindValue;
}
export class DatabaseStatement {
private params: unknown[] = [];
readonly database: DatabaseClient;
readonly sql: string;
constructor(database: DatabaseClient, sql: string) {
this.database = database;
this.sql = sql;
}
bind(...params: unknown[]) {
const statement = new DatabaseStatement(this.database, this.sql);
statement.params = params;
return statement;
}
async all<T = Record<string, unknown>>() {
return this.database.execute<T>(this.sql, this.params);
}
async first<T = Record<string, unknown>>(column?: string) {
const result = await this.all<T>();
const row = result.results[0];
if (!row) return null;
return column ? ((row as Record<string, unknown>)[column] as T) : row;
}
async run() {
return this.database.execute(this.sql, this.params);
}
values() {
return [...this.params];
}
}
export class DatabaseClient {
private readonly executor: Executor;
constructor(executor: Executor) {
this.executor = executor;
}
prepare(sql: string) {
return new DatabaseStatement(this, sql);
}
async execute<T = Record<string, unknown>>(sql: string, params: unknown[] = []) {
const [result] = await this.executor.execute(
normalizeSqlForMysql(sql),
params.map(normalizeBindValue),
);
if (Array.isArray(result)) {
return {
results: result as T[],
success: true,
meta: { changes: 0, last_row_id: 0 },
} satisfies DatabaseResult<T>;
}
const header = result as ResultSetHeader;
return {
results: [],
success: true,
meta: {
changes: header.affectedRows ?? 0,
last_row_id: header.insertId ?? 0,
},
} satisfies DatabaseResult<T>;
}
async batch(statements: DatabaseStatement[]) {
const pool = getPool();
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
const tx = new DatabaseClient(connection);
const results = [];
for (const statement of statements) {
results.push(await tx.execute(statement.sql, statement.values()));
}
await connection.commit();
return results;
} catch (error) {
await connection.rollback();
throw error;
} finally {
connection.release();
}
}
async transaction<T>(operation: (database: DatabaseClient) => Promise<T>) {
const pool = getPool();
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
const transaction = new DatabaseClient(connection);
const result = await operation(transaction);
await connection.commit();
return result;
} catch (error) {
await connection.rollback();
throw error;
} finally {
connection.release();
}
}
}
declare global {
var __kocLoopMysqlPool: Pool | undefined;
}
export function getPool() {
if (!globalThis.__kocLoopMysqlPool) {
globalThis.__kocLoopMysqlPool = mysql.createPool({
uri: getDatabaseUrl(),
connectionLimit: 10,
waitForConnections: true,
queueLimit: 0,
charset: "utf8mb4",
timezone: "Z",
dateStrings: true,
decimalNumbers: true,
enableKeepAlive: true,
});
}
return globalThis.__kocLoopMysqlPool;
}
export function getDatabase() {
return new DatabaseClient(getPool());
}
export async function checkDatabaseConnection() {
const [rows] = await getPool().query<RowDataPacket[]>("SELECT 1 AS healthy");
return rows[0]?.healthy === 1;
}
export async function withDatabaseLock<T>(
name: string,
timeoutSeconds: number,
operation: () => Promise<T>,
) {
const connection = await getPool().getConnection();
try {
const [rows] = await connection.query<RowDataPacket[]>(
"SELECT GET_LOCK(?, ?) AS acquired",
[name, timeoutSeconds],
);
if (Number(rows[0]?.acquired ?? 0) !== 1) return null;
try {
return await operation();
} finally {
await connection.query("SELECT RELEASE_LOCK(?)", [name]);
}
} finally {
connection.release();
}
}

153
lib/distribution-release-service.ts Normal file
View File

@@ -0,0 +1,153 @@
import type { DatabaseClient } from "./database";
type ReleasableDistribution = {
id: string;
task_id: string;
task_type: string;
content_id: string;
content_title: string;
partner_id: string;
partner_name: string;
claim_id: string | null;
delegation_bundle_id: string | null;
publish_url: string | null;
result_submitted_at: string | null;
};
export class DistributionReleaseError extends Error {
readonly status: number;
constructor(message: string, status: number) {
super(message);
this.name = "DistributionReleaseError";
this.status = status;
}
}
export function distributionReleaseBlockReason(input: {
publishUrl?: string | null;
resultSubmittedAt?: string | null;
taskType?: string | null;
}) {
if (input.publishUrl) return "已回填发布链接的笔记不能释放";
if (input.resultSubmittedAt) return "已提交结果截图的任务不能释放";
return null;
}
export async function releaseUnfinishedDistribution(
database: DatabaseClient,
distributionId: string,
) {
if (!distributionId) {
throw new DistributionReleaseError("请选择需要释放的领取记录", 400);
}
return database.transaction(async (db) => {
const distribution = await db
.prepare(
`SELECT
d.id,
d.task_id,
t.task_type,
d.content_id,
c.title AS content_title,
d.partner_id,
p.name AS partner_name,
d.claim_id,
d.delegation_bundle_id,
d.publish_url,
d.result_submitted_at
FROM distributions d
JOIN tasks t ON t.id = d.task_id
JOIN contents c ON c.id = d.content_id
JOIN partners p ON p.id = d.partner_id
WHERE d.id = ?
FOR UPDATE`,
)
.bind(distributionId)
.first<ReleasableDistribution>();
if (!distribution) {
throw new DistributionReleaseError("领取记录不存在或已被释放", 404);
}
const blocked = distributionReleaseBlockReason({
publishUrl: distribution.publish_url,
resultSubmittedAt: distribution.result_submitted_at,
taskType: distribution.task_type,
});
if (blocked) throw new DistributionReleaseError(blocked, 409);
await db
.prepare("DELETE FROM collection_runs WHERE distribution_id = ?")
.bind(distribution.id)
.run();
await db
.prepare("DELETE FROM distributions WHERE id = ?")
.bind(distribution.id)
.run();
await db
.prepare("UPDATE contents SET status = 'available' WHERE id = ?")
.bind(distribution.content_id)
.run();
await db
.prepare(
`UPDATE tasks
SET claimed_quantity = GREATEST(claimed_quantity - 1, 0)
WHERE id = ?`,
)
.bind(distribution.task_id)
.run();
await db
.prepare(
`UPDATE partners
SET claimed_total = GREATEST(claimed_total - 1, 0)
WHERE id = ?`,
)
.bind(distribution.partner_id)
.run();
if (distribution.delegation_bundle_id) {
await db
.prepare(
`UPDATE delegation_bundles
SET quantity = GREATEST(quantity - 1, 0),
status = CASE WHEN quantity <= 1 THEN 'revoked' ELSE status END,
revoked_at = CASE WHEN quantity <= 1 THEN CURRENT_TIMESTAMP ELSE revoked_at END,
updated_at = CURRENT_TIMESTAMP
WHERE id = ?`,
)
.bind(distribution.delegation_bundle_id)
.run();
}
if (distribution.claim_id) {
await db
.prepare(
`UPDATE claims
SET quantity = GREATEST(quantity - 1, 0)
WHERE id = ?`,
)
.bind(distribution.claim_id)
.run();
await db
.prepare(
`DELETE FROM claims
WHERE id = ?
AND quantity <= 0
AND NOT EXISTS (
SELECT 1 FROM distributions WHERE claim_id = ?
)`,
)
.bind(distribution.claim_id, distribution.claim_id)
.run();
}
return {
distributionId: distribution.id,
taskId: distribution.task_id,
contentId: distribution.content_id,
contentTitle: distribution.content_title,
partnerName: distribution.partner_name,
};
});
}

65
lib/mcp-collection-client.ts
View File

@@ -225,7 +225,7 @@ async function createMcpSession(
timeoutMs,
);
const sessionId = initialize.response.headers.get("mcp-session-id");
if (!sessionId) throw new Error("MCP采集服务未返回会话标识");
if (!sessionId) return undefined;
await postMcp(
fetchImpl,
@@ -242,10 +242,10 @@ async function createMcpSession(
return sessionId;
}
async function callMcpTool(
async function invokeMcpTool(
fetchImpl: typeof fetch,
endpoint: string,
sessionId: string,
sessionId: string | undefined,
timeoutMs: number,
name: string,
args: Record<string, unknown>,
@@ -272,6 +272,12 @@ async function callMcpTool(
try {
payload = JSON.parse(text);
} catch {
if (result.envelope?.result?.isError === true) {
return {
isError: true,
payload: { message: text },
};
}
throw new Error("MCP采集工具返回了无法解析的数据");
}
return {
@@ -280,6 +286,40 @@ async function callMcpTool(
};
}
function isToolArgumentShapeError(result: ToolResult) {
if (!result.isError) return false;
const root = asRecord(result.payload);
const message = String(root?.message ?? "");
return /input validation error/i.test(message);
}
async function callMcpTool(
fetchImpl: typeof fetch,
endpoint: string,
sessionId: string | undefined,
timeoutMs: number,
name: string,
args: Record<string, unknown>,
): Promise<ToolResult> {
const nested = await invokeMcpTool(
fetchImpl,
endpoint,
sessionId,
timeoutMs,
name,
{ request: args },
);
if (!isToolArgumentShapeError(nested)) return nested;
return invokeMcpTool(
fetchImpl,
endpoint,
sessionId,
timeoutMs,
name,
args,
);
}
function metricsFromToolResult(result: ToolResult): XhsPublicMetrics {
const root = asRecord(result.payload);
const response = asRecord(root?.response) ?? root;
@@ -579,7 +619,7 @@ export async function resolveXhsPublicAccountDetails(
try {
parsed = new URL(profileUrl);
} catch {
return { redId: "", followers: null, ipLocation: "" };
return { nickname: "", redId: "", followers: null, ipLocation: "" };
}
if (
parsed.protocol !== "https:" ||
@@ -589,7 +629,7 @@ export async function resolveXhsPublicAccountDetails(
) ||
!parsed.pathname.startsWith("/user/profile/")
) {
return { redId: "", followers: null, ipLocation: "" };
return { nickname: "", redId: "", followers: null, ipLocation: "" };
}
try {
const response = await fetchImpl(parsed.toString(), {
@@ -602,10 +642,12 @@ export async function resolveXhsPublicAccountDetails(
},
});
if (!response.ok) {
return { redId: "", followers: null, ipLocation: "" };
return { nickname: "", redId: "", followers: null, ipLocation: "" };
}
const html = await response.text();
return {
nickname:
html.match(/"(?:nickname|nickName)":"([^"]+)"/)?.[1] ?? "",
redId:
html.match(/"(?:redId|red_id)":"([^"]+)"/)?.[1] ??
html.match(/小红书号[：:]\s*([^<"\s]+)/)?.[1] ??
@@ -620,7 +662,7 @@ export async function resolveXhsPublicAccountDetails(
ipLocation: "",
};
} catch {
return { redId: "", followers: null, ipLocation: "" };
return { nickname: "", redId: "", followers: null, ipLocation: "" };
}
}
@@ -650,6 +692,9 @@ function profileDetailsFromToolResult(result: ToolResult) {
const payload =
asRecord(response?.data) ?? asRecord(root?.data) ?? result.payload;
const followers = followerCountFromPayload(payload);
const nickname =
findStringByKey(payload, "nickname") ||
findStringByKey(payload, "nickName");
const redId =
findStringByKey(payload, "red_id") ||
findStringByKey(payload, "redId") ||
@@ -658,10 +703,10 @@ function profileDetailsFromToolResult(result: ToolResult) {
const ipLocation =
findStringByKey(payload, "ip_location") ||
findStringByKey(payload, "ipLocation");
if (followers === null && !redId && !ipLocation) {
if (followers === null && !nickname && !redId && !ipLocation) {
throw new Error("账号主页采集结果缺少可用字段");
}
return { followers, redId, ipLocation };
return { nickname, followers, redId, ipLocation };
}
function accountProfileFromToolResult(
@@ -717,7 +762,7 @@ async function completeAccountProfile(
profile: XhsAccountProfile,
fetchImpl: typeof fetch,
endpoint: string,
sessionId: string,
sessionId: string | undefined,
timeoutMs: number,
) {
let completed = profile;

70
lib/mcp-operations.ts
View File

@@ -83,9 +83,9 @@ export async function taskList(
(SELECT COUNT(*) FROM distributions d WHERE d.task_id = t.id AND COALESCE(d.publish_url, '') != '') AS published_count,
(SELECT COUNT(*) FROM distributions d WHERE d.task_id = t.id AND d.exposure IS NOT NULL AND d.views IS NOT NULL) AS day7_count
FROM tasks t ${where}
ORDER BY t.created_at DESC LIMIT ? OFFSET ?`,
ORDER BY t.created_at DESC LIMIT ${limit} OFFSET ${offset}`,
)
.bind(...bindings, limit, offset)
.bind(...bindings)
.all<Record<string, unknown>>(),
db
.prepare(`SELECT COUNT(*) AS total FROM tasks t ${where}`)
@@ -96,14 +96,19 @@ export async function taskList(
total: count?.total ?? 0,
limit,
offset,
tasks: rows.results.map((row) => ({
...row,
collection_days: parseJsonArray(row.collection_days),
claim_url:
portalUrl && row.share_token
? buildClaimUrl(portalUrl, String(row.share_token))
: null,
})),
tasks: rows.results.map(
(row): Record<string, unknown> & {
collection_days: unknown[];
claim_url: string | null;
} => ({
...row,
collection_days: parseJsonArray(row.collection_days),
claim_url:
portalUrl && row.share_token
? buildClaimUrl(portalUrl, String(row.share_token))
: null,
}),
),
};
}
@@ -151,15 +156,19 @@ export async function taskGet(taskId: string, portalUrl: string) {
.bind(taskId)
.all<Record<string, unknown>>(),
]);
return {
task: {
const taskOutput: Record<string, unknown> & {
collection_days: unknown[];
claim_url: string | null;
} = {
...task,
collection_days: parseJsonArray(task.collection_days),
claim_url:
portalUrl && task.share_token
? buildClaimUrl(portalUrl, String(task.share_token))
: null,
},
};
return {
task: taskOutput,
notes: notes.results.map((row) => ({
...row,
image_assets: parseJsonArray(row.image_assets),
@@ -214,9 +223,9 @@ export async function recoveryList(
`SELECT d.*, t.name AS task_name, c.source_row, c.title,
a.nickname AS account_nickname, a.profile_url, p.name AS cooperation_source,
cl.claimant_name ${base}
ORDER BY COALESCE(d.publish_time, d.claimed_at) DESC LIMIT ? OFFSET ?`,
ORDER BY COALESCE(d.publish_time, d.claimed_at) DESC LIMIT ${limit} OFFSET ${offset}`,
)
.bind(...bindings, limit, offset)
.bind(...bindings)
.all<Record<string, unknown>>(),
db
.prepare(`SELECT COUNT(*) AS total ${base}`)
@@ -275,7 +284,7 @@ export async function setCollectionPlan(
collection_status_description = CASE WHEN latest_likes IS NULL THEN ? ELSE collection_status_description END,
updated_at = CURRENT_TIMESTAMP
WHERE task_id = ? AND COALESCE(publish_url, '') != ''`,
).bind(`已安排${normalizedDays.length}个采集日，每日10:00执行`, taskId),
).bind(`已安排${normalizedDays.length}个采集日，每日09:00执行`, taskId),
]);
const created = await createCollectionRunTasks(db, taskId, startDate, normalizedDays);
const catchup = await runDueScheduledCollections(
@@ -359,10 +368,12 @@ function resourceWhere(input: ResourceFilters) {
}
if (input.cooperationSource?.trim()) {
conditions.push(
`EXISTS (SELECT 1 FROM distributions dx JOIN partners px ON px.id = dx.partner_id
WHERE dx.account_id = a.id AND px.name LIKE ? ESCAPE '\\')`,
`(a.cooperation_source LIKE ? ESCAPE '\\' OR
EXISTS (SELECT 1 FROM distributions dx JOIN partners px ON px.id = dx.partner_id
WHERE dx.account_id = a.id AND px.name LIKE ? ESCAPE '\\'))`,
);
bindings.push(like(input.cooperationSource.trim()));
const pattern = like(input.cooperationSource.trim());
bindings.push(pattern, pattern);
}
if (input.platform?.trim() && input.platform !== "all") {
conditions.push("a.platform = ?");
@@ -380,18 +391,27 @@ export async function resourceSearch(input: ResourceFilters) {
(SELECT COUNT(*) FROM distributions d WHERE d.account_id = a.id) AS cooperation_count,
(SELECT GROUP_CONCAT(DISTINCT p.name) FROM distributions d JOIN partners p ON p.id = d.partner_id WHERE d.account_id = a.id) AS cooperation_sources`;
const [rows, count] = await Promise.all([
db.prepare(`${select} FROM accounts a ${where} ORDER BY a.last_seen_at DESC LIMIT ? OFFSET ?`)
.bind(...bindings, limit, offset).all<Record<string, unknown>>(),
db.prepare(`${select} FROM accounts a ${where} ORDER BY a.last_seen_at DESC LIMIT ${limit} OFFSET ${offset}`)
.bind(...bindings).all<Record<string, unknown>>(),
db.prepare(`SELECT COUNT(*) AS total FROM accounts a ${where}`).bind(...bindings).first<{ total: number }>(),
]);
return {
total: count?.total ?? 0,
limit,
offset,
accounts: rows.results.map((row) => ({
...row,
cooperation_sources: String(row.cooperation_sources ?? "").split(",").filter(Boolean),
})),
accounts: rows.results.map(
(row): Record<string, unknown> & { cooperation_sources: string[] } => ({
...row,
cooperation_sources: [
...new Set(
`${row.cooperation_sources ?? ""}、${row.cooperation_source ?? ""}`
.split(/[、,，;；|]/)
.map((item) => item.trim())
.filter(Boolean),
),
],
}),
),
};
}

2
lib/mcp-tools.ts
View File

@@ -127,7 +127,7 @@ export function registerMcpOperationTools(server: McpServer, options: Options) {
"collection_plan_set",
{
title: "设置自动采集计划",
description: "为任务设置开始日期及第1至第7天的自动采集日，系统在北京时间10:00执行。",
description: "为任务设置开始日期及第1至第7天的自动采集日，系统在北京时间09:00执行。",
inputSchema: z.object({
task_id: z.string().min(1),
start_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).describe("北京时间开始日期 YYYY-MM-DD"),

106
lib/mvp-db.ts
View File

@@ -1,26 +1,80 @@
import { env } from "cloudflare:workers";
import { type DatabaseClient, getDatabase } from "./database";
import { getObjectStore } from "./object-store";
import { getRuntimeEnv, isEnabled } from "./runtime-env";
import feishuSnapshot from "./feishu-source-snapshot.json";
type D1ResultRow = Record<string, unknown>;
export function getRawDb(): D1Database {
const database = (env as unknown as { DB?: D1Database }).DB;
if (!database) {
throw new Error("数据库尚未连接");
}
return database;
export function getRawDb(): DatabaseClient {
return getDatabase();
}
export function getUploadBucket(): R2Bucket {
const bucket = (env as unknown as { UPLOADS?: R2Bucket }).UPLOADS;
if (!bucket) {
throw new Error("文件存储尚未连接");
}
return bucket;
export function getUploadBucket() {
return getObjectStore();
}
export async function ensureSchema(database?: D1Database) {
export async function ensureSchema(database?: DatabaseClient) {
const db = database ?? getRawDb();
{
await db.prepare("SELECT id FROM tasks LIMIT 1").all();
const tasksWithoutShare = await db
.prepare("SELECT id FROM tasks WHERE share_token IS NULL OR share_token = ''")
.all<{ id: string }>();
for (const task of tasksWithoutShare.results) {
await db
.prepare("UPDATE tasks SET share_token = ? WHERE id = ?")
.bind(crypto.randomUUID().replaceAll("-", ""), task.id)
.run();
}
await db
.prepare(
`UPDATE distributions
SET latest_likes = COALESCE(d7_likes, d5_likes, d2_likes),
latest_comments = COALESCE(d7_comments, d5_comments, d2_comments),
latest_collects = COALESCE(d7_collects, d5_collects, d2_collects),
collection_status = 'success',
collection_status_description = '历史采集数据已迁移',
collection_updated_at = updated_at,
last_collection_day = CASE
WHEN d7_likes IS NOT NULL THEN 7
WHEN d5_likes IS NOT NULL THEN 5
WHEN d2_likes IS NOT NULL THEN 2
ELSE NULL
END
WHERE latest_likes IS NULL
AND COALESCE(d7_likes, d5_likes, d2_likes) IS NOT NULL`,
)
.run();
const tasksNeedingImages = await db
.prepare(
`SELECT DISTINCT t.id
FROM tasks t
JOIN contents c ON c.task_id = t.id
WHERE t.source_sheet_id = ?
AND (c.image_assets IS NULL OR c.image_assets = '' OR c.image_assets = '[]')`,
)
.bind(feishuSnapshot.sheetId)
.all<{ id: string }>();
for (const task of tasksNeedingImages.results) {
const updates = feishuSnapshot.rows
.filter((row) => row.images.length > 0)
.map((row) =>
db
.prepare(
`UPDATE contents SET image_assets = ?
WHERE task_id = ? AND source_row = ?
AND (image_assets IS NULL OR image_assets = '' OR image_assets = '[]')`,
)
.bind(JSON.stringify(row.images), task.id, row.sourceRow),
);
if (updates.length > 0) await db.batch(updates);
}
}
return;
const statements = [
`CREATE TABLE IF NOT EXISTS partners (
id TEXT PRIMARY KEY,
@@ -39,6 +93,7 @@ export async function ensureSchema(database?: D1Database) {
claimed_quantity INTEGER NOT NULL DEFAULT 0,
due_at TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active',
task_type TEXT NOT NULL DEFAULT 'content_publish',
source_url TEXT NOT NULL DEFAULT '',
source_sheet_id TEXT NOT NULL DEFAULT '',
source_sheet_name TEXT NOT NULL DEFAULT '',
@@ -71,6 +126,7 @@ export async function ensureSchema(database?: D1Database) {
followers INTEGER NOT NULL DEFAULT 0,
post_count INTEGER NOT NULL DEFAULT 0,
avg_views INTEGER NOT NULL DEFAULT 0,
cooperation_source TEXT NOT NULL DEFAULT '',
first_seen_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_seen_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)`,
@@ -109,6 +165,8 @@ export async function ensureSchema(database?: D1Database) {
publish_url TEXT,
publish_time TEXT,
publish_screenshot_key TEXT,
result_screenshot_key TEXT,
result_submitted_at TEXT,
status TEXT NOT NULL DEFAULT 'claimed',
claimed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
screenshot_key TEXT,
@@ -192,6 +250,11 @@ export async function ensureSchema(database?: D1Database) {
};
await ensureColumn("tasks", "source_url", "source_url TEXT NOT NULL DEFAULT ''");
await ensureColumn(
"tasks",
"task_type",
"task_type TEXT NOT NULL DEFAULT 'content_publish'",
);
await ensureColumn(
"tasks",
"source_sheet_id",
@@ -241,6 +304,16 @@ export async function ensureSchema(database?: D1Database) {
"publish_screenshot_key",
"publish_screenshot_key TEXT",
);
await ensureColumn(
"distributions",
"result_screenshot_key",
"result_screenshot_key TEXT",
);
await ensureColumn(
"distributions",
"result_submitted_at",
"result_submitted_at TEXT",
);
await ensureColumn(
"distributions",
"latest_likes",
@@ -399,6 +472,7 @@ export async function ensureSchema(database?: D1Database) {
}
export async function seedIfEmpty() {
if (!isEnabled(getRuntimeEnv().SEED_DEMO_DATA)) return;
const db = getRawDb();
const row = await db.prepare("SELECT COUNT(*) AS count FROM tasks").first<{
count: number;
@@ -733,6 +807,7 @@ export async function getDashboardData() {
a.platform AS account_platform,
t.name AS task_name,
t.brand AS task_brand,
t.task_type AS task_type,
t.due_at AS due_at
FROM distributions d
JOIN contents c ON c.id = d.content_id
@@ -749,8 +824,7 @@ export async function getDashboardData() {
tasks: tasksResult.results as D1ResultRow[],
accounts: accountsResult.results as D1ResultRow[],
distributions: distributionsResult.results as D1ResultRow[],
portal_url:
(env as unknown as { KOC_PORTAL_URL?: string }).KOC_PORTAL_URL ?? "",
portal_url: getRuntimeEnv().KOC_PORTAL_URL ?? "",
};
}

125
lib/object-store.ts Normal file
View File

@@ -0,0 +1,125 @@
import { mkdir, readFile, rename, stat, writeFile } from "node:fs/promises";
import path from "node:path";
import { getRuntimeEnv } from "./runtime-env";
type PutOptions = {
httpMetadata?: { contentType?: string };
customMetadata?: Record<string, string>;
};
type StoredMetadata = {
contentType: string;
customMetadata: Record<string, string>;
uploadedAt: string;
};
export class StoredObjectBody {
readonly body: Uint8Array;
private readonly bytes: Buffer;
private readonly metadata: StoredMetadata;
constructor(bytes: Buffer, metadata: StoredMetadata) {
this.bytes = bytes;
this.metadata = metadata;
this.body = new Uint8Array(bytes);
}
async arrayBuffer() {
return this.body.buffer.slice(
this.body.byteOffset,
this.body.byteOffset + this.body.byteLength,
) as ArrayBuffer;
}
writeHttpMetadata(headers: Headers) {
headers.set("Content-Type", this.metadata.contentType);
}
get customMetadata() {
return this.metadata.customMetadata;
}
}
export function normalizeObjectKey(key: string) {
const normalized = key.replaceAll("\\", "/").replace(/^\/+/, "");
if (!normalized || normalized.includes("\0")) {
throw new Error("文件存储键无效");
}
const parts = normalized.split("/");
if (parts.some((part) => !part || part === "." || part === "..")) {
throw new Error("文件存储键不安全");
}
return parts.join("/");
}
function defaultUploadDir() {
return path.join(process.cwd(), ".data", "uploads");
}
export class LocalObjectStore {
readonly root: string;
constructor(root = getRuntimeEnv().UPLOAD_DIR || defaultUploadDir()) {
this.root = root;
}
private resolve(key: string) {
const normalized = normalizeObjectKey(key);
const filePath = path.join(this.root, ...normalized.split("/"));
return { normalized, filePath, metadataPath: `${filePath}.metadata.json` };
}
async put(key: string, input: ArrayBuffer | Uint8Array, options: PutOptions = {}) {
const target = this.resolve(key);
await mkdir(path.dirname(target.filePath), { recursive: true });
const bytes = input instanceof Uint8Array ? input : new Uint8Array(input);
const metadata: StoredMetadata = {
contentType: options.httpMetadata?.contentType || "application/octet-stream",
customMetadata: options.customMetadata ?? {},
uploadedAt: new Date().toISOString(),
};
const suffix = `${process.pid}-${crypto.randomUUID()}`;
const temporaryFile = `${target.filePath}.${suffix}.tmp`;
const temporaryMetadata = `${target.metadataPath}.${suffix}.tmp`;
await Promise.all([
writeFile(temporaryFile, bytes),
writeFile(temporaryMetadata, JSON.stringify(metadata), "utf8"),
]);
await rename(temporaryFile, target.filePath);
await rename(temporaryMetadata, target.metadataPath);
return { key: target.normalized };
}
async get(key: string) {
const target = this.resolve(key);
try {
const [bytes, metadataText] = await Promise.all([
readFile(target.filePath),
readFile(target.metadataPath, "utf8").catch(() => ""),
]);
const fallback: StoredMetadata = {
contentType: "application/octet-stream",
customMetadata: {},
uploadedAt: (await stat(target.filePath)).mtime.toISOString(),
};
const metadata = metadataText
? ({ ...fallback, ...JSON.parse(metadataText) } as StoredMetadata)
: fallback;
return new StoredObjectBody(bytes, metadata);
} catch (error) {
if ((error as NodeJS.ErrnoException).code === "ENOENT") return null;
throw error;
}
}
}
declare global {
var __kocLoopObjectStore: LocalObjectStore | undefined;
}
export function getObjectStore() {
if (!globalThis.__kocLoopObjectStore) {
globalThis.__kocLoopObjectStore = new LocalObjectStore();
}
return globalThis.__kocLoopObjectStore;
}

18
lib/partner-cors.ts
View File

@@ -1,12 +1,22 @@
import { env } from "cloudflare:workers";
import { getRuntimeEnv } from "./runtime-env";
const env = getRuntimeEnv();
function allowedOrigin(request: Request) {
const origin = request.headers.get("origin");
if (!origin) return null;
const portalOrigin = String(
const portalUrl = String(
(env as unknown as { KOC_PORTAL_URL?: string }).KOC_PORTAL_URL ?? "",
).replace(/\/$/, "");
return origin === portalOrigin || origin === "http://localhost:3000"
).trim();
let portalOrigin = "";
try {
portalOrigin = portalUrl ? new URL(portalUrl).origin : "";
} catch {
portalOrigin = "";
}
return [portalOrigin, "http://localhost:3000", "http://localhost:3001"].includes(
origin,
)
? origin
: null;
}

322
lib/resource-import.ts Normal file
View File

@@ -0,0 +1,322 @@
import { strFromU8, unzipSync } from "fflate";
export const RESOURCE_IMPORT_MAX_ROWS = 100;
export const RESOURCE_IMPORT_MAX_BYTES = 5 * 1024 * 1024;
export type ResourceImportRow = {
rowNumber: number;
platform: string;
nickname: string;
publicAccountId: string;
profileUrl: string;
ipLocation: string;
followers: number;
followersResolved: boolean;
cooperationSource: string;
errors: string[];
};
const HEADER_ALIASES = {
profileUrl: ["账号链接", "账号主页", "账号主页链接", "主页链接"],
nickname: ["账号昵称", "账号名称", "昵称"],
publicAccountId: ["账号ID", "账号号", "小红书号", "抖音号"],
ipLocation: ["IP属地", "IP地址", "IP地区", "IP所在地"],
followers: ["粉丝数", "粉丝", "粉丝量"],
cooperationSource: ["合作来源", "资源来源", "历史合作来源"],
} as const;
type CanonicalHeader = keyof typeof HEADER_ALIASES;
function decodeXml(value: string) {
return value
.replace(/<[^>]+>/g, "")
.replace(/&lt;/g, "<")
.replace(/&gt;/g, ">")
.replace(/&quot;/g, '"')
.replace(/&apos;/g, "'")
.replace(/&amp;/g, "&")
.replace(/&#(\d+);/g, (_, code) => String.fromCodePoint(Number(code)))
.replace(/&#x([0-9a-f]+);/gi, (_, code) =>
String.fromCodePoint(Number.parseInt(code, 16)),
);
}
function textNodes(xml: string) {
return [...xml.matchAll(/<t\b[^>]*>([\s\S]*?)<\/t>/g)]
.map((match) => decodeXml(match[1]))
.join("");
}
function columnIndex(reference: string) {
const letters = reference.match(/^[A-Z]+/i)?.[0]?.toUpperCase() ?? "";
let result = 0;
for (const letter of letters) result = result * 26 + letter.charCodeAt(0) - 64;
return Math.max(0, result - 1);
}
function parseWorksheet(xml: string, sharedStrings: string[]) {
const rows: string[][] = [];
for (const rowMatch of xml.matchAll(/<row\b([^>]*)>([\s\S]*?)<\/row>/g)) {
const rowAttributes = rowMatch[1];
const rowNumber = Number(rowAttributes.match(/\br="(\d+)"/)?.[1] ?? rows.length + 1);
const values: string[] = [];
for (const cellMatch of rowMatch[2].matchAll(/<c\b([^>]*)>([\s\S]*?)<\/c>/g)) {
const attributes = cellMatch[1];
const body = cellMatch[2];
const reference = attributes.match(/\br="([A-Z]+\d+)"/i)?.[1] ?? "A1";
const type = attributes.match(/\bt="([^"]+)"/)?.[1] ?? "";
const rawValue = body.match(/<v>([\s\S]*?)<\/v>/)?.[1] ?? "";
let value = "";
if (type === "s") value = sharedStrings[Number(rawValue)] ?? "";
else if (type === "inlineStr") value = textNodes(body);
else if (type === "b") value = rawValue === "1" ? "TRUE" : "FALSE";
else value = decodeXml(rawValue);
values[columnIndex(reference)] = value.trim();
}
while (rows.length < rowNumber - 1) rows.push([]);
rows[rowNumber - 1] = values;
}
return rows;
}
function parseXlsx(bytes: Uint8Array) {
const entries = unzipSync(bytes);
const sharedXml = entries["xl/sharedStrings.xml"]
? strFromU8(entries["xl/sharedStrings.xml"])
: "";
const sharedStrings = [...sharedXml.matchAll(/<si\b[^>]*>([\s\S]*?)<\/si>/g)].map(
(match) => textNodes(match[1]),
);
const sheets = Object.keys(entries)
.filter((name) => /^xl\/worksheets\/sheet\d+\.xml$/.test(name))
.sort((left, right) => left.localeCompare(right, undefined, { numeric: true }));
if (sheets.length === 0) throw new Error("Excel 中没有可读取的工作表");
return sheets.map((name) => parseWorksheet(strFromU8(entries[name]), sharedStrings));
}
function parseCsv(text: string) {
const rows: string[][] = [];
let row: string[] = [];
let cell = "";
let quoted = false;
for (let index = 0; index < text.length; index += 1) {
const char = text[index];
if (quoted) {
if (char === '"' && text[index + 1] === '"') {
cell += '"';
index += 1;
} else if (char === '"') quoted = false;
else cell += char;
} else if (char === '"') quoted = true;
else if (char === ",") {
row.push(cell.trim());
cell = "";
} else if (char === "\n" || char === "\r") {
if (char === "\r" && text[index + 1] === "\n") index += 1;
row.push(cell.trim());
if (row.some(Boolean)) rows.push(row);
row = [];
cell = "";
} else cell += char;
}
row.push(cell.trim());
if (row.some(Boolean)) rows.push(row);
return rows;
}
function normalizeHeader(value: string) {
return value
.replace(/[\s_\-（）()]/g, "")
.replace(/必填|选填/g, "")
.toLocaleLowerCase("zh-CN");
}
function canonicalHeader(value: string): CanonicalHeader | null {
const normalized = normalizeHeader(value);
for (const [key, aliases] of Object.entries(HEADER_ALIASES)) {
if (aliases.some((alias) => normalizeHeader(alias) === normalized)) {
return key as CanonicalHeader;
}
}
return null;
}
function findHeader(rows: string[][]) {
for (let index = 0; index < Math.min(rows.length, 12); index += 1) {
const mapping = new Map<CanonicalHeader, number>();
rows[index].forEach((cell, column) => {
const header = canonicalHeader(cell);
if (header && !mapping.has(header)) mapping.set(header, column);
});
if (mapping.has("profileUrl")) {
return { rowIndex: index, mapping };
}
}
return null;
}
export function normalizeProfileUrl(value: string) {
const extracted = value.match(/https?:\/\/[^\s，,；;]+/i)?.[0] ?? value.trim();
if (!extracted) return "";
try {
const url = new URL(extracted);
if (!(["http:", "https:"].includes(url.protocol))) return "";
url.protocol = "https:";
url.hostname = url.hostname.toLowerCase();
url.search = "";
url.hash = "";
url.pathname = url.pathname.replace(/\/+$/, "") || "/";
return url.toString().replace(/\/$/, "");
} catch {
return "";
}
}
export function platformFromProfileUrl(profileUrl: string) {
if (!profileUrl) return "";
try {
const url = new URL(profileUrl);
if (
(url.hostname === "xiaohongshu.com" ||
url.hostname.endsWith(".xiaohongshu.com")) &&
/^\/user\/profile\/[^/]+/i.test(url.pathname)
) {
return "小红书";
}
} catch {
// URL validation is reported by normalizeRows.
}
return "";
}
function valueAt(row: string[], mapping: Map<CanonicalHeader, number>, key: CanonicalHeader) {
const index = mapping.get(key);
return index === undefined ? "" : String(row[index] ?? "").trim();
}
export function parseResourceFollowers(value: string) {
const normalized = value.trim().replace(/[,，\s]/g, "").replace(/\+$/, "");
if (!normalized) return { value: 0, resolved: false, valid: true };
const match = normalized.match(/^(\d+(?:\.\d+)?)(万|w|W|千|k|K)?$/);
if (!match) return { value: 0, resolved: false, valid: false };
const multiplier =
match[2] === "万" || match[2]?.toLowerCase() === "w"
? 10_000
: match[2] === "千" || match[2]?.toLowerCase() === "k"
? 1_000
: 1;
return {
value: Math.round(Number(match[1]) * multiplier),
resolved: true,
valid: true,
};
}
export function resourceImportMissingFields(
row: Pick<
ResourceImportRow,
"nickname" | "publicAccountId" | "ipLocation" | "followersResolved"
>,
) {
const missing: string[] = [];
if (!row.nickname.trim()) missing.push("nickname");
if (!row.publicAccountId.trim()) missing.push("publicAccountId");
if (!row.ipLocation.trim() || row.ipLocation.trim() === "待识别") {
missing.push("ipLocation");
}
if (!row.followersResolved) missing.push("followers");
return missing;
}
function normalizeRows(rows: string[][]) {
const header = findHeader(rows);
if (!header) {
throw new Error("没有找到模板表头，请使用系统提供的导入模板");
}
const result: ResourceImportRow[] = [];
for (let index = header.rowIndex + 1; index < rows.length; index += 1) {
const source = rows[index];
if (!source.some((cell) => String(cell ?? "").trim())) continue;
const rawProfileUrl = valueAt(source, header.mapping, "profileUrl");
const profileUrl = normalizeProfileUrl(rawProfileUrl);
const platform = platformFromProfileUrl(profileUrl);
const rawFollowers = valueAt(source, header.mapping, "followers");
const parsedFollowers = parseResourceFollowers(rawFollowers);
const errors: string[] = [];
if (!rawProfileUrl) errors.push("账号主页不能为空");
else if (!profileUrl) errors.push("账号主页链接格式不正确");
else if (!platform) errors.push("当前自动解析仅支持小红书账号主页");
if (!parsedFollowers.valid) {
errors.push("粉丝数格式不正确，请填写数字或如 1.3万、10+");
}
result.push({
rowNumber: index + 1,
platform,
nickname: valueAt(source, header.mapping, "nickname"),
publicAccountId: valueAt(source, header.mapping, "publicAccountId"),
profileUrl,
ipLocation: valueAt(source, header.mapping, "ipLocation"),
followers: parsedFollowers.value,
followersResolved: parsedFollowers.resolved,
cooperationSource: valueAt(source, header.mapping, "cooperationSource"),
errors,
});
}
if (result.length === 0) throw new Error("表格中没有可导入的账号数据");
if (result.length > RESOURCE_IMPORT_MAX_ROWS) {
throw new Error(`单次最多导入 ${RESOURCE_IMPORT_MAX_ROWS} 个账号`);
}
return result;
}
export function parseResourceImportFile(fileName: string, bytes: Uint8Array) {
const extension = fileName.toLocaleLowerCase().split(".").pop();
const workbooks =
extension === "csv"
? [parseCsv(new TextDecoder("utf-8").decode(bytes).replace(/^\uFEFF/, ""))]
: extension === "xlsx"
? parseXlsx(bytes)
: null;
if (!workbooks) throw new Error("仅支持 .xlsx 或 .csv 文件");
for (const rows of workbooks) {
if (findHeader(rows)) return normalizeRows(rows);
}
throw new Error("没有找到模板表头，请使用系统提供的导入模板");
}
function shortHash(value: string) {
let hash = 2166136261;
for (const character of value) {
hash ^= character.charCodeAt(0);
hash = Math.imul(hash, 16777619);
}
return Math.abs(hash >>> 0).toString(36);
}
export function resourcePlatformUid(row: Pick<ResourceImportRow, "platform" | "profileUrl" | "publicAccountId">) {
if (row.profileUrl) {
try {
const url = new URL(row.profileUrl);
const candidate =
url.pathname.match(/\/user\/profile\/([^/]+)/i)?.[1] ??
url.pathname.match(/\/(?:user|profile)\/([^/]+)/i)?.[1] ??
url.pathname.split("/").filter(Boolean).at(-1);
if (candidate && candidate.length >= 3) return candidate;
} catch {
// Validation already reports malformed profile links.
}
}
const identity = row.publicAccountId || row.profileUrl;
return `manual-${shortHash(`${row.platform}:${identity}`)}`;
}
export function mergeCooperationSources(existing: string, incoming: string) {
return [
...new Set(
`${existing}、${incoming}`
.split(/[、,，;；|]/)
.map((item) => item.trim())
.filter(Boolean),
),
].join("、");
}

34
lib/result-screenshots.ts Normal file
View File

@@ -0,0 +1,34 @@
export const MAX_RESULT_SCREENSHOTS = 9;
function isResultScreenshotKey(value: unknown): value is string {
return (
typeof value === "string" &&
value.startsWith("task-results/") &&
value.length <= 512
);
}
export function parseResultScreenshotKeys(value: unknown): string[] {
const text = String(value ?? "").trim();
if (!text) return [];
if (isResultScreenshotKey(text)) return [text];
try {
const parsed = JSON.parse(text) as unknown;
if (!Array.isArray(parsed)) return [];
return [...new Set(parsed.filter(isResultScreenshotKey))].slice(
0,
MAX_RESULT_SCREENSHOTS,
);
} catch {
return [];
}
}
export function serializeResultScreenshotKeys(keys: string[]) {
return JSON.stringify(
[...new Set(keys.filter(isResultScreenshotKey))].slice(
0,
MAX_RESULT_SCREENSHOTS,
),
);
}

45
lib/runtime-env.ts Normal file
View File

@@ -0,0 +1,45 @@
export type RuntimeEnv = {
DATABASE_URL?: string;
MYSQL_HOST?: string;
MYSQL_PORT?: string;
MYSQL_USER?: string;
MYSQL_PASSWORD?: string;
MYSQL_DATABASE?: string;
UPLOAD_DIR?: string;
APP_ORIGIN?: string;
KOC_PORTAL_URL?: string;
SUPER_ADMIN_USERNAME?: string;
SUPER_ADMIN_PASSWORD?: string;
ADMIN_INTERNAL_TOKEN?: string;
KOC_MCP_API_KEY?: string;
KOC_LOOP_MCP_API_KEY?: string;
MCP_API_KEY?: string;
FEISHU_APP_ID?: string;
FEISHU_APP_SECRET?: string;
AI_TOOL_CENTER_MCP_URL?: string;
AI_TOOL_CENTER_MCP_KEY?: string;
COLLECTION_MCP_URL?: string;
COLLECTION_MCP_KEY?: string;
SEED_DEMO_DATA?: string;
ENABLE_SCHEDULER?: string;
};
export function getRuntimeEnv(): RuntimeEnv {
return process.env as RuntimeEnv;
}
export function getDatabaseUrl() {
const env = getRuntimeEnv();
if (env.DATABASE_URL) return env.DATABASE_URL;
const host = env.MYSQL_HOST ?? "127.0.0.1";
const port = env.MYSQL_PORT ?? "3306";
const user = encodeURIComponent(env.MYSQL_USER ?? "koc");
const password = encodeURIComponent(env.MYSQL_PASSWORD ?? "");
const database = env.MYSQL_DATABASE ?? "koc_loop";
return `mysql://${user}:${password}@${host}:${port}/${database}`;
}
export function isEnabled(value: string | undefined, defaultValue = false) {
if (value === undefined || value === "") return defaultValue;
return !["0", "false", "no", "off"].includes(value.toLowerCase());
}

43
lib/scheduler.ts Normal file
View File

@@ -0,0 +1,43 @@
import cron, { type ScheduledTask } from "node-cron";
import { backfillAccountProfiles } from "./account-enrichment-service";
import { runScheduledCollections } from "./collection-service";
import { withDatabaseLock } from "./database";
import {
resolveCollectionMcpConfig,
type CollectionMcpBindings,
} from "./mcp-collection-client";
import { ensureSchema, getRawDb } from "./mvp-db";
import { getRuntimeEnv, isEnabled } from "./runtime-env";
declare global {
var __kocLoopScheduler: ScheduledTask | undefined;
}
async function runDailyJob() {
await withDatabaseLock("koc-loop-daily-collection", 0, async () => {
await ensureSchema();
const db = getRawDb();
const config = resolveCollectionMcpConfig(
getRuntimeEnv() as unknown as CollectionMcpBindings,
);
const collections = await runScheduledCollections(db, Date.now(), config);
const accounts = await backfillAccountProfiles(db, config, 10);
console.info("[KOC LOOP] daily scheduler completed", {
collections,
accounts,
});
});
}
export function startScheduler() {
if (!isEnabled(getRuntimeEnv().ENABLE_SCHEDULER, true)) return;
if (globalThis.__kocLoopScheduler) return;
globalThis.__kocLoopScheduler = cron.schedule(
"0 9 * * *",
() => void runDailyJob().catch((error) => {
console.error("[KOC LOOP] daily scheduler failed", error);
}),
{ timezone: "Asia/Shanghai", noOverlap: true },
);
console.info("[KOC LOOP] scheduler enabled at 09:00 Asia/Shanghai");
}

26
lib/sort-utils.ts Normal file
View File

@@ -0,0 +1,26 @@
export type SortDirection = "asc" | "desc";
export function sortWithNullsLast<T>(
items: T[],
valueFor: (item: T) => number | null,
direction: SortDirection,
) {
return items
.map((item, index) => ({ item, index }))
.sort((left, right) => {
const leftValue = valueFor(left.item);
const rightValue = valueFor(right.item);
if (leftValue === null && rightValue === null) {
return left.index - right.index;
}
if (leftValue === null) return 1;
if (rightValue === null) return -1;
const compared = leftValue - rightValue;
return compared === 0
? left.index - right.index
: direction === "asc"
? compared
: -compared;
})
.map(({ item }) => item);
}

108
lib/task-service.ts
View File

@@ -12,6 +12,16 @@ export type CreateDistributionTaskInput = {
dueAt: string;
};
export type CreateScreenshotTaskInput = {
name: string;
brand: string;
dueAt: string;
keyword: string;
instructions: string;
quantity: number;
exampleImageKey?: string;
};
export type DistributionTaskCreation = {
created: boolean;
taskId: string;
@@ -25,6 +35,16 @@ export type DistributionTaskCreation = {
sourceUrl: string;
};
export type ScreenshotTaskCreation = {
created: true;
taskId: string;
shareToken: string;
name: string;
brand: string;
dueAt: string;
quantity: number;
};
type TaskRow = {
id: string;
share_token: string | null;
@@ -213,6 +233,94 @@ export async function createDistributionTask(
};
}
export async function createScreenshotTask(
rawInput: CreateScreenshotTaskInput,
): Promise<ScreenshotTaskCreation> {
await ensureSchema();
const input = {
name: normalizedValue(rawInput.name),
brand: normalizedValue(rawInput.brand),
dueAt: normalizedDueDate(rawInput.dueAt),
keyword: normalizedValue(rawInput.keyword),
instructions: normalizedValue(rawInput.instructions),
quantity: Math.floor(Number(rawInput.quantity)),
exampleImageKey: normalizedValue(rawInput.exampleImageKey ?? ""),
};
if (!input.name || !input.brand || !input.keyword || !input.instructions) {
throw new Error("请补全任务名称、品牌/项目、搜索关键词和任务说明");
}
if (!Number.isInteger(input.quantity) || input.quantity < 1 || input.quantity > 500) {
throw new Error("任务数量需为 1—500 份");
}
if (input.exampleImageKey && !input.exampleImageKey.startsWith("task-assets/")) {
throw new Error("示例截图无效，请重新上传");
}
const db = getRawDb();
const taskId = uid("task");
const shareToken = crypto.randomUUID().replaceAll("-", "");
const imageAssets = input.exampleImageKey
? JSON.stringify([
{ index: 1, key: input.exampleImageKey, width: null, height: null },
])
: "[]";
await db
.prepare(
`INSERT INTO tasks
(id, name, brand, quantity, claimed_quantity, due_at, status,
task_type, source_url, source_sheet_id, source_sheet_name,
share_token, collection_days)
VALUES (?, ?, ?, ?, 0, ?, 'active', 'screenshot_collect', '', '', '', ?, '[]')`,
)
.bind(
taskId,
input.name,
input.brand,
input.quantity,
input.dueAt,
shareToken,
)
.run();
try {
const statements = Array.from({ length: input.quantity }, (_, index) =>
db
.prepare(
`INSERT INTO contents
(id, task_id, title, body, image_assets, status, source, source_row)
VALUES (?, ?, ?, ?, ?, 'available', '截图回收任务', ?)`,
)
.bind(
uid("content"),
taskId,
input.keyword,
input.instructions,
imageAssets,
index + 1,
),
);
for (let index = 0; index < statements.length; index += 100) {
await db.batch(statements.slice(index, index + 100));
}
} catch (error) {
await db.batch([
db.prepare("DELETE FROM contents WHERE task_id = ?").bind(taskId),
db.prepare("DELETE FROM tasks WHERE id = ?").bind(taskId),
]);
throw error;
}
return {
created: true,
taskId,
shareToken,
name: input.name,
brand: input.brand,
dueAt: input.dueAt,
quantity: input.quantity,
};
}
export function buildClaimUrl(portalOrigin: string, shareToken: string) {
const origin = normalizedValue(portalOrigin).replace(/\/$/, "");
if (!origin) throw new Error("KOC 领取站点地址尚未配置");

15
lib/user-auth.ts
View File

@@ -1,6 +1,8 @@
import { env } from "cloudflare:workers";
import { getRuntimeEnv } from "./runtime-env";
import { ensureSchema, getRawDb } from "./mvp-db";
const env = getRuntimeEnv();
export type UserRole = "super_admin" | "admin" | "user";
export type AuthUser = {
@@ -15,7 +17,7 @@ export type RequestPrincipal =
const SESSION_COOKIE = "koc_session";
const SESSION_MAX_AGE_SECONDS = 60 * 60 * 24 * 7;
// Cloudflare Workers caps PBKDF2 at 100,000 iterations.
// Keep existing password records compatible while using a production-safe cost.
const PASSWORD_ITERATIONS = 100_000;
function bytesToHex(bytes: Uint8Array) {
@@ -198,6 +200,15 @@ export function clearSessionCookie(secure = true) {
.join("; ");
}
export function requestUsesHttps(request: Request) {
const forwardedProtocol = request.headers
.get("x-forwarded-proto")
?.split(",")[0]
?.trim()
.toLowerCase();
return forwardedProtocol === "https" || new URL(request.url).protocol === "https:";
}
export async function createSession(userId: string) {
const tokenBytes = new Uint8Array(32);
crypto.getRandomValues(tokenBytes);

195
mysql/0001_init.sql Normal file
View File

@@ -0,0 +1,195 @@
CREATE TABLE IF NOT EXISTS partners (
id VARCHAR(64) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
wecom_name VARCHAR(255) NOT NULL,
owner VARCHAR(255) NOT NULL DEFAULT '运营组',
claimed_total INT NOT NULL DEFAULT 0,
completed_total INT NOT NULL DEFAULT 0,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS tasks (
id VARCHAR(64) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
brand VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
claimed_quantity INT NOT NULL DEFAULT 0,
due_at VARCHAR(32) NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'active',
source_url TEXT NOT NULL DEFAULT (''),
source_sheet_id VARCHAR(255) NOT NULL DEFAULT '',
source_sheet_name VARCHAR(255) NOT NULL DEFAULT '',
source_synced_at DATETIME(3) NULL,
share_token VARCHAR(128) NULL,
collection_start_date VARCHAR(32) NULL,
collection_days TEXT NOT NULL DEFAULT ('[]'),
collection_schedule_updated_at DATETIME(3) NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE KEY tasks_share_token_idx (share_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS contents (
id VARCHAR(64) PRIMARY KEY,
task_id VARCHAR(64) NOT NULL,
title TEXT NOT NULL,
body LONGTEXT NOT NULL DEFAULT (''),
image_assets LONGTEXT NOT NULL DEFAULT ('[]'),
status VARCHAR(32) NOT NULL DEFAULT 'available',
source VARCHAR(255) NOT NULL DEFAULT '飞书内容表',
source_row INT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
KEY contents_task_status_idx (task_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS accounts (
id VARCHAR(64) PRIMARY KEY,
platform VARCHAR(32) NOT NULL DEFAULT '小红书',
platform_uid VARCHAR(255) NOT NULL,
public_account_id VARCHAR(255) NOT NULL DEFAULT '',
nickname VARCHAR(255) NOT NULL,
profile_url TEXT NOT NULL DEFAULT (''),
ip_location VARCHAR(255) NOT NULL DEFAULT '待识别',
followers INT NOT NULL DEFAULT 0,
post_count INT NOT NULL DEFAULT 0,
avg_views INT NOT NULL DEFAULT 0,
first_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
last_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE KEY accounts_platform_uid_idx (platform, platform_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS claims (
id VARCHAR(64) PRIMARY KEY,
task_id VARCHAR(64) NOT NULL,
partner_id VARCHAR(64) NOT NULL,
claimant_name VARCHAR(255) NOT NULL,
claim_token VARCHAR(128) NOT NULL,
quantity INT NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE KEY claims_claim_token_idx (claim_token),
KEY claims_task_partner_created_idx (task_id, partner_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS delegation_bundles (
id VARCHAR(64) PRIMARY KEY,
task_id VARCHAR(64) NOT NULL,
claim_id VARCHAR(64) NOT NULL,
partner_id VARCHAR(64) NOT NULL,
label VARCHAR(255) NOT NULL,
share_token VARCHAR(128) NOT NULL,
quantity INT NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'active',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
revoked_at DATETIME(3) NULL,
UNIQUE KEY delegation_bundles_share_token_idx (share_token),
KEY delegation_bundles_claim_created_idx (claim_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS distributions (
id VARCHAR(64) PRIMARY KEY,
task_id VARCHAR(64) NOT NULL,
content_id VARCHAR(64) NOT NULL,
partner_id VARCHAR(64) NOT NULL,
claim_id VARCHAR(64) NULL,
delegation_bundle_id VARCHAR(64) NULL,
account_id VARCHAR(64) NULL,
publish_url TEXT NULL,
publish_time DATETIME(3) NULL,
publish_screenshot_key VARCHAR(512) NULL,
status VARCHAR(32) NOT NULL DEFAULT 'claimed',
claimed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
screenshot_key VARCHAR(512) NULL,
ocr_status VARCHAR(32) NOT NULL DEFAULT 'none',
exposure INT NULL,
views INT NULL,
d2_likes INT NULL,
d2_comments INT NULL,
d2_collects INT NULL,
d5_likes INT NULL,
d5_comments INT NULL,
d5_collects INT NULL,
d7_likes INT NULL,
d7_comments INT NULL,
d7_collects INT NULL,
latest_likes INT NULL,
latest_comments INT NULL,
latest_collects INT NULL,
collection_status VARCHAR(32) NOT NULL DEFAULT 'pending',
collection_status_description TEXT NULL,
collection_updated_at DATETIME(3) NULL,
last_collection_day INT NULL,
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
KEY distributions_task_status_idx (task_id, status),
KEY distributions_content_idx (content_id),
KEY distributions_account_idx (account_id),
KEY distributions_claim_idx (claim_id),
KEY distributions_delegation_idx (delegation_bundle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS collection_runs (
id VARCHAR(64) PRIMARY KEY,
task_id VARCHAR(64) NOT NULL,
distribution_id VARCHAR(64) NOT NULL,
scheduled_date VARCHAR(32) NOT NULL,
schedule_day INT NULL,
scheduled_at DATETIME(3) NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'pending',
likes INT NULL,
comments INT NULL,
collects INT NULL,
status_description TEXT NULL,
started_at DATETIME(3) NULL,
completed_at DATETIME(3) NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE KEY collection_runs_distribution_date_idx (distribution_id, scheduled_date),
KEY collection_runs_task_date_idx (task_id, scheduled_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS users (
id VARCHAR(64) PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
password_salt VARCHAR(255) NOT NULL,
password_iterations INT NOT NULL,
role VARCHAR(32) NOT NULL,
super_admin_guard TINYINT GENERATED ALWAYS AS (
CASE WHEN role = 'super_admin' THEN 1 ELSE NULL END
) STORED,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE KEY users_username_idx (username),
UNIQUE KEY users_single_super_admin_idx (super_admin_guard)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS auth_sessions (
token_hash VARCHAR(128) PRIMARY KEY,
user_id VARCHAR(64) NOT NULL,
expires_at DATETIME(3) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
KEY auth_sessions_user_id_idx (user_id),
KEY auth_sessions_expires_at_idx (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS mcp_export_tokens (
token_hash VARCHAR(128) PRIMARY KEY,
kind VARCHAR(64) NOT NULL,
payload LONGTEXT NOT NULL,
expires_at DATETIME(3) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
KEY mcp_export_tokens_expires_at_idx (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS background_jobs (
id VARCHAR(64) PRIMARY KEY,
type VARCHAR(64) NOT NULL,
payload LONGTEXT NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'pending',
attempts INT NOT NULL DEFAULT 0,
available_at DATETIME(3) NOT NULL,
locked_at DATETIME(3) NULL,
completed_at DATETIME(3) NULL,
last_error TEXT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
KEY background_jobs_pending_idx (status, available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2
mysql/0002_resource_import.sql Normal file
View File

@@ -0,0 +1,2 @@
ALTER TABLE accounts
ADD COLUMN cooperation_source VARCHAR(500) NOT NULL DEFAULT '' AFTER avg_views;

8
mysql/0003_screenshot_tasks.sql Normal file
View File

@@ -0,0 +1,8 @@
ALTER TABLE tasks
ADD COLUMN task_type VARCHAR(32) NOT NULL DEFAULT 'content_publish' AFTER status;
-- statement-breakpoint
ALTER TABLE distributions
ADD COLUMN result_screenshot_key VARCHAR(512) NULL AFTER publish_screenshot_key;
-- statement-breakpoint
ALTER TABLE distributions
ADD COLUMN result_submitted_at DATETIME(3) NULL AFTER result_screenshot_key;

2
mysql/0004_multi_result_screenshots.sql Normal file
View File

@@ -0,0 +1,2 @@
ALTER TABLE distributions
MODIFY COLUMN result_screenshot_key TEXT NULL;

3
next.config.ts
View File

@@ -1,7 +1,8 @@
import type { NextConfig } from "next";
const nextConfig: NextConfig = {
/* config options here */
output: "standalone",
serverExternalPackages: ["mysql2"],
};
export default nextConfig;

3448
package-lock.json generated
View File

File diff suppressed because it is too large Load Diff

32
package.json
View File

@@ -1,44 +1,44 @@
{
"name": "site-creator-vinext-starter",
"name": "koc-loop",
"version": "0.1.0",
"private": true,
"engines": {
"node": ">=22.13.0"
},
"scripts": {
"dev": "WRANGLER_LOG_PATH=.wrangler/wrangler.log vinext dev",
"build": "WRANGLER_LOG_PATH=.wrangler/wrangler.log vinext build",
"start": "WRANGLER_LOG_PATH=.wrangler/wrangler.log vinext start",
"test": "npm run build && node --test tests/*.test.mjs",
"lint": "eslint . --ignore-pattern dist --ignore-pattern .next",
"db:generate": "drizzle-kit generate"
"dev": "next dev",
"build": "next build",
"start": "next start",
"test": "npm run build && node --import tsx --test tests/*.test.mjs",
"lint": "eslint . --ignore-pattern dist --ignore-pattern .next --ignore-pattern koc-portal/out",
"db:generate": "drizzle-kit generate",
"db:migrate": "node scripts/migrate-mysql.mjs",
"db:check": "node scripts/check-mysql.mjs",
"db:import-json": "node scripts/import-d1-json.mjs",
"storage:import": "node scripts/import-object-directory.mjs"
},
"dependencies": {
"@modelcontextprotocol/server": "^2.0.0",
"drizzle-orm": "0.45.2",
"fflate": "0.7.4",
"next": "16.2.6",
"mysql2": "^3.15.3",
"next": "^16.3.0",
"node-cron": "^4.2.1",
"react": "19.2.6",
"react-dom": "19.2.6",
"zod": "^4.4.3"
},
"devDependencies": {
"@cloudflare/vite-plugin": "1.37.1",
"@tailwindcss/postcss": "4.2.1",
"@types/node": "22.19.19",
"@types/react": "19.2.14",
"@types/react-dom": "19.2.3",
"@vitejs/plugin-react": "6.0.2",
"@vitejs/plugin-rsc": "0.5.26",
"drizzle-kit": "0.31.10",
"eslint": "9.39.4",
"eslint-config-next": "16.2.6",
"react-server-dom-webpack": "19.2.6",
"tailwindcss": "4.2.1",
"typescript": "5.9.3",
"vinext": "0.0.50",
"vite": "8.0.13",
"wrangler": "4.92.0"
"tsx": "^4.20.6",
"typescript": "5.9.3"
},
"type": "module"
}

BIN
public/KOC资源导入模板.xlsx Normal file
View File

Binary file not shown.

24
scripts/check-mysql.mjs Normal file
View File

@@ -0,0 +1,24 @@
import mysql from "mysql2/promise";
function databaseUrl() {
if (process.env.DATABASE_URL) return process.env.DATABASE_URL;
const host = process.env.MYSQL_HOST ?? "127.0.0.1";
const port = process.env.MYSQL_PORT ?? "3306";
const user = encodeURIComponent(process.env.MYSQL_USER ?? "koc");
const password = encodeURIComponent(process.env.MYSQL_PASSWORD ?? "");
const database = process.env.MYSQL_DATABASE ?? "koc_loop";
return `mysql://${user}:${password}@${host}:${port}/${database}`;
}
const connection = await mysql.createConnection({
uri: databaseUrl(),
charset: "utf8mb4",
timezone: "Z",
});
try {
const [rows] = await connection.query(
"SELECT COUNT(*) AS table_count FROM information_schema.tables WHERE table_schema = DATABASE()",
);
console.info(`database ready, tables=${Number(rows[0]?.table_count ?? 0)}`);
} finally {
await connection.end();
}

103
scripts/import-d1-json.mjs Normal file
View File

@@ -0,0 +1,103 @@
import { readFile } from "node:fs/promises";
import mysql from "mysql2/promise";
const allowedTables = [
"partners",
"tasks",
"contents",
"accounts",
"claims",
"delegation_bundles",
"distributions",
"collection_runs",
"users",
"auth_sessions",
"mcp_export_tokens",
];
function databaseUrl() {
if (process.env.DATABASE_URL) return process.env.DATABASE_URL;
const host = process.env.MYSQL_HOST ?? "127.0.0.1";
const port = process.env.MYSQL_PORT ?? "3306";
const user = encodeURIComponent(process.env.MYSQL_USER ?? "koc");
const password = encodeURIComponent(process.env.MYSQL_PASSWORD ?? "");
const database = process.env.MYSQL_DATABASE ?? "koc_loop";
return `mysql://${user}:${password}@${host}:${port}/${database}`;
}
function normalizeImportedValue(value) {
if (
typeof value === "string" &&
/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d{1,3})?Z$/.test(value)
) {
return value.replace("T", " ").replace(/Z$/, "");
}
return value;
}
const inputPath = process.argv[2];
if (!inputPath) {
throw new Error("用法：npm run db:import-json -- /path/to/d1-export.json");
}
const parsed = JSON.parse(await readFile(inputPath, "utf8"));
const tables = parsed.tables && typeof parsed.tables === "object"
? parsed.tables
: parsed;
const pool = mysql.createPool({
uri: databaseUrl(),
connectionLimit: 2,
charset: "utf8mb4",
timezone: "Z",
dateStrings: true,
});
try {
for (const table of allowedTables) {
const rows = Array.isArray(tables[table]) ? tables[table] : [];
if (rows.length === 0) continue;
const [columnRows] = await pool.query(
`SELECT column_name, extra FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = ? ORDER BY ordinal_position`,
[table],
);
const allowedColumns = new Set(
columnRows
.filter((column) => !String(column.extra ?? "").includes("GENERATED"))
.map((column) => String(column.column_name)),
);
const columns = [...new Set(rows.flatMap((row) => Object.keys(row)))]
.filter((column) => allowedColumns.has(column));
if (columns.length === 0) continue;
const quotedColumns = columns.map((column) => `\`${column}\``).join(", ");
const updates = columns
.filter((column) => column !== "id" && column !== "token_hash")
.map((column) => `\`${column}\` = VALUES(\`${column}\`)`)
.join(", ");
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
for (let offset = 0; offset < rows.length; offset += 200) {
const chunk = rows.slice(offset, offset + 200);
const placeholders = chunk
.map(() => `(${columns.map(() => "?").join(", ")})`)
.join(", ");
const sql = `INSERT INTO \`${table}\` (${quotedColumns}) VALUES ${placeholders}${
updates ? ` ON DUPLICATE KEY UPDATE ${updates}` : ""
}`;
const values = chunk.flatMap((row) =>
columns.map((column) => normalizeImportedValue(row[column] ?? null)),
);
await connection.query(sql, values);
}
await connection.commit();
console.info(`imported ${table}: ${rows.length}`);
} catch (error) {
await connection.rollback();
throw error;
} finally {
connection.release();
}
}
} finally {
await pool.end();
}

59
scripts/import-object-directory.mjs Normal file
View File

@@ -0,0 +1,59 @@
import { copyFile, mkdir, readdir, readFile, writeFile } from "node:fs/promises";
import path from "node:path";
const sourceRoot = process.argv[2];
const targetRoot = process.env.UPLOAD_DIR || process.argv[3];
if (!sourceRoot || !targetRoot) {
throw new Error(
"用法：UPLOAD_DIR=/data/koc/uploads npm run storage:import -- /path/to/r2-export",
);
}
const contentTypes = {
".avif": "image/avif",
".gif": "image/gif",
".jpeg": "image/jpeg",
".jpg": "image/jpeg",
".png": "image/png",
".webp": "image/webp",
};
let copied = 0;
async function walk(directory) {
const entries = await readdir(directory, { withFileTypes: true });
for (const entry of entries) {
const absolute = path.join(directory, entry.name);
if (entry.isDirectory()) {
await walk(absolute);
continue;
}
if (entry.name.endsWith(".metadata.json")) continue;
const relative = path.relative(sourceRoot, absolute);
if (relative.startsWith("..") || path.isAbsolute(relative)) {
throw new Error("导入目录越界");
}
const destination = path.join(targetRoot, relative);
await mkdir(path.dirname(destination), { recursive: true });
await copyFile(absolute, destination);
const sourceMetadata = `${absolute}.metadata.json`;
const metadataDestination = `${destination}.metadata.json`;
let metadata;
try {
metadata = JSON.parse(await readFile(sourceMetadata, "utf8"));
} catch {
metadata = {
contentType:
contentTypes[path.extname(entry.name).toLowerCase()] ||
"application/octet-stream",
customMetadata: { source: "r2-export" },
uploadedAt: new Date().toISOString(),
};
}
await writeFile(metadataDestination, JSON.stringify(metadata), "utf8");
copied += 1;
}
}
await mkdir(targetRoot, { recursive: true });
await walk(sourceRoot);
console.info(`imported objects: ${copied}`);

63
scripts/migrate-mysql.mjs Normal file
View File

@@ -0,0 +1,63 @@
import { readFile, readdir } from "node:fs/promises";
import path from "node:path";
import mysql from "mysql2/promise";
function databaseUrl() {
if (process.env.DATABASE_URL) return process.env.DATABASE_URL;
const host = process.env.MYSQL_HOST ?? "127.0.0.1";
const port = process.env.MYSQL_PORT ?? "3306";
const user = encodeURIComponent(process.env.MYSQL_USER ?? "koc");
const password = encodeURIComponent(process.env.MYSQL_PASSWORD ?? "");
const database = process.env.MYSQL_DATABASE ?? "koc_loop";
return `mysql://${user}:${password}@${host}:${port}/${database}`;
}
const pool = mysql.createPool({
uri: databaseUrl(),
connectionLimit: 2,
charset: "utf8mb4",
timezone: "Z",
});
const connection = await pool.getConnection();
try {
const [lockRows] = await connection.query(
"SELECT GET_LOCK('koc-loop-schema-migration', 60) AS acquired",
);
if (Number(lockRows[0]?.acquired ?? 0) !== 1) {
throw new Error("无法获取数据库迁移锁");
}
await connection.query(`CREATE TABLE IF NOT EXISTS schema_migrations (
version VARCHAR(255) PRIMARY KEY,
applied_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci`);
const migrationDir = path.join(process.cwd(), "mysql");
const files = (await readdir(migrationDir))
.filter((file) => file.endsWith(".sql"))
.sort();
for (const file of files) {
const [rows] = await connection.query(
"SELECT version FROM schema_migrations WHERE version = ?",
[file],
);
if (rows.length > 0) continue;
const source = await readFile(path.join(migrationDir, file), "utf8");
const statements = source
.split(/^-- statement-breakpoint\s*$/m)
.map((statement) => statement.trim())
.filter(Boolean);
for (const statement of statements) {
await connection.query(statement);
}
await connection.query(
"INSERT INTO schema_migrations (version) VALUES (?)",
[file],
);
console.info(`applied ${file}`);
}
await connection.query("SELECT RELEASE_LOCK('koc-loop-schema-migration')");
} finally {
connection.release();
await pool.end();
}

58
tests/collection-schedule.test.mjs Normal file
View File

@@ -0,0 +1,58 @@
import assert from "node:assert/strict";
import test from "node:test";
import { isCollectionScheduleDue } from "../lib/collection-service.ts";
import { sortWithNullsLast } from "../lib/sort-utils.ts";
test("runs the Beijing collection schedule from 09:00", () => {
const scheduledDate = "2026-08-12";
assert.equal(
isCollectionScheduleDue(
scheduledDate,
Date.parse("2026-08-12T00:59:59Z"),
),
false,
);
assert.equal(
isCollectionScheduleDue(
scheduledDate,
Date.parse("2026-08-12T01:00:00Z"),
),
true,
);
assert.equal(
isCollectionScheduleDue(
scheduledDate,
Date.parse("2026-08-13T00:00:00Z"),
),
true,
);
assert.equal(
isCollectionScheduleDue(
scheduledDate,
Date.parse("2026-08-11T16:00:00Z"),
),
false,
);
});
test("sorts both directions while keeping missing values last", () => {
const values = [
{ id: "missing-a", value: null },
{ id: "middle", value: 20 },
{ id: "high", value: 50 },
{ id: "missing-b", value: null },
{ id: "low", value: 10 },
];
assert.deepEqual(
sortWithNullsLast(values, (item) => item.value, "asc").map(
(item) => item.id,
),
["low", "middle", "high", "missing-a", "missing-b"],
);
assert.deepEqual(
sortWithNullsLast(values, (item) => item.value, "desc").map(
(item) => item.id,
),
["high", "middle", "low", "missing-a", "missing-b"],
);
});

58
tests/distribution-release.test.mjs Normal file
View File

@@ -0,0 +1,58 @@
import assert from "node:assert/strict";
import { readFile } from "node:fs/promises";
import test from "node:test";
import {
DistributionReleaseError,
distributionReleaseBlockReason,
} from "../lib/distribution-release-service.ts";
test("allows only unfinished assignments to return to the claim pool", () => {
assert.equal(
distributionReleaseBlockReason({
publishUrl: null,
resultSubmittedAt: null,
taskType: "content_publish",
}),
null,
);
assert.equal(
distributionReleaseBlockReason({
publishUrl: "https://www.xiaohongshu.com/discovery/item/example",
resultSubmittedAt: null,
taskType: "content_publish",
}),
"已回填发布链接的笔记不能释放",
);
assert.equal(
distributionReleaseBlockReason({
publishUrl: null,
resultSubmittedAt: "2026-08-12 09:00:00",
taskType: "screenshot_collect",
}),
"已提交结果截图的任务不能释放",
);
assert.equal(new DistributionReleaseError("blocked", 409).status, 409);
});
test("releases a claim atomically and restores all counters", async () => {
const [service, database, actionRoute, adminApp] = await Promise.all([
readFile(new URL("../lib/distribution-release-service.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/database.ts", import.meta.url), "utf8"),
readFile(new URL("../app/api/action/route.ts", import.meta.url), "utf8"),
readFile(new URL("../app/admin-app.tsx", import.meta.url), "utf8"),
]);
assert.match(database, /async transaction<T>/);
assert.match(service, /FOR UPDATE/);
assert.match(service, /DELETE FROM distributions WHERE id = \?/);
assert.match(service, /UPDATE contents SET status = 'available'/);
assert.match(service, /claimed_quantity = GREATEST\(claimed_quantity - 1, 0\)/);
assert.match(service, /claimed_total = GREATEST\(claimed_total - 1, 0\)/);
assert.match(service, /UPDATE claims/);
assert.match(service, /UPDATE delegation_bundles/);
assert.match(actionRoute, /body\.action === "release_distribution"/);
assert.match(actionRoute, /isManagerRequest/);
assert.match(adminApp, /确认释放这篇笔记/);
assert.match(adminApp, /已释放，可重新领取/);
assert.match(adminApp, /role="alertdialog"/);
});

54
tests/mcp-collection-client.test.mjs
View File

@@ -106,11 +106,55 @@ test("collects likes, comments and favorites from the verified MCP shape", async
});
assert.equal(calls.length, 3);
assert.equal(calls[2].body.params.name, "fetch_content_detail");
assert.equal(calls[2].body.params.arguments.include_comments, false);
assert.equal(calls[2].body.params.arguments.request.include_comments, false);
assert.equal(calls[2].headers.get("mcp-session-id"), "session-test");
assert.equal(new URL(calls[0].url).searchParams.get("key"), "test-key");
});
test("collects through a stateless MCP server without a session header", async () => {
const calls = [];
const fetchImpl = async (_url, init) => {
const body = JSON.parse(init.body);
calls.push({ body, headers: new Headers(init.headers) });
if (body.method === "initialize") {
return sse({
jsonrpc: "2.0",
id: body.id,
result: {
protocolVersion: "2025-03-26",
capabilities: { tools: { listChanged: false } },
serverInfo: { name: "ai-tool-center", version: "production" },
},
});
}
if (body.method === "tools/call") {
return sse(
toolEnvelope({
response: {
code: 200,
success: true,
data: { likes: 12, comments: 3, collects: 6 },
},
}),
);
}
throw new Error(`Unexpected MCP method: ${body.method}`);
};
const result = await collectXhsMetricsFromMcp(
"https://www.xiaohongshu.com/explore/test",
{ endpoint: "https://collector.example/mcp", key: "test-key" },
fetchImpl,
);
assert.deepEqual(result, { likes: 12, comments: 3, collects: 6 });
assert.deepEqual(
calls.map((call) => call.body.method),
["initialize", "tools/call"],
);
assert.equal(calls[1].headers.get("mcp-session-id"), null);
});
test("resolves the real XHS account profile from a submitted note link", async () => {
const { calls, fetchImpl } = createFakeMcp([
toolEnvelope({
@@ -180,12 +224,12 @@ test("resolves the real XHS account profile from a submitted note link", async (
"collect_xhs_wen_note_detail",
);
assert.equal(
calls[2].body.params.arguments.note_id,
calls[2].body.params.arguments.request.note_id,
"6a671108000000000f004bef",
);
assert.equal(calls[3].body.params.name, "parse_xhs_user_summary");
assert.equal(
calls[3].body.params.arguments.url,
calls[3].body.params.arguments.request.url,
"https://www.xiaohongshu.com/user/profile/6905cbca0000000037009f49",
);
assert.equal(
@@ -225,6 +269,7 @@ test("resolves followers directly from the supported XHS user summary tool", asy
);
assert.deepEqual(details, {
nickname: "555 五",
followers: 6,
redId: "1020668113",
ipLocation: "福建",
@@ -293,7 +338,7 @@ test("resolves an xhslink short URL before requesting the author profile", async
"https://www.xiaohongshu.com/user/profile/6905cbca0000000037009f49",
);
assert.equal(
fakeMcp.calls[2].body.params.arguments.note_id,
fakeMcp.calls[2].body.params.arguments.request.note_id,
"6a572da40000000021018bd2",
);
});
@@ -379,6 +424,7 @@ test("reads the user-visible Xiaohongshu number from a public profile", async ()
assert.equal(accountId, "1020668113");
assert.deepEqual(details, {
nickname: "",
redId: "1020668113",
followers: 10,
ipLocation: "",

6
tests/mcp-task-server.test.mjs
View File

@@ -9,11 +9,11 @@ test("exposes authenticated KOC task, recovery, collection, and resource MCP too
readFile(new URL("../lib/mcp-tools.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/mcp-operations.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/mcp-export-token.ts", import.meta.url), "utf8"),
readFile(new URL("../drizzle/0008_worried_ultimatum.sql", import.meta.url), "utf8"),
readFile(new URL("../mysql/0001_init.sql", import.meta.url), "utf8"),
readFile(new URL("../lib/task-service.ts", import.meta.url), "utf8"),
readFile(new URL("../app/api/action/route.ts", import.meta.url), "utf8"),
readFile(new URL("../README.md", import.meta.url), "utf8"),
readFile(new URL("../.dev.vars.example", import.meta.url), "utf8"),
readFile(new URL("../.env.self-hosted.example", import.meta.url), "utf8"),
readFile(new URL("../package.json", import.meta.url), "utf8"),
]);
@@ -53,7 +53,7 @@ test("exposes authenticated KOC task, recovery, collection, and resource MCP too
assert.match(tokenService, /SHA-256/);
assert.match(tokenService, /expires_at > CURRENT_TIMESTAMP/);
assert.doesNotMatch(tokenService, /KOC_MCP_API_KEY/);
assert.match(migration, /CREATE TABLE `mcp_export_tokens`/);
assert.match(migration, /CREATE TABLE IF NOT EXISTS mcp_export_tokens/);
assert.match(migration, /mcp_export_tokens_expires_at_idx/);
assert.match(taskService, /readFeishuSource/);

130
tests/rendered-html.test.mjs
View File

@@ -19,8 +19,8 @@ test("builds the KOC LOOP product shell", async () => {
assert.match(adminApp, /获取KOC领取链接/);
assert.match(layout, /KOC LOOP｜内容分发闭环/);
assert.doesNotMatch(adminApp, /codex-preview|Your site is taking shape/);
await access(new URL("../dist/server/index.js", import.meta.url));
await access(new URL("../dist/client/assets", import.meta.url));
await access(new URL("../.next/standalone/server.js", import.meta.url));
await access(new URL("../.next/static", import.meta.url));
});
test("stacks user management and securely removes departed accounts", async () => {
@@ -40,12 +40,13 @@ test("stacks user management and securely removes departed accounts", async () =
assert.match(usersRoute, /DELETE FROM users WHERE id = \? AND role <> 'super_admin'/);
});
test("ships persistence, uploads, metadata, and no starter preview", async () => {
const [adminApp, layout, packageJson, hosting] = await Promise.all([
test("ships MySQL persistence, local uploads, metadata, and no starter preview", async () => {
const [adminApp, layout, packageJson, compose, objectStore] = await Promise.all([
readFile(new URL("../app/admin-app.tsx", import.meta.url), "utf8"),
readFile(new URL("../app/layout.tsx", import.meta.url), "utf8"),
readFile(new URL("../package.json", import.meta.url), "utf8"),
readFile(new URL("../.openai/hosting.json", import.meta.url), "utf8"),
readFile(new URL("../docker-compose.self-hosted.yml", import.meta.url), "utf8"),
readFile(new URL("../lib/object-store.ts", import.meta.url), "utf8"),
]);
assert.doesNotMatch(adminApp, /批量回填发布链接/);
@@ -54,8 +55,10 @@ test("ships persistence, uploads, metadata, and no starter preview", async () =>
assert.match(layout, /KOC LOOP｜内容分发闭环/);
assert.match(layout, /\/og\.png/);
assert.doesNotMatch(packageJson, /react-loading-skeleton/);
assert.match(hosting, /"d1": "DB"/);
assert.match(hosting, /"r2": "UPLOADS"/);
assert.match(packageJson, /"mysql2"/);
assert.match(compose, /mysql:8\.4/);
assert.match(compose, /upload_data:\/data\/koc\/uploads/);
assert.match(objectStore, /class LocalObjectStore/);
await access(new URL("../public/og.png", import.meta.url));
});
@@ -72,6 +75,8 @@ test("organizes distribution and recovery by task and imports the verified Feish
assert.match(adminApp, /选择一个数据回收任务/);
assert.match(adminApp, /读取表格/);
assert.match(adminApp, /当前仅展示/);
assert.match(adminApp, /release_distribution/);
assert.match(adminApp, /确认释放这篇笔记/);
assert.equal(snapshot.sheetId, "954953");
assert.equal(snapshot.rows.length, 61);
assert.equal(
@@ -140,8 +145,7 @@ test("issues external task links and supports one-to-one note submissions", asyn
assert.match(partnerRoute, /publicImageAssets/);
assert.match(partnerRoute, /withPartnerCors/);
assert.match(partnerRoute, /enrichDistributionAccount/);
assert.match(partnerRoute, /getRequestExecutionContext/);
assert.match(partnerRoute, /executionContext\.waitUntil\(enrichment\)/);
assert.match(partnerRoute, /runInBackground\(enrichment/);
assert.match(accountEnrichment, /profile_url = excluded\.profile_url/);
assert.match(accountEnrichment, /resolveXhsAccountProfileFromMcp/);
assert.match(accountEnrichment, /resolveXhsProfileDetailsFromMcp/);
@@ -163,6 +167,10 @@ test("issues external task links and supports one-to-one note submissions", asyn
assert.match(uploadRoute, /x-koc-distribution/);
assert.match(uploadRoute, /request\.arrayBuffer/);
assert.match(imageRoute, /content-assets\//);
assert.match(imageRoute, /publish-evidence\//);
assert.match(imageRoute, /creator-center\//);
assert.match(imageRoute, /imageKind === "publish"/);
assert.match(imageRoute, /imageKind === "creator"/);
assert.match(imageRoute, /cl\.claim_token/);
assert.match(imageUploadRoute, /isAdminRequest/);
assert.match(cors, /KOC_PORTAL_URL/);
@@ -171,6 +179,15 @@ test("issues external task links and supports one-to-one note submissions", asyn
assert.match(cors, /Access-Control-Allow-Origin/);
assert.match(adminApp, /hasCreatorMetrics/);
assert.match(adminApp, /待KOC填写数据/);
assert.match(adminApp, /AdminImageLightbox/);
assert.match(adminApp, /CreatorScreenshotPreview/);
assert.match(adminApp, /admin-image-lightbox/);
assert.match(adminApp, /aria-label="上一张"/);
assert.match(adminApp, /aria-label="下一张"/);
assert.match(adminApp, /event\.key === "ArrowLeft"/);
assert.match(adminApp, /event\.key === "ArrowRight"/);
assert.match(adminApp, /activeIndex \+ 1/);
assert.doesNotMatch(adminApp, /href=\{`\/api\/task-result-image/);
assert.match(creatorScreenshotRoute, /getUploadBucket/);
assert.match(creatorScreenshotRoute, /Content-Disposition/);
assert.match(creatorScreenshotRoute, /isAdminRequest/);
@@ -184,21 +201,21 @@ test("supports task collection schedules and latest public metrics", async () =>
actionRoute,
bootstrapRoute,
collectionService,
worker,
viteConfig,
scheduler,
nextConfig,
migration,
accountMigration,
deployConfig,
compose,
] = await Promise.all([
readFile(new URL("../app/admin-app.tsx", import.meta.url), "utf8"),
readFile(new URL("../app/api/action/route.ts", import.meta.url), "utf8"),
readFile(new URL("../app/api/bootstrap/route.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/collection-service.ts", import.meta.url), "utf8"),
readFile(new URL("../worker/index.ts", import.meta.url), "utf8"),
readFile(new URL("../vite.config.ts", import.meta.url), "utf8"),
readFile(new URL("../drizzle/0004_sharp_the_liberteens.sql", import.meta.url), "utf8"),
readFile(new URL("../drizzle/0005_foamy_sage.sql", import.meta.url), "utf8"),
readFile(new URL("../dist/server/wrangler.json", import.meta.url), "utf8"),
readFile(new URL("../lib/scheduler.ts", import.meta.url), "utf8"),
readFile(new URL("../next.config.ts", import.meta.url), "utf8"),
readFile(new URL("../mysql/0001_init.sql", import.meta.url), "utf8"),
readFile(new URL("../mysql/0001_init.sql", import.meta.url), "utf8"),
readFile(new URL("../docker-compose.self-hosted.yml", import.meta.url), "utf8"),
]);
for (const label of [
@@ -214,10 +231,19 @@ test("supports task collection schedules and latest public metrics", async () =>
}
assert.match(adminApp, /选择开始日期与采集日/);
assert.match(adminApp, /第1天到第7天/);
assert.match(adminApp, /SortableRecoveryHeader/);
assert.match(adminApp, /sortRecoveryDistributions/);
assert.match(adminApp, /aria-sort=/);
assert.match(adminApp, /direction === "asc" \? "↑" : "↓"/);
assert.match(adminApp, /sortWithNullsLast/);
assert.match(adminApp, /内容 \/ 发布账号/);
assert.match(adminApp, /recovery-title-link/);
assert.match(adminApp, /打开小红书笔记/);
assert.match(adminApp, /target="_blank"/);
assert.match(adminApp, /noopener noreferrer/);
assert.match(adminApp, /const noteUrl = xhsPublishUrl\(item\.publish_url\)/);
assert.match(adminApp, /noteUrl \? \(/);
assert.match(adminApp, /hostname === "xhslink\.cn"/);
assert.match(actionRoute, /save_collection_schedule/);
assert.match(actionRoute, /collect_now/);
assert.match(actionRoute, /backfill_account_profiles/);
@@ -233,17 +259,49 @@ test("supports task collection schedules and latest public metrics", async () =>
assert.match(collectionService, /runDueScheduledCollections/);
assert.match(collectionService, /retryFailedCollections/);
assert.match(collectionService, /exposure IS NOT NULL AND views IS NOT NULL/);
assert.match(collectionService, /等待第\$\{scheduleDay\}天 10:00自动采集/);
assert.match(collectionService, /等待第\$\{scheduleDay\}天 09:00自动采集/);
assert.match(collectionService, /isCollectionScheduleDue/);
assert.doesNotMatch(collectionService, /latestDueSchedule/);
assert.match(collectionService, /自动追采/);
assert.match(worker, /async scheduled/);
assert.match(worker, /backfillAccountProfiles/);
assert.match(scheduler, /0 9 \* \* \*/);
assert.match(scheduler, /backfillAccountProfiles/);
assert.match(scheduler, /Asia\/Shanghai/);
assert.match(bootstrapRoute, /backfillAccountProfiles/);
assert.match(viteConfig, /"0 2 \* \* \*"/);
assert.match(nextConfig, /output: "standalone"/);
assert.match(migration, /latest_likes/);
assert.match(migration, /collection_runs_distribution_date_idx/);
assert.match(accountMigration, /public_account_id/);
assert.match(deployConfig, /"crons":\["0 2 \* \* \*"\]/);
assert.match(compose, /ENABLE_SCHEDULER/);
});
test("supports fixed screenshot collection tasks without publish metrics", async () => {
const [adminApp, actionRoute, partnerRoute, uploadRoute, migration, multiMigration, exportRoute] =
await Promise.all([
readFile(new URL("../app/admin-app.tsx", import.meta.url), "utf8"),
readFile(new URL("../app/api/action/route.ts", import.meta.url), "utf8"),
readFile(new URL("../app/api/partner/route.ts", import.meta.url), "utf8"),
readFile(new URL("../app/api/partner-upload/route.ts", import.meta.url), "utf8"),
readFile(new URL("../mysql/0003_screenshot_tasks.sql", import.meta.url), "utf8"),
readFile(new URL("../mysql/0004_multi_result_screenshots.sql", import.meta.url), "utf8"),
readFile(new URL("../app/api/screenshot-task-export/route.ts", import.meta.url), "utf8"),
]);
assert.match(adminApp, /内容发布/);
assert.match(adminApp, /截图回收/);
assert.match(adminApp, /小红书搜索关键词/);
assert.match(adminApp, /批量下载截图/);
assert.match(actionRoute, /create_screenshot_task/);
assert.match(partnerRoute, /submit_screenshot_result/);
assert.match(partnerRoute, /截图回收任务无需填写发布链接/);
assert.match(uploadRoute, /task-results\//);
assert.match(uploadRoute, /task-result/);
assert.match(uploadRoute, /MAX_RESULT_SCREENSHOTS/);
assert.match(migration, /task_type/);
assert.match(migration, /result_screenshot_key/);
assert.match(multiMigration, /MODIFY COLUMN result_screenshot_key TEXT/);
assert.match(adminApp, /admin-result-gallery/);
assert.match(exportRoute, /回收清单\.csv/);
assert.match(exportRoute, /application\/zip/);
});
test("exports complete task recovery data to Excel with embedded images", async () => {
@@ -295,7 +353,7 @@ test("provides simple username-password login and three server-enforced roles",
readFile(new URL("../app/api/resources-export/route.ts", import.meta.url), "utf8"),
readFile(new URL("../db/schema.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/mvp-db.ts", import.meta.url), "utf8"),
readFile(new URL("../drizzle/0007_fantastic_sentinels.sql", import.meta.url), "utf8"),
readFile(new URL("../mysql/0001_init.sql", import.meta.url), "utf8"),
]);
assert.match(loginPage, /登录账号/);
@@ -321,9 +379,9 @@ test("provides simple username-password login and three server-enforced roles",
assert.match(bootstrapRoute, /accounts: \[\]/);
assert.match(resourceExportRoute, /isManagerRequest/);
assert.match(schema, /authSessions/);
assert.match(schema, /users_single_super_admin_idx/);
assert.match(schema, /backgroundJobs/);
assert.match(runtimeSchema, /CREATE TABLE IF NOT EXISTS auth_sessions/);
assert.match(migration, /CREATE TABLE `users`/);
assert.match(migration, /CREATE TABLE IF NOT EXISTS users/);
assert.match(migration, /users_single_super_admin_idx/);
});
@@ -349,6 +407,24 @@ test("filters and exports the current KOC resource result set", async () => {
assert.match(workbook, /relationships\/hyperlink/);
});
test("imports existing KOC resources through a validated spreadsheet preview", async () => {
const [adminApp, importRoute, resourceParser, accountMigration] = await Promise.all([
readFile(new URL("../app/admin-app.tsx", import.meta.url), "utf8"),
readFile(new URL("../app/api/resources-import/route.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/resource-import.ts", import.meta.url), "utf8"),
readFile(new URL("../mysql/0002_resource_import.sql", import.meta.url), "utf8"),
]);
assert.match(adminApp, /下载导入模板/);
assert.match(adminApp, /校验并预览/);
assert.match(adminApp, /确认导入/);
assert.match(importRoute, /isManagerRequest/);
assert.match(importRoute, /mode !== "commit"/);
assert.match(resourceParser, /RESOURCE_IMPORT_MAX_ROWS = 100/);
assert.match(resourceParser, /当前自动解析仅支持小红书账号主页/);
assert.match(importRoute, /resolveXhsProfileDetailsFromMcp/);
assert.match(accountMigration, /cooperation_source/);
});
test("supports anonymous partner delegation without creating a second data flow", async () => {
const [
adminApp,
@@ -367,7 +443,7 @@ test("supports anonymous partner delegation without creating a second data flow"
readFile(new URL("../lib/partner-cors.ts", import.meta.url), "utf8"),
readFile(new URL("../db/schema.ts", import.meta.url), "utf8"),
readFile(new URL("../lib/mvp-db.ts", import.meta.url), "utf8"),
readFile(new URL("../drizzle/0006_moaning_dark_phoenix.sql", import.meta.url), "utf8"),
readFile(new URL("../mysql/0001_init.sql", import.meta.url), "utf8"),
]);
assert.match(schema, /delegationBundles/);
@@ -380,7 +456,7 @@ test("supports anonymous partner delegation without creating a second data flow"
assert.match(partnerRoute, /findAccessibleAssignment/);
assert.match(partnerRoute, /b\.status = 'active'/);
assert.match(partnerRoute, /部分笔记刚刚已被转派/);
assert.match(partnerRoute, /分享链接只能用于查看和回填包内笔记/);
assert.match(partnerRoute, /分享链接只能用于查看和回填包内任务/);
assert.match(partnerRoute, /private, no-store/);
assert.match(uploadRoute, /x-koc-delegation/);
assert.match(uploadRoute, /delegation_bundles/);

117
tests/resource-import.test.mjs Normal file
View File

@@ -0,0 +1,117 @@
import assert from "node:assert/strict";
import test from "node:test";
import { buildRecoveryWorkbook } from "../lib/recovery-workbook.ts";
import {
mergeCooperationSources,
normalizeProfileUrl,
parseResourceFollowers,
parseResourceImportFile,
resourceImportMissingFields,
resourcePlatformUid,
} from "../lib/resource-import.ts";
test("parses CSV resources and normalizes public profile data", () => {
const csv = [
"账号主页,合作来源",
'"主页：https://www.xiaohongshu.com/user/profile/abc123?xsec_token=secret",林林KOC社群',
].join("\n");
const rows = parseResourceImportFile("resources.csv", new TextEncoder().encode(csv));
assert.equal(rows.length, 1);
assert.deepEqual(rows[0], {
rowNumber: 2,
platform: "小红书",
nickname: "",
publicAccountId: "",
profileUrl: "https://www.xiaohongshu.com/user/profile/abc123",
ipLocation: "",
followers: 0,
followersResolved: false,
cooperationSource: "林林KOC社群",
errors: [],
});
assert.equal(resourcePlatformUid(rows[0]), "abc123");
});
test("uses optional account fields directly and only requires the profile URL", () => {
const csv = [
"账号链接,账号昵称,账号ID,IP属地,粉丝数,合作来源",
"https://www.xiaohongshu.com/user/profile/abc123,番茄不炒蛋,4171542126,江西,10+,历史资源",
].join("\n");
const [row] = parseResourceImportFile(
"resources.csv",
new TextEncoder().encode(csv),
);
assert.equal(row.nickname, "番茄不炒蛋");
assert.equal(row.publicAccountId, "4171542126");
assert.equal(row.ipLocation, "江西");
assert.equal(row.followers, 10);
assert.equal(row.followersResolved, true);
assert.deepEqual(resourceImportMissingFields(row), []);
});
test("normalizes common follower formats and identifies missing enrichment fields", () => {
assert.deepEqual(parseResourceFollowers("1.3万"), {
value: 13_000,
resolved: true,
valid: true,
});
assert.deepEqual(parseResourceFollowers("10+"), {
value: 10,
resolved: true,
valid: true,
});
assert.deepEqual(parseResourceFollowers(""), {
value: 0,
resolved: false,
valid: true,
});
});
test("parses the first matching worksheet from an XLSX workbook", () => {
const workbook = buildRecoveryWorkbook({
sheetName: "KOC资源导入",
headers: ["账号主页", "合作来源"],
columnWidths: [48, 24],
rows: [
{
cells: ["https://www.xiaohongshu.com/user/profile/abc123", "存量资源包"],
images: [],
},
],
});
const rows = parseResourceImportFile("resources.xlsx", workbook);
assert.equal(rows[0].platform, "小红书");
assert.equal(rows[0].cooperationSource, "存量资源包");
assert.equal(rows[0].errors.length, 0);
assert.equal(rows[0].followersResolved, false);
});
test("reports invalid required fields without hiding valid rows", () => {
const csv = "账号主页,合作来源\n,历史资源\nhttps://www.douyin.com/user/demo,社群";
const rows = parseResourceImportFile("resources.csv", new TextEncoder().encode(csv));
assert.match(rows[0].errors.join("；"), /账号主页不能为空/);
assert.match(rows[1].errors.join("；"), /仅支持小红书账号主页/);
});
test("rejects invalid optional follower values without requiring other optional fields", () => {
const csv = [
"账号链接,粉丝数",
"https://www.xiaohongshu.com/user/profile/abc123,很多",
].join("\n");
const [row] = parseResourceImportFile(
"resources.csv",
new TextEncoder().encode(csv),
);
assert.match(row.errors.join("；"), /粉丝数格式不正确/);
});
test("normalizes profile URLs and merges cooperation sources", () => {
assert.equal(
normalizeProfileUrl("https://www.xiaohongshu.com/user/profile/abc/?foo=1#top"),
"https://www.xiaohongshu.com/user/profile/abc",
);
assert.equal(
mergeCooperationSources("林林社群、木子", "木子；历史表格"),
"林林社群、木子、历史表格",
);
});

31
tests/result-screenshots.test.mjs Normal file
View File

@@ -0,0 +1,31 @@
import assert from "node:assert/strict";
import test from "node:test";
import {
MAX_RESULT_SCREENSHOTS,
parseResultScreenshotKeys,
serializeResultScreenshotKeys,
} from "../lib/result-screenshots.ts";
test("keeps legacy task result screenshots readable", () => {
assert.deepEqual(
parseResultScreenshotKeys("task-results/dist-1/shot-1.png"),
["task-results/dist-1/shot-1.png"],
);
});
test("serializes multiple task screenshots safely and caps their count", () => {
const keys = Array.from(
{ length: MAX_RESULT_SCREENSHOTS + 3 },
(_, index) => `task-results/dist-1/shot-${index + 1}.png`,
);
const serialized = serializeResultScreenshotKeys([
...keys,
"content-assets/not-allowed.png",
]);
assert.equal(parseResultScreenshotKeys(serialized).length, MAX_RESULT_SCREENSHOTS);
assert.ok(
parseResultScreenshotKeys(serialized).every((key) =>
key.startsWith("task-results/"),
),
);
});

75
tests/self-hosted-runtime.test.mjs Normal file
View File

@@ -0,0 +1,75 @@
import assert from "node:assert/strict";
import { mkdtemp, readFile } from "node:fs/promises";
import os from "node:os";
import path from "node:path";
import test from "node:test";
import { DatabaseClient, normalizeSqlForMysql } from "../lib/database.ts";
import { LocalObjectStore, normalizeObjectKey } from "../lib/object-store.ts";
import { requestUsesHttps } from "../lib/user-auth.ts";
test("normalizes the limited SQLite syntax still used by business queries", () => {
assert.match(
normalizeSqlForMysql("INSERT OR IGNORE INTO collection_runs (id) VALUES (?)"),
/^INSERT IGNORE INTO/,
);
const upsert = normalizeSqlForMysql(
"INSERT INTO accounts (id, nickname) VALUES (?, ?) ON CONFLICT(id) DO UPDATE SET nickname = excluded.nickname",
);
assert.match(upsert, /ON DUPLICATE KEY UPDATE/);
assert.match(upsert, /nickname = VALUES\(nickname\)/);
assert.equal(
normalizeSqlForMysql("SELECT datetime(publish_time, '+7 days')"),
"SELECT DATE_ADD(publish_time, INTERVAL 7 DAY)",
);
});
test("normalizes ISO timestamps before binding to MySQL DATETIME", async () => {
let receivedParams;
const database = new DatabaseClient({
async execute(_sql, params) {
receivedParams = params;
return [{ affectedRows: 1, insertId: 0 }];
},
});
await database
.prepare("INSERT INTO auth_sessions (expires_at) VALUES (?)")
.bind("2026-08-11T02:03:04.567Z")
.run();
assert.deepEqual(receivedParams, ["2026-08-11 02:03:04.567"]);
});
test("marks login cookies secure behind the Nginx HTTPS proxy", () => {
assert.equal(
requestUsesHttps(
new Request("http://app:3000/api/auth/login", {
headers: { "x-forwarded-proto": "https" },
}),
),
true,
);
assert.equal(requestUsesHttps(new Request("http://localhost/login")), false);
});
test("stores uploaded objects under a safe persistent directory", async () => {
const directory = await mkdtemp(path.join(os.tmpdir(), "koc-object-store-"));
const store = new LocalObjectStore(directory);
await store.put("creator-center/dist-1/shot.png", Uint8Array.from([1, 2, 3]), {
httpMetadata: { contentType: "image/png" },
});
const object = await store.get("creator-center/dist-1/shot.png");
assert.ok(object);
assert.deepEqual([...new Uint8Array(await object.arrayBuffer())], [1, 2, 3]);
const headers = new Headers();
object.writeHttpMetadata(headers);
assert.equal(headers.get("Content-Type"), "image/png");
assert.equal(
JSON.parse(
await readFile(
path.join(directory, "creator-center/dist-1/shot.png.metadata.json"),
"utf8",
),
).contentType,
"image/png",
);
assert.throws(() => normalizeObjectKey("../secret"), /不安全/);
});

2
tsconfig.json
View File

@@ -30,5 +30,5 @@
".next/dev/types/**/*.ts",
"**/*.mts"
],
"exclude": ["node_modules"]
"exclude": ["node_modules", "koc-portal", "build", "worker", "examples"]
}

61
vite.config.ts
View File

@@ -1,61 +0,0 @@
import vinext from "vinext";
import { defineConfig } from "vite";
import hostingConfig from "./.openai/hosting.json";
import { sites } from "./build/sites-vite-plugin";
const SITE_CREATOR_PLACEHOLDER_DATABASE_ID =
"00000000-0000-4000-8000-000000000000";
const { d1, r2 } = hostingConfig;
// macOS Seatbelt blocks FSEvents, so Codex previews need polling for HMR.
const isCodexSeatbeltSandbox = process.env.CODEX_SANDBOX === "seatbelt";
const localBindingConfig = {
main: "./worker/index.ts",
compatibility_flags: ["nodejs_compat"],
// Cloudflare cron uses UTC. 02:00 UTC is 10:00 in Asia/Shanghai.
triggers: { crons: ["0 2 * * *"] },
d1_databases: d1
? [
{
binding: d1,
database_name: "site-creator-d1",
database_id: SITE_CREATOR_PLACEHOLDER_DATABASE_ID,
},
]
: [],
r2_buckets: r2
? [
{
binding: r2,
bucket_name: "site-creator-r2",
},
]
: [],
};
export default defineConfig(async () => {
// Keep Wrangler and Miniflare state project-local. These are non-secret tool
// settings; application environment belongs in ignored `.env*` files.
process.env.WRANGLER_WRITE_LOGS ??= "false";
process.env.WRANGLER_LOG_PATH ??= ".wrangler/logs";
process.env.MINIFLARE_REGISTRY_PATH ??= ".wrangler/registry";
// Wrangler snapshots its log path while the Cloudflare plugin is imported.
const { cloudflare } = await import("@cloudflare/vite-plugin");
return {
server: isCodexSeatbeltSandbox
? { watch: { useFsEvents: false, usePolling: true } }
: undefined,
plugins: [
vinext(),
sites(),
cloudflare({
viteEnvironment: { name: "rsc", childEnvironments: ["ssr"] },
config: localBindingConfig,
}),
],
};
});

85
worker/index.ts
View File

@@ -1,85 +0,0 @@
/** Cloudflare Worker entry point for the vinext-starter template. */
import { handleImageOptimization, DEFAULT_DEVICE_SIZES, DEFAULT_IMAGE_SIZES } from "vinext/server/image-optimization";
import handler from "vinext/server/app-router-entry";
import { backfillAccountProfiles } from "../lib/account-enrichment-service";
import { ensureSchema } from "../lib/mvp-db";
import { runScheduledCollections } from "../lib/collection-service";
import {
resolveCollectionMcpConfig,
type CollectionMcpBindings,
} from "../lib/mcp-collection-client";
import type { FeishuBindings } from "../lib/feishu-client";
interface Env extends CollectionMcpBindings, FeishuBindings {
ASSETS: Fetcher;
DB: D1Database;
UPLOADS: R2Bucket;
IMAGES: {
input(stream: ReadableStream): {
transform(options: Record<string, unknown>): {
output(options: { format: string; quality: number }): Promise<{ response(): Response }>;
};
};
};
}
interface ExecutionContext {
waitUntil(promise: Promise<unknown>): void;
passThroughOnException(): void;
}
interface ScheduledController {
scheduledTime: number;
cron: string;
noRetry(): void;
}
// Image security config. SVG sources with .svg extension auto-skip the
// optimization endpoint on the client side (served directly, no proxy).
// To route SVGs through the optimizer (with security headers), set
// dangerouslyAllowSVG: true in next.config.js and uncomment below:
// const imageConfig: ImageConfig = { dangerouslyAllowSVG: true };
const worker = {
async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
const url = new URL(request.url);
if (url.pathname === "/_vinext/image") {
const allowedWidths = [...DEFAULT_DEVICE_SIZES, ...DEFAULT_IMAGE_SIZES];
return handleImageOptimization(request, {
fetchAsset: (path) => env.ASSETS.fetch(new Request(new URL(path, request.url))),
transformImage: async (body, { width, format, quality }) => {
const result = await env.IMAGES.input(body).transform(width > 0 ? { width } : {}).output({ format, quality });
return result.response();
},
}, allowedWidths);
}
return handler.fetch(request, env, ctx);
},
async scheduled(
controller: ScheduledController,
env: Env,
ctx: ExecutionContext,
) {
ctx.waitUntil(
(async () => {
await ensureSchema(env.DB);
const mcpConfig = resolveCollectionMcpConfig(env);
await runScheduledCollections(
env.DB,
controller.scheduledTime,
mcpConfig,
);
const accountBackfill = await backfillAccountProfiles(
env.DB,
mcpConfig,
10,
);
console.info("KOC scheduled account backfill completed", accountBackfill);
})(),
);
},
};
export default worker;