lotus-replace-odbc-direct-writes

What this skill does

Works with ODBC query migration, connection string updates, transaction handling, and error recovery patterns.
# Replace Lotus ODBC Direct Writes with API Patterns

## Table of Contents

**Quick Start** → [What Is This](#purpose) | [When to Use](#when-to-use) | [Simple Example](#examples)

**How to Implement** → [Step-by-Step](#instructions) | [Expected Outcomes](#quick-start)

**Reference** → [Requirements](#requirements) | [Related Skills](#see-also)

## Purpose

Legacy Lotus Notes systems often use ODBC (Open Database Connectivity) for direct database manipulation—bypassing the Notes client and directly modifying NSF files. This approach is problematic for migration because it creates hidden dependencies, lacks proper transaction handling, and breaks encapsulation. This skill guides you through replacing ODBC direct writes with clean, API-based patterns that work in modern architectures.

## When to Use

Use this skill when you need to:

- **Migrate direct ODBC writes** - Replace code that directly modifies Lotus Notes databases via ODBC
- **Convert backend connections** - Transform ODBC connections to HTTP API calls
- **Implement proper request/response patterns** - Design clean API contracts with validation and error handling
- **Establish secure data synchronization** - Replace direct database access with authenticated API calls
- **Update transaction handling** - Migrate ODBC transactions to API-based transaction semantics
- **Improve error recovery** - Add retry logic, idempotency, and proper error propagation

This skill is essential for modernizing tightly-coupled Lotus Notes integrations that bypass proper application boundaries.

## Quick Start

To migrate from ODBC direct writes to APIs:

1. Identify all ODBC connection strings and database access patterns
2. Map direct writes to business operations (what data changes are intended?)
3. Design API endpoints that encapsulate these operations
4. Implement request/response contracts with validation
5. Update calling code to use HTTP APIs instead of ODBC
6. Add retry logic, error handling, and logging
7. Test transaction behavior and rollback scenarios

## Instructions

### Step 1: Identify ODBC Direct Writes

Locate all direct ODBC database access in the codebase:

**Search for patterns:**
- ODBC connection strings or DSN references
- Direct SQL UPDATE/INSERT/DELETE statements to NSF databases
- Lotus Notes backend agent scripts that modify databases
- VB, Java, or scripting code that writes directly to Notes databases

**Document each occurrence with:**
- Source code location (file, function, line)
- ODBC connection details (database path, credentials)
- SQL statement being executed
- What business operation does this represent?
- Who calls this code and how often?
- What data validation is currently in place?

Example:

```
File: OrderProcessing.cls
Function: ApproveOrder(OrderID)
  Line 45: conn.Execute("UPDATE Orders SET Status='Approved' WHERE OrderID='" & OrderID & "'")
  Issue: Direct write with no validation or transaction handling
  Business operation: Mark order as approved
```

### Step 2: Understand Current Transaction Semantics

Analyze the ODBC access patterns to understand what transactions are needed:

**For each write operation, document:**
- Is this a single update or are multiple tables modified?
- What validation must happen before the write?
- What happens if the write fails? (rollback other changes?)
- Are there dependent operations that must follow?
- Does this operation trigger external side effects (emails, notifications)?

Example structure:

```
Operation: ApproveOrder(OrderID)
  Preconditions:
    - Order exists and Status='Draft'
    - User has 'Approver' role
    - Order total < budget limit for supplier
  Changes:
    - Update Order.Status = 'Approved'
    - Update Order.ApprovedDate = Now()
    - Create AuditLog entry
  Side effects:
    - Send email to Supplier
    - Update Dashboard cache
  Rollback behavior:
    - If email fails, should approval be rolled back?
```

### Step 3: Design API Endpoints

Create API endpoint definitions that replace the ODBC operations:

**For each operation, design:**

```
POST /api/orders/{orderId}/approve
  Description: Approve a pending order

  Request:
    {
      "approvalReason": "Budget approved",
      "approverNotes": "..."
    }

  Response (Success - 200):
    {
      "id": "ORDER-123",
      "status": "Approved",
      "approvedDate": "2025-11-03T10:30:00Z",
      "approvedBy": "[email protected]"
    }

  Response (Validation Error - 400):
    {
      "error": "ORDER_NOT_DRAFT",
      "message": "Order must be in Draft status to approve"
    }

  Response (Permission Error - 403):
    {
      "error": "INSUFFICIENT_PERMISSIONS",
      "message": "User does not have approval role"
    }

  Response (Server Error - 500):
    {
      "error": "APPROVAL_FAILED",
      "message": "Failed to send approval notification"
    }
```

**Design principles:**

- **Validation at API boundary**: All input validation before any database changes
- **Clear error responses**: Distinguish between user errors (4xx) and system errors (5xx)
- **Transaction clarity**: Make it clear what will and won't be rolled back
- **Idempotency**: Consider if calling the same operation twice should be safe
- **Audit trail**: Ensure who, what, when is captured

### Step 4: Implement API Endpoints

Create backend API endpoints with proper structure:

**Example Python implementation pattern:**

```python
from flask import Flask, request, jsonify
from typing import Dict, Tuple

class OrderService:
    def approve_order(self, order_id: str, approver_id: str, reason: str) -> Dict:
        """
        Approve a pending order with full validation and transaction handling.

        Args:
            order_id: The order to approve
            approver_id: User ID of approver
            reason: Reason for approval

        Returns:
            Approved order details

        Raises:
            OrderNotFound: Order doesn't exist
            OrderNotPending: Order is not in Draft status
            InsufficientPermissions: User can't approve
            ApprovalFailed: Underlying operation failed
        """
        # Step 1: Validate inputs
        if not order_id or not approver_id:
            raise ValueError("order_id and approver_id required")

        # Step 2: Check preconditions
        order = self.db.orders.get(order_id)
        if not order:
            raise OrderNotFound(f"Order {order_id} not found")

        if order['status'] != 'Draft':
            raise OrderNotPending(f"Order status is {order['status']}, expected Draft")

        if not self._user_can_approve(approver_id, order):
            raise InsufficientPermissions(f"User {approver_id} cannot approve")

        # Step 3: Execute transaction
        try:
            with self.db.transaction() as txn:
                # Update order
                self.db.orders.update(order_id, {
                    'status': 'Approved',
                    'approved_date': datetime.now(),
                    'approved_by': approver_id,
                    'approval_reason': reason
                })

                # Create audit entry
                self.db.audit_log.create({
                    'order_id': order_id,
                    'action': 'APPROVE',
                    'user': approver_id,
                    'timestamp': datetime.now(),
                    'details': reason
                })

                txn.commit()
        except Exception as e:
            raise ApprovalFailed(f"Failed to approve order: {str(e)}")

        # Step 4: Handle side effects (outside transaction)
        try:
            self._send_approval_notification(order_id)
        except Exception as e:
            # Log but don't fail—approval is already committed
            logger.error(f"Failed to send notification for {order_id}: {e}")

        # Step 5: Return updated order
        return self.db.orders.get(