CRUD Operation Test Matrix Generation Prompt

This prompt is built for backend testers and QA engineers who need to generate a comprehensive test matrix from a data model specification. The job-to-be-done is translating a schema definition—tables, columns, constraints, and relationships—into a structured set of test cases covering every Create, Read, Update, and Delete operation. The ideal user has a data model spec in hand (DDL, an entity-relationship diagram, or a structured API resource definition) and needs traceable, coverage-aware test cases that can be fed directly into a test management system or automation harness.

Use this prompt when you have a stable data model and need to verify that all CRUD operations behave correctly under valid inputs, constraint violations, and referential integrity scenarios. It is particularly valuable before a schema migration, after adding new entities, or when onboarding a new service that must prove its data layer is correct. The prompt is designed to catch missing operation scenarios—for example, if your spec defines a foreign key but no test validates cascading deletes, the output should flag that gap. It also checks for constraint violation coverage, ensuring you test not-null violations, unique constraint conflicts, and check constraint failures, not just the happy path.

Do not use this prompt when your data model is still in flux or when you need performance or concurrency test cases—those require separate playbooks focused on load profiles and isolation levels. This prompt also assumes you are testing against a single service's data layer; if you need end-to-end tests spanning multiple services with eventual consistency, combine this output with an integration test playbook. Before shipping the generated test matrix, review any flagged coverage gaps manually and ensure the expected error codes and messages match your actual API or database driver behavior.

This prompt template is designed to take a data model specification—such as a DDL snippet, an ORM model definition, or a structured schema document—and produce a comprehensive test matrix covering Create, Read, Update, and Delete operations. The matrix includes valid operations, constraint violations, referential integrity checks, and cascade behavior scenarios. Use this template as the core instruction set in your test generation pipeline, replacing the square-bracket placeholders with concrete inputs before each run.

textCopy
You are a backend test architect. Given a data model specification, generate a CRUD operation test matrix.

## INPUT
[SCHEMA_DEFINITION]

## ADDITIONAL CONTEXT
- Business rules: [BUSINESS_RULES]
- Known edge cases: [EDGE_CASES]
- Risk level: [RISK_LEVEL]

## OUTPUT SCHEMA
Return a JSON object with the following structure:
{
  "entities": [
    {
      "entity_name": "string",
      "operations": {
        "create": [
          {
            "test_id": "string",
            "scenario": "string",
            "input_data": {},
            "expected_result": "success | constraint_error | integrity_error",
            "expected_error": "string | null",
            "preconditions": ["string"],
            "postconditions": ["string"],
            "tags": ["string"]
          }
        ],
        "read": [ ... ],
        "update": [ ... ],
        "delete": [ ... ]
      },
      "referential_integrity_tests": [
        {
          "test_id": "string",
          "relationship": "string",
          "scenario": "string",
          "action": "string",
          "expected_cascade_behavior": "string",
          "expected_result": "success | integrity_error",
          "preconditions": ["string"]
        }
      ]
    }
  ],
  "coverage_summary": {
    "total_tests": 0,
    "valid_operations_covered": 0,
    "constraint_violations_covered": 0,
    "referential_integrity_scenarios_covered": 0,
    "missing_scenarios": ["string"]
  }
}

## CONSTRAINTS
- Generate at least one test case for each CRUD operation per entity.
- For each column with a constraint (NOT NULL, UNIQUE, CHECK, FOREIGN KEY), generate at least one violation test.
- For each foreign key relationship, generate tests for ON DELETE and ON UPDATE cascade behavior.
- Include boundary tests for data types (e.g., max length for VARCHAR, range limits for INTEGER).
- Flag any operation type that is missing from the matrix in `coverage_summary.missing_scenarios`.
- Use deterministic, unique test IDs prefixed with the entity name (e.g., `USR-CREATE-001`).

## EXAMPLES
[FEW_SHOT_EXAMPLES]

## INSTRUCTIONS
1. Parse the schema to identify all entities, columns, constraints, and relationships.
2. For each entity, generate the four operation blocks.
3. For each constraint, generate at least one violation scenario.
4. For each foreign key, generate referential integrity tests covering cascade and restrict behaviors.
5. Populate the coverage summary with counts and flag any gaps.
6. Return ONLY the JSON object. No markdown fences, no commentary.

To adapt this template, replace [SCHEMA_DEFINITION] with your DDL, ORM model, or structured schema document. Populate [BUSINESS_RULES] with any domain-specific constraints not captured in the schema (e.g., "a user must have at least one active role"). Use [EDGE_CASES] to inject known problem scenarios from past incidents. Set [RISK_LEVEL] to low, medium, or high to influence the density of negative tests. The [FEW_SHOT_EXAMPLES] placeholder should contain one or two complete entity test matrices that demonstrate your expected quality bar—this is critical for output consistency across runs. Before integrating this prompt into a pipeline, validate the output JSON against the schema using a lightweight validator and reject any response that omits required fields or contains malformed test IDs.

This prompt is designed to be called programmatically as part of a test case generation service. The primary input is a data model specification—typically a DDL schema, an ORM model definition, or a structured API resource description. The harness should extract entity definitions, field constraints (types, nullability, uniqueness, defaults), and relationship metadata (foreign keys, cascade rules) before populating the [DATA_MODEL_SPEC] placeholder. Do not pass raw, unparsed documentation; the model needs structured field-level detail to produce a complete matrix.

Wrap the prompt call in a function that validates the output against a strict JSON schema. The expected output is an array of test case objects, each containing operation (CREATE, READ, UPDATE, DELETE), scenario, preconditions, input, expected_result, constraints_tested, and cascade_behavior fields. After generation, run a coverage validator that checks for missing operation types, unexercised constraints (e.g., a NOT NULL column with no violation test), and absent cascade scenarios for every foreign key relationship. If coverage gaps are detected, re-invoke the prompt with a targeted [CONSTRAINTS] block listing the missing items. Implement a maximum of two retry attempts before flagging the output for human review.

For production use, log every generation request with the model version, input spec hash, coverage scores, and retry count. Store the validated test matrix in your test management system (e.g., TestRail, Xray, or a custom database) with traceability back to the source data model version. If the data model includes tables storing PII or financial data, route the generated test cases through a human QA lead for approval before they enter the executable test suite. Avoid using this prompt for schemas with more than 30 entities in a single call—split large models into bounded contexts and merge the results to stay within token limits and maintain output quality.