update: dependency version

fix: missing field in db schema
fix: several bugs about type
2025-06-10 15:08:08 +08:00 · 2025-06-10 15:03:39 +08:00 · 2025-06-10 14:46:28 +08:00 · 2025-06-10 14:38:55 +08:00 · 2025-06-08 18:24:15 +08:00 · 2025-06-08 18:09:49 +08:00
411 changed files with 23651 additions and 3189 deletions
--- a/.dockerignore
+++ b/.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
--- a/.gitattributes
+++ b/.gitattributes
@ -0,0 +1 @@
+*.woff2 filter=lfs diff=lfs merge=lfs -text
--- a/.gitignore
+++ b/.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
--- a/.idea/.gitignore
+++ b/.idea/.gitignore
@ -6,4 +6,5 @@
 # Datasource local storage ignored files
 /dataSources/
 /dataSources.local.xml
-dataSources.xml
+dataSources.xml
+MarsCodeWorkspaceAppSettings.xml
--- a/.idea/bun.xml
+++ b/.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>
--- a/.idea/codeStyles/Project.xml
+++ b/.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>
--- a/.idea/codeStyles/codeStyleConfig.xml
+++ b/.idea/codeStyles/codeStyleConfig.xml
@ -0,0 +1,5 @@
+<component name="ProjectCodeStyleConfiguration">
+  <state>
+    <option name="USE_PER_PROJECT_SETTINGS" value="true" />
+  </state>
+</component>
--- a/.idea/cvsa.iml
+++ b/.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" />
--- a/.idea/deno.xml
+++ b/.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>
--- a/.idea/inspectionProfiles/Project_Default.xml
+++ b/.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>
--- a/.idea/scopes/Astro.xml
+++ b/.idea/scopes/Astro.xml
@ -0,0 +1,3 @@
+<component name="DependencyValidationManager">
+  <scope name="Astro" pattern="file:*.astro" />
+</component>
--- a/.prettierrc
+++ b/.prettierrc
@ -0,0 +1,8 @@
+{
+	"useTabs": true,
+	"tabWidth": 4,
+	"trailingComma": "none",
+	"singleQuote": false,
+	"printWidth": 120,
+	"endOfLine": "lf"
+}
--- a/.tokeignore
+++ b/.tokeignore
@ -3,4 +3,9 @@ data
 *.svg
 *.txt
 *.md
-*config*
+*config*
+Inter.css
+MiSans.css
+*.yaml
+*.yml
+*.mdx
--- a/.vscode/extensions.json
+++ b/.vscode/extensions.json
@ -1,6 +0,0 @@
-{
-	"recommendations": [
-		"denoland.vscode-deno",
-		"bradlc.vscode-tailwindcss"
-	]
-}
--- a/.zed/settings.json
+++ b/.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"
-		}
-	}
-}
--- a/Dockerfile.backend
+++ b/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"]
--- a/Dockerfile.crawler
+++ b/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"]
--- a/Dockerfile.frontend
+++ b/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"]
--- a/Dockerfile.next
+++ b/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"]
--- a/bun.lock
+++ b/bun.lock
--- a/deno.json
+++ b/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"
-	}
-}
--- a/doc/en/SUMMARY.md
+++ b/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)
--- a/doc/en/about/scope-of-inclusion.md
+++ b/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").&#x20;
+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").&#x20;

-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

--- a/doc/en/architecure/artificial-intelligence.md
+++ b/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.
--- a/doc/en/architecure/crawler.md
+++ b/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.
+
--- a/doc/en/architecure/database-structure/README.md
+++ b/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.
--- a/doc/en/architecure/message-queue.md
+++ b/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`.
--- a/doc/en/architecure/overview.md
+++ b/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/`.&#x20;

-* 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.
+
--- a/doc/zh/.gitbook/assets/1.yaml
+++ b/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: 详细的错误信息
--- a/doc/zh/SUMMARY.md
+++ b/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)
--- a/doc/zh/api-doc/catalog.md
+++ b/doc/zh/api-doc/catalog.md
@ -1,3 +1,4 @@
 # 目录

