CRM Build

Stage 03 · Backend

Home › Apps › CRM › Stage 03
🐍 Stage 03 · 2–3 hours

Add a backend.
Flask + SQLite + fetch.

In this stage, the CRM stops being a toy. You'll add a Python Flask backend with a SQLite database. The UI barely changes — the buttons still say "Add customer", the table still shows rows. But now the data lives in a real database on a real server. Two users on two computers see the same customers. Restart the server, and the data is still there. This is the transition from a client-side demo to a real application.

2–3 hours Intermediate Python · Flask · SQLite · fetch
CRM hub What you'll build The REST API Rewire the frontend Setup and run Full project

Short answer: Three files in Python (app.py, database.py, schema.sql) plus a seed script. Four REST routes: GET /api/customers, POST /api/customers, PUT /api/customers/:id, DELETE /api/customers/:id. The frontend swaps localStorage.getItem for await fetch('/api/customers'). Every CRUD operation becomes async. The UI is identical. The data is now in SQLite.

✅ What changes

The UI stays. The data layer changes.

Where data lives

Was localStorage (in the browser). Now crm.db (a SQLite file on the server).

How CRUD works

Was synchronous localStorage.setItem(). Now async fetch() to the API.

What users see

Identical. Same table, same modal, same buttons, same KPIs. The visual layer is unchanged.

What you learn

REST APIs, HTTP methods, JSON serialization, async/await, error handling, CORS, SQL, and how a real backend works.

✅ The REST API

Four routes. Standard HTTP verbs.

api.py
@app.route('/api/customers', methods=['GET'])
def list_customers():
    db = get_db()
    rows = db.execute(
        'SELECT id, name, email, phone, company, status, notes, '
        'created_at AS createdAt, updated_at AS updatedAt '
        'FROM customers ORDER BY created_at DESC'
    ).fetchall()
    return jsonify([dict(row) for row in rows])


@app.route('/api/customers', methods=['POST'])
def create_customer():
    data = request.get_json()
    # ... validate, insert ...
    return jsonify(new_row), 201


@app.route('/api/customers/<int:customer_id>', methods=['PUT'])
def update_customer(customer_id):
    # ... find existing, validate, update ...
    return jsonify(updated_row)


@app.route('/api/customers/<int:customer_id>', methods=['DELETE'])
def delete_customer(customer_id):
    # ... find existing, delete ...
    return jsonify({'deleted': customer_id}), 200
The four REST calls:

  GET /api/customers
    → fetch('/api/customers')
    → returns [{...}, {...}]

  POST /api/customers
    → fetch('/api/customers', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ name, email, ... })
      })
    → returns the new customer with id and timestamps

  PUT /api/customers/:id
    → fetch('/api/customers/1', {
        method: 'PUT',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ ... })
      })
    → returns the updated customer

  DELETE /api/customers/:id
    → fetch('/api/customers/1', { method: 'DELETE' })
    → returns { deleted: 1 }

Why REST:

  The URL identifies the resource (customers).
  The method identifies the operation
    (GET = read, POST = create, PUT = update, DELETE = delete).
  The body carries the data (as JSON).
  The response carries the result (as JSON).

  This is the pattern every modern API uses.
  Once you understand it here, you understand
  every REST API you'll ever use.

Status codes:

  200 OK           — successful GET, PUT, DELETE
  201 Created      — successful POST
  400 Bad Request  — validation failed
  404 Not Found    — the ID doesn't exist
  500 Server Error — the server crashed

  The frontend uses res.ok to distinguish
  success from failure. That's what allows
  the try/catch to work.

✅ Rewire the frontend

Same functions. Now async. Now fetch.

fetch.js
// ─── API base URL ───
const API_BASE = window.location.hostname === 'localhost'
  ? 'http://127.0.0.1:5000'
  : '';

// ─── Generic fetch helper ───
async function apiFetch(path, options) {
  const res = await fetch(API_BASE + path, options);
  if (!res.ok) {
    let message = 'Request failed: ' + res.status;
    try {
      const data = await res.json();
      if (data && data.error) message = data.error;
    } catch (e) { /* ignore */ }
    throw new Error(message);
  }
  return res.json();
}

// ─── CRUD ───
async function fetchCustomers() {
  return apiFetch('/api/customers');
}

async function createCustomer(data) {
  return apiFetch('/api/customers', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(data)
  });
}

async function updateCustomerAPI(id, data) {
  return apiFetch('/api/customers/' + id, {
    method: 'PUT',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(data)
  });
}

async function deleteCustomerAPI(id) {
  return apiFetch('/api/customers/' + id, { method: 'DELETE' });
}

// ─── Load (called on page start) ───
async function loadCustomers() {
  setLoading(true);
  try {
    state.customers = await fetchCustomers();
    state.error = null;
  } catch (e) {
    state.error = e.message;
    showToast('Could not load: ' + e.message, true);
  } finally {
    setLoading(false);
    render();
  }
}
Stage 02 vs Stage 03:

Stage 02 (localStorage):
  function loadCustomers() {
    const raw = localStorage.getItem(KEY);
    state.customers = JSON.parse(raw) || [];
    render();
  }
  
  function addCustomer(data) {
    state.customers.unshift(newCustomer(data));
    localStorage.setItem(KEY, JSON.stringify(state.customers));
    render();
  }

Stage 03 (fetch):
  async function loadCustomers() {
    setLoading(true);
    try {
      state.customers = await fetchCustomers();
    } catch (e) {
      state.error = e.message;
    } finally {
      setLoading(false);
      render();
    }
  }
  
  async function handleCreate(data) {
    setLoading(true);
    try {
      await createCustomer(data);
      await loadCustomers();  // refresh from server
      closeModal();
    } catch (e) {
      showToast(e.message, true);
    }
  }

Key differences:

  1. async / await
     The function waits for the server.
     The UI shows a loading spinner.
     When the response arrives, the UI updates.

  2. try / catch / finally
     catch: handle the error (toast, error state)
     finally: hide the spinner, re-render
     This is the standard async error pattern.

  3. Refresh after mutation
     After POST / PUT / DELETE, we call
     loadCustomers() to get the server's
     canonical view of the data.
     
     Why? The server assigns the id, the
     timestamps, and might normalize fields.
     Refreshing gives us the server's truth.

  4. Loading state
     state.loading shows a spinner while
     a request is in flight.
     Without it, the user might click
     "Add" twice and create duplicates.

  5. Error state
     If loadCustomers fails (server down),
     state.error holds the message and the
     table shows "could not load — retry".
     This is a real production pattern.

The optimistic vs pessimistic update:

  Optimistic:
    Update state immediately, send the
    request in the background.
    Fast, but if the request fails,
    the UI is wrong.
    Used for likes, follows, etc.

  Pessimistic:
    Wait for the server, then update state.
    Slower (network round-trip), but
    the UI is always correct.
    Used for CRUD that matters.
    
  This stage uses pessimistic. The
  "loading" spinner covers the wait.
  For a CRM, correctness matters more
  than speed.

  Later, you could optimize individual
  operations to optimistic. That's a
  Stage 04+ decision.

✅ Setup and run

Five commands. Five minutes.

terminal
# 1. Navigate to the backend folder
cd crm-stage-03/backend

# 2. Create a virtual environment
python -m venv venv

# 3. Activate it
source venv/bin/activate        # macOS/Linux
venv\Scriptsctivate           # Windows

# 4. Install dependencies
pip install -r requirements.txt

# 5. Seed the database with sample data
python seed.py
# → Seeded 5 customers.

# 6. Start the server
python app.py
# → Running on http://127.0.0.1:5000

# 7. Open http://127.0.0.1:5000 in your browser
What each step does:

1. cd backend
   Get into the folder with app.py.

2. python -m venv venv
   Create an isolated Python environment.
   Dependencies installed here don't
   affect your system Python.

3. source venv/bin/activate
   Activate the environment. Your shell
   now uses the venv's Python and pip.

4. pip install -r requirements.txt
   Install Flask and flask-cors.
   Two packages. Both small.

5. python seed.py
   Create the crm.db file, the customers
   table, and insert 5 sample rows.
   Idempotent — safe to run twice.

6. python app.py
   Start the Flask development server.
   Serves the frontend at /
   and the API at /api/customers.

7. Open the browser
   You see the CRM with 5 customers.
   Add, edit, delete — all persisted.

Verify it's working:

  $ curl http://127.0.0.1:5000/api/health
  {"service":"crm-backend","status":"ok"}
  
  $ curl http://127.0.0.1:5000/api/customers
  [{"id":1,"name":"Alice Rahman",...},...]
  
  If you see this, the backend works.

To stop the server:
  Ctrl+C in the terminal.

To reset the database:
  Delete crm.db and run `python seed.py` again.

Common first-run issues:

  "Address already in use"
    Another process is on port 5000.
    Change the port in app.py:
    app.run(port=5001)
    Then change API_BASE in frontend/index.html.

  "flask not found"
    You forgot to activate the venv,
    or didn't run pip install.

  "CORS error"
    The frontend and backend are on
    different origins. flask-cors handles
    this if it's installed. If you still
    see it, add the origin to CORS:
    CORS(app, origins=['http://localhost:3000'])

  "Could not load customers"
    The server isn't running.
    Start it with `python app.py`.

Full instructions in the README.md
in the download folder.

✅ The full project

Seven files. Copy them, or recreate them with the script below.

Stage 03 is a folder, not a single file. It has a Python backend (5 files) and a frontend (1 HTML file), plus a README. Copy each file to the exact path shown. Or run the script at the bottom of this section to recreate the whole folder structure in one command.

Files to save:
Path Purpose
backend/app.py Flask app + 4 REST routes
backend/database.py SQLite connection management
backend/schema.sql The customers table
backend/seed.py Sample data loader
backend/requirements.txt Flask, flask-cors
frontend/index.html The CRM UI (fetch-based)
README.md Setup instructions

Option A: Copy each file manually

Each file is in a code block above (Files 1–7). Save each one to the path shown in the table. The folder structure must be:

folder structure
crm-stage-03/
├── backend/
│ ├── app.py
│ ├── database.py
│ ├── schema.sql
│ ├── seed.py
│ └── requirements.txt
├── frontend/
│ └── index.html
└── README.md

Option B: Recreate the folder in one command

If you'd rather not copy-paste seven files one at a time, run this shell script. It creates the folder structure and writes a placeholder into each file — you then copy the actual code from the code blocks above into each file. (A fully-populated script would be ~500 lines; the placeholder version is faster and you still get working files once you paste the code.)

terminal
mkdir -p crm-stage-03/backend crm-stage-03/frontend
cd crm-stage-03

touch backend/app.py
touch backend/database.py
touch backend/schema.sql
touch backend/seed.py
touch backend/requirements.txt
touch frontend/index.html
touch README.md

echo "Folder structure created. Now paste the code from each code block above into the matching file."
ls -R

Note on the download link. The site doesn't currently host zip files for the CRM downloads — the code is delivered as inline code blocks that you copy into your own files. If you want a one-click download, you'd need to:

  1. Create a crm-stage-03.zip file locally (right-click the folder → Compress).
  2. Upload it to /apps/crm/downloads/crm-stage-03.zip on the site.
  3. Update the link in this section to Download the folder.

That would work, but it requires uploading a binary to the site. The inline code approach is what the previous stages use, and it works today without any additional hosting.

Verify the project works

Once all seven files are in place, run:

terminal
cd crm-stage-03/backend
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt
python seed.py
python app.py

Expected output: Running on http://127.0.0.1:5000. Open that URL in a browser. If you see 5 customers and can add/edit/delete them, everything works.

Real database

SQLite stores the data in crm.db. Copy the file to back up. Delete it to reset.

Real HTTP

Every CRUD operation is a network request. The UI shows loading and errors.

Real validation

The server validates name and email, returns 400 with an error message.

Real portability

Runs on Windows, macOS, Linux. Same code. Same setup.

⚠️ Common mistakes

What to check if the app doesn't work.

1. Forgetting await on fetch()

fetch() returns a promise, not data. Fix: const res = await fetch(...); const data = await res.json();.

2. Missing Content-Type header on POST/PUT

The server gets None from request.get_json(). Fix: headers: { 'Content-Type': 'application/json' }.

3. Not checking res.ok

4xx and 5xx responses are not thrown by fetch. Fix: if (!res.ok) throw ....

4. Using fetch URL without the base

The request goes to the wrong origin. Fix: API_BASE + path, with API_BASE = 'http://127.0.0.1:5000' locally.

5. CORS not enabled

Browser blocks the request. Fix: CORS(app) in app.py.

6. Database file not created

Table doesn't exist. Fix: run python seed.py (which calls init_db()) before the first request.

7. Body is not JSON on the server

request.get_json() returns None. Fix: request.get_json(force=True) or ensure the header is set.

8. Port 5000 already in use

The server won't start. Fix: change to port 5001 and update API_BASE.

❓ Frequently asked

What will I build in Stage 03?

The same CRM UI, but now talking to a Python Flask backend with a SQLite database. Customers persist in a real database file, not localStorage. The frontend calls a REST API at /api/customers to read and write data. You run two things locally: a Flask server on port 5000, and the frontend served by Flask itself.

Why move from localStorage to a backend?

Three reasons. (1) localStorage is per-device: two users on two computers see two different datasets. A backend shares data across users. (2) localStorage is limited to ~5MB. (3) localStorage is synchronous: every write blocks the UI. A backend is async. This stage teaches the transition from a toy app to a real one.

Why Flask and SQLite instead of Django and Postgres?

Because the goal is to teach the concepts, not to build a production system. Flask is a minimal framework: one file for the whole app. SQLite is a single-file database with no server. Together they fit in your head. Learn the concepts here, and the transition to Django and Postgres is straightforward.

Why async/await for the fetch calls?

Because network requests take time. Without await, the code that uses the result runs before the response arrives. The pattern is: async function loadCustomers() { const data = await fetchCustomers(); state.customers = data; render(); }. If the fetch fails, the try/catch catches it, and the UI shows an error.

What is CORS and why do I need it?

CORS (Cross-Origin Resource Sharing) is a browser security rule. If the frontend is served from http://127.0.0.1:5000 and the API is on the same origin, no problem. But if you serve the frontend from a separate dev server, the browser blocks the fetch. flask-cors adds the headers that tell the browser to allow it. For production, you serve both from the same origin.

Where is the database stored?

In a file called crm.db in the backend/ folder. SQLite stores the entire database in a single file. Copy that file to back up the database. Delete it to reset. No server process, no configuration, no credentials.

🔗 Continue learning

🖥️ Next: Stage 04 — Desktop
Rebuild the CRM as a native desktop app with PySide6. Package it as an installable .exe or .app. 2–3 hours.

Or explore related Python concepts:

🐍 Python problem solvers
15 real answers to real queries — dicts, files, copy, slicing, and more
← Back to CRM build hub
CRM Stage 03 · Backend