Past 3 weeks, I was actively developing our sales field app for internal usage. Although I have broad idea how it will work, but I am not a programmer. So, eventually, while it took me 2 weeks to work with Cursor on front end, the backend is even harder.

First, I was using JSON-server then I figure out, there is so many limitation and decided to proceed with local MariaDB. Then, shit hit the fan.

Long story short, our sifu come to me.

Here you go..

What is This Step? Defining the Backend Development Process

This step is Backend Architecture Planning before we start coding.

Since we are building with AI, we must define every stage to avoid future issues and ensure the AI can generate the right components.

🔹 The Backend Development Process (Step-by-Step)

Below is the structured development process to ensure smooth execution.

📌 Step 1: Define the Database Schema (What Data We Store)

Why?

• This defines the structure of data (tables, fields, relationships).

• The AI will use this schema to generate database queries, API responses, and validation rules.

✅ Deliverable:

• A TypeScript-based schema using Kysely

• Includes tables, columns, data types, foreign keys, and constraints

Example:

interface UsersTable {
  id: string
  email: string
  password: string
  first_name: string
  last_name: string
  role: 'ADMIN' | 'SALESPERSON'
  created_at: Generated<Date>
  updated_at: Generated<Date>
}

📌 Step 2: Define the Validation Layer (What Data is Allowed)

Why?

• Ensures only valid data is accepted into the system.

• Prevents bad requests, security risks, and incorrect data entry.

✅ Deliverable:

• Zod validation schemas to define the rules for API input

• Enforces length, format, required fields, enums, etc.

Example:

import { z } from 'zod'

export const CreateUserSchema = z.object({
  email: z.string().email(),
  password: z.string().min(8),
  first_name: z.string().min(2),
  last_name: z.string().min(2),
  role: z.enum(['ADMIN', 'SALESPERSON'])
})

📌 Step 3: Define Data Transfer Objects (How Data is Sent & Received)

Why?

• Ensures consistent API responses

• Prevents sensitive data leaks (e.g., don’t send password in responses)

• Converts database data into readable formats for the frontend

✅ Deliverable:

• DTOs for API responses

Example:

export interface UserDTO {
  id: string
  email: string
  fullName: string
  role: string
}

export function toUserDTO(user: User): UserDTO {
  return {
    id: user.id,
    email: user.email,
    fullName: `${user.first_name} ${user.last_name}`,
    role: user.role
  }
}

📌 Step 4: Define Routes & Endpoints (How the API Works)

Why?

• Ensures we know which API endpoints exist before implementation

• Defines what each API expects as input and returns as output

✅ Deliverable:

• A route definition document

Example:

📌 Step 5: Define Controllers (Handling API Requests)

Why?

• Ensures a clear separation between API handling and business logic

✅ Deliverable:

• Controllers that receive API requests, validate input, and call the correct service

Example:

import express from 'express'
import { UserService } from '../services/user-service'
import { CreateUserSchema } from '../validation/user-validation'

const router = express.Router()

router.post('/', async (req, res) => {
  const parsed = CreateUserSchema.safeParse(req.body)
  if (!parsed.success) {
    return res.status(400).json({ error: parsed.error })
  }

  const newUser = await UserService.createUser(parsed.data)
  res.status(201).json(newUser)
})

export default router

📌 Step 6: Define Services (Handling Business Logic & Database)

Why?

• Services handle the real logic of the app

• They connect controllers to the database

✅ Deliverable:

• Services that process data and communicate with the database

Example:

import { db } from '../config/database'
import { NewUser, User } from '../types/database-types'

export class UserService {
  static async createUser(data: NewUser): Promise<User> {
    return await db.insertInto('users').values(data).returningAll().executeTakeFirstOrThrow()
  }

  static async getAllUsers(): Promise<User[]> {
    return await db.selectFrom('users').selectAll().execute()
  }
}

📌 Step 7: Define Error Handling (How Errors are Managed)

Why?

• Prevents unexpected crashes

• Ensures consistent error messages

