API Service Component

The API Service component is where you add custom business logic to your system design. While optional for simple CRUD operations, it becomes essential when you need validation, data transformation, or complex processing before database operations.

Overview

Think of the API Service as middleware between your User Request and Database. It intercepts requests, runs your custom Python code, and can modify, validate, or even reject operations before they reach the database.

Whether you're documenting production systems, preparing for interviews, or learning system design patterns, the API Service component lets you implement and test business logic interactively.

Real-World Example

In a URL shortener like bit.ly:

1. User sends POST request with a long URL
2. API Service generates a short code, validates the URL, checks for duplicates
3. Database stores both the short code and long URL
4. User can later retrieve the long URL using the short code

Without API Service, you'd just store whatever data comes in without any processing.

When to Use API Service

Use API Service when you need:

• ✅ Data validation (email format, required fields, etc.)
• ✅ Data transformation (hashing passwords, generating IDs)
• ✅ Custom IDs (short URLs, user-friendly slugs)
• ✅ Business logic (checking inventory, calculating prices)
• ✅ Duplicate detection (checking existing records)
• ✅ Error handling (custom validation messages)
• ✅ Batch operations (inserting/updating multiple records at once)

Skip API Service if you only need:

• ❌ Simple CRUD operations
• ❌ Direct pass-through from request to database
• ❌ No data processing or validation

Component Features

Description Field

Add a description to document what your API Service does:

"Validates email format and checks for duplicate users before registration"
"Generates short URL codes and stores mappings"
"Processes payment and updates order status"

This helps you and others understand your system design at a glance.

Custom Code Editor

Click "Edit Code" or "Add Custom Code" to open a full-featured code editor with:

• Syntax highlighting for Python
• Line numbers
• Dark/light mode support

The process_request Function

Every API Service must define a process_request function. This is your entry point where all logic begins.

Function Signature

def process_request(input_data):
    """
    Process the incoming request with access to existing database records

    Args:
        input_data (dict): Request data containing:
            - method: HTTP method (e.g., "POST", "GET", "PUT", "DELETE")
            - endpoint: API endpoint (e.g., "users", "urls")
            - data: Request body data (e.g., {"email": "user@example.com"})
            - all_records: DICT of all records keyed by table name (e.g., {'users': [...], 'rate_limits': [...]})

    Returns:
        dict: Response object with operation, table, columns, and data
    """

Input Data Structure

The input_data parameter contains:

{
    "method": "POST",           # HTTP method from User Request
    "endpoint": "users",        # Endpoint name (becomes table name)
    "data": {                   # Request body from User Request properties
        "name": "John Doe",
        "email": "john@example.com"
    },
    "all_records": {            # DICT: All records across all tables in the session
        "users": [              # Records from the 'users' table
            {
                "record_id": "abc123",
                "name": "Jane Smith",
                "email": "jane@example.com",
                "created_at": "2024-01-15T10:00:00Z"
            }
        ],
        "rate_limits": [ ... ]  # Records from another table
    }
}

Accessing Input Data:

method = input_data.get('method')              # "POST", "GET", "PUT", "DELETE"
endpoint = input_data.get('endpoint')          # "users", "posts", etc.
data = input_data.get('data', {})             # Request body
# Access records for the specific table corresponding to the current endpoint
current_table_records = input_data.get('all_records', {}).get(endpoint, [])

Return Structure

Your function must return a dictionary with these keys:

{
    "operation": "INSERT",      # Required: INSERT, UPDATE, SELECT, DELETE, BATCH_INSERT, BATCH_UPDATE, or NONE
    "table": "users",          # Optional: defaults to endpoint if not provided
    "columns": [               # Required for INSERT/UPDATE/BATCH_INSERT/BATCH_UPDATE
        {"name": "record_id", "type": "TEXT"},     # Optional: for custom IDs
        {"name": "name", "type": "TEXT"},
        {"name": "email", "type": "TEXT"},
        {"name": "age", "type": "INTEGER"}
    ],
    "data": {                  # Required for INSERT/UPDATE (dict for single record)
        "record_id": "custom_id_123",  # Optional: custom ID
        "name": "John Doe",
        "email": "john@example.com",
        "age": 27
    }
    # OR for BATCH_INSERT/BATCH_UPDATE:
    # "data": [                # Array of records for batch operations
    #     {"name": "John", "email": "john@test.com"},
    #     {"name": "Jane", "email": "jane@test.com"}
    # ]
}

