johnwatson11218

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 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...

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.

-- -- 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 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'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.

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

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.

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.

"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.

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.

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

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.

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.