mini-starter/docs/stories/012.001.story.md

Story 012.001: Database Schema Expansion

Status

Draft

Story

As a system developer, I want to extend the database schema to support employer mini-program features, so that subsequent API implementations have complete data support and accurate age statistics.

Acceptance Criteria

Copy the acceptance criteria numbered list from the epic file

1. disabled_person表成功添加birth_date字段，现有记录该字段值为NULL
2. order_person_asset表的asset_type枚举扩展完成，新增视频类型枚举值
3. users2表成功添加company_id字段，现有admin用户的该字段值为NULL
4. 数据库迁移脚本可正确执行向前和向后迁移
5. Prisma/Drizzle schema定义更新完成
6. 现有业务功能不受影响，测试通过

Tasks / Subtasks

Break down the story into specific tasks and subtasks needed for implementation. Reference applicable acceptance criteria numbers where relevant.

• Task 1: Add nullable birth_date field to disabled_person table (AC: 1)
  • Modify allin-packages/disability-module/src/entities/disabled-person.entity.ts to add birthDate field
  • Update corresponding schema validation in disability-module
  • Create database migration script for adding birth_date column
• Task 2: Extend asset_type enum in order_person_asset table (AC: 2)
  • Update AssetType enum in allin-packages/order-module/src/schemas/order.schema.ts
  • Add new video types: salary_video, tax_video, checkin_video, work_video
  • Ensure backward compatibility with existing enum values
  • Update entity validation in order-person-asset.entity.ts
• Task 3: Add nullable company_id field to users2 table (AC: 3)
  • Locate user entity (likely in user-module or auth-module)
  • Add companyId field with foreign key reference to employer_company.company_id
  • Create database migration script for adding company_id column
• Task 4: Create database migration scripts (AC: 4)
  • Create forward migration script for all three schema changes
  • Create backward/rollback migration script
  • Test migration scripts on test database
• Task 5: Update Prisma/Drizzle schema definitions (AC: 5)
  • Update schema definitions to reflect new fields and enum values
  • Verify schema consistency across all modules
• Task 6: Validation and testing (AC: 6)
  • Run existing tests to ensure no regression
  • Add unit tests for new fields and enum values
  • Test backward compatibility with existing data
  • Verify all existing business functions work correctly

Dev Notes

Populate relevant information, only what was pulled from actual artifacts from docs folder, relevant to this story:

Previous Story Insights

No previous stories in epic 012 (this is the first story).

Data Models

Based on existing entity definitions:

DisabledPerson Entity (allin-packages/disability-module/src/entities/disabled-person.entity.ts):

• Primary key: id (mapped to person_id column) [Source: architecture/backend-module-package-standards.md#实体设计规范]
• Field naming convention: database underscore → TypeScript camelCase [Source: architecture/backend-module-package-standards.md#字段命名转换]
• Current fields include: name, gender, idCard, disabilityId, disabilityType, disabilityLevel, phone, jobStatus, etc.
• Missing birth_date field (DATE type) for accurate age calculation
• New field should be nullable: birthDate?: Date [Source: docs/prd/epic-012-api-supplement-for-employer-mini-program.md#故事012-01]

OrderPersonAsset Entity (allin-packages/order-module/src/entities/order-person-asset.entity.ts):

• Primary key: id (mapped to op_id column)
• assetType field uses AssetType enum (varchar(50))
• Current enum values: tax, salary, job_result, contract_sign, disability_cert, other [Source: allin-packages/order-module/src/schemas/order.schema.ts]
• Need to extend with video types: salary_video, tax_video, checkin_video, work_video
• Enum values should be lowercase strings with underscores [Source: architecture/backend-module-package-standards.md#枚举值一致性]

User Entity (to be located):

• Based on epic description, users2 table needs company_id field
• Should reference employer_company.company_id as foreign key
• Field should be nullable (existing admin users have NULL) [Source: docs/prd/epic-012-api-supplement-for-employer-mini-program.md#故事012-01]

API Specifications

Not applicable for this schema-only story. Subsequent stories will implement APIs using these schema changes.

Component Specifications

Not applicable (backend schema story).

File Locations

Based on project structure [Source: architecture/source-tree.md]:

• allin-packages/disability-module/src/entities/disabled-person.entity.ts
• allin-packages/order-module/src/entities/order-person-asset.entity.ts
• allin-packages/order-module/src/schemas/order.schema.ts
• User entity location: Need to locate (check packages/user-module or packages/auth-module)
• Migration scripts: Should be created in appropriate location (TBD based on existing migration pattern)

Technical Constraints

• Backward compatibility: All new fields must be nullable, existing data unaffected [Source: docs/prd/epic-012-api-supplement-for-employer-mini-program.md#兼容性要求]
• Database: PostgreSQL 17, TypeORM 0.3.25 [Source: architecture/tech-stack.md]
• Enum extension: Must retain existing enum values, new values added [Source: docs/prd/epic-012-api-supplement-for-employer-mini-program.md#风险缓解]
• Migration scripts: Must support forward and backward migration [Source: docs/prd/epic-012-api-supplement-for-employer-mini-program.md#任务列表]

Project Structure Notes

• Monorepo structure with allin-packages/ for business modules [Source: architecture/source-tree.md]
• Each module follows backend module package standards [Source: architecture/backend-module-package-standards.md]
• Entity naming: disabled-person.entity.ts (kebab-case) [Source: architecture/source-tree.md#集成指南]
• Field mapping: Database birth_date → TypeScript birthDate [Source: architecture/backend-module-package-standards.md#字段命名转换]

Testing

List Relevant Testing Standards from Architecture the Developer needs to conform to:

Test File Location

• Unit tests: allin-packages/{module-name}/tests/unit/**/*.test.ts [Source: architecture/testing-strategy.md#单元测试]
• Integration tests: allin-packages/{module-name}/tests/integration/**/*.test.ts [Source: architecture/testing-strategy.md#集成测试]

Test Standards

• Test framework: Vitest [Source: architecture/testing-strategy.md#单元测试]
• Database testing: Use test database with transaction rollback [Source: architecture/testing-strategy.md#数据库测试策略]
• Coverage requirements: Unit tests ≥ 80%, Integration tests ≥ 60% [Source: architecture/testing-strategy.md#测试覆盖率标准]

Testing Frameworks and Patterns

• Unit tests: Test individual entities and schemas
• Integration tests: Test database migration scripts and schema changes
• Backward compatibility tests: Verify existing data and queries work
• Enum validation tests: Test new enum values are accepted

Specific Testing Requirements for This Story

1. Schema validation tests: Verify new fields are correctly nullable
2. Enum extension tests: Verify new enum values work with existing code
3. Migration tests: Test forward and backward migration scripts
4. Regression tests: Run existing module tests to ensure no breakage
5. Foreign key tests: Verify company_id foreign key relationship

Change Log

Track changes made to this story document

Date	Version	Description	Author
2025-12-13	1.0	Initial story creation	Bob (Scrum Master)

Dev Agent Record

This section is populated by the development agent during implementation

Agent Model Used

{{agent_model_name_version}}

Debug Log References

Reference any debug logs or traces generated during development

Completion Notes List

Notes about the completion of tasks and any issues encountered

File List

List all files created, modified, or affected during story implementation

QA Results

Results from QA Agent QA review of the completed story implementation