Populate virtual SQLite FTS5 (full text search) table from content table

Question

I've followed https://kimsereylam.com/sqlite/2020/03/06/full-text-search-with-sqlite.html to set up SQLite's virtual table extension FTS5 for full text search on an external content table. While the blog shows how to set up triggers to keep the virtual FTS table updated with the data:

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    short_description TEXT
)

CREATE VIRTUAL TABLE user_fts USING fts5(
    username, 
    short_description, 
    email UNINDEXED, 
    content='user', 
    content_rowid='id' 
)

CREATE TRIGGER user_ai AFTER INSERT ON user
    BEGIN
        INSERT INTO user_fts (rowid, username, short_description)
        VALUES (new.id, new.username, new.short_description);
    END;
...

I am failing to populate the FTS table from all previous data in an analogous fashion. I'll stick to the example from the blog:

INSERT INTO user_fts (rowid, username, short_description) SELECT (id, username, short_description) FROM user;

However, sqlite (3.37.2) fails with row value misused.

Please explain how id, content_rowid, rowid and new.id are related and how to modify the query to update the FTS table properly.

Answer 1

INSERT INTO user_fts (rowid, username, short_description) SELECT id, username, short_description FROM user;

(no parentheses) works.

rowid is a unique 64 bit unsigned integer row id. If the table contains an integer primary key (as id in user), they are the same (alias). I.e. user.rowid == user.id = user_fts.rowid.
Doc: https://www.sqlite.org/lang_createtable.html#rowid

The new refers to the element being inserted.
Doc: https://www.sqlite.org/lang_createtrigger.html

content_rowid links the virtual FTS table to the external data table row id column (it defaults to rowid).
Doc: https://www.sqlite.org/fts5.html#external_content_tables