# WebDevRock

> A working knowledge base of SQL Server data-fix scripts, integration and operations patterns, browser tools, and hands-on AI guides, shaped by real constraints: messy data, limited schema control, and the need to be reliable.

Every page listed here is available as clean Markdown at the linked .md URL. The full content of the site in one file is at https://webdevrock.uk/llms-full.txt.

## Patterns

Higher-level engineering approaches: integration pipelines, data fixes, audit trails, and operational design.

- [Changing Large Volumes of Production Data Without an Outage](https://webdevrock.uk/patterns/large-data-changes-without-outage.md): Planning backfills and clean-ups that touch millions of rows while the system stays live.
- [Designing a Reversible Data Fix in SQL Server](https://webdevrock.uk/patterns/sql-reversible-data-fix.md): A repeatable approach for production-safe updates with verification and rollback.
- [Designing Audit Trails That Help Investigation](https://webdevrock.uk/patterns/audit-trails-for-investigation.md): What to record about data changes, and how to store it so you can answer what happened when something goes wrong.
- [Designing Integration Jobs That Are Safe to Rerun](https://webdevrock.uk/patterns/integration-jobs-safe-to-rerun.md): How to make jobs produce the same result whether they run once, twice, or restart halfway through.
- [Designing Integration Pipelines That Fail Safely](https://webdevrock.uk/patterns/integration-pipelines-fail-safely.md): A practical approach to building integration pipelines that prevent bad data from reaching downstream systems.
- [Retry, Back Off, Give Up: Handling Unreliable External APIs](https://webdevrock.uk/patterns/handling-unreliable-external-apis.md): A system-level policy for which failures to retry, how long to keep trying, and what happens when retries run out.
- [Stopping Duplicates at the Source Instead of Cleaning Them Up](https://webdevrock.uk/patterns/stop-duplicates-at-source.md): Where duplicate records come from, and how to design imports, constraints, and matching so they cannot be created.
- [Why Status Tables Fail Operations Teams](https://webdevrock.uk/patterns/status-tables-fail-operations.md): Why simple status fields rarely communicate system state clearly, and how staged models improve operational clarity.

## Snippets

Small copy/paste solutions with short explanations, mostly SQL Server.

- [SQL Server: Bulk Update with JOIN](https://webdevrock.uk/snippets/sql-bulk-update-join.md): Update many rows from a supplied list of IDs + values using a temp table and JOIN. Ideal for data fixes.
- [SQL Server: Convert Between XML and JSON](https://webdevrock.uk/snippets/sql-xml-json-convert.md): Turn XML into JSON with nodes() and FOR JSON, and JSON into XML with OPENJSON and FOR XML PATH, keeping nested rows nested.
- [SQL Server: Find and Remove Duplicate Rows with ROW_NUMBER()](https://webdevrock.uk/snippets/sql-remove-duplicates-row-number.md): Preview duplicate rows, keep one per group, and delete the rest safely.
- [SQL Server: Pass a List of IDs Without Dynamic SQL](https://webdevrock.uk/snippets/sql-pass-id-list.md): Turn a comma-separated or JSON list into rows you can JOIN to, with no string concatenation or SQL injection risk.
- [SQL Server: Query Large XML Documents Without Timeouts](https://webdevrock.uk/snippets/sql-query-large-xml.md): Why shredding a big XML file with nodes() can take minutes, and the fixes that bring it back to seconds: no parent axis, full paths, text(), shredding once.
- [SQL Server: Record Changed Rows with OUTPUT INTO](https://webdevrock.uk/snippets/sql-output-into-audit.md): Capture before and after values of an UPDATE or DELETE in the same statement, so any data fix can be undone.
- [SQL Server: Safe Bulk DELETE Using JOIN](https://webdevrock.uk/snippets/sql-safe-bulk-delete-join.md): Delete rows using a JOIN with a preview step to avoid accidental data loss.
- [SQL Server: Turn XML Into Rows with nodes()](https://webdevrock.uk/snippets/sql-xml-to-rows.md): Shred an XML document into rows and typed columns with nodes() and value(), including child rows, attributes and namespaces.
- [SQL Server: Update Only When Values Change](https://webdevrock.uk/snippets/sql-update-only-when-changed.md): Avoid unnecessary writes and locking by updating rows only when values differ.
- [SQL Server: Update or Delete Millions of Rows in Batches](https://webdevrock.uk/snippets/sql-batch-update-delete.md): Change large volumes of data in small batches to avoid table locks, log growth and blocking.
- [SQL Server: UPSERT Without MERGE](https://webdevrock.uk/snippets/sql-upsert-without-merge.md): Insert new rows and update existing rows safely without using MERGE.
- [TypeScript: Retry fetch with Exponential Backoff and Jitter](https://webdevrock.uk/snippets/ts-fetch-retry-backoff.md): A small typed fetch wrapper that retries timeouts, rate limits and server errors without hammering the API.

## Tools

Browser-based utilities for SQL Server and integration work.

- [Foreign Key Diagram from SQL Server](https://webdevrock.uk/tools/foreign-key-diagram.md): Paste the results of a query against sys.foreign_keys and get a diagram of your tables and how they relate, with every column if you want them. Export as SVG, PNG or Mermaid.
- [JSON to OPENJSON Generator](https://webdevrock.uk/tools/json-to-openjson.md): Paste a sample JSON payload and get a SQL Server OPENJSON query with column types inferred from the data.
- [List to SQL Converter](https://webdevrock.uk/tools/list-to-sql.md): Turn a pasted list of ids, or ids and new values, into a #temp table, an IN list, or JSON for OPENJSON.
- [Retry Backoff Schedule Calculator](https://webdevrock.uk/tools/retry-schedule.md): See when each retry happens for an exponential backoff policy, and how long a call can take before it gives up.
- [Reversible Data Fix Script Builder](https://webdevrock.uk/tools/data-fix-script.md): Generate a SQL Server data fix script for an update or a delete, with a preview, backup table, verification, and a ready-made rollback or restore.
- [Timestamp Converter for Integrations](https://webdevrock.uk/tools/timestamp-converter.md): Convert between Unix timestamps, ISO 8601, .NET ticks, Microsoft JSON dates, HTTP dates, and SQL Server literals.
- [XML to JSON Converter](https://webdevrock.uk/tools/xml-json-converter.md): Convert XML to JSON and JSON back to XML, with attributes, repeated elements and namespaces handled the same way in both directions.
- [XML to nodes() Query Generator](https://webdevrock.uk/tools/xml-to-nodes.md): Paste a sample XML document and get a SQL Server nodes()/value() query, with column types inferred from the data and child rows shredded with CROSS APPLY.

## AI & LLMs

Practical guides on AI agents, local LLMs, and automation workflows.

- [AI Agents: What They Are and How to Use Them](https://webdevrock.uk/ai/what-are-ai-agents.md): Stop treating AI as a chatbot and start treating it as a system: what AI agents are, how they differ from LLMs, and workflows you can set up today.
- [Architecting Multi-Agent Systems](https://webdevrock.uk/ai/multi-agent-architecture.md): When one agent isn't enough. Routing, specialisation, coordination, and the patterns I've used to build systems like Hermes.
- [Building Your Own AI Agent System](https://webdevrock.uk/ai/building-agent-system.md): A practical walkthrough of setting up local LLMs, connecting tools, and running agents on a schedule, based on what I actually use rather than theory.
- [Letting an LLM Write SQL Against Production, Safely](https://webdevrock.uk/ai/llm-sql-production-safely.md): How to let an AI assistant or agent query a live SQL Server database without risking the data: a read-only login it can't escape, limits on what reads cost, and a reviewed, reversible path for changes.
- [LLM Productivity Workflows](https://webdevrock.uk/ai/llm-productivity-workflows.md): How I actually use LLMs day to day: email triage, content drafting, research synthesis, and decision support. Real patterns, not theoretical possibilities.

## Experiments

Visual and technical experiments.

- [Custom Shaders from First Principles: A Terrain Generator in Three.js](https://webdevrock.uk/experiments/terrain-shader.md): Build a 3D landscape in seven steps with your own GLSL shaders: a grid of vertices, noise, fractal hills, colour by height, lighting, water and fog.
- [Embeddings Explained: Explore How AI Represents Meaning in 3D](https://webdevrock.uk/experiments/embedding-space-explorer.md): An interactive 3D map of about 200 phrases embedded with a real model. See why similar meanings sit together, how semantic search finds answers without shared words, and where it goes wrong.
- [Flocking Algorithm (Boids)](https://webdevrock.uk/experiments/flocking-algorithm.md): Implementation of Craig Reynolds' Boids flocking simulation in Three.js.
- [How a Database Index Works: A B-Tree in 3D](https://webdevrock.uk/experiments/database-index-btree.md): Watch SQL Server find customers by ID and by surname, page by page. An interactive Three.js model of a table, an index, key lookups and page splits.
- [How SQL Server Joins Tables: Nested Loops, Hash and Merge, Side by Side](https://webdevrock.uk/experiments/sql-server-join-algorithms.md): Watch SQL Server's three join algorithms join the same rows at the same time, then see which one does the least work at real table sizes, and why.
- [How to Read a SQL Server Execution Plan, in 3D](https://webdevrock.uk/experiments/sql-execution-plan-3d.md): An interactive 3D execution plan viewer. See where a query's cost goes, why a scan, a key lookup or a bad estimate slows it down, or paste your own plan.
- [OutSystems REST API Implementation](https://webdevrock.uk/experiments/outsystems-rest-api.md): Implementation guide for REST API endpoints in OutSystems to track publish activity and application metrics.
- [Rate Limiting Algorithms Compared: Token Bucket, Leaky Bucket and Sliding Window](https://webdevrock.uk/experiments/rate-limiting-algorithms.md): Four rate limiters enforce the same limit on the same traffic, side by side in 3D. See why fixed windows let bursts through, how the two buckets differ, and how retries make it worse.
- [Retries, Backoff and Circuit Breakers, Visualised in Three.js](https://webdevrock.uk/experiments/integration-pipeline-particle-flow.md): An interactive Three.js simulation of an integration pipeline. Watch messages retry, back off, trip a circuit breaker and land in a needs-attention queue during an API outage.
- [Three.js: Force-Directed Node Graph](https://webdevrock.uk/experiments/threejs-node-graph.md): A 3D force-directed graph in Three.js. Nodes repel, connections act as springs, and the layout cools until it settles.