The Story of How I Built a Site to Cross-Search Internal Accumulated Memos with the Help of AI
To start with, I am someone who cannot read code at all.
Even so, as someone in charge of back-office operations for a small cram school management company, I have long been frustrated by the lack of a system to pull up scattered business records in one go. This time, I finally built an internal "information log search site" with my own hands (or more accurately, with the help of AI) alongside Claude.
Some of this might be obvious, but I want to keep it as a diary. If there are better ways to do this, please let me know.
Before: What I was struggling with
Internal business memos really end up scattered everywhere before you know it.
Business report memos are in Word files
Daily work memos are in text files or spreadsheets
Casual insight memos are also in different folders and formats
Even when I think, "I know I wrote that down somewhere before...", I can't remember which folder or file it's in. I often find myself opening folders one by one, opening files one by one, and searching with Ctrl+F, only to forget what I was looking for in the first place.
I wanted something like an "internal log search site," but due to the nature of the information, I was a bit afraid to put it on an external cloud. The starting point for this project was the wish to create an internal-only search site that stays entirely within our company's rental server.
What I did (in summary)
After consulting with Claude, the technical configuration settled on something like this.
Place a small site using PHP on the rental server
Store data in a file-based database called SQLite
Use a full-text search mechanism called FTS5 to speed up searches
Apply BASIC authentication and force HTTPS at the entrance so it cannot be accessed from outside the company in principle
...Writing it out like that makes it sound technical, but I started at the level of "What does PHP stand for again?" I think the division of labor was that Claude wrote everything, and I just placed and replaced the files.
I first created a UI mockup locally to compare color schemes, headers, and badge displays. Once we decided, "Let's go with this," I put the set of PHP files (top page/search/individual display/upload/DB initialization) on the server, uploaded 85 pieces of actual data, and confirmed it was working.
It might look like it went smoothly up to this point, but of course, there were hurdles.
Hurdle 1: The "It hits for compound words, but zero hits for parts of them" problem
After inputting 85 pieces of data and trying to search, this was the discrepancy I noticed.
For example, when searching for the compound word "Training Report," it hits 3 results correctly. If the record has a [Training] tag in the body, it hits 5 results in the heading search.
However, searching for just "Training" yields 0 results.
I did a double-take at the screen, thinking, "No way, 'Training Report' is clearly written right there in the body..."
When I consulted Claude, asking, "This didn't work, why?", the cause seemed to be a mechanism called the FTS5 tokenizer. The default "unicode61" tokenizer treats consecutive Japanese kanji as a "single block." So, when searching for a partial match of "Training" against the block "Training Report," it doesn't match because they are different things at the token level, or so it seems.
Honestly, at first, I only understood about half of what was being said. So, I asked it to "break it down again so someone who can't read code can understand," and I finally felt like I grasped the point that "I was using a search engine that isn't good at Japanese."
Claude suggested four solutions.
Option A: Switch to a trigram (split by 3-character units) tokenizer
Option B: Switch to LIKE search (traditional partial matching) without using FTS5
Option C: Separate keywords into a different table and search as tags
Option D: Split compound words into smaller parts on the script side when importing data and register them
Each has its pros and cons, so I decided not to make an immediate decision here. Since most of the 85 pieces of data still work if they hit on a full compound word match, the honest truth is that I decided to "postpone the partial search issue until next time" and prioritize other maintenance today.
I think that even if I couldn't have done it on my own, just "recognizing the existence of the wall and setting it as a theme for next time" instead of "having to solve everything right now" is a big step. With AI, it's faster to gather the materials for making decisions.
Wall 2: "VACUUM INTO SQL statement in progress error"
This was an error that occurred in a different situation than the partial search.
When I had Claude write a migration script to organize the DB (database) state and ran it, this English error appeared.
cannot VACUUM - SQL statements in progress
The moment I saw it, I felt a sense of mild despair. A double wall of SQLite terminology plus English.
However, even here, when I consulted Claude, saying, "I can't read this error, so please tell me what's happening and how to fix it, for someone who can't read code," it broke it down and explained it properly.
In short,
When I tried to perform an operation (VACUUM INTO) to 'clean up the DB file and save it as a separate file',
it turned out that the DB was still in a state where 'another process is still in progress'.
So, it was scolding me, saying 'I cannot VACUUM because other work is not finished'.
…That’s the kind of situation, which in terms of housework, is like 'trying to wash the whole sink while you're still using the dishes'.
What Claude proposed was, 'In v2, let's fix it so that VACUUM INTO is executed as soon as you connect to the DB.' With this simple fix of just changing the order, the error was resolved.
What I realized here is that English error messages aren't scary when you're dealing with AI. Before, every time I saw an English error, I wanted to close the screen, thinking 'I can't do this anymore…', but if I paste it and ask it to 'read, break it down, and fix it', it returns the meaning and how to fix it in three lines. This might have been the biggest psychological change.
Wall 3: 'Rebuilding only the index without touching the DB itself'—is that really okay?
When facing the challenge of partial search (Wall 1), if you choose Option A, 'switch to a trigram tokenizer' out of the four options, you need to recreate the index.
I suddenly got scared here. 'Recreating the index'—doesn't that affect the actual data? Won't the 85 items I painstakingly entered disappear? That was my feeling.
I asked Claude over and over, 'Does this operation touch the actual data? Absolutely?' The explanation I got back was something like this.
The table containing the actual data (the content of the records) and the search index are separate things.
Even if you recreate only the index, it does not touch the main table.
If you are still worried, you can just copy the entire DB file to take a backup before the operation.
…With that flow, we reached an agreement that 'the way of taking a backup and then recreating only the index' is safe.
This, too, for someone who can't read code, 'Claude says it's okay, so it's probably okay' isn't enough. Having it broken down to the point where I can explain in my own words 'what is touched and what is not' became the material for peace of mind, I suppose.
The actual switching work was postponed to next time, just like Wall 1, but I feel like I'm mentally prepared to do it now.
After: What has changed?
85 internal business memos are now searchable in one go from the site's search box.
The time spent digging through folders wondering 'where did I write that?' has become almost zero.
A configuration where only specific people in the company can access it via BASIC authentication + forced HTTPS.
The issue of partial search is already identified as a theme for next time (planning to switch to trigram or LIKE).
Minor operational maintenance tasks, such as the delete function, have also been listed as tasks for next time.
It is not fully automated or completely finished. However, the fact that a small gateway for cross-searching has been launched feels like a significant milestone for me.
And above all, the very fact that I, someone who cannot read code, am hosting PHP on a server and running a search site is a bit surreal. I just upload the files exactly as Claude tells me to, saying 'replace these files entirely,' and the screen just works.
Perhaps 'semi-self-made' is a more accurate term than 'semi-automated'.
Points to Note
Even though it is for internal use, since I am placing files on the web, I wanted to ensure secure operation from the very beginning.
In my case, I consulted with Claude during the initial configuration phase and did the following.
Always apply BASIC authentication + HTTPS enforcement at the entrance.
Include security headers (like CSP) to prevent unnecessary browser behavior.
Configure the folder containing the actual data so it cannot be accessed directly from the web (Deny from all).
Management files such as migration scripts are also not to be kept publicly accessible.
I follow the practice of including these as fixed settings during the initial configuration phase, as I would likely forget if I tried to add them later.
Also, it is a personal rule of mine to always back up the DB file before inserting data. The same applies when touching index-related areas, such as switching tokenizers. I feel that preparing a state where 'I can restore it even if it breaks' before I start breaking things is the best precaution someone who cannot read code can take.
Summary
The biggest takeaway from this experience was that 'English and technical error messages are no longer scary.'
In the past, the moment I saw an English error filling the screen, I would have thought, 'I can't do this, someone help me.' Now, if I paste it into Claude and ask it to 'read, break it down, and fix it,' it returns the cause and solution in three lines. Just being able to receive what is happening and what needs to be done to fix it in Japanese has expanded the scope of what I can do myself many times over.
Also, working with AI makes it easier to decide that 'I don't have to solve everything right now.' When four proposals for the partial search issue were lined up, I didn't rush to choose one and was able to set it as a theme for next time. If I had been carrying this alone, I probably would have pushed myself, thinking 'I have to solve everything today,' and eventually given up halfway through.
Instead of aiming for extreme forms like complete automation or complete self-creation, I am launching useful internal mechanisms little by little, keeping them within a range where my own hands and head can manage them without strain, through just the right amount of semi-automation and semi-self-creation. I think today was another day where I was able to proceed in that manner.
Next time, I plan to properly face the wall of partial search.
In this series, I introduce my real-life experience of streamlining the management of a small cram school using AI and no-code tools.