johnwatson11218

https://github.com/johnwatson11218/LatentTopicExplorer/

This tool allows you to read in a collection of pdf files and it will use AI to create clusters of documents based on semantic content. Pdfplumber -> postgresql -> 1 doc 2 many pages -> pages embedded using sentence_transformers -> 384 reduced to 2d for plotting using UMAP -> custers identified using HDBScan -> labels identifed using class based tf-idf implemented inside of postgresql using stored procedures. Once the files are read in the system is more stable and reliable. My personal document collection in about 1200 files and the ingestion takes 12 to 24 hours on my various laptops. Once that is done the website is very fast.

I'm using postgres as part of my current project - https://github.com/johnwatson11218/LatentTopicExplorer

I had added Spacey to my codebase for one of its features and found that just doing the work in the db was near instant and my containers would not run out of ram. I want to get back to Spacey for more involved nlp work but right now the db "just works". I think Oracle is nicer but postgres does what I need for a lot less money!

https://github.com/johnwatson11218/LatentTopicExplorer/commi...

I posted my code https://github.com/johnwatson11218/LatentTopicExplorer

https://github.com/johnwatson11218/LatentTopicExplorer

You have to use docker compose to get to localhost:8000 , there are still bugs but I'm working on it and there was interest expressed in this project on Hacker News a couple of weeks back.

Thanks for the supportive comments. I'm definitely thinking I should release sooner rather than later. I have been using LLM for specific tasks and here is some sample stored procedure I had an LLM write for me.

-- -- Name: refresh_topic_tables(); Type: PROCEDURE; Schema: public; Owner: postgres --

CREATE PROCEDURE public.refresh_topic_tables() LANGUAGE plpgsql AS $$ BEGIN -- Drop tables in reverse dependency order DROP TABLE IF EXISTS topic_top_terms; DROP TABLE IF EXISTS topic_term_tfidf; DROP TABLE IF EXISTS term_df; DROP TABLE IF EXISTS term_tf; DROP TABLE IF EXISTS topic_terms;

    -- Recreate tables in correct dependency order
    CREATE TABLE topic_terms AS
    SELECT
        dt.term_id,
        dot.topic_id,
        COUNT(DISTINCT dt.document_id) as document_count,
        SUM(frequency) as total_frequency
    FROM document_terms dt
    JOIN document_topics dot ON dt.document_id = dot.document_id
    GROUP BY dt.term_id, dot.topic_id;

    CREATE TABLE term_tf AS
    SELECT
        topic_id,
        term_id,
        SUM(total_frequency) as term_frequency
    FROM topic_terms
    GROUP BY topic_id, term_id;

    CREATE TABLE term_df AS
    SELECT
        term_id,
        COUNT(DISTINCT topic_id) as document_frequency
    FROM topic_terms
    GROUP BY term_id;

    CREATE TABLE topic_term_tfidf AS
    SELECT
        tt.topic_id,
        tt.term_id,
        tt.term_frequency as tf,
        tdf.document_frequency as df,
        tt.term_frequency * LN( (SELECT COUNT(id) FROM topics) / GREATEST(tdf.document_frequency, 1)) as tf_idf
    FROM term_tf tt
    JOIN term_df tdf ON tt.term_id = tdf.term_id;

    CREATE TABLE topic_top_terms AS
    WITH ranked_terms AS (
        SELECT
            ttf.topic_id,
            t.term_text,
            ttf.tf_idf,
            ROW_NUMBER() OVER (PARTITION BY ttf.topic_id ORDER BY ttf.tf_idf DESC) as rank
        FROM topic_term_tfidf ttf
        JOIN terms t ON ttf.term_id = t.id
    )
    SELECT
        topic_id,
        term_text,
        tf_idf,
        rank
    FROM ranked_terms
    WHERE rank <= 5
    ORDER BY topic_id, rank;

    RAISE NOTICE 'All topic tables refreshed successfully';
   

I did something similar whereby I used pdfplumber to extract text from my pdf book collection. I dumped it into postgresql, then chunked the text into 100 char chunks w/ a 10 char overlap. These chunks were directly embedded into a 384D space using python sentence_transformers. Then I simply averaged all chunks for a doc and wrote that single vector back to postgresql. Then I used UMAP + HDBScan to perform dimensionality reduction and clustering. I ended up with a 2D data set that I can plot with plotly to see my clusters. It is very cool to play with this. It takes hours to import 100 pdf files but I can take one folder that contains a mix of programming titles, self-help, math, science fiction etc. After the fully automated analysis you can clearly see the different topic clusters.

I just spent time getting it all running on docker compose and moved my web ui from express js to flask. I want to get the code cleaned up and open source it at some point.

