Thinking in CRUD: Designing a Clean and Intent-Driven Plan Management API

Backend engineering is not about writing endpoints quickly, it is about designing predictable, safe, and maintainable systems.

Before a single line of code is written, a good backend engineer can clearly explain:

• what an endpoint does,
• why it exists,
• what could go wrong,
• and how those risks are handled.

One of the simplest but most powerful mental models for this is CRUD:
Create, Read, Update, Delete.

In this article, we walk through a Plan Management API, explaining each CRUD operation using:

• deep, step-by-step thinking logic
• code samples that directly reflect that thinking

This approach ensures that code mirrors reasoning, not shortcuts.

---

1. CREATE — Creating a Plan

Creating a plan is foundational. Other parts of the system (subscriptions, billing, permissions) will rely on it, so correctness matters.

Detailed Thinking Logic

1. Clarify the responsibility of the endpoint
   This endpoint must only create a new plan — never update or duplicate existing ones.

2. Identify required inputs
   Decide which fields are mandatory for a valid plan (e.g. name, price, duration).

3. Extract inputs from the request body
   The backend should trust nothing except what is explicitly provided.

4. Validate presence of required fields
   Missing required data should stop execution immediately.

5. Validate business rules
   Values must make sense (price > 0, duration > 0).

6. Check for duplicates
   Prevent creating multiple plans with the same unique identifier.

7. Prepare data for persistence
   Ensure the data matches the database structure.

8. Persist the plan
   Insert into the database safely.

9. Confirm successful creation
   Ensure the database operation succeeded.

10. Return a clear success response
    Use HTTP 201 Created.

11. Handle unexpected failures gracefully

Code Sample (CREATE)

exports.createPlan = async (req, res) => {
    /*
    Thinking logic:
    - Extract input
    - Validate presence
    - Validate business rules
    - Prevent duplicates
    - Persist data
    - Respond clearly
    */

    try {
        // 1. Extract expected inputs from request body
        // We only trust data explicitly sent by the client
        const { name, price, duration } = req.body

        // 2. Validate presence of required fields
        // If any required field is missing, stop early
        if (!name || price == null || duration == null) {
            return res.status(400).json({
                message: "Required fields: name, price, duration"
            })
        }

        // 3. Validate business rules
        // Even valid-looking data must still make logical sense
        if (price <= 0 || duration <= 0) {
            return res.status(400).json({
                message: "Price and duration must be greater than zero"
            })
        }

        // 4. Check for duplicates
        // Prevent creation of multiple plans with the same name
        const existing = await query(
            "SELECT id FROM plans WHERE name = ?",
            [name]
        )

        if (existing.length > 0) {
            return res.status(409).json({
                message: "Plan already exists"
            })
        }

        // 5. Persist the new plan
        // At this point, all validations have passed
        const result = await query(
            "INSERT INTO plans (name, price, duration) VALUES (?, ?, ?)",
            [name, price, duration]
        )

        // 6. Return success response
        // Use 201 to indicate a new resource was created
        return res.status(201).json({
            message: "Plan created successfully",
            planId: result.insertId
        })

    } catch (error) {
        // 7. Catch unexpected runtime or database errors
        return res.status(500).json({
            message: "Failed to create plan"
        })
    }
}

---

2. READ (Single) — Viewing One Plan

Reading a single plan is about accuracy and trust.

Detailed Thinking Logic

1. Clarify intent
   Fetch exactly one plan using a unique identifier.

2. Extract identifier from request parameters

3. Validate identifier presence

4. Query the database

5. Check query results

6. Return the plan if found

7. Return 404 Not Found if missing

8. Handle unexpected errors

Code Sample (READ SINGLE)

exports.viewPlan = async (req, res) => {
    /*
    Thinking logic:
    - Validate identifier
    - Fetch resource
    - Handle absence
    - Respond clearly
    */

    try {
        // 1. Extract plan identifier from URL parameters
        const { id } = req.params

        // 2. Validate identifier
        // Without an ID, the request is meaningless
        if (!id) {
            return res.status(400).json({
                message: "Plan ID is required"
            })
        }

        // 3. Query database for the specific plan
        const plans = await query(
            "SELECT * FROM plans WHERE id = ?",
            [id]
        )

        // 4. Handle case where plan does not exist
        if (plans.length === 0) {
            return res.status(404).json({
                message: "Plan not found"
            })
        }

        // 5. Return the found plan
        return res.status(200).json(plans[0])

    } catch (error) {
        // 6. Handle unexpected errors
        return res.status(500).json({
            message: "Failed to fetch plan"
        })
    }
}

---

3. READ (List) — Viewing Multiple Plans

Listing resources introduces performance and scalability concerns.

Detailed Thinking Logic

1. Define listing responsibility

2. Extract pagination parameters

3. Normalize and validate values

4. Calculate offset

5. Fetch controlled dataset

6. Handle empty results gracefully

7. Return structured response with metadata

8. Handle unexpected failures

Code Sample (READ LIST)

