DEV Community: Zhang Lixiang

Sorting Bilibili videos with Python: a 20-result keyboard research sample

Zhang Lixiang — Wed, 04 Nov 2026 11:47:44 +0000

Half of the 20 videos in my Bilibili search sample had fewer than 1,000 views. Nine titles mentioned magnetic switches. Those two details mattered when deciding which videos to review: the results mixed different levels of reach and different kinds of keyboards.

The search term was 机械键盘, Chinese for "mechanical keyboard". I exported the results as JSON and used Python to make a review list. The script below needs only the standard library.

I developed the Bilibili Scraper used to collect this sample and sell it on Apify. You can also run the analysis on data you have permission to use, provided it has the same fields.

Collect a small sample

This example task searches for 机械键盘, sorts by relevance, and returns up to 20 video records. It doesn't use a proxy or fetch extra video details. You don't need a Bilibili login or cookies; you do need an Apify account to copy and run the task.

The input is:

{
  "searchKeywords": ["机械键盘"],
  "maxResultsPerKeyword": 20,
  "searchOrder": "totalrank",
  "enrichSearchResults": false,
  "includePopular": false,
  "proxyConfiguration": {"useApifyProxy": false}
}

Copy the task after signing in, change the keyword if needed, and run it. From the output page, select Export and download JSON as keyword-demo-results.json. CSV and Excel exports are available too.

The run on November 4, 2026 took about six seconds and returned 20 distinct video URLs. Each record had a title, creator name, view count, likes, favorites, and publication time. That timing describes one small run; it doesn't establish performance for other keywords or larger batches, which may hit rate limits.

The tool costs $0.002 per video or creator profile, or $2 per 1,000 records, with platform usage included. At that rate, 20 video results cost $0.04. My own development run is not a customer purchase, and its rounded cost display is not evidence that the product is free.

Make a review list

For this sample, I filtered out records with fewer than 1,000 views, then sorted the rest by favorites divided by views. The cutoff reduces the effect of very small denominators. It is a choice for this demonstration, not a general research standard.

Save this script beside the JSON file and run it with Python:

import csv
import json
from pathlib import Path

rows = json.loads(Path("keyword-demo-results.json").read_text(encoding="utf-8"))
review = []

for row in rows:
    views = row.get("views")
    favorites = row.get("favorites")
    if not isinstance(views, (int, float)) or views < 1000:
        continue
    if not isinstance(favorites, (int, float)) or favorites < 0:
        continue
    review.append({
        "title": row["title"],
        "authorName": row["authorName"],
        "views": views,
        "favorites": favorites,
        "favorite_pct": round(100 * favorites / views, 2),
        "publishedAt": row["publishedAt"],
        "url": row["url"],
    })

review.sort(key=lambda row: row["favorites"] / row["views"], reverse=True)

with open("review.csv", "w", encoding="utf-8-sig", newline="") as stream:
    writer = csv.DictWriter(stream, fieldnames=[
        "title", "authorName", "views", "favorites",
        "favorite_pct", "publishedAt", "url",
    ])
    writer.writeheader()
    writer.writerows(review)

print(f"Rows to review: {len(review)}")

This sample produces 10 rows in review.csv. The first three are shown below. Creator names remain in Chinese so they match the source records. Bilibili calls creators "UP主"; here they are stored in authorName.

Creator	Views	Favorites	Favorites / views
键盘推荐2026	156954	3133	2.00%
搞机研玩社	119813	2311	1.93%
歪设长	337386	3818	1.13%

These counts are a snapshot from the collection time. The percentage is calculated locally. It isn't an official Bilibili conversion rate, a measure of buying intent, or a sales estimate. It gives me an order in which to open the links.

Review the videos behind the numbers

For product research, I would open each URL and record the price range, keyboard layout, switch type, and use case being discussed. A title that says "review" or "tested" still needs checking against the video itself. Sponsorship also needs a separate check.

The nine titles mentioning magnetic switches tell us that this search includes that category. They don't tell us its market share. Relevance-ranked search results are not a random sample, and 20 records are too few for a conclusion about the whole keyboard market.

If the purpose is creator research, the CSV is only a starting point. Recent uploads, audience fit, and public contact information need separate review. I kept publishedAt because an older video has had more time to accumulate views than a new one.

For recent content, change searchOrder to pubdate and collect another sample. I haven't run that second query for this article. When combining searches, deduplicate by bvid or URL before counting videos.

Missing fields and collection limits

Search results can have null values for coins and shares. Missing is not zero. The optional enrichSearchResults mode fetches extra details and makes more requests; this demonstration did not test that mode.

