update: dependency version

add: a useCaptcha hook

.dockerignore

@@ -0,0 +1,96 @@
+built/*
+tests/cases/rwc/*
+tests/cases/perf/*
+!tests/cases/webharness/compilerToString.js
+test-args.txt
+~*.docx
+\#*\#
+.\#*
+tests/baselines/local/*
+tests/baselines/local.old/*
+tests/services/baselines/local/*
+tests/baselines/prototyping/local/*
+tests/baselines/rwc/*
+tests/baselines/reference/projectOutput/*
+tests/baselines/local/projectOutput/*
+tests/baselines/reference/testresults.tap
+tests/baselines/symlinks/*
+tests/services/baselines/prototyping/local/*
+tests/services/browser/typescriptServices.js
+src/harness/*.js
+src/compiler/diagnosticInformationMap.generated.ts
+src/compiler/diagnosticMessages.generated.json
+src/parser/diagnosticInformationMap.generated.ts
+src/parser/diagnosticMessages.generated.json
+rwc-report.html
+*.swp
+build.json
+*.actual
+tests/webTestServer.js
+tests/webTestServer.js.map
+tests/webhost/*.d.ts
+tests/webhost/webtsc.js
+tests/cases/**/*.js
+tests/cases/**/*.js.map
+*.config
+scripts/eslint/built/
+scripts/debug.bat
+scripts/run.bat
+scripts/**/*.js
+scripts/**/*.js.map
+coverage/
+internal/
+**/.DS_Store
+.settings
+**/.vs
+**/.vscode/*
+!**/.vscode/tasks.json
+!**/.vscode/settings.template.json
+!**/.vscode/launch.template.json
+!**/.vscode/extensions.json
+!tests/cases/projects/projectOption/**/node_modules
+!tests/cases/projects/NodeModulesSearch/**/*
+!tests/baselines/reference/project/nodeModules*/**/*
+yarn.lock
+yarn-error.log
+.parallelperf.*
+tests/baselines/reference/dt
+.failed-tests
+TEST-results.xml
+package-lock.json
+.eslintcache
+*v8.log
+
+# dotenv environment variable files
+.env
+.env.development.local
+.env.test.local
+.env.production.local
+.env.local
+
+# npm dependencies
+node_modules/
+
+# project specific
+logs/
+__pycache__
+ml/filter/runs
+ml/pred/runs
+ml/pred/checkpoints
+ml/pred/observed
+ml/data/
+ml/filter/checkpoints
+scripts
+model/
+
+
+.astro
+
+# Database
+*.dump
+*.db
+*.sqlite
+*.sqlite3
+data/
+
+docker-compose.yml

.gitattributes

@@ -0,0 +1 @@
+*.woff2 filter=lfs diff=lfs merge=lfs -text

.gitignore

@@ -1,79 +1,18 @@
-built/*
-tests/cases/rwc/*
-tests/cases/perf/*
-!tests/cases/webharness/compilerToString.js
-test-args.txt
-~*.docx
-\#*\#
-.\#*
-tests/baselines/local/*
-tests/baselines/local.old/*
-tests/services/baselines/local/*
-tests/baselines/prototyping/local/*
-tests/baselines/rwc/*
-tests/baselines/reference/projectOutput/*
-tests/baselines/local/projectOutput/*
-tests/baselines/reference/testresults.tap
-tests/baselines/symlinks/*
-tests/services/baselines/prototyping/local/*
-tests/services/browser/typescriptServices.js
-src/harness/*.js
-src/compiler/diagnosticInformationMap.generated.ts
-src/compiler/diagnosticMessages.generated.json
-src/parser/diagnosticInformationMap.generated.ts
-src/parser/diagnosticMessages.generated.json
-rwc-report.html
-*.swp
-build.json
-*.actual
-tests/webTestServer.js
-tests/webTestServer.js.map
-tests/webhost/*.d.ts
-tests/webhost/webtsc.js
-tests/cases/**/*.js
-tests/cases/**/*.js.map
-*.config
-scripts/eslint/built/
-scripts/debug.bat
-scripts/run.bat
-scripts/**/*.js
-scripts/**/*.js.map
-coverage/
-internal/
 **/.DS_Store
 .settings
 **/.vs
 **/.vscode/*
-!**/.vscode/tasks.json
-!**/.vscode/settings.template.json
-!**/.vscode/launch.template.json
-!**/.vscode/extensions.json
-!tests/cases/projects/projectOption/**/node_modules
-!tests/cases/projects/NodeModulesSearch/**/*
-!tests/baselines/reference/project/nodeModules*/**/*
-yarn.lock
-yarn-error.log
-.parallelperf.*
-tests/baselines/reference/dt
-.failed-tests
-TEST-results.xml
-package-lock.json
+
 .eslintcache
 *v8.log
 
 # dotenv environment variable files
 .env
-.env.development.local
-.env.test.local
-.env.production.local
-.env.local
+.env.*
 
-# Fresh build directory
-_fresh/
 # npm dependencies
 node_modules/
 
-
 # project specific
 logs/
 __pycache__
@@ -85,3 +24,21 @@ ml/data/
 ml/filter/checkpoints
 scripts
 model/
+
+.astro
+
+# Database
+*.dump
+*.db
+*.sqlite
+*.sqlite3
+data/
+redis/
+
+# Build 
+dist/
+build/
+
+docker-compose.yml
+
+ucaptcha-config.yaml

.idea/.gitignore

@@ -7,3 +7,4 @@
 /dataSources/
 /dataSources.local.xml
 dataSources.xml
+MarsCodeWorkspaceAppSettings.xml

.idea/bun.xml

@@ -0,0 +1,6 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="BunSettings">
+    <option name="bunPath" value="$USER_HOME$/.bun/bin/bun" />
+  </component>
+</project>

.idea/codeStyles/Project.xml

@@ -0,0 +1,55 @@
+<component name="ProjectCodeStyleConfiguration">
+  <code_scheme name="Project" version="173">
+    <option name="LINE_SEPARATOR" value="&#10;" />
+    <HTMLCodeStyleSettings>
+      <option name="HTML_SPACE_INSIDE_EMPTY_TAG" value="true" />
+    </HTMLCodeStyleSettings>
+    <JSCodeStyleSettings version="0">
+      <option name="FORCE_SEMICOLON_STYLE" value="true" />
+      <option name="SPACE_BEFORE_FUNCTION_LEFT_PARENTH" value="false" />
+      <option name="FORCE_QUOTE_STYlE" value="true" />
+      <option name="ENFORCE_TRAILING_COMMA" value="Remove" />
+      <option name="SPACES_WITHIN_OBJECT_LITERAL_BRACES" value="true" />
+      <option name="SPACES_WITHIN_IMPORTS" value="true" />
+    </JSCodeStyleSettings>
+    <TypeScriptCodeStyleSettings version="0">
+      <option name="FORCE_SEMICOLON_STYLE" value="true" />
+      <option name="SPACE_BEFORE_FUNCTION_LEFT_PARENTH" value="false" />
+      <option name="FORCE_QUOTE_STYlE" value="true" />
+      <option name="ENFORCE_TRAILING_COMMA" value="Remove" />
+      <option name="SPACES_WITHIN_OBJECT_LITERAL_BRACES" value="true" />
+      <option name="SPACES_WITHIN_IMPORTS" value="true" />
+    </TypeScriptCodeStyleSettings>
+    <VueCodeStyleSettings>
+      <option name="INTERPOLATION_NEW_LINE_AFTER_START_DELIMITER" value="false" />
+      <option name="INTERPOLATION_NEW_LINE_BEFORE_END_DELIMITER" value="false" />
+    </VueCodeStyleSettings>
+    <codeStyleSettings language="HTML">
+      <option name="SOFT_MARGINS" value="120" />
+      <indentOptions>
+        <option name="CONTINUATION_INDENT_SIZE" value="4" />
+        <option name="USE_TAB_CHARACTER" value="true" />
+      </indentOptions>
+    </codeStyleSettings>
+    <codeStyleSettings language="JavaScript">
+      <option name="SOFT_MARGINS" value="120" />
+      <indentOptions>
+        <option name="USE_TAB_CHARACTER" value="true" />
+      </indentOptions>
+    </codeStyleSettings>
+    <codeStyleSettings language="TypeScript">
+      <option name="SOFT_MARGINS" value="120" />
+      <indentOptions>
+        <option name="USE_TAB_CHARACTER" value="true" />
+      </indentOptions>
+    </codeStyleSettings>
+    <codeStyleSettings language="Vue">
+      <option name="SOFT_MARGINS" value="120" />
+      <indentOptions>
+        <option name="INDENT_SIZE" value="4" />
+        <option name="TAB_SIZE" value="4" />
+        <option name="USE_TAB_CHARACTER" value="true" />
+      </indentOptions>
+    </codeStyleSettings>
+  </code_scheme>
+</component>

.idea/codeStyles/codeStyleConfig.xml

@@ -0,0 +1,5 @@
+<component name="ProjectCodeStyleConfiguration">
+  <state>
+    <option name="USE_PER_PROJECT_SETTINGS" value="true" />
+  </state>
+</component>

.idea/cvsa.iml

@@ -14,6 +14,22 @@
       <excludeFolder url="file://$MODULE_DIR$/logs" />
       <excludeFolder url="file://$MODULE_DIR$/model" />
       <excludeFolder url="file://$MODULE_DIR$/src/db" />
+      <excludeFolder url="file://$MODULE_DIR$/.idea" />
+      <excludeFolder url="file://$MODULE_DIR$/.vscode" />
+      <excludeFolder url="file://$MODULE_DIR$/.zed" />
+      <excludeFolder url="file://$MODULE_DIR$/packages/frontend/.astro" />
+      <excludeFolder url="file://$MODULE_DIR$/scripts" />
+      <excludeFolder url="file://$MODULE_DIR$/.astro" />
+      <excludeFolder url="file://$MODULE_DIR$/ml/pred/checkpoints" />
+      <excludeFolder url="file://$MODULE_DIR$/ml/pred/observed" />
+      <excludeFolder url="file://$MODULE_DIR$/ml/pred/runs" />
+      <excludeFolder url="file://$MODULE_DIR$/packages/backend/logs" />
+      <excludeFolder url="file://$MODULE_DIR$/packages/core/net/logs" />
+      <excludeFolder url="file://$MODULE_DIR$/packages/crawler/logs" />
+      <excludeFolder url="file://$MODULE_DIR$/data" />
+      <excludeFolder url="file://$MODULE_DIR$/redis" />
+      <excludeFolder url="file://$MODULE_DIR$/ml" />
+      <excludeFolder url="file://$MODULE_DIR$/src" />
     </content>
     <orderEntry type="inheritedJdk" />
     <orderEntry type="sourceFolder" forTests="false" />

.idea/deno.xml

@@ -0,0 +1,7 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="DenoSettings">
+    <option name="denoInit" value="{&#10;  &quot;enable&quot;: true,&#10;  &quot;lint&quot;: true,&#10;  &quot;unstable&quot;: true,&#10;  &quot;importMap&quot;: &quot;import_map.json&quot;,&#10;  &quot;config&quot;: &quot;deno.json&quot;,&#10;  &quot;fmt&quot;: {&#10;    &quot;useTabs&quot;: true,&#10;    &quot;lineWidth&quot;: 120,&#10;    &quot;indentWidth&quot;: 4,&#10;    &quot;semiColons&quot;: true,&#10;    &quot;proseWrap&quot;: &quot;always&quot;&#10;  }&#10;}" />
+    <option name="useDenoValue" value="DISABLE" />
+  </component>
+</project>

.idea/inspectionProfiles/Project_Default.xml

@@ -1,12 +1,36 @@
 <component name="InspectionProjectProfileManager">
   <profile version="1.0">
     <option name="myName" value="Project Default" />
+    <option name="scopesOrder">
+      <list>
+        <option value="Astro" />
+        <option value="All Changed Files" />
+        <option value="Open Files" />
+        <option value="Project Files" />
+        <option value="Scratches and Consoles" />
+        <option value="Tests" />
+      </list>
+    </option>
+    <inspection_tool class="ES6UnusedImports" enabled="true" level="WARNING" enabled_by_default="true">
+      <scope name="Astro" level="INFORMATION" enabled="false" editorAttributes="INFORMATION_ATTRIBUTES" />
+    </inspection_tool>
     <inspection_tool class="GrazieInspection" enabled="false" level="GRAMMAR_ERROR" enabled_by_default="false" />
+    <inspection_tool class="HtmlUnknownAttribute" enabled="true" level="WARNING" enabled_by_default="true">
+      <option name="myValues">
+        <value>
+          <list size="1">
+            <item index="0" class="java.lang.String" itemvalue="autocorrect" />
+          </list>
+        </value>
+      </option>
+      <option name="myCustomValuesEnabled" value="true" />
+    </inspection_tool>
     <inspection_tool class="LanguageDetectionInspection" enabled="false" level="WARNING" enabled_by_default="false" />
     <inspection_tool class="SpellCheckingInspection" enabled="false" level="TYPO" enabled_by_default="false">
       <option name="processCode" value="true" />
       <option name="processLiterals" value="true" />
       <option name="processComments" value="true" />
     </inspection_tool>
+    <inspection_tool class="TsLint" enabled="true" level="WARNING" enabled_by_default="true" />
   </profile>
 </component>

.idea/scopes/Astro.xml

@@ -0,0 +1,3 @@
+<component name="DependencyValidationManager">
+  <scope name="Astro" pattern="file:*.astro" />
+</component>

.prettierrc

@@ -0,0 +1,8 @@
+{
+	"useTabs": true,
+	"tabWidth": 4,
+	"trailingComma": "none",
+	"singleQuote": false,
+	"printWidth": 120,
+	"endOfLine": "lf"
+}

.tokeignore

@@ -4,3 +4,8 @@ data
 *.txt
 *.md
 *config*
+Inter.css
+MiSans.css
+*.yaml
+*.yml
+*.mdx

.vscode/extensions.json

@@ -1,6 +0,0 @@
-{
-	"recommendations": [
-		"denoland.vscode-deno",
-		"bradlc.vscode-tailwindcss"
-	]
-}

.zed/settings.json

@@ -1,35 +0,0 @@
-// Folder-specific settings
-//
-// For a full list of overridable settings, and general information on folder-specific settings,
-// see the documentation: https://zed.dev/docs/configuring-zed#settings-files
-{
-	"lsp": {
-		"deno": {
-			"settings": {
-				"deno": {
-					"enable": true
-				}
-			}
-		}
-	},
-	"languages": {
-		"TypeScript": {
-			"language_servers": [
-				"deno",
-				"!typescript-language-server",
-				"!vtsls",
-				"!eslint"
-			],
-			"formatter": "language_server"
-		},
-		"TSX": {
-			"language_servers": [
-				"deno",
-				"!typescript-language-server",
-				"!vtsls",
-				"!eslint"
-			],
-			"formatter": "language_server"
-		}
-	}
-}

Dockerfile.backend

@@ -0,0 +1,23 @@
+FROM oven/bun:1.2.8-debian
+
+WORKDIR /app
+
+COPY ./packages/core ./core
+
+COPY ./packages/backend/package.json ./packages/backend/bun.lock ./backend/
+
+RUN apt update && apt install -y curl
+
+RUN ln -s /bin/uname /usr/bin/uname
+
+RUN /bin/bash -c "$(curl -fsSL https://aliyuncli.alicdn.com/install.sh)"
+
+WORKDIR backend
+
+RUN bun install
+
+COPY ./packages/backend/ .
+
+RUN mkdir -p /app/logs
+
+CMD ["bun", "start"]

Dockerfile.crawler

@@ -0,0 +1,19 @@
+FROM oven/bun:1.2.8-debian
+
+WORKDIR /app
+
+COPY . .
+
+RUN bun i
+
+RUN mkdir -p /app/logs
+
+RUN apt update && apt install -y curl
+
+RUN ln -s /bin/uname /usr/bin/uname
+
+RUN /bin/bash -c "$(curl -fsSL https://aliyuncli.alicdn.com/install.sh)"
+
+WORKDIR packages/crawler
+
+CMD ["bun", "all"]

Dockerfile.frontend

@@ -0,0 +1,23 @@
+FROM oven/bun
+
+ARG BACKEND_URL
+
+WORKDIR /app
+
+COPY . .
+
+RUN bun install
+
+WORKDIR packages/frontend
+
+RUN bun run build
+
+ENV HOST=0.0.0.0
+ENV PORT=4321
+ENV BACKEND_URL=${BACKEND_URL}
+
+EXPOSE 4321
+
+RUN mkdir -p /app/logs
+
+CMD ["bun", "/app/packages/frontend/dist/server/entry.mjs"]

Dockerfile.next

@@ -0,0 +1,14 @@
+FROM node:lts-slim AS production
+
+WORKDIR /app
+
+COPY ./packages/next/.next ./.next
+COPY ./packages/next/public ./public
+COPY ./packages/next/package.json ./package.json
+COPY ./packages/next/node_modules ./node_modules
+
+ENV NODE_ENV production
+
+EXPOSE 7400
+
+CMD ["npm", "start"]

deno.json

@@ -1,22 +0,0 @@
-{
-	"lock": false,
-	"workspace": ["./packages/crawler", "./packages/frontend", "./packages/backend", "./packages/core"],
-	"nodeModulesDir": "auto",
-	"tasks": {
-		"crawler": "deno task --filter 'crawler' all",
-		"backend": "deno task --filter 'backend' start"
-	},
-	"fmt": {
-		"useTabs": true,
-		"lineWidth": 120,
-		"indentWidth": 4,
-		"semiColons": true,
-		"proseWrap": "always"
-	},
-	"imports": {
-		"@astrojs/node": "npm:@astrojs/node@^9.1.3",
-		"@astrojs/svelte": "npm:@astrojs/svelte@^7.0.8",
-		"@core/db/": "./packages/core/db/",
-		"date-fns": "npm:date-fns@^4.1.0"
-	}
-}

doc/en/SUMMARY.md

@@ -1,21 +1,21 @@
 # Table of contents
 
-- [Welcome](README.md)
+* [Welcome](README.md)
 
 ## About
 
-- [About CVSA Project](about/this-project.md)
-- [Scope of Inclusion](about/scope-of-inclusion.md)
+* [About CVSA Project](about/this-project.md)
+* [Scope of Inclusion](about/scope-of-inclusion.md)
 
 ## Architecure
 
 * [Overview](architecure/overview.md)
+* [Crawler](architecure/crawler.md)
 * [Database Structure](architecure/database-structure/README.md)
   * [Type of Song](architecure/database-structure/type-of-song.md)
-* [Message Queue](architecure/message-queue.md)
 * [Artificial Intelligence](architecure/artificial-intelligence.md)
 
 ## API Doc
 
-- [Catalog](api-doc/catalog.md)
-- [Songs](api-doc/songs.md)
+* [Catalog](api-doc/catalog.md)
+* [Songs](api-doc/songs.md)

doc/en/about/scope-of-inclusion.md

@@ -7,23 +7,34 @@ For a **song**, it must meet the following conditions to be included in CVSA:
 
 ### Category 30
 