- [歌曲](songs.md)
+* [视频快照](video-snapshot.md)
+
--- a/doc/zh/api-doc/songs.md
+++ b/doc/zh/api-doc/songs.md
@ -1,3 +0,0 @@
-# 歌曲
-
-暂未实现。
--- a/doc/zh/api-doc/video-snapshot.md
+++ b/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 %}
+
--- a/doc/zh/architecture/database-structure/README.md
+++ b/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：视频快照的规划信息，为辅助表
--- a/doc/zh/architecture/message-queue/latestvideosqueue-dui-lie.md
+++ b/doc/zh/architecture/message-queue/latestvideosqueue-dui-lie.md
@ -1,2 +1 @@
 # LatestVideosQueue 队列
-
--- a/doc/zh/architecture/overview.md
+++ b/doc/zh/architecture/overview.md
@ -20,8 +20,7 @@ layout:

 位于项目目录`packages/crawler` 下，它负责以下工作：

-* 抓取新的视频并收录作品
-* 持续监控视频的播放量等统计信息
+- 抓取新的视频并收录作品
+- 持续监控视频的播放量等统计信息

 整个 crawler 由 BullMQ 消息队列驱动，使用 Redis 和 PostgreSQL 管理状态。
-
--- a/docker-compose.example.yml
+++ b/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:
--- a/package.json
+++ b/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"
+	}
+}
--- a/packages/backend/.prettierrc
+++ b/packages/backend/.prettierrc
@ -0,0 +1,8 @@
+{
+	"useTabs": true,
+	"tabWidth": 4,
+	"trailingComma": "none",
+	"singleQuote": false,
+	"printWidth": 120,
+	"endOfLine": "lf"
+}
--- a/packages/backend/bun.lock
+++ b/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=="],
+  }
+}
--- a/packages/backend/database.ts
+++ b/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;
-	}
-}
--- a/packages/backend/db/latestSnapshots.ts
+++ b/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
+	`;
+}
--- a/packages/backend/db/snapshots.ts
+++ b/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}
+		`;
+	}
+}
--- a/packages/backend/deno.json
+++ b/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"
-}
--- a/packages/backend/lib/auth/captchaDifficulty.ts
+++ b/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;
+}
--- a/packages/backend/lib/auth/getJWTsecret.ts
+++ b/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];
+};
--- a/packages/backend/lib/const/singers.ts
+++ b/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);
+};
--- a/packages/backend/main.ts
+++ b/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";
--- a/packages/backend/middleware/bodyLimits.ts
+++ b/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);
+	}
+});
--- a/packages/backend/middleware/captcha.ts
+++ b/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();
+};
--- a/packages/backend/middleware/contentType.ts
+++ b/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");
+};
--- a/packages/backend/middleware/cors.ts
+++ b/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);
+};
--- a/packages/backend/middleware/logger.ts
+++ b/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;
+}
--- a/packages/backend/middleware/preetifyResponse.ts
+++ b/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" } });
+	}
+};
--- a/packages/backend/middleware/rateLimiters.ts
+++ b/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();
+};
--- a/packages/backend/package.json
+++ b/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"
+}
--- a/packages/backend/register.ts
+++ b/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);
-		}
-	}
-});
--- a/packages/backend/root.ts
+++ b/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
-	})
-})
--- a/packages/backend/routes/404.ts
+++ b/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
+	);
+};
--- a/packages/backend/routes/captcha/[id]/result/GET.ts
+++ b/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
+		});
+	}
+);
--- a/packages/backend/routes/captcha/difficulty/GET.ts
+++ b/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);
+		}
+	}
+});
--- a/packages/backend/routes/captcha/index.ts
+++ b/packages/backend/routes/captcha/index.ts
@ -0,0 +1,2 @@
+export * from "./session/POST.ts";
+export * from "./[id]/result/GET.ts";
--- a/packages/backend/routes/captcha/session/POST.ts
+++ b/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);
+		}
+	}
+});
--- a/packages/backend/routes/index.ts
+++ b/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
+	});
+});
--- a/packages/backend/routes/login/session/POST.ts
+++ b/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);
+			}
+		}
+	}
+);
--- a/packages/backend/routes/ping/index.ts
+++ b/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
+		}
+	});
+});
--- a/packages/backend/routes/session/[id]/DELETE.ts
+++ b/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);
+		}
+	}
+});
--- a/packages/backend/routes/session/index.ts
+++ b/packages/backend/routes/session/index.ts
@ -0,0 +1 @@
+export * from "./[id]/DELETE";
--- a/packages/backend/routes/user/POST.ts
+++ b/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);
+		}
+	}
+});
--- a/packages/backend/routes/user/index.ts
+++ b/packages/backend/routes/user/index.ts
@ -0,0 +1,2 @@
+export * from "./POST.ts";
+export * from "./session/[id]/GET.ts";
--- a/packages/backend/routes/user/session/[id]/GET.ts
+++ b/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
+		});
+	}
+);
--- a/packages/backend/routes/video/[id]/info.ts
+++ b/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);
+		}
+	}
+});
--- a/packages/backend/routes/video/[id]/snapshots.ts
+++ b/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);
 		}
 	}
 });