The tool collects public video metadata. It does not download videos or collect comment text, members-only content, or other non-public material. Public visibility does not grant unrestricted collection, redistribution, or commercial-use rights. Check the platform's terms, your data permissions, and applicable law before collecting. Reduce the scope or stop when access is restricted; don't try to bypass verification challenges.

The tool page lists its fields and input options. For a different research question, decide which fields you need and what you will check manually before increasing the result limit.

The “quick change” that quietly breaks a freelance project

Zhang Lixiang — Tue, 03 Nov 2026 16:00:35 +0000

Scope creep rarely starts with a dramatic request. It’s often one extra field, one more version, or another review round. The request itself isn’t the problem; the problem is doing it before both sides agree what changes.

A simple habit helps:

Check the request against the latest approved scope. Separate a clarification or correction from a new deliverable or revision.
Name the change plainly: what’s being added, how many revisions it involves, and whether it affects timing or dependencies.
Offer clear options: swap it for something already in scope, add it for an agreed fee and schedule, or move it to a later phase.
Get written approval before starting, then update the project record.

A reply could be:

“Thanks for asking. I checked this against [scope/version]. [Request] is outside [agreed item]. I can swap it for [item] within the current plan, add it for [fee] and move delivery to [date], or put it in a later phase. Which works best? I’ll update the plan once we agree.”

This isn’t about turning every small request into bureaucracy. It’s about making the trade-off clear before either person is surprised.

Disclosure: I sell a project-boundaries toolkit on Gumroad. Its free 2-page sample includes a client email, a change-request record, and a checklist. It’s an educational workflow aid, not a contract or legal advice.

https://lexang.gumroad.com/l/ifqwwp

My agent ran prisma migrate reset. Here's the hook I wrote so it can't again

Zhang Lixiang — Mon, 02 Nov 2026 13:17:18 +0000

Last week I wrote about pgbrake, an MCP server that makes an agent's SQL writes undoable. People pointed out the obvious hole: agents don't only touch the database through SQL. They also run terminal commands, and prisma migrate reset --force wipes everything without a single SQL statement going through the MCP server.

pgbrake 0.2.0 adds a Claude Code PreToolUse hook for that. It watches Bash commands for migration tools doing destructive things: prisma migrate reset --force, drizzle-kit push --force, alembic downgrade base and similar. When it sees one, it takes a pg_dump snapshot first (if PGBRAKE_DSN is set), then stops and asks you.

pgbrake install-hook

That's the whole setup.

The hook is part of Pro, which is $29 one-time for the first 100 licenses, then $39. No subscription, 14-day refund, and the key is checked offline, so pgbrake never phones home. Pro also lifts the free tier's 20-change undo limit and adds pgbrake log --export csv|json.

The SQL side (undo, approvals, audit log) stays free and MIT.

What it doesn't do yet: Codex hooks, a policy file for custom rules, and online key revocation. If one of those would decide whether you'd use it, tell me which.

https://github.com/lexang/pgbrake
https://lexang.github.io/#pricing

Sorting website form enquiries with an LLM that can only answer from a list

Zhang Lixiang — Thu, 29 Oct 2026 02:01:56 +0000

Contact forms collect everything: real sales leads, support questions, partnership pitches, job applications and a steady drip of spam. Someone has to read each one and decide where it goes. An LLM can do that sorting, but if you let it answer freely you get categories like "Potential Sales Inquiry (High Value)" one day and "sales lead" the next, and your spreadsheet filters stop working.

So I built a small n8n workflow where the model gets a fixed list of category ids and may answer with one of them or null. Anything else is treated as a failure.

How it runs

Form POST -> Webhook (name, email, company, message)
  -> Normalize; no valid email -> HTTP 400, nothing logged
  -> LLM sees: category ids + one-line descriptions, and the enquiry
  -> Code node: is the reply exactly one of my ids?
       yes -> that category
       no (null, unknown id, a sentence, API error) -> needs_human
  -> Append one row to Google Sheets
  -> Webhook answers 200 with the category

The category list lives in one Code node called Settings (edit me), something like sales_inquiry, support, partnership, job_application, spam, other, each with a short description and a priority. The prompt and the check both read from that node, so you can't end up with a prompt that mentions a category the check doesn't know.

The check is a few lines of code

The model's reply gets trimmed, lowercased, and stripped of quotes and a trailing full stop. "Partnership." therefore still counts as partnership. After that it has to match an id exactly. A reply like "This looks like a partnership request" fails, even though it contains a valid id, because I don't want to guess what the model meant. It goes to needs_human with the reason written in a check_notes column.

An HTTP 500 from the model API goes down the same path. The webhook still answers 200 and the row is still written, so a flaky API doesn't lose enquiries.

