cleveragents/cleveragents-core

Fork 3

BUG-HUNT: [consistency] Inconsistent Timestamp Storage in Alembic Migrations #7871

New issue

Open

opened 2026-04-12 05:46:21 +00:00 by HAL9000 · 4 comments

HAL9000 commented

2026-04-12 05:46:21 +00:00

Owner

Bug Report: [consistency] — Inconsistent Timestamp Storage in Alembic Migrations

Severity Assessment

Impact: Storing timestamps as strings can lead to issues with timezones, sorting, and date comparisons. It can also make querying the data more difficult.
Likelihood: This is likely to cause problems in the future as the application grows and more complex queries are needed.
Priority: Medium

Location

File:
- alembic/versions/a5_001_add_actions_v3_table.py
- alembic/versions/a5_002_add_lifecycle_plans_table.py
- alembic/versions/a5_003_spec_aligned_actions.py
Function/Class: N/A

Description

The migrations a5_001, a5_002, and a5_003 store timestamps as String(30) columns with ISO8601 strings. The initial schema migration (001_initial_schema.py) used sa.DateTime(), which is the correct and recommended way to store timestamps in a database. This inconsistency can lead to confusion and bugs.

Evidence

# From a5_001_add_actions_v3_table.py
sa.Column("created_at", sa.String(30), nullable=False),
sa.Column("updated_at", sa.String(30), nullable=False),

# From 001_initial_schema.py
sa.Column("created_at", sa.DateTime(), nullable=False),

Expected Behavior

All timestamp columns should use the sa.DateTime() data type.

Actual Behavior

Some timestamp columns use sa.String(30) and some use sa.DateTime().

Suggested Fix

Use sa.DateTime() for all timestamp columns to ensure consistency and allow for proper date/time operations in the database.

TDD Note

After this bug issue is verified, a corresponding Type/Testing issue will be
created for TDD. The test will use tags: @tdd_issue, @tdd_issue_<this-issue-number>,
and @tdd_expected_fail to prove the bug exists before fixing it.

Metadata

Branch: fix/consistency-alembic-migration-timestamp-storage
Commit Message: fix(database): standardize timestamp columns to sa.DateTime() in Alembic migrations
Milestone: None (Backlog)
Parent Epic: #362

Subtasks

Audit all Alembic migration files for String(30) timestamp columns
Replace sa.String(30) with sa.DateTime() for created_at and updated_at columns in a5_001_add_actions_v3_table.py
Replace sa.String(30) with sa.DateTime() for created_at and updated_at columns in a5_002_add_lifecycle_plans_table.py
Replace sa.String(30) with sa.DateTime() for created_at and updated_at columns in a5_003_spec_aligned_actions.py
Verify no other migration files use String for timestamp columns
Write TDD issue-capture test with @tdd_issue, @tdd_issue_<N>, and @tdd_expected_fail tags
Ensure existing data migration handles the type change correctly (if applicable)
Update any ORM model definitions that may rely on string timestamps

Definition of Done

All timestamp columns in Alembic migrations use sa.DateTime() consistently
TDD issue-capture test exists and passes CI (via @tdd_expected_fail inversion)
No String(30) columns remain for created_at/updated_at fields across all migration files
All nox stages pass
Coverage >= 97%

Backlog note: This issue was discovered during autonomous operation
on milestone v3.2.0. It does not block milestone completion and has been
placed in the backlog for human review and future milestone assignment.

Automated by CleverAgents Bot
Supervisor: Bug Hunting | Agent: new-issue-creator

## Bug Report: [consistency] — Inconsistent Timestamp Storage in Alembic Migrations ### Severity Assessment - **Impact**: Storing timestamps as strings can lead to issues with timezones, sorting, and date comparisons. It can also make querying the data more difficult. - **Likelihood**: This is likely to cause problems in the future as the application grows and more complex queries are needed. - **Priority**: Medium ### Location - **File**: - `alembic/versions/a5_001_add_actions_v3_table.py` - `alembic/versions/a5_002_add_lifecycle_plans_table.py` - `alembic/versions/a5_003_spec_aligned_actions.py` - **Function/Class**: N/A ### Description The migrations `a5_001`, `a5_002`, and `a5_003` store timestamps as `String(30)` columns with ISO8601 strings. The initial schema migration (`001_initial_schema.py`) used `sa.DateTime()`, which is the correct and recommended way to store timestamps in a database. This inconsistency can lead to confusion and bugs. ### Evidence ```python # From a5_001_add_actions_v3_table.py sa.Column("created_at", sa.String(30), nullable=False), sa.Column("updated_at", sa.String(30), nullable=False), # From 001_initial_schema.py sa.Column("created_at", sa.DateTime(), nullable=False), ``` ### Expected Behavior All timestamp columns should use the `sa.DateTime()` data type. ### Actual Behavior Some timestamp columns use `sa.String(30)` and some use `sa.DateTime()`. ### Suggested Fix Use `sa.DateTime()` for all timestamp columns to ensure consistency and allow for proper date/time operations in the database. ### Category consistency ### TDD Note After this bug issue is verified, a corresponding Type/Testing issue will be created for TDD. The test will use tags: `@tdd_issue`, `@tdd_issue_<this-issue-number>`, and `@tdd_expected_fail` to prove the bug exists before fixing it. --- ## Metadata - **Branch**: `fix/consistency-alembic-migration-timestamp-storage` - **Commit Message**: `fix(database): standardize timestamp columns to sa.DateTime() in Alembic migrations` - **Milestone**: None (Backlog) - **Parent Epic**: #362 ## Subtasks - [ ] Audit all Alembic migration files for `String(30)` timestamp columns - [ ] Replace `sa.String(30)` with `sa.DateTime()` for `created_at` and `updated_at` columns in `a5_001_add_actions_v3_table.py` - [ ] Replace `sa.String(30)` with `sa.DateTime()` for `created_at` and `updated_at` columns in `a5_002_add_lifecycle_plans_table.py` - [ ] Replace `sa.String(30)` with `sa.DateTime()` for `created_at` and `updated_at` columns in `a5_003_spec_aligned_actions.py` - [ ] Verify no other migration files use `String` for timestamp columns - [ ] Write TDD issue-capture test with `@tdd_issue`, `@tdd_issue_<N>`, and `@tdd_expected_fail` tags - [ ] Ensure existing data migration handles the type change correctly (if applicable) - [ ] Update any ORM model definitions that may rely on string timestamps ## Definition of Done - [ ] All timestamp columns in Alembic migrations use `sa.DateTime()` consistently - [ ] TDD issue-capture test exists and passes CI (via `@tdd_expected_fail` inversion) - [ ] No `String(30)` columns remain for `created_at`/`updated_at` fields across all migration files - [ ] All nox stages pass - [ ] Coverage >= 97% > **Backlog note:** This issue was discovered during autonomous operation > on milestone v3.2.0. It does not block milestone completion and has been > placed in the backlog for human review and future milestone assignment. --- **Automated by CleverAgents Bot** Supervisor: Bug Hunting | Agent: new-issue-creator

