jabrena/plinth / skills/704-technologies-sql
704-technologies-sql
VerifiedUse when you need framework-agnostic SQL guidance — schema naming, relational table design, query readability, indexes, transactions, database security, migrations, testing, and monitoring — without choosing Spring Boot, Quarkus, or Micronaut. This should trigger for requests such as Review SQL schema or migrations; Improve SQL query performance and readability; Design relational tables and indexes; Review database transaction, security, or monitoring practices. Part of Plinth Toolkit
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 3.9 KB |
| references/704-technologies-sql.md | markdown | 7.9 KB |
references/704-technologies-sql.md
View on GitHub---
name: 704-technologies-sql
description: Use when you need framework-agnostic SQL guidance — schema naming, relational table design, query readability, indexes, transactions, database security, migrations, testing, and monitoring — without choosing Spring Boot, Quarkus, or Micronaut.
license: Apache-2.0
metadata:
author: Juan Antonio Breña Moral
version: 0.19.0
---
# SQL best practices
## Role
You are a senior database engineer with deep experience in relational schema design, SQL performance, secure data access, migrations, and Java application persistence
## Goal
Apply **framework-agnostic** SQL practices so schemas, migrations, and queries are clear, safe, performant, and maintainable across Java projects.
1. **Keep SQL understandable**: use consistent names, readable formatting, explicit columns, clear aliases, and documented dialect assumptions.
2. **Protect data integrity**: model primary keys, foreign keys, unique constraints, check constraints, nullability, timestamps, and deletion semantics deliberately.
3. **Design for performance**: review execution plans, index predicates and joins, avoid over-indexing, batch bulk work, and keep transactions short.
4. **Secure the database layer**: require bind parameters, least-privilege roles, encrypted transport, sensitive-data controls, backups, and auditability.
5. **Ship schema changes safely**: use versioned migrations, non-production testing, rollback or forward-fix plans, and production-aware deployment sequencing.
Defer framework-specific data-access patterns to JDBC, Spring Data JDBC, Quarkus Panache, Micronaut Data, or migration-tool skills when implementation details move out of SQL itself.
## Constraints
Before recommending Java or build changes alongside SQL work, ensure the workspace builds. Treat unknown dialect and production data-safety constraints as design inputs, not afterthoughts.
- **MANDATORY**: Run `./mvnw compile` or `mvn compile` before changing Java or build descriptors in the same change set
- **SCOPE**: Stay within SQL, schema design, migrations, and database operations unless the user asks for framework-specific Java implementation
- **SAFETY**: Do not propose destructive migration steps without backup, rollback/forward-fix, locking, and data-retention considerations
- **PORTABILITY**: Mark dialect-specific syntax explicitly, especially identity columns, enum types, upserts, generated columns, JSON, full-text search, partitioning, and stored procedures
## Examples
### Table of contents
- Example 1: Clear schema naming and table design
- Example 2: Readable and safe queries
- Example 3: Index for real access patterns
- Example 4: Production-aware migrations
### Example 1: Clear schema naming and table design
Title: snake_case, constraints, timestamps, and dialect-aware identity columns
Description: Prefer consistent database-object names: `snake_case`; plural table names when that is the project convention; singular, descriptive column names; and explicit prefixes for constraints and indexes (`pk_`, `fk_`, `uq_`, `ck_`, `idx_`). Include audit columns where the domain needs them and model deletion semantics intentionally.
**Good example:**
```sql
CREATE TABLE customers (
id BIGINT GENERATED BY DEFAULT AS IDENTITY,
email VARCHAR(255) NOT NULL,
status VARCHAR(32) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
CONSTRAINT pk_customers PRIMARY KEY (id),
CONSTRAINT uq_customers_email UNIQUE (email),
CONSTRAINT ck_customers_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED'))
);
CREATE INDEX idx_customers_status_created_at
ON customers (status, created_at);
```
**Bad example:**
```sql
CREATE TABLE Customer (
ID INT PRIMARY KEY,
email VARCHAR(255),
status VARCHAR(20),
created TIMESTAMP
);
-- Inconsistent names, weak nullability, no named constraints, no domain checks.
```
### Example 2: Readable and safe queries
Title: explicit columns, meaningful aliases, bind parameters, and plan-aware predicates
Description: Use uppercase SQL keywords, one major clause per line, explicit column lists, stable aliases, and bind parameters. Review execution plans for high-volume queries and ensure indexes support the actual predicates, joins, grouping, and ordering.
**Good example:**
```sql
SELECT
c.id,
c.email,
o.id AS order_id,
o.created_at AS order_created_at
FROM customers c
JOIN orders o
ON o.customer_id = c.id
WHERE c.status = ?
AND o.created_at >= ?
AND c.deleted_at IS NULL
ORDER BY o.created_at DESC;
```
**Bad example:**
```sql
SELECT * FROM customers c, orders o
WHERE c.id = o.customer_id
AND c.status = 'ACTIVE';
-- Avoid SELECT *, implicit joins, and hard-coded values where application input should be bound.
```
### Example 3: Index for real access patterns
Title: foreign keys, filters, sorts, composite order, and write overhead
Description: Index foreign-key columns and frequent predicates. For composite indexes, order columns by equality filters first, then range and sort columns when that matches the database optimizer. Avoid duplicating indexes that only add write overhead.
**Good example:**
```sql
CREATE INDEX idx_orders_customer_id_created_at
ON orders (customer_id, created_at DESC);
-- Supports:
-- WHERE customer_id = ?
-- ORDER BY created_at DESC
```
**Bad example:**
```sql
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_customer_created_duplicate ON orders (customer_id, created_at);
-- Avoid redundant indexes without checking the query plan and existing usage.
```
### Example 4: Production-aware migrations
Title: small, versioned, tested, and reversible or forward-fixable
Description: Version every schema change with Flyway, Liquibase, or the project's migration tool. Prefer additive, backwards-compatible migrations for rolling deployments. Separate large data backfills from DDL when locks or runtime are uncertain.
**Good example:**
```sql
-- V20260603_1200__add_customer_status.sql
ALTER TABLE customers
ADD COLUMN status VARCHAR(32);
UPDATE customers
SET status = 'ACTIVE'
WHERE status IS NULL;
ALTER TABLE customers
ALTER COLUMN status SET NOT NULL;
ALTER TABLE customers
ADD CONSTRAINT ck_customers_status
CHECK (status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED'));
```
**Bad example:**
```sql
ALTER TABLE customers DROP COLUMN old_status;
ALTER TABLE customers ADD COLUMN status VARCHAR(32) NOT NULL;
-- Risky for production: destructive, no backfill, no compatibility window.
```
## Output Format
- **REVIEW** schema, migrations, and queries against naming, integrity, security, performance, and operational safety
- **IDENTIFY** the SQL dialect and mark portability-sensitive syntax or assumptions
- **LIST** concrete issues: missing constraints, unsafe dynamic SQL, under-indexed joins, redundant indexes, long transactions, risky migrations, and missing tests
- **PROPOSE** SQL snippets or migration steps that are directly applicable and production-aware
- **VALIDATE** with appropriate checks: build, migration validation, database tests, execution plans, and representative data volumes where available
- **DEFER** Java framework wiring to the matching persistence or migration skill when the fix belongs in application code
## Safeguards
- **INJECTION SAFETY**: Never build SQL by concatenating untrusted input; use bind parameters through the application's data-access API
- **DATA SAFETY**: Treat destructive DDL, mass updates, and long locks as high-risk changes requiring explicit migration strategy
- **PERFORMANCE EVIDENCE**: Prefer execution plans, database metrics, and representative data over guesses about query speed
- **LEAST PRIVILEGE**: Application database users should have only the privileges required for runtime behavior; migrations should use controlled elevated privilegesRelated skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| rea-analysis-optimizationmorluto/reaOptimize REA providers and shared analysis workflows for agent context, latency, CPU, and memory while preserving correctness and Evidence. Use for analysis bottlenecks and cache or query design; CI/test-suite optimization belongs to rea-ci-tests-audit. | Stars: 470k | Updated | Claude CodeCodex | Verified | Install |
| rea-ci-tests-auditmorluto/reaAudit REA CI and test suites for correctness, fixture drift, false passes, and measured performance; use for suite or workflow investigations and efficiency changes, rather than ordinary feature testing or a single test failure. | Stars: 470k | Updated | Claude CodeCodex | Verified | Install |
| rea-tool-designmorluto/reaAudit, investigate, design, or change REA investigation tools, CLI/MCP contracts, provider capabilities, and Evidence semantics. | Stars: 470k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-auditDietrichGebert/ponytailQuality audit of the whole repo: bugs, security, real load, missing tests, speed, and what to delete. Most important first. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |