Laravel Database ExpertSAFE

A collection of Agent Skills Standard and Best Practice for Programming Languages, Frameworks that help our AI Agent follow best practies on frameworks and programming laguages

The instruction file, verbatim from the audited commit — this is the text the model reads, and the surface the audit's instruction layer examines. Quoted here so you can judge it without cloning anything.

---
name: laravel-database-expert
description: 'Optimize Laravel queries with subqueries, joinSub, Redis cache-aside patterns, and read/write connection splitting. Use when writing complex joins, implementing Cache::remember with tags, or configuring database read replicas.'
metadata:
  triggers:
    files:
    - 'config/database.php'
    - 'database/migrations/*.php'
    keywords:
    - join
    - aggregate
    - subquery
    - selectRaw
    - Cache
---
# Laravel Database Expert

## **Priority: P1 (HIGH)**

## Workflow: Optimize Slow Query

1. **Profile query** — Use `DB::enableQueryLog()` or Laravel Debugbar.
2. **Add missing indexes** — Create migration for join/where columns.
3. **Replace N+1** — Use `withCount()`, `withSum()`, or `addSelect` subqueries.
4. **Cache results** — Apply `Cache::remember()` with tags for frequently accessed data.
5. **Split reads/writes** — Configure `read`/`write` keys in `config/database.php`.

## Cache-Aside with Tags Example

See [implementation examples](references/implementation.md#cache-aside-with-tags) for cache-aside pattern with tag-based invalidation.

## Implementation Guidelines

### Advanced Query Builder

- **Complex Joins**: Prefer **`joinSub($subquery, 'alias', ...)`** and **`whereExists(fn($q) => $q->select(DB::raw(1))...)`** over raw SQL or `whereIn` for correlated subqueries.
- **Subqueries**: Use **`addSelect`** with **`DB::raw`** subquery to avoid N+1 issues.
- **Aggregates**: Use **`withCount()`**, **`withSum()`**, and **`withAvg()`** directly via Eloquent for optimized column-based aggregation.
- **Raw Expressions**: Always use **`selectRaw`** or **`whereRaw`** with bindings; **never use string concatenation** in raw queries.

### Caching Strategy (Redis/Memcached)

- **Cache-Aside**: Utilize **`Cache::remember('key', $ttl, $closure)`** for frequently accessed data (e.g., `posts.all`).
- **Redis Tagging**: Group related keys using **`Cache::tags(['posts', 'user:1'])`** for **grouped invalidation**.
- **Invalidation**: Call **`Cache::tags(['posts'])->flush()`** to clear specific subsets; **never use `Cache::flush()` globally** in production.

### Scalability & Infrastructure

- **Read/Write Splitting**: Configure **'read'** and **'write'** keys in **`config/database.php`** mysql/pgsql connections. Laravel automatically routes **SELECT** to read and **INSERT/UPDATE/DELETE** to write; **no code changes needed**.
- **Indices**: Ensure correct **database indexes** present on all join and aggregate columns.

## Anti-Patterns

- **No string SQL concatenation**: Use bindings or Query Builder.
- **No queries in loops**: Use subqueries, joins, or aggregates.
- **No `Cache::flush()`**: Use tags to target specific cache groups.
- **No direct Redis calls**: Use Laravel Cache wrappers consistently.

## References

- [Advanced SQL & Cache Patterns](references/implementation.md)

## Canonical response anchors

When this skill applies, preserve the following domain terminology or equivalent concrete examples in the answer when relevant:
- INSERT/UPDATE/DELETE
- correlated subqueries
- grouped invalidation
- no code changes needed
- posts.all

- Additional task-grounded exact anchors: Cache::remember; withAvg

SAFEgrade B · trust 89/100 Nothing in the source contradicts what it says it does. Grade A is reserved for packages that have also passed the behavioural sandbox.

What the source does

Filesystem

none-observed

Network

none-observed

Shell

none-observed

Dependencies

pinned

Secrets in source

none-found

Findings (0)

No findings outside the package's declared scope.

Gates applied: no_behavioural_pass.