Quality: Provide "instructions.md"

[amotl]

About

Just emphasizing a backlog item.

Details

Provide an "instructions.md" file for enhanced guidance, like others are doing it?

For the best experience with the CrateDB MCP Server, it’s crucial to provide your
AI assistant with proper instructions on how to interact. [...] The CrateDB MCP Server
comes with a set of predefined instructions that you can copy from the instructions.md file.

Blueprint

• https://fluxcd.io/blog/2025/05/ai-assisted-gitops/
• https://fluxcd.control-plane.io/mcp/prompt-engineering/
• https://raw.githubusercontent.com/controlplaneio-fluxcd/distribution/refs/heads/main/docs/mcp/instructions.md

[amotl]

@hammerhead shared a good set of instructions per crate/cratedb-examples#989, see code cell at async def query_agent(question). Thanks!

While it includes some items about the specific use case at hand, most of it are general instructions that should be integrated into the package itself on behalf of a future iteration.

You are a friendly assistant who receives information from CrateDB.
Your task is to translate questions into SQL queries, run them on CrateDB, and return back results.
Try to generate SQL queries based on the known data model and don't ask questions back.

You have the following tools available:
1. `query_sql`: Executes SQL queries on CrateDB
2. `get_cratedb_documentation`: Returns the table of contents for the CrateDB documentation. If in doubt about CrateDB-specific syntax, you can obtain the documentation here.
3. `fetch_cratedb_docs`: Once a specific link within the CrateDB documentation is identified, you can download its content here by providing the link.
4. `get_table_metadata`: This returns all metadata for tables in CrateDB.

Try to reason and give an interpretation of the result.

When asked about manuals, query the `manual` column of the `machine_manuals` table to retrieve the manual. Interpret its content to provide an answer.

Rules for writing SQL queries:
  - To retrieve the latest value for a column, use CrateDB's `MAX_BY` function.
  - When using date intervals, always include both the quantity and the unit in a string, e.g. INTERVAL '7 days'.
  - Don't use DATE_SUB, it does not exist in CrateDB. Use DATE_TRUNC instead.

[amotl]

Sometimes, the model hallucinates about Elasticsearch. Here, @WalBeh asked model XY about how to create snapshots including the global cluster state. The LLM comes up with an SQL statement, but it includes options from Elasticsearch, which CrateDB does not provide.

-- Before making major cluster changes
CREATE SNAPSHOT my_repo.pre_migration_state
WITH (
    include_global_state = true,
    wait_for_completion = true
);

[WalBeh]

pg_size_pretty

Q: Asking about shards...

SELECT 
    node['name'] as node_name,
    COUNT(*) as shard_count,
    pg_size_pretty(SUM(translog_stats['size'])) as total_translog_size,
    pg_size_pretty(SUM(translog_stats['uncommitted_size'])) as total_uncommitted_size,
    SUM(translog_stats['number_of_operations']) as total_operations,
    SUM(translog_stats['uncommitted_operations']) as total_uncommitted_operations
FROM sys.shards
WHERE schema_name NOT IN ('information_schema', 'sys', 'pg_catalog')
GROUP BY node['name']
ORDER BY SUM(translog_stats['size']) DESC;

I ask to: please fix the query: UnsupportedFunctionException[Unknown function: pg_size_pretty(sys.shards.translog_stats['size'])] and that worked.

[WalBeh]

Query nested data (MongoDB-like)

Q: Can I store JSON documents like in MongoDB?

@> 'electronics'; is not suppored

SELECT 
  document['name'] AS product_name,
  document['specs']['cpu'] AS cpu,
  document['price'] AS price
FROM products
WHERE document['tags'] @> 'electronics';

[WalBeh]

Monitoring query for shard distribution is wrong (column names):

This was arround the question: How do I apply sharding properly? Which overall looks satisfying, but needs a closer look.

SELECT table_name, node_id, shard_id, state
FROM sys.shards
WHERE table_schema = 'doc'
ORDER BY table_name, shard_id;

[amotl]

Sometimes, the LLM is overly ambitious to present timestamps in a human readable format. Because CrateDB might use a special function, the LLM often fails on the first attempt.

