That might work, but beware that the resulting table will not inherit any constraints, indexes, or even the primary key of the original. If you want those, it is better to copy the database schema (all or part of it), for example by running .schema in the SQLite shell, editing it as you see fit, and pasting back into the shell.

If I were you, I would do some very careful sanity checking on the copied data before deleting the old.

But what is the best way to see next year that the table definition is changed?

Not sure what you mean by this. Care to expand on this part of the question?

I had hoped that it could be done programmatically, but that does not seem an option then. :'-(

After a year a table could be modified. I hoped that that change could be taken care of by the program. But if the original table has to be created 'manually', then for sure the changes have also to be done manually.

It can be done programmatically. With the sqlite3 shell tool:

sqlite3 current.db ".schema" | sqlite3 history.db
sqlite3 history,db \
 "ATTACH 'current.db' AS cur" \
 "INSERT INTO main.table1 SELECT * FROM cur.table1 WHERE recdate < something" \
 "INSERT INTO main.table2 SELECT * FROM cur.table2 WHERE recdate < something" \
 ...
 "DELETE FROM cur.table1 WHERE recdate < soething" \
 "DELETE FROM cur.table2 WHERE recdate < something" \
 ...
 "DETACH cur"

Or in application code, by reading the sqlite_schema table of the "current" database and use the "sql" column to construct the history database. Then the select / delete stuff.

Depends on what you want to archive.

In my quote management system, I want to archive all old quotes and orders (and old notes, documents, etc, relating to them) but keep the actual customer (they might pop back).

As this is yearly, I can simply backup the database, creating a 'snapshot' of year X, then go through and delete what I don't need anymore.

I had hoped that it could be done programmatically, but that does not seem an option then.

Depends on how your database is built - foreign keys, etc. If I delete order X, then it can delete all related gumph for that order. I just need to identify what orders to delete. So it's 'sort of' programmatic. I don't have to delete every record individually, but do have to pick what orders to delete (can be a simple query).

That looks like a very good setup. (With minor tweaks.) I suppose I need to do a BEGIN COMMIT after the ATTACH and an END COMMIT before the DETACH?

Thanks.

And it is also handy to use a variable for the table:

    where="WHERE date NOT LIKE '2022-%'"
    table=directoryUsage
    sqlite3 generalHistory.sqlite           \
    "ATTACH 'general.sqlite' AS cur"        \
    "BEGIN TRANSACTION"                     \
    "INSERT INTO main.${table}
     SELECT * FROM cur.${table} ${where}"   \
    "DELETE FROM cur.${table} ${where}"     \
    "COMMIT TRANSACTION"                    \
    "DETACH cur"
    sqlite3 general.sqlite VACUUM

I created a bash script to do what I want.

Was not that hard. :-D