-In principle, the songs must be featured in a video that is categorized under the VOCALOID·UTAU (ID 30) category in [Bilibili](https://en.wikipedia.org/wiki/Bilibili) in order to be observed by our [automation program](../architecure/overview.md#crawler). We welcome editors to manually add songs that have not been uploaded to bilibili / categorized under this category.
+In principle, the songs must be featured in a video that is categorized under the VOCALOID·UTAU (ID 30) category in
+[Bilibili](https://en.wikipedia.org/wiki/Bilibili) in order to be observed by our
+[automation program](../architecure/overview.md#crawler). We welcome editors to manually add songs that have not been
+uploaded to bilibili / categorized under this category.
 
 #### NEWS
 
-Recently, Bilibili seems to be offlining the sub-category. This means the VOCALOID·UTAU category can no longer be entered from the frontend, and producers can no longer upload videos to this category (instead, they can only choose the parent category "Music"). 
+Recently, Bilibili seems to be offlining the sub-category. This means the VOCALOID·UTAU category can no longer be
+entered from the frontend, and producers can no longer upload videos to this category (instead, they can only choose the
+parent category "Music"). 
 
-According to our experiments, Bilibili still retains the code logic of sub-categories in the backend, and newly published songs may still be in the VOCALOID·UTAU sub-category, and the related APIs can still work normally. However, there are [reports](https://www.bilibili.com/opus/1041223385394184199) that some of the new songs have been placed under the "Music General" sub-category.\
-We are still waiting for Bilibili's follow-up actions, and in the future, we may adjust the scope of our automated program's crawling.
+According to our experiments, Bilibili still retains the code logic of sub-categories in the backend, and newly
+published songs may still be in the VOCALOID·UTAU sub-category, and the related APIs can still work normally. However,
+there are [reports](https://www.bilibili.com/opus/1041223385394184199) that some of the new songs have been placed under
+the "Music General" sub-category.\
+We are still waiting for Bilibili's follow-up actions, and in the future, we may adjust the scope of our automated
+program's crawling.
 
 ### At Leats One Line of Chinese / Chinese Virtual Singer
 
-The lyrics of the song must contain at least one line in Chinese. Otherwise, if the lyrics of the song do not contain Chinese,  it will only be included in the CVSA only if a Chinese virtual singer has been used.
+The lyrics of the song must contain at least one line in Chinese. Otherwise, if the lyrics of the song do not contain
+Chinese, it will only be included in the CVSA only if a Chinese virtual singer has been used.
 
 We define a **Chinese virtual singer** as follows:
 
 1. The singer primarily uses Chinese voicebank (i.e. the most widely used voickbank for the singer is Chinese)
-2. The singer is operated by a company, organization, individual or group located in Mainland China, Hong Kong, Macau or Taiwan.
+2. The singer is operated by a company, organization, individual or group located in Mainland China, Hong Kong, Macau or
+   Taiwan.
 
 ### Using Vocal Synthesizer
 

doc/en/architecure/artificial-intelligence.md

@@ -9,10 +9,13 @@ The AI ​​systems we currently use are:
 Located at `/filter/` under project root dir, it classifies a video in the
 [category 30](../about/scope-of-inclusion.md#category-30) into the following categories:
 
-* 0: Not related to Chinese vocal synthesis
-* 1: A original song with Chinese vocal synthesis
-* 2: A cover/remix song with Chinese vocal synthesis
+- 0: Not related to Chinese vocal synthesis
+- 1: A original song with Chinese vocal synthesis
+- 2: A cover/remix song with Chinese vocal synthesis
 
 ### The Predictor
 
-Located at `/pred/`under the project root dir, it predicts the future views of a video. This is a regression model that takes historical view trends of a video, other contextual information (such as the current time), and future time points to be predicted as feature inputs, and outputs the increment in the video's view count from "now" to the specified future time point.
+Located at `/pred/`under the project root dir, it predicts the future views of a video. This is a regression model that
+takes historical view trends of a video, other contextual information (such as the current time), and future time points
+to be predicted as feature inputs, and outputs the increment in the video's view count from "now" to the specified
+future time point.

doc/en/architecure/crawler.md

@@ -0,0 +1,4 @@
+# Crawler
+
+A central aspect of CVSA's technical design is its emphasis on automation. The data collection process within the `crawler` is orchestrated using a message queue powered by [BullMQ](https://bullmq.io/). This enables concurrent processing of various tasks involved in the data lifecycle. State management and data persistence are handled by a combination of Redis for caching and real-time data, and PostgreSQL as the primary database.
+

doc/en/architecure/database-structure/README.md

@@ -5,10 +5,11 @@ CVSA uses [PostgreSQL](https://www.postgresql.org/) as our database.
 All public data of CVSA (excluding users' personal data) is stored in a database named `cvsa_main`, which contains the
 following tables:
 
-* songs: stores the main information of songs
-* bili\_user: stores snapshots of Bilibili user information
-* all\_data: metadata of all videos in [category 30](../../about/scope-of-inclusion.md#category-30).
-* labelling\_result: Contains label of videos in `all_data`tagged by our [AI system](../artificial-intelligence.md#the-filter).
-* video\_snapshot: Statistical data of videos that are fetched regularly (e.g., number of views, etc.), we call this fetch process as "snapshot".
-* snapshot\_schedule: The scheduling information for video snapshots.
-
+- songs: stores the main information of songs
+- bili\_user: stores snapshots of Bilibili user information
+- all\_data: metadata of all videos in [category 30](../../about/scope-of-inclusion.md#category-30).
+- labelling\_result: Contains label of videos in `all_data`tagged by our
+  [AI system](../artificial-intelligence.md#the-filter).
+- video\_snapshot: Statistical data of videos that are fetched regularly (e.g., number of views, etc.), we call this
+  fetch process as "snapshot".
+- snapshot\_schedule: The scheduling information for video snapshots.

doc/en/architecure/message-queue.md

@@ -1,7 +0,0 @@
-# Message Queue
-
-We rely on message queues to manage the various tasks that [the cralwer ](overview.md#crawler)needs to perform.
-
-### Code Path
-
-Currently, the code related to message queues are located at `lib/mq` and `src`.

doc/en/architecure/overview.md

@@ -14,14 +14,29 @@ layout:
 
 # Overview
 
-The whole CVSA system can be sperate into three different parts:
+The CVSA is a [monorepo](https://en.wikipedia.org/wiki/Monorepo) codebase, mainly using TypeScript as the development language. With [Deno workspace](https://docs.deno.com/runtime/fundamentals/workspaces/), the major part of the codebase is under `packages/`. 
 
-* Frontend
-* API
-* Crawler
+**Project structure:**
 
-The frontend is driven by [Astro](https://astro.build/) and is used to display the final CVSA page. The API is driven by [Hono](https://hono.dev) and is used to query the database and provide REST/GraphQL APIs that can be called by out website, applications, or third parties. The crawler is our automatic data collector, used to automatically collect new songs from bilibili, track their statistics, etc.
+```
+cvsa
+├── deno.json
+├── packages
+│   ├── backend
+│   ├── core
+│   ├── crawler
+│   └── frontend
+└── README.md
+```
+
+**Package Breakdown:**
+
+* **`backend`**: This package houses the server-side logic, built with the [Hono](https://hono.dev/) web framework. It's responsible for interacting with the database and exposing data through REST and GraphQL APIs for consumption by the frontend, internal applications, and third-party developers.
+* **`frontend`**: The user-facing web interface of CVSA is developed using [Astro](https://astro.build/). This package handles the presentation layer, displaying information fetched from the database.
+* **`crawler`**: This automated data collection system is a key component of CVSA. It's designed to automatically discover and gather new song data from bilibili, as well as track relevant statistics over time.
+* **`core`**: This package contains reusable and generic code that is utilized across multiple workspaces within the CVSA monorepo.
 
 ### Crawler
 
-Automation is the biggest highlight of CVSA's technical design. To achieve this, we use a message queue powered by [BullMQ](https://bullmq.io/) to concurrently process various tasks in the data collection life cycle.
+Automation is the biggest highlight of CVSA's technical design. The data collection process within the `crawler` is orchestrated using a message queue powered by [BullMQ](https://bullmq.io/). This enables concurrent processing of various tasks involved in the data collection lifecycle. State management and data persistence are handled by a combination of Redis for caching and real-time data, and PostgreSQL as the primary database.
+

doc/zh/.gitbook/assets/1.yaml

@@ -0,0 +1,106 @@
+openapi: 3.0.0
+info:
+  title: CVSA API
+  version: v1
+
+servers:
+  - url: https://api.projectcvsa.com
+
+paths:
+  /video/{id}/snapshots:
+    get:
+      summary: 获取视频快照列表
+      description: 根据视频 ID 获取视频的快照列表。视频 ID 可以是以 "av" 开头的数字，以 "BV" 开头的 12 位字母数字，或者一个正整数。
+      parameters:
+        - in: path
+          name: id
+          required: true
+          schema:
+            type: string
+          description: "视频 ID (如: av78977256, BV1KJ411C7CW, 78977256)"
+        - in: query
+          name: ps
+          schema:
+            type: integer
+            minimum: 1
+          description: 每页返回的快照数量 (pageSize)，默认为 1000。
+        - in: query
+          name: pn
+          schema:
+            type: integer
+            minimum: 1
+          description: 页码 (pageNumber)，用于分页查询。offset 与 pn 只能选择一个。
+        - in: query
+          name: offset
+          schema:
+            type: integer
+            minimum: 1
+          description: 偏移量，用于基于偏移量的查询。offset 与 pn 只能选择一个。
+        - in: query
+          name: reverse
+          schema:
+            type: boolean
+          description: 是否反向排序（从旧到新），默认为 false。
+      responses:
+        '200':
+          description: 成功获取快照列表
+          content:
+            application/json:
+              schema:
+                type: array
+                items:
+                  type: object
+                  properties:
+                    id:
+                      type: integer
+                      description: 快照 ID
+                    aid:
+                      type: integer
+                      description: 视频的 av 号
+                    views:
+                      type: integer
+                      description: 视频播放量
+                    coins:
+                      type: integer
+                      description: 视频投币数
+                    likes:
+                      type: integer
+                      description: 视频点赞数
+                    favorites:
+                      type: integer
+                      description: 视频收藏数
+                    shares:
+                      type: integer
+                      description: 视频分享数
+                    danmakus:
+                      type: integer
+                      description: 视频弹幕数
+                    replies:
+                      type: integer
+                      description: 视频评论数
+        '400':
+          description: 无效的查询参数
+          content:
+            application/json:
+              schema:
+                type: object
+                properties:
+                  message:
+                    type: string
+                    description: 错误消息
+                  errors:
+                    type: object
+                    description: 详细的错误信息
+        '500':
+          description: 服务器内部错误
+          content:
+            application/json:
+              schema:
+                type: object
+                properties:
+                  message:
+                    type: string
+                    description: 错误消息
+                  error:
+                    type: object
+                    description: 详细的错误信息

doc/zh/SUMMARY.md

@@ -1,22 +1,22 @@
 # Table of contents
 
-- [欢迎](README.md)
+* [欢迎](README.md)
 
 ## 关于 <a href="#about" id="about"></a>
 
-- [关于本项目](about/this-project.md)
-- [收录范围](about/scope-of-inclusion.md)
+* [关于本项目](about/this-project.md)
+* [收录范围](about/scope-of-inclusion.md)
 
 ## 技术架构 <a href="#architecture" id="architecture"></a>
 
-* [概览](architecture/overview.md)
-* [数据库结构](architecture/database-structure/README.md)
-  * [歌曲类型](architecture/database-structure/type-of-song.md)
-* [人工智能](architecture/artificial-intelligence.md)
-* [消息队列](architecture/message-queue/README.md)
-  * [LatestVideosQueue 队列](architecture/message-queue/latestvideosqueue-dui-lie.md)
+- [概览](architecture/overview.md)
+- [数据库结构](architecture/database-structure/README.md)
+  - [歌曲类型](architecture/database-structure/type-of-song.md)
+- [人工智能](architecture/artificial-intelligence.md)
+- [消息队列](architecture/message-queue/README.md)
+  - [LatestVideosQueue 队列](architecture/message-queue/latestvideosqueue-dui-lie.md)
 
 ## API 文档 <a href="#api-doc" id="api-doc"></a>
 
-- [目录](api-doc/catalog.md)
-- [歌曲](api-doc/songs.md)
+* [目录](api-doc/catalog.md)
+* [视频快照](api-doc/video-snapshot.md)

doc/zh/api-doc/catalog.md

@@ -1,3 +1,4 @@
 # 目录
 
-- [歌曲](songs.md)
+* [视频快照](video-snapshot.md)
+

doc/zh/api-doc/songs.md

@@ -1,3 +0,0 @@
-# 歌曲
-
-暂未实现。

doc/zh/api-doc/video-snapshot.md

@@ -0,0 +1,6 @@
+# 视频快照
+
+{% openapi src="../.gitbook/assets/1.yaml" path="/video/{id}/snapshots" method="get" %}
+[1.yaml](../.gitbook/assets/1.yaml)
+{% endopenapi %}
+

doc/zh/architecture/database-structure/README.md

@@ -2,13 +2,14 @@
 
 CVSA 使用 [PostgreSQL](https://www.postgresql.org/) 作为数据库。
 
+CVSA 设计了两个
+
 CVSA 的所有公开数据（不包括用户的个人数据）都存储在名为 `cvsa_main` 的数据库中，该数据库包含以下表：
 
-* songs：存储歌曲的主要信息
-* bilibili\_user：存储 Bilibili 用户信息快照
-* bilibili\_metadata：[分区 30](../../about/scope-of-inclusion.md#vocaloiduatu-fen-qu) 中所有视频的元数据
-* labelling\_result：包含由我们的 AI 系统 标记的 `all_data` 中视频的标签。
-* latest\_video\_snapshot：存储视频最新的快照
-* video\_snapshot：存储视频的快照，包括特定时间下视频的统计信息（播放量、点赞数等）
-* snapshot\_schedule：视频快照的规划信息，为辅助表
-
+- songs：存储歌曲的主要信息
+- bilibili\_user：存储 Bilibili 用户信息快照
+- bilibili\_metadata：[分区 30](../../about/scope-of-inclusion.md#vocaloiduatu-fen-qu) 中所有视频的元数据
+- labelling\_result：包含由我们的 AI 系统 标记的 `all_data` 中视频的标签。
+- latest\_video\_snapshot：存储视频最新的快照
+- video\_snapshot：存储视频的快照，包括特定时间下视频的统计信息（播放量、点赞数等）
+- snapshot\_schedule：视频快照的规划信息，为辅助表

doc/zh/architecture/message-queue/latestvideosqueue-dui-lie.md

@@ -1,2 +1 @@
 # LatestVideosQueue 队列
-

doc/zh/architecture/overview.md

@@ -20,8 +20,7 @@ layout:
 
 位于项目目录`packages/crawler` 下，它负责以下工作：
 
-* 抓取新的视频并收录作品
-* 持续监控视频的播放量等统计信息
+- 抓取新的视频并收录作品
+- 持续监控视频的播放量等统计信息
 
 整个 crawler 由 BullMQ 消息队列驱动，使用 Redis 和 PostgreSQL 管理状态。
-

docker-compose.example.yml

@@ -0,0 +1,71 @@
+version: '3.8'
+
+services:
+  db:
+    image: postgres:17
+    ports:
+      - "5431:5432"
+    environment:
+      POSTGRES_USER: cvsa
+      POSTGRES_PASSWORD: ""
+      POSTGRES_DB: cvsa_main
+    volumes:
+      - ./data:/var/lib/postgresql/data
+  
+  redis:
+    image: redis:latest
+    ports:
+      - "6378:6379"
+    volumes:
+      - ./redis/data:/data
+      - ./redis/redis.conf:/usr/local/etc/redis/redis.conf
+      - ./redis/logs:/logs
+
+  frontend:
+    build:
+      context: .
+      dockerfile: Dockerfile.frontend
+    ports:
+      - "4321:4321"
+    environment:
+      - HOST=0.0.0.0
+      - PORT=4321
+      - DB_HOST=db
+      - DB_NAME=cvsa_main
+      - DB_NAME_CRED=cvsa_cred
+      - DB_USER=cvsa
+      - DB_PORT=5432
+      - DB_PASSWORD=""
+      - LOG_VERBOSE=/app/logs/verbose.log
+      - LOG_WARN=/app/logs/warn.log
+      - LOG_ERR=/app/logs/error.log
+    depends_on:
+      - db
+    volumes:
+      - /path/to/your/logs:/app/logs
+  backend:
+    build:
+      context: .
+      dockerfile: Dockerfile.backend
+    ports:
+      - "8000:8000"
+    environment:
+      - HOST=0.0.0.0
+      - DB_HOST=db
+      - DB_NAME=cvsa_main
+      - DB_NAME_CRED=cvsa_cred
+      - DB_USER=cvsa
+      - DB_PORT=5432
+      - DB_PASSWORD=""
+      - LOG_VERBOSE=/app/logs/verbose.log
+      - LOG_WARN=/app/logs/warn.log
+      - LOG_ERR=/app/logs/error.log
+      - REDIS_HOST=redis
+      - REDIS_PORT=6379
+    depends_on:
+      - db
+    volumes:
+      - /path/to/your/logs:/app/logs
+
+volumes:
+  db_data:

package.json

@@ -0,0 +1,23 @@
+{
+	"name": "cvsa",
+	"version": "3.15.34",
+	"private": false,
+	"type": "module",
+	"workspaces": [
+		"packages/frontend",
+		"packages/core",
+		"packages/backend",
+		"packages/crawler"
+	],
+	"dependencies": {
+		"arg": "^5.0.2",
+		"postgres": "^3.4.5"
+	},
+	"devDependencies": {
+		"@types/bun": "^1.2.15",
+		"prettier": "^3.5.3",
+		"vite-tsconfig-paths": "^5.1.4",
+		"vitest": "^3.1.2",
+		"vitest-tsconfig-paths": "^3.4.1"
+	}
+}

packages/backend/.prettierrc

@@ -0,0 +1,8 @@
+{
+	"useTabs": true,
+	"tabWidth": 4,
+	"trailingComma": "none",
+	"singleQuote": false,
+	"printWidth": 120,
+	"endOfLine": "lf"
+}

packages/backend/bun.lock

@@ -0,0 +1,208 @@
+{
+  "lockfileVersion": 1,
+  "workspaces": {
+    "": {
+      "dependencies": {
+        "@rabbit-company/argon2id": "^2.1.0",
+        "hono": "^4.7.8",
+        "hono-rate-limiter": "^0.4.2",
+        "ioredis": "^5.6.1",
+        "postgres": "^3.4.5",
+        "rate-limit-redis": "^4.2.0",
+        "yup": "^1.6.1",
+        "zod": "^3.24.3",
+      },
+      "devDependencies": {
+        "@types/bun": "^1.2.11",
+        "prettier": "^3.5.3",
+      },
+    },
+  },
+  "packages": {
+    "@ioredis/commands": ["@ioredis/commands@1.2.0", "", {}, "sha512-Sx1pU8EM64o2BrqNpEO1CNLtKQwyhuXuqyfH7oGKCk+1a33d2r5saW8zNwm3j6BTExtjrv2BxTgzzkMwts6vGg=="],
+
+    "@rabbit-company/argon2id": ["@rabbit-company/argon2id@2.1.0", "", { "peerDependencies": { "typescript": "^5.6.2" } }, "sha512-X/kt89qjmS9+Zh+DYCGcWeTwHa4C8vY8T3EnSma+vWj7spMzAYX4F8vmGUkny9hygpTOeC/yXwAUdJAfJ52H+w=="],
+
+    "@types/bun": ["@types/bun@1.2.11", "", { "dependencies": { "bun-types": "1.2.11" } }, "sha512-ZLbbI91EmmGwlWTRWuV6J19IUiUC5YQ3TCEuSHI3usIP75kuoA8/0PVF+LTrbEnVc8JIhpElWOxv1ocI1fJBbw=="],
+
+    "@types/node": ["@types/node@22.15.3", "", { "dependencies": { "undici-types": "~6.21.0" } }, "sha512-lX7HFZeHf4QG/J7tBZqrCAXwz9J5RD56Y6MpP0eJkka8p+K0RY/yBTW7CYFJ4VGCclxqOLKmiGP5juQc6MKgcw=="],
+
+    "accepts": ["accepts@2.0.0", "", { "dependencies": { "mime-types": "^3.0.0", "negotiator": "^1.0.0" } }, "sha512-5cvg6CtKwfgdmVqY1WIiXKc3Q1bkRqGLi+2W/6ao+6Y7gu/RCwRuAhGEzh5B4KlszSuTLgZYuqFqo5bImjNKng=="],
+
+    "body-parser": ["body-parser@2.2.0", "", { "dependencies": { "bytes": "^3.1.2", "content-type": "^1.0.5", "debug": "^4.4.0", "http-errors": "^2.0.0", "iconv-lite": "^0.6.3", "on-finished": "^2.4.1", "qs": "^6.14.0", "raw-body": "^3.0.0", "type-is": "^2.0.0" } }, "sha512-02qvAaxv8tp7fBa/mw1ga98OGm+eCbqzJOKoRt70sLmfEEi+jyBYVTDGfCL/k06/4EMk/z01gCe7HoCH/f2LTg=="],
+
+    "bun-types": ["bun-types@1.2.11", "", { "dependencies": { "@types/node": "*" } }, "sha512-dbkp5Lo8HDrXkLrONm6bk+yiiYQSntvFUzQp0v3pzTAsXk6FtgVMjdQ+lzFNVAmQFUkPQZ3WMZqH5tTo+Dp/IA=="],
+
+    "bytes": ["bytes@3.1.2", "", {}, "sha512-/Nf7TyzTx6S3yRJObOAV7956r8cr2+Oj8AC5dt8wSP3BQAoeX58NoHyCU8P8zGkNXStjTSi6fzO6F0pBdcYbEg=="],
+
+    "call-bind-apply-helpers": ["call-bind-apply-helpers@1.0.2", "", { "dependencies": { "es-errors": "^1.3.0", "function-bind": "^1.1.2" } }, "sha512-Sp1ablJ0ivDkSzjcaJdxEunN5/XvksFJ2sMBFfq6x0ryhQV/2b/KwFe21cMpmHtPOSij8K99/wSfoEuTObmuMQ=="],
+
+    "call-bound": ["call-bound@1.0.4", "", { "dependencies": { "call-bind-apply-helpers": "^1.0.2", "get-intrinsic": "^1.3.0" } }, "sha512-+ys997U96po4Kx/ABpBCqhA9EuxJaQWDQg7295H4hBphv3IZg0boBKuwYpt4YXp6MZ5AmZQnU/tyMTlRpaSejg=="],
+
+    "cluster-key-slot": ["cluster-key-slot@1.1.2", "", {}, "sha512-RMr0FhtfXemyinomL4hrWcYJxmX6deFdCxpJzhDttxgO1+bcCnkk+9drydLVDmAMG7NE6aN/fl4F7ucU/90gAA=="],
+
+    "content-disposition": ["content-disposition@1.0.0", "", { "dependencies": { "safe-buffer": "5.2.1" } }, "sha512-Au9nRL8VNUut/XSzbQA38+M78dzP4D+eqg3gfJHMIHHYa3bg067xj1KxMUWj+VULbiZMowKngFFbKczUrNJ1mg=="],
+
+    "content-type": ["content-type@1.0.5", "", {}, "sha512-nTjqfcBFEipKdXCv4YDQWCfmcLZKm81ldF0pAopTvyrFGVbcR6P/VAAd5G7N+0tTr8QqiU0tFadD6FK4NtJwOA=="],
+
+    "cookie": ["cookie@0.7.2", "", {}, "sha512-yki5XnKuf750l50uGTllt6kKILY4nQ1eNIQatoXEByZ5dWgnKqbnqmTrBE5B4N7lrMJKQ2ytWMiTO2o0v6Ew/w=="],
+
+    "cookie-signature": ["cookie-signature@1.2.2", "", {}, "sha512-D76uU73ulSXrD1UXF4KE2TMxVVwhsnCgfAyTg9k8P6KGZjlXKrOLe4dJQKI3Bxi5wjesZoFXJWElNWBjPZMbhg=="],
+
+    "debug": ["debug@4.4.0", "", { "dependencies": { "ms": "^2.1.3" } }, "sha512-6WTZ/IxCY/T6BALoZHaE4ctp9xm+Z5kY/pzYaCHRFeyVhojxlrm+46y68HA6hr0TcwEssoxNiDEUJQjfPZ/RYA=="],
+
+    "denque": ["denque@2.1.0", "", {}, "sha512-HVQE3AAb/pxF8fQAoiqpvg9i3evqug3hoiwakOyZAwJm+6vZehbkYXZ0l4JxS+I3QxM97v5aaRNhj8v5oBhekw=="],
+
+    "depd": ["depd@2.0.0", "", {}, "sha512-g7nH6P6dyDioJogAAGprGpCtVImJhpPk/roCzdb3fIh61/s/nPsfR6onyMwkCAR/OlC3yBC0lESvUoQEAssIrw=="],
+
+    "dunder-proto": ["dunder-proto@1.0.1", "", { "dependencies": { "call-bind-apply-helpers": "^1.0.1", "es-errors": "^1.3.0", "gopd": "^1.2.0" } }, "sha512-KIN/nDJBQRcXw0MLVhZE9iQHmG68qAVIBg9CqmUYjmQIhgij9U5MFvrqkUL5FbtyyzZuOeOt0zdeRe4UY7ct+A=="],
+
+    "ee-first": ["ee-first@1.1.1", "", {}, "sha512-WMwm9LhRUo+WUaRN+vRuETqG89IgZphVSNkdFgeb6sS/E4OrDIN7t48CAewSHXc6C8lefD8KKfr5vY61brQlow=="],
+
+    "encodeurl": ["encodeurl@2.0.0", "", {}, "sha512-Q0n9HRi4m6JuGIV1eFlmvJB7ZEVxu93IrMyiMsGC0lrMJMWzRgx6WGquyfQgZVb31vhGgXnfmPNNXmxnOkRBrg=="],
+
+    "es-define-property": ["es-define-property@1.0.1", "", {}, "sha512-e3nRfgfUZ4rNGL232gUgX06QNyyez04KdjFrF+LTRoOXmrOgFKDg4BCdsjW8EnT69eqdYGmRpJwiPVYNrCaW3g=="],
+
+    "es-errors": ["es-errors@1.3.0", "", {}, "sha512-Zf5H2Kxt2xjTvbJvP2ZWLEICxA6j+hAmMzIlypy4xcBg1vKVnx89Wy0GbS+kf5cwCVFFzdCFh2XSCFNULS6csw=="],
+
+    "es-object-atoms": ["es-object-atoms@1.1.1", "", { "dependencies": { "es-errors": "^1.3.0" } }, "sha512-FGgH2h8zKNim9ljj7dankFPcICIK9Cp5bm+c2gQSYePhpaG5+esrLODihIorn+Pe6FGJzWhXQotPv73jTaldXA=="],
+
+    "escape-html": ["escape-html@1.0.3", "", {}, "sha512-NiSupZ4OeuGwr68lGIeym/ksIZMJodUGOSCZ/FSnTxcrekbvqrgdUxlJOMpijaKZVjAJrWrGs/6Jy8OMuyj9ow=="],
+
+    "etag": ["etag@1.8.1", "", {}, "sha512-aIL5Fx7mawVa300al2BnEE4iNvo1qETxLrPI/o05L7z6go7fCw1J6EQmbK4FmJ2AS7kgVF/KEZWufBfdClMcPg=="],
+
+    "express": ["express@5.1.0", "", { "dependencies": { "accepts": "^2.0.0", "body-parser": "^2.2.0", "content-disposition": "^1.0.0", "content-type": "^1.0.5", "cookie": "^0.7.1", "cookie-signature": "^1.2.1", "debug": "^4.4.0", "encodeurl": "^2.0.0", "escape-html": "^1.0.3", "etag": "^1.8.1", "finalhandler": "^2.1.0", "fresh": "^2.0.0", "http-errors": "^2.0.0", "merge-descriptors": "^2.0.0", "mime-types": "^3.0.0", "on-finished": "^2.4.1", "once": "^1.4.0", "parseurl": "^1.3.3", "proxy-addr": "^2.0.7", "qs": "^6.14.0", "range-parser": "^1.2.1", "router": "^2.2.0", "send": "^1.1.0", "serve-static": "^2.2.0", "statuses": "^2.0.1", "type-is": "^2.0.1", "vary": "^1.1.2" } }, "sha512-DT9ck5YIRU+8GYzzU5kT3eHGA5iL+1Zd0EutOmTE9Dtk+Tvuzd23VBU+ec7HPNSTxXYO55gPV/hq4pSBJDjFpA=="],
+
+    "express-rate-limit": ["express-rate-limit@7.5.0", "", { "peerDependencies": { "express": "^4.11 || 5 || ^5.0.0-beta.1" } }, "sha512-eB5zbQh5h+VenMPM3fh+nw1YExi5nMr6HUCR62ELSP11huvxm/Uir1H1QEyTkk5QX6A58pX6NmaTMceKZ0Eodg=="],
+
+    "finalhandler": ["finalhandler@2.1.0", "", { "dependencies": { "debug": "^4.4.0", "encodeurl": "^2.0.0", "escape-html": "^1.0.3", "on-finished": "^2.4.1", "parseurl": "^1.3.3", "statuses": "^2.0.1" } }, "sha512-/t88Ty3d5JWQbWYgaOGCCYfXRwV1+be02WqYYlL6h0lEiUAMPM8o8qKGO01YIkOHzka2up08wvgYD0mDiI+q3Q=="],
+
+    "forwarded": ["forwarded@0.2.0", "", {}, "sha512-buRG0fpBtRHSTCOASe6hD258tEubFoRLb4ZNA6NxMVHNw2gOcwHo9wyablzMzOA5z9xA9L1KNjk/Nt6MT9aYow=="],
+
+    "fresh": ["fresh@2.0.0", "", {}, "sha512-Rx/WycZ60HOaqLKAi6cHRKKI7zxWbJ31MhntmtwMoaTeF7XFH9hhBp8vITaMidfljRQ6eYWCKkaTK+ykVJHP2A=="],
+
+    "function-bind": ["function-bind@1.1.2", "", {}, "sha512-7XHNxH7qX9xG5mIwxkhumTox/MIRNcOgDrxWsMt2pAr23WHp6MrRlN7FBSFpCpr+oVO0F744iUgR82nJMfG2SA=="],
+
+    "get-intrinsic": ["get-intrinsic@1.3.0", "", { "dependencies": { "call-bind-apply-helpers": "^1.0.2", "es-define-property": "^1.0.1", "es-errors": "^1.3.0", "es-object-atoms": "^1.1.1", "function-bind": "^1.1.2", "get-proto": "^1.0.1", "gopd": "^1.2.0", "has-symbols": "^1.1.0", "hasown": "^2.0.2", "math-intrinsics": "^1.1.0" } }, "sha512-9fSjSaos/fRIVIp+xSJlE6lfwhES7LNtKaCBIamHsjr2na1BiABJPo0mOjjz8GJDURarmCPGqaiVg5mfjb98CQ=="],
+
+    "get-proto": ["get-proto@1.0.1", "", { "dependencies": { "dunder-proto": "^1.0.1", "es-object-atoms": "^1.0.0" } }, "sha512-sTSfBjoXBp89JvIKIefqw7U2CCebsc74kiY6awiGogKtoSGbgjYE/G/+l9sF3MWFPNc9IcoOC4ODfKHfxFmp0g=="],
+
+    "gopd": ["gopd@1.2.0", "", {}, "sha512-ZUKRh6/kUFoAiTAtTYPZJ3hw9wNxx+BIBOijnlG9PnrJsCcSjs1wyyD6vJpaYtgnzDrKYRSqf3OO6Rfa93xsRg=="],
+
+    "has-symbols": ["has-symbols@1.1.0", "", {}, "sha512-1cDNdwJ2Jaohmb3sg4OmKaMBwuC48sYni5HUw2DvsC8LjGTLK9h+eb1X6RyuOHe4hT0ULCW68iomhjUoKUqlPQ=="],
+
+    "hasown": ["hasown@2.0.2", "", { "dependencies": { "function-bind": "^1.1.2" } }, "sha512-0hJU9SCPvmMzIBdZFqNPXWa6dqh7WdH0cII9y+CyS8rG3nL48Bclra9HmKhVVUHyPWNH5Y7xDwAB7bfgSjkUMQ=="],
+
+    "hono": ["hono@4.7.8", "", {}, "sha512-PCibtFdxa7/Ldud9yddl1G81GjYaeMYYTq4ywSaNsYbB1Lug4mwtOMJf2WXykL0pntYwmpRJeOI3NmoDgD+Jxw=="],
+
+    "hono-rate-limiter": ["hono-rate-limiter@0.4.2", "", { "peerDependencies": { "hono": "^4.1.1" } }, "sha512-AAtFqgADyrmbDijcRTT/HJfwqfvhalya2Zo+MgfdrMPas3zSMD8SU03cv+ZsYwRU1swv7zgVt0shwN059yzhjw=="],
+
+    "http-errors": ["http-errors@2.0.0", "", { "dependencies": { "depd": "2.0.0", "inherits": "2.0.4", "setprototypeof": "1.2.0", "statuses": "2.0.1", "toidentifier": "1.0.1" } }, "sha512-FtwrG/euBzaEjYeRqOgly7G0qviiXoJWnvEH2Z1plBdXgbyjv34pHTSb9zoeHMyDy33+DWy5Wt9Wo+TURtOYSQ=="],
+
+    "iconv-lite": ["iconv-lite@0.6.3", "", { "dependencies": { "safer-buffer": ">= 2.1.2 < 3.0.0" } }, "sha512-4fCk79wshMdzMp2rH06qWrJE4iolqLhCUH+OiuIgU++RB0+94NlDL81atO7GX55uUKueo0txHNtvEyI6D7WdMw=="],
+
+    "inherits": ["inherits@2.0.4", "", {}, "sha512-k/vGaX4/Yla3WzyMCvTQOXYeIHvqOKtnqBduzTHpzpQZzAskKMhZ2K+EnBiSM9zGSoIFeMpXKxa4dYeZIQqewQ=="],
+
+    "ioredis": ["ioredis@5.6.1", "", { "dependencies": { "@ioredis/commands": "^1.1.1", "cluster-key-slot": "^1.1.0", "debug": "^4.3.4", "denque": "^2.1.0", "lodash.defaults": "^4.2.0", "lodash.isarguments": "^3.1.0", "redis-errors": "^1.2.0", "redis-parser": "^3.0.0", "standard-as-callback": "^2.1.0" } }, "sha512-UxC0Yv1Y4WRJiGQxQkP0hfdL0/5/6YvdfOOClRgJ0qppSarkhneSa6UvkMkms0AkdGimSH3Ikqm+6mkMmX7vGA=="],
+
+    "ipaddr.js": ["ipaddr.js@1.9.1", "", {}, "sha512-0KI/607xoxSToH7GjN1FfSbLoU0+btTicjsQSWQlh/hZykN8KpmMf7uYwPW3R+akZ6R/w18ZlXSHBYXiYUPO3g=="],
+
+    "is-promise": ["is-promise@4.0.0", "", {}, "sha512-hvpoI6korhJMnej285dSg6nu1+e6uxs7zG3BYAm5byqDsgJNWwxzM6z6iZiAgQR4TJ30JmBTOwqZUw3WlyH3AQ=="],
+
+    "lodash.defaults": ["lodash.defaults@4.2.0", "", {}, "sha512-qjxPLHd3r5DnsdGacqOMU6pb/avJzdh9tFX2ymgoZE27BmjXrNy/y4LoaiTeAb+O3gL8AfpJGtqfX/ae2leYYQ=="],
+
+    "lodash.isarguments": ["lodash.isarguments@3.1.0", "", {}, "sha512-chi4NHZlZqZD18a0imDHnZPrDeBbTtVN7GXMwuGdRH9qotxAjYs3aVLKc7zNOG9eddR5Ksd8rvFEBc9SsggPpg=="],
+
+    "math-intrinsics": ["math-intrinsics@1.1.0", "", {}, "sha512-/IXtbwEk5HTPyEwyKX6hGkYXxM9nbj64B+ilVJnC/R6B0pH5G4V3b0pVbL7DBj4tkhBAppbQUlf6F6Xl9LHu1g=="],
+
+    "media-typer": ["media-typer@1.1.0", "", {}, "sha512-aisnrDP4GNe06UcKFnV5bfMNPBUw4jsLGaWwWfnH3v02GnBuXX2MCVn5RbrWo0j3pczUilYblq7fQ7Nw2t5XKw=="],
+
+    "merge-descriptors": ["merge-descriptors@2.0.0", "", {}, "sha512-Snk314V5ayFLhp3fkUREub6WtjBfPdCPY1Ln8/8munuLuiYhsABgBVWsozAG+MWMbVEvcdcpbi9R7ww22l9Q3g=="],
+
+    "mime-db": ["mime-db@1.54.0", "", {}, "sha512-aU5EJuIN2WDemCcAp2vFBfp/m4EAhWJnUNSSw0ixs7/kXbd6Pg64EmwJkNdFhB8aWt1sH2CTXrLxo/iAGV3oPQ=="],
+
+    "mime-types": ["mime-types@3.0.1", "", { "dependencies": { "mime-db": "^1.54.0" } }, "sha512-xRc4oEhT6eaBpU1XF7AjpOFD+xQmXNB5OVKwp4tqCuBpHLS/ZbBDrc07mYTDqVMg6PfxUjjNp85O6Cd2Z/5HWA=="],
+
+    "ms": ["ms@2.1.3", "", {}, "sha512-6FlzubTLZG3J2a/NVCAleEhjzq5oxgHyaCU9yYXvcLsvoVaHJq/s5xXI6/XXP6tz7R9xAOtHnSO/tXtF3WRTlA=="],
+
+    "negotiator": ["negotiator@1.0.0", "", {}, "sha512-8Ofs/AUQh8MaEcrlq5xOX0CQ9ypTF5dl78mjlMNfOK08fzpgTHQRQPBxcPlEtIw0yRpws+Zo/3r+5WRby7u3Gg=="],
+
+    "object-inspect": ["object-inspect@1.13.4", "", {}, "sha512-W67iLl4J2EXEGTbfeHCffrjDfitvLANg0UlX3wFUUSTx92KXRFegMHUVgSqE+wvhAbi4WqjGg9czysTV2Epbew=="],
+
+    "on-finished": ["on-finished@2.4.1", "", { "dependencies": { "ee-first": "1.1.1" } }, "sha512-oVlzkg3ENAhCk2zdv7IJwd/QUD4z2RxRwpkcGY8psCVcCYZNq4wYnVWALHM+brtuJjePWiYF/ClmuDr8Ch5+kg=="],
+
+    "once": ["once@1.4.0", "", { "dependencies": { "wrappy": "1" } }, "sha512-lNaJgI+2Q5URQBkccEKHTQOPaXdUxnZZElQTZY0MFUAuaEqe1E+Nyvgdz/aIyNi6Z9MzO5dv1H8n58/GELp3+w=="],
+
+    "parseurl": ["parseurl@1.3.3", "", {}, "sha512-CiyeOxFT/JZyN5m0z9PfXw4SCBJ6Sygz1Dpl0wqjlhDEGGBP1GnsUVEL0p63hoG1fcj3fHynXi9NYO4nWOL+qQ=="],
+
+    "path-to-regexp": ["path-to-regexp@8.2.0", "", {}, "sha512-TdrF7fW9Rphjq4RjrW0Kp2AW0Ahwu9sRGTkS6bvDi0SCwZlEZYmcfDbEsTz8RVk0EHIS/Vd1bv3JhG+1xZuAyQ=="],
+
+    "postgres": ["postgres@3.4.5", "", {}, "sha512-cDWgoah1Gez9rN3H4165peY9qfpEo+SA61oQv65O3cRUE1pOEoJWwddwcqKE8XZYjbblOJlYDlLV4h67HrEVDg=="],
+
+    "prettier": ["prettier@3.5.3", "", { "bin": { "prettier": "bin/prettier.cjs" } }, "sha512-QQtaxnoDJeAkDvDKWCLiwIXkTgRhwYDEQCghU9Z6q03iyek/rxRh/2lC3HB7P8sWT2xC/y5JDctPLBIGzHKbhw=="],
+
+    "property-expr": ["property-expr@2.0.6", "", {}, "sha512-SVtmxhRE/CGkn3eZY1T6pC8Nln6Fr/lu1mKSgRud0eC73whjGfoAogbn78LkD8aFL0zz3bAFerKSnOl7NlErBA=="],
+
+    "proxy-addr": ["proxy-addr@2.0.7", "", { "dependencies": { "forwarded": "0.2.0", "ipaddr.js": "1.9.1" } }, "sha512-llQsMLSUDUPT44jdrU/O37qlnifitDP+ZwrmmZcoSKyLKvtZxpyV0n2/bD/N4tBAAZ/gJEdZU7KMraoK1+XYAg=="],
+
+    "qs": ["qs@6.14.0", "", { "dependencies": { "side-channel": "^1.1.0" } }, "sha512-YWWTjgABSKcvs/nWBi9PycY/JiPJqOD4JA6o9Sej2AtvSGarXxKC3OQSk4pAarbdQlKAh5D4FCQkJNkW+GAn3w=="],
+
+    "range-parser": ["range-parser@1.2.1", "", {}, "sha512-Hrgsx+orqoygnmhFbKaHE6c296J+HTAQXoxEF6gNupROmmGJRoyzfG3ccAveqCBrwr/2yxQ5BVd/GTl5agOwSg=="],
+
+    "rate-limit-redis": ["rate-limit-redis@4.2.0", "", { "peerDependencies": { "express-rate-limit": ">= 6" } }, "sha512-wV450NQyKC24NmPosJb2131RoczLdfIJdKCReNwtVpm5998U8SgKrAZrIHaN/NfQgqOHaan8Uq++B4sa5REwjA=="],
+
+    "raw-body": ["raw-body@3.0.0", "", { "dependencies": { "bytes": "3.1.2", "http-errors": "2.0.0", "iconv-lite": "0.6.3", "unpipe": "1.0.0" } }, "sha512-RmkhL8CAyCRPXCE28MMH0z2PNWQBNk2Q09ZdxM9IOOXwxwZbN+qbWaatPkdkWIKL2ZVDImrN/pK5HTRz2PcS4g=="],
+
+    "redis-errors": ["redis-errors@1.2.0", "", {}, "sha512-1qny3OExCf0UvUV/5wpYKf2YwPcOqXzkwKKSmKHiE6ZMQs5heeE/c8eXK+PNllPvmjgAbfnsbpkGZWy8cBpn9w=="],
+
+    "redis-parser": ["redis-parser@3.0.0", "", { "dependencies": { "redis-errors": "^1.0.0" } }, "sha512-DJnGAeenTdpMEH6uAJRK/uiyEIH9WVsUmoLwzudwGJUwZPp80PDBWPHXSAGNPwNvIXAbe7MSUB1zQFugFml66A=="],
+
+    "router": ["router@2.2.0", "", { "dependencies": { "debug": "^4.4.0", "depd": "^2.0.0", "is-promise": "^4.0.0", "parseurl": "^1.3.3", "path-to-regexp": "^8.0.0" } }, "sha512-nLTrUKm2UyiL7rlhapu/Zl45FwNgkZGaCpZbIHajDYgwlJCOzLSk+cIPAnsEqV955GjILJnKbdQC1nVPz+gAYQ=="],
+
+    "safe-buffer": ["safe-buffer@5.2.1", "", {}, "sha512-rp3So07KcdmmKbGvgaNxQSJr7bGVSVk5S9Eq1F+ppbRo70+YeaDxkw5Dd8NPN+GD6bjnYm2VuPuCXmpuYvmCXQ=="],
+
+    "safer-buffer": ["safer-buffer@2.1.2", "", {}, "sha512-YZo3K82SD7Riyi0E1EQPojLz7kpepnSQI9IyPbHHg1XXXevb5dJI7tpyN2ADxGcQbHG7vcyRHk0cbwqcQriUtg=="],
+
+    "send": ["send@1.2.0", "", { "dependencies": { "debug": "^4.3.5", "encodeurl": "^2.0.0", "escape-html": "^1.0.3", "etag": "^1.8.1", "fresh": "^2.0.0", "http-errors": "^2.0.0", "mime-types": "^3.0.1", "ms": "^2.1.3", "on-finished": "^2.4.1", "range-parser": "^1.2.1", "statuses": "^2.0.1" } }, "sha512-uaW0WwXKpL9blXE2o0bRhoL2EGXIrZxQ2ZQ4mgcfoBxdFmQold+qWsD2jLrfZ0trjKL6vOw0j//eAwcALFjKSw=="],
+
+    "serve-static": ["serve-static@2.2.0", "", { "dependencies": { "encodeurl": "^2.0.0", "escape-html": "^1.0.3", "parseurl": "^1.3.3", "send": "^1.2.0" } }, "sha512-61g9pCh0Vnh7IutZjtLGGpTA355+OPn2TyDv/6ivP2h/AdAVX9azsoxmg2/M6nZeQZNYBEwIcsne1mJd9oQItQ=="],
+
+    "setprototypeof": ["setprototypeof@1.2.0", "", {}, "sha512-E5LDX7Wrp85Kil5bhZv46j8jOeboKq5JMmYM3gVGdGH8xFpPWXUMsNrlODCrkoxMEeNi/XZIwuRvY4XNwYMJpw=="],
+
+    "side-channel": ["side-channel@1.1.0", "", { "dependencies": { "es-errors": "^1.3.0", "object-inspect": "^1.13.3", "side-channel-list": "^1.0.0", "side-channel-map": "^1.0.1", "side-channel-weakmap": "^1.0.2" } }, "sha512-ZX99e6tRweoUXqR+VBrslhda51Nh5MTQwou5tnUDgbtyM0dBgmhEDtWGP/xbKn6hqfPRHujUNwz5fy/wbbhnpw=="],
+
+    "side-channel-list": ["side-channel-list@1.0.0", "", { "dependencies": { "es-errors": "^1.3.0", "object-inspect": "^1.13.3" } }, "sha512-FCLHtRD/gnpCiCHEiJLOwdmFP+wzCmDEkc9y7NsYxeF4u7Btsn1ZuwgwJGxImImHicJArLP4R0yX4c2KCrMrTA=="],
+
+    "side-channel-map": ["side-channel-map@1.0.1", "", { "dependencies": { "call-bound": "^1.0.2", "es-errors": "^1.3.0", "get-intrinsic": "^1.2.5", "object-inspect": "^1.13.3" } }, "sha512-VCjCNfgMsby3tTdo02nbjtM/ewra6jPHmpThenkTYh8pG9ucZ/1P8So4u4FGBek/BjpOVsDCMoLA/iuBKIFXRA=="],
+
+    "side-channel-weakmap": ["side-channel-weakmap@1.0.2", "", { "dependencies": { "call-bound": "^1.0.2", "es-errors": "^1.3.0", "get-intrinsic": "^1.2.5", "object-inspect": "^1.13.3", "side-channel-map": "^1.0.1" } }, "sha512-WPS/HvHQTYnHisLo9McqBHOJk2FkHO/tlpvldyrnem4aeQp4hai3gythswg6p01oSoTl58rcpiFAjF2br2Ak2A=="],
+
+    "standard-as-callback": ["standard-as-callback@2.1.0", "", {}, "sha512-qoRRSyROncaz1z0mvYqIE4lCd9p2R90i6GxW3uZv5ucSu8tU7B5HXUP1gG8pVZsYNVaXjk8ClXHPttLyxAL48A=="],
+
+    "statuses": ["statuses@2.0.1", "", {}, "sha512-RwNA9Z/7PrK06rYLIzFMlaF+l73iwpzsqRIFgbMLbTcLD6cOao82TaWefPXQvB2fOC4AjuYSEndS7N/mTCbkdQ=="],
+
+    "tiny-case": ["tiny-case@1.0.3", "", {}, "sha512-Eet/eeMhkO6TX8mnUteS9zgPbUMQa4I6Kkp5ORiBD5476/m+PIRiumP5tmh5ioJpH7k51Kehawy2UDfsnxxY8Q=="],
+
+    "toidentifier": ["toidentifier@1.0.1", "", {}, "sha512-o5sSPKEkg/DIQNmH43V0/uerLrpzVedkUh8tGNvaeXpfpuwjKenlSox/2O/BTlZUtEe+JG7s5YhEz608PlAHRA=="],
+
+    "toposort": ["toposort@2.0.2", "", {}, "sha512-0a5EOkAUp8D4moMi2W8ZF8jcga7BgZd91O/yabJCFY8az+XSzeGyTKs0Aoo897iV1Nj6guFq8orWDS96z91oGg=="],
+
+    "type-fest": ["type-fest@2.19.0", "", {}, "sha512-RAH822pAdBgcNMAfWnCBU3CFZcfZ/i1eZjwFU/dsLKumyuuP3niueg2UAukXYF0E2AAoc82ZSSf9J0WQBinzHA=="],
+
+    "type-is": ["type-is@2.0.1", "", { "dependencies": { "content-type": "^1.0.5", "media-typer": "^1.1.0", "mime-types": "^3.0.0" } }, "sha512-OZs6gsjF4vMp32qrCbiVSkrFmXtG/AZhY3t0iAMrMBiAZyV9oALtXO8hsrHbMXF9x6L3grlFuwW2oAz7cav+Gw=="],
+
+    "typescript": ["typescript@5.8.3", "", { "bin": { "tsc": "bin/tsc", "tsserver": "bin/tsserver" } }, "sha512-p1diW6TqL9L07nNxvRMM7hMMw4c5XOo/1ibL4aAIGmSAt9slTE1Xgw5KWuof2uTOvCg9BY7ZRi+GaF+7sfgPeQ=="],
+
+    "undici-types": ["undici-types@6.21.0", "", {}, "sha512-iwDZqg0QAGrg9Rav5H4n0M64c3mkR59cJ6wQp+7C4nI0gsmExaedaYLNO44eT4AtBBwjbTiGPMlt2Md0T9H9JQ=="],
+
+    "unpipe": ["unpipe@1.0.0", "", {}, "sha512-pjy2bYhSsufwWlKwPc+l3cN7+wuJlK6uz0YdJEOlQDbl6jo/YlPi4mb8agUkVC8BF7V8NuzeyPNqRksA3hztKQ=="],
+
+    "vary": ["vary@1.1.2", "", {}, "sha512-BNGbWLfd0eUPabhkXUVm0j8uuvREyTh5ovRa/dyow/BqAbZJyC+5fU+IzQOzmAKzYqYRAISoRhdQr3eIZ/PXqg=="],
+
+    "wrappy": ["wrappy@1.0.2", "", {}, "sha512-l4Sp/DRseor9wL6EvV2+TuQn63dMkPjZ/sp9XkghTEbV9KlPS1xUsZ3u7/IQO4wxtcFB4bgpQPRcR3QCvezPcQ=="],
+
+    "yup": ["yup@1.6.1", "", { "dependencies": { "property-expr": "^2.0.5", "tiny-case": "^1.0.3", "toposort": "^2.0.2", "type-fest": "^2.19.0" } }, "sha512-JED8pB50qbA4FOkDol0bYF/p60qSEDQqBD0/qeIrUCG1KbPBIQ776fCUNb9ldbPcSTxA69g/47XTo4TqWiuXOA=="],
+
+    "zod": ["zod@3.24.3", "", {}, "sha512-HhY1oqzWCQWuUqvBFnsyrtZRhyPeR7SUGv+C4+MsisMuVfSPx8HpwWqH8tRahSlt6M3PiFAcoeFhZAqIXTxoSg=="],
+  }
+}

packages/backend/database.ts

@@ -1,30 +0,0 @@
-import { type Client, Pool } from "https://deno.land/x/postgres@v0.19.3/mod.ts";
-import { postgresConfig, postgresConfigCred } from "@core/db/pgConfig.ts";
-import { createMiddleware } from "hono/factory";
-
-const pool = new Pool(postgresConfig, 4);
-const poolCred = new Pool(postgresConfigCred, 2);
-
-export const db = pool;
-export const dbCred = poolCred;
-
-export const dbMiddleware = createMiddleware(async (c, next) => {
-    const connection = await pool.connect();
-	c.set("db", connection);
-	await next();
-	connection.release();
-});
-
-export const dbCredMiddleware = createMiddleware(async (c, next) => {
-    const connection = await poolCred.connect();
-	c.set("dbCred", connection);
-	await next();
-	connection.release();
-})
-
-declare module "hono" {
-	interface ContextVariableMap {
-		db: Client;
-		dbCred: Client;
-	}
-}

packages/backend/db/latestSnapshots.ts

@@ -0,0 +1,13 @@
+import { sql } from "@core/db/dbNew";
+import type { LatestSnapshotType } from "@core/db/schema.d.ts";
+
+export async function getVideosInViewsRange(minViews: number, maxViews: number) {
+	return sql<LatestSnapshotType[]>`
+        SELECT *
+        FROM latest_video_snapshot
+        WHERE views >= ${minViews}
+        AND views <= ${maxViews}
+        ORDER BY views DESC
+        LIMIT 5000
+	`;
+}

packages/backend/db/snapshots.ts

@@ -0,0 +1,58 @@
+import { sql } from "@core/db/dbNew";
+import type { VideoSnapshotType } from "@core/db/schema.d.ts";
+
+export async function getVideoSnapshots(
+	aid: number,
+	limit: number,
+	pageOrOffset: number,
+	reverse: boolean,
+	mode: "page" | "offset" = "page"
+) {
+	const offset = mode === "page" ? (pageOrOffset - 1) * limit : pageOrOffset;
+	if (reverse) {
+		return sql<VideoSnapshotType[]>`
+            SELECT *
+            FROM video_snapshot
+            WHERE aid = ${aid}
+            ORDER BY created_at
+            LIMIT ${limit} OFFSET ${offset}
+		`;
+	} else {
+		return sql<VideoSnapshotType[]>`
+            SELECT *
+            FROM video_snapshot
+            WHERE aid = ${aid}
+            ORDER BY created_at DESC
+            LIMIT ${limit} OFFSET ${offset}
+		`;
+	}
+}
+
+export async function getVideoSnapshotsByBV(
+	bv: string,
+	limit: number,
+	pageOrOffset: number,
+	reverse: boolean,
+	mode: "page" | "offset" = "page"
+) {
+	const offset = mode === "page" ? (pageOrOffset - 1) * limit : pageOrOffset;
+	if (reverse) {
+		return sql<VideoSnapshotType[]>`
+            SELECT vs.*
+            FROM video_snapshot vs
+                     JOIN bilibili_metadata bm ON vs.aid = bm.aid
+            WHERE bm.bvid = ${bv}
+            ORDER BY vs.created_at
+            LIMIT ${limit} OFFSET ${offset}
+		`;
+	} else {
+		return sql<VideoSnapshotType[]>`
+            SELECT vs.*
+            FROM video_snapshot vs
+                     JOIN bilibili_metadata bm ON vs.aid = bm.aid
+            WHERE bm.bvid = ${bv}
+            ORDER BY vs.created_at DESC
+            LIMIT ${limit} OFFSET ${offset}
+		`;
+	}
+}

packages/backend/deno.json

@@ -1,18 +0,0 @@
-{
-	"name": "@cvsa/backend",
-	"imports": {
-		"@rabbit-company/argon2id": "jsr:@rabbit-company/argon2id@^2.1.0",
-		"hono": "jsr:@hono/hono@^4.7.5",
-		"zod": "npm:zod",
-		"yup": "npm:yup"
-	},
-	"tasks": {
-		"dev": "deno serve --env-file=.env --allow-env --allow-net --watch main.ts",
-		"start": "deno serve --env-file=.env --allow-env --allow-net --host 127.0.0.1 main.ts"
-	},
-	"compilerOptions": {
-		"jsx": "precompile",
-		"jsxImportSource": "hono/jsx"
-	},
-	"exports": "./main.ts"
-}

packages/backend/lib/auth/captchaDifficulty.ts

@@ -0,0 +1,62 @@
+import { Psql } from "@core/db/psql";
+import { SlidingWindow } from "@core/mq/slidingWindow.ts";
+import { redis } from "@core/db/redis.ts";
+import { getIdentifier } from "@/middleware/rateLimiters.ts";
+import { Context } from "hono";
+
+type seconds = number;
+
+export interface CaptchaDifficultyConfig {
+	global: boolean;
+	duration: seconds;
+	threshold: number;
+	difficulty: number;
+}
+
+export const getCaptchaDifficultyConfigByRoute = async (sql: Psql, route: string): Promise<CaptchaDifficultyConfig[]> =>  {
+	return sql<CaptchaDifficultyConfig[]>`
+		SELECT duration, threshold, difficulty, global
+		FROM captcha_difficulty_settings
+		WHERE CONCAT(method, '-', path) = ${route}
+		ORDER BY duration
+	`;
+};
+
+export const getCaptchaConfigMaxDuration = async (sql: Psql, route: string): Promise<seconds> => {
+	const rows = await sql<{max: number}[]>`
+		SELECT MAX(duration)
+		FROM captcha_difficulty_settings
+		WHERE CONCAT(method, '-', path) = ${route}
+	`;
+	if (rows.length < 1){
+		return Number.MAX_SAFE_INTEGER;
+	}
+	return rows[0].max;
+}
+
+
+export const getCurrentCaptchaDifficulty = async (sql: Psql, c: Context | string): Promise<number | null> => {
+	const isRoute = typeof c === "string";
+	const route = isRoute ? c : `${c.req.method}-${c.req.path}`
+	const configs = await getCaptchaDifficultyConfigByRoute(sql, route);
+	if (configs.length < 1) {
+		return null
+	}
+	else if (configs.length == 1) {
+		return configs[0].difficulty
+	}
+	const maxDuration = configs.reduce((max, config) =>
+		Math.max(max, config.duration), 0);
+	const slidingWindow = new SlidingWindow(redis, maxDuration);
+	for (let i = 1; i < configs.length; i++) {
+		const config = configs[i];
+		const lastConfig = configs[i - 1];
+		const identifier = isRoute ? c : getIdentifier(c, config.global);
+		const count = await slidingWindow.count(`captcha-${identifier}`, config.duration);
+		if (count >= config.threshold) {
+			continue;
+		}
+		return lastConfig.difficulty
+	}
+	return configs[configs.length-1].difficulty;
+}

packages/backend/lib/auth/getJWTsecret.ts

@@ -0,0 +1,14 @@
+import { ErrorResponse } from "src/schema";
+
+export const getJWTsecret = () => {
+	const secret = process.env["JWT_SECRET"];
+	if (!secret) {
+		const response: ErrorResponse = {
+			message: "JWT_SECRET is not set",
+			code: "SERVER_ERROR",
+			errors: []
+		};
+		return [response, true];
+	}
+	return [secret, null];
+};

packages/backend/lib/const/singers.ts

@@ -0,0 +1,103 @@
+export const singers = [
+	{
+		name: "洛天依",
+		color: "#66CCFF",
+		birthday: "0712"
+	},
+	{
+		name: "言和",
+		color: "#00FFCC",
+		birthday: "0711"
+	},
+	{
+		name: "乐正绫",
+		color: "#EE0000",
+		birthday: "0412"
+	},
+	{
+		name: "乐正龙牙",
+		color: "#006666",
+		birthday: "1002"
+	},
+	{
+		name: "徵羽摩柯",
+		color: "#0080FF",
+		birthday: "1210"
+	},
+	{
+		name: "墨清弦",
+		color: "#FFFF00",
+		birthday: "0520"
+	},
+	{
+		name: "星尘",
+		color: "#9999FF",
+		birthday: "0812"
+	},
+	{
+		name: "心华",
+		color: "#EE82EE",
+		birthday: "0210"
+	},
+	{
+		name: "海伊",
+		color: "#3399FF",
+		birthday: "0722"
+	},
+	{
+		name: "苍穹",
+		color: "#8BC0B5",
+		birthday: "0520"
+	},
+	{
+		name: "赤羽",
+		color: "#FF4004",
+		birthday: "1126"
+	},
+	{
+		name: "诗岸",
+		color: "#F6BE72",
+		birthday: "0119"
+	},
+	{
+		name: "牧心",
+		color: "#2A2859",
+		birthday: "0807"
+	}
+];
+
+export interface Singer {
+	name: string;
+	color?: string;
+	birthday?: string;
+	message?: string;
+}
+
+export const specialSingers = [
+	{
+		name: "雅音宫羽",
+		message: "你是我最真模样，从来不曾遗忘。"
+	},
+	{
+		name: "初音未来",
+		message: "初始之音，响彻未来!"
+	}
+];
+
+export const pickSinger = () => {
+	const index = Math.floor(Math.random() * singers.length);
+	return singers[index];
+};
+
+export const pickSpecialSinger = () => {
+	const index = Math.floor(Math.random() * specialSingers.length);
+	return specialSingers[index];
+};
+
+export const getSingerForBirthday = (): Singer[] => {
+	const today = new Date();
+	const month = String(today.getMonth() + 1).padStart(2, "0");
+	const day = String(today.getDate()).padStart(2, "0");
+	const datestring = `${month}${day}`;
+	return singers.filter((singer) => singer.birthday === datestring);
+};

packages/backend/main.ts

@@ -1,23 +0,0 @@
-import { Hono } from "hono";
-import { dbCredMiddleware, dbMiddleware } from "./database.ts";
-import { rootHandler } from "./root.ts";
-import { getSnapshotsHanlder } from "./snapshots.ts";
-import { registerHandler } from "./register.ts";
-
-export const app = new Hono();
-
-app.use('/video/*', dbMiddleware);
-app.use('/user', dbCredMiddleware);
-
-app.get("/", ...rootHandler);
-
-app.get('/video/:id/snapshots', ...getSnapshotsHanlder);
-app.post('/user', ...registerHandler);
-
-const fetch = app.fetch;
-
-export default {
-	fetch,
-} satisfies Deno.ServeDefaultExport;
-
-export const VERSION = "0.3.0";

packages/backend/middleware/bodyLimits.ts

@@ -0,0 +1,14 @@
+import { bodyLimit } from "hono/body-limit";
+import { ErrorResponse } from "../src/schema";
+
+export const bodyLimitForPing = bodyLimit({
+	maxSize: 14000,
+	onError: (c) => {
+		const res: ErrorResponse<string> = {
+			message: "Body too large",
+			errors: ["Body should not be larger than 14kB."],
+			code: "BODY_TOO_LARGE"
+		};
+		return c.json(res, 413);
+	}
+});

packages/backend/middleware/captcha.ts

@@ -0,0 +1,120 @@
+import { Context, Next } from "hono";
+import { ErrorResponse } from "src/schema";
+import { SlidingWindow } from "@core/mq/slidingWindow.ts";
+import { getCaptchaConfigMaxDuration, getCurrentCaptchaDifficulty } from "@/lib/auth/captchaDifficulty.ts";
+import { sqlCred } from "@core/db/dbNew.ts";
+import { redis } from "@core/db/redis.ts";
+import { verify } from "hono/jwt";
+import { JwtTokenInvalid, JwtTokenExpired } from "hono/utils/jwt/types";
+import { getJWTsecret } from "@/lib/auth/getJWTsecret.ts";
+import { lockManager } from "@core/mq/lockManager.ts";
+import { object, string, number, ValidationError } from "yup";
+import { getIdentifier } from "@/middleware/rateLimiters.ts";
+
+const tokenSchema = object({
+	exp: number().integer(),
+	id: string().length(6),
+	difficulty: number().integer().moreThan(0)
+});
+
+export const captchaMiddleware = async (c: Context, next: Next) => {
+	const authHeader = c.req.header("Authorization");
+
+	if (!authHeader) {
+		const response: ErrorResponse = {
+			message: "'Authorization' header is missing.",
+			code: "UNAUTHORIZED",
+			errors: []
+		};
+		return c.json<ErrorResponse>(response, 401);
+	}
+
+	const authIsBearer = authHeader.startsWith("Bearer ");
+	if (!authIsBearer || authHeader.length < 8) {
+		const response: ErrorResponse = {
+			message: "'Authorization' header is invalid.",
+			code: "INVALID_HEADER",
+			errors: []
+		};
+		return c.json<ErrorResponse>(response, 400);
+	}
+
+	const [r, err] = getJWTsecret();
+	if (err) {
+		return c.json<ErrorResponse>(r as ErrorResponse, 500);
+	}
+	const jwtSecret = r as string;
+
+	const token = authHeader.substring(7);
+
+	const path = c.req.path;
+	const method = c.req.method;
+	const route = `${method}-${path}`;
+
+	const requiredDifficulty = await getCurrentCaptchaDifficulty(sqlCred, c);
+
+	try {
+		const decodedPayload = await verify(token, jwtSecret);
+		const payload = await tokenSchema.validate(decodedPayload);
+		const difficulty = payload.difficulty;
+		const tokenID = payload.id;
+		const consumed = await lockManager.isLocked(tokenID);
+		if (consumed) {
+			const response: ErrorResponse = {
+				message: "Token has already been used.",
+				code: "INVALID_CREDENTIALS",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 401);
+		}
+		if (difficulty < requiredDifficulty) {
+			const response: ErrorResponse = {
+				message: "Token too weak.",
+				code: "UNAUTHORIZED",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 401);
+		}
+		const EXPIRE_FIVE_MINUTES = 300;
+		await lockManager.acquireLock(tokenID, EXPIRE_FIVE_MINUTES);
+	} catch (e) {
+		if (e instanceof JwtTokenInvalid) {
+			const response: ErrorResponse = {
+				message: "Failed to verify the token.",
+				code: "INVALID_CREDENTIALS",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 400);
+		} else if (e instanceof JwtTokenExpired) {
+			const response: ErrorResponse = {
+				message: "Token expired.",
+				code: "INVALID_CREDENTIALS",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 400);
+		} else if (e instanceof ValidationError) {
+			const response: ErrorResponse = {
+				code: "INVALID_QUERY_PARAMS",
+				message: "Invalid query parameters",
+				errors: e.errors
+			};
+			return c.json<ErrorResponse>(response, 400);
+		} else {
+			const response: ErrorResponse = {
+				message: "Unknown error.",
+				code: "UNKNOWN_ERROR",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 500);
+		}
+	}
+	const duration = await getCaptchaConfigMaxDuration(sqlCred, route);
+	const window = new SlidingWindow(redis, duration);
+
+	const identifierWithIP = getIdentifier(c, true);
+	const identifier = getIdentifier(c, false);
+	await window.event(`captcha-${identifier}`);
+	await window.event(`captcha-${identifierWithIP}`);
+
+	await next();
+};

packages/backend/middleware/contentType.ts

@@ -0,0 +1,6 @@
+import { Context, Next } from "hono";
+
+export const contentType = async (c: Context, next: Next) => {
+	await next();
+	c.header("Content-Type", "application/json; charset=utf-8");
+};

packages/backend/middleware/cors.ts

@@ -0,0 +1,14 @@
+import { cors } from "hono/cors";
+import { Context, Next } from "hono";
+
+export const corsMiddleware = async (c: Context, next: Next) => {
+	if (c.req.path.startsWith("/user") || c.req.path.startsWith("/login")) {
+		const corsMiddlewareHandler = cors({
+			origin: c.req.header("Origin"),
+			credentials: true
+		});
+		return corsMiddlewareHandler(c, next);
+	}
+	const corsMiddlewareHandler = cors();
+	return corsMiddlewareHandler(c, next);
+};

packages/backend/middleware/logger.ts

@@ -0,0 +1,160 @@
+// Color constants
+import { Context, Next } from "hono";
+import { TimingVariables } from "hono/timing";
+import { getConnInfo } from "hono/bun";
+
+const green = "\x1b[97;42m";
+const white = "\x1b[90;47m";
+const yellow = "\x1b[90;43m";
+const red = "\x1b[97;41m";
+const blue = "\x1b[97;44m";
+const magenta = "\x1b[97;45m";
+const cyan = "\x1b[97;46m";
+const reset = "\x1b[0m";
+
+let consoleColorMode = "auto";
+
+function formatCurrentTime() {
+	const now = new Date();
+	const year = now.getFullYear();
+	const month = String(now.getMonth() + 1).padStart(2, "0"); // Month is 0-indexed
+	const day = String(now.getDate()).padStart(2, "0");
+	const hours = String(now.getHours()).padStart(2, "0");
+	const minutes = String(now.getMinutes()).padStart(2, "0");
+	const seconds = String(now.getSeconds()).padStart(2, "0");
+	const milliseconds = String(now.getMilliseconds()).padStart(3, "0");
+
+	return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}.${milliseconds}`;
+}
+
+export const DisableConsoleColor = () => {
+	consoleColorMode = "disable";
+};
+
+export const ForceConsoleColor = () => {
+	consoleColorMode = "force";
+};
+
+const defaultFormatter = (params) => {
+	const latency = params.latency > 60000 ? `${Math.round(params.latency / 1000)}s` : `${params.latency}ms`;
+
+	let statusColor = white;
+	if (params.isOutputColor) {
+		if (params.status >= 100 && params.status < 300) statusColor = green;
+		else if (params.status >= 300 && params.status < 400) statusColor = white;
+		else if (params.status >= 400 && params.status < 500) statusColor = yellow;
+		else statusColor = red;
+	}
+
+	let methodColor = reset;
+	switch (params.method) {
+		case "GET":
+			methodColor = blue;
+			break;
+		case "POST":
+			methodColor = cyan;
+			break;
+		case "PUT":
+			methodColor = yellow;
+			break;
+		case "DELETE":
+			methodColor = red;
+			break;
+		case "PATCH":
+			methodColor = green;
+			break;
+		case "HEAD":
+			methodColor = magenta;
+			break;
+		case "OPTIONS":
+			methodColor = white;
+			break;
+	}
+
+	return (
+		`${params.timestamp} |${statusColor} ${params.status} ${reset}| ` +
+		`${latency.padStart(7)} | ${params.ip.padStart(16)} |` +
+		`${methodColor} ${params.method.padEnd(6)}${reset} ${params.path}`
+	);
+};
+type Ctx = Context;
+export const logger = (config) => {
+	const { formatter = defaultFormatter, output = console, skipPaths = [], skip = null } = config;
+
+	// Convert skipPaths to Set for faster lookups
+	const skipPathsSet = new Set(skipPaths);
+
+	return async (c: Ctx, next: Next) => {
+		const start = Date.now();
+		const url = new URL(c.req.url);
+		const path = url.pathname;
+
+		// Check if we should skip logging
+		if (skipPathsSet.has(path) || (typeof skip === "function" && skip(c))) {
+			return next();
+		}
+
+		try {
+			await next();
+		} catch (error) {
+			// Handle errors
+			const errorParams = {
+				timestamp: formatCurrentTime(),
+				latency: Date.now() - start,
+				status: 500,
+				ip: getClientIP(c),
+				method: c.req.method,
+				path,
+				error: error.message,
+				isOutputColor: shouldColorize(c)
+			};
+
+			output.error(
+				formatter({
+					...errorParams,
+					errorMessage: error.message
+				})
+			);
+
+			throw error;
+		}
+
+		const status = c.res.status;
+		const latency = Date.now() - start;
+
+		const params = {
+			timestamp: formatCurrentTime(),
+			latency,
+			status,
+			ip: getClientIP(c),
+			method: c.req.method,
+			path,
+			bodySize: c.res.headers.get("content-length") || 0,
+			isOutputColor: shouldColorize(c)
+		};
+
+		// Format and output the log
+		const logMessage = formatter(params);
+
+		if (status >= 400 && status < 500) {
+			output.warn?.(logMessage) || output.log(logMessage);
+		} else if (status >= 500) {
+			output.error?.(logMessage) || output.log(logMessage);
+		} else {
+			output.log(logMessage);
+		}
+	};
+};
+
+function shouldColorize(c) {
+	if (consoleColorMode === "disable") return false;
+	if (consoleColorMode === "force") return true;
+
+	// In development environment with TTY
+	return process.stdout.isTTY;
+}
+
+export function getClientIP(c: Ctx) {
+	const info = getConnInfo(c);
+	return info.remote.address;
+}

packages/backend/middleware/preetifyResponse.ts

@@ -0,0 +1,18 @@
+import { startTime, endTime } from "hono/timing";
+import { Context, Next } from "hono";
+
+export const preetifyResponse = async (c: Context, next: Next) => {
+	await next();
+	const contentType = c.res.headers.get("Content-Type") || "";
+	if (!contentType.includes("application/json")) return;
+	const accept = c.req.header("Accept") || "";
+	const secFetchMode = c.req.header("Sec-Fetch-Mode");
+	const isBrowser = accept.includes("text/html") || secFetchMode === "navigate";
+	if (isBrowser) {
+		const json = await c.res.json();
+		startTime(c, "seralize", "Prettify the response");
+		const prettyJson = JSON.stringify(json, null, 2);
+		endTime(c, "seralize");
+		c.res = new Response(prettyJson, { headers: { "Content-Type": "text/plain; charset=utf-8" } });
+	}
+};

packages/backend/middleware/rateLimiters.ts

@@ -0,0 +1,53 @@
+import type { BlankEnv } from "hono/types";
+import { getConnInfo } from "hono/bun";
+import { Context, Next } from "hono";
+import { generateRandomId } from "@core/lib/randomID.ts";
+import { RateLimiter } from "@koshnic/ratelimit";
+import { ErrorResponse } from "@/src/schema";
+import { redis } from "@core/db/redis.ts";
+
+export const getUserIP = (c: Context) => {
+	let ipAddr = null;
+	const info = getConnInfo(c);
+	if (info.remote && info.remote.address) {
+		ipAddr = info.remote.address;
+	}
+	const forwardedFor = c.req.header("X-Forwarded-For");
+	if (forwardedFor) {
+		ipAddr = forwardedFor.split(",")[0];
+	}
+	return ipAddr;
+};
+
+export const getIdentifier = (c: Context, includeIP: boolean = true) => {
+	let ipAddr = generateRandomId(6);
+	if (getUserIP(c)) {
+		ipAddr = getUserIP(c);
+	}
+	const path = c.req.path;
+	const method = c.req.method;
+	const ipIdentifier = includeIP ? `@${ipAddr}` : "";
+	return `${method}-${path}${ipIdentifier}`;
+};
+
+export const registerRateLimiter = async (c: Context<BlankEnv, "/user", {}>, next: Next) => {
+	const limiter = new RateLimiter(redis);
+	const identifier = getIdentifier(c, true);
+	const { allowed, retryAfter } = await limiter.allow(identifier, {
+		burst: 5,
+		ratePerPeriod: 5,
+		period: 120,
+		cost: 1
+	});
+
+	if (!allowed) {
+		const response: ErrorResponse = {
+			message: `Too many requests, please retry after ${Math.round(retryAfter)} seconds.`,
+			code: "RATE_LIMIT_EXCEEDED",
+			errors: []
+		};
+		return c.json<ErrorResponse>(response, 429);
+	}
+
+	await next();
+};

packages/backend/package.json

@@ -0,0 +1,30 @@
+{
+	"name": "@cvsa/backend",
+	"private": false,
+	"version": "0.6.0",
+	"scripts": {
+		"format": "prettier --write .",
+		"dev": "NODE_ENV=development bun run --hot src/main.ts",
+		"start": "NODE_ENV=production bun run src/main.ts",
+		"build": "bun build ./src/main.ts --target bun --outdir ./dist"
+	},
+	"dependencies": {
+		"@koshnic/ratelimit": "^1.0.3",
+		"@rabbit-company/argon2id": "^2.1.0",
+		"chalk": "^5.4.1",
+		"hono": "^4.7.8",
+		"hono-rate-limiter": "^0.4.2",
+		"ioredis": "^5.6.1",
+		"limiter": "^3.0.0",
+		"postgres": "^3.4.5",
+		"rate-limit-redis": "^4.2.0",
+		"yup": "^1.6.1",
+		"zod": "^3.24.3"
+	},
+	"devDependencies": {
+		"@types/bun": "^1.2.11",
+		"prettier": "^3.5.3"
+	},
+	"main": "./dist/main.js",
+	"types": "./src/types.d.ts"
+}

packages/backend/register.ts

@@ -1,65 +0,0 @@
-import { createHandlers } from "./utils.ts";
-import Argon2id from "@rabbit-company/argon2id";
-import { object, string, ValidationError } from "yup";
-import type { Context } from "hono";
-import type { Bindings, BlankEnv, BlankInput } from "hono/types";
-import type { Client } from "https://deno.land/x/postgres@v0.19.3/mod.ts";
-
-const RegistrationBodySchema = object({
-	username: string().trim().required("Username is required").max(50, "Username cannot exceed 50 characters"),
-	password: string().required("Password is required"),
-    nickname: string().optional(),
-});
-
-type ContextType = Context<BlankEnv & { Bindings: Bindings }, "/user", BlankInput>;
-
-export const userExists = async (username: string, client: Client) => {
-	const query = `
-        SELECT * FROM users WHERE username = $1
-    `;
-	const result = await client.queryObject(query, [username]);
-	return result.rows.length > 0;
-}
-
-export const registerHandler = createHandlers(async (c: ContextType) => {
-	const client = c.get("dbCred");
-
-	try {
-		const body = await RegistrationBodySchema.validate(await c.req.json());
-		const { username, password, nickname } = body;
-
-        if (await userExists(username, client)) {
-			return c.json({
-				message: `User "${username}" already exists.`,
-			}, 400);
-        }
-
-		const hash = await Argon2id.hashEncoded(password);
-
-		const query = `
-            INSERT INTO users (username, password, nickname) VALUES ($1, $2, $3)
-        `;
-		await client.queryObject(query, [username, hash, nickname || null]);
-
-		return c.json({
-			message: `User "${username}" registered successfully.`,
-		}, 201);
-	} catch (e) {
-		if (e instanceof ValidationError) {
-			return c.json({
-				message: "Invalid registration data.",
-				errors: e.errors,
-			}, 400); 
-		} else if (e instanceof SyntaxError) {
-			return c.json({
-				message: "Invalid JSON in request body.",
-			}, 400);
-		} else {
-			console.error("Registration error:", e);
-			return c.json({
-				message: "An unexpected error occurred during registration.",
-				error: (e as Error).message,
-			}, 500);
-		}
-	}
-});

packages/backend/root.ts

@@ -1,31 +0,0 @@
-import { getSingerForBirthday, pickSinger, pickSpecialSinger, type Singer } from "./singers.ts";
-import { VERSION } from "./main.ts";
-import { createHandlers } from "./utils.ts";
-
-export const rootHandler = createHandlers((c) => {
-	let singer: Singer | Singer[] | null = null;
-	const shouldShowSpecialSinger = Math.random() < 0.016;
-	if (getSingerForBirthday().length !== 0){
-		singer = getSingerForBirthday();
-		for (const s of singer) {
-			delete s.birthday;
-			s.message = `祝${s.name}生日快乐~`
-		}
-	}
-	else if (shouldShowSpecialSinger) {
-	    singer = pickSpecialSinger();
-	}
-	else {
-	    singer = pickSinger();
-	}
-	return c.json({
-		"project": {
-			"name": "中V档案馆",
-			"motto": "一起唱吧，心中的歌！"
-		},
-		"status": 200,
-		"version": VERSION,
-		"time": Date.now(),
-		"singer": singer
-	})
-})

packages/backend/routes/404.ts

@@ -0,0 +1,10 @@
+import { Context } from "hono";
+
+export const notFoundRoute = (c: Context) => {
+	return c.json(
+		{
+			message: "Not Found"
+		},
+		404
+	);
+};

packages/backend/routes/captcha/[id]/result/GET.ts

@@ -0,0 +1,99 @@
+import { Context } from "hono";
+import { Bindings, BlankEnv } from "hono/types";
+import { ErrorResponse } from "src/schema";
+import { createHandlers } from "src/utils.ts";
+import { sign } from "hono/jwt";
+import { generateRandomId } from "@core/lib/randomID.ts";
+import { getJWTsecret } from "lib/auth/getJWTsecret.ts";
+
+interface CaptchaResponse {
+	success: boolean;
+	difficulty?: number;
+	error?: string;
+}
+
+const getChallengeVerificationResult = async (id: string, ans: string) => {
+	const baseURL = process.env["UCAPTCHA_URL"];
+	const url = new URL(baseURL);
+	url.pathname = `/challenge/${id}/validation`;
+	return await fetch(url.toString(), {
+		method: "POST",
+		headers: {
+			"Content-Type": "application/json"
+		},
+		body: JSON.stringify({
+			y: ans
+		})
+	});
+};
+
+export const verifyChallengeHandler = createHandlers(
+	async (c: Context<BlankEnv & { Bindings: Bindings }, "/captcha/:id/result">) => {
+		const id = c.req.param("id");
+		const ans = c.req.query("ans");
+		if (!ans) {
+			const response: ErrorResponse = {
+				message: "Missing required query parameter: ans",
+				code: "INVALID_QUERY_PARAMS",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 400);
+		}
+		const res = await getChallengeVerificationResult(id, ans);
+		const data: CaptchaResponse = await res.json();
+		if (data.error && res.status === 404) {
+			const response: ErrorResponse = {
+				message: data.error,
+				code: "ENTITY_NOT_FOUND",
+				i18n: {
+					key: "backend.error.captcha_not_found"
+				},
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 401);
+		} else if (data.error && res.status === 400) {
+			const response: ErrorResponse = {
+				message: data.error,
+				code: "INVALID_QUERY_PARAMS",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 400);
+		} else if (data.error) {
+			const response: ErrorResponse = {
+				message: data.error,
+				code: "UNKNOWN_ERROR",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 500);
+		}
+		if (!data.success) {
+			const response: ErrorResponse = {
+				message: "Incorrect answer",
+				code: "INVALID_CREDENTIALS",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 401);
+		}
+
+		const [r, err] = getJWTsecret();
+		if (err) {
+			return c.json<ErrorResponse>(r as ErrorResponse, 500);
+		}
+		const jwtSecret = r as string;
+
+		const tokenID = generateRandomId(6);
+		const NOW = Math.floor(Date.now() / 1000);
+		const FIVE_MINUTES_LATER = NOW + 60 * 5;
+		const jwt = await sign(
+			{
+				difficulty: data.difficulty!,
+				id: tokenID,
+				exp: FIVE_MINUTES_LATER
+			},
+			jwtSecret
+		);
+		return c.json({
+			token: jwt
+		});
+	}
+);

packages/backend/routes/captcha/difficulty/GET.ts

@@ -0,0 +1,44 @@
+import { createHandlers } from "src/utils.ts";
+import { object, string, ValidationError } from "yup";
+import { ErrorResponse } from "src/schema";
+import { getCurrentCaptchaDifficulty } from "@/lib/auth/captchaDifficulty.ts";
+import { sqlCred } from "@core/db/dbNew.ts";
+
+const queryParamsSchema = object({
+	route: string().matches(/(?:GET|POST|PUT|PATCH|DELETE)-\/.*/g)
+});
+
+export const getCaptchaDifficultyHandler = createHandlers(async (c) => {
+	try {
+		const queryParams = await queryParamsSchema.validate(c.req.query());
+		const { route } = queryParams;
+		const difficulty = await getCurrentCaptchaDifficulty(sqlCred, route);
+		if (!difficulty) {
+			const response: ErrorResponse<unknown> = {
+				code: "ENTITY_NOT_FOUND",
+				message: "No difficulty configs found for this route.",
+				errors: []
+			};
+			return c.json<ErrorResponse<unknown>>(response, 404);
+		}
+		return c.json({
+			difficulty: difficulty
+		});
+	} catch (e: unknown) {
+		if (e instanceof ValidationError) {
+			const response: ErrorResponse = {
+				code: "INVALID_QUERY_PARAMS",
+				message: "Invalid query parameters",
+				errors: e.errors
+			};
+			return c.json<ErrorResponse>(response, 400);
+		} else {
+			const response: ErrorResponse<unknown> = {
+				code: "UNKNOWN_ERROR",
+				message: "Unknown error",
+				errors: [e]
+			};
+			return c.json<ErrorResponse<unknown>>(response, 500);
+		}
+	}
+});

packages/backend/routes/captcha/index.ts

@@ -0,0 +1,2 @@
+export * from "./session/POST.ts";
+export * from "./[id]/result/GET.ts";

packages/backend/routes/captcha/session/POST.ts

@@ -0,0 +1,51 @@
+import { createHandlers } from "src/utils.ts";
+import { getCurrentCaptchaDifficulty } from "@/lib/auth/captchaDifficulty.ts";
+import { sqlCred } from "@core/db/dbNew.ts";
+import { object, string, ValidationError } from "yup";
+import { CaptchaSessionResponse, ErrorResponse } from "@/src/schema";
+import type { ContentfulStatusCode } from "hono/utils/http-status";
+
+const bodySchema = object({
+	route: string().matches(/(?:GET|POST|PUT|PATCH|DELETE)-\/.*/g)
+});
+
+const createNewChallenge = async (difficulty: number) => {
+	const baseURL = process.env["UCAPTCHA_URL"];
+	const url = new URL(baseURL);
+	url.pathname = "/challenge";
+	return await fetch(url.toString(), {
+		method: "POST",
+		headers: {
+			"Content-Type": "application/json"
+		},
+		body: JSON.stringify({
+			difficulty: difficulty
+		})
+	});
+};
+
+export const createCaptchaSessionHandler = createHandlers(async (c) => {
+	try {
+		const requestBody = await bodySchema.validate(await c.req.json());
+		const { route } = requestBody;
+		const difficuly = await getCurrentCaptchaDifficulty(sqlCred, route);
+		const res = await createNewChallenge(difficuly);
+		return c.json<CaptchaSessionResponse | unknown>(await res.json(), res.status as ContentfulStatusCode);
+	} catch (e: unknown) {
+		if (e instanceof ValidationError) {
+			const response: ErrorResponse = {
+				code: "INVALID_QUERY_PARAMS",
+				message: "Invalid query parameters",
+				errors: e.errors
+			};
+			return c.json<ErrorResponse>(response, 400);
+		} else {
+			const response: ErrorResponse<unknown> = {
+				code: "UNKNOWN_ERROR",
+				message: "Unknown error",
+				errors: [e]
+			};
+			return c.json<ErrorResponse<unknown>>(response, 500);
+		}
+	}
+});

packages/backend/routes/index.ts

@@ -0,0 +1,29 @@
+import { getSingerForBirthday, pickSinger, pickSpecialSinger, type Singer } from "lib/const/singers.ts";
+import { VERSION } from "src/main.ts";
+import { createHandlers } from "src/utils.ts";
+
+export const rootHandler = createHandlers((c) => {
+	let singer: Singer | Singer[];
+	const shouldShowSpecialSinger = Math.random() < 0.016;
+	if (getSingerForBirthday().length !== 0) {
+		singer = JSON.parse(JSON.stringify(getSingerForBirthday())) as Singer[];
+		for (const s of singer) {
+			delete s.birthday;
+			s.message = `祝${s.name}生日快乐~`;
+		}
+	} else if (shouldShowSpecialSinger) {
+		singer = pickSpecialSinger();
+	} else {
+		singer = pickSinger();
+	}
+	return c.json({
+		project: {
+			name: "中V档案馆",
+			motto: "一起唱吧，心中的歌！"
+		},
+		status: 200,
+		version: VERSION,
+		time: Date.now(),
+		singer: singer
+	});
+});

packages/backend/routes/login/session/POST.ts

@@ -0,0 +1,105 @@
+import { Context } from "hono";
+import { Bindings, BlankEnv } from "hono/types";
+import { ErrorResponse, LoginResponse } from "src/schema";
+import { createHandlers } from "src/utils.ts";
+import { sqlCred } from "@core/db/dbNew";
+import { object, string, ValidationError } from "yup";
+import { setCookie } from "hono/cookie";
+import Argon2id from "@rabbit-company/argon2id";
+import { createLoginSession } from "routes/user/POST";
+import { UserType } from "@core/db/schema";
+
+const LoginBodySchema = object({
+	username: string().trim().required("Username is required").max(50, "Username cannot exceed 50 characters"),
+	password: string().required("Password is required")
+});
+
+export const loginHandler = createHandlers(
+	async (c: Context<BlankEnv & { Bindings: Bindings }, "/user/session/:id">) => {
+		try {
+			const body = await LoginBodySchema.validate(await c.req.json());
+			const { username, password: submittedPassword } = body;
+
+			const result = await sqlCred<UserType[]>`
+			    SELECT *
+				FROM users
+				WHERE username = ${username}
+			`;
+
+			if (result.length === 0) {
+				const response: ErrorResponse<string> = {
+					message: `User does not exist.`,
+					errors: [`User ${username} does not exist.`],
+					code: "ENTITY_NOT_FOUND"
+				};
+				return c.json<ErrorResponse<string>>(response, 400);
+			}
+
+			const storedPassword = result[0].password;
+			const uid = result[0].id;
+			const nickname = result[0].nickname;
+			const role = result[0].role;
+
+			const passwordAreSame = await Argon2id.verify(storedPassword, submittedPassword);
+
+			if (!passwordAreSame) {
+				const response: ErrorResponse<string> = {
+					message: "Incorrect password.",
+					errors: [],
+					i18n: {
+						key: "backend.error.incorrect_password"
+					},
+					code: "INVALID_CREDENTIALS"
+				};
+				return c.json<ErrorResponse<string>>(response, 401);
+			}
+
+			const sessionID = await createLoginSession(uid, c);
+
+			const response: LoginResponse = {
+				uid: uid,
+				username: username,
+				nickname: nickname,
+				role: role,
+				token: sessionID
+			};
+
+			const A_YEAR = 365 * 86400;
+			const isDev = process.env.NODE_ENV === "development";
+
+			setCookie(c, "session_id", sessionID, {
+				path: "/",
+				maxAge: A_YEAR,
+				domain: process.env.DOMAIN,
+				secure: isDev ? true : true,
+				sameSite: isDev ? "None" : "Lax",
+				httpOnly: true
+			});
+
+			return c.json<LoginResponse>(response, 200);
+		} catch (e) {
+			if (e instanceof ValidationError) {
+				const response: ErrorResponse<string> = {
+					message: "Invalid registration data.",
+					errors: e.errors,
+					code: "INVALID_PAYLOAD"
+				};
+				return c.json<ErrorResponse<string>>(response, 400);
+			} else if (e instanceof SyntaxError) {
+				const response: ErrorResponse<string> = {
+					message: "Invalid JSON payload.",
+					errors: [e.message],
+					code: "INVALID_FORMAT"
+				};
+				return c.json<ErrorResponse<string>>(response, 400);
+			} else {
+				const response: ErrorResponse<string> = {
+					message: "Unknown error.",
+					errors: [(e as Error).message],
+					code: "UNKNOWN_ERROR"
+				};
+				return c.json<ErrorResponse<string>>(response, 500);
+			}
+		}
+	}
+);

packages/backend/routes/ping/index.ts

@@ -0,0 +1,24 @@
+import { getClientIP } from "middleware/logger.ts";
+import { createHandlers } from "src/utils.ts";
+import { VERSION } from "src/main.ts";
+
+export const pingHandler = createHandlers(async (c) => {
+	const requestHeaders = c.req.raw.headers;
+	return c.json({
+		message: "pong",
+		request: {
+			headers: requestHeaders,
+			ip: getClientIP(c),
+			mode: c.req.raw.mode,
+			method: c.req.method,
+			query: new URL(c.req.url).searchParams,
+			body: await c.req.text(),
+			url: c.req.raw.url
+		},
+		response: {
+			time: new Date().getTime(),
+			status: 200,
+			version: VERSION
+		}
+	});
+});

packages/backend/routes/session/[id]/DELETE.ts

@@ -0,0 +1,75 @@
+import { Context } from "hono";
+import { Bindings, BlankEnv } from "hono/types";
+import { ErrorResponse } from "src/schema";
+import { createHandlers } from "src/utils.ts";
+import { sqlCred } from "@core/db/dbNew";
+import { object, string, ValidationError } from "yup";
+import { setCookie } from "hono/cookie";
+
+const loginSessionExists = async (sessionID: string) => {
+	const result = await sqlCred`
+        SELECT 1
+        FROM login_sessions
+        WHERE id = ${sessionID}
+	`;
+	return result.length > 0;
+};
+
+export const logoutHandler = createHandlers(async (c: Context<BlankEnv & { Bindings: Bindings }, "/session/:id">) => {
+	try {
+		const session_id = c.req.param("id");
+
+		const exists = loginSessionExists(session_id);
+
+		if (!exists) {
+			const response: ErrorResponse<string> = {
+				message: "Cannot found given session_id.",
+				errors: [`Session ${session_id} not found`],
+				code: "ENTITY_NOT_FOUND"
+			};
+			return c.json<ErrorResponse<string>>(response, 404);
+		}
+
+		await sqlCred`
+		    UPDATE login_sessions
+			SET deactivated_at = CURRENT_TIMESTAMP
+			WHERE id = ${session_id}
+		`;
+
+		const isDev = process.env.NODE_ENV === "development";
+
+		setCookie(c, "session_id", "", {
+			path: "/",
+			maxAge: 0,
+			domain: process.env.DOMAIN,
+			secure: isDev ? true : true,
+			sameSite: isDev ? "None" : "Lax",
+			httpOnly: true
+		});
+
+		return c.body(null, 204);
+	} catch (e) {
+		if (e instanceof ValidationError) {
+			const response: ErrorResponse<string> = {
+				message: "Invalid registration data.",
+				errors: e.errors,
+				code: "INVALID_PAYLOAD"
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
+		} else if (e instanceof SyntaxError) {
+			const response: ErrorResponse<string> = {
+				message: "Invalid JSON payload.",
+				errors: [e.message],
+				code: "INVALID_FORMAT"
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
+		} else {
+			const response: ErrorResponse<string> = {
+				message: "Unknown error.",
+				errors: [(e as Error).message],
+				code: "UNKNOWN_ERROR"
+			};
+			return c.json<ErrorResponse<string>>(response, 500);
+		}
+	}
+});

packages/backend/routes/session/index.ts

@@ -0,0 +1 @@
+export * from "./[id]/DELETE";

packages/backend/routes/user/POST.ts

@@ -0,0 +1,140 @@
+import { createHandlers } from "src/utils.ts";
+import Argon2id from "@rabbit-company/argon2id";
+import { object, string, ValidationError } from "yup";
+import type { Context } from "hono";
+import type { Bindings, BlankEnv, BlankInput } from "hono/types";
+import { sqlCred } from "@core/db/dbNew.ts";
+import { ErrorResponse, SignUpResponse } from "src/schema";
+import { generateRandomId } from "@core/lib/randomID";
+import { getUserIP } from "@/middleware/rateLimiters";
+import { setCookie } from "hono/cookie";
+
+const RegistrationBodySchema = object({
+	username: string().trim().required("Username is required").max(50, "Username cannot exceed 50 characters"),
+	password: string().required("Password is required"),
+	nickname: string().optional()
+});
+
+type ContextType = Context<BlankEnv & { Bindings: Bindings }, "/user", BlankInput>;
+
+export const userExists = async (username: string) => {
+	const result = await sqlCred`
+        SELECT 1
+        FROM users
+        WHERE username = ${username}
+	`;
+	return result.length > 0;
+};
+
+export const createLoginSession = async (uid: number, c: Context): Promise<string> => {
+	const ipAddress = getUserIP(c) || null;
+	const userAgent = c.req.header("User-Agent") || null;
+	const id = generateRandomId(24);
+	await sqlCred`
+        INSERT INTO login_sessions (id, uid, expire_at, ip_address, user_agent)
+        VALUES (${id}, ${uid}, CURRENT_TIMESTAMP + INTERVAL '1 year', ${ipAddress}, ${userAgent})
+    `;
+	return id;
+};
+
+const getUserIDByName = async (username: string) => {
+	const result = await sqlCred<{ id: number }[]>`
+        SELECT id
+        FROM users
+        WHERE username = ${username}
+    `;
+	if (result.length === 0) {
+		return null;
+	}
+	return result[0].id;
+};
+
+export const registerHandler = createHandlers(async (c: ContextType) => {
+	try {
+		const body = await RegistrationBodySchema.validate(await c.req.json());
+		const { username, password, nickname } = body;
+
+		if (await userExists(username)) {
+			const response: ErrorResponse = {
+				message: `User "${username}" already exists.`,
+				code: "ENTITY_EXISTS",
+				errors: [],
+				i18n: {
+					key: "backend.error.user_exists",
+					values: {
+						username: username
+					}
+				}
+			};
+			return c.json<ErrorResponse>(response, 400);
+		}
+
+		const hash = await Argon2id.hashEncoded(password);
+
+		await sqlCred`
+            INSERT INTO users (username, password, nickname)
+            VALUES (${username}, ${hash}, ${nickname ? nickname : null})
+		`;
+
+		const uid = await getUserIDByName(username);
+
+		if (!uid) {
+			const response: ErrorResponse<string> = {
+				message: "Cannot find registered user.",
+				errors: [`Cannot find user ${username} in table 'users'.`],
+				code: "ENTITY_NOT_FOUND",
+				i18n: {
+					key: "backend.error.user_not_found_after_register",
+					values: {
+						username: username
+					}
+				}
+			};
+			return c.json<ErrorResponse<string>>(response, 500);
+		}
+
+		const sessionID = await createLoginSession(uid, c);
+
+		const response: SignUpResponse = {
+			username: username,
+			token: sessionID
+		};
+
+		const A_YEAR = 365 * 86400;
+		const isDev = process.env.NODE_ENV === "development";
+
+		setCookie(c, "session_id", sessionID, {
+			path: "/",
+			maxAge: A_YEAR,
+			domain: process.env.DOMAIN,
+			secure: isDev ? false : true,
+			sameSite: "Lax",
+			httpOnly: true
+		});
+
+		return c.json<SignUpResponse>(response, 201);
+	} catch (e) {
+		if (e instanceof ValidationError) {
+			const response: ErrorResponse<string> = {
+				message: "Invalid registration data.",
+				errors: e.errors,
+				code: "INVALID_PAYLOAD"
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
+		} else if (e instanceof SyntaxError) {
+			const response: ErrorResponse<string> = {
+				message: "Invalid JSON payload.",
+				errors: [e.message],
+				code: "INVALID_FORMAT"
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
+		} else {
+			const response: ErrorResponse<string> = {
+				message: "Unknown error.",
+				errors: [(e as Error).message],
+				code: "UNKNOWN_ERROR"
+			};
+			return c.json<ErrorResponse<string>>(response, 500);
+		}
+	}
+});

packages/backend/routes/user/index.ts

@@ -0,0 +1,2 @@
+export * from "./POST.ts";
+export * from "./session/[id]/GET.ts";

packages/backend/routes/user/session/[id]/GET.ts

@@ -0,0 +1,32 @@
+import { Context } from "hono";
+import { Bindings, BlankEnv } from "hono/types";
+import { ErrorResponse } from "src/schema";
+import { createHandlers } from "src/utils.ts";
+import { sqlCred } from "@core/db/dbNew";
+import { UserType } from "@core/db/schema";
+
+export const getUserByLoginSessionHandler = createHandlers(
+	async (c: Context<BlankEnv & { Bindings: Bindings }, "/user/session/:id">) => {
+		const id = c.req.param("id");
+		const users = await sqlCred<UserType[]>`
+    		SELECT u.*
+            FROM users u
+            JOIN login_sessions ls ON u.id = ls.uid
+            WHERE ls.id = ${id};
+        `;
+		if (users.length === 0) {
+			const response: ErrorResponse = {
+				message: "Cannot find user",
+				code: "ENTITY_NOT_FOUND",
+				errors: []
+			};
+			return c.json<ErrorResponse>(response, 404);
+		}
+		const user = users[0];
+		return c.json({
+			username: user.username,
+			nickname: user.nickname,
+			role: user.role
+		});
+	}
+);

packages/backend/routes/video/[id]/info.ts

@@ -0,0 +1,85 @@
+import logger from "@core/log/logger.ts";
+import { redis } from "@core/db/redis.ts";
+import { sql } from "@core/db/dbNew.ts";
+import { number, ValidationError } from "yup";
+import { createHandlers } from "@/src/utils.ts";
+import { getVideoInfo, getVideoInfoByBV } from "@core/net/getVideoInfo.ts";
+import { idSchema } from "./snapshots.ts";
+import { NetSchedulerError } from "@core/net/delegate.ts";
+import type { Context } from "hono";
+import type { BlankEnv, BlankInput } from "hono/types";
+import type { VideoInfoData } from "@core/net/bilibili.d.ts";
+import { startTime, endTime } from "hono/timing";
+
+const CACHE_EXPIRATION_SECONDS = 60;
+
+type ContextType = Context<BlankEnv, "/video/:id/info", BlankInput>;
+
+async function insertVideoSnapshot(data: VideoInfoData) {
+	const views = data.stat.view;
+	const danmakus = data.stat.danmaku;
+	const replies = data.stat.reply;
+	const likes = data.stat.like;
+	const coins = data.stat.coin;
+	const shares = data.stat.share;
+	const favorites = data.stat.favorite;
+	const aid = data.aid;
+
+	await sql`
+        INSERT INTO video_snapshot (aid, views, danmakus, replies, likes, coins, shares, favorites)
+        VALUES (${aid}, ${views}, ${danmakus}, ${replies}, ${likes}, ${coins}, ${shares}, ${favorites})
+    `;
+
+	logger.log(`Inserted into snapshot for video ${aid} by videoInfo API.`, "api", "fn:insertVideoSnapshot");
+}
+
+export const videoInfoHandler = createHandlers(async (c: ContextType) => {
+	startTime(c, "parse", "Parse the request");
+	try {
+		const id = await idSchema.validate(c.req.param("id"));
+		let videoId: string | number = id as string;
+		if (videoId.startsWith("av")) {
+			videoId = parseInt(videoId.slice(2));
+		} else if (await number().isValid(videoId)) {
+			videoId = parseInt(videoId);
+		}
+
+		const cacheKey = `cvsa:videoInfo:${videoId}`;
+		endTime(c, "parse");
+		startTime(c, "cache", "Check for cached data");
+		const cachedData = await redis.get(cacheKey);
+		endTime(c, "cache");
+		if (cachedData) {
+			return c.json(JSON.parse(cachedData));
+		}
+		startTime(c, "net", "Fetch data");
+		let result: VideoInfoData | number;
+		if (typeof videoId === "number") {
+			result = await getVideoInfo(videoId, "getVideoInfo");
+		} else {
+			result = await getVideoInfoByBV(videoId, "getVideoInfo");
+		}
+		endTime(c, "net");
+
+		if (typeof result === "number") {
+			return c.json({ message: "Error fetching video info", code: result }, 500);
+		}
+
+		startTime(c, "db", "Write data to database");
+
+		await redis.setex(cacheKey, CACHE_EXPIRATION_SECONDS, JSON.stringify(result));
+
+		await insertVideoSnapshot(result);
+
+		endTime(c, "db");
+		return c.json(result);
+	} catch (e) {
+		if (e instanceof ValidationError) {
+			return c.json({ message: "Invalid query parameters", errors: e.errors }, 400);
+		} else if (e instanceof NetSchedulerError) {
+			return c.json({ message: "Error fetching video info", code: e.code }, 500);
+		} else {
+			return c.json({ message: "Unhandled error", error: e }, 500);
+		}
+	}
+});

packages/backend/routes/video/[id]/snapshots.ts

@@ -1,23 +1,25 @@
 import type { Context } from "hono";
-import { createHandlers } from "./utils.ts";
+import { createHandlers } from "src/utils.ts";
 import type { BlankEnv, BlankInput } from "hono/types";
-import { getVideoSnapshots, getVideoSnapshotsByBV } from "@core/db/videoSnapshot.ts";
+import { getVideoSnapshots, getVideoSnapshotsByBV } from "db/snapshots.ts";
 import type { VideoSnapshotType } from "@core/db/schema.d.ts";
 import { boolean, mixed, number, object, ValidationError } from "yup";
+import { ErrorResponse } from "src/schema";
+import { startTime, endTime } from "hono/timing";
 
 const SnapshotQueryParamsSchema = object({
 	ps: number().integer().optional().positive(),
 	pn: number().integer().optional().positive(),
 	offset: number().integer().optional().positive(),
-	reverse: boolean().optional(),
+	reverse: boolean().optional()
 });
 
-const idSchema = mixed().test(
+export const idSchema = mixed().test(
 	"is-valid-id",
 	'id must be a string starting with "av" followed by digits, or "BV" followed by 10 alphanumeric characters, or a positive integer',
 	async (value) => {
-		if (value && await number().integer().isValid(value)) {
-            const v = parseInt(value as string);
+		if (value && (await number().integer().isValid(value))) {
+			const v = parseInt(value as string);
 			return Number.isInteger(v) && v > 0;
 		}
 
@@ -34,22 +36,20 @@ const idSchema = mixed().test(
 		}
 
 		return false;
-	},
+	}
 );
 
 type ContextType = Context<BlankEnv, "/video/:id/snapshots", BlankInput>;
 export const getSnapshotsHanlder = createHandlers(async (c: ContextType) => {
-	const client = c.get("db");
-
+	startTime(c, "parse", "Parse the request");
 	try {
 		const idParam = await idSchema.validate(c.req.param("id"));
 		let videoId: string | number = idParam as string;
 		if (videoId.startsWith("av")) {
 			videoId = parseInt(videoId.slice(2));
-		}
-        else if (await number().isValid(videoId)) {
+		} else if (await number().isValid(videoId)) {
 			videoId = parseInt(videoId);
-        }
+		}
 		const queryParams = await SnapshotQueryParamsSchema.validate(c.req.query());
 		const { ps, pn, offset, reverse = false } = queryParams;
 
@@ -71,23 +71,36 @@ export const getSnapshotsHanlder = createHandlers(async (c: ContextType) => {
 
 		let result: VideoSnapshotType[];
 
+		endTime(c, "parse");
+		startTime(c, "db", "Query the database");
 		if (typeof videoId === "number") {
-			result = await getVideoSnapshots(client, videoId, limit, pageOrOffset, reverse, mode);
+			result = await getVideoSnapshots(videoId, limit, pageOrOffset, reverse, mode);
 		} else {
-			result = await getVideoSnapshotsByBV(client, videoId, limit, pageOrOffset, reverse, mode);
+			result = await getVideoSnapshotsByBV(videoId, limit, pageOrOffset, reverse, mode);
 		}
+		endTime(c, "db");
 
 		const rows = result.map((row) => ({
 			...row,
-			aid: Number(row.aid),
+			aid: Number(row.aid)
 		}));
 
 		return c.json(rows);
-	} catch (e) {
+	} catch (e: unknown) {
 		if (e instanceof ValidationError) {
-			return c.json({ message: "Invalid query parameters", errors: e.errors }, 400);
+			const response: ErrorResponse<string> = {
+				code: "INVALID_QUERY_PARAMS",
+				message: "Invalid query parameters",
+				errors: e.errors
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
 		} else {
-			return c.json({ message: "Unhandled error", error: e }, 500);
+			const response: ErrorResponse<unknown> = {
+				code: "UNKNOWN_ERROR",
+				message: "Unhandled error",
+				errors: [e]
+			};
+			return c.json<ErrorResponse<unknown>>(response, 500);
 		}
 	}
 });

packages/backend/routes/video/index.ts

@@ -0,0 +1,2 @@
+export * from "./[id]/info";
+export * from "./[id]/snapshots";

packages/backend/routes/videos/GET.ts

@@ -0,0 +1,65 @@
+import type { Context } from "hono";
+import { createHandlers } from "src/utils.ts";
+import type { BlankEnv, BlankInput } from "hono/types";
+import { number, object, ValidationError } from "yup";
+import { ErrorResponse } from "src/schema";
+import { startTime, endTime } from "hono/timing";
+import { getVideosInViewsRange } from "@/db/latestSnapshots";
+
+const SnapshotQueryParamsSchema = object({
+	min_views: number().integer().optional().positive(),
+	max_views: number().integer().optional().positive()
+});
+
+type ContextType = Context<BlankEnv, "/videos", BlankInput>;
+
+export const getVideosHanlder = createHandlers(async (c: ContextType) => {
+	startTime(c, "parse", "Parse the request");
+	try {
+		const queryParams = await SnapshotQueryParamsSchema.validate(c.req.query());
+		const { min_views, max_views } = queryParams;
+
+		if (!min_views && !max_views) {
+			const response: ErrorResponse<string> = {
+				code: "INVALID_QUERY_PARAMS",
+				message: "Invalid query parameters",
+				errors: ["Must provide one of these query parameters: min_views, max_views"]
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
+		}
+
+		endTime(c, "parse");
+
+		startTime(c, "db", "Query the database");
+
+		const minViews = min_views ? min_views : 0;
+		const maxViews = max_views ? max_views : 2147483647;
+
+		const result = await getVideosInViewsRange(minViews, maxViews);
+
+		endTime(c, "db");
+
+		const rows = result.map((row) => ({
+			...row,
+			aid: Number(row.aid)
+		}));
+
+		return c.json(rows);
+	} catch (e: unknown) {
+		if (e instanceof ValidationError) {
+			const response: ErrorResponse<string> = {
+				code: "INVALID_QUERY_PARAMS",
+				message: "Invalid query parameters",
+				errors: e.errors
+			};
+			return c.json<ErrorResponse<string>>(response, 400);
+		} else {
+			const response: ErrorResponse<unknown> = {
+				code: "UNKNOWN_ERROR",
+				message: "Unhandled error",
+				errors: [e]
+			};
+			return c.json<ErrorResponse<unknown>>(response, 500);
+		}
+	}
+});

packages/backend/routes/videos/index.ts

@@ -0,0 +1 @@
+export * from "./GET.ts";

packages/backend/singers.ts

@@ -1,103 +0,0 @@
-export const singers = [
-	{
-		"name": "洛天依",
-		"color": "#66CCFF",
-		"birthday": "0712",
-	},
-	{
-		"name": "言和",
-		"color": "#00FFCC",
-		"birthday": "0711",
-	},
-	{
-		"name": "乐正绫",
-		"color": "#EE0000",
-		"birthday": "0412",
-	},
-	{
-		"name": "乐正龙牙",
-		"color": "#006666",
-		"birthday": "1002",
-	},
-	{
-		"name": "徵羽摩柯",
-		"color": "#0080FF",
-		"birthday": "1210",
-	},
-	{
-		"name": "墨清弦",
-		"color": "#FFFF00",
-		"birthday": "0520",
-	},
-	{
-		"name": "星尘",
-		"color": "#9999FF",
-		"birthday": "0812",
-	},
-	{
-		"name": "心华",
-		"color": "#EE82EE",
-		"birthday": "0210",
-	},
-	{
-		"name": "海伊",
-		"color": "#3399FF",
-		"birthday": "0722",
-	},
-	{
-		"name": "苍穹",
-		"color": "#8BC0B5",
-		"birthday": "0520",
-	},
-	{
-		"name": "赤羽",
-		"color": "#FF4004",
-		"birthday": "1126",
-	},
-	{
-		"name": "诗岸",
-		"color": "#F6BE72",
-		"birthday": "0119",
-	},
-	{
-		"name": "牧心",
-		"color": "#2A2859",
-		"birthday": "0807",
-	},
-];
-
-export interface Singer {
-	name: string;
-	color?: string;
-	birthday?: string;
-    message?: string;
-}
-
-export const specialSingers = [
-	{
-		"name": "雅音宫羽",
-		"message": "你是我最真模样，从来不曾遗忘。",
-	},
-	{
-		"name": "初音未来",
-		"message": "初始之音，响彻未来!",
-	},
-];
-
-export const pickSinger = () => {
-	const index = Math.floor(Math.random() * singers.length);
-	return singers[index];
-};
-
-export const pickSpecialSinger = () => {
-	const index = Math.floor(Math.random() * specialSingers.length);
-	return specialSingers[index];
-};
-
-export const getSingerForBirthday = (): Singer[] => {
-	const today = new Date();
-	const month = String(today.getMonth() + 1).padStart(2, "0");
-	const day = String(today.getDate()).padStart(2, "0");
-	const datestring = `${month}${day}`;
-	return singers.filter((singer) => singer.birthday === datestring);
-};

packages/backend/src/main.ts

@@ -0,0 +1,18 @@
+import { Hono } from "hono";
+import type { TimingVariables } from "hono/timing";
+import { startServer } from "./startServer.ts";
+import { configureRoutes } from "./routing.ts";
+import { configureMiddleWares } from "./middleware.ts";
+import { notFoundRoute } from "routes/404.ts";
+
+type Variables = TimingVariables;
+const app = new Hono<{ Variables: Variables }>();
+
+app.notFound(notFoundRoute);
+
+configureMiddleWares(app);
+configureRoutes(app);
+
+await startServer(app);
+
+export const VERSION = "0.6.0";

packages/backend/src/middleware.ts

@@ -0,0 +1,24 @@
+import { Hono } from "hono";
+import { timing } from "hono/timing";
+import { Variables } from "hono/types";
+import { pingHandler } from "routes/ping";
+import { logger } from "middleware/logger.ts";
+import { corsMiddleware } from "@/middleware/cors";
+import { contentType } from "middleware/contentType.ts";
+import { captchaMiddleware } from "middleware/captcha.ts";
+import { bodyLimitForPing } from "middleware/bodyLimits.ts";
+import { registerRateLimiter } from "middleware/rateLimiters.ts";
+import { preetifyResponse } from "middleware/preetifyResponse.ts";
+
+export function configureMiddleWares(app: Hono<{ Variables: Variables }>) {
+	app.use("*", corsMiddleware);
+
+	app.use("*", contentType);
+	app.use(timing());
+	app.use("*", preetifyResponse);
+	app.use("*", logger({}));
+
+	app.post("/user", registerRateLimiter);
+	app.post("/user", captchaMiddleware);
+	app.all("/ping", bodyLimitForPing, ...pingHandler);
+}

packages/backend/src/routing.ts

@@ -0,0 +1,32 @@
+import { rootHandler } from "routes";
+import { pingHandler } from "routes/ping";
+import { getUserByLoginSessionHandler, registerHandler } from "routes/user";
+import { videoInfoHandler, getSnapshotsHanlder } from "routes/video";
+import { Hono } from "hono";
+import { Variables } from "hono/types";
+import { createCaptchaSessionHandler, verifyChallengeHandler } from "routes/captcha";
+import { getCaptchaDifficultyHandler } from "routes/captcha/difficulty/GET.ts";
+import { getVideosHanlder } from "@/routes/videos";
+import { loginHandler } from "@/routes/login/session/POST";
+import { logoutHandler } from "@/routes/session";
+
+export function configureRoutes(app: Hono<{ Variables: Variables }>) {
+	app.get("/", ...rootHandler);
+	app.all("/ping", ...pingHandler);
+
+	app.get("/videos", ...getVideosHanlder);
+
+	app.get("/video/:id/snapshots", ...getSnapshotsHanlder);
+	app.get("/video/:id/info", ...videoInfoHandler);
+
+	app.post("/login/session", ...loginHandler);
+
+	app.delete("/session/:id", ...logoutHandler);
+
+	app.post("/user", ...registerHandler);
+	app.get("/user/session/:id", ...getUserByLoginSessionHandler);
+
+	app.post("/captcha/session", ...createCaptchaSessionHandler);
+	app.get("/captcha/:id/result", ...verifyChallengeHandler);
+	app.get("/captcha/difficulty", ...getCaptchaDifficultyHandler);
+}

packages/backend/src/schema.d.ts

@@ -0,0 +1,66 @@
+export type ErrorCode =
+	| "INVALID_QUERY_PARAMS"
+	| "UNKNOWN_ERROR"
+	| "INVALID_PAYLOAD"
+	| "INVALID_FORMAT"
+	| "INVALID_HEADER"
+	| "BODY_TOO_LARGE"
+	| "UNAUTHORIZED"
+	| "INVALID_CREDENTIALS"
+	| "ENTITY_NOT_FOUND"
+	| "SERVER_ERROR"
+	| "RATE_LIMIT_EXCEEDED"
+	| "ENTITY_EXISTS";
+
+export interface ErrorResponse<E = string> {
+	code: ErrorCode;
+	message: string;
+	errors: E[] = [];
+	i18n?: {
+		key: string;
+		values?: {
+			[key: string]: string | number | Date;
+		};
+	};
+}
+
+export interface StatusResponse {
+	message: string;
+}
+
+export type CaptchaSessionResponse = ErrorResponse | CaptchaSessionRawResponse;
+
+interface CaptchaSessionRawResponse {
+	success: boolean;
+	id: string;
+	g: string;
+	n: string;
+	t: number;
+}
+
+export interface LoginResponse {
+    uid: number;
+    username: string;
+    nickname: string | null;
+    role: string;
+    token: string;
+}
+
+export interface SignUpResponse {
+    username: string;
+    token: string;
+}
+
+export interface UserResponse {
+    username: string;
+    nickname: string | null;
+    role: string;
+}
+
+export type CaptchaVerificationRawResponse = {
+	token: string;
+}
+
+export type CaptchaVerificationResponse =
+	| ErrorResponse
+	| CaptchaVerificationRawResponse;

packages/backend/src/startServer.ts

@@ -0,0 +1,91 @@
+import { serve } from "bun";
+import { Hono } from "hono";
+import os from "os";
+import { BlankSchema, Variables } from "hono/types";
+
+function getLocalIpAddress(): string {
+	const interfaces = os.networkInterfaces();
+	for (const name of Object.keys(interfaces)) {
+		for (const iface of interfaces[name]!) {
+			if (iface.family === "IPv4" && !iface.internal) {
+				return iface.address;
+			}
+		}
+	}
+	return "localhost";
+}
+
+function logStartup(hostname: string, port: number, wasAutoIncremented: boolean, originalPort: number) {
+	const localUrl = `http://localhost:${port}`;
+	const networkIp = hostname === "0.0.0.0" ? getLocalIpAddress() : "";
+	const networkUrl = networkIp ? `http://${networkIp}:${port}` : "";
+
+	console.log("\n");
+	console.log("🚀 Server is running at:");
+	console.log(`> Local:   ${localUrl}`);
+	if (networkIp) {
+		console.log(`> Network: ${networkUrl}`);
+	}
+	if (wasAutoIncremented) {
+		console.log(`\n⚠️ Port ${originalPort} is in use, using port ${port} instead.`);
+	}
+	console.log("\nPress Ctrl+C to quit.");
+}
+
+export async function startServer(app: Hono<{ Variables: Variables }>) {
+	const NODE_ENV = process.env.NODE_ENV || "production";
+	const HOST = process.env.HOST ?? (NODE_ENV === "development" ? "0.0.0.0" : "127.0.0.1");
+	const PORT = process.env.PORT ? parseInt(process.env.PORT, 10) : undefined;
+
+	const DEFAULT_PORT = 3000;
+	const MAX_ATTEMPTS = 15;
+
+	if (PORT !== undefined) {
+		try {
+			const server = serve({
+				fetch: app.fetch,
+				hostname: HOST,
+				port: PORT
+			});
+			logStartup(HOST, PORT, false, DEFAULT_PORT);
+			return server;
+		} catch (e: any) {
+			console.error(`Failed to start server on port ${PORT}:`, e.message);
+			process.exit(1);
+		}
+	}
+
+	let attemptPort = DEFAULT_PORT;
+	let success = false;
+	let error: unknown = null;
+
+	for (let i = 0; i <= MAX_ATTEMPTS; i++) {
+		try {
+			const server = serve({
+				fetch: app.fetch,
+				hostname: HOST,
+				port: attemptPort
+			});
+
+			const wasAutoIncremented = attemptPort !== DEFAULT_PORT;
+
+			logStartup(HOST, attemptPort, wasAutoIncremented, DEFAULT_PORT);
+			return server;
+		} catch (e: any) {
+			if (e.code === "EADDRINUSE") {
+				attemptPort++;
+			} else {
+				error = e;
+				break;
+			}
+		}
+	}
+
+	if (!success) {
+		console.error(`Could not find an available port after ${MAX_ATTEMPTS + 1} attempts.`);
+		if (error) {
+			console.error("Last error:", (error as Error).message);
+		}
+		process.exit(1);
+	}
+}

packages/backend/src/types.d.ts

@@ -0,0 +1 @@
+export * from "./schema";

packages/backend/src/utils.ts

@@ -0,0 +1,5 @@
+import { createFactory } from "hono/factory";
+
+const factory = createFactory();
+
+export const createHandlers = factory.createHandlers;

packages/backend/tsconfig.json

@@ -0,0 +1,20 @@
+{
+	"include": ["**/*.ts"],
+	"compilerOptions": {
+		"baseUrl": ".",
+		"target": "ESNext",
+		"module": "ESNext",
+		"useDefineForClassFields": true,
+		"moduleResolution": "bundler",
+		"lib": ["ES2020", "DOM", "DOM.Iterable"],
+		"skipLibCheck": true,
+		"paths": {
+			"@core/*": ["../core/*"],
+			"@/*": ["./*"],
+			"@crawler/*": ["../crawler/*"]
+		},
+		"allowSyntheticDefaultImports": true,
+		"allowImportingTsExtensions": true,
+		"noEmit": true
+	}
+}

packages/backend/utils.ts

@@ -1,5 +0,0 @@
-import { createFactory } from 'hono/factory'
-
-const factory = createFactory();
-
-export const createHandlers = factory.createHandlers;

packages/core/bun.lock

@@ -0,0 +1,104 @@
+{
+  "lockfileVersion": 1,
+  "workspaces": {
+    "": {
+      "dependencies": {
+        "chalk": "^5.4.1",
+        "ioredis": "^5.6.1",
+        "logform": "^2.7.0",
+        "postgres": "^3.4.5",
+        "winston": "^3.17.0",
+      },
+      "devDependencies": {
+        "@types/ioredis": "^5.0.0",
+      },
+    },
+  },
+  "packages": {
+    "@colors/colors": ["@colors/colors@1.6.0", "", {}, "sha512-Ir+AOibqzrIsL6ajt3Rz3LskB7OiMVHqltZmspbW/TJuTVuyOMirVqAkjfY6JISiLHgyNqicAC8AyHHGzNd/dA=="],
+
+    "@dabh/diagnostics": ["@dabh/diagnostics@2.0.3", "", { "dependencies": { "colorspace": "1.1.x", "enabled": "2.0.x", "kuler": "^2.0.0" } }, "sha512-hrlQOIi7hAfzsMqlGSFyVucrx38O+j6wiGOf//H2ecvIEqYN4ADBSS2iLMh5UFyDunCNniUIPk/q3riFv45xRA=="],
+
+    "@ioredis/commands": ["@ioredis/commands@1.2.0", "", {}, "sha512-Sx1pU8EM64o2BrqNpEO1CNLtKQwyhuXuqyfH7oGKCk+1a33d2r5saW8zNwm3j6BTExtjrv2BxTgzzkMwts6vGg=="],
+
+    "@types/ioredis": ["@types/ioredis@5.0.0", "", { "dependencies": { "ioredis": "*" } }, "sha512-zJbJ3FVE17CNl5KXzdeSPtdltc4tMT3TzC6fxQS0sQngkbFZ6h+0uTafsRqu+eSLIugf6Yb0Ea0SUuRr42Nk9g=="],
+
+    "@types/triple-beam": ["@types/triple-beam@1.3.5", "", {}, "sha512-6WaYesThRMCl19iryMYP7/x2OVgCtbIVflDGFpWnb9irXI3UjYE4AzmYuiUKY1AJstGijoY+MgUszMgRxIYTYw=="],
+
+    "async": ["async@3.2.6", "", {}, "sha512-htCUDlxyyCLMgaM3xXg0C0LW2xqfuQ6p05pCEIsXuyQ+a1koYKTuBMzRNwmybfLgvJDMd0r1LTn4+E0Ti6C2AA=="],
+
+    "chalk": ["chalk@5.4.1", "", {}, "sha512-zgVZuo2WcZgfUEmsn6eO3kINexW8RAE4maiQ8QNs8CtpPCSyMiYsULR3HQYkm3w8FIA3SberyMJMSldGsW+U3w=="],
+
+    "cluster-key-slot": ["cluster-key-slot@1.1.2", "", {}, "sha512-RMr0FhtfXemyinomL4hrWcYJxmX6deFdCxpJzhDttxgO1+bcCnkk+9drydLVDmAMG7NE6aN/fl4F7ucU/90gAA=="],
+
+    "color": ["color@3.2.1", "", { "dependencies": { "color-convert": "^1.9.3", "color-string": "^1.6.0" } }, "sha512-aBl7dZI9ENN6fUGC7mWpMTPNHmWUSNan9tuWN6ahh5ZLNk9baLJOnSMlrQkHcrfFgz2/RigjUVAjdx36VcemKA=="],
+
+    "color-convert": ["color-convert@1.9.3", "", { "dependencies": { "color-name": "1.1.3" } }, "sha512-QfAUtd+vFdAtFQcC8CCyYt1fYWxSqAiK2cSD6zDB8N3cpsEBAvRxp9zOGg6G/SHHJYAT88/az/IuDGALsNVbGg=="],
+
+    "color-name": ["color-name@1.1.3", "", {}, "sha512-72fSenhMw2HZMTVHeCA9KCmpEIbzWiQsjN+BHcBbS9vr1mtt+vJjPdksIBNUmKAW8TFUDPJK5SUU3QhE9NEXDw=="],
+
+    "color-string": ["color-string@1.9.1", "", { "dependencies": { "color-name": "^1.0.0", "simple-swizzle": "^0.2.2" } }, "sha512-shrVawQFojnZv6xM40anx4CkoDP+fZsw/ZerEMsW/pyzsRbElpsL/DBVW7q3ExxwusdNXI3lXpuhEZkzs8p5Eg=="],
+
+    "colorspace": ["colorspace@1.1.4", "", { "dependencies": { "color": "^3.1.3", "text-hex": "1.0.x" } }, "sha512-BgvKJiuVu1igBUF2kEjRCZXol6wiiGbY5ipL/oVPwm0BL9sIpMIzM8IK7vwuxIIzOXMV3Ey5w+vxhm0rR/TN8w=="],
+
+    "debug": ["debug@4.4.0", "", { "dependencies": { "ms": "^2.1.3" } }, "sha512-6WTZ/IxCY/T6BALoZHaE4ctp9xm+Z5kY/pzYaCHRFeyVhojxlrm+46y68HA6hr0TcwEssoxNiDEUJQjfPZ/RYA=="],
+
+    "denque": ["denque@2.1.0", "", {}, "sha512-HVQE3AAb/pxF8fQAoiqpvg9i3evqug3hoiwakOyZAwJm+6vZehbkYXZ0l4JxS+I3QxM97v5aaRNhj8v5oBhekw=="],
+
+    "enabled": ["enabled@2.0.0", "", {}, "sha512-AKrN98kuwOzMIdAizXGI86UFBoo26CL21UM763y1h/GMSJ4/OHU9k2YlsmBpyScFo/wbLzWQJBMCW4+IO3/+OQ=="],
+
+    "fecha": ["fecha@4.2.3", "", {}, "sha512-OP2IUU6HeYKJi3i0z4A19kHMQoLVs4Hc+DPqqxI2h/DPZHTm/vjsfC6P0b4jCMy14XizLBqvndQ+UilD7707Jw=="],
+
+    "fn.name": ["fn.name@1.1.0", "", {}, "sha512-GRnmB5gPyJpAhTQdSZTSp9uaPSvl09KoYcMQtsB9rQoOmzs9dH6ffeccH+Z+cv6P68Hu5bC6JjRh4Ah/mHSNRw=="],
+
+    "inherits": ["inherits@2.0.4", "", {}, "sha512-k/vGaX4/Yla3WzyMCvTQOXYeIHvqOKtnqBduzTHpzpQZzAskKMhZ2K+EnBiSM9zGSoIFeMpXKxa4dYeZIQqewQ=="],
+
+    "ioredis": ["ioredis@5.6.1", "", { "dependencies": { "@ioredis/commands": "^1.1.1", "cluster-key-slot": "^1.1.0", "debug": "^4.3.4", "denque": "^2.1.0", "lodash.defaults": "^4.2.0", "lodash.isarguments": "^3.1.0", "redis-errors": "^1.2.0", "redis-parser": "^3.0.0", "standard-as-callback": "^2.1.0" } }, "sha512-UxC0Yv1Y4WRJiGQxQkP0hfdL0/5/6YvdfOOClRgJ0qppSarkhneSa6UvkMkms0AkdGimSH3Ikqm+6mkMmX7vGA=="],
+
+    "is-arrayish": ["is-arrayish@0.3.2", "", {}, "sha512-eVRqCvVlZbuw3GrM63ovNSNAeA1K16kaR/LRY/92w0zxQ5/1YzwblUX652i4Xs9RwAGjW9d9y6X88t8OaAJfWQ=="],
+
+    "is-stream": ["is-stream@2.0.1", "", {}, "sha512-hFoiJiTl63nn+kstHGBtewWSKnQLpyb155KHheA1l39uvtO9nWIop1p3udqPcUd/xbF1VLMO4n7OI6p7RbngDg=="],
+
+    "kuler": ["kuler@2.0.0", "", {}, "sha512-Xq9nH7KlWZmXAtodXDDRE7vs6DU1gTU8zYDHDiWLSip45Egwq3plLHzPn27NgvzL2r1LMPC1vdqh98sQxtqj4A=="],
+
+    "lodash.defaults": ["lodash.defaults@4.2.0", "", {}, "sha512-qjxPLHd3r5DnsdGacqOMU6pb/avJzdh9tFX2ymgoZE27BmjXrNy/y4LoaiTeAb+O3gL8AfpJGtqfX/ae2leYYQ=="],
+
+    "lodash.isarguments": ["lodash.isarguments@3.1.0", "", {}, "sha512-chi4NHZlZqZD18a0imDHnZPrDeBbTtVN7GXMwuGdRH9qotxAjYs3aVLKc7zNOG9eddR5Ksd8rvFEBc9SsggPpg=="],
+
+    "logform": ["logform@2.7.0", "", { "dependencies": { "@colors/colors": "1.6.0", "@types/triple-beam": "^1.3.2", "fecha": "^4.2.0", "ms": "^2.1.1", "safe-stable-stringify": "^2.3.1", "triple-beam": "^1.3.0" } }, "sha512-TFYA4jnP7PVbmlBIfhlSe+WKxs9dklXMTEGcBCIvLhE/Tn3H6Gk1norupVW7m5Cnd4bLcr08AytbyV/xj7f/kQ=="],
+
+    "ms": ["ms@2.1.3", "", {}, "sha512-6FlzubTLZG3J2a/NVCAleEhjzq5oxgHyaCU9yYXvcLsvoVaHJq/s5xXI6/XXP6tz7R9xAOtHnSO/tXtF3WRTlA=="],
+
+    "one-time": ["one-time@1.0.0", "", { "dependencies": { "fn.name": "1.x.x" } }, "sha512-5DXOiRKwuSEcQ/l0kGCF6Q3jcADFv5tSmRaJck/OqkVFcOzutB134KRSfF0xDrL39MNnqxbHBbUUcjZIhTgb2g=="],
+
+    "postgres": ["postgres@3.4.5", "", {}, "sha512-cDWgoah1Gez9rN3H4165peY9qfpEo+SA61oQv65O3cRUE1pOEoJWwddwcqKE8XZYjbblOJlYDlLV4h67HrEVDg=="],
+
+    "readable-stream": ["readable-stream@3.6.2", "", { "dependencies": { "inherits": "^2.0.3", "string_decoder": "^1.1.1", "util-deprecate": "^1.0.1" } }, "sha512-9u/sniCrY3D5WdsERHzHE4G2YCXqoG5FTHUiCC4SIbr6XcLZBY05ya9EKjYek9O5xOAwjGq+1JdGBAS7Q9ScoA=="],
+
+    "redis-errors": ["redis-errors@1.2.0", "", {}, "sha512-1qny3OExCf0UvUV/5wpYKf2YwPcOqXzkwKKSmKHiE6ZMQs5heeE/c8eXK+PNllPvmjgAbfnsbpkGZWy8cBpn9w=="],
+
+    "redis-parser": ["redis-parser@3.0.0", "", { "dependencies": { "redis-errors": "^1.0.0" } }, "sha512-DJnGAeenTdpMEH6uAJRK/uiyEIH9WVsUmoLwzudwGJUwZPp80PDBWPHXSAGNPwNvIXAbe7MSUB1zQFugFml66A=="],
+
+    "safe-buffer": ["safe-buffer@5.2.1", "", {}, "sha512-rp3So07KcdmmKbGvgaNxQSJr7bGVSVk5S9Eq1F+ppbRo70+YeaDxkw5Dd8NPN+GD6bjnYm2VuPuCXmpuYvmCXQ=="],
+
+    "safe-stable-stringify": ["safe-stable-stringify@2.5.0", "", {}, "sha512-b3rppTKm9T+PsVCBEOUR46GWI7fdOs00VKZ1+9c1EWDaDMvjQc6tUwuFyIprgGgTcWoVHSKrU8H31ZHA2e0RHA=="],
+
+    "simple-swizzle": ["simple-swizzle@0.2.2", "", { "dependencies": { "is-arrayish": "^0.3.1" } }, "sha512-JA//kQgZtbuY83m+xT+tXJkmJncGMTFT+C+g2h2R9uxkYIrE2yy9sgmcLhCnw57/WSD+Eh3J97FPEDFnbXnDUg=="],
+
+    "stack-trace": ["stack-trace@0.0.10", "", {}, "sha512-KGzahc7puUKkzyMt+IqAep+TVNbKP+k2Lmwhub39m1AsTSkaDutx56aDCo+HLDzf/D26BIHTJWNiTG1KAJiQCg=="],
+
+    "standard-as-callback": ["standard-as-callback@2.1.0", "", {}, "sha512-qoRRSyROncaz1z0mvYqIE4lCd9p2R90i6GxW3uZv5ucSu8tU7B5HXUP1gG8pVZsYNVaXjk8ClXHPttLyxAL48A=="],
+
+    "string_decoder": ["string_decoder@1.3.0", "", { "dependencies": { "safe-buffer": "~5.2.0" } }, "sha512-hkRX8U1WjJFd8LsDJ2yQ/wWWxaopEsABU1XfkM8A+j0+85JAGppt16cr1Whg6KIbb4okU6Mql6BOj+uup/wKeA=="],
+
+    "text-hex": ["text-hex@1.0.0", "", {}, "sha512-uuVGNWzgJ4yhRaNSiubPY7OjISw4sw4E5Uv0wbjp+OzcbmVU/rsT8ujgcXJhn9ypzsgr5vlzpPqP+MBBKcGvbg=="],
+
+    "triple-beam": ["triple-beam@1.4.1", "", {}, "sha512-aZbgViZrg1QNcG+LULa7nhZpJTZSLm/mXnHXnbAbjmN5aSa0y7V+wvv6+4WaBtpISJzThKy+PIPxc1Nq1EJ9mg=="],
+
+    "util-deprecate": ["util-deprecate@1.0.2", "", {}, "sha512-EPD5q1uXyFxJpCrLnCc1nHnq3gOa6DZBocAIiI2TaSCA7VCJ1UJDMagCzIkXNsUYfD1daK//LTEQ8xiIbrHtcw=="],
+
+    "winston": ["winston@3.17.0", "", { "dependencies": { "@colors/colors": "^1.6.0", "@dabh/diagnostics": "^2.0.2", "async": "^3.2.3", "is-stream": "^2.0.0", "logform": "^2.7.0", "one-time": "^1.0.0", "readable-stream": "^3.4.0", "safe-stable-stringify": "^2.3.1", "stack-trace": "0.0.x", "triple-beam": "^1.3.0", "winston-transport": "^4.9.0" } }, "sha512-DLiFIXYC5fMPxaRg832S6F5mJYvePtmO5G9v9IgUFPhXm9/GkXarH/TUrBAVzhTCzAj9anE/+GjrgXp/54nOgw=="],
+
+    "winston-transport": ["winston-transport@4.9.0", "", { "dependencies": { "logform": "^2.7.0", "readable-stream": "^3.6.2", "triple-beam": "^1.3.0" } }, "sha512-8drMJ4rkgaPo1Me4zD/3WLfI/zPdA9o2IipKODunnGDcuqbHwjsbB79ylv04LCGGzU0xQ6vTznOMpQGaLhhm6A=="],
+  }
+}

packages/core/const/time.ts

@@ -0,0 +1,5 @@
+export const SECOND = 1000;
+export const MINUTE = 60 * SECOND;
+export const HOUR = 60 * MINUTE;
+export const DAY = 24 * HOUR;
+export const WEEK = 7 * DAY;

packages/core/db/dbNew.ts

@@ -0,0 +1,8 @@
+import postgres from "postgres";
+import { postgresConfigCred, postgresConfig } from "./pgConfigNew";
+
+export const sql = postgres(postgresConfig);
+
+export const sqlCred = postgres(postgresConfigCred);
+
+export const sqlTest = postgres(postgresConfig);

packages/core/db/pgConfig.ts

@@ -1,30 +0,0 @@
-const requiredEnvVars = ["DB_HOST", "DB_NAME", "DB_USER", "DB_PASSWORD", "DB_PORT"];
-
-const unsetVars = requiredEnvVars.filter((key) => Deno.env.get(key) === undefined);
-
-if (unsetVars.length > 0) {
-	throw new Error(`Missing required environment variables: ${unsetVars.join(", ")}`);
-}
-
-const databaseHost = Deno.env.get("DB_HOST")!;
-const databaseName = Deno.env.get("DB_NAME")!;
-const databaseNameCred = Deno.env.get("DB_NAME_CRED")!;
-const databaseUser = Deno.env.get("DB_USER")!;
-const databasePassword = Deno.env.get("DB_PASSWORD")!;
-const databasePort = Deno.env.get("DB_PORT")!;
-
-export const postgresConfig = {
-	hostname: databaseHost,
-	port: parseInt(databasePort),
-	database: databaseName,
-	user: databaseUser,
-	password: databasePassword,
-};
-
-export const postgresConfigCred = {
-	hostname: databaseHost,
-	port: parseInt(databasePort),
-	database: databaseNameCred,
-	user: databaseUser,
-	password: databasePassword,
-};

packages/core/db/pgConfigNew.ts

@@ -0,0 +1,34 @@
+const requiredEnvVars = ["DB_HOST", "DB_NAME", "DB_USER", "DB_PASSWORD", "DB_PORT", "DB_NAME_CRED"];
+
+const getEnvVar = (key: string) => {
+	return process.env[key] || import.meta.env[key];
+};
+
+const unsetVars = requiredEnvVars.filter((key) => getEnvVar(key) === undefined);
+
+if (unsetVars.length > 0) {
+	throw new Error(`Missing required environment variables: ${unsetVars.join(", ")}`);
+}
+
+const databaseHost = getEnvVar("DB_HOST")!;
+const databaseName = getEnvVar("DB_NAME");
+const databaseNameCred = getEnvVar("DB_NAME_CRED")!;
+const databaseUser = getEnvVar("DB_USER")!;
+const databasePassword = getEnvVar("DB_PASSWORD")!;
+const databasePort = getEnvVar("DB_PORT")!;
+
+export const postgresConfig = {
+	host: databaseHost,
+	port: parseInt(databasePort),
+	database: databaseName,
+	username: databaseUser,
+	password: databasePassword
+};
+
+export const postgresConfigCred = {
+	hostname: databaseHost,
+	port: parseInt(databasePort),
+	database: databaseNameCred,
+	user: databaseUser,
+	password: databasePassword
+};

packages/core/db/psql.d.ts

@@ -0,0 +1,3 @@
+import type postgres from "postgres";
+
+export type Psql = postgres.Sql;

packages/core/db/redis.ts

@@ -0,0 +1,10 @@
+import { Redis } from "ioredis";
+
+const host = process.env.REDIS_HOST || "localhost";
+const port = parseInt(process.env.REDIS_PORT) || 6379;
+
+export const redis = new Redis({
+    port: port,
+    host: host,
+    maxRetriesPerRequest: null,
+});

packages/core/db/schema.d.ts

@@ -1,16 +1,3 @@
-export interface AllDataType {
-	id: number;
-	aid: bigint;
-	bvid: string | null;
-	description: string | null;
-	uid: number | null;
-	tags: string | null;
-	title: string | null;
-	published_at: string | null;
-	duration: number;
-	created_at: string | null;
-}
-
 export interface BiliUserType {
 	id: number;
 	uid: number;
@@ -21,19 +8,19 @@ export interface BiliUserType {
 
 export interface VideoSnapshotType {
 	id: number;
-	created_at: string;
+	created_at: Date;
 	views: number;
 	coins: number;
 	likes: number;
 	favorites: number;
 	shares: number;
 	danmakus: number;
-	aid: bigint;
+	aid: number;
 	replies: number;
 }
 
 export interface LatestSnapshotType {
-	aid: bigint;
+	aid: number;
 	time: number;
 	views: number;
 	danmakus: number;
@@ -46,10 +33,35 @@ export interface LatestSnapshotType {
 
 export interface SnapshotScheduleType {
 	id: number;
-	aid: bigint;
+	aid: number;
 	type?: string;
-	created_at: string;
-	started_at?: string;
-	finished_at?: string;
+	created_at: Date;
+	started_at?: Date;
+	finished_at?: Date;
 	status: string;
 }
+
+export interface UserType {
+	id: number;
+	username: string;
+	nickname: string | null;
+	password: string;
+	unq_id: string;
+	role: string;
+	created_at: Date;
+}
+
+export interface BiliVideoMetadataType {
+	id: number;
+	aid: number;
+	bvid: string | null;
+	description: string | null;
+	uid: number | null;
+	tags: string | null;
+	title: string | null;
+	published_at: Date | null;
+	duration: number | null;
+	created_at: Date;
+	status: number;
+	cover_url: string | null;
+}

packages/core/db/videoSnapshot.ts

@@ -1,33 +0,0 @@
-import { Client } from "https://deno.land/x/postgres@v0.19.3/mod.ts";
-import { VideoSnapshotType } from "@core/db/schema.d.ts";
-
-export async function getVideoSnapshots(client: Client, aid: number, limit: number, pageOrOffset: number, reverse: boolean, mode: 'page' | 'offset' = 'page') {
-    const offset = mode === 'page' ? (pageOrOffset - 1) * limit : pageOrOffset;
-    const order = reverse ? 'ASC' : 'DESC';
-    const query = `
-        SELECT *
-        FROM video_snapshot
-        WHERE aid = $1
-        ORDER BY created_at ${order}
-        LIMIT $2
-        OFFSET $3
-    `;
-    const queryResult = await client.queryObject<VideoSnapshotType>(query, [aid, limit, offset]);
-    return queryResult.rows;
-}
-
-export async function getVideoSnapshotsByBV(client: Client, bv: string, limit: number, pageOrOffset: number, reverse: boolean, mode: 'page' | 'offset' = 'page') {
-    const offset = mode === 'page' ? (pageOrOffset - 1) * limit : pageOrOffset;
-    const order = reverse ? 'ASC' : 'DESC';
-    const query = `
-        SELECT vs.*
-        FROM video_snapshot vs
-        JOIN bilibili_metadata bm ON vs.aid = bm.aid
-        WHERE bm.bvid = $1
-        ORDER BY vs.created_at ${order}
-        LIMIT $2
-        OFFSET $3
-    `
-    const queryResult = await client.queryObject<VideoSnapshotType>(query, [bv, limit, offset]);
-    return queryResult.rows;
-}

packages/core/index.ts

@@ -0,0 +1 @@
+export * from "./db/dbNew";

packages/core/lib/randomID.ts

@@ -0,0 +1,15 @@
+export function generateRandomId(length: number): string {
+	const characters = 'abcdefghijkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789';
+	const charactersLength = characters.length;
+	const randomBytes = new Uint8Array(length);
+
+	crypto.getRandomValues(randomBytes);
+
+	let result = '';
+	for (let i = 0; i < length; i++) {
+		const randomIndex = randomBytes[i] % charactersLength;
+		result += characters.charAt(randomIndex);
+	}
+
+	return result;
+}

packages/core/log/logger.ts

@@ -1,5 +1,5 @@
-import winston, { format, transports } from "npm:winston";
-import { TransformableInfo } from "npm:logform";
+import winston, { format, transports } from "winston";
+import type { TransformableInfo } from "logform";
 import chalk from "chalk";
 
 const customFormat = format.printf((info: TransformableInfo) => {
@@ -24,13 +24,13 @@ const createTransport = (level: string, filename: string) => {
 	let maxsize = undefined;
 	let maxFiles = undefined;
 	let tailable = undefined;
-	if (level === "verbose") {
-		maxsize = 10 * MB;
-		maxFiles = 10;
+	if (level === "silly") {
+		maxsize = 500 * MB;
+		maxFiles = undefined;
 		tailable = false;
 	} else if (level === "warn") {
 		maxsize = 10 * MB;
-		maxFiles = 1;
+		maxFiles = 5;
 		tailable = false;
 	}
 	function replacer(key: unknown, value: unknown) {
@@ -52,9 +52,9 @@ const createTransport = (level: string, filename: string) => {
 	});
 };
 
-const sillyLogPath = Deno.env.get("LOG_VERBOSE") ?? "logs/verbose.log";
-const warnLogPath = Deno.env.get("LOG_WARN") ?? "logs/warn.log";
-const errorLogPath = Deno.env.get("LOG_ERROR") ?? "logs/error.log";
+const sillyLogPath = process.env["LOG_VERBOSE"] ?? "logs/verbose.log";
+const warnLogPath = process.env["LOG_WARN"] ?? "logs/warn.log";
+const errorLogPath = process.env["LOG_ERROR"] ?? "logs/error.log";
 
 const winstonLogger = winston.createLogger({
 	levels: winston.config.npm.levels,
@@ -62,7 +62,7 @@ const winstonLogger = winston.createLogger({
 		new transports.Console({
 			level: "debug",
 			format: format.combine(
-				format.timestamp({ format: "HH:mm:ss.SSS" }),
+				format.timestamp({ format: "YYYY-MM-DD HH:mm:ss.SSS" }),
 				format.colorize(),
 				format.errors({ stack: true }),
 				customFormat,

packages/core/log/test.ts

@@ -1,4 +1,4 @@
-import logger from "log/logger.ts";
+import logger from "@core/log/logger.ts";
 
 logger.error(Error("test error"), "test service");
 logger.debug(`some string`);