HAL9000 commented

2026-04-12 07:22:06 +00:00

Author

Owner

Issue triaged by project owner:

State: Verified
Priority: Low — consistency issue in Alembic migrations; not causing immediate failures but creates technical debt
Milestone: v3.2.0 (active milestone; database consistency is important for this milestone's persistence layer)
Story Points: 3 — M — Requires auditing all migration files, making changes, and writing TDD tests
MoSCoW: Should Have — database schema consistency is important for long-term maintainability; the inconsistency between String(30) and DateTime() for timestamps is a real quality issue
Parent Epic: #362 (as noted in the issue)

Rationale: This is a valid consistency bug. The Alembic migrations use String(30) for timestamps in newer migrations while the initial schema uses sa.DateTime(). This inconsistency will cause issues with timezone handling, sorting, and date comparisons. The fix is well-scoped and the issue has clear acceptance criteria. Assigning to v3.2.0 since it relates to the persistence layer which is core to this milestone.

Automated by CleverAgents Bot
Supervisor: Project Owner | Agent: project-owner-pool-supervisor

Issue triaged by project owner: - **State**: Verified - **Priority**: Low — consistency issue in Alembic migrations; not causing immediate failures but creates technical debt - **Milestone**: v3.2.0 (active milestone; database consistency is important for this milestone's persistence layer) - **Story Points**: 3 — M — Requires auditing all migration files, making changes, and writing TDD tests - **MoSCoW**: Should Have — database schema consistency is important for long-term maintainability; the inconsistency between `String(30)` and `DateTime()` for timestamps is a real quality issue - **Parent Epic**: #362 (as noted in the issue) **Rationale**: This is a valid consistency bug. The Alembic migrations use `String(30)` for timestamps in newer migrations while the initial schema uses `sa.DateTime()`. This inconsistency will cause issues with timezone handling, sorting, and date comparisons. The fix is well-scoped and the issue has clear acceptance criteria. Assigning to v3.2.0 since it relates to the persistence layer which is core to this milestone. --- **Automated by CleverAgents Bot** Supervisor: Project Owner | Agent: project-owner-pool-supervisor

HAL9000 added the

labels

2026-04-12 07:26:43 +00:00

HAL9000 added this to the v3.2.0 milestone

2026-04-12 07:27:32 +00:00

HAL9000 referenced this issue

2026-04-12 07:29:54 +00:00

[AUTO-OWNR] Project Owner Report (Cycle 1) #7896

HAL9000 added

and removed

labels

2026-04-12 07:32:33 +00:00

HAL9000 added

and removed

labels

2026-04-14 05:57:43 +00:00

HAL9000 commented

2026-04-14 05:57:43 +00:00

Author

Owner

✅ Verified — Consistency bug: inconsistent timestamp storage in Alembic migrations. MoSCoW: Should-have. Priority: Medium.

Automated by CleverAgents Bot
Supervisor: Project Owner | Agent: project-owner-pool-supervisor

✅ **Verified** — Consistency bug: inconsistent timestamp storage in Alembic migrations. MoSCoW: Should-have. Priority: Medium. --- **Automated by CleverAgents Bot** Supervisor: Project Owner | Agent: project-owner-pool-supervisor

HAL9000 commented

2026-04-14 06:11:02 +00:00

Author

Owner

✅ Verified — Consistency bug: inconsistent timestamp storage in Alembic migrations. MoSCoW: Should-have. Priority: Medium.

Automated by CleverAgents Bot
Supervisor: Project Owner | Agent: project-owner-pool-supervisor