The CREATE script incorporates the following components:

• prologue - json_ops through base_ops;
• ancestor list generation - levels through ancestors (note that the referenced template contains a SELECT-style prologue, which is replaced with a modify-style prologue);
• path_terms removes rows corresponding to already existent categories and adds row numbers;
• ASCII id generation - id_counts through ids;
• new_nodes joins path terms with newly generated IDs.

WITH
    ------------------------------ PROLOGUE ------------------------------
    json_ops(ops) AS (
        VALUES
            (json(
                '['                                                            ||
                    '{"op":"create", "path_new":"BAZ/bld/tcl/tests/safe/"},'   ||
                    '{"op":"create", "path_new":"safe/ssub00/modules/"},'      ||
                    '{"op":"create", "path_new":"safe/ssub00/msys/nix/misc/"}' ||
                ']'
            ))
    ),
    base_ops AS (
        SELECT
            "key" + 1 AS opid,
            json_extract(value, '$.op') AS op,
            json_extract(value, '$.path_old') AS rootpath_old,
            json_extract(value, '$.path_new') AS rootpath_new
        FROM json_ops AS jo, json_each(jo.ops) AS terms
    ),
    /********************************************************************/
    --------------------------- ANCESTOR LIST ----------------------------
    levels AS (
        SELECT opid, rootpath_new AS path, length(rootpath_new) - length(replace(rootpath_new, '/', '')) AS depth
        FROM base_ops
    ),
    json_objs AS (
        SELECT *, json('{"' || replace(rtrim(path, '/'), '/', '": {"') ||
            '":""' || replace(hex(zeroblob(depth)), '00', '}')) AS json_obj
        FROM levels
    ),
    ancestors AS (
        SELECT min(jo.opid) AS opid,
            replace(replace(substr(fullkey, 3), '.', '/'), '^#^', '.') || '/' AS asc_path,
            replace("key", '^#^', '.') AS asc_name
        FROM
            json_objs AS jo,
            json_tree(replace(jo.json_obj, '.', '^#^')) AS terms
        WHERE terms.parent IS NOT NULL
        GROUP BY asc_path
        ORDER BY opid, asc_path
    ),
    /********************************************************************/
    path_terms AS (
        SELECT 
            row_number() OVER (ORDER BY opid, asc_path) AS counter,
            ancestors.*, substr(asc_path, 1, length(asc_path) - length(asc_name) - 1) AS asc_prefix
        FROM ancestors
        LEFT JOIN categories AS cats ON asc_path = cats.path
        WHERE cats.ascii_id IS NULL        
    ),
    ------------------------- ASCII ID GENERATOR -------------------------
    id_counts(id_counter) AS (SELECT count(*) FROM path_terms),
    json_templates AS (SELECT '[' || replace(hex(zeroblob(id_counter*8/2-1)), '0', '0,') || '0,0]' AS json_template FROM id_counts),
    char_templates(char_template) AS (VALUES ('-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_')),
    ascii_ids AS (
        SELECT group_concat(substr(char_template, (random() & 63) + 1, 1), '') AS ascii_id, "key"/8 + 1 AS counter
        FROM char_templates, json_templates, json_each(json_templates.json_template) AS terms
        GROUP BY counter
    ),
    ids AS (
        SELECT counter, ascii_id,
               (unicode(substr(ascii_id, 1, 1)) << 8*7) +
               (unicode(substr(ascii_id, 2, 1)) << 8*6) +
               (unicode(substr(ascii_id, 3, 1)) << 8*5) +
               (unicode(substr(ascii_id, 4, 1)) << 8*4) +
               (unicode(substr(ascii_id, 5, 1)) << 8*3) +
               (unicode(substr(ascii_id, 6, 1)) << 8*2) +
               (unicode(substr(ascii_id, 7, 1)) << 8*1) +
               (unicode(substr(ascii_id, 8, 1)) << 8*0) AS bin_id
        FROM ascii_ids
    ),
    /********************************************************************/
    new_nodes AS (
        SELECT bin_id AS id, asc_name AS name, asc_prefix AS prefix
		FROM path_terms, ids USING (counter)
	)
INSERT INTO categories (id, name, prefix)
SELECT * FROM new_nodes;