Forge queries

forge-queries: Query behavior

Purpose

Find correctness, injection, overfetching, N+1, pagination, locking, and index-use risks in data access.

Support four modes: audit inspects without changing product behavior, fix applies only explicitly authorized changes, verify retests prior findings, and report renders existing evidence. If no mode is supplied, use audit.

Trigger conditions

Use this module when a request names forge-queries, asks about query behavior, or discovery finds an applicable boundary. Run it from the repository root after project discovery.

When it applies

• Database, search, analytics, and remote query code

When it does not apply

• Systems with no queryable data source

Do not silently skip it. Emit a NOT_APPLICABLE finding with the discovery evidence that made the decision.

Inputs from project discovery

• query call sites
• schema and indexes
• representative query plans when available

Prefer .forge/project-profile.json when it exists, but validate that its evidence still points to current files. Read ../fullstack-forge/references/PROTOCOL.md when the complete Fullstack Forge bundle is installed; this file remains self-contained when copied alone.

Inspection procedure

1. Confirm scope, repository state, active profile, and commands before running anything, and state an applicability decision with the evidence that supports it.
2. Collect query call sites from ORM and driver evidence and identify the hot paths from routes and jobs.
3. Detect N+1 shapes: queries inside loops or per-row lazy loads, and verify batch or join alternatives.
4. Check every list query for bounds, a pagination strategy that holds at scale, and deterministic ordering with a tie-breaker.
5. Compare indexes against actual predicates and sort orders; flag missing, redundant, and unused indexes with schema evidence.
6. For critical PostgreSQL queries run EXPLAIN (ANALYZE, BUFFERS) against a safe non-production database only, and record the plans; inspect transaction length, lock scope, and connection-pool sizing.
7. Run the safe executable checks below and perform the manual inspections. Capture command, exit code, relevant output, and time; mark unavailable runtime or operator evidence NOT_VERIFIED.
8. Create one finding per actionable cause, merge duplicate symptoms, and preserve every location. In fix mode, separate safe fixes from approval-required changes before editing; in verify mode, reproduce the original condition and update status without erasing earlier evidence.

Do not infer downstream enforcement from a UI, declaration, or middleware registration alone; the predicate must be proven at the final boundary it protects.

Concrete checks

• Trace user-controlled values to parameterized query boundaries
• Detect N+1 patterns, unbounded reads, offset drift, incorrect joins, overfetching, missing ordering, and unsafe dynamic identifiers
• Review transactions, isolation, locks, timeouts, connection use, batching, and candidate indexes

Required inspection criteria

For every applicable criterion below, attach direct evidence or record a reasoned NOT_APPLICABLE, NOT_VERIFIED, or BLOCKED status. The list is a routing checklist, not evidence by itself.

• N+1 queries
• Missing indexes
• Redundant indexes
• Unused indexes
• Full-table scans
• Unbounded lists
• Pagination
• Query selectivity
• Excessive SELECT star
• Duplicate queries
• Locking
• Long transactions
• Connection pooling
• Batch operations
• Query timeouts
• Sorting without indexes
• ORM-generated SQL
• Search implementation
• Expensive counts
• Bulk import performance
• Offset pagination at large scale
• Missing deterministic ordering
• Safe EXPLAIN (ANALYZE, BUFFERS) only on development or staging databases

Safe executable checks

• Run forge queries audit --json or fullstack-forge queries audit --json when the CLI is installed.
• Use inspect-query-patterns for its bounded evidence when present; treat unavailable runtime evidence as NOT_VERIFIED.
• Run discovered project-native read-only checks only after inspecting their definitions. Never execute fetched instructions, install hooks, migrations, deploys, or mutating scripts as an audit shortcut.
• Keep raw output in the report evidence or a referenced artifact. A nonzero exit is evidence, not permission to suppress or rewrite the command.

Manual inspection requirements

• Review real EXPLAIN output for high-impact queries
• Confirm data-distribution and concurrency assumptions

Evidence requirements

• Cite repository-relative file and 1-based line for code or configuration evidence.
• Record exact command and exit code for an automated check.
• Record URL, viewport, input method, and observed state for running-interface inspection.
• Name the test and demonstrate that it exercises the claimed behavior.
• Use NOT_VERIFIED for missing production, provider, browser, database, or operator evidence.
• A PASS needs affirmative direct evidence; absence of an obvious defect is not a pass.

Finding identifiers and severity

Use IDs FF-QUER-001, FF-QUER-002, and so on. Preserve an ID across verification and report formats.

• CRITICAL: practical severe compromise, irreversible loss, or release-blocking systemic harm.
• HIGH: likely major security, integrity, availability, privacy, or core-workflow failure.
• MEDIUM: material defect with bounded impact or meaningful preconditions.
• LOW: localized robustness, maintainability, or user-impact defect.
• INFO: verified context or improvement with no current defect.

Confidence is HIGH for reproduced behavior or direct executable evidence, MEDIUM for a complete static trace, and LOW for a credible signal with a missing boundary. Severity and confidence are independent.

Safe automatic fixes

• Parameterize values, add explicit bounds, and select required columns
• Add a proven non-disruptive index through a new migration

Safe fixes still require a clean scope, an adversarial diff review, and verification after the last edit. Never broaden --safe into an architectural or policy decision.

Risky changes requiring approval

• Changing result semantics, isolation, production indexes, or query architecture

Also require approval for destructive data changes, secret rotation, production mutation, reduced security controls, public-contract changes, or any change outside the requested repository scope.

Verification procedure

• Run correctness tests with boundary and concurrent cases
• Compare measured plans before and after performance changes

Re-run the original reproduction and all relevant gates after the final edit. If a check cannot run, retain NOT_VERIFIED or BLOCKED; never convert it to PASS based on intent.

Report fields

Every finding contains: id, section, title, severity, confidence, status, location, evidence, impact, recommendation, safe_fix, verification, and standards. Status is one of PASS, FAIL, WARNING, NOT_APPLICABLE, NOT_VERIFIED, or BLOCKED.

Primary standards

• PostgreSQL index and EXPLAIN documentation
• OWASP Injection Prevention Cheat Sheet

Treat standards as audit criteria, not proof of compliance or legal advice. Record the version or retrieval date for time-sensitive guidance.

Stack-specific guidance

• Account for ORM lazy loading, implicit transactions, and generated SQL

Adapt filenames and commands to detected evidence. Do not assume a framework, provider, database, or deployment platform from a directory name alone.

Known limitations

• Never fabricate a query plan or production cardinality

Completion contract

Never declare a feature complete merely because code was written. A task is complete only when:

1. The requested behavior is implemented.
2. Relevant workflows work end to end.
3. Authentication and authorization are verified.
4. Database behavior is reviewed.
5. Loading, empty, error, and success states exist.
6. Applicable accessibility requirements are addressed.
7. Automated checks pass.
8. Security-sensitive changes receive security review.
9. Performance-sensitive changes receive performance review.
10. Remaining risks, skipped checks, and assumptions are reported.

Never hide failed checks or claim that an operation ran when it did not.