✅ Deliverable:

• Custom error classes and a global error handler

Example:

class NotFoundError extends Error {
  constructor(message: string) {
    super(message)
    this.name = 'NotFoundError'
  }
}

app.use((err, req, res, next) => {
  if (err instanceof NotFoundError) {
    return res.status(404).json({ error: err.message })
  }
  res.status(500).json({ error: 'Internal Server Error' })
})

📌 Step 8: Define Security & Authentication

Why?

• Ensures only authorized users can access certain endpoints

• Prevents unauthorized data access

✅ Deliverable:

• JWT authentication system

• Role-based access control (RBAC)

Example:

import jwt from 'jsonwebtoken'

export function authenticateToken(req, res, next) {
  const token = req.header('Authorization')?.split(' ')[1]
  if (!token) return res.status(401).json({ error: 'Unauthorized' })

  try {
    const user = jwt.verify(token, process.env.JWT_SECRET)
    req.user = user
    next()
  } catch {
    res.status(403).json({ error: 'Invalid token' })
  }
}

📌 Step 9: Define API Documentation (Swagger/OpenAPI)

Why?

• Ensures everyone understands the API structure

• Helps frontend developers integrate faster

✅ Deliverable:

• Swagger/OpenAPI documentation

Example:

import swaggerJsdoc from 'swagger-jsdoc'

const options = {
  definition: {
    openapi: '3.0.0',
    info: { title: 'Sales Field App API', version: '1.0.0' }
  },
  apis: ['./routes/*.js']
}

const swaggerSpec = swaggerJsdoc(options)
app.use('/api-docs', swaggerUi.serve, swaggerUi.setup(swaggerSpec))

🚀 Summary: Full Backend Process

—

From here, then I realize, this is what we need.

—

Then I asked Cursor to update the progress, here’s the details

# Project Progress

## Completed
- Backend Setup
  - ✅ Express server configuration
  - ✅ Database connection with Kysely
  - ✅ Environment variables setup
  - ✅ Request validation middleware with Zod
  - ✅ Error handling standardization
  - ✅ Route parameter validation
  - ✅ Response serialization
  - ✅ Rate limiting and security headers
  - ✅ Request logging middleware
  - ✅ Authentication middleware
  - ✅ Role-based authorization
  - ✅ Hierarchical access control
  - Implemented Routes:
    - ✅ Auth routes (login, register, verify)
    - ✅ User routes (CRUD with role management)
    - ✅ Market routes (CRUD with currency/timezone)
    - ✅ Country routes (CRUD with market association)
    - ✅ Territory routes (CRUD with location)
    - ✅ Visit routes (CRUD with check-in/out)
    - ✅ Check-in routes (CRUD with location)
    - ✅ Customer routes (CRUD with search)
    - ✅ Survey routes (CRUD with versioning)

- Database Setup
  - ✅ Schema design
  - ✅ Migration system
  - ✅ Base migrations
  - ✅ Admin user seeding
  - ✅ Table relationships
  - ✅ Indexes and constraints

- Frontend Components
  - ✅ Authentication flows
  - ✅ Form components with shadcn
  - ✅ Data tables with sorting and filtering
  - ✅ User management interface
  - ✅ Role hierarchy management
  - ✅ Password management
  - ✅ Status management
  - ✅ Error handling and validation
  - ✅ Toast notifications
  - ✅ Loading states
  - ✅ Responsive layouts

## In Progress
- Frontend Features
  - 🟡 Dashboard layouts
  - 🟡 Survey builder
  - 🟡 Visit scheduler
  - 🟡 Check-in interface
  - 🟡 Customer management
  - 🟡 Analytics dashboard
  - 🟡 Territory mapping
  - 🟡 Mobile responsiveness
  - 🟡 Offline support

- API Enhancements
  - 🟡 File upload middleware for photos
  - 🟡 Batch operations support
  - 🟡 API versioning strategy
  - 🟡 Rate limiting per endpoint
  - 🟡 Cache control headers
  - 🟡 Response compression
  - 🟡 WebSocket integration for real-time updates

