scriptoriumadmin/skillhub
1
0
Fork 0
mirror of https://github.com/iflytek/skillhub.git synced 2026-08-28 11:25:00 +00:00
Code Issues Projects Releases Packages Wiki Activity Actions 3
skillhub/docs/16-backend-time-inventory.md
dongmucat 04caf21e76
fix(audit): resolve 8-hour timezone offset in audit log timestamps (#472) 
## Problem
Audit log timestamps displayed 8 hours later than actual time when JVM
default timezone != UTC. Root cause: `audit_log.created_at` was
`TIMESTAMP without time zone`, and `rs.getTimestamp()` interprets bare
values using JVM timezone.

## Solution
### Backend
- **V42 migration**: Upgrade `audit_log.created_at` from `TIMESTAMP` to
  `TIMESTAMPTZ`, anchor historical data as UTC via `USING created_at AT
  TIME ZONE 'UTC'` (same pattern as V18/V19/V23/V25/V36)
- **Read path**: `AdminAuditLogAppService.readInstant()` uses
  `rs.getObject(col, OffsetDateTime.class).toInstant()`, result
  independent of JVM timezone
- **Write path (filter params)**: `startTime`/`endTime` binding changed
  from `Timestamp.from()` to `OffsetDateTime.ofInstant(instant,
  ZoneOffset.UTC)` via `toUtcOffsetDateTime()` helper, symmetric with
  read path

### Migration Safety
- `SET LOCAL lock_timeout = '30s'` (transaction-scoped, won't leak to pool)
- `DO $$ ... IF data_type = 'timestamp without time zone' THEN ... ELSE
  ... END $$` idempotent guard with dual-branch `RAISE NOTICE`
- Safe retry: re-running won't double-apply `AT TIME ZONE 'UTC'`

### Test Coverage (10 tests, 477 total suite)
- `rowMapper_readsCreatedAtAsInstant` — UTC offset regression
- `rowMapper_normalisesNonUtcOffsetToInstant` — Non-UTC offset (+08:00)
- `rowMapper_returnsNullTimestampWhenColumnIsNull` — Null path
- `rowMapper_isIndependentOfJvmDefaultTimezone` — JVM TZ=Asia/Shanghai
  drift prevention with `verify(rs, never()).getTimestamp()`
- `@ParameterizedTest buildWhereClause_bindsTimeRangeAsOffsetDateTime` —
  3 cases (both/startOnly/endOnly) for filter param binding
- `@BeforeEach setUp()` — Mock isolation to prevent cross-test stub
  accumulation

## Quality Gates
- [x] `make test-backend-app` passes (477 tests, 0 failures)
- [x] No Controller changes, `make generate-api` not needed
- [x] No frontend changes, typecheck/lint/e2e not needed

## Deployment
V42 must run before new code (guaranteed by Spring Boot startup sequence
→ Flyway executes before app accepts traffic). Rolling deployment:
- New pod + migrated column: correct
- Old pod + migrated column: old code reads TIMESTAMPTZ correctly (pgjdbc
  returns absolute instant)

## Related Docs
- `docs/15-backend-time-governance-plan.md` §3.1: V42 progress registered
- `docs/16-backend-time-inventory.md` §3.1: V42 listed
- Same migration pattern: V18/V19/V23/V25/V36
2026-06-05 15:28:12 +08:00

240 lines
6.1 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# skillhub 后端时间字段台账
## 1. 扫描范围
本台账基于 `server/skillhub-app`、`server/skillhub-auth`、`server/skillhub-domain`、`server/skillhub-infra`、`server/skillhub-storage` 的当前生产代码与 Flyway migration。
目标已经从“摸底问题分布”转为“记录当前真实进展与剩余尾项”。
## 2. 当前代码分布
### 2.1 生产代码中的 `LocalDateTime` 已基本清空
当前生产代码里只剩 1 处兼容解析保留 `LocalDateTime`：
- `ApiTokenService`
- 用于兼容旧接口传入的裸时间字符串
- 当前明确按 UTC 解释后转成 `Instant`
此前集中使用 `LocalDateTime` 的主链区域已完成迁移或收口：
- 认证与账号：
- `api_token`
- `account_merge_request`
- `user_account`
- `identity_binding`
- `role`
- `user_role_binding`
- `local_credential`
- 核心领域：
- `namespace`
- `namespace_member`
- `skill`
- `skill_version`
- `skill_file`
- `skill_tag`
- `skill_version_stats`
- `skill_report`
- `skill_star`
- `skill_rating`
- 服务层：
- `AccountMergeService`
- `LocalAuthService`
- `SkillPublishService`
- `SkillGovernanceService`
- `ReviewService`
- `PromotionService`
- `SkillReportService`
- DTO 与接口输出：
- `NamespaceResponse`
- `MemberResponse`
- `SkillSummaryResponse`
- `SkillVersionResponse`
- `SkillVersionDetailResponse`
- `TagResponse`
- `AdminUserSummaryResponse`
- `AdminSkillReportSummaryResponse`
结论：
- 主系统核心“事件发生时间”已经基本收口成 UTC 绝对时间
- 当前剩余工作主要是兼容策略、数据库尾项复核和防回归约束
### 2.2 `Instant` 已成为主流绝对时间类型
当前已稳定使用 `Instant` 的代表区域：
- 审计：
- `AuditLog`
- `AuditLogItemResponse`
- 通知：
- `UserNotification`
- 审核流程：
- `ReviewTask`
- `PromotionRequest`
- `ReviewTaskResponse`
- `PromotionResponseDto`
- 幂等：
- `IdempotencyRecord`
- `IdempotencyInterceptor`
- `IdempotencyCleanupTask`
- 技能主链：
- `Skill`
- `SkillVersion`
- `SkillTag`
- `SkillFile`
- `SkillVersionStats`
- 认证主链：
- `ApiToken`
- `AccountMergeRequest`
- `UserAccount`
- `IdentityBinding`
- `Role`
- `UserRoleBinding`
- `LocalCredential`
## 3. 数据库层分布
### 3.1 已完成的 `TIMESTAMPTZ` 迁移
- `V12__governance_notifications.sql`
- `user_notification.created_at / read_at`
- `V24__api_token_timestamptz.sql`
- `api_token.expires_at / last_used_at / revoked_at / created_at`
- `V25__account_merge_request_timestamptz.sql`
- `account_merge_request.token_expires_at / completed_at / created_at`
- `V26__skill_version_timestamptz.sql`
- `skill_version.published_at / created_at / yanked_at`
- `V16__skill_hidden_at_timestamptz.sql`
- `skill.hidden_at`
- `V17__skill_created_updated_timestamptz.sql`
- `skill.created_at / updated_at`
- `V18__namespace_timestamptz.sql`
- `namespace.created_at / updated_at`
- `namespace_member.created_at / updated_at`
- `V19__skill_secondary_timestamptz.sql`
- `skill_tag.created_at / updated_at`
- `skill_file.created_at`
- `skill_version_stats.updated_at`
- `V20__social_and_skill_report_timestamptz.sql`
- `skill_star.created_at`
- `skill_rating.created_at / updated_at`
- `skill_report.created_at / handled_at`
- `V21__user_account_timestamptz.sql`
- `user_account.created_at / updated_at`
- `V22__auth_supporting_tables_timestamptz.sql`
- `identity_binding.created_at / updated_at`
- `role.created_at`
- `user_role_binding.created_at`
- `local_credential.locked_until / created_at / updated_at`
- `V23__review_and_idempotency_timestamptz.sql`
- `review_task.submitted_at / reviewed_at`
- `promotion_request.submitted_at / reviewed_at`
- `idempotency_record.created_at / expires_at`
- `V42__audit_log_created_at_timestamptz.sql`
- `audit_log.created_at`
### 3.2 当前状态
- 主链核心事件时间列已基本完成 `TIMESTAMPTZ` 收口
- 初始建表 migration 中仍然能看到旧 `TIMESTAMP` 定义，但已由后续 Flyway 升级覆盖
- 后续重点不是“大批量迁移”，而是查漏补缺和约束新增
## 4. 已解决的高风险热点
### 4.1 兼容层时区解释冲突
此前：
- `ClawHubCompatController` 按 `ZoneOffset.UTC` 转 epoch
- `ClawHubRegistryFacade` 按系统默认时区解释
当前：
- 已统一按 UTC 解释绝对时间
- `ClawHubRegistryFacade` 的 `LocalDateTime` epoch 转换重载已移除
### 4.2 服务层散落的 `now()`
此前热点包括：
- `ApiTokenService`
- `AccountMergeService`
- `LocalAuthService`
- `SkillPublishService`
- `SkillGovernanceService`
- `ReviewService`
- `PromotionService`
- `SkillReportService`
- 多个实体 `@PrePersist` / `@PreUpdate`
当前：
- 服务层当前时间已基本统一为注入 `Clock`
- 实体回调已基本统一为显式 UTC
## 5. 分批迁移进展
### Batch 1：基础设施与治理链路
已完成：
- UTC `Clock` Bean
- Hibernate UTC 配置
- Jackson UTC 配置
- `ApiResponseFactory`
- `IdempotencyInterceptor`
- `IdempotencyCleanupTask`
- 审计、通知、审核、幂等链路
### Batch 2：认证与账号链路
已完成：
- `ApiToken` / `ApiTokenService`
- `AccountMergeRequest` / `AccountMergeService`
- `LocalCredential`
- `UserAccount`
- `IdentityBinding`
- `Role`
- `UserRoleBinding`
- `LocalAuthService`
### Batch 3：技能核心领域
已完成：
- `Skill`
- `SkillVersion`
- `SkillFile`
- `SkillTag`
- `SkillVersionStats`
- `Namespace`
- `NamespaceMember`
- `SkillPublishService`
- `SkillGovernanceService`
- `ReviewService`
- `PromotionService`
- `SkillReport`
- `SkillStar`
- `SkillRating`
### Batch 4：DTO 与 API 契约收口
已完成：
- `NamespaceResponse`
- `MemberResponse`
- `SkillSummaryResponse`
- `SkillVersionResponse`
- `SkillVersionDetailResponse`
- `TagResponse`
- `AdminUserSummaryResponse`
- `AdminSkillReportSummaryResponse`
- `TokenController` 的 UTC 输出收口
## 6. 当前剩余尾项
- `ApiTokenService` 仍保留对裸 `LocalDateTime` 字符串的兼容解析
- 需要补静态扫描或 ArchUnit 约束，防止新增 `LocalDateTime.now()`
- 需要做一轮跨时区回归，把 `UTC` / `Asia/Shanghai` 纳入关键测试
Reference in a new issue View git blame Copy permalink