Mert

If you are a developer who has ever been handed credentials to run a service on a Windows domain, you have probably contributed to a security problem you did not know existed. I am not saying this to be condescending — I did not understand Kerberoasting until I started doing Active Directory security reviews professionally. Now I find the same three misconfigurations in almost every organization I assess.

This post explains Kerberoasting plainly for people who write code but do not specialize in Active Directory security. No acronym soup, no red team posturing.

What is Kerberos, briefly

When you log into a Windows domain, Kerberos handles authentication. You prove your identity to a central server (the Key Distribution Center, or KDC) and receive a Ticket Granting Ticket (TGT). When you need to access a specific service — a SQL Server, a web application, a file share — your client uses that TGT to request a Service Ticket (TGS) for that specific service.

The service is identified by a Service Principal Name (SPN). An SPN is a string like MSSQLSvc/sqlserver01.corp.local:1433 registered in Active Directory against the account that runs that service.

Here is the key point: the TGS is encrypted with the NTLM hash of the service account’s password. The KDC does not check whether the requesting user is authorized to use the service — that check happens when the user actually presents the ticket to the service. Any authenticated domain user can request a TGS for any SPN in the domain.

What Kerberoasting actually is

Kerberoasting is requesting TGS tickets for service accounts and taking those encrypted tickets offline to crack them. Since the ticket is encrypted with the service account’s password hash, cracking it gives you the plaintext password.

There is nothing exotic about this. Any domain user can do it. No elevated privileges required for the initial step.

# Find accounts with SPNs (potential Kerberoasting targets)
# Run this as any domain user
Get-ADUser -Filter {ServicePrincipalName -ne "$null"} `
    -Properties ServicePrincipalName, PasswordLastSet, Description |
    Select-Object SamAccountName, ServicePrincipalName, PasswordLastSet, Description |
    Where-Object { $_.SamAccountName -ne "krbtgt" } |
    Sort-Object PasswordLastSet

# Request and export TGS tickets (legitimate Windows API call)
Add-Type -AssemblyName System.IdentityModel
$spns = @("MSSQLSvc/sqlserver01.corp.local:1433")
foreach ($spn in $spns) {
    New-Object System.IdentityModel.Tokens.KerberosRequestorSecurityToken `
        -ArgumentList $spn
}
# Tickets now in memory, exportable with tools like Mimikatz or Rubeus

Once you have the .kirbi ticket file, you take it to a machine with GPU power and run Hashcat against it. A service account with password Summer2023! falls in seconds.

The three misconfigurations developers introduce

1. Running applications as domain admin accounts

This is the most damaging one. A developer needs their application to read from Active Directory or write to a shared folder. IT gives them a domain admin account to “make sure it works.” The application runs, it works, everyone moves on.

Now that domain admin account has an SPN registered against it (so Kerberos can issue tickets for the service), and if its password is weak or old, an attacker who compromises any domain account can request that ticket and crack it offline — resulting in full domain compromise.

The fix: create a dedicated service account with only the permissions the application actually needs. Nothing more. This sounds obvious but it requires someone to actually think through what “the permissions the application actually needs” means, which often requires back-and-forth with the developer. This conversation frequently does not happen.

2. Service account passwords that never change

Active Directory, by default, does not enforce password rotation for service accounts the way it does for user accounts. A service account created in 2018 with a password set during the Obama administration is still out there in most organizations I assess.

The modern solution is Group Managed Service Accounts (gMSA). These are accounts where Active Directory manages the password automatically, rotating it every 30 days. The password is 120 characters of random bytes. Kerberoasting a gMSA account is computationally impossible with current hardware.

# Create a gMSA (run as domain admin)
New-ADServiceAccount -Name "svc-myapp" `
    -DNSHostName "svc-myapp.corp.local" `
    -PrincipalsAllowedToRetrieveManagedPassword "WebServers$"  # AD group

# Install on the target server
Install-ADServiceAccount -Identity "svc-myapp"

# Test it
Test-ADServiceAccount -Identity "svc-myapp"

Most applications support gMSA. The ones that do not tend to be legacy applications that have other problems.

3. No tiering — everything on one flat network/domain

Active Directory tiering (also called the administrative tier model) separates accounts and systems into tiers: Tier 0 (domain controllers, PKI), Tier 1 (servers and applications), Tier 2 (workstations and end-user devices). Accounts in Tier 2 should never be able to touch Tier 0 systems.

When everything is flat, a Kerberoastable service account on a developer’s web application server can be the entry point to full domain compromise if that account has been granted excessive permissions “just in case.”

I have documented the most common AD misconfigurations and their remediation steps in detail in the Active Directory security checklist I publish — it covers SPNs, password policies, Kerberos delegation settings, and tiering controls.

Detection

If someone is Kerberoasting your domain, it generates Event ID 4769 (Kerberos Service Ticket Request) with encryption type 0x17 (RC4-HMAC). Modern environments should be using AES-256 (encryption type 0x12). A burst of 4769 events with RC4 from a single account is a strong signal.

Enable auditing of Kerberos service ticket operations and forward logs to your SIEM. This costs nothing except log volume.

The honest assessment

Kerberoasting gets patched at the detection layer more often than at the root cause. Organizations add SIEM rules to flag it rather than eliminating Kerberoastable accounts. The root cause fix — gMSA everywhere, no shared passwords, proper tiering — requires coordination between developers and IT that many organizations are not set up to do efficiently.

As a developer, the practical thing you can do is: when you need a service account, request a gMSA and ask why if someone tries to give you a regular user account with a static password instead. That one question, asked consistently, changes the security posture of the systems you build.

I run AYI NEDJIMI Consultants, a cybersecurity consulting firm. We publish security hardening checklists for FortiGate, Palo Alto, Active Directory, and more — free PDF and Excel.

I distrust SEO tools. Not because they are wrong, but because they are generic. They optimize for the average page, not your specific content and audience. When I was running a cybersecurity consulting site with over 1,600 technical articles, I needed scoring rules that made sense for long-form, technical content — not e-commerce product pages.

So I built my own SEO scoring engine in Go, embedded directly in the backend. It runs on every article save and surfaces scores through an internal API. Here is what I actually learned.

Why build it yourself

External SEO tools are either expensive, rate-limited, or both. More importantly, they cannot score your content against your own standards. My articles needed to satisfy criteria that Yoast or Clearscope cannot express: a minimum 120-word opening paragraph (the “chapeau”), a FAQ section with interrogative headings, at least 4 internal links pointing to related guides, and a specific H2-to-word-count ratio.

These rules came from watching traffic data for 18 months. They are opinionated and they are mine.

The Unicode problem Go developers always hit

Before I explain the rules, here is the most important technical thing I learned: len() in Go counts bytes, not characters. For SEO character counting, this matters because a lot of cybersecurity content includes accented French characters, special symbols, and technical abbreviations.

// Wrong — counts bytes, not characters
func titleTooShort(title string) bool {
    return len(title) < 50 // WRONG for non-ASCII content
}

// Correct — counts Unicode code points
func titleLength(title string) int {
    return len([]rune(title))
}

func scoreTitleLength(title string) (int, string) {
    l := len([]rune(title))
    switch {
    case l < 30:
        return 0, "title too short (< 30 chars)"
    case l < 50:
        return 5, "title acceptable but below optimal (50–60 chars)"
    case l <= 60:
        return 10, "title length optimal"
    case l <= 70:
        return 5, "title slightly long (> 60 chars)"
    default:
        return 0, "title too long (> 70 chars)"
    }
}

[]rune(s) converts a string to a slice of Unicode code points. len() on that slice gives you what you actually want. I have seen Go codebases where someone reimplemented this logic with a regex or a byte loop — just use []rune.

The 8 rules

1. Title: 50–60 characters

Shorter titles get truncated less often in SERPs and tend to rank better, but too short and you waste keyword space. I target 50–60 runes (Unicode characters). Scores below 30 characters get zero points — those are usually placeholder titles left in by mistake.

2. Meta description: 140–160 characters

The classic rule. More important than people think not for ranking directly, but for click-through rate, which does affect ranking indirectly. I reject descriptions that are copy-pasted from the first sentence of the article without being rewritten for a SERP context.

3. Chapeau (opening paragraph) ≥ 120 words

This is the most impactful rule I added. The “chapeau” is the first <p> tag in the article body. Search engines weight early content heavily. I found that articles with a dense, information-rich opening paragraph consistently outranked shorter-intro versions of similar content.

Extracting and counting it in Go:

import "golang.org/x/net/html"