Operations

INSERT Operation

Creates a new record in the database.

When to use:

• POST requests creating new records
• After validation passes
• When generating new data

Example:

def process_request(input_data):
    data = input_data.get('data', {})

    # Validate required fields
    if 'email' not in data:
        return {
            'operation': 'NONE',
            'error': 'Email is required'
        }

    return {
        'operation': 'INSERT',
        'table': 'users',
        'columns': [
            {'name': 'name', 'type': 'TEXT'},
            {'name': 'email', 'type': 'TEXT'}
        ],
        'data': {
            'name': data.get('name'),
            'email': data.get('email')
        }
    }

---

BATCH_INSERT Operation

Creates multiple records in a single database operation for improved performance.

When to use:

• Bulk data imports
• Seeding test data
• Creating multiple related records at once
• Importing CSV or batch uploads

Example:

def process_request(input_data):
    # Expecting an array of user records
    users_data = input_data.get('data', [])

    if not isinstance(users_data, list):
        return {
            'operation': 'NONE',
            'error': 'Data must be an array for batch insert'
        }

    # Validate each record
    validated_records = []
    for user in users_data:
        if 'email' in user and 'name' in user:
            validated_records.append({
                'name': user['name'],
                'email': user['email'],
                'status': 'active'
            })

    if not validated_records:
        return {
            'operation': 'NONE',
            'error': 'No valid records to insert'
        }

    return {
        'operation': 'BATCH_INSERT',
        'table': 'users',
        'columns': [
            {'name': 'name', 'type': 'TEXT'},
            {'name': 'email', 'type': 'TEXT'},
            {'name': 'status', 'type': 'TEXT'}
        ],
        'data': validated_records  # Array of records
    }

Performance Note: BATCH_INSERT is significantly faster than multiple INSERT operations. Use it when inserting 10+ records at once.

---

SELECT Operation

Queries existing records from the database with optional filtering.

When to use:

• GET requests retrieving data
• Looking up specific records
• Filtering data based on conditions

Basic SELECT (all records):

def process_request(input_data):
    return {
        'operation': 'SELECT',
        'table': 'users'
    }

SELECT with WHERE clause:

def process_request(input_data):
    data = input_data.get('data', {})

    return {
        'operation': 'SELECT',
        'table': 'users',
        'where': {
            'email': data.get('email')  # Filter by email
        }
    }

SELECT with OR conditions:

def process_request(input_data):
    return {
        'operation': 'SELECT',
        'table': 'users',
        'where': {
            'OR': [
                {'email': 'alice@test.com'},
                {'email': 'bob@test.com'},
                {'status': 'admin'}
            ]
        }
    }

SELECT with AND conditions:

def process_request(input_data):
    return {
        'operation': 'SELECT',
        'table': 'users',
        'where': {
            'AND': [
                {'status': 'active'},
                {'role': 'admin'}
            ]
        }
    }

SELECT specific columns:

def process_request(input_data):
    return {
        'operation': 'SELECT',
        'table': 'users',
        'columns': ['name', 'email']  # Only return these columns
    }

---

UPDATE Operation

Modifies existing records in the database.

When to use:

• PUT/PATCH requests updating existing records
• Modifying specific fields
• Changing record status

Basic UPDATE:

def process_request(input_data):
    data = input_data.get('data', {})

    return {
        'operation': 'UPDATE',
        'table': 'users',
        'columns': [
            {'name': 'status', 'type': 'TEXT'},
            {'name': 'updated_at', 'type': 'TEXT'}
        ],
        'data': {
            'status': 'active',
            'updated_at': '2024-01-15T10:00:00Z'
        }
    }

UPDATE with WHERE clause:

def process_request(input_data):
    data = input_data.get('data', {})

    return {
        'operation': 'UPDATE',
        'table': 'users',
        'columns': [
            {'name': 'last_login', 'type': 'TEXT'}
        ],
        'data': {
            'last_login': '2024-01-15T10:00:00Z'
        },
        'where': {
            'email': data.get('email')  # Update only this user
        }
    }

