How do I “flush” data to my RSQLite disk database?

You’re not using the pattern suggested by the RSQLite documentation. That documentation uses dbWriteTable to copy a data frame into a SQLite table:

dbWriteTable(con, "mtcars", mtcars)

According to this documentation, your full code would look something like this:

con <- dbConnect(RSQLite::SQLite(), "./mtcars.db")
data(mtcars)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
# Fetch all query results into a data frame:
dbGetQuery(con, "SELECT * FROM mtcars")

The copy_to() method for dbplyr sources (dbplyr:::copy_to.src_sql()) has a temporary argument which is set to TRUE by default. This means that the new table will be visible only for your active connection and disappear after you close the connection. The following should work as expected:

copy_to(con, mtcars, "mtcars", temporary = FALSE)

Alternatively, use dbWriteTable() as Tim suggests.