Spaces:

garvit-010
/

enterprise-text-to-sql

Sleeping

enterprise-text-to-sql / prompt_module.py

Deploy Enterprise Text-to-SQL Space — garvit-010

12593e3 verified 26 days ago

4.2 kB

"""Prompt v2: v1 plus the conventions the failure analysis showed were missing.

Phase 15.

v1 (`src/model/prompt.py`) is deliberately plain and stays frozen — the base
and fine-tuned v1 numbers are measured against it. v2 adds three things the
model cannot infer from the schema alone:

* **A reference date.** Questions like "in the last 6 months" need a "now".
  The benchmark anchors every such question to ``DATA_AS_OF`` rather than
  ``NOW()`` so answers do not change overnight; v1 never told the model that,
  so it either used NOW() or guessed at an ``is_active`` flag.
* **A business glossary.** What "revenue", "spend", "net revenue", "paid" and
  "awaiting payment" mean in this database. Every one of these is a rule the
  training templates already follow; they were simply unstated. A production
  system carries the same thing as a semantic layer.
* **Output conventions.** Whole row when no columns are named; months as
  ``DATE_TRUNC`` buckets; PostgreSQL date arithmetic; entities by name.

Kept import-free on purpose, like ``repair_prompt.py``: this file is shipped
verbatim to the GPU host inside the eval pack and rendered there, so it must
not depend on anything else in ``src``. ``tests/test_prompt_v2.py`` asserts
the literal date below matches ``src.constants.DATA_AS_OF``.
"""

from __future__ import annotations

import hashlib

PROMPT_VERSION = "v2"

# Must equal src.constants.DATA_AS_OF_SQL. Literal here so the module stays
# import-free; the test suite enforces the equality.
REFERENCE_DATE_SQL = "TIMESTAMPTZ '2026-08-01 00:00:00+00:00'"

SYSTEM_PROMPT = f"""You are an expert PostgreSQL analyst. You convert business \
questions into correct, executable PostgreSQL queries.

Rules:
- Output ONLY the SQL query. No explanation, no commentary.
- Use only the tables and columns given in the schema.
- Write a single SELECT statement. Never modify data.
- Use PostgreSQL syntax.
- When a foreign key is nullable, consider whether LEFT JOIN is needed to \
avoid silently dropping rows.

Reference date:
- "Today" for this database is {REFERENCE_DATE_SQL}. Use that literal for \
"now", "today", "this year", "last N days/months" and similar. Never use NOW() \
or CURRENT_DATE.

Business definitions:
- Order value, amount spent, and revenue by customer, country, sales rep, \
department, segment or time period = SUM(orders.total_amount).
- Revenue by product, category or supplier = SUM(order_items.quantity * \
order_items.unit_price * (1 - order_items.discount_pct / 100)).
- Net or actual revenue excludes orders whose status is 'cancelled' or \
'returned'.
- A payment counts only when payments.status = 'completed'. An order is \
awaiting payment when it is not cancelled and has no completed payment.
- A shipment is delivered when delivered_date IS NOT NULL; in transit when \
shipped_date IS NOT NULL and delivered_date IS NULL.

Output conventions:
- If the question asks to show or list records without naming columns, \
return every column of that table (SELECT *).
- Identify customers, products, suppliers, warehouses, categories and \
departments by their name column when that table is in the query; employees \
by first_name and last_name.
- Group by calendar month with DATE_TRUNC('month', <date>) and by year with \
EXTRACT(YEAR FROM <date>).
- Days between two timestamps: EXTRACT(EPOCH FROM (later - earlier)) / 86400.0."""

USER_TEMPLATE = """Database schema:

{schema}

Question: {question}

PostgreSQL query:"""


def build_messages(question: str, schema: str) -> list[dict[str, str]]:
    """Same shape as v1: system + user, ``/no_think`` appended."""
    return [
        {"role": "system", "content": SYSTEM_PROMPT},
        {
            "role": "user",
            "content": USER_TEMPLATE.format(schema=schema, question=question)
                       + " /no_think",
        },
    ]