UPDATE with OR conditions:

def process_request(input_data):
    return {
        'operation': 'UPDATE',
        'table': 'users',
        'columns': [
            {'name': 'notification_sent', 'type': 'BOOLEAN'}
        ],
        'data': {
            'notification_sent': True
        },
        'where': {
            'OR': [
                {'role': 'admin'},
                {'role': 'moderator'}
            ]
        }
    }

---

BATCH_UPDATE Operation

Updates multiple records with different values in a single operation.

When to use:

• Updating different records with different values
• Bulk status changes with custom data per record
• Synchronizing external data changes

Example with record_id:

def process_request(input_data):
    # Array of update specifications
    updates = input_data.get('data', [])

    return {
        'operation': 'BATCH_UPDATE',
        'table': 'posts',
        'data': [
            {
                'record_id': 'post_123',
                'data': {'likes_count': 42, 'updated_at': '2024-01-15T10:00:00Z'}
            },
            {
                'record_id': 'post_456',
                'data': {'likes_count': 17, 'updated_at': '2024-01-15T10:00:00Z'}
            }
        ]
    }

Example with WHERE clause:

def process_request(input_data):
    return {
        'operation': 'BATCH_UPDATE',
        'table': 'users',
        'data': [
            {
                'where': {'email': 'alice@test.com'},
                'data': {'status': 'premium'}
            },
            {
                'where': {'email': 'bob@test.com'},
                'data': {'status': 'basic'}
            }
        ]
    }

BATCH_UPDATE data format:

Each item in the data array must contain:

• Either record_id OR where clause to identify the record(s) to update
• data: Dictionary of fields to update

---

DELETE Operation

Removes records from the database.

When to use:

• DELETE requests removing data
• Cleaning up old records
• Removing specific entries

Basic DELETE (all records):

def process_request(input_data):
    return {
        'operation': 'DELETE',
        'table': 'users'
    }

DELETE with WHERE clause:

def process_request(input_data):
    data = input_data.get('data', {})

    return {
        'operation': 'DELETE',
        'table': 'users',
        'where': {
            'email': data.get('email')  # Delete only this user
        }
    }

DELETE with OR conditions:

def process_request(input_data):
    return {
        'operation': 'DELETE',
        'table': 'users',
        'where': {
            'OR': [
                {'status': 'inactive'},
                {'status': 'banned'}
            ]
        }
    }

DELETE with AND conditions:

def process_request(input_data):
    from datetime import datetime, timedelta

    # Delete inactive users who haven't logged in for 90 days
    cutoff_date = (datetime.now() - timedelta(days=90)).isoformat()

    return {
        'operation': 'DELETE',
        'table': 'users',
        'where': {
            'AND': [
                {'status': 'inactive'},
                {'last_login': cutoff_date}  # Simplified for example
            ]
        }
    }

---

NONE Operation

Stops processing without database operation. Used for validation failures or when returning custom errors.

When to use:

• Validation fails
• Duplicate detection
• Conditional logic that prevents database changes
• Custom error messages

Example:

def process_request(input_data):
    data = input_data.get('data', {})
    users_records = input_data.get('all_records', {}).get('users', [])

    # Check for duplicate email
    email = data.get('email')
    for record in users_records:
        if record.get('email') == email:
            return {
                'operation': 'NONE',
                'error': 'Email already exists'
            }

    # Continue with INSERT...

WHERE Clause Syntax

The WHERE clause supports simple equality checks, OR conditions, and AND conditions.

Simple WHERE (equality)

'where': {
    'email': 'user@example.com'
}

OR Conditions

Match records that satisfy ANY of the conditions:

'where': {
    'OR': [
        {'email': 'alice@test.com'},
        {'email': 'bob@test.com'},
        {'status': 'admin'}
    ]
}

This matches records where:

• email is 'alice@test.com' OR
• email is 'bob@test.com' OR
• status is 'admin'

AND Conditions

Match records that satisfy ALL of the conditions:

'where': {
    'AND': [
        {'status': 'active'},
        {'role': 'admin'},
        {'email_verified': True}
    ]
}