exports.viewPlans = async (req, res) => {
    /*
    Thinking logic:
    - Normalize pagination
    - Fetch controlled dataset
    - Return structured response
    */

    try {
        // 1. Extract pagination parameters from query string
        // Defaults are applied to ensure predictable behavior
        const page = Math.max(parseInt(req.query.page) || 1, 1)
        const limit = Math.max(parseInt(req.query.limit) || 10, 1)

        // 2. Convert page-based pagination into SQL offset
        const offset = (page - 1) * limit

        // 3. Fetch a controlled subset of plans
        // This avoids returning too much data at once
        const plans = await query(
            "SELECT * FROM plans LIMIT ? OFFSET ?",
            [limit, offset]
        )

        // 4. Return structured response
        // Metadata helps clients handle pagination properly
        return res.status(200).json({
            page,
            limit,
            count: plans.length,
            data: plans
        })

    } catch (error) {
        // 5. Handle unexpected failures
        return res.status(500).json({
            message: "Failed to fetch plans"
        })
    }
}

---

4. UPDATE — Updating a Plan

Updating is risky because it modifies existing truth.

Detailed Thinking Logic

1. Clarify update responsibility

2. Extract identifier and payload

3. Validate identifier

4. Validate payload presence

5. Validate business rules

6. Confirm resource exists

7. Restrict updatable fields

8. Build dynamic update query

9. Execute update

10. Confirm success

11. Return confirmation

12. Handle failures

Code Sample (UPDATE)

exports.updatePlan = async (req, res) => {

    /*
    Thinking logic:
    - Identify resource
    - Validate inputs
    - Confirm existence
    - Apply controlled updates
    */

    try {
        // 1. Extract plan identifier
        const { id } = req.params

        // 2. Extract fields that may be updated
        const { name, price, duration } = req.body

        // 3. Validate identifier presence
        if (!id) {
            return res.status(400).json({
                message: "Plan ID is required"
            })
        }

        // 4. Ensure at least one field is provided for update
        if (!name && price == null && duration == null) {
            return res.status(400).json({
                message: "At least one field must be provided for update"
            })
        }

        // 5. Validate business rules for updated fields
        if (price != null && price <= 0) {
            return res.status(400).json({
                message: "Price must be greater than zero"
            })
        }

        if (duration != null && duration <= 0) {
            return res.status(400).json({
                message: "Duration must be greater than zero"
            })
        }

        // 6. Confirm the plan exists before updating
        const existing = await query(
            "SELECT id FROM plans WHERE id = ?",
            [id]
        )

        if (existing.length === 0) {
            return res.status(404).json({
                message: "Plan not found"
            })
        }

        // 7. Build update query dynamically
        // Only fields provided by the client are updated
        const fields = []
        const values = []

        if (name) {
            fields.push("name = ?")
            values.push(name)
        }

        if (price != null) {
            fields.push("price = ?")
            values.push(price)
        }

        if (duration != null) {
            fields.push("duration = ?")
            values.push(duration)
        }

        // 8. Append identifier for WHERE clause
        values.push(id)

        // 9. Execute update
        await query(
            `UPDATE plans SET ${fields.join(", ")} WHERE id = ?`,
            values
        )

        // 10. Return confirmation response
        return res.status(200).json({
            message: "Plan updated successfully"
        })

    } catch (error) {
        // 11. Handle unexpected errors
        return res.status(500).json({
            message: "Failed to update plan"
        })
    }
}

---

5. DELETE — Deleting a Plan

Deletion should be deliberate and safe.

Detailed Thinking Logic

1. Understand deletion impact

2. Prefer soft delete

3. Extract identifier

4. Validate identifier

5. Confirm resource exists

6. Prevent duplicate deletion

7. Perform deletion

8. Confirm success

9. Return confirmation

10. Handle failures

Code Sample (DELETE)

exports.deletePlan = async (req, res) => {
    /*
    Thinking logic:
    - Confirm existence
    - Prevent unsafe deletes
    - Perform soft deletion
    */

    try {
        // 1. Extract plan identifier
        const { id } = req.params

        // 2. Validate identifier
        if (!id) {
            return res.status(400).json({
                message: "Plan ID is required"
            })
        }

        // 3. Fetch plan to confirm existence and status
        const plans = await query(
            "SELECT id, is_active FROM plans WHERE id = ?",
            [id]
        )

        // 4. Handle non-existent plan
        if (plans.length === 0) {
            return res.status(404).json({
                message: "Plan not found"
            })
        }

        // 5. Prevent repeated deletion
        if (plans[0].is_active === 0) {
            return res.status(400).json({
                message: "Plan is already inactive"
            })
        }

        // 6. Perform soft delete
        // Soft deletes are safer for billing and audit purposes
        await query(
            "UPDATE plans SET is_active = 0 WHERE id = ?",
            [id]
        )

        // 7. Return confirmation response
        return res.status(200).json({
            message: "Plan deleted successfully"
        })

    } catch (error) {
        // 8. Handle unexpected failures
        return res.status(500).json({
            message: "Failed to delete plan"
        })
    }
}

---

Conclusion: Why This Approach Matters

This is intent-driven backend engineering.

By:

• thinking before coding,
• writing logic that explains itself,
• and mapping reasoning directly into code,

you build APIs that are:

• easier to debug,
• safer to extend,
• and clearer to teammates.

CRUD is not just about operations — it is about discipline, clarity, and responsibility.

If someone can read your controller and understand why every line exists, you are doing backend engineering right.