I've read that by the end of ancient Egyptian history they had used tricks like a picture of an eye for the letter or sound 'I' or a picture of a bee for the sound of 'B' there was a complete alphabet embedded within the system. To be literate you had to know the tricks from the ancient and middle kingdoms as well. The result was three complete alphabets, similar to modern Japanese. From that point of view the invention of the alphabet was more of a simplification. This always reminded me of the situation in modern enterprise development where lots of infrastructure was written in-house.

They talk about the specific systems in terms of legacy code and how far removed government agencies are from automated testing and other modern, best practices. It has been a couple of years since I read it but I recall a part about a business process at the IRS that that people don't start learning until they have been there for about 17 years - due to the complexity. It talks about how there had been failed attempts to migrate to a new database, some of the data is now duplicated but the upgrade is de-funded so all the new code has to be aware that data may be duplicated.

I'm not sure if this book got into it but I've also read that the IRS has assembly code from the 1960s that is very optimized and only a few devs can work on it. ChatGPT knows a lot about this history as well.

If you want to know what you are up against I highly recommend - https://www.amazon.com/Recoding-America-Government-Failing-D...

This book discusses the IT systems at the IRS and VA and shows the kind of push back you can expect from entrenched players.

I have a pdf of this book and was using LLM to translate the old code into modern, idiomatic python and it is very cool. I wonder if somebody will re-release it with modern code and tooling? In fact , google Gemini was able to do it on the fly using the posted links.

I have a pipeline in Docker compose that starts up postgresql on one container and a python container. The python scripts will recursively read all the pdf files in a directory, use pdf plumber to parse the text to store in a postgres table. Then I use sentence_transformers to take 100 char, w/ 10 char overlap, chunks and embed each section as a 384D vector which is written back to the db. Then I average all the chunks to create a single embedding for the entire pdf file. I have used numpy as well as built in postgres functions to average and it fast either way.

Then I use HMAP + DBSCAN to create a 2D projection of my dataset. DBSCAN writes the clusters to a csv file. I read that back in to create topics, docs2topcs join table. Then I join each topic into a mega doc and consider the original corpus, I compute tf-idf, using only db functions. This gives me the top 5 or so terms per topic and serves as useful topic labels.

I can do 30 to 50 docs in an couple of hours. I imported 1100 pdf files and it took all weekend on an old gaming laptop w/ a ssd. I have a gpu, and I think the embedding steps would go faster but I'm still doing it all synchronously w/o any parallel processing.

I just got a project running whereby I used python + pdfplumber to read in 1100 pdf files, most of my humble bundle collection. I extracted the text and dumped it into a 'documents' table in postgresql. Then I used sentence transformers to reduce each 1K chunk to a single 384D vector which I wrote back to the db. Then I averaged these to produce a document level embedding as a single vector.

Then I was able to apply UMAP + HDBSCAN to this dataset and it produced a 2D plot of all my books. Later I put the discovered topic back in the db and used that to compute tf-idf for my clusters from which I could pick the top 5 terms to serve as a crude cluster label.

It took about 20 to 30 hours to finish all these steps and I was very impressed with the results. I could see my cookbooks clearly separated from my programming and math books. I could drill in and see subclusters for baking, bbq, salads etc.

Currently I'm putting it into a 2 container docker compose file, base postgresql + a python container I'm working on.

Everyone is talking about having LLMs write software but what about having them delete code? That can be very hard in a legacy enterprise environment. I think dead code detection overlaps with security and that is a good way to sell that kind of code clean up. Having LLMs review your architecture is a fun exercise, being able to incorporate that feedback is a good measure for the dev teams.

I think the Oracle Transaction Manager is one of the best pieces of software that I had to work with in a professional settings. Lots of other stuff in an enterprise setting is very flaky and follows trends but the Oracle internals seem very nice.

My prompt that I couldn't get the LLM to understand was the following. I was having it generate images of depressing offices with no windows and with lots of depressing, grey cubicles with paper all over the floor. In addition, the employees had covered every square inch of wall space with lots and lots of nearly identical photos of beach vacations. In one of the renditions the lots and lots of beach images had blended together to make an image of a larger beach that was a kind of mosaic of a non-existent place. Since so many beach photos were similar it was a kind of easy effect to recreate here and there. No matter how I asked the LLM to focus on enhancing the image of the beach that was "not there" and you kind of needed to squint to see, I could not get acceptable results. Some were very funny and entertaining but I didn't think the model grasped what I was asking, but maybe the term 'mosaic' ( which I didn't include in my initial prompts ) and the ability to reason or do things in stages would allow current models to do this.

♠♥♣♦

I was working with deepseek to distill my thoughts on this and the best quote was " This resembles a lossy compression of software, where the "loss" is non-essential complexity.".

"literate programming meets LLMs"—where the goal isn’t just fewer lines, but denser meaning. If you’re experimenting with it, start small: try distilling a single function with GPT-4 + human review, and see if the result feels correct but simpler. In other words LLM assisted code refactoring for compression and clarity is the way to resolve the argument between 'more' or 'less' code in general.

Why did mathematicians invent new symbols? Imagine if all of algebra, calculus, linear algebra looked like those word problems from antiquity? Natural language is not good for describing systems, symbolic forms are more compressed and be considered a kind of technology in its own right.

A good game is supposed to be "easy to learn and hard to master". I think software abstractions should have this property as well. Too often the next "fix" in a long chain of failed ideas in overly engineered software feels like the Batman games where one has to complete a mini tutorial to learn to use the "bat-whatever" for a single application/puzzle. Contrast this with the Borderlands franchise, I can learn to play Borderlands in 5 minutes and explore the skills tree and whatnot at my leisure if at all. You hear about "Deus ex machina" as a lazy trait in writing, but it is commonplace in enterprise software. Load Bearing Abstractions.

I have wanted to do something like this for news websites for a while now. I tried the recipe site, my first url from lifehacker wasn't supported, the second attempt from the curated list gave an error loading site, so maybe make the links clickable, have a top 10 ready to go ( cached) at bottom of landing page. Write a bot to periodically check the list of sites this works on and indicate how recent said check was done. Then I might spend more time. Good luck!

There was a documentary on Prime Video a couple of years ago that followed a card counter as he moved from casino to casino living out of his RV. I think he was up around $600K after one year. He was having to get kicked out of small casinos all over the place and keep moving to new ones every few days. At one point he tells the camera crew that he has to start driving 1/2 a day to skip over a few spots to stay ahead of the surveillance. It didn't look easy but not impossible. He got to set his own hours and be his own boss.

The article you linked, "tetris-sql," appears to be using database extensions to overcome SQL's limitations. While the core SQL language itself is not Turing complete, these extensions, such as Common Table Expressions (CTEs) and procedural languages, can provide the necessary constructs for iterative execution and conditional logic.

By leveraging these extensions, the author has effectively created a Turing complete environment within the database system, allowing them to implement Tetris. However, it's important to note that the implementation would be more convoluted and less efficient compared to a traditional programming language designed for game development.

Essentially, the article is demonstrating how to create a Turing complete environment using SQL and its extensions, but it's not a direct representation of SQL's inherent capabilities.

I think asking here is a good idea but you should also try this question on the LLMs like perplexity, chatGPT , etc. This question might be too high level and not contain enough context, but you can ask the model how to focus your question to specific aspects of your business. The models can outline the tradeoffs you can expect once you pick a tech stack and a team. The models can also suggest how things might go wrong and what you should watch out for. Try and get the LLMs to design your test suite.

Remember: There's no one-size-fits-all answer. The best approach depends on your specific circumstances, goals, and resources.

Yes that is correct, I had read a different article that had a more rust first focus. I just tried to google for a reference to the news release I was referencing, but yea it doesn't really support my comment :(

Recently the federal government issued a security advisory encouraging all new development to be done in Rust. I'm not sure the extent of which agencies this was meant to cover but it struck me as very unrealistic having just done years of java development for a state agency. https://www.nextgov.com/cybersecurity/2024/02/white-house-ur...

I think a lot of the mis-match comes from scale. Most companies want to be operating at 10X of whatever they currently are doing. So there is a clear bias towards solutions that can auto scale and are easy for growing companies to hire for.

It might make sense for you to bite the bullet and settle down in some out of the way software shop where there is no conceit of needing to scale. State and Local government come to mind. Be prepared for other tradeoffs and inefficiencies.

When the teams struggle to get a baseline suite working, adding k8s is not helpful.

Last year I left a job that was about to give me the maintenance of a poorly implemented kafka system. I'm so glad I left before I really had to work with that system.

Since then I've been reading about async and await in the newer versions of javascript and it really threw me for a loop. I needed this to slow down some executing code but as I worked through my problems I realized "my god! this is exactly what we could have used for pub/sub at my last job".

We could have replaced a kafka system as well an enterprise workflow system with javascript and the async/await paradigm. Some of these systems cost millions per year to license and administer.

My favorite was when a manager tried to get me to agree with the statement that "math was just for the numbers right?". Meaning not character strings nor dates. I was dumbstruck by the question.

The idea of the 3 levels really resonated with the ideas in "Bernoulli's Fallacy" as well. Right now we are seeing a resurgence of Bayesian reasoning across all fields that deal with data and statistical reasoning. I think many errors of modern civilization were caused by people at a level 2 understanding attempting to operationalize their knowledge for others at level 1.

We need it to become much more common to operate at level 3, especially in fields like enterprise software development.