UAT: LockModel.acquired_at and expires_at use String(30) but ISO datetime with timezone is 32 characters — potential truncation in PostgreSQL #4003

New issue

Open

opened 2026-04-06 08:28:14 +00:00 by freemo · 0 comments

freemo commented

2026-04-06 08:28:14 +00:00

Owner

Metadata

Commit Message: fix(concurrency): increase LockModel datetime column size from String(30) to String(35)
Branch: fix/lock-model-datetime-column-size
Milestone: Backlog
Parent Epic: (linked via dependency — see issue dependencies)

Bug Description

LockModel.acquired_at and LockModel.expires_at are defined as String(30) in the SQLAlchemy model, but datetime.now(tz=UTC).isoformat() produces strings that are 32 characters long. This causes silent truncation in PostgreSQL (which enforces VARCHAR length limits) and could cause incorrect datetime comparisons.

Expected Behavior

The column size should accommodate the full ISO 8601 datetime string with timezone offset. datetime.now(tz=UTC).isoformat() produces strings like:

2026-04-06T10:30:00.123456+00:00

That is 32 characters. The column should be at least String(35) to accommodate this format with margin.

Actual Behavior

src/cleveragents/infrastructure/database/models.py (lines 2645-2646):

acquired_at: Mapped[str] = mapped_column(String(30), nullable=False)
expires_at: Mapped[str] = mapped_column(String(30), nullable=False)

String(30) is 2 characters too short for the ISO datetime format used throughout lock_service.py:

now = datetime.now(tz=UTC)
expires = now + timedelta(seconds=ttl_seconds)
now_iso = now.isoformat()         # e.g. "2026-04-06T10:30:00.123456+00:00" (32 chars)
expires_iso = expires.isoformat() # e.g. "2026-04-06T10:35:00.123456+00:00" (32 chars)

Impact

SQLite (used in tests and local mode): No impact — SQLite does not enforce VARCHAR length limits
PostgreSQL (production/server mode): acquired_at and expires_at values would be silently truncated to 30 characters, resulting in:
- Corrupted datetime values stored in the database
- Incorrect lock expiry comparisons (string comparison on truncated values)
- Potential lock conflicts or missed expirations

Character Count Verification

2026-04-06T10:30:00.123456+00:00
^         ^         ^         ^^
1234567890123456789012345678901234
                              ^^-- 32 chars total

Code Location

src/cleveragents/infrastructure/database/models.py: lines 2645-2646 (LockModel)
src/cleveragents/application/services/lock_service.py: lines 159-161 (datetime generation)

Fix

Change String(30) to String(35) for both columns in LockModel:

acquired_at: Mapped[str] = mapped_column(String(35), nullable=False)
expires_at: Mapped[str] = mapped_column(String(35), nullable=False)

Also update docs/reference/concurrency.md table to reflect VARCHAR(35).

A database migration will be needed to alter the column sizes in existing databases.

Subtasks

Change String(30) to String(35) for acquired_at and expires_at in LockModel
Create Alembic migration to alter column sizes in existing databases
Update docs/reference/concurrency.md table to show VARCHAR(35)
Add a test that verifies datetime strings of 32+ characters are stored and retrieved correctly
Verify coverage >= 97%
Run nox and fix any errors

Definition of Done

All subtasks completed
LockModel datetime columns are String(35) or larger
Migration created and tested
Documentation updated
All nox stages pass
Coverage >= 97%

Backlog note: This issue was discovered during autonomous operation
on milestone v3.3.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: UAT Testing | Agent: ca-uat-tester

Automated by CleverAgents Bot
Supervisor: UAT Testing | Agent: ca-new-issue-creator

## Metadata - **Commit Message**: `fix(concurrency): increase LockModel datetime column size from String(30) to String(35)` - **Branch**: `fix/lock-model-datetime-column-size` - **Milestone**: Backlog - **Parent Epic**: (linked via dependency — see issue dependencies) ## Bug Description `LockModel.acquired_at` and `LockModel.expires_at` are defined as `String(30)` in the SQLAlchemy model, but `datetime.now(tz=UTC).isoformat()` produces strings that are 32 characters long. This causes silent truncation in PostgreSQL (which enforces VARCHAR length limits) and could cause incorrect datetime comparisons. ## Expected Behavior The column size should accommodate the full ISO 8601 datetime string with timezone offset. `datetime.now(tz=UTC).isoformat()` produces strings like: ``` 2026-04-06T10:30:00.123456+00:00 ``` That is **32 characters**. The column should be at least `String(35)` to accommodate this format with margin. ## Actual Behavior `src/cleveragents/infrastructure/database/models.py` (lines 2645-2646): ```python acquired_at: Mapped[str] = mapped_column(String(30), nullable=False) expires_at: Mapped[str] = mapped_column(String(30), nullable=False) ``` `String(30)` is 2 characters too short for the ISO datetime format used throughout `lock_service.py`: ```python now = datetime.now(tz=UTC) expires = now + timedelta(seconds=ttl_seconds) now_iso = now.isoformat() # e.g. "2026-04-06T10:30:00.123456+00:00" (32 chars) expires_iso = expires.isoformat() # e.g. "2026-04-06T10:35:00.123456+00:00" (32 chars) ``` ## Impact - **SQLite** (used in tests and local mode): No impact — SQLite does not enforce VARCHAR length limits - **PostgreSQL** (production/server mode): `acquired_at` and `expires_at` values would be silently truncated to 30 characters, resulting in: - Corrupted datetime values stored in the database - Incorrect lock expiry comparisons (string comparison on truncated values) - Potential lock conflicts or missed expirations ## Character Count Verification ``` 2026-04-06T10:30:00.123456+00:00 ^ ^ ^ ^^ 1234567890123456789012345678901234 ^^-- 32 chars total ``` ## Code Location - `src/cleveragents/infrastructure/database/models.py`: lines 2645-2646 (LockModel) - `src/cleveragents/application/services/lock_service.py`: lines 159-161 (datetime generation) ## Fix Change `String(30)` to `String(35)` for both columns in `LockModel`: ```python acquired_at: Mapped[str] = mapped_column(String(35), nullable=False) expires_at: Mapped[str] = mapped_column(String(35), nullable=False) ``` Also update `docs/reference/concurrency.md` table to reflect `VARCHAR(35)`. A database migration will be needed to alter the column sizes in existing databases. ## Subtasks - [ ] Change `String(30)` to `String(35)` for `acquired_at` and `expires_at` in `LockModel` - [ ] Create Alembic migration to alter column sizes in existing databases - [ ] Update `docs/reference/concurrency.md` table to show `VARCHAR(35)` - [ ] Add a test that verifies datetime strings of 32+ characters are stored and retrieved correctly - [ ] Verify coverage >= 97% - [ ] Run `nox` and fix any errors ## Definition of Done - [ ] All subtasks completed - [ ] `LockModel` datetime columns are `String(35)` or larger - [ ] Migration created and tested - [ ] Documentation updated - [ ] All nox stages pass - [ ] Coverage >= 97% > **Backlog note:** This issue was discovered during autonomous operation > on milestone v3.3.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: UAT Testing | Agent: ca-uat-tester --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: ca-new-issue-creator

freemo added a new dependency

2026-04-06 08:28:20 +00:00

#362 Epic: Security & Safety Hardening

freemo referenced this issue

2026-04-06 08:29:00 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo added the

Points

label

2026-04-06 08:31:45 +00:00

freemo referenced this issue

2026-04-06 08:33:59 +00:00