Module: database/repositories/testRepo

Test CRUD backed by SQLite.

JSON columns: steps, tags (arrays stored as JSON strings). Boolean columns: isJourneyTest, assertionEnhanced, isApiTest (stored as 0/1).

All read queries filter WHERE deletedAt IS NULL by default. Hard deletes are replaced with soft-deletes: deletedAt = datetime('now'). Use getDeletedByProjectId / restore for recycle-bin operations.

Pagination

getByProjectIdPaged and getAllPagedByProjectIds return { data: Test[], meta: { total, page, pageSize, hasMore } }.

Source:

Members

(inner, constant) TAG_LIKE_ESCAPE

SQL fragment appended to every tags LIKE ? clause to honour the backslash escapes produced by buildTagLikePattern. SQLite has no default LIKE escape, so this is required for the metacharacter escapes to take effect; PostgreSQL accepts the same syntax.

Source:

Methods

(static) bulkSetStale(testIds, isStale)

Bulk-set the isStale flag for a list of test IDs.

Parameters:
Name Type Description
testIds Array.<string>
isStale boolean
Source:

(static) bulkUpdateReviewStatus(testIds, projectId, reviewStatus, reviewedAt, extraFieldsopt) → {Array.<Object>}

Bulk update review status for a list of test IDs within a project. Only applies to non-deleted tests.

AUTO-003b: optional extraFields are applied in the SAME UPDATE statement as reviewStatus / reviewedAt, inside the same transaction. This keeps reviewStatus and provenance columns (approvalSource, approvedBy, approvedAt, approvalThreshold) atomic — the previous two-phase pattern (bulk status update, then per-row provenance writes) could leave tests approved with null provenance if the request was aborted between phases, which would miscount as human-approved on the approval-stats endpoint.

extraFields keys are filtered through VALID_COLS so caller-supplied keys can never inject column names. Values are bound via parameters.

Parameters:
Name Type Attributes Description
testIds Array.<string>
projectId string
reviewStatus string
reviewedAt string | null
extraFields Object <optional>

Additional column→value pairs to set in the same UPDATE (e.g. provenance).
Source:
Returns:

Updated test objects (re-read after the UPDATE so the response reflects all fields, not just reviewStatus).

Type
Array.<Object>

(static) clearStaleByProjectIds(projectIds) → {number}

Clear the isStale flag on all tests for the given project IDs. Called before re-evaluating staleness so previously-stale tests that have since been run are unflagged.

Parameters:
Name Type Description
projectIds Array.<string>
Source:
Returns:

Number of tests that had their stale flag cleared.

Type
number

(static) countApprovalSplitByProjectId(projectId) → {Object}

Count tests by review status + approval source for a project (AUTO-003b).

Splits approved tests into human (reviewer-driven) and auto (machine- driven) buckets based on the approvalSource column. Used by the project approval-stats endpoint to render the calibration line N auto-approved · N human · N draft.

Done as a single SUM(CASE WHEN ...) aggregate so the query cost is O(index lookup) instead of the previous O(n) in-JS scan over every row in the project. Matches the existing countByReviewStatus pattern — both are portable across the SQLite and PostgreSQL adapters (INF-001) because they use only vanilla CASE WHEN, no dialect-specific JSON or date functions.

auto matches approvalSource = 'auto' exactly; human matches approved with any other source ('human' on new rows, NULL on legacy rows approved before migration 017). This preserves the invariant auto + human == approved without a separate LEFT JOIN.

Parameters:
Name Type Description
projectId string
Source:
Returns:
Type
Object

(static) countApprovedByProjectIds(projectIds) → {number}

Count approved tests for a set of project IDs.

Parameters:
Name Type Description
projectIds Array.<string>
Source:
Returns:
Type
number

(static) countByProjectIds(projectIds) → {number}

Count non-deleted tests for a set of project IDs.

Parameters:
Name Type Description
projectIds Array.<string>
Source:
Returns:
Type
number

(static) countByReviewStatus(projectId) → {Object}

Count tests by review status for a project (non-deleted only). Also returns last-result breakdown (passed/failed) for approved tests and category breakdown (api/ui) across all statuses — so the frontend can display accurate stats without fetching all rows.

Parameters:
Name Type Description
projectId string
Source:
Returns:
Type
Object

(static) countDraftByProjectIds(projectIds) → {number}