This matches records where:

• status is 'active' AND
• role is 'admin' AND
• email_verified is True

Combining Multiple Fields (implicit AND)

When you provide multiple fields without OR/AND, they're treated as AND conditions:

'where': {
    'status': 'active',
    'role': 'admin'
}
# Equivalent to:
'where': {
    'AND': [
        {'status': 'active'},
        {'role': 'admin'}
    ]
}

Note on Complex Queries

Currently, nested OR/AND combinations (like "A AND (B OR C)") are not supported. For complex filtering, use the all_records to filter in Python code instead.

Custom Record IDs

By default, the system auto-generates unique IDs for all records. However, you can override this with custom IDs - essential for systems like URL shorteners or user-friendly slugs.

Why Custom IDs?

Problem without custom IDs:

Long URL: https://example.com/very/long/path
Auto-generated ID: "a7b3c9d2-8f1e-4a6b-9c3d-2e5f8a1b4c7d"
Access via: /api/urls/a7b3c9d2-8f1e-4a6b-9c3d-2e5f8a1b4c7d  ❌ Not user-friendly

Solution with custom IDs:

Long URL: https://example.com/very/long/path
Custom ID: "abc123"
Access via: /api/urls/abc123  ✅ Clean and short!

How to Set Custom IDs

Step 1: Add record_id to columns array:

columns = [
    {'name': 'record_id', 'type': 'TEXT'},  # This enables custom IDs
    {'name': 'long_url', 'type': 'TEXT'},
    {'name': 'short_code', 'type': 'TEXT'}
]

Step 2: Add record_id to data:

data = {
    'record_id': 'abc123',  # Your custom ID
    'long_url': 'https://example.com/very/long/path',
    'short_code': 'abc123'
}

Complete Example: URL Shortener

import random
import string

def generate_short_code(length=6):
    """Generate a random short code"""
    chars = string.ascii_letters + string.digits
    return ''.join(random.choice(chars) for _ in range(length))

def process_request(input_data):
    data = input_data.get('data', {})
    urls_records = input_data.get('all_records', {}).get('urls', [])

    # Validate long URL
    long_url = data.get('long_url')
    if not long_url:
        return {
            'operation': 'NONE',
            'error': 'Long URL is required'
        }

    # Generate unique short code
    while True:
        short_code = generate_short_code()
        # Check if code already exists
        if not any(r.get('record_id') == short_code for r in urls_records):
            break

    return {
        'operation': 'INSERT',
        'table': 'urls',
        'columns': [
            {'name': 'record_id', 'type': 'TEXT'},    # Enable custom ID
            {'name': 'long_url', 'type': 'TEXT'},
            {'name': 'short_code', 'type': 'TEXT'},
            {'name': 'clicks', 'type': 'INTEGER'}
        ],
        'data': {
            'record_id': short_code,                   # Use short code as ID
            'long_url': long_url,
            'short_code': short_code,
            'clicks': 0
        }
    }

Important

If you don't provide record_id in both columns and data, the system will auto-generate an ID. This is fine for most use cases, but custom IDs are required for systems where the ID has meaning (URL shorteners, slugs, etc.).

Returning Custom Errors

Stop processing and return error messages to the user.

Error Structure

return {
    'operation': 'NONE',
    'error': 'Your error message here'
}

Error Examples

Validation Error:

if 'email' not in data or '@' not in data['email']:
    return {
        'operation': 'NONE',
        'error': 'Valid email address is required'
    }

Duplicate Detection:

email = data.get('email')
for record in existing:
    if record.get('email') == email:
        return {
            'operation': 'NONE',
            'error': f'User with email {email} already exists'
        }

Business Logic:

age = data.get('age', 0)
if age < 18:
    return {
        'operation': 'NONE',
        'error': 'Must be 18 or older to register'
    }

Column Types

When defining columns, use these supported types:

Type	Python Type	Use For
TEXT	str	Strings, emails, URLs, descriptions, dates
INTEGER	int	Whole numbers, counts, IDs
REAL	float	Decimals, prices, measurements
BOOLEAN	bool	True/false flags

Example:

columns = [
    {'name': 'username', 'type': 'TEXT'},
    {'name': 'age', 'type': 'INTEGER'},
    {'name': 'balance', 'type': 'REAL'},
    {'name': 'is_active', 'type': 'BOOLEAN'},
    {'name': 'created_at', 'type': 'TEXT'}  # Store ISO format dates as TEXT
]

Working with Existing Records

Access previously inserted records to check for duplicates, calculate aggregates, or implement business logic.

Structure of Existing Records

all_records = {
    "users": [
        { "record_id": "abc123", "name": "John Doe", "email": "john@example.com", ... },
        { "record_id": "def456", "name": "Jane Smith", "email": "jane@example.com", ... }
    ],
    "orders": [
        { "record_id": "ord789", "amount": 99.99, "status": "paid", ... }
    ]
}

Example: Check for Duplicates

def process_request(input_data):
    data = input_data.get('data', {})
    current_records = input_data.get('all_records', {}).get(input_data.get('endpoint'), [])

    email = data.get('email')

    # Check if email already exists
    for record in current_records:
        if record.get('email') == email:
            return {
                'operation': 'NONE',
                'error': f'Email {email} is already registered'
            }

    # Email is unique, proceed with insert
    return {
        'operation': 'INSERT',
        'columns': [
            {'name': 'email', 'type': 'TEXT'},
            {'name': 'name', 'type': 'TEXT'}
        ],
        'data': data
    }

Example: Count Records

def process_request(input_data):
    users_records = input_data.get('all_records', {}).get('users', [])

    # Count active users
    active_count = sum(1 for r in users_records if r.get('is_active'))

    # Limit to 100 active users
    if active_count >= 100:
        return {
            'operation': 'NONE',
            'error': 'Maximum number of active users reached'
        }

    # Continue with insert...

Example: Generate Sequential IDs

def process_request(input_data):
    order_records = input_data.get('all_records', {}).get('orders', [])

    # Find highest order number
    max_order = 0
    for record in order_records:
        order_num = record.get('order_number', 0)
        if order_num > max_order:
            max_order = order_num

    new_order_number = max_order + 1

    return {
        'operation': 'INSERT',
        'columns': [
            {'name': 'order_number', 'type': 'INTEGER'},
            {'name': 'customer', 'type': 'TEXT'}
        ],
        'data': {
            'order_number': new_order_number,
            'customer': input_data['data'].get('customer')
        }
    }

Available Python Libraries

Your custom code runs in a sandboxed Python environment with access to:

Standard Library:

• json - JSON parsing
• datetime, timedelta - Date/time operations
• random - Random number generation
• string - String constants and operations
• math - Mathematical functions
• re - Regular expressions
• Other standard Python modules

Example using datetime:

from datetime import datetime, timedelta

def process_request(input_data):
    # Calculate expiration date (30 days from now)
    expiry = datetime.now() + timedelta(days=30)

    return {
        'operation': 'INSERT',
        'columns': [
            {'name': 'token', 'type': 'TEXT'},
            {'name': 'expires_at', 'type': 'TEXT'}
        ],
        'data': {
            'token': 'abc123',
            'expires_at': expiry.isoformat()
        }
    }

info

Third-party libraries (like requests, numpy, etc.) are not currently available. The code runs in a secure sandboxed environment using Judge0 API.

Debugging Your Code

Using Return Errors for Debugging

The best way to debug your code is to return diagnostic information as errors:

def process_request(input_data):
    data = input_data.get('data', {})

    # Debug: Check what data you received
    return {
        'operation': 'NONE',
        'error': f'Debug: Received data = {data}'
    }

This lets you see variable values without causing execution errors.

Common Debugging Patterns

Check input structure:

return {
    'operation': 'NONE',
    'error': f'Keys: {list(input_data.keys())}'
}

Verify existing records:

all_records = input_data.get('all_records', {})
return {
    'operation': 'NONE',
    'error': f'Found {list(all_records.keys())} existing records'
}

Test conditions:

email = data.get('email')
has_at = '@' in email if email else False
return {
    'operation': 'NONE',
    'error': f'Email: {email}, Has @: {has_at}'
}

About Print Statements

Do not use print() statements in your code. They will cause the simulation to fail because the system expects a structured JSON response.