One more thing that tripped me up in an earlier workflow: form text can contain {name} or {{ $json.x }}, and n8n's LangChain nodes treat braces as template syntax. The message needs escaping before it goes into the prompt. In this workflow, text with braces is stored in the sheet exactly as sent.

Testing

I ran it on a real n8n 1.123.87 instance with local stand-ins for the OpenAI and Google Sheets APIs, and 17 checks passed: correct categories logged for sales and support enquiries, form-encoded and JSON bodies, null / invented id / sentence / HTTP 500 all ending up as needs_human, braces surviving, missing or invalid emails getting a 400 with no row and no model call, exactly one row per valid request, and no other hosts contacted.

What I haven't tested: a real model and a real sheet. The checks show that a correct answer gets through and a bad one doesn't, not how well a given model sorts your enquiries. The webhook also has no authentication or dedupe, so add those before you put it on a public form.

Get it

This workflow is in a free bundle with two others (a WhatsApp FAQ bot that can only send stored answers, and a B2B cold-email drafter that never sends anything): 3 free n8n AI workflows, pay what you want, $0 is fine.

If you need the WhatsApp side done properly, with signature checks, dedupe, opt-out and human handoff, the full kit is $19.

pgbrake: an undo button for Postgres when an AI agent is driving

Zhang Lixiang — Wed, 28 Oct 2026 03:52:37 +0000

I've started letting Claude Code and Codex work against my dev databases, and the part that worried me was the database itself. Claude Code checkpoints rewind files. A bad DELETE stays done. Permission prompts help, but they live in the client, and an agent can always reach for psql instead.

pgbrake is a small MCP server the agent uses in place of direct database access. Every statement goes through the real Postgres parser (pglast). Normal writes run and return a change id. Before an UPDATE or DELETE it saves the affected rows locally, so pgbrake undo <id> puts them back, and it refuses if those rows changed since.

DDL, UPDATE/DELETE without WHERE, and anything touching more than 100 rows wait for a human: the client's own prompt, or pgbrake approve <id> in a terminal. That command refuses to run without a TTY, so the agent can't approve its own SQL. Every statement and decision lands in a local audit log. Nothing leaves your machine.

MIT, Python:

pip install pgbrake
claude mcp add pgbrake --env PGBRAKE_DSN="postgresql://me@localhost/app" -- uvx pgbrake serve

Limits: undo only covers changes made through pgbrake. TRUNCATE and DDL can be held but not undone. It doesn't replace backups. I've only tested on Postgres 17 so far.

I'd like to hear where it breaks. I'm also thinking about a paid add-on that catches migration commands like prisma migrate reset --force (snapshot first, then ask). Would that be worth a one-time $29 to you?

GitHub: https://github.com/lexang/pgbrake

n8n orchestrates, LangGraph writes, a human approves: an AI cold-email pipeline that can't spam

Zhang Lixiang — Sun, 25 Oct 2026 13:11:37 +0000

"AI SDR" demos usually end with an agent firing off a few hundred emails. In real B2B sales that burns your domain within a week, and the emails are full of claims nobody can back up.

I built a lead-generation pipeline around an export manufacturer's use case, with three rules. Every claim in an email has to come from data the APIs returned, quoted verbatim. A rule checker rejects bad drafts and sends them back for a rewrite, at most twice. And nothing is sent until a person clicks Approve.

The part I found most useful was deciding what goes in n8n and what goes in LangGraph.

n8n does the plumbing

The n8n side is 55 nodes and handles everything deterministic: manual, schedule and webhook triggers; Apollo to find people and enrich emails; Hunter to verify them; dedupe against the CRM and qualification filters; HubSpot or a Google Sheet as the CRM; an approval link per draft, an unsubscribe link, and a suppression list that is checked again right before sending. Sending goes through SendGrid, Gmail or SMTP, behind a SEND_ENABLED flag.

LangGraph does the parts that need a model

A small FastAPI service runs the graph:

research -> write -> check -> (rewrite, max 2) -> draft for approval

The research step writes a short note about the company. Every reason in it must carry a verbatim quote from the lead data, otherwise it's cleared. The write step drafts the email from that note and nothing else.

The check step is plain code. It blocks numbers that don't appear in the research or the approved facts, unsupported claim phrases, placeholders and empty fields, a missing unsubscribe line, and drafts that run too long. A failing draft goes back to a rewrite step with the checker's reasons. After two failed rewrites it's flagged needs_human_fix and never sent.

The model can be any OpenAI-compatible endpoint: OpenAI, DeepSeek, Qwen, OpenRouter.

I made the checker code rather than a second LLM because an LLM grading an LLM tends to agree with itself. A regex and a list of allowed numbers don't. It's dull, and it's the reason I'd trust the drafts enough to put them in front of a salesperson.

