sql-inventory-from-code

Collects and maintains a complete CSV-based SQL query inventory (SSOT) by extracting all SQL statements from a service application's source code. This skill's only deliverable is the inventory CSV. Use when extracting SQL from application source code, building a query inventory from the codebase, or preparing for SQL tuning from code.

SQL Inventory from Code

This skill defines how to build and maintain a complete SQL inventory by extracting SQL statements from a service application's source code.

The goal is to:

  • Find all SQL used in the application codebase
  • Centralize them into a structured CSV file
  • Make them traceable and future-proof

This skill's only deliverable is the inventory CSV. No index design, no query rewriting, no runtime capture.

This skill is code-only. It does not cover DB logs, APM, or runtime query capture. (DB logs and APM are out of scope; they may be used elsewhere for coverage verification but are not part of this skill.)

Core Principle

All SQL present in the application code must be registered in a single structured file:

templates/sql_catalog.csv

This inventory acts as the Single Source of Truth (SSOT) for:

  • Query visibility
  • Filtering/sorting/pagination pattern analysis
  • Preparing future tuning work

What Counts as “SQL in Code”

Include SQL found in:

  • Raw SQL strings (inline strings)
  • Repository / DAO layers
  • Query builder outputs (where the final SQL template exists in code)
  • Named queries / XML mappings
  • Stored procedure invocations (e.g., CALL proc_name(...))
  • Migration/seed scripts only if they run as part of the service runtime (otherwise mark as non-runtime)

Exclude (unless explicitly required):

  • One-off admin scripts not deployed with the service
  • Ad-hoc local/debug queries

How to Discover All Queries (Code Strategy)

1. Search Raw SQL Patterns

Search for common tokens:

  • SELECT, INSERT, UPDATE, DELETE
  • FROM, JOIN, WHERE, ORDER BY, GROUP BY

Recommended actions:

  • Grep/ripgrep for SQL keywords
  • Scan constant files where SQL templates are stored
  • Scan repository/mapper directories

Note:

  • If SQL is split across concatenated strings, reconstruct it into a canonical single statement for the inventory.

2. Identify ORM / Query Builder SQL Templates

Some frameworks generate SQL at runtime but still define templates/structures in code.

Capture:

  • Named query definitions
  • Mapper XML/DSL definitions
  • Query builder fragments when the final structure is deterministic

If a query is truly dynamic (structure changes based on runtime conditions), register:

  • The canonical base query
  • Each meaningful structural variant (separate rows)

3. Normalize and Deduplicate

Many queries differ only by parameter values or whitespace.

Normalize by:

  • Replacing literal values with placeholders
  • Standardizing whitespace
  • Converting into parameterized form (?, :name, $1, etc.)

Each structurally identical query should be registered once.

Inventory Rules

1. One Query Structure = One Row

Each distinct SQL structure must have one row.

If the same query is used in multiple modules/endpoints, keep a single row and record the primary usage.

2. Stable Query ID

  • Each query must have a unique query_id.
  • The query_id must never change.
  • Historical entries must not be deleted.

3. Required Metadata

Each row must include structured metadata:

  • Location (for traceability): file_path, function_or_method, line_range
  • Context: execution_context (e.g. api, batch, worker, cron, admin)
  • Query structure: main_table, joined_tables, where_columns, order_by_columns, pagination_type, has_or_condition, has_like, like_pattern_type, uses_function_on_column, full_scan_risk
  • Optional: sql_dialect or db_engine (e.g. MySQL, Postgres, SQLite) when relevant for downstream use

The purpose is to avoid repeatedly parsing raw SQL and to make each entry traceable back to code.

Inventory Creation Workflow

Step 1 — Collect

  • Extract SQL from the application codebase
  • Reconstruct concatenated SQL where needed
  • List all structural variants for dynamic queries

Step 2 — Normalize

  • Remove literal values
  • Convert to parameterized format
  • Standardize formatting
  • When SQL in code uses string placeholders or interpolated values (e.g. :id, ?, $1, template vars), infer they are bound parameters and normalize to one canonical form per structure.

Step 3 — Register in CSV

For each unique SQL structure:

  • Assign query_id
  • Fill metadata consistently
  • Escape SQL correctly

Step 4 — Validate (Code Coverage)

Before committing:

  • Ensure all repository/DAO/mapper locations were scanned
  • Ensure the most important endpoints/jobs are represented
  • Ensure dynamic query variants are included when they change structure

Metadata Interpretation Guidelines

Code Location (file_path, function_or_method, line_range)

  • file_path: Repository-relative path (e.g. src/repository/UserRepository.java)
  • function_or_method: Method or function name where the SQL is defined or invoked
  • line_range: Optional; e.g. 120-125 or single line 120

Fill these when the SQL is extracted from code so the inventory remains traceable.

Execution Context

  • api → HTTP/gRPC API handler
  • batch → batch job
  • worker → background worker / queue consumer
  • cron → scheduled job
  • admin → admin-only or internal tool

WHERE Columns

  • List only column names
  • No operators
  • Comma-separated format

Example:

"status,created_at"

ORDER BY Columns

  • Include direction (ASC/DESC)
  • Preserve order

Example:

"created_at DESC,id DESC"

LIKE Pattern Type

  • prefix → column LIKE 'abc%'
  • suffix → column LIKE '%abc'
  • full → column LIKE '%abc%'
  • none → no LIKE

Pagination Type

  • offset → LIMIT/OFFSET
  • keyset → cursor/keyset
  • none → no pagination

Full Scan Risk Estimation

  • high → leading wildcard LIKE, function on indexed column, weak predicates
  • medium → partial risk
  • low → index-aligned filtering and sorting

CSV Safety Rules

  • Always wrap SQL in double quotes.
  • Escape internal quotes as "".
  • Prefer single-line SQL.
  • Do not add markdown inside the CSV file.

Quality Checklist

For each new entry:

  • query_id is unique
  • SQL text is quoted correctly
  • Metadata matches actual SQL structure
  • file_path (and function_or_method, line_range when available) is filled for traceability
  • execution_context is set (api/batch/worker/cron/admin)
  • Query structure is deduplicated and normalized
  • Dynamic variants are represented when structure differs

This skill ensures a complete, structured SQL inventory extracted from application source code. Index and WHERE proposals are a separate, optional follow-up; for reference only, see references/sql_tuning_proposals_by_table.md (not part of this skill's deliverable).