> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Demo Prompt Anatomy and Best Practices
> Guide to refactoring a casting number lookup app to ingest Chevrolet small‑block CSVs, update models, handle messy CSVs, migrations, and import utilities
This walkthrough takes a deeper look at the Casting Number Lookup project and focuses on prompt engineering techniques, context injection, and using an assistant (Cline) to refactor an application to a new data model. Goal: convert casting numbers (stamped on automotive parts) into structured metadata so you can decide whether to buy a part from a junkyard.
Keywords: casting number lookup, Chevrolet small-block, SQLAlchemy refactor, CSV import, data model migration, FastAPI, Pydantic v2.
We’ll cover:
* The original project and its initial data model
* A real Chevrolet small‑block casting CSV (target dataset)
* Crafting prompts to refactor the app to the new schema
* Running the migration and handling errors
* Validating the refactored API
## Project background and original model
The original SQLAlchemy model used generic fields (material, weight, manufacturer, etc.). That general shape doesn’t match the Chevrolet small‑block casting CSV we want to ingest, which uses fields like Years, Casting, CID, factory power ratings, and notes about main caps or comments.
Original SQLAlchemy model:
```python theme={null}
# app/models/casting.py (original)
from sqlalchemy import Column, Integer, String, Float, Text
from app.db.database import Base
class Casting(Base):
"""SQLAlchemy model for casting data."""
__tablename__ = "castings"
id = Column(Integer, primary_key=True, index=True)
casting_number = Column(Integer, unique=True, index=True, nullable=False)
name = Column(String, index=True)
description = Column(Text, nullable=True)
material = Column(String, nullable=True)
weight = Column(Float, nullable=True)
dimensions = Column(String, nullable=True)
manufacturer = Column(String, nullable=True)
year_introduced = Column(Integer, nullable=True)
```
## Why this matters
Chevrolet casting datasets are organized around production years, casting IDs, displacement (CID), factory power ranges, main cap counts, and free‑form comments. For salvage or parts selection, these attributes are more important than generic material/weight fields. For example, casting `330817` corresponds to engines built 1973–1980 with two‑bolt mains and use in cars and trucks—details that determine interchangeability.
## Example: sample generic CSV (incorrect for this domain)
Below is the simple CSV Cline initially inferred from the original model—note it’s the wrong shape for Chevrolet casting information:
```csv theme={null}
# sample_generic_castings.csv
casting_number,name,description,material,weight,dimensions,manufacturer,year_introduced
1001,"Engine Block","V8 engine block for high-performance applications","Cast Iron",75.5,"24x18x12","ABC Castings",1985
1002,"Cylinder Head","4-cylinder head with improved cooling channels","Aluminum",12.3,"18x12x4","XYZ Manufacturing",1990
1003,"Crankshaft","Forged steel crankshaft for durability","Steel",28.7,"36x6x6","Precision Parts Inc.",1988
1004,"Connecting Rod","High-strength connecting rod for performance engines","Forged Steel",1.2,"8x2x1","Performance Engineering",1992
1005,"Piston","Lightweight piston with thermal barrier coating","Aluminum Alloy",0.8,"4x4x5","Advanced Components",1995
1006,"Camshaft","Performance camshaft with hardened lobes","Hardened Steel",5.6,"24x2x2","Valve Train Specialists",1987
1007,"Intake Manifold","Flow-optimized intake manifold","Aluminum",8.9,"18x12x8","Flow Masters",1993
1008,"Exhaust Manifold","High-flow exhaust manifold","Cast Iron",12.4,"16x10x6","Exhaust Systems Co.",1986
1009,"Valve Cover","Decorative valve cover with improved sealing","Aluminum",3.2,"16x8x3","Custom Components",1991
1010,"Oil Pan","Deep sump oil pan for increased capacity","Stamped Steel",6.7,"20x14x8","Lubrication Systems",1989
```
## Target dataset: Real Chevrolet casting CSV
This is the actual Chevy small‑block CSV format we want to adopt. Notice fields oriented to engine specs and application context:
```csv theme={null}
# chev-casting.csv (excerpt)
Years,Casting,CID,Low Power,High Power,Main Caps,Comments
1980-85,332817,400,150,180,2,cars
1973-80,331817,400,150,180,2,"car, truck"
1975,355909,262,110,110,2,"car, truck"
1976-85,355909,305,,-,2,A
1975,360851,262,,-,2,Monza
1976-79,361979,305,,-,2,"car, truck"
1978-86,366245,350,,-,,-,car
1982-86,366286,350,,-,4,"Chevrolet, siamese"
1982,366299,350,,-,4,"Chevrolet, aluminium"
1956-67,383810,283,,-,2,-
1967-68,389257,302,,-,2,Z-28
1968-73,391436,307,,-,2,-
1965-67,393288,283,,-,2,"car, truck"
1976-79,460776,305,,-,2,"car, truck"
1976-79,460777,305,,-,2,car
1978-79,460778,305,,-,2,"car, truck"
1979-82,471511,267,,-,2,"car, truck"
1976-85,581671,305,,-,2,A
1985-94,1489363,350,,-,2,-
1979-82,2135412,267,,-,2,-
1973-76,3030817,400,,-,2,"car, truck"
1958-61,3556519,283,,-,2,"car, truck"
1955,3703524,265,195,,-,2,"car, no filter"
```
## Mapping the change: old model → new schema
Use this table to anchor the refactor mapping between the original generic model and the Chevrolet casting schema:
| Original field | New Chevrolet field | Purpose |
| ---------------------------------: | ------------------------- | ----------------------------------------------------------------------- |
| `casting_number` | `casting` | Unique casting identifier (string to allow long IDs) |
| `year_introduced` | `years` | Production years or ranges (e.g., `1973-80`) |
| `name`, `description` | `comments` | Merged into free‑form comments where applicable |
| `material`, `weight`, `dimensions` | N/A | Not used for the Chevrolet dataset; drop or store in comments if needed |
| (new) `cid` | `cid` | Displacement in cubic inches |
| (new) `low_power` / `high_power` | `low_power`, `high_power` | Factory horsepower ranges |
| (new) `main_caps` | `main_caps` | Number of main caps (2, 4, or dash) |
## Prompt design principles used in this lesson
1. Intention first, not syntax first — describe what you want (the refactored schema and behavior) rather than low‑level implementation steps.
2. Anchor with context — provide example rows and the target schema so the assistant can reliably infer field mappings.
3. One‑shot vs iterative prompts — show a one‑shot example and demonstrate a Plan → Act workflow for large refactors to reduce risk.
## Crafting the refactor prompt
Context files provided to the assistant:
* `chev-casting.csv` (target data)
* example CSV(s) and current codebase snapshot
Explicit asks:
* Refactor the application to ingest the Chevrolet schema.
* Update models, Pydantic schemas, import utility, CRUD functions, and migrations.
* Provide a migration plan (alter in place or drop/recreate + reimport).
* Show example mappings: a current row and a desired target row.
## Messy input example (real CSV pitfalls)
Real CSVs can have inconsistent quoting, missing headers, or unnamed columns. The import utility must be defensive. Example of messy rows:
```csv theme={null}
# messy_sample_rows.csv (illustrative only)
7,1006,"Camshaft","Performance camshaft with hardened lobes","Hardened Steel",1987
8,1007,"Intake Manifold","Flow-optimized intake manifold","Aluminum"
9,1008,"Exhaust Manifold","High-flow exhaust manifold","Cast Iron",
10,1009,"Valve Cover","Decorative valve cover with improved sealing",1991
11,1010,"Oil Pan","Deep sump oil pan for increased capacity","Stamped",1989
```
## Refactor plan and the assistant’s plan-mode output
The assistant’s plan-mode outline included:
* Inventory of the current application: data model, DB, API endpoints, import utility.
* New requirements: `years`, `casting`, `cid`, `low_power`, `high_power`, `main_caps`, `comments`.
* Files to update: SQLAlchemy models, Pydantic schemas, import utilities, CRUDs, migration scripts.
* Migration approach: either alter existing tables or drop and recreate followed by a reimport.
## Applying the plan (Act mode) and migration issues
In Act mode the assistant implemented changes—updated SQLAlchemy model, Pydantic schemas, created a defensive import utility and attempted a migration. During migration a CSV parsing issue (an unnamed column) caused a model instantiation error:
Example migration trace (cleaned):
```bash theme={null}
(venv) jeremy@MACSTUDIO CastingLookup % python migrate_database.py --skip-drop
Traceback (most recent call last):
File ".../sqlalchemy/orm/decl_base.py", line 2142, in _declare
raise TypeError(...)
TypeError: 'Unnamed: 7' is an invalid keyword argument for Casting
```
Sound remediation steps:
* Inspect and drop unnamed or empty CSV columns before creating model instances.
* Add a cleaning step in the import utility to remove columns with no header.
* If schema drift is significant, consider resetting the DB and reimporting (only in development or with backups in production).
## Improved model (refactored)
The refactored SQLAlchemy model now mirrors the Chevrolet CSV:
```python theme={null}
# app/models/casting.py (refactored)
from sqlalchemy import Column, Integer, String, Text
from app.db.database import Base
class Casting(Base):
"""SQLAlchemy model for Chevrolet casting data."""
__tablename__ = "castings"
id = Column(Integer, primary_key=True, index=True)
years = Column(String, index=True, nullable=True)
casting = Column(String, unique=True, index=True, nullable=False)
cid = Column(Integer, nullable=True)
low_power = Column(String, nullable=True)
high_power = Column(String, nullable=True)
main_caps = Column(String, nullable=True)
comments = Column(Text, nullable=True)
```
## Pydantic schemas and API changes
Schemas were updated to match the new fields and to use Pydantic v2 ergonomics:
```python theme={null}
# app/schemas/casting.py
from pydantic import BaseModel
class CastingBase(BaseModel):
years: str | None = None
casting: str
cid: int | None = None
low_power: str | None = None
high_power: str | None = None
main_caps: str | None = None
comments: str | None = None
class CastingInDB(CastingBase):
id: int
model_config = {"from_attributes": True}
class Casting(CastingInDB):
pass
```
Note: In Pydantic v2 the previous `orm_mode` setting is replaced by `from_attributes`. The assistant updated schemas accordingly to ensure FastAPI can return ORM models as Pydantic objects.
## Import utility and cleaning strategy
Make the CSV import robust:
* Use pandas to read CSV robustly (`pd.read_csv(..., dtype=str)`).
* Drop columns where header names start with `Unnamed`.
* Normalize column names (lowercase, strip whitespace) and map them to expected fields.
* Coerce numeric fields (`cid`) with safe casting and `NaN` handling.
* Handle duplicates via skip or upsert logic based on `casting` uniqueness.
A recommended import flow:
1. Load CSV as strings to avoid silent numeric coercion.
2. Drop unnamed/empty headers.
3. Rename columns to the expected schema.
4. Validate rows and coerce types.
5. Insert with upsert/skip duplicate semantics.
## Reset and re-import script
Because in-place ALTER migrations failed in this iteration, a reset script was provided to drop and recreate tables and reimport the cleaned Chevrolet CSV. This is useful for development and iterative testing—do not run in production without backups.
Example reset/import script skeleton:
```python theme={null}
#!/usr/bin/env python3
# reset_db.py (generated by assistant)
"""
Script to reset the database and import the Chevrolet casting data.
This script will:
1. Drop the existing database
2. Create the new tables
3. Import the data from chev-casting.csv
"""
import os
import sys
import pandas as pd
# Add the current directory to the path to allow imports
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
from app.db.database import engine, SessionLocal
from app.models import casting as casting_models
from app.models.casting import Casting as CastingModel
from app.utils.import_data import clean_data
def import_data(file_path, db):
"""
Import data from CSV file, handling duplicates by skipping them.
"""
df = pd.read_csv(file_path, dtype=str)
# Drop unnamed columns produced by malformed CSVs
df = df.loc[:, ~df.columns.str.startswith("Unnamed")]
for _, row in df.iterrows():
record = clean_data(row.to_dict())
# upsert/skip logic here
```
## Note on virtual environments and dependencies
Make sure your virtual environment is activated before running installs or migrations. If a script requires newer packages, update them inside the venv:
```bash theme={null}
pip install --upgrade pandas numpy
```
## Running the app and validating the API
After refactor and import, the FastAPI app exposes Swagger UI at `/docs`. Common endpoints:
* GET /api/castings/{casting}
* GET /api/castings?skip=...\&limit=...
* POST /api/castings
Example curl request for casting 366299:
```bash theme={null}
curl -X 'GET' \
'http://0.0.0.0:8000/api/castings/366299' \
-H 'accept: application/json'
```
Example successful response (200 OK):
```json theme={null}
{
"casting": "366299",
"years": "1982",
"cid": 350,
"low_power": null,
"high_power": null,
"main_caps": "4",
"comments": "Chevrolet, aluminium",
"id": 8
}
```
## API docs screenshot
Here’s the API documentation (Swagger / OpenAPI) showing the GET /api/castings endpoint and the interactive "Try it out" control:
## Troubleshooting and iteration
When migrations fail because of unnamed CSV columns or UNIQUE constraint conflicts:
* Drop unnamed columns produced by malformed CSVs.
* Add a cleaning pass in the import utility to normalize headers and drop empties.
* Use Plan → Act workflow: validate the assistant’s plan before applying large structural changes.
* For repeated in-place migration failures, use a reset + reimport workflow in development and keep backups for production.
## Screenshot: diagnosing UNIQUE/CSV issues
The assistant diagnosed a UNIQUE constraint / CSV header problem and the developer fixed it in the CSV/editor:
## Plan vs Act workflow
Recommended workflow for large codebase changes:
* Plan mode: ask the assistant to produce a refactoring plan (files to change, mapping, migration approach) and review the plan.
* Act mode: have the assistant apply changes, update files, run migrations, import data, and start the server.
* Iterate: address issues uncovered during Act mode (CSV quirks, duplicates, type errors), update the plan, and re-run.
## Key takeaways
* Start prompts with intent — describe the desired result, not every implementation detail.
* Anchor the assistant with example inputs and the target output schema to reduce ambiguity.
* When changing a data model, update models, schemas, import utilities, migrations, and API endpoints consistently.
* Add robust CSV cleaning steps (drop unnamed columns, normalize headers) to handle messy real‑world data.
* Use Plan mode to review the assistant’s proposed changes, then switch to Act mode to apply them.
* Validate the API with Swagger or curl and test edge cases (duplicate rows, malformed CSVs).
## Next steps and extension ideas
Consider extending the application with:
* Fuzzy matching on casting numbers (tolerant search)
* Mobile-friendly web UI for quick lookups in junkyards
* Batch import with validation reports and error summaries
* Caching layers for frequently queried castings
## References
* SQLAlchemy: [https://www.sqlalchemy.org/](https://www.sqlalchemy.org/)
* Pandas: [https://pandas.pydata.org/](https://pandas.pydata.org/)
* FastAPI: [https://fastapi.tiangolo.com/](https://fastapi.tiangolo.com/)
* Pydantic v2: [https://docs.pydantic.dev/latest/](https://docs.pydantic.dev/latest/)