A "rowid table" is any table in an SQLite schema that
Rowid tables are distinguished by the fact that they all have a unique, non-NULL, signed 64-bit integer [rowid] that is used as the access key for the data in the underlying [B-tree] storage engine.
The [PRIMARY KEY] of a rowid table (if there is one) is usually not the true primary key for the table, in the sense that it is not the unique key used by the underlying [B-tree] storage engine. The exception to this rule is when the rowid table declares an [INTEGER PRIMARY KEY]. In the exception, the INTEGER PRIMARY KEY becomes an alias for the [rowid].
The true primary key for a rowid table (the value that is used as the key to look up rows in the underlying [B-tree] storage engine) is the [rowid].
The PRIMARY KEY constraint for a rowid table (as long as it is not the true primary key or INTEGER PRIMARY KEY) is really the same thing as a [UNIQUE constraint]. Because it is not a true primary key, columns of the PRIMARY KEY are allowed to be NULL, in violation of all SQL standards.
The [rowid] of a rowid table can be accessed (or changed) by reading or writing to any of the "rowid" or "oid" or "_rowid_" columns. Except, if there is a declared columns in the table that use those special names, then those names refer to the declared columns, not to the underlying [rowid].
Access to records via [rowid] is highly optimized and very fast.
If the [rowid] is not aliased by [INTEGER PRIMARY KEY] then it is not persistent and might change. In particular the [VACUUM] command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
In the underlying [file format], each rowid is stored as a [variable-length integer]. That means that small non-negative rowid values take up less disk space than large or negative rowid values.
All of the complications above (and others not mentioned here) arise from the need to preserve backwards compatibility to the tens of billions of SQLite database files in circulation. In a perfect world, there would be no such thing as a "rowid" and all tables would following the standard semantics implemented as [WITHOUT ROWID] tables, only without the extra "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of SQLite offers his sincere apology for the current mess.