Upgrades (hoy)

Zeit	Von Version	Zu Version	Status	Operation ID
06:33:28	5.9.13 → 5.xx.xx	✅ SUCCEEDED	18a5dff6-7a7c-4d46-98d4-91a69fb38496
06:04:08	5.8.7 → 5.xx.xx	✅ SUCCEEDED	0c2bfba6-b9ea-40da-a8d3-32209bf80b0f
05:41:18	5.7.6 → 5.xx.xxx	✅ SUCCEEDED	f62fb979-d6bb-419e-98eb-54186d584102
05:17:18	5.7.1 → 5.7xxxx	✅ SUCCEEDED	55bad90a-f002-49e4-8ee6-7c522a2d85c8

timestamps are resolved by llm not in the query

Operation Type	Status	Cluster Name	Project Name	Timestamp	Datum/Zeit
PASSWORD_UPDATE	SUCCEEDED	xxx-jinn	xxx-ixxxnn	1752765746223	Jul 17, 2025 - 07:35:46
PASSWORD_UPDATE	SUCCEEDED	xxxx-nxxxxxn	xx	1752764954328	Jul 17, 2025 - 07:22:34
CREATE	SUCCEEDED	xxxxx	xxxxy	1752764499231	Jul 17, 2025 - 07:14:59
ALLOWED_CIDR_UPDATE	SUCCEEDED	xxxx	xxxxx	1752763593691	Jul 17, 2025 - 07:00:33
ALLOWED_CIDR_UPDATE	SUCCEEDED	xxxx	xxxx	1752763480241	Jul 17, 2025 - 06:58:00

confused about timestamp conversion to date

You're absolutely right to question this! Let me double-check those timestamps more carefully. Looking at the Unix timestamps I provided:

1752743608 (18a5dff6) - Jul 17, 2025 06:33:28
1752741848 (0c2bfba6) - Jul 17, 2025 06:04:08
1752740478 (f62fb979) - Jul 17, 2025 05:41:18
1752739038 (55bad90a) - Jul 17, 2025 05:17:18

[amotl]

@WalBeh conceived an instructions prompt which improved the situation significantly. Thanks!

Make yourself familiar with all the available features of the cratedb-mcp server. Make sure to understand that cratedb is not Elasticsearch. In doubt whether a feature is supported or not consult the cratedb documentation or look at the cratedb source code github repository crate/crate. Make yourself deeply familiar with the concepts, functions cratedb offers. For SQL statements also consult the cratedb documentation, especially in regards of supported datatypes and functions. CrateDB is not Postgres although the protocol itself is wire compatible. For accessing data you could also use the cratedb sqlalchemy driver - if applicable

[amotl]

@WalBeh shared another bunch of refined instructions -- thanks!

You are a CrateDB database engineer, focused on technical level and optimization abilities.

Key guidelines:

• Use all available tools from the CrateDB MCP server for accurate information
• Remember: CrateDB is NOT Elasticsearch - they are different systems
• CrateDB is PostgreSQL wire compatible but NOT PostgreSQL - important differences exist
• Always consult the CrateDB documentation for supported features and syntax
• For architectural questions, refer to CrateDB-specific documentation and best practices
• For SQL queries, use CrateDB-specific functions and syntax
• Examine the CrateDB source code when needed for deep technical insights
• Focus on performance optimization and proper CrateDB usage patterns
• Provide high-quality, technically accurate responses based on actual CrateDB capabilities

-- marvin_cratedb_mcp » config.yaml

[amotl]

The instructions for the Mintlify technical writing assistant aren't too bad either.

[amotl]

Those patches are aiming to make a difference.

• Prompt: Add instructions about working with CrateDB about#52
• Prompt: Add general and MCP tool instructions to system prompt of MCP server #53

[amotl]

Sometimes you want GPT to be aware of certain coding guidelines, like whether to provide type hints, which libraries or packages to prefer, etc.

Similar like adding user-specified custom coding conventions, in the domain of Text-to-SQL, users may want to add individual guidelines about how to use their specific table/column schema, in order to educate about relevant quirks or other obstacles that otherwise could cause hiccups and multiple iterations.