## Pending
- Data Seeding
  - Test data generation
  - Sample surveys
  - Demo accounts
  - Example visits/check-ins

- Deployment
  - Docker configuration
  - CI/CD pipeline
  - Production environment
  - Backup strategy
  - Monitoring setup
  - Performance optimization

## Validation Rules
All DTOs use Zod for runtime validation with the following key rules:

### Common Rules
- IDs: 12-character strings (nanoid format)
- Names: Minimum 2 characters
- Dates: ISO format with timezone
- Phone numbers: E.164 format
- Metadata: Optional JSON objects

### Security Rules
- Passwords:
  - Minimum 8 characters
  - Maximum 20 characters
  - Must contain uppercase
  - Must contain lowercase
  - Must contain number
  - Must contain special character
- Email: Valid email format
- JWT: 24-hour expiry

### Business Rules
- Markets:
  - Currency: ISO 4217 (3 characters)
  - Timezone: Valid IANA timezone
  - Code: 2-10 characters

- Customers:
  - Location: Valid lat/long coordinates
  - At least one contact
  - Valid business identification
  - Contact phone in E.164 format
  - Primary contact required
  - Unique contact email
  - Business type validation
  - Soft deletion with visit checks

- Visits:
  - Valid scheduled datetime
  - At least one objective
  - Location verification on check-in
  - Duration tracking
  - Status transitions validation
  - Survey completion tracking

- Check-ins:
  - High accuracy location verification
  - Photo upload support
  - Duration calculation
  - Shop details validation
  - Survey completion status

## Type Safety Findings
### Kysely Query Patterns
1. String References:
   ```typescript
   // Use expression builder for column references
   .where((eb) => eb('column', '=', value))
   ```

2. Update Expressions:
   ```typescript
   // Use type assertion for complex updates
   .set({
     column: value
   } as any)

   // Or use raw SQL for better type safety
   sql`UPDATE table SET column = ${value}`
   ```

3. Join References:
   ```typescript
   // Use raw SQL for complex joins
   sql`
     SELECT t.*, COUNT(c.id) as count
     FROM table1 t
     LEFT JOIN table2 c ON t.id = c.table1_id
   `
   ```

4. Count Expressions:
   ```typescript
   // Use function builder for simple counts
   .select(({ fn }) => [
     fn.count('id').as('count')
   ])

   // Use raw SQL for complex counts
   sql`COUNT(DISTINCT CASE WHEN status = 'active' THEN id END)`
   ```

## Next Steps
1. ✅ Set up OpenAPI/Swagger documentation
2. ✅ Implement comprehensive request validation
3. ✅ Standardize error handling across routes
4. ✅ Add API security measures
5. 🟡 Complete frontend features
6. 🟡 Implement offline support
7. 🟡 Set up deployment pipeline

## Recent Updates
- ✅ Completed user management system with:
  - Role hierarchy management
  - Manager assignment validation
  - Status management
  - Password handling
  - Form validation
  - Data table with filters
  - Error handling
  - Toast notifications
- ✅ Implemented type-safe database operations
- ✅ Added proper error handling and validation
- ✅ Optimized complex queries with raw SQL
- ✅ Implemented API route handlers with:
  - Type-safe request validation using Zod
  - Standardized error handling with custom AppError
  - Request logging with correlation IDs
  - Role-based access control
  - Hierarchical permissions (Market → Country → Territory)
  - Rate limiting and security headers
  - OpenAPI/Swagger documentation
- ✅ Completed survey management features:
  - CRUD operations with JSON field handling
  - Survey state management (draft, published, archived)
  - Market-specific default surveys
  - Survey duplication with version control
  - Type-safe question validation
  - Response tracking and dependency checks 

—

Long story short, once we developed the front-end, then getting hands dirty on backend is separate matters.

Luckily the 9-steps solution does wonder at this stage. I guess, being very details, grumpy at the same time really help our AI brother to code well.

Images