Project Logs

Collapse

• Verifying The Underlying Data Pipeline Is Working Correctly

  Bret Bernhoft • 05/28/2026 at 00:58 • 0 comments

  Instead of building a polished dashboard next, I wanted to confirm the underlying data pipeline was working. So this evening I inspected the SQLite database directly to get a quick row count across four tables.

  Using the terminal, I opened the project database with:
  

  sqlite3 data/radar.sqlite 

  Then I ran a small SQL query to count the rows in the core tables:

  SELECT 'posts' AS table_name, COUNT(*) AS rows FROM posts 
  UNION ALL 
  SELECT 'authors', COUNT(*) FROM authors
  UNION ALL
  SELECT 'matches', COUNT(*) FROM post_keyword_matches
  UNION ALL
  SELECT 'runs', COUNT(*) FROM collection_runs; 

  The results were encouraging:
  

  posts|62952
  authors|30138
  matches|294065
  runs|497 

  Which means the system has collected 62,952 posts, tracked 30,138 authors, recorded 294,065 keyword matches and completed 497 collection runs. For a local project running on my own hardware, this is a healthy amount of data to start analyzing.

  However these stats are slightly skewed because the system had two restarts during the collection period. Those restarts affect how cleanly the run count and collection continuity should be interpreted. The totals are still useful, but they should not be treated as a perfectly uninterrupted measurement window. In future versions, I will add restart tracking, run session IDs or metadata for distinguishing between normal collection cycles and interrupted sessions.

  Overall, this was a good validation checkpoint. The collector is producing real data, the SQLite structure is easy to inspect and the next step is to turn these raw counts into visual analysis, trends and eventually a more complete OSINT dashboard.

• Always-On Headless Bluesky OSINT Radar Node Launched

  Bret Bernhoft • 05/20/2026 at 23:36 • 0 comments

  Today I reached the first real milestone for this project; launching an OSINT appliance running in my homelab, collecting public Bluesky activity for specific keywords and storing the results in SQLite for later review.

  A major motivator for this project is wanting to experiment with headless machines on my local network, quietly collecting public conversation data over time. The technology stack is simple, consisting of Linux Mint, Python, Flask, SQLite, the ATProto API and eventually D3.js for visualization.

  The first version of this project started as a collector script with output available in the terminal. It loaded credentials from a .env file, authenticated with Bluesky, pinged the ATProto search API and normalized the returned posts into records. 

  From there, I moved the project into a more always-on mode. I added a Flask server and SQLite database. The Flask app now owns the database, exposes various endpoints and runs scheduled collection automatically. The collection interval is currently set to every 20 minutes.
  

  The database schema is designed around four core tables. Those being posts, authors, post_keyword_matches and collection_runs. This structure prevents exact duplicate posts by using the Bluesky post URI as the primary key, while preserving useful repeated observations over time.

  Ultimately, the OSINT radar node is collecting. The database is clean. The appliance is alive.

• Removed Original Project Files From Raspberry Pi 5 Computer

  Bret Bernhoft • 05/14/2026 at 01:50 • 0 comments

  Today’s progress focused on removing my first attempt at developing this application on a Raspberry Pi 5, while confirming the existing Pi-hole installation remained healthy and unaffected.

  The first step was to verify whether any application databases still existed on the RPi 5 system. A search was run for common SQLite database file types. The results showed several databases including Pi-hole databases, Mesa graphics cache files, Linux system databases and Docker metadata. None of these were related to the deleted project, so no unnecessary database files were removed.

  Next, systemd services were reviewed to identify anything running from the old application. This revealed the Headless Bluesky OSINT Radar service was still installed and active. The service definition was inspected, indicating the presence of the original project directory, a related Python virtual environment, a loaded .env file and app.py script. This confirmed the exact project path and service file needing to be removed.

  The cleanup plan included stopping and disabling the systemd service, deleting the service file, reloading systemd and removing the remaining project directory. Additional commands were used to check for any leftover files, running processes, Docker containers and Docker volumes from the old project.

  After completely removing the old application, Pi-hole was checked to ensure the Raspberry Pi’s DNS filtering service was still functioning correctly. The service was confirmed to be active and running. A DNS test also returned a successful response, confirming the local DNS resolution through Pi-hole was working properly.

  Moving forward, instead of using a Raspberry Pi 5 computer, this project is utilizing a Linux Mint machine. Which will be documented in future log entries and project details.

View all 3 project logs