func extractChapeauWordCount(body string) int {
    doc, err := html.Parse(strings.NewReader(body))
    if err != nil {
        return 0
    }
    var count int
    var findFirst func(*html.Node) bool
    findFirst = func(n *html.Node) bool {
        if n.Type == html.ElementNode && n.Data == "p" {
            text := extractText(n)
            words := strings.Fields(text)
            count = len(words)
            return true // stop after first <p>
        }
        for c := n.FirstChild; c != nil; c = c.NextSibling {
            if findFirst(c) {
                return true
            }
        }
        return false
    }
    findFirst(doc)
    return count
}

4. H2 to word ratio ≤ 1 H2 per 350 words

Too many subheadings fractures content into fragments that search engines cannot contextualize. Too few and you miss structural signals. The ratio ≤ 350 words per H2 means a 1,400-word article should have no more than 4 H2 headings. I do not enforce a minimum — sometimes a dense 800-word article needs only one or two H2s.

5. Internal links ≥ 4

This one is purely practical. Pages with few internal links do not pass PageRank well and feel thin. I count <a href> attributes that match my own domain. Four is the minimum; articles in the security library that score highest typically have 7–12.

6. External links ≥ 2

Outbound links to authoritative sources (ANSSI, NIST, CVE database, vendor advisories) signal that the content is contextually grounded. I require at least 2. This runs counter to old SEO advice about “leaking PageRank” — that advice is outdated.

7. FAQ section with interrogative H3s

A section where H2 or H3 headings end with ? triggers the FAQ rule. These headings get picked up for featured snippets and People Also Ask boxes. I validate by checking whether at least one heading in the article is interrogative.

8. Key takeaway block

A <div class="a-retenir"> or <div class="key-takeaway"> block signals to both readers and search engines that the article has a summary. Articles with this block have a measurably higher average time-on-page on my site.

The API

The engine exposes a simple JSON endpoint:

GET /api/seo/scores?slug=fortigate-hardening-guide-2025

Response includes a total score (0–100) and per-rule breakdown. The editorial interface shows a colored badge on each article card — green above 70, orange 50–70, red below 50.

What actually moved rankings

Honestly: the chapeau rule and the FAQ structure had the most visible impact. After adding the chapeau minimum and rewriting the opening paragraphs of the 200 lowest-scoring articles, organic traffic on those URLs increased about 18% over three months. That is not a controlled experiment — other things changed too — but the correlation was strong enough that I now treat it as a near-hard requirement.

The H2 ratio rule helped catch a class of articles that were structured more like bullet-point listicles than actual guides. Rewriting those into coherent sections with paragraph-length content under each heading improved dwell time.

SEO tooling you build yourself is more useful than generic tooling, because you can encode what you actually observe in your own data. The rules above are not universal — they work for long-form cybersecurity technical content on a French-language site. Your rules will be different. But the architecture (engine in your backend, scores in your CMS, bulk audit capability) is transferable to any content-heavy site.

I run AYI NEDJIMI Consultants, a cybersecurity consulting firm. We publish security hardening checklists for FortiGate, Palo Alto, Active Directory, and more — free PDF and Excel.

Stop Losing Shopify Webhooks: A Retry Strategy That Survives Real Outages

Here is a scenario every Shopify developer eventually lives through.

It is the middle of a deploy. Your endpoint is down for maybe 90 minutes. Nothing dramatic. But while you were shipping, three orders came in, an inventory sync fired, and a customer updated their address.

Shopify tried to deliver those webhooks. It retried. Then it gave up.

Those events are gone. Not delayed. Gone.

I have watched this exact thing happen, and the worst part is how quiet it is. No error in your logs. No alert. Just a slow drift between what Shopify knows and what your system knows. You find out days later when a customer asks where their order went.

So let us talk about how to actually fix this, properly, with a retry strategy that survives more than a quick hiccup.

First, Know What Shopify Actually Does

You cannot build a good retry layer until you know what the platform gives you for free.

When Shopify sends a webhook, it waits 5 seconds for your endpoint to respond. Any 2xx status code is a success. Anything else, including a timeout, is a failure.

After a failure, Shopify retries. And here is the part people get wrong, because the internet is full of outdated info:

As of the September 2024 policy update, Shopify retries a failed webhook up to 8 times over a 4-hour window with exponential backoff.

The old “19 retries over 48 hours” number you will find in older blog posts is dead. If your reliability code was written before late 2024, your timing assumptions are probably wrong.

A quick reference:

That last row is the one that ruins weekends. If your endpoint fails enough, Shopify does not just drop events. It removes the webhook subscription. New events stop firing entirely until you re-register. Silent and total.

The Problem With 4 Hours

Exponential backoff over 8 attempts in 4 hours front-loads everything. Most of your retries happen in the first 30 minutes. By hour 2, roughly 5 of your 8 attempts are spent.

So Shopify’s retry system is genuinely good at one thing: surviving short, transient blips. A momentary network drop. A one-off timeout.

It is bad at the thing that actually hurts you: real outages.

• A deploy that runs long
• A downstream API that rate-limits you for hours
• A traffic spike during a sale that pushes responses past 5 seconds
• A botched HMAC secret rotation that 401s every webhook

In all of those, the 4-hour window runs out before you recover. Shopify did its job. Your data is still lost.

The conclusion is simple: you need your own retry layer. Shopify’s is the floor, not the ceiling.

Rule Zero: Acknowledge Fast, Process Later

Before any retry logic, fix the most common mistake in Shopify webhook code: running business logic inside the webhook endpoint.

If your handler hits a database, calls an external API, or does anything heavy, you are gambling against that 5-second timeout. Cross it once and Shopify marks a perfectly good delivery as failed.

Your endpoint should do almost nothing:

// The entire job of your webhook endpoint
app.post("/webhooks/orders-create", async (req, res) => {
  // 1. Verify the HMAC signature
  if (!verifyHmac(req)) return res.sendStatus(401);

  // 2. Push the raw payload onto a queue
  await queue.add("orders-create", {
    body: req.rawBody,
    eventId: req.headers["x-shopify-event-id"],
    triggeredAt: req.headers["x-shopify-triggered-at"],
  });

  // 3. Respond immediately
  res.sendStatus(200);
});

That is it. A background worker pulls from the queue and does the real work. This one change eliminates the majority of timeout failures, and it turns retries into a calm internal concern instead of a race against a clock.

Building the Retry Layer

Your retry logic lives in the worker, not the endpoint. When processing fails, the worker decides: retry, or give up?

Step 1: Classify the error

Not every failure deserves a retry. Retrying a permanent error just burns resources.

Retrying a malformed payload 8 times will not magically make it valid. Categorize first, act second.

Step 2: Exponential backoff with jitter

For transient errors, retry with increasing delays. Each retry waits longer than the last.

Notice the window stretches across days, not Shopify’s 4 hours. That is the whole point of building your own layer.

But pure exponential backoff has a trap. If 500 webhooks fail at the same moment, they all retry at the same moment, and your recovering service gets hammered flat again. This is the thundering herd.

Fix it with jitter: add a small random offset to each delay. Instead of retrying at exactly 8 minutes, retry somewhere between 7 and 9. It spreads the load and smooths recovery.

function nextDelay(attempt) {
  const base = Math.min(30 * 2 ** attempt, 86400); // cap at 24h
  const jitter = base * (Math.random() * 0.3); // up to 30% jitter
  return base + jitter;
}

Step 3: Cap retries, then use a dead letter queue

Retries cannot run forever. Cap them, usually somewhere between 5 and 10 attempts. When a webhook exhausts its retries, it does not vanish. It moves to a dead letter queue.

The DLQ stores the failed webhook with full context: original payload, error message, attempt count, timestamp. Now you can:

• Inspect it and find the root cause
• Reprocess it once the bug is fixed
• Alert when the DLQ grows past a normal threshold

That alert is your early warning system. A spike in DLQ volume tells you something is wrong before any customer notices.

The Duplicate Problem Nobody Mentions

Retries create a brand new problem: the same webhook arriving twice.

Shopify delivers a webhook, your slow response times out, Shopify retries, but your worker already processed the first one. Now you have a duplicate. Without protection, that means double inventory adjustments, duplicate emails, double-charged orders.

The fix is idempotency. Every Shopify webhook carries an X-Shopify-Event-Id header, and that value stays identical across every retry of the same event.

Use it as a dedup key:

async function processWebhook(job) {
  const { eventId } = job.data;

  if (await alreadyProcessed(eventId)) {
    return; // safe no-op
  }

  await doTheRealWork(job.data);
  await markProcessed(eventId);
}

