technical case study

SaaS • Lead generation / Mini CRM

Leads Karpaz

MVP for finding, ingesting, organizing and analyzing leads, with a mini CRM structure and a path for commercial growth.

Product, architecture and development Mature MVP Product maturing
Python FastAPI MySQL JavaScript Bootstrap
executive summary

Problem

Manual prospecting produces scattered, inconsistent data that is hard to prioritize, reducing sales efficiency.

Solution

An MVP was built to capture, organize and query leads in a mini CRM structure, ready for filters, classification and a growing sales workflow.

Result

The system created a central place to review opportunities and validate prospecting processes with more clarity and repeatability.

Current stage

Mature MVP, ready to expand with scoring, automations and commercial integrations.

context

Turning prospecting into a process

Leads Karpaz came from the need to organize sales opportunities more objectively. Instead of relying on loose searches and disconnected spreadsheets, the project centralizes data, makes leads easier to read and paves the way for automation and smart prioritization.

goals and constraints

Goals

  • Centralize leads in an organized database
  • Reduce manual prospecting and checking
  • Create an initial mini CRM structure
  • Prepare the product for filters, scoring and future integrations

Constraints

  • Avoid excessive complexity in the first version
  • Keep data organized and easy to query
  • Separate lead capture, processing and display
  • Allow gradual evolution without rewriting the core
solution architecture

Front-end

Web interface organized into panels, lists and visual states that make leads easy to read, filter and follow up.

Back-end

Python/FastAPI API that receives, processes and serves lead data in a structured way.

Database

MySQL schema storing leads, commercial attributes, statuses and information useful for analysis and for growing the CRM.

security and safeguards

Data organization

Ingestion, persistence and display are separated to reduce inconsistencies and ease maintenance.

Input validation

Incoming data is normalized before being used in the sales workflow.

Foundation for future controls

The architecture allows adding authentication, permissions and audit trails in later stages.

process
01

Sales pain

Identifying how hard it was to keep leads organized and actionable in a manual process.

02

Organization MVP

Building the base to store and query leads in a more predictable format.

03

Reading layer

Screens and filters that turn data into a useful commercial view.

04

Product expansion

Preparing for scoring, automations, integrations and more advanced prospecting routines.

behind the scenes
app/main.py Python lines 116–156
# ---------- APIs protegidas ----------
@app.post("/api/search")
async def api_search(request: Request, payload: dict, _user=Depends(require_user)):
    rate_limit(request, key="search", limit_per_min=15)

    try:
        comercio = (payload.get("comercio") or "").strip()
        local_mode = payload.get("local_mode")  # "ddd" | "city"
        local_value = (payload.get("local_value") or "").strip()

        if not comercio or local_mode not in ("ddd", "city") or not local_value:
            return JSONResponse({"error": "Campos inválidos"}, status_code=400)

        rows, stats = fetch_and_store_new(comercio, local_mode, local_value, max_new=50)
        return {"rows": rows, "stats": stats}

    except Exception as e:
        return JSONResponse({"error": str(e)}, status_code=500)

@app.get("/api/leads")
def api_leads(
    status: int = 1,
    q: str | None = None,
    city: str | None = None,
    ddd: str | None = None,
    tags: str | None = None,
    limit: int = 200,
    offset: int = 0,
    _user=Depends(require_user)
):
    tag_names = [t.strip().lower() for t in (tags or "").split(",") if t.strip()]
    rows = list_leads(
        status=status,
        q=q,
        city=city,
        ddd=ddd,
        tag_names=tag_names,
        limit=limit,
        offset=offset
    )
    return {"rows": rows, "count": len(rows)}
Authenticated routes with rate limiting, input validation and filters on lead queries.
app/schema.mysql.sql SQL lines 1–40
CREATE TABLE IF NOT EXISTS places (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  place_id VARCHAR(128) NOT NULL,
  name VARCHAR(255) NULL,
  phone VARCHAR(64) NULL,
  stars DECIMAL(3,2) NULL,
  maps_url TEXT NULL,
  website TEXT NULL,
  city VARCHAR(128) NULL,
  ddd VARCHAR(16) NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_places_place_id (place_id),
  KEY idx_places_status (status),
  KEY idx_places_city (city),
  KEY idx_places_ddd (ddd),
  KEY idx_places_created_at (created_at),
  CONSTRAINT chk_places_status CHECK (status IN (1,2,3))
);

CREATE TABLE IF NOT EXISTS tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tags_name (name)
);

CREATE TABLE IF NOT EXISTS place_tags (
  place_id BIGINT UNSIGNED NOT NULL,
  tag_id   BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (place_id, tag_id),
  KEY idx_place_tags_tag_id (tag_id),
  CONSTRAINT fk_place_tags_place
    FOREIGN KEY (place_id) REFERENCES places(id) ON DELETE CASCADE,
  CONSTRAINT fk_place_tags_tag
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
Schema with a unique place_id, indexes on status, city and area code, and tags in an N:N relationship with cascading deletes.
app/static/crm.js JavaScript lines 163–208
function buildQuery() {
  const q = $("q").value.trim();
  const city = $("city").value.trim();
  const ddd = $("ddd").value.trim();
  const tags = filterTags.join(",");

  const p = new URLSearchParams();
  p.set("status", String(currentStatus));
  if (q) p.set("q", q);
  if (city) p.set("city", city);
  if (ddd) p.set("ddd", ddd);
  if (tags) p.set("tags", tags);
  p.set("limit", "200");
  p.set("offset", "0");

  return "/api/leads?" + p.toString();
}

async function load(showOkLog=true) {
  const url = buildQuery();
  setLog("Carregando leads...", "info");

  try {
    const res = await fetch(url);
    const data = await res.json();
    if (!res.ok) {
      setLog(data.error || "Erro ao listar", "err");
      return;
    }
    lastRows = data.rows || [];
    renderTable(lastRows);

    if (showOkLog) {
      setLog(`OK: ${data.count} leads em "${statusLabel(currentStatus)}".`, "ok");
    }
  } catch {
    setLog("Falha de rede/servidor.", "err");
  }
}

// Debounce de busca
let tmr = null;
function debounceLoad() {
  clearTimeout(tmr);
  tmr = setTimeout(() => load(), 250);
}
Filters become a query string and the search is debounced so it doesn't fire a request on every keystroke.
impact

Results

Leads Karpaz brought sales opportunities into a clearer structure, laying the groundwork for analysis, prioritization and a better prospecting process.

Learnings

The project reinforced that a useful SaaS starts by solving one specific step of the process well, before trying to cover the whole sales workflow.