Count draft tests for a set of project IDs.

Parameters:
Name Type Description
projectIds Array.<string>
Source:
Returns:
Type
number

(static) countReviewQueueByProjectIds(projectIds, filtersopt) → {Object}

Per-status test counts across a set of project IDs, with the same filter shape as getAllPagedByProjectIds. Powers the Review Queue's tab badges in a single COUNT-aggregated query — replaces the previous trio of pageSize: 1 paginated probes (one per tab) which produced three concurrent round-trips on every filter / page change.

The aggregate uses SUM(CASE WHEN ...) so the WHERE filters apply to every status uniformly — switching the project filter or the search input updates all three counts in lock-step.

Parameters:
Name Type Attributes Description
projectIds Array.<string>
filters Object <optional>

Same shape as getAllPagedByProjectIds's filters arg, minus reviewStatus (which is partitioned in the SUM, not filtered).
Source:
Returns:
Type
Object

(static) create(test)

Create a test.

Parameters:
Name Type Description
test Object
Source:

(static) deleteById(id)

Soft-delete a test by ID (sets deletedAt to now).

Parameters:
Name Type Description
id string
Source:

(static) deleteByProjectId(projectId) → {Array.<string>}

Soft-delete all tests for a project. Returns IDs of the tests that were just soft-deleted (excludes already-deleted).

Parameters:
Name Type Description
projectId string
Source:
Returns:

IDs of newly soft-deleted tests.

Type
Array.<string>

(static) findStaleByAge(projectIds, staleDays) → {Array.<string>}

Find non-deleted tests that have not been run in the last N days.

Parameters:
Name Type Description
projectIds Array.<string>

— Scope to these projects.
staleDays number

— Days since last run to consider stale.
Source:
Returns:

Test IDs.

Type
Array.<string>

(static) getAll() → {Array.<Object>}

Get all non-deleted tests.

Source:
Returns:
Type
Array.<Object>

(static) getAllByProjectIds(projectIds) → {Array.<Object>}

Get all non-deleted tests belonging to the given project IDs. Used by the workspace-scoped GET /api/tests endpoint (ACL-001).

Parameters:
Name Type Description
projectIds Array.<string>
Source:
Returns:
Type
Array.<Object>

(static) getAllIdsByProjectIdsIncludeDeleted(projectIds) → {Array.<string>}

Get all test IDs (including soft-deleted) for the given project IDs. Used by data-management cleanup endpoints that need to clear derived data (e.g. healing history) for ALL tests, not just live ones.

Parameters:
Name Type Description
projectIds Array.<string>
Source:
Returns:
Type
Array.<string>

(static) getAllPagedByProjectIds(projectIds, page, pageSize, filtersopt)

Cross-project paginated list with optional filters.

Mirrors getByProjectIdPaged's filter shape so the cross-project Review Queue can server-paginate without forcing the client to fetch every test in the workspace and filter in memory.

Parameters:
Name Type Attributes Description
projectIds Array.<string>

Workspace-scoped project IDs (ACL gate).
page number
pageSize number
filters Object <optional>
Properties
Name Type Attributes Description
reviewStatus string <optional>

"draft" | "approved" | "rejected"
category string <optional>

"api" | "ui" | "journey" ("journey" matches isJourneyTest = 1, orthogonal to api/ui — same column contract as getByProjectIdPaged.)
search string <optional>

LIKE match against name + sourceUrl
stale boolean <optional>
projectId string <optional>

Narrow to a single project; ignored if the project isn't in projectIds.
sortBy string <optional>

"newest" (default) | "oldest" | "quality" | "name". Applied server-side BEFORE the LIMIT/OFFSET so a global sort can span pages — a client-side sort over the current page would only reorder the rows already in hand.
Source:

(static) getById(id) → {Object|undefined}

Get a non-deleted test by ID.

Parameters:
Name Type Description
id string
Source:
Returns:
Type
Object | undefined

(static) getByIdIncludeDeleted(id) → {Object|undefined}

Get a test by ID including soft-deleted (needed for restore operations).

Parameters:
Name Type Description
id string
Source:
Returns:
Type
Object | undefined

(static) getByProjectId(projectId) → {Array.<Object>}

Get non-deleted tests for a specific project.

Parameters:
Name Type Description
projectId string
Source:
Returns:
Type
Array.<Object>