Instead, use the error return pattern shown above for debugging.

Complete Examples

Example 1: User Registration with Validation

import re
from datetime import datetime

def process_request(input_data):
    data = input_data.get('data', {})
    users_records = input_data.get('all_records', {}).get('users', [])

    # Validate email format
    email = data.get('email', '')
    if not re.match(r'^[\w\.-]+@[\w\.-]+\.\w+$', email):
        return {
            'operation': 'NONE',
            'error': 'Invalid email format'
        }

    # Check for duplicate email
    for record in users_records:
        if record.get('email') == email:
            return {
                'operation': 'NONE',
                'error': 'Email already registered'
            }

    # Validate password strength (if provided)
    password = data.get('password', '')
    if len(password) < 8:
        return {
            'operation': 'NONE',
            'error': 'Password must be at least 8 characters'
        }

    # Create user
    return {
        'operation': 'INSERT',
        'table': 'users',
        'columns': [
            {'name': 'email', 'type': 'TEXT'},
            {'name': 'name', 'type': 'TEXT'},
            {'name': 'is_active', 'type': 'BOOLEAN'},
            {'name': 'created_at', 'type': 'TEXT'}
        ],
        'data': {
            'email': email,
            'name': data.get('name'),
            'is_active': True,
            'created_at': datetime.now().isoformat()
        }
    }

Example 2: Rate Limiter

from datetime import datetime, timedelta

def process_request(input_data):
    data = input_data.get('data', {})
    rate_limit_records = input_data.get('all_records', {}).get('rate_limits', [])

    user_id = data.get('user_id')
    current_time = datetime.now()
    one_minute_ago = current_time - timedelta(minutes=1)

    # Count requests in last minute for this user
    recent_requests = 0
    for record in rate_limit_records:
        if record.get('user_id') == user_id:
            request_time = datetime.fromisoformat(record.get('timestamp', ''))
            if request_time > one_minute_ago:
                recent_requests += 1

    # Limit: 10 requests per minute
    if recent_requests >= 10:
        return {
            'operation': 'NONE',
            'error': 'Rate limit exceeded. Try again later.'
        }

    # Log this request
    return {
        'operation': 'INSERT',
        'table': 'rate_limits',
        'columns': [
            {'name': 'user_id', 'type': 'TEXT'},
            {'name': 'timestamp', 'type': 'TEXT'}
        ],
        'data': {
            'user_id': user_id,
            'timestamp': current_time.isoformat()
        }
    }

Example 3: User Lookup with SELECT

def process_request(input_data):
    data = input_data.get('data', {})
    method = input_data.get('method')

    # GET request - look up user by email
    if method == 'GET':
        return {
            'operation': 'SELECT',
            'table': 'users',
            'where': {
                'email': data.get('email')
            }
        }

    # POST request - create new user
    elif method == 'POST':
        return {
            'operation': 'INSERT',
            'table': 'users',
            'columns': [
                {'name': 'email', 'type': 'TEXT'},
                {'name': 'name', 'type': 'TEXT'}
            ],
            'data': data
        }

Example 4: Soft Delete with UPDATE

from datetime import datetime

def process_request(input_data):
    data = input_data.get('data', {})

    # Instead of DELETE, mark as deleted
    return {
        'operation': 'UPDATE',
        'table': 'users',
        'columns': [
            {'name': 'is_deleted', 'type': 'BOOLEAN'},
            {'name': 'deleted_at', 'type': 'TEXT'}
        ],
        'data': {
            'is_deleted': True,
            'deleted_at': datetime.now().isoformat()
        },
        'where': {
            'email': data.get('email')
        }
    }

Example 5: Batch User Import

def process_request(input_data):
    users_to_import = input_data.get('data', [])
    existing_users = input_data.get('all_records', {}).get('users', [])

    # Get existing emails for duplicate check
    existing_emails = {user.get('email') for user in existing_users}

    # Filter out duplicates
    valid_users = []
    for user in users_to_import:
        email = user.get('email')
        if email and email not in existing_emails:
            valid_users.append({
                'name': user.get('name'),
                'email': email,
                'status': 'active'
            })
            existing_emails.add(email)  # Prevent duplicates within batch

    if not valid_users:
        return {
            'operation': 'NONE',
            'error': 'No new users to import (all emails already exist)'
        }

    return {
        'operation': 'BATCH_INSERT',
        'table': 'users',
        'columns': [
            {'name': 'name', 'type': 'TEXT'},
            {'name': 'email', 'type': 'TEXT'},
            {'name': 'status', 'type': 'TEXT'}
        ],
        'data': valid_users
    }