--- a/packages/backend/routes/video/index.ts
+++ b/packages/backend/routes/video/index.ts
@ -0,0 +1,2 @@
+export * from "./[id]/info";
+export * from "./[id]/snapshots";
--- a/packages/backend/routes/videos/GET.ts
+++ b/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);
+		}
+	}
+});
--- a/packages/backend/routes/videos/index.ts
+++ b/packages/backend/routes/videos/index.ts
@ -0,0 +1 @@
+export * from "./GET.ts";
--- a/packages/backend/singers.ts
+++ b/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);
-};
--- a/packages/backend/src/main.ts
+++ b/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";
--- a/packages/backend/src/middleware.ts
+++ b/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);
+}
--- a/packages/backend/src/routing.ts
+++ b/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);
+}
--- a/packages/backend/src/schema.d.ts
+++ b/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;
--- a/packages/backend/src/startServer.ts
+++ b/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);
+	}
+}
--- a/packages/backend/src/types.d.ts
+++ b/packages/backend/src/types.d.ts
@ -0,0 +1 @@
+export * from "./schema";
--- a/packages/backend/src/utils.ts
+++ b/packages/backend/src/utils.ts
@ -0,0 +1,5 @@
+import { createFactory } from "hono/factory";
+
+const factory = createFactory();
+
+export const createHandlers = factory.createHandlers;
--- a/packages/backend/tsconfig.json
+++ b/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
+	}
+}
--- a/packages/backend/utils.ts
+++ b/packages/backend/utils.ts
@ -1,5 +0,0 @@
-import { createFactory } from 'hono/factory'
-
-const factory = createFactory();
-
-export const createHandlers = factory.createHandlers;
--- a/packages/core/bun.lock
+++ b/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=="],
+  }
+}
--- a/packages/core/const/time.ts
+++ b/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;
--- a/packages/core/db/dbNew.ts
+++ b/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);
--- a/packages/core/db/pgConfig.ts
+++ b/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,
-};
--- a/packages/core/db/pgConfigNew.ts
+++ b/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
+};
--- a/packages/core/db/psql.d.ts
+++ b/packages/core/db/psql.d.ts
@ -0,0 +1,3 @@
+import type postgres from "postgres";
+
+export type Psql = postgres.Sql;
--- a/packages/core/db/redis.ts
+++ b/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,
+});
--- a/packages/core/db/schema.d.ts
+++ b/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;
+}
--- a/packages/core/db/videoSnapshot.ts
+++ b/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;
-}
--- a/packages/core/deno.json
+++ b/packages/core/deno.json
--- a/packages/core/index.ts
+++ b/packages/core/index.ts
@ -0,0 +1 @@
+export * from "./db/dbNew";
--- a/packages/core/lib/randomID.ts
+++ b/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;
+}
--- a/packages/crawler/log/logger.ts
+++ b/packages/crawler/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,
--- a/packages/crawler/log/test.ts
+++ b/packages/crawler/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`);
--- a/Show More
+++ b/Show More
Author	SHA1	Message	Date
alikia2x	92c3c8eefe	update: dependency version	2025-06-10 15:08:08 +08:00
alikia2x	497ea031d8	fix: missing field in db schema	2025-06-10 15:03:39 +08:00
alikia2x	39ca394a56	fix: several bugs about type	2025-06-10 14:46:28 +08:00
alikia2x	0bd1771f35	improve: date formatting in frontend	2025-06-10 14:38:55 +08:00
alikia2x	328c73c209	fix: 'close' is missing in DialogButtonGroup in ErrorDialog	2025-06-08 18:24:15 +08:00
alikia2x	5ac952ec13	merge: branch 'feat/frontend' into main	2025-06-08 18:09:49 +08:00
alikia2x	2cf5923b28	add: logout in frontend	2025-06-08 18:06:46 +08:00
alikia2x	75973c72ee	add: login, user profile page & license	2025-06-07 19:42:36 +08:00
alikia2x	b40d24721c	add: milestone monitoring for videos close to N million views	2025-06-06 21:37:55 +08:00
alikia2x	0a6ecc6314	add: route for getting videos	2025-06-06 21:03:52 +08:00
alikia2x	b4a0320e3e	version: cvsa/3.15.34	2025-06-06 17:40:32 +08:00
alikia2x	8cf9395354	fix: don't adjust start time for schedule when no proxy is available	2025-06-06 17:11:44 +08:00
alikia2x	1e8d28e194	fix: incorrectly ignored type when collecting videos for archive snapshots	2025-06-06 16:52:27 +08:00
alikia2x (寒寒)	c0340677a1	merge: pull request #8 from ref/next	2025-06-05 22:31:06 +08:00
alikia2x (寒寒)	54a2de0a11	Merge branch 'main' into ref/next	2025-06-05 22:30:19 +08:00
alikia2x	3abd6666c0	add: missing dependency in backend	2025-06-02 05:33:50 +08:00
alikia2x	44e13724fc	fix: missing dependency in backend	2025-06-02 05:17:46 +08:00
alikia2x	dd7e2242a0	add: docker support for nextjs frontend	2025-06-02 05:04:26 +08:00
alikia2x	503a93a09f	update: .tokeignore	2025-06-02 04:06:56 +08:00
alikia2x	507f2c331e	add: several pages, synced with old frontend	2025-06-02 04:04:17 +08:00
alikia2x	a1a4abff46	fix: missing import in @cvsa/core	2025-06-01 21:31:37 +08:00
alikia2x	c6b7736dac	fix: import from the core package in next frontend	2025-06-01 18:48:50 +08:00
alikia2x	fa5ab258da	add: login status detection in frontend	2025-06-01 17:21:09 +08:00
alikia2x	9dd06fa7bc	add: set cookie after signing up	2025-06-01 16:18:01 +08:00
alikia2x	bb7f846305	improve: the structure of the error handling in sign up page	2025-06-01 14:36:55 +08:00
alikia2x	7f9563a2a6	ref: use axios for API requests add: a useCaptcha hook	2025-06-01 01:53:06 +08:00
alikia2x	d0d9c21aba	update: as-nned URL prefix for i18n routing	2025-05-31 21:56:15 +08:00
alikia2x	44bc99dd9d	add: i18n	2025-05-31 21:29:47 +08:00
alikia2x	2c83b79881	update: termination condition to time-based in classifyVideosWorker	2025-05-31 12:23:01 +08:00
alikia2x	1a20d5afe0	update: schedule archive snapshots to next Saturday midnight fix: no expire when acquiring lock for classifyVideos ref: format	2025-05-31 12:13:56 +08:00
alikia2x	ae338f88ee	update: the error dialog	2025-05-31 11:47:45 +08:00
alikia2x	96903dec2b	fix: prevent scrolling of body when model dialog is shown	2025-05-30 03:24:37 +08:00
alikia2x	58b4e2613c	add: button components in dialog	2025-05-28 23:01:43 +08:00
alikia2x	2b0497c83a	fix: incorrect timezone when adding to bilibili metadata	2025-05-27 22:40:53 +08:00
alikia2x	3bc72720d1	fix: remove bull-board from all scripts to prevent crashing	2025-05-27 22:35:44 +08:00
alikia2x	557a013b42	add: some components, fonts	2025-05-27 22:33:28 +08:00
alikia2x	16cfae8bad	fix: unexpectedly set prop variant of TextField to required	2025-05-25 03:15:51 +08:00
alikia2x	cbd46d4030	improve: code style and some subtle changes	2025-05-25 03:15:10 +08:00
alikia2x	6b93a781b7	add: sign up page	2025-05-24 17:13:26 +08:00
alikia2x	fe2fd4fe36	feat: home page with header	2025-05-24 03:08:32 +08:00
alikia2x	dd70543594	init: next for frontend	2025-05-22 03:15:07 +08:00
alikia2x	1ff71ab241	improve: code quality	2025-05-19 00:13:01 +08:00
alikia2x	cf7a285f57	fix: incorrect import for psql.d.ts, remove unnecessary benchmark files	2025-05-19 00:11:53 +08:00
alikia2x	79a37d927a	fix: forget to specify type when collecting videos without active schedules; missing return when eta too long for milestone snapshot	2025-05-19 00:10:33 +08:00
alikia2x	f003e77d52	add: text fields and button in signup page	2025-05-18 20:55:51 +08:00
alikia2x	4addadb035	update: the style in content.css	2025-05-18 15:34:12 +08:00
alikia2x	23917b2976	merge: branch 'feat/backend' into main	2025-05-18 03:53:22 +08:00
alikia2x	6d946f74df	update: rate limiter	2025-05-18 03:52:42 +08:00
alikia2x	c5ba673069	fix: several bugs in captcha rate limiting	2025-05-18 03:28:27 +08:00
alikia2x	fa5ccce83f	fix: incorrect import of type `Psql`	2025-05-18 00:36:39 +08:00
alikia2x	7786d66dbb	add: complete guarding under uCaptcha	2025-05-18 00:33:43 +08:00
alikia2x	b18b45078f	add: tokenID added to JWT in endpoint GET /captcha/:id/result	2025-05-17 02:15:05 +08:00
alikia2x	1633e56b1e	update: returns JWT in the /captcha/:id/result endpoint	2025-05-11 19:40:24 +08:00
alikia2x	a063f2401b	update: rename the captcha endpoint	2025-05-11 19:14:19 +08:00
alikia2x	44f68993a0	fix: missing env during building in frontend	2025-05-11 16:20:22 +08:00
alikia2x	980dd542ee	add: video info page in frontend	2025-05-11 03:57:26 +08:00
alikia2x	c82a95d0bc	fix: missing aliyun CLI installation process in backend Dockerfile	2025-05-11 02:38:46 +08:00
alikia2x	137c19d74e	ref: move from sliding window to token bucket in rate limiter	2025-05-11 01:50:02 +08:00
alikia2x	5fb1355346	ref: rename endpoint to POST /validation/session	2025-05-10 22:41:10 +08:00
alikia2x	8456bb7485	add: route for validation	2025-05-10 21:48:02 +08:00
alikia2x	01f5e57864	ref: dir structure of backend package	2025-05-10 00:20:20 +08:00
alikia2x	bf00918c00	merge: branch 'main' into feat/backend	2025-05-10 00:19:55 +08:00
alikia2x	44f4ee5b01	fix: incorrect path in build.ts in crawler	2025-05-06 03:42:42 +08:00
alikia2x	cd8fe502ba	update: Dockerfile for crawler fix: incorrect condition in logger	2025-05-06 03:24:08 +08:00
alikia2x	95681dcbf3	add: Dockerfile for crawler	2025-05-06 03:10:20 +08:00
alikia2x	59f09ca5eb	ref: switch to bun	2025-05-05 01:53:33 +08:00
alikia2x	d686b6a369	add: example docker compose file	2025-05-04 02:59:04 +08:00
alikia2x	d8c74a609a	merge: branch 'main' into ref/deno	2025-05-03 05:02:21 +08:00
alikia2x	9d5c7cc47d	update: new delegate that fits to new infra	2025-05-02 20:14:05 +08:00
alikia2x	7528dcdf81	debug: add debug logging in alicloudFcRequest	2025-05-02 20:01:21 +08:00
alikia2x	811a8261b3	fix: BullMQ crashes when not setting Redis config maxRetriesPerRequest to null	2025-05-02 19:48:17 +08:00
alikia2x	d0aa27b2ad	ref: backend structure, make backend & frontend containerize fix: some errors in dockerfile	2025-04-29 21:45:38 +08:00
alikia2x	280825cf67	fix: missing dependency in frontend	2025-04-29 06:42:24 +08:00
alikia2x	e658135a81	fix: missing name in packages/core/package.json	2025-04-29 06:23:11 +08:00
alikia2x	4632bd5906	fix: missing dependency that causes failure of resolving import	2025-04-29 06:20:22 +08:00
alikia2x	43b52dee0b	add: complete docker config	2025-04-29 06:12:44 +08:00
alikia2x	8900ac7ec7	update: .gitignore	2025-04-29 04:53:26 +08:00
alikia2x	2772849933	update: .gitignore	2025-04-29 04:52:58 +08:00
alikia2x	784939074a	merge: branch 'ref/deno' into ref/docker	2025-04-29 04:00:57 +08:00
alikia2x	94dd662c40	fix: module not found "net/getVideoInfo.ts"	2025-04-29 04:00:15 +08:00
alikia2x	1ebc0d0c1b	update: .gitignore	2025-04-29 03:52:20 +08:00
alikia2x	728a74f4d3	merge: branch 'feat/backend' into ref/docker	2025-04-29 03:49:27 +08:00
alikia2x	0c8a459d92	update: .gitignore	2025-04-29 03:49:17 +08:00
alikia2x	d7b6792d05	add: basic docker config	2025-04-29 03:45:43 +08:00
alikia2x	50bcb48bd6	fix: incorrect key causing RangeError in date-fns	2025-04-29 00:33:12 +08:00
alikia2x	fe386d2b02	debug: add logs for info.astro	2025-04-29 00:24:05 +08:00
alikia2x	e72b3008d1	ref: switch to bun & postgres.js for backend	2025-04-29 00:07:12 +08:00
alikia2x	a31f702499	fix: missing navigator object in server-side	2025-04-28 06:15:06 +08:00
alikia2x	5a112aeaee	fix: missing navigator object in server-side	2025-04-28 06:05:37 +08:00
alikia2x	d675187f68	add: showing browser version in the test result of VDF benchmark	2025-04-28 06:01:07 +08:00
alikia2x	10de53b773	add: title for test-vdf page	2025-04-28 05:51:07 +08:00
alikia2x	f97e42e7d0	fix: incorrect speedSample	2025-04-28 05:46:24 +08:00
alikia2x	be0ff294be	update: UI of VDF test page	2025-04-28 05:40:02 +08:00
alikia2x	d74ff02a3f	chores: .idea config & gitignore	2025-04-28 02:19:41 +08:00
alikia2x	92e00d033d	improve: UI/UX for info page and search box	2025-04-28 02:15:50 +08:00
alikia2x	6d1698fcb6	update: color system with tailwind theme	2025-04-28 02:02:52 +08:00
alikia2x	7d8361589c	add: link to homepage in login page	2025-04-27 01:07:35 +08:00
alikia2x	66b89eb562	fix: incorrectly hard-coded height for a p tag, resulting in overflow on small screens	2025-04-27 01:06:13 +08:00
alikia2x	d994e67036	fix: incorrect method of importing tailwindcss in content.css	2025-04-27 00:57:32 +08:00
alikia2x	ee40764fdc	merge: branch 'feat/frontend' into main	2025-04-27 00:53:15 +08:00
alikia2x	175ec047cf	update: login & register page	2025-04-27 00:51:25 +08:00
alikia2x	358dd1ee5e	improve: style for search bar & song info page	2025-04-26 23:51:40 +08:00
alikia2x	d9c8253019	add: custom fonts, register page improve: style	2025-04-26 20:50:30 +08:00
alikia2x	1a86831e90	fix: handle ALICLOUD_PROXY_ERR error in snapshotVideoWorker	2025-04-24 03:42:25 +08:00
alikia2x	a67d896d86	update: WebStorm config	2025-04-19 03:20:15 +08:00
alikia2x	244298913a	fix: comparison condition error in scheduleSnapshot	2025-04-18 22:38:02 +08:00
alikia2x	2c47105913	fix: incorrectly delayed milestone schedule due to UPDATE	2025-04-17 02:18:25 +08:00
alikia2x	6eaaf921d6	fix: ignored the case of inserting schedule when type = 'milestone' in scheduleSnapshot	2025-04-15 22:06:14 +08:00
alikia2x	288e4f9571	fix: incorrect SQL in scheduleSnapshot	2025-04-15 22:00:02 +08:00
alikia2x	907c0a6976	fix: update started_at instead of returning for milestone snapshots in scheduleSnapshot	2025-04-15 21:42:02 +08:00
alikia2x	7689e687ff	ref: extract scheduleCleanup into individual file improve: logic of scheduleCleanup	2025-04-15 03:50:03 +08:00
alikia2x	651eef0b9e	fix: incorrectly set snapshot status to 'completed' when bilibili returned other status code	2025-04-15 03:34:45 +08:00
alikia2x	68bd46fd8a	improve: logging text in snapshotVideo.ts	2025-04-14 05:39:27 +08:00
alikia2x	13ea8fec8b	improve: remove unused import in snapshotTick.ts	2025-04-14 05:37:06 +08:00
alikia2x	3d9e98c949	ref: extract snapshotVideoWorker into individual file	2025-04-14 05:36:07 +08:00
alikia2x	c7dd1cfc2e	improve: unused imports in snapshotTick.ts	2025-04-14 05:21:06 +08:00
alikia2x	e0a19499e1	ref: move some worker functions into individual files	2025-04-14 05:20:35 +08:00
alikia2x	0930bbe6f4	improve: remove unused import	2025-04-14 05:11:35 +08:00
alikia2x	054d28e796	merge: branch 'main' into ref/structure	2025-04-14 05:10:44 +08:00
alikia2x	0614067278	update: handler for SIGINT & SIGTERM	2025-04-14 00:49:57 +08:00
alikia2x	6df6345ec1	update: status of schedule when no proxy available	2025-04-14 00:46:43 +08:00
alikia2x	bae1f84bea	add: logging for releasing locks	2025-04-14 00:39:17 +08:00
alikia2x	21c918f1fa	fix: BitInt serialization in bulkSnapshotTickWorker	2025-04-14 00:32:06 +08:00
alikia2x	f1651fee30	fix: did not release lock for dispatchArchiveSnapshots when quitting	2025-04-14 00:28:45 +08:00
alikia2x	d0b7d93e5b	fix: incorrect logging text in archiveSnapshotsWorker	2025-04-14 00:26:59 +08:00
alikia2x	7a7c5cada9	feat: full snapshot archiving for all videos	2025-04-14 00:25:39 +08:00
alikia2x	10b761e3db	improve: query for getVideosNearMilestone	2025-04-14 00:07:50 +08:00
alikia2x	1f6411b512	fix: added lower limit in collecting videos near 100k milestone	2025-04-14 00:02:00 +08:00
alikia2x	9ef513eed7	update: add randomness in interval of re-scheduling when no proxy available	2025-04-13 23:55:43 +08:00
alikia2x	d80a6bfcd9	add: restarting when process quit	2025-04-13 23:15:09 +08:00
alikia2x	7a6892ae8e	fix: passing reference causes a const object to be incorrectly changed	2025-04-13 22:22:41 +08:00
alikia2x	b080c51c3e	merge: branch 'main' into ref/structure	2025-04-13 18:38:34 +08:00
alikia2x	f4d08e944a	fix: expand the candidate range of milestone snapshot	2025-04-13 16:50:19 +08:00
alikia2x	a9582722f4	ref: wrap some worker functions with withDbConnection	2025-04-13 05:06:52 +08:00
alikia2x	4ee4d2ede9	ref: move some functions into separate files	2025-04-12 02:08:04 +08:00
alikia2x	f21ff45dd3	version: crawler/1.0.26	2025-04-08 02:12:04 +08:00
alikia2x	b5dbf293a2	fix: missing import map for logger	2025-04-08 02:07:53 +08:00
alikia2x	fc90dad185	ref: move some common files to @core	2025-04-08 02:05:44 +08:00
alikia2x	0b36f52c6c	merge: branch 'feat/backend' into ref/structure	2025-04-08 01:43:46 +08:00
alikia2x	445886815a	add: insert snapshot when videoInfo API hit	2025-04-07 21:45:07 +08:00
alikia2x	8e7a1c3076	add: cache in videoInfo API	2025-04-07 21:37:59 +08:00
alikia2x	71ed0bd66b	feat: videoInfo API in backend	2025-04-07 21:29:53 +08:00
alikia2x	b76d8e589c	merge: branch 'main' into ref/structure	2025-04-07 02:44:21 +08:00
alikia2x	69fb3604b1	fix: missing setSnapshotStatus in takeSnapshotForVideoWorker	2025-04-07 00:41:27 +08:00
alikia2x	d98e24b62f	fix: incorrect return from snapshotTickWorker	2025-04-07 00:36:44 +08:00
alikia2x	c4c9a3a440	update: return the value from the worker in BullMQ worker	2025-04-07 00:35:36 +08:00
alikia2x	da1bea7f41	fix: unlimited addition of milestone tasks	2025-04-07 00:28:28 +08:00
alikia2x	38c0cbd371	fix: missing dependeny in frontend	2025-04-06 21:39:11 +08:00
alikia2x	a90747878e	fix: incorrect SQL ingetVideosNearMilestone	2025-04-06 16:21:46 +08:00
alikia2x	dd720b18fa	add: debug logging for collectMilestoneSnapshotsWorker	2025-04-06 16:19:05 +08:00
alikia2x	3a83df7954	add: log for collectMilestoneSnapshotsWorker	2025-04-06 15:51:21 +08:00
alikia2x	a8292d7b6b	merge: branch 'gitbook' into ref/structure	2025-04-06 02:01:05 +08:00
alikia2x	0923a34e16	doc: GitBook - No subject	2025-04-05 18:00:09 +00:00
alikia2x	f34633dc35	doc: GitBook - No subject	2025-04-05 17:59:42 +00:00
alikia2x	94e19690d1	ref: rename NetScheduler to NetworkDelegate	2025-04-06 01:38:45 +08:00
alikia2x	20668609dd	fix: SQL query to make WebStorm happy	2025-04-05 18:34:06 +08:00
alikia2x	33c6a3c1f8	ref: the imports of db schema	2025-04-05 18:26:01 +08:00
alikia2x	f39fef0d9a	ref: change all reference to db/schema to @core/db/schema	2025-04-05 15:54:40 +08:00
alikia2x	757cbbab7e	merge: branch 'main' into gitbook	2025-04-05 06:15:44 +08:00
alikia2x	b53366dbab	doc: GitBook - No subject	2025-03-30 22:20:54 +00:00