(static) getByProjectIdPaged(projectId, pageopt, pageSizeopt, filtersopt) → {PagedResult}

Get non-deleted tests for a project with pagination and optional filters.

Parameters:
Name Type Attributes Default Description
projectId string
page number | string <optional>
 1
pageSize number | string <optional>
 DEFAULT_PAGE_SIZE
filters Object <optional>
Properties
Name Type Attributes Description
reviewStatus string <optional>

— "draft", "approved", "rejected", or undefined for all.
category string <optional>

— "api", "ui", "journey", or undefined for all. ("journey" matches isJourneyTest = 1 — orthogonal to api/ui.)
search string <optional>

— free-text search against name and sourceUrl.
Source:
Returns:
Type
PagedResult

(static) getDeletedAll() → {Array.<Object>}

Get all soft-deleted tests across all projects.

Source:
Returns:
Type
Array.<Object>

(static) getDeletedByProjectId(projectId) → {Array.<Object>}

Get soft-deleted tests for a project (recycle bin view).

Parameters:
Name Type Description
projectId string
Source:
Returns:
Type
Array.<Object>

(static) hardDeleteById(id)

Hard-delete a test by ID (permanent — use only for purge operations).

Parameters:
Name Type Description
id string
Source:

(static) hardDeleteByProjectId(projectId) → {Array.<string>}

Hard-delete all tests for a project (permanent — for project purge).

Parameters:
Name Type Description
projectId string
Source:
Returns:

IDs of all deleted tests.

Type
Array.<string>

(static) restore(id) → {boolean}

Restore a soft-deleted test (clears deletedAt).

Parameters:
Name Type Description
id string
Source:
Returns:

Whether the test was found and restored.

Type
boolean

(static) restoreByProjectIdAfter(projectId, deletedAfter) → {number}

Restore soft-deleted tests for a project that were deleted at or after a given timestamp. Used by project cascade-restore to avoid restoring items that were individually deleted before the project.

Parameters:
Name Type Description
projectId string
deletedAfter string

— ISO timestamp (inclusive lower bound).
Source:
Returns:

Number of tests restored.

Type
number

(static) revokeApprovalIfApproved(id) → {boolean}

Atomic revoke (AUTO-003b): clear reviewStatus + provenance ONLY IF the row is currently approved. Returns true on success, false if the row was already in a different state.

Why a dedicated function instead of update(): revoke is the canonical concurrent-write target — two reviewers can hit POST /tests/:id/revoke at the same time, and we need exactly one to win. The route handler does a read-then-write pair (getById → check reviewStatus === 'approved' → update), which is safe on better-sqlite3 (synchronous, single-connection) but races on PostgreSQL where the pool can serve both reads from the same pre-revoke snapshot. Baking the check into the UPDATE's WHERE clause makes the operation atomic on every adapter — only the request whose UPDATE actually flips a row from approved to draft succeeds; the second request's UPDATE … WHERE reviewStatus = 'approved' matches zero rows and we return false.

Caller is responsible for the 400/404 mapping: the route returns 400 "only approved tests can be revoked" when this returns false, matching the existing read-then-write semantics.

Parameters:
Name Type Description
id string
Source:
Returns:

true if the row transitioned approved → draft.

Type
boolean

(static) update(id, fields)

Update specific fields on a test.

Parameters:
Name Type Description
id string
fields Object

— Partial test fields to update.
Source:

(inner) buildTagLikePattern(tag) → {string}

Build a tags LIKE pattern for a single tag value, escaping the SQL LIKE metacharacters (%, _) and the escape char itself (\) so user-supplied tags like "50%_off" don't match unrelated rows. The returned pattern MUST be used with a LIKE ? ESCAPE '\\' clause — see TAG_LIKE_ESCAPE.

Tags are persisted as a JSON-encoded array, so the pattern wraps the value in "…" quotes to anchor on the JSON-string boundary; embedded " chars in the tag value are escaped with \" to match JSON.stringify output.

Parameters:
Name Type Description
tag string
Source:
Returns:

LIKE pattern

Type
string

(inner) countByProjectIdsAndStatus(projectIds, reviewStatus) → {number}

Count tests by review status for a set of project IDs.

Parameters:
Name Type Description
projectIds Array.<string>
reviewStatus "approved" | "draft"
Source:
Returns:
Type
number