Example 6: Batch Status Update with OR Clause

from datetime import datetime

def process_request(input_data):
    # Deactivate all inactive or banned users
    return {
        'operation': 'UPDATE',
        'table': 'users',
        'columns': [
            {'name': 'is_active', 'type': 'BOOLEAN'},
            {'name': 'deactivated_at', 'type': 'TEXT'}
        ],
        'data': {
            'is_active': False,
            'deactivated_at': datetime.now().isoformat()
        },
        'where': {
            'OR': [
                {'status': 'inactive'},
                {'status': 'banned'}
            ]
        }
    }

Use Cases

Professional System Design

• Model production business logic - Document and test validation rules, data transformations, and business workflows
• Design data pipelines - Plan how data flows through validation, processing, and storage layers
• Test architectural patterns - Experiment with different approaches before implementing in production
• Collaborate on API contracts - Share working examples of request/response handling with your team
• Document existing systems - Create executable documentation of current API behavior

Interview Preparation & Portfolio

• Practice system design patterns - Implement common patterns like URL shorteners, rate limiters, and caching
• Build working examples - Demonstrate technical depth with executable code, not just diagrams
• Show problem-solving approach - Display validation logic, error handling, and edge case consideration
• Explain trade-offs - Use concrete examples to discuss different implementation approaches

Learning & Education

• Understand business logic layers - See how validation and transformation work in real systems
• Experiment safely - Test different approaches without production consequences
• Learn from templates - Study working examples of common patterns and best practices

Best Practices

1. Always validate input data - Never trust incoming data
2. Use meaningful variable names - Make your code readable
3. Return clear error messages - Help users understand what went wrong
4. Check all_records when needed - Prevent duplicates and conflicts
5. Use appropriate column types - INTEGER for numbers, TEXT for strings, etc.
6. Document your logic - Add comments explaining complex operations
7. Test incrementally - Start simple, add complexity gradually
8. Handle edge cases - What if data is missing? What if it's invalid?
9. Use WHERE clauses for targeted operations - Avoid updating/deleting all records unintentionally
10. Use batch operations for bulk data - BATCH_INSERT and BATCH_UPDATE are much faster for multiple records
11. Use OR/AND clauses for complex filtering - More efficient than filtering in Python code

Troubleshooting

"Invalid code output structure"

• Cause: Return dictionary missing required keys
• Solution: Ensure you return operation, and include columns/data for INSERT/UPDATE/BATCH operations

"Error processing the request"

• Cause: Python syntax error or runtime exception
• Solution: Check the error output in the logs panel, verify your Python syntax

"Missing 'operation' field"

• Cause: Forgot to include operation in return dictionary
• Solution: Always include 'operation': 'INSERT', 'SELECT', 'UPDATE', 'DELETE', 'BATCH_INSERT', 'BATCH_UPDATE', or 'NONE'

Custom ID not working

• Cause: Forgot to add record_id to columns or data
• Solution: Add {'name': 'record_id', 'type': 'TEXT'} to columns AND 'record_id': 'your_id' to data

Can't access existing records

• Cause: Variable name typo or wrong key
• Solution: Use input_data.get('all_records', {}) exactly

WHERE clause not filtering correctly

• Cause: Column name mismatch or incorrect value
• Solution: Ensure column names in WHERE match your table schema exactly

BATCH operation failing

• Cause: Data is not an array, or array items are not dictionaries
• Solution: For BATCH_INSERT/BATCH_UPDATE, data must be a list of dictionaries

Next Steps

• Learn about Database Component for data persistence and querying
• Explore Load Balancer Component for traffic distribution and scaling
• Review User Request Component for configuring incoming requests
• Try our Templates to see API Service patterns in action
• Share your designs with your team for collaborative system planning