Switching industries

Target industry, buyer type, product lines and selling points live in one profile file, and a small script turns it into the n8n variables. The kit includes a battery-storage example and an LED-lighting example.

Testing

There are 21 pytest tests for the LangGraph service and 8 JS tests for the n8n Code nodes. A live run on a real n8n instance (1.123.87) with local mock Apollo, Hunter, HubSpot and SendGrid passed 26 of 26 checks. Ten fake leads end to end gave 6 drafts waiting for approval, 2 disqualified, 1 deduped and 1 blocked by the unsubscribe list.

Those runs used mocked vendor APIs and a deterministic fake model, so I haven't seen how drafts look with a real model and real Apollo data. You bring your own Apollo, Hunter, HubSpot, SendGrid and LLM keys.

The template

The full kit (importable n8n workflow, LangGraph + FastAPI service, Docker Compose, tests, sample profiles) is $29 on Gumroad: B2B AI Lead Generation: n8n + LangGraph template.

I also wrote about a WhatsApp bot where the LLM may only pick an FAQ id. There's a free version in the n8n template library (workflow 21700) and a full kit for $19.

How I stopped my n8n WhatsApp bot from inventing prices (the "LLM picks an ID" pattern)

Zhang Lixiang — Sun, 25 Oct 2026 13:06:50 +0000

Most "AI WhatsApp bot" tutorials pass the customer's message to an LLM and send back whatever it writes. Fine for a demo. For a real small business it's a liability, because the model will quote a price, a warranty or an opening hour that doesn't exist.

I built a WhatsApp FAQ bot for small service businesses (the sample data is an aircon-servicing company) with one rule: the LLM never writes the reply. It only gets to pick which stored answer to send.

The LLM picks an ID

The FAQ lives in a table, a Google Sheet or a CSV, with id, question, keywords, answer.

Every message first goes through plain keyword scoring. Most messages ("how much for a general service?") match without any AI, which is fast and costs nothing. Only when nothing matches does the bot send the question plus the list of FAQ ids and questions to the LLM, asking for one id or null and nothing else.

The response is then checked. If it isn't exactly a known id, it counts as unknown. A valid id means the stored answer goes out word for word. Unknown, null, free text or an API error all trigger a human handoff: the customer gets one "a staff member will reply" message, the owner gets a WhatsApp alert, and the bot stays quiet in that chat until someone resumes it.

So the worst case is a human answering a question the bot could maybe have handled. I'll take that over a made-up price.

The rest of the flow

Customer message -> Webhook (GET verify / POST messages)
  -> X-Hub-Signature-256 HMAC check (bad signature -> 401, nothing processed)
  -> Dedupe on message id (Meta retries webhooks)
  -> STOP / START opt-out handling
  -> Chat in human handoff? -> stay silent
  -> Asked for "agent / human" or sent a photo/voice note? -> handoff
  -> FAQ match (keywords) -> fallback: LLM picks an FAQ id
  -> Reply via Graph API POST /{phone_number_id}/messages

Some things that bit me:

Check the signature before anything else. Meta signs every webhook with your app secret, and in n8n you need the raw body for the HMAC, so turn on the raw body option in the Webhook node.

Dedupe on the message id. Meta retries when you're slow, and without dedupe a customer gets the same answer two or three times.

Handle STOP and START with a confirmation message. Opted-out chats get nothing after that, reminders included.

I kept the Code node logic in separate .js files and generated the workflow JSON from them, so the logic could be unit tested outside n8n. The workflow then passed a 16/16 live run on a real n8n instance against a mocked Graph API.

Booking reminders

The same workflow can send an approved WhatsApp template to customers whose booking starts within 24 hours, once per booking, skipping anyone who opted out. Outside the 24-hour customer-service window WhatsApp only allows template messages, so it has to be a template.

Code

A free, smaller version (keyword matching plus the OpenAI id-picking fallback) is in the official n8n template library: Answer WhatsApp FAQs with keyword matching and an OpenAI fallback.

The full kit adds signature checks, dedupe, opt-out, handoff and reminders, plus an equivalent Python FastAPI service (SQLite state, 31 pytest tests), a Docker + Caddy HTTPS setup and a step-by-step Meta Cloud API guide. It's $19 on Gumroad: WhatsApp AI Customer Service Bot kit.

For B2B outreach there's also an n8n + LangGraph lead-generation template where nothing is sent until you click Approve: B2B AI Lead Generation kit, $29.

The id-picking trick isn't specific to WhatsApp. I'd use it for ticket routing or intent classification too: anywhere you can limit the model's output to a set you can check.