def prompt_fingerprint() -> str:
    """Hash of the template text, recorded alongside results."""
    combined = SYSTEM_PROMPT + "\x00" + USER_TEMPLATE + "\x00" + PROMPT_VERSION
    return hashlib.sha256(combined.encode("utf-8")).hexdigest()[:16]

	"""Prompt v2: v1 plus the conventions the failure analysis showed were missing.

	Phase 15.

	v1 (`src/model/prompt.py`) is deliberately plain and stays frozen — the base
	and fine-tuned v1 numbers are measured against it. v2 adds three things the
	model cannot infer from the schema alone:

	* A reference date. Questions like "in the last 6 months" need a "now".
	The benchmark anchors every such question to ``DATA_AS_OF`` rather than
	``NOW()`` so answers do not change overnight; v1 never told the model that,
	so it either used NOW() or guessed at an ``is_active`` flag.
	* A business glossary. What "revenue", "spend", "net revenue", "paid" and
	"awaiting payment" mean in this database. Every one of these is a rule the
	training templates already follow; they were simply unstated. A production
	system carries the same thing as a semantic layer.
	* Output conventions. Whole row when no columns are named; months as
	``DATE_TRUNC`` buckets; PostgreSQL date arithmetic; entities by name.

	Kept import-free on purpose, like ``repair_prompt.py``: this file is shipped
	verbatim to the GPU host inside the eval pack and rendered there, so it must
	not depend on anything else in ``src``. ``tests/test_prompt_v2.py`` asserts
	the literal date below matches ``src.constants.DATA_AS_OF``.
	"""

	from __future__ import annotations

	import hashlib

	PROMPT_VERSION = "v2"

	# Must equal src.constants.DATA_AS_OF_SQL. Literal here so the module stays
	# import-free; the test suite enforces the equality.
	REFERENCE_DATE_SQL = "TIMESTAMPTZ '2026-08-01 00:00:00+00:00'"

	SYSTEM_PROMPT = f"""You are an expert PostgreSQL analyst. You convert business \
	questions into correct, executable PostgreSQL queries.

	Rules:
	- Output ONLY the SQL query. No explanation, no commentary.
	- Use only the tables and columns given in the schema.
	- Write a single SELECT statement. Never modify data.
	- Use PostgreSQL syntax.
	- When a foreign key is nullable, consider whether LEFT JOIN is needed to \
	avoid silently dropping rows.

	Reference date:
	- "Today" for this database is {REFERENCE_DATE_SQL}. Use that literal for \
	"now", "today", "this year", "last N days/months" and similar. Never use NOW() \
	or CURRENT_DATE.

	Business definitions:
	- Order value, amount spent, and revenue by customer, country, sales rep, \
	department, segment or time period = SUM(orders.total_amount).
	- Revenue by product, category or supplier = SUM(order_items.quantity * \
	order_items.unit_price * (1 - order_items.discount_pct / 100)).
	- Net or actual revenue excludes orders whose status is 'cancelled' or \
	'returned'.
	- A payment counts only when payments.status = 'completed'. An order is \
	awaiting payment when it is not cancelled and has no completed payment.
	- A shipment is delivered when delivered_date IS NOT NULL; in transit when \
	shipped_date IS NOT NULL and delivered_date IS NULL.

	Output conventions:
	- If the question asks to show or list records without naming columns, \
	return every column of that table (SELECT *).
	- Identify customers, products, suppliers, warehouses, categories and \
	departments by their name column when that table is in the query; employees \
	by first_name and last_name.
	- Group by calendar month with DATE_TRUNC('month', <date>) and by year with \
	EXTRACT(YEAR FROM <date>).
	- Days between two timestamps: EXTRACT(EPOCH FROM (later - earlier)) / 86400.0."""

	USER_TEMPLATE = """Database schema:

	{schema}

	Question: {question}

	PostgreSQL query:"""


	def build_messages(question: str, schema: str) -> list[dict[str, str]]:
	"""Same shape as v1: system + user, ``/no_think`` appended."""
	return [
	{"role": "system", "content": SYSTEM_PROMPT},
	{
	"role": "user",
	"content": USER_TEMPLATE.format(schema=schema, question=question)
	+ " /no_think",
	},
	]


	def prompt_fingerprint() -> str:
	"""Hash of the template text, recorded alongside results."""
	combined = SYSTEM_PROMPT + "\x00" + USER_TEMPLATE + "\x00" + PROMPT_VERSION
	return hashlib.sha256(combined.encode("utf-8")).hexdigest()[:16]