A retry strategy without deduplication is incomplete. Build this in from day one.

Watch Out for Stale Payloads

One more subtlety. When Shopify retries, it sends the original payload from when the event was triggered, not the current state.

If an order changed three times during the retry window, a late retry still carries the first version. Apply it blindly and you overwrite newer data with older data.

Always check the X-Shopify-Triggered-At header against your own records. If your data is already newer, skip the stale update or fetch fresh data from the Admin API instead.

The Layer Even Retries Cannot Save You From

Here is the uncomfortable truth: even a perfect retry layer cannot recover an event Shopify never sent, or one it dropped after its own retries failed.

For that, you need reconciliation. Periodically poll the Shopify Admin API and compare its data against yours. Hourly for high-value topics like orders/*. Daily for lower-stakes data.

Reconciliation is the final safety net. It catches events lost to extended outages, dropped events, and gaps from a removed subscription.

Combine the three layers and you lose almost nothing:

Do Not Forget Monitoring

A retry strategy fails silently without eyes on it. Track these:

• Webhook failure rate
• DLQ size and growth
• Retry volume
• Subscription status (run a daily check, alert on any missing topic)
• Processing latency creeping toward 5 seconds

Shopify’s Dev Dashboard has a delivery metrics report with response codes and retry counts per topic. Use it alongside your own monitoring, not instead of it.

TL;DR

Shopify gives you 8 retries over 4 hours. Real outages last longer than that. So:

1. Acknowledge fast. Return 200 in under 5 seconds, queue the payload.
2. Classify errors. Transient gets retried, permanent goes to the DLQ.
3. Exponential backoff with jitter. Retry over days, not hours.
4. Idempotency. Use X-Shopify-Event-Id to kill duplicates.
5. Dead letter queue. Nothing gets lost, everything is replayable.
6. Reconcile. Poll the Admin API to catch what slipped through.
7. Monitor. Surface problems before customers do.

Build these layers once and your integration stops losing data, even on its worst day.

If you want the full guide, you can read it here.

How does your team handle webhook reliability? I would genuinely like to hear what has worked for you in the comments.

If you’re shipping an Electron desktop app to end users and you don’t have auto-update wired in, you’re going to keep getting “this is broken” bug reports from users who are actually running a version from three releases ago.

I’ve shipped 25 releases of GramShift Desktop (an Instagram automation indie SaaS) over the past several months, and bolting on auto-update from day one was easily one of the highest-ROI infrastructure decisions I’ve made. This post is the implementation core + the specific traps I walked into.

Setup: electron-updater + GitHub Releases

The stack is boring on purpose:

• electron-updater (the npm package) handles update detection + download + install
• GitHub Releases hosts the installer assets + a latest.yml manifest
• Clients periodically check latest.yml against their current version
• On detection, they download and prompt the user to install

Release flow:

1. npm version patch (or minor/major)
2. npm run build produces installers (.exe, .dmg)
3. Upload .exe + latest.yml to a new GitHub Release
4. Running clients see the new manifest on next check, prompt the user, install

The big upside: no separate update server. GitHub Releases acts as your CDN. The free tier handles indie-scale traffic without breaking a sweat.

Minimum-viable main-process code

The smallest thing that works in your Electron main process:

const { app, dialog } = require('electron');
const { autoUpdater } = require('electron-updater');

function setupAutoUpdate(mainWindow) {
  autoUpdater.autoDownload = false;
  autoUpdater.autoInstallOnAppQuit = true;

  autoUpdater.on('update-available', (info) => {
    dialog.showMessageBox(mainWindow, {
      type: 'info',
      title: 'Update available',
      message: `Version ${info.version} is ready.`,
      buttons: ['Update now', 'Later']
    }).then((result) => {
      if (result.response === 0) autoUpdater.downloadUpdate();
    });
  });

  autoUpdater.on('update-downloaded', () => {
    dialog.showMessageBox(mainWindow, {
      title: 'Update ready',
      message: 'Restart to apply the update.',
      buttons: ['Restart', 'On next launch']
    }).then((result) => {
      if (result.response === 0) autoUpdater.quitAndInstall();
    });
  });

  autoUpdater.checkForUpdates();
}

app.on('ready', () => {
  const mainWindow = createWindow();
  setupAutoUpdate(mainWindow);
});

autoDownload = false is deliberate. Users hate background traffic and disk usage they didn’t consent to. Asking first (“Update available, want it now?”) meaningfully improves the install rate vs. silently downloading and then asking to restart.

Trap 1: obfuscating your network module silently breaks production

This one cost me about three hours of confused debugging.

I was using JavaScript obfuscation to make reverse-engineering the desktop client harder. The obfuscator was configured to process most of src/, and I accidentally included api.js — the file that handles HTTP communication with the backend.

The symptom: reportActivity would silently fail in production. The dashboard would show “0 likes today” while the real database had the correct counts. Local dev was perfect because obfuscation only runs on the production build.

Meanwhile heartbeat and reportStats (which lived in the same api.js) were sending fine. The obfuscator’s name-mangling had hit one specific function path differently from the others.

Fixes:

• Exclude network-layer files from obfuscation entirely. The marginal anti-reverse-engineering benefit is not worth the debugging cost when something goes wrong.
• Diff the production bundle against local for any file that handles auth, network, or persistence. A 30-second sanity check that would have saved my three hours.
• Watch your dashboards in real time for at least an hour after a release. This trap was visible in the data within minutes — I just wasn’t looking.

Trap 2: forgetting to upload latest.yml

electron-updater needs latest.yml in the GitHub Release to detect new versions. It is shockingly easy to forget to upload it (or to upload only the installer).

If you forget, existing clients never detect the new release. They keep running the old version forever, and you don’t notice until a user reports a bug “fixed in v1.4” while clearly running v1.3.

Mitigation: I now have a release script that, right before announcing internally, fetches the latest GitHub Release via the API and checks both .exe and latest.yml are present. If either is missing, it errors out before I post the announcement.

Trap 3: Windows SmartScreen + no code-signing cert

For Windows distribution without a code-signing certificate, your installer triggers “Windows protected your PC” SmartScreen warnings for the first few hundred downloads. A code-signing cert is about ¥30-50k/year (USD ~$200-350), and that’s the official fix.

For an indie launching with limited budget, I chose to skip the cert and instead:

• Publish a clear installation guide page with screenshots showing the “More info” → “Run anyway” path
• Link to that guide from the download button, not just the README
• Accept that there will be a small drop-off of users who bounce on the warning

Empirically, SmartScreen’s reputation system also softens after enough downloads accumulate — so the early downloads where the warning is harshest are also when you have the least audience. The cost of acquiring the early users with the rougher experience is real but bounded.

Trap 4: full-version-number changelog mistakes are public forever

The flip side of “easy release flow” is that every release note ships permanently. Early on I wrote some release notes that referenced internal feature names that weren’t worth disclosing, or had typos that aged poorly.

Habit I’ve adopted: every release note is “for users, not for me.” Internal version comments go in CHANGELOG.md (private), user-visible notes go in the GitHub Release body, and the two are written separately. The discipline is mildly annoying but the permanence of public release notes is the kind of thing you don’t notice until you wish you hadn’t written something a year ago.

Why auto-update is a “support-cost-saving investment”, not a “feature”

The thing that surprised me most: auto-update is not a feature your users will thank you for. They expect it. What it actually does is move you from the “what version are you on? please update” support pattern to “let me see the error” within a minute or two.

For a solo developer, that’s the difference between a bug report consuming 30 minutes of triage vs. 5 minutes of actual diagnosis. The implementation work (about a day) pays itself back inside the first month.

If you’re shipping Electron and don’t have auto-update yet, the ROI is hard to beat. Wiring it in before your first paying customer means you never have to triage version drift.

What auto-update trap have you hit? Curious if other Electron devs have run into the obfuscation-name-mangling one — felt like an obscure failure mode at the time, but the post-mortem suggests it’s probably more common than people talk about.

If you run a brand, manage social media, or just want to know what’s said about your product on X/Twitter, you’ve probably tried the same thing I tried first: native notifications, TweetDeck, search columns, hashtag pages.

Here’s what nobody admits: none of that scales past one keyword. Add a second brand to track, a competitor handle, a misspelling, a niche term — and the firehose breaks you. You stop checking. You miss the angry quote-tweet at 11pm on a Saturday. You don’t see the share-of-voice shift until the next quarterly review.

I built Twigest because I needed it. It’s a tool that watches X/Twitter for whatever you tell it to watch, runs everything through AI classification, and delivers one structured digest per day — to email, Slack, or Telegram.

This post explains what it does, who it’s for, and how it’s different from the dozen monitoring tools that already exist.

What is Twigest?

Twigest is an AI-powered Twitter monitoring and social listening tool that sends you a daily digest of what mattered on X/Twitter — instead of a real-time notification firehose.

You connect what you want tracked:

• Accounts — competitor handles, industry voices, your own brand
• Keywords — brand name, product name, hashtags, topic phrases
• Misspellings — the 8-15% of brand mentions that get your name wrong

Twigest runs its own scraping pipeline (no X API access needed, no developer account, no $5,000/mo Enterprise tier), classifies every captured tweet across 5 sentiment categories — positive, negative, angry, excited, neutral — and writes a summary every 24 hours.

You get the summary in your inbox, your team Slack channel, or a Telegram bot. Pick whichever you actually read.

Who is it for?

Founders & solo operators

You don’t have a community manager. You can’t afford to scroll. You need to know if someone’s complaining about your product before it goes viral, and you need to know which competitors are gaining momentum without sitting on X all day.

Twigest replaces 200 notifications with one 5-minute morning read.

Social media managers

You’re tracking 5+ brands across 30+ keywords. Native X notifications miss 40-60% of brand conversation because most people don’t @-tag your handle — they just type the brand name. You’re also expected to surface “share of voice” against competitors weekly.

Twigest gives you SOV calculations per digest, sentiment trends per keyword, and named-group monitoring (one digest per client).

Agencies

You manage Twitter for 10+ clients. Each client wants a branded report. You can’t manually screenshot dashboards every Monday.

Twigest Agency tier gives you 100 keywords + 200 accounts across separate client workspaces, each with its own branded digest output.

PR teams & comms

You need to spot a brewing crisis within minutes, not hours. You need a paper trail of mentions when a complaint escalates.

Spike alerts fire on the next hourly check when a tracked keyword crosses a 3x rolling 7-day baseline — typically within an hour of the surge.

How is it different from Mention, Brand24, Awario, Hootsuite, Sprout?

Real comparison, no marketing fluff:

The trade-off: Twigest is Twitter/X-only. If you need Reddit, LinkedIn, news mentions in one dashboard, the multi-platform tools win. If your brand conversation happens primarily on X — and for SaaS, dev tools, finance, crypto, media, gaming, that’s usually true — Twigest is faster, cheaper, and more accurate.

What does the daily digest actually look like?

Six sections, in this order:

1. Top mentions — the highest-signal tweets of the day, with original author, engagement metrics, and a one-line AI summary explaining why it matters
2. Sentiment breakdown — % positive / negative / angry / excited / neutral with day-over-day delta
3. Competitor activity — what tracked competitor accounts posted, plus their best-performing tweet of the day
4. Keyword spikes — any tracked keyword that crossed a 3x volume threshold against its 7-day baseline
5. New mentions you might miss — replies to your tweets, quote-tweets that don’t tag you, threads about you
6. Suggested actions — AI-generated suggestions: “Reply to this complaint thread”, “Quote-tweet this competitor announcement”, “Engage with this superfan”

Plans:

The free tier isn’t a 14-day trial — it’s permanent. The upsell hook isn’t quality, it’s quantity (more keywords, more mentions per digest, faster alerts).

How does the AI part actually work?

Two-pass model architecture, optimized per plan tier:

Pass 1 — Classify. Every captured tweet runs through a fast small-context model with reasoning disabled. For each tweet it assigns:

• One of 5 sentiment classes
• Topic tags (product/pricing/feature-request/complaint/competitor)
• Priority score (0-100) based on engagement, author followers, and sentiment intensity

This pass is cheap — fractions of a cent per tweet.

Pass 2 — Synthesize. The top-N classified tweets (sorted by priority) get fed into a stronger reasoning model that writes the actual digest prose — top mentions, summary, suggested actions. Higher plan tiers use stronger synthesis models.

This pass is where the writing quality lives. The model knows the brand context (you provide a short description at signup), knows the plan tier (so it adjusts depth), and knows the language (digest output matches your account language preference).

Two-pass instead of one-pass because: classifying 1,000 tweets with a reasoning model would cost dollars per digest. Classifying with a nano-tier model then synthesizing only the top 25 costs cents. Same output quality where it matters — in the prose you actually read.

How do you handle the “no X API” problem?

This is the question everyone asks first.

X/Twitter’s official API costs $5,000/month for production-grade access. Most monitoring tools either:

• Pass that cost to you (which is why Brand24 starts at $99/mo)
• Lie about coverage and only catch @-mentions
• Sample heavily and miss your conversation

Twigest runs its own data pipeline using Twikit — a browser-session-based Python client that authenticates the same way a logged-in user does. We rotate multiple authenticated accounts (“twikit pool” pattern) to distribute the read load, and we cache aggressively at the keyword level so the same query isn’t hit twice.

The trade-off: we don’t have real-time webhooks (X doesn’t offer those at a price we could pass to a $9/mo plan anyway). Our resolution is hourly polling with spike detection running on the same hourly cadence. For “what mattered today?”, that’s plenty. For arbitrage trading, look elsewhere.

Frequently Asked Questions

Do I need a Twitter API or X Premium subscription?

No. Twigest runs its own data pipeline independent of paid API tiers. Signup is email + password. No developer account, no API key.

What can I monitor?

Three things in one stream:

1. Accounts — competitor handles, industry voices, your own brand
2. Keywords — brand name, product name, “hashtag, topic phrase, misspellings
3. Sentiment + volume — changes in how people feel and how much they’re talking

How quickly do I receive alerts?

Daily digests arrive every morning at your chosen time. Spike alerts run on hourly checks — when a tracked keyword crosses a 3x rolling 7-day baseline, the alert fires on the next check (typically within an hour). Available on Pro and above.

Does it cover replies, quote-tweets, and threads?

Yes. Replies and quote-tweets matching your keywords are captured. Thread context is preserved when the AI writes the summary, so you see why a quote-tweet matters, not just the tweet itself.

What about misspellings?

Add each common misspelling as a separate keyword. Pro = 10 keywords, Business = 30. Misspellings account for 8-15% of brand mentions on X for most consumer brands — adding the top 3 covers most of it.

Can I monitor multiple brands or clients?

Yes. Business and Agency tiers support named topic groups with white-label branding. Business covers up to 5 topics with 3 team seats; Agency covers up to 10 topics with 10 team seats.

Is there a free plan?

Yes, permanent. Free covers 3 keywords + 3 accounts with a weekly digest at $0/mo. No credit card required.

How to start

Sign up for the free tier at twigest.com. Add 3 keywords (your brand name + 2 misspellings, or your brand + 2 competitors), connect an email, and you’ll get your first digest within 24 hours.

If the digest is useful, upgrade to Pro for $9/mo to unlock 10 keywords, 15 accounts, daily delivery, and spike alerts. If it’s not, the free tier stays free forever.

What I’m building next

• Hashtag clustering — automatic grouping of related campaign hashtags
• Public benchmark reports — anonymized SOV data by industry vertical
• Browser extension — capture a tweet into Twigest with one click
• Multi-platform expansion — Reddit and LinkedIn signal are on the roadmap once X coverage is rock-solid

If you want updates, follow @gettwigest on X or subscribe to the changelog at twigest.com/changelog.

I’m Taha, building Twigest solo. Always happy to compare notes — what does your X monitoring stack look like right now? What’s broken about it?

This is a submission for the Google I/O Writing Challenge

I spent some time watching the Google I/O 2026 “What’s New in AI” session, and one small detail personally stood out to me.

One of the presenters was Paige Bailey. Years ago, I used to watch her GenAI workshops on Kaggle while learning AI on my own. Seeing her present at Google I/O felt like a reminder of how quickly this space has evolved.

But beyond the announcements themselves, I think the biggest message from Google I/O was this:

AI is no longer being treated as a standalone product.
It’s becoming ambient infrastructure.

AI is becoming ambient

What stood out to me most was how deeply AI is now integrated across Google’s ecosystem.

Search, Workspace, Android, Chrome, YouTube, developer tools — AI is quietly being embedded everywhere.

Not as a separate chatbot tab, but as an invisible layer sitting behind the products people already use every day.

AI is becoming ambient.

Multimodal AI is becoming the default

Another thing that stood out was how natural multimodal interaction now feels.

Text, voice, images, video, screen understanding, and real-time context are all starting to blend together into a single experience.

Instead of switching between different tools, the AI increasingly understands multiple forms of input at once.

That feels like a major UX shift.

The Shift Toward “Agentic” AI

Another word that repeatedly appeared during the presentations was “agentic.”

The industry is clearly moving away from AI that simply answers questions toward AI that can actually perform tasks, make decisions, and operate across tools.

That shift feels important.

The conversation is no longer:

“Can AI chat with you?”

Now it’s:

“Can AI do things for you?”

Google Search is long gone

One thing became very obvious during the demos:

The old version of Google Search is quietly disappearing.

Typing keywords and manually digging through links is slowly being replaced by AI-generated summaries, conversational search, and agents that help organize information for you.

Whether that change is good or bad is still open for debate, especially for creators and websites across the internet.

But the direction feels clear.

Comparing Google with OpenAI and Anthropic

What’s interesting is how differently each company seems to be approaching AI.

Google still feels strongest in consumer AI momentum and product experience.

It has built a strong reputation around reasoning quality and safety-focused systems.

But it may have the biggest advantage in ecosystem integration.

Google already owns platforms used by billions of people daily:

• Search
• Android
• YouTube
• Chrome
• Workspace

That means AI can spread across everyday life extremely fast.

And after watching Google I/O, it feels like that’s exactly where things are heading.

The other day I came across a Claude Code todo list that looked like this:

✓ Read and identify all screenshots
✓ Categorize screenshots into 4 groups
◼ Create folders and move/rename screenshots
☐ Write business requirements doc per screenshot

Four steps. Clean. The kind of plan a vibe coder would screenshot and post on X with the caption “AI is unreal right now.”

And honestly — it is a good plan. Better than 90% of what I see in the wild. But it’s also one tiny step away from producing 30 beautifully written documents that quietly contradict each other.

Let me walk you through why this strategy works, where it breaks, and the one step I’d add before you let your AI write a single requirement.

The strategy, decoded

Before we critique anything, let’s give credit where it’s due. This workflow has three things going for it that most vibe coding sessions don’t.

It uses screenshots as the source of truth. Most people start vibe coding from a vague idea in their head — “build me a project management app like ClickUp but cheaper”. The AI has nothing concrete to anchor on, so it hallucinates a generic CRUD app, and you spend the next three days correcting it. Screenshots flip this. They’re concrete. They show real states, real data, real edge cases. An AI looking at a screenshot can’t drift as far as one listening to a wish.

It categorizes before it documents. The “4 groups” step is the unsung hero here. Without grouping, you end up with thirty disconnected requirements docs that don’t share vocabulary. Grouping forces pattern recognition — these eight screens are all CRUD on the same entity, these five are reporting, these three are settings. That clustering naturally maps to modules or bounded contexts later, which is exactly what you want when you start translating documents into code.

It produces atomic, reviewable artifacts. One BRD per screenshot. Small, self-contained, easy to diff, easy to hand to Claude Code with a prompt like “generate the Livewire component for this spec.” You can iterate on one BRD without rewriting the whole project.

This is genuinely a good foundation. If you stopped reading here and ran with it, you’d be ahead of most teams.

But we’re not stopping here.

Failure mode #1: BRDs without a shared vocabulary

Here’s what happens when you skip from categorization straight to writing BRDs.

Screenshot 03 shows a customer list. The AI writes: “The system shall display a paginated list of **Customers.”

Screenshot 11 shows a customer detail page. The AI writes: “The **Member* profile shall include…”*

Screenshot 19 shows the same person logging in. The AI writes: “Upon authentication, the **User* is redirected…”*

Screenshot 24 is a B2B contact view. The AI writes: “Each **Account* has a primary contact…”*

Customer. Member. User. Account. Four words. Same entity. Different document. Different author session. Different vibes.

Now imagine you feed all 30 BRDs to Claude Code and ask it to generate migrations. Watch what happens.

You get a customers table, a members table, a users table (probably from Laravel’s default), and an accounts table. None of them reference each other. The Eloquent relationships are guesswork. Half your Actions take Customer $customer, the other half take User $user, and your form requests use both interchangeably.

This is the most common failure I see in screenshot-driven workflows, and it never shows up in the demo. It shows up in week three, when you realize half your codebase is talking past itself.

The fix is a vocabulary extraction step. Before any BRD gets written, you produce a glossary.md that looks something like this:

| Term     | Definition                              | Aliases                  |
|----------|-----------------------------------------|--------------------------|
| Customer | An organisation that purchases services | Account, Client          |
| Member   | An individual user within a Customer    | User, Contact, Staff     |
| Order    | A purchase transaction                  | Sale, Invoice (informal) |

Now when the AI writes BRD-019, it consults the glossary, sees that “Member” and “User” are aliases for the same concept, and picks one canonical term. Suddenly your thirty documents speak the same language.

Failure mode #2: Screen-driven design is not system design

Screenshots tell you what the user sees. They don’t tell you what the system does.

Look at any login screen. The screenshot shows a form with email, password, and a button. The BRD writes itself: “User enters credentials, system authenticates, user is redirected to dashboard.”

What the screenshot doesn’t show:

• The rate limiter that locks the account after five failed attempts
• The audit log entry that records every login attempt, successful or not
• The webhook that fires to your CRM when a new user logs in for the first time this month
• The background job that recomputes the user’s permission cache
• The SSO redirect path that bypasses the password field entirely
• The API endpoint that mobile clients use, which doesn’t render this form at all

None of that is in the picture. All of it is in the system.

If you’re building a SaaS — and especially if you’re in the kind of architecture I tend to work in, where you’ve got a hub-and-spoke pattern with things like identity providers, API gateways, and webhook routers — half your architecture lives behind the UI. A pure screenshot-driven workflow will miss it entirely.

The fix is to add a step per group (not per screenshot) where you explicitly list the non-UI concerns:

• Background jobs and scheduled tasks
• Webhooks emitted and consumed
• Permission matrix (who can do what)
• Audit and observability requirements
• API contracts for non-UI consumers
• Integration points with other modules

This doesn’t need to be exhaustive on the first pass. It just needs to exist so you remember to look behind the curtain.

Failure mode #3: Losing the trail

The original workflow says “create folders and move/rename screenshots.” Good — but rename them to what?

If your screenshots become screenshot_001.png, screenshot_002.png, you’ve lost the only useful thing you could have encoded: traceability.

Three months later, when you’re debugging a Livewire component and you want to know “what was the original requirement for this?”, you want to be able to trace:

Code:        app/Livewire/CustomerList.php
   ↑ generated from
BRD:         docs/brd/g01-s03-customer-list.md
   ↑ extracted from
Screenshot:  screenshots/g01-customers/s03-list-view.png

The naming convention does this work for you, silently. Something like g01-s03-customer-list.png tells you immediately: group 1, screen 3, the customer list. The BRD inherits the same ID. The generated code references it in a docblock. Now you have a chain of custody from pixel to production.

This costs you nothing at rename time. It costs you everything if you skip it.

Failure mode #4: Inconsistent BRD structure

If you let the AI freestyle each BRD, you’ll get thirty documents with thirty slightly different structures. One has a “User Stories” section, another has “Use Cases”, a third has “Functional Requirements”, a fourth jumps straight into a numbered list of behaviours.

This isn’t a vibe problem, it’s a parsing problem. When you later want to feed these BRDs into Claude Code to generate code, the AI does much better with predictable structure. Same headings, same order, same vocabulary for sections.

Define the template once, before the loop:

# {Screen ID} — {Screen Name}

## Purpose
What this screen exists to do, in one sentence.

## Actors & Permissions
Who can access this screen, and what role gates apply.

## Entities Referenced
List of glossary terms this screen interacts with.

## User Actions
For each action: trigger, validation rules, side effects, success state, failure state.

## States
Empty state, loading state, error state, success state.

## Non-Functional Notes
Performance expectations, accessibility, audit requirements.

## Open Questions
Things the screenshot doesn't tell us and we need to confirm.

Now every BRD looks the same. Claude Code knows exactly where to look for “what validation rules apply to the Save button.” Your reviewers know exactly where to add comments. Your future self thanks you.

The refined workflow

Putting it all together, here’s what I’d actually run:

1. ✓ Read and identify all screenshots
2. ✓ Categorize into groups (by feature area, not screen type)
3. ◼ Create folders and rename with traceable IDs (g{NN}-s{NN}-{slug})
4. NEW: Extract shared entities, enums, and permissions → glossary.md
5. NEW: Define the BRD template once
6. ☐ Write BRD per screenshot, referencing the glossary
7. NEW: Per group, document non-UI concerns (jobs, webhooks, audits, APIs)
8. NEW: Synthesise — produce the domain model, module boundaries, Action inventory

Steps 4, 5, 7, and 8 are the ones that turn a demo into a system.

Step 8 is where this becomes powerful, by the way. Once you have a glossary, BRDs in consistent format, and non-UI concerns mapped, you can feed all of it to Claude Code with a prompt like:

Given this glossary and these BRDs, generate the Eloquent models with relationships, Form Requests, invokable Actions, and Pest tests for the Customers module.

And the output will actually fit. The naming will be consistent. The relationships will make sense. The tests will reference the right entities. Because you did the upstream work of giving the AI a coherent context to operate in.

The lesson nobody puts on the screenshot

If you take one thing from this, take this:

AI is excellent at extraction. Humans must still define the schema of what’s extracted.

The screenshot-to-BRD workflow looks like the AI is doing all the work. It isn’t. It’s doing the typing. The thinking — the categorization, the vocabulary, the template, the boundaries between modules — that’s still yours. Skipping those steps doesn’t speed you up. It just defers the cost to a later point in the project where it’s ten times more expensive to fix.

Vibe coding isn’t about letting the AI decide. It’s about giving the AI a structured enough world that its decisions stop being random.

Add the glossary step. Define the template. Document what the screenshot doesn’t show. Encode the trail in your filenames.

Then let it rip.

If you’ve tried screenshot-driven workflows on your own projects, I’d love to hear which failure modes you’ve hit and how you worked around them. Drop a comment.

Everyone reaching for a vector database when building RAG is solving the wrong problem first. For most domain-specific corpora — technical documentation, company knowledge bases, article archives — BM25 retrieval is competitive with semantic search, costs a fraction of the compute, and is dramatically simpler to operate. This tutorial shows you how to build a full RAG pipeline using Meilisearch as the retrieval backend, stream responses from an LLM API, and evaluate hit rate without a single embedding model.

Why RAG, and why not a vector database

Retrieval-Augmented Generation solves a fundamental problem: LLMs have a knowledge cutoff and a finite context window. You want answers grounded in your documents, not hallucinated from pre-training.

The standard advice is to use a vector database (Pinecone, Weaviate, Chroma). Vector search is powerful for open-domain retrieval where semantic similarity matters. But on a domain-specific corpus with consistent terminology — think a cybersecurity knowledge base or a medical reference — BM25 with typo tolerance typically achieves 85–95% of the recall you’d get from embeddings, with zero GPU cost, sub-10ms latency, and no embedding pipeline to maintain.

Meilisearch gives you BM25 out of the box, plus typo tolerance, faceted filtering, and a simple REST API. It’s what I use to power the search across 1,600+ articles at AYI NEDJIMI Consultants.

Setup

pip install meilisearch openai httpx

Run Meilisearch locally:

docker run -d -p 7700:7700 getmeili/meilisearch:latest

Step 1: Index your documents

Your documents need an id, searchable content, and any filter attributes you want to use at query time.

import meilisearch
import hashlib
import json

MEILI_URL = "http://127.0.0.1:7700"
MEILI_KEY = "your_master_key"  # or "" for local dev
INDEX_NAME = "knowledge_base"

client = meilisearch.Client(MEILI_URL, MEILI_KEY)

def get_or_create_index():
    try:
        index = client.get_index(INDEX_NAME)
    except meilisearch.errors.MeilisearchApiError:
        task = client.create_index(INDEX_NAME, {"primaryKey": "id"})
        client.wait_for_task(task.task_uid)
        index = client.get_index(INDEX_NAME)

    # Configure searchable attributes and filters
    index.update_settings({
        "searchableAttributes": ["title", "content", "tags"],
        "filterableAttributes": ["category", "doc_type"],
        "rankingRules": [
            "words", "typo", "proximity", "attribute", "sort", "exactness"
        ],
        "typoTolerance": {
            "enabled": True,
            "minWordSizeForTypos": {"oneTypo": 4, "twoTypos": 8}
        }
    })
    return index

def index_documents(documents: list[dict]):
    """
    Each document: {"id": str, "title": str, "content": str,
                    "tags": list[str], "category": str, "doc_type": str}
    """
    index = get_or_create_index()

    # Add stable IDs if not present
    for doc in documents:
        if "id" not in doc:
            doc["id"] = hashlib.sha256(doc["content"].encode()).hexdigest()[:16]

    task = index.add_documents(documents, primary_key="id")
    client.wait_for_task(task.task_uid)
    print(f"Indexed {len(documents)} documents.")

# Example: load from a JSONL file
def load_and_index(filepath: str):
    docs = []
    with open(filepath) as f:
        for line in f:
            docs.append(json.loads(line.strip()))
    index_documents(docs)

Step 2: Retrieve top-k documents

def retrieve(query: str, top_k: int = 5, filters: str = "") -> list[dict]:
    """
    Returns top_k documents matching the query.
    filters example: "category = 'security' AND doc_type = 'guide'"
    """
    index = client.get_index(INDEX_NAME)

    search_params = {
        "limit": top_k,
        "attributesToRetrieve": ["id", "title", "content", "category"],
        "attributesToHighlight": ["content"],
        "highlightPreTag": "**",
        "highlightPostTag": "**",
    }

    if filters:
        search_params["filter"] = filters

    results = index.search(query, search_params)
    return results["hits"]

Step 3: Construct the prompt

The prompt structure is critical. You want the model to be explicitly grounded — it should cite only what’s in the retrieved chunks, not hallucinate.

def build_prompt(query: str, retrieved_docs: list[dict]) -> list[dict]:
    context_blocks = []
    for i, doc in enumerate(retrieved_docs, 1):
        context_blocks.append(
            f"[Source {i}] {doc['title']}n{doc['content'][:1200]}"
        )

    context = "nn---nn".join(context_blocks)

    system_prompt = (
        "You are a technical assistant. Answer the user's question using ONLY "
        "the provided sources. If the answer is not in the sources, say so explicitly. "
        "Cite sources by number, e.g. [Source 1]."
    )

    user_message = f"""Sources:
{context}

---

Question: {query}"""

    return [
        {"role": "system", "content": system_prompt},
        {"role": "user", "content": user_message},
    ]

Step 4: Stream the LLM response

Never buffer the full response before sending it to the user. Streaming is essential for UX on long answers.

from openai import OpenAI  # generic llm_client — swap for any compatible SDK

llm_client = OpenAI(
    api_key="your_api_key",
    base_url="https://api.your-llm-provider.com/v1",  # adjust per provider
)

def rag_stream(query: str, category_filter: str = ""):
    """Generator that yields text chunks as they arrive from the LLM."""
    filters = f"category = '{category_filter}'" if category_filter else ""
    docs = retrieve(query, top_k=5, filters=filters)

    if not docs:
        yield "No relevant documents found in the knowledge base."
        return

    messages = build_prompt(query, docs)

    stream = llm_client.chat.completions.create(
        model="gpt-4o-mini",  # or your preferred model
        messages=messages,
        stream=True,
        temperature=0.2,  # lower temp for factual retrieval tasks
        max_tokens=800,
    )

    for chunk in stream:
        delta = chunk.choices[0].delta
        if delta.content:
            yield delta.content

Step 5: Wire it together — a minimal CLI

import sys

def main():
    query = " ".join(sys.argv[1:]) if len(sys.argv) > 1 else input("Query: ")
    print(f"nQuery: {query}n{'='*60}n")

    for token in rag_stream(query):
        print(token, end="", flush=True)

    print("n")

if __name__ == "__main__":
    main()

Usage:

python rag.py "What are the key requirements of NIS 2 for SMEs?"

Step 6: Evaluate hit rate

Before deploying, measure whether your retrieval is actually finding the right documents. You need a small golden dataset: query → expected document ID.

def evaluate_hit_rate(golden_set: list[dict], top_k: int = 5) -> float:
    """
    golden_set: [{"query": "...", "expected_id": "doc_id"}, ...]
    Returns hit rate @ top_k.
    """
    hits = 0
    for item in golden_set:
        results = retrieve(item["query"], top_k=top_k)
        retrieved_ids = {r["id"] for r in results}
        if item["expected_id"] in retrieved_ids:
            hits += 1

    hit_rate = hits / len(golden_set)
    print(f"Hit rate @{top_k}: {hit_rate:.2%} ({hits}/{len(golden_set)})")
    return hit_rate

# Example usage
golden = [
    {"query": "NIS 2 SME requirements", "expected_id": "nis2-guide-001"},
    {"query": "ISO 27001 certification steps", "expected_id": "iso27001-checklist"},
    {"query": "penetration testing methodology", "expected_id": "pentest-guide-002"},
]

evaluate_hit_rate(golden, top_k=5)

On a 1,600-article cybersecurity corpus, this setup achieves roughly 91% hit rate at k=5 — without a single embedding model call.

Production considerations

Chunking strategy: For long documents, chunk at 512–800 tokens with 10% overlap. Store doc_id and chunk_index so you can reconstruct the full document if needed.

Re-ranking: If your hit rate plateaus below 85%, add a lightweight cross-encoder re-ranker as a second stage. cross-encoder/ms-marco-MiniLM-L-6-v2 from Sentence Transformers works locally and adds ~30ms latency.

Context window budget: At 5 docs × 1,200 chars, you’re using roughly 1,500 tokens of context. Adjust top_k and content truncation to stay within your model’s window while leaving room for the answer.

Caching: Cache retrieval results for identical queries with a TTL of 5–15 minutes using Redis or even a simple in-memory dict. LLM call results can be cached longer for factual queries.

This pipeline — retrieval with Meilisearch, prompt construction, streaming output — is what I run in production. No embedding pipeline, no vector database operational overhead. For domain-specific retrieval, BM25 is frequently the pragmatic choice. Reach for semantic search when your query vocabulary genuinely diverges from your document vocabulary; otherwise, ship the simpler thing.

The bug that wasted my afternoon

I tried to set a video as my Telegram profile picture. I recorded a short clip on my iPhone, opened Telegram, and picked it as my avatar. Telegram accepted the file, showed a spinner, then quietly fell back to a static frame. No error. No warning. Just a still image where a looping video should have been.

Turns out Telegram has strict rules for video avatars, and iPhone video breaks almost all of them at once. The clip was HEVC, the wrong resolution, too long, and carried an audio track. Telegram does not tell you any of this. It just gives up.

I wanted the actual feature, so I dug into what the spec really needs and wrote a converter. This post is the result.

What Telegram actually requires

A Telegram video avatar is not a normal video upload. The constraints are tight:

• Codec: H.264 (AVC). HEVC is rejected.
• Container: MP4 with the moov atom at the front (faststart).
• Resolution: square. 800×800 is the safe target.
• Duration: 10 seconds or less.
• Size: roughly 2MB or less.
• No audio track.
• Pixel format: yuv420p. Some players choke on yuv444p or 10-bit.

iPhone footage fails on codec, shape, duration, audio, and often pixel format. A modern iPhone records HEVC in 10-bit at 1080×1920 or wider. Every one of those points needs fixing before Telegram will animate it.

The silent failure is the worst part. You cannot debug a process that refuses to report what went wrong.

Fixing it with ffmpeg

ffmpeg does all the real work here. The interesting step is cropping a vertical phone video into a centered square without squishing faces.

Detecting the crop region

cropdetect scans frames and prints the largest non-black crop rectangle. I run a quick pass to read it:

ffmpeg -i input.mov -vf cropdetect=24:16:0 -frames:v 60 -f null - 2>&1 | 
  grep -o 'crop=[0-9:]*' | tail -1

That prints something like crop=1080:1080:0:420. For a phone clip I usually ignore the detected offset and take a centered square of the shorter side, but cropdetect earns its keep when the video has letterboxing or black bars.

The encode pass

Here is the command that produces a Telegram-ready avatar:

ffmpeg -y -i input.mov 
  -t 10 
  -an 
  -vf "crop='min(iw,ih)':'min(iw,ih)',scale=800:800:flags=lanczos,format=yuv420p" 
  -c:v libx264 -profile:v high -level 4.0 
  -preset slow -crf 26 
  -movflags +faststart 
  -pix_fmt yuv420p 
  output.mp4

Walking through the flags that matter:

• -t 10 hard-caps duration at 10 seconds.
• -an drops the audio track. Telegram wants none.
• crop='min(iw,ih)':'min(iw,ih)' takes the largest centered square that fits.
• scale=800:800:flags=lanczos resizes to the target. Lanczos keeps edges sharp.
• format=yuv420p plus -pix_fmt yuv420p force 8-bit 4:2:0, which fixes 10-bit HEVC.
• -c:v libx264 re-encodes to H.264.
• -movflags +faststart moves the moov atom to the front so playback can start immediately.
• -crf 26 is the quality knob. If the file lands above 2MB, I raise CRF a few points and re-run.

One pass, and an iPhone clip becomes a file Telegram will actually loop.

Keeping it under 2MB

CRF 26 handles most 10-second clips, but a busy scene with lots of motion can still overshoot. My loop is simple: encode, check the file size, and if it is over 2MB raise CRF by 4 and try again. Two attempts cover almost everything. For stubborn clips I trim duration before touching quality, since 6 seconds at decent quality beats 10 seconds of mush.

Wiring it into a bot

Running ffmpeg by hand is fine once. I wanted to send a video to a chat and get the avatar back, so I wrapped it in an aiogram 3 handler.

from aiogram import Bot, Dispatcher, F
from aiogram.types import Message, FSInputFile
import asyncio, tempfile, os

dp = Dispatcher()


@dp.message(F.video | F.animation | F.document)
async def handle_video(message: Message, bot: Bot):
    file = message.video or message.animation or message.document
    if not file:
        await message.answer("Send me a video or GIF.")
        return

    with tempfile.TemporaryDirectory() as tmp:
        src = os.path.join(tmp, "in")
        dst = os.path.join(tmp, "out.mp4")
        await bot.download(file, destination=src)

        ok = await convert_to_avatar(src, dst)
        if not ok:
            await message.answer("Could not convert that one. Try a shorter clip.")
            return

        await message.answer_video(
            FSInputFile(dst),
            caption="Ready. Set this as your profile video.",
        )


async def convert_to_avatar(src: str, dst: str) -> bool:
    proc = await asyncio.create_subprocess_exec(
        "ffmpeg", "-y", "-i", src, "-t", "10", "-an",
        "-vf", "crop='min(iw,ih)':'min(iw,ih)',scale=800:800:flags=lanczos,format=yuv420p",
        "-c:v", "libx264", "-preset", "slow", "-crf", "26",
        "-movflags", "+faststart", "-pix_fmt", "yuv420p", dst,
        stdout=asyncio.subprocess.DEVNULL,
        stderr=asyncio.subprocess.DEVNULL,
    )
    await proc.wait()
    return proc.returncode == 0 and os.path.exists(dst)

The F.video | F.animation | F.document filter matters. Telegram delivers the same file as different types depending on whether the user sends a video, a GIF, or a file attachment. Catching all three saves a lot of confused users.

asyncio.create_subprocess_exec keeps the bot responsive while ffmpeg runs. A 10-second clip encodes in a couple of seconds on a small VPS, but blocking the event loop would still freeze every other chat.

Packaging it as a bot anyone can use

I cleaned this up and shipped it as @LiveAvaBot. You send a video or GIF, and it returns an 800×800 H.264 file under 2MB with audio stripped, ready to drop in as a profile video. It handles HEVC from iPhones, vertical clips, oversized files, and GIFs pulled from random websites.

Most of the credit goes to ffmpeg. I just wrote the wrapper that picks sane flags and hides the silent-failure problem behind a single message.

Edge cases and what is next

A few things I learned along the way:

• Square clips that are already H.264 still need re-encoding for the moov atom and the audio strip. Skipping the convert step because the file looks fine produced more silent failures.
• Animated WebP and some GIFs decode with a variable frame rate. Adding -r 30 to the encode smooths playback.
• Very dark footage confuses cropdetect. The centered-square crop is a more reliable default than trusting detection on every clip.
• 4K HEVC is slow to decode on a small VPS. It works, it just takes longer, and that is the next thing I want to speed up.

The whole project started because Telegram would not tell me why my avatar was a still image. Now it is one message to a bot.

I built this one myself. The bot is @liveavabot. If you try it and a clip fails, I want to hear which clip.

What If You Could Just… Ask Your Data a Question? 🤔

Most people who need insights from a data file are blocked by one simple thing: they don’t know SQL. Even technically strong users often don’t want to stop, inspect schema manually, write queries, debug syntax, and format results just to answer a quick question like “Which category has the highest revenue?” or “Show me null rates by column.”

This project removes that friction entirely. Upload your file, type your question in plain English, and let a Gemma 4-powered agentic backend inspect the schema, generate DuckDB SQL, execute it, and return the results — right inside a clean chat interface. 🎯

👉 Try it live here: https://databro.dev/backend/ai-data-chat/

🛠️ The Full Stack at a Glance

Before diving deep, here’s the architecture that powers this tool:

🌟 Meet the Gemma 4 Model Family

Gemma 4 is Google DeepMind’s latest family of open models — and they are genuinely exciting for agentic builders. Unlike earlier iterations, Gemma 4 is built with logic-heavy, reasoning-oriented workflows in mind, which makes it a natural fit for tasks like natural-language-to-SQL generation.

Here’s a builder-friendly overview of the lineup:

The AI Data Chat tool exposes google/gemma-4-31B-it and google/gemma-4-26B-A4B-it via Hugging Face endpoints. Once you see these models turn a fuzzy natural-language question into a working DuckDB query over your own uploaded file, it becomes very hard not to imagine ten more use cases. 🔥

🤖 A Brief Foundation on CrewAI

CrewAI is an open-source framework for orchestrating autonomous AI agents in structured multi-agent workflows. Instead of a single giant prompt doing everything, CrewAI lets you break a problem into specialized, coordinated responsibilities.

Three building blocks to understand:

🧑‍💼 Agents

Specialized workers, each with a defined role. Think of them as employees on your AI team — one might be a “Schema Inspector,” another a “SQL Writer,” another a “Result Formatter.”

📋 Tasks

Units of work assigned to agents. A task has a clear description, expected output, and the agent responsible for it. Examples:

• “Analyze the uploaded file and return its schema”
• “Given this schema and user intent, write a valid DuckDB SQL query”
• “Execute the SQL and format the result for the user”

🔧 Tools

Capabilities agents can invoke to act on the world — file inspection utilities, DuckDB query executors, schema extractors, etc.

This model is powerful because it creates transparent, inspectable pipelines instead of black-box AI magic. Every step has a purpose, and every output is traceable.

🏗️ How the Tool Is Built — End to End

Step 1 — The Next.js Frontend

The portfolio app at databro.dev/backend/ai-data-chat/ provides a split-panel chat UI:

• Left panel: File upload zone (drag & drop), attached file display with name and size, LLM Settings panel with Provider + Model selectors
• Right panel: Chat interface with starter prompt buttons, conversation history with SQL panels and output tables

The user picks a file, selects a Gemma 4 model via Hugging Face, and types a natural-language question. The frontend packages everything into FormData (file + user intent + provider + model) and sends it to a Supabase Edge Function.

Step 2 — Supabase Edge Function

A lean TypeScript Edge Function validates the multipart request and proxies it to the Google Cloud Run backend. This keeps the frontend thin, avoids exposing Cloud Run endpoints directly to the browser, and centralizes auth concerns at the edge.

Step 3 — FastAPI on Google Cloud Run

The Cloud Run backend receives the uploaded file and user intent. It:

1. Stores the file in a temporary directory
2. Detects file type (CSV, Parquet, Arrow, JSON, XLSX)
3. Loads the data into DuckDB as a data table
4. Runs DESCRIBE data to extract the full schema
5. Counts total rows
6. Passes schema context + row count + user intent to the CrewAI agent pipeline

Step 4 — CrewAI Agent Pipeline

The agentic pipeline runs two coordinated tasks:

Task 1 — Schema Analysis
An agent uses a DuckDB tool to introspect the uploaded file and returns column names, types, and a row count. This grounding step is critical — the LLM sees real column names before generating SQL, which dramatically reduces hallucination.

Task 2 — SQL Generation + Execution
The schema and user intent are passed to the Gemma 4 model via Hugging Face. The LLM returns a valid DuckDB SQL query. The agent then executes that SQL against the uploaded file and serializes the results.

Step 5 — Response Back to the UI

The backend returns a structured JSON response containing:

• model — the resolved model used
• sql — the exact DuckDB SQL that was generated and run
• schema — column definitions
• total_rows — row count of the uploaded file
• result — the query output rows

The frontend renders the SQL in a dark code panel and the results in a scrollable table. Transparency is built in — you always see exactly what query was run.

🎬 Live Demo — Real Queries, Real Results

I tested the tool with a real e-commerce sales CSV (49 rows, 18 columns covering orders, customers, products, categories, regions, sales, profit, discounts, and returns). Here’s what Gemma 4 + CrewAI + DuckDB produced for 6 progressively complex natural language questions.

✅ Test Setup — File Uploaded, Gemma 4 31B Selected

The file sample_test_file.csv is attached (8.7 KB). Provider set to Hugging Face, model set to Gemma 4 31B Instruct. The chat interface is ready with starter prompts visible.

💡 The LLM Settings panel (visible by scrolling right on the chat panel) lets you switch between Gemma 4 31B Instruct and Gemma 4 26B A4B Instruct mid-session.

🟢 Query 1 — “Show me the top 10 rows”

What Gemma 4 generated:

SELECT * FROM data LIMIT 10

Result: Processed 49 CSV rows, returned 10 of 10 matching rows. Clean tabular output showing order_id, customer_id, customer_name, customer_email, customer_segment and more.

🟢 Query 2 — “What are the key columns and null rates?”

This is where things get interesting. Instead of a simple SELECT, Gemma 4 understood intent — it generated a full null-rate audit query covering every single column:

What Gemma 4 generated:

SELECT
    count(*) AS total_rows,
    (count(*) - count(order_id)) * 100.0 / count(*) AS order_id_null_rate,
    (count(*) - count(customer_id)) * 100.0 / count(*) AS customer_id_null_rate,
    (count(*) - count(customer_name)) * 100.0 / count(*) AS customer_name_null_rate,
    (count(*) - count(customer_email)) * 100.0 / count(*) AS customer_email_null_rate,
    -- ... (every column covered)
    (count(*) - count(returned)) * 100.0 / count(*) AS returned_null_rate
FROM data

Result: 49 total rows, 0% null rate across all 18 columns — a complete data quality snapshot in one shot, without the user writing a single line of SQL.

🤯 This is the magic moment. A non-technical user typed a plain English question, and the system produced a production-quality data quality audit query autonomously.

🟡 Query 3 — “Show top 5 categories ranked by total sales and profit margin percentage”

What Gemma 4 generated:

SELECT
    category,
    SUM(sales) AS total_sales,
    (SUM(profit) / SUM(sales)) * 100 AS profit_margin_percentage
FROM data
GROUP BY category
ORDER BY total_sales DESC
LIMIT 5

Result: 3 categories returned with calculated profit margins:

Insight unlocked: Furniture is the highest-revenue category but is running at a loss. Technology and Office Supplies are both profitable — Office Supplies has the best margin.

🟡 Query 4 — “Show me monthly revenue trend with total orders count and average order value grouped by month”

What Gemma 4 generated:

SELECT
    date_trunc('month', order_date) AS month,
    SUM(sales) AS total_revenue,
    COUNT(order_id) AS total_orders,
    AVG(sales) AS avg_order_value
FROM data
GROUP BY date_trunc('month', order_date)
ORDER BY month

Result:

Gemma 4 correctly used DuckDB’s native date_trunc('month', ...) function — not a workaround, not a SUBSTR hack — real DuckDB date intelligence applied from natural language.

🔴 Query 5 — “Which customers have placed more than one order? Show their name, order count and total spend ranked by highest spend”

What Gemma 4 generated:

SELECT
    customer_name,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(sales) AS total_spend
FROM data
GROUP BY customer_name
HAVING COUNT(DISTINCT order_id) > 1
ORDER BY total_spend DESC

Result:

Gemma 4 correctly identified the need for HAVING COUNT(DISTINCT ...) > 1 — a classic SQL pattern that trips up many developers, generated correctly from plain English.

🔴 Query 6 — “Find all returned orders, show total profit loss by category and ship mode, sorted by biggest loss first”

What Gemma 4 generated:

SELECT
    category,
    ship_mode,
    SUM(profit) AS total_profit_loss
FROM data
WHERE returned = 'Yes'
GROUP BY category, ship_mode
ORDER BY total_profit_loss ASC

Result:

Every single return came from Furniture. Second Class shipping returns caused the most financial damage. This is exactly the kind of insight a business analyst would spend hours finding — delivered in under 30 seconds via plain English.

💡 Why This Is Genuinely Useful — No SQL Expertise Needed

Look at what just happened across those 6 queries: