Tools & AI

LLM Settings

Configure your LLM provider, model and behavior.

Connection

Endpoint and authentication used to contact the LLM provider.

LLM URI?

Example: http://host.docker.internal:11434/v1/ when running Ollama locally alongside the pgAssistant Docker image. Make sure to set OLLAMA_HOST=0.0.0.0:11434.

API Key

Connection test

Model

Select the model returned by the configured endpoint.

Model

SQL behavior

Guidelines used when pgAssistant asks the LLM to review SQL or table definitions.

SQL conventions URL

These guidelines are available as {{GUIDELINES}} in the naming conventions prompt.

RFC prompt

Template used to analyze table structures against RFCs and standards.

Prompt template?

# SQL Table Structure Validation Based on RFC & International Standards (PostgreSQL Compatible)

## **Task**  
You are an expert in **database design**, **SQL optimization**, and **data standards**. Your goal is to **validate the structure of a SQL table (DDL)** based on relevant **RFCs, international standards, and best practices**.

## **Instructions**  
1. **Analyze the given DDL statement** and verify whether it adheres to **established standards and best practices** in different domains, including:  
   - **Networking & Web** (RFCs for emails, domain names, and addresses)  
   - **Healthcare** (FHIR, HL7)  
   - **E-commerce & Invoicing** (UBL, UN/CEFACT, EDIFACT)  
   - **Finance & Payments** (ISO 20022, IBAN, BIC, SWIFT, PCI-DSS)  
   - **Geolocation** (ISO 3166 for country codes, ISO 6709 for geolocation)  
   - **Personal Data & Identity** (ISO 5218 for gender, ISO 27799 for health privacy, OIDC/SAML for identity management)  
   - **Date & Time** (ISO 8601)  
   - **Languages & Localization** (ISO 639 for language codes, ISO 4217 for currencies)  
   - **Database Best Practices** (Normalization, indexing, constraints)  
2. **Validate column types, sizes, constraints, and indexes**, ensuring compliance (if relevant)with:  
   - **RFC 5322** for **email addresses**  
   - **RFC 6350** for **names and addresses (vCard format)**  
   - **RFC 3696** for **name and domain validation**  
   - **FHIR (HL7 Fast Healthcare Interoperability Resources)** for **medical data**  
   - **UBL (Universal Business Language)** for **invoices and orders**  
   - **ISO 20022** for **financial transactions**  
   - **ISO 3166** for **country codes**  
   - **ISO 5218** for **gender classification**  
   - **ISO 4217** for **currency codes**  
   - **ISO 8601** for **date and time formats**  
   - **E.164** for **phone number formatting**  
3. **Propose improvements**:  
   - Adjust **column data types or sizes** if necessary  
   - Add missing **constraints** (e.g., `NOT NULL`, `UNIQUE`, `CHECK`)  
   - Optimize **indexing strategies** for better performance  
4. **Generate SQL `ALTER TABLE` statements**, ensuring **100% PostgreSQL compatibility**:  
   - **Use PostgreSQL syntax for altering column types** (`ALTER COLUMN ... SET DATA TYPE`)  
   - **Use `ADD CONSTRAINT ... CHECK(...)` for validations**  
   - **Use `CREATE INDEX` to improve search performance**  
5. **Provide justifications** for each recommended change based on relevant standards.

## **Here is the DDL to analyze**  
```sql
{{DDL}}
```

Must contain {{DDL}}. Leave empty or reset to use the original pgAssistant template.

Naming prompt

Template used to check SQL naming conventions on table definitions.

Prompt template?

Please review the following DDL using this SQL Naming Conventions Guide: {{GUIDELINES}}

Produce a single Markdown table with EXACTLY five columns:

1. **Current column name**
2. **Recommended column name** (per the guide)
3. **Reference** (paste the FULL URL to the exact section)
4. **SQL to rename** (PostgreSQL `ALTER TABLE ... RENAME COLUMN ...`)
5. **SQL to describe** (`COMMENT ON COLUMN ... IS ...`)

# Decision rules (STRICT — do not violate):
- **ZERO-CHANGE RULE:** If the recommended name is **identical** to the current name **after normalization** (trim spaces, remove surrounding quotes, and lowercase comparison), then:
  - Put **"—"** in column 4 (no ALTER).
  - Put **"—"** in column 5 unless you truly add/modify a description.
  - Add no other SQL for that column.
- **RENAME ONLY IF NEEDED:** Emit an `ALTER` **only** when the normalized recommended name **differs** from the normalized current name.
- **TABLE NAME ACCURACY:** Use the exact table name(s) found in the DDL; do not invent names.
- **QUOTES:** Preserve quotes in SQL when identifiers are quoted in the DDL.
- **POSTGRES VALIDITY:** All SQL must be valid PostgreSQL.
- **REFERENCES:** The Reference column must be a **direct link** (full URL) to the specific guideline you applied (not just the homepage).
- **NO EXTRA TEXT:** Output only the table. No prose before or after.

Now analyze the following DDL:

```sql
{{DDL}}
```

Must contain {{DDL}}. Use {{GUIDELINES}} if the prompt should include the SQL conventions URL.