Merge origin/develop into feature/schema-indexes

Conflicts in UPGRADE.md and docs/schema-postgres.sql: kept develop's
durable-turns and starter-defaults notes and DDL; the index audit block
now follows the durable-turns block at the end of the schema.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

CHANGELOG.md

@@ -23,6 +23,8 @@
 
 ## Unreleased - Starter defaults
 
+- Every seed changeset's rollback is empty: a seeded row cannot be told apart from a host's own row with the same name, and Liquibase also rolls back changesets it marked ran, so a name-based undo would delete host data.
+
 - Fix the defaults changelog: every precondition is a portable `sql-check` (the formatted-SQL `table-exists` form failed to parse); the policy, budget and prices are seeded only into empty tables and the profile only when no default exists, so an existing host never changes on deploy. The test now runs the changelog through Liquibase 4.33, including an already configured host and a missing table.
 - The Setup card prices the default model the way calls are costed (registry, price rows, configured prices); "Add rows for every table" reads fresh data-access rows.
 

UPGRADE.md

@@ -1,5 +1,15 @@
 # Upgrading qbit-assistant
 
+## Index audit (2026-10-14)
+
+Recommended, not required: apply the "Index audit" block at the end of
+`docs/schema-postgres.sql`. It adds six indexes, every one `IF NOT EXISTS`:
+- usage by turn (read on each scheduled run)
+- the three retention date filters (turn events, tool calls, inactive conversations)
+- the two `ON DELETE SET NULL` foreign keys on `assistant_scheduled_run`
+
+No code change.
+
 ## Durable turns (Unreleased)
 
 Apply the **Durable turns** block at the end of `docs/schema-postgres.sql` before

docs/schema-postgres.sql

@@ -592,3 +592,14 @@ CREATE INDEX assistant_turn_user_status ON assistant_turn (user_id, status);
 -- Check for pre-existing duplicate message.end rows before applying this index.
 CREATE UNIQUE INDEX assistant_turn_event_terminal ON assistant_turn_event (turn_id)
    WHERE event_type = 'message.end';
+
+-- Index audit (2026-10-14): lookup, retention and FK paths with no leading index.
+-- ScheduledTurn.cost reads usage by turn on every scheduled run.
+CREATE INDEX IF NOT EXISTS assistant_usage_turn ON assistant_usage (turn_id);
+-- Retention: deleteTurnEventsBefore, clearToolResultsBefore and listConversationsInactiveSince filter by date.
+CREATE INDEX IF NOT EXISTS assistant_turn_event_create_date ON assistant_turn_event (create_date);
+CREATE INDEX IF NOT EXISTS assistant_tool_call_create_date ON assistant_tool_call (create_date);
+CREATE INDEX IF NOT EXISTS assistant_conversation_modify_date ON assistant_conversation (modify_date);
+-- ON DELETE SET NULL children: deleting a conversation or turn would scan scheduled runs without these.
+CREATE INDEX IF NOT EXISTS assistant_scheduled_run_conversation ON assistant_scheduled_run (conversation_id);
+CREATE INDEX IF NOT EXISTS assistant_scheduled_run_turn ON assistant_scheduled_run (turn_id);

qbit-assistant/src/main/resources/db/liquibase/qbit-assistant/assistant-defaults.changelog.sql

@@ -9,6 +9,9 @@
 -- Rules for this file:
 --   * insert-only: every row is inserted only when its natural key is absent,
 --     so an administrator's edits always survive and nothing is ever updated;
+--   * rollback is empty: a seeded row cannot be told apart from a host's own
+--     row with the same name, and Liquibase also rolls back changesets it marked
+--     ran, so undoing would delete the host's data (remove seeded rows by hand);
 --   * no secrets: environment variable NAMES only, never values;
 --   * portable SQL (PostgreSQL and H2): INSERT ... SELECT ... WHERE NOT EXISTS;
 --   * every changeset has a precondition on its table, so a host that has not
@@ -22,7 +25,7 @@
 INSERT INTO assistant_policy (name, kill_switch, writes_require_confirmation, redact_pii, max_rows_per_call, create_date, modify_date)
 SELECT 'default', FALSE, TRUE, TRUE, 50, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_policy);
--- rollback DELETE FROM assistant_policy WHERE name = 'default';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-2
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -37,7 +40,7 @@ Which tables can I look at?
 What changed in the last 7 days?',
    TRUE, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_profile WHERE name = 'default' OR is_default = TRUE);
--- rollback DELETE FROM assistant_profile WHERE name = 'default';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-3
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -53,7 +56,7 @@ SELECT 'find-and-count', 'Finding, filtering and counting records.',
 5. If the result was truncated, say so and offer the link instead of listing more.',
    TRUE, 'find,show,list,which,how many,count,filter,search,look up', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_skill WHERE name = 'find-and-count');
--- rollback DELETE FROM assistant_skill WHERE name = 'find-and-count';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-4
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -68,7 +71,7 @@ SELECT 'explain-a-record', 'Summarizing a record and who changed it.',
 4. Link to the record.',
    TRUE, 'explain,summarize,summary,tell me about,what is,status,history,changed,who changed,audit', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_skill WHERE name = 'explain-a-record');
--- rollback DELETE FROM assistant_skill WHERE name = 'explain-a-record';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-5
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -83,7 +86,7 @@ SELECT 'safe-changes', 'Proposing creates, updates and deletes safely.',
 4. For many rows from a file, use the bulk load tools and always run bulk_load_dry_run and show its summary before bulk_load_run.',
    TRUE, 'change,update,set,edit,create,add,new,delete,remove,fix,rename,import,load', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_skill WHERE name = 'safe-changes');
--- rollback DELETE FROM assistant_skill WHERE name = 'safe-changes';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-6
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -97,7 +100,7 @@ SELECT 'access-questions', 'Explaining why data is missing or not available.',
 3. Suggest the next step: check the filter, open the list screen, or ask an administrator for access.',
    TRUE, 'cannot see,can''t see,missing,not found,denied,access,permission,not allowed,hidden', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_skill WHERE name = 'access-questions');
--- rollback DELETE FROM assistant_skill WHERE name = 'access-questions';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-7
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -109,7 +112,7 @@ FROM assistant_profile p, assistant_skill s
 WHERE p.name = 'default'
   AND s.name IN ('find-and-count', 'explain-a-record', 'safe-changes', 'access-questions')
   AND NOT EXISTS (SELECT 1 FROM assistant_profile_skill ps WHERE ps.profile_id = p.id AND ps.skill_id = s.id);
--- rollback DELETE FROM assistant_profile_skill WHERE profile_id IN (SELECT id FROM assistant_profile WHERE name = 'default') AND skill_id IN (SELECT id FROM assistant_skill WHERE name IN ('find-and-count', 'explain-a-record', 'safe-changes', 'access-questions'));
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-8
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -120,7 +123,7 @@ SELECT 'Monthly spend (whole instance)', TRUE, 'INSTANCE', 'MONTH', 'COST_USD',
    'Starter budget. Warns everyone at 80% of $50 a month, switches to the cheapest allowed model at 100% (or stops when there is none), and stops at 120%. Set Downgrade Model to choose the fallback; add an override to raise it temporarily.',
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 WHERE NOT EXISTS (SELECT 1 FROM assistant_budget);
--- rollback DELETE FROM assistant_budget WHERE name = 'Monthly spend (whole instance)';
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-9
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -143,7 +146,7 @@ FROM (VALUES
    ('anthropic', 'claude-haiku-4-5', 1.00, 5.00, 0.10, 1.25, 2.00, 'Anthropic list price, 2026-10')
 ) AS v (provider_type, model, input_price, output_price, cache_read, cache_write5m, cache_write1h, notes)
 WHERE NOT EXISTS (SELECT 1 FROM assistant_model_price p);
--- rollback SELECT 1; -- prices may have been edited; keep them
+-- rollback empty
 
 -- changeset qbit-assistant:defaults-10
 -- preconditions onFail:MARK_RAN onError:MARK_RAN
@@ -156,4 +159,4 @@ SELECT 'Example: weekly change summary', FALSE,
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
 FROM (SELECT MIN(id) AS id FROM assistant_profile WHERE name = 'default') p
 WHERE NOT EXISTS (SELECT 1 FROM assistant_scheduled_task WHERE name = 'Example: weekly change summary');
--- rollback DELETE FROM assistant_scheduled_task WHERE name = 'Example: weekly change summary' AND enabled = FALSE;
+-- rollback empty

qbit-assistant/src/test/java/com/kingsrook/qbits/assistant/AssistantDefaultsChangelogTest.java

@@ -144,6 +144,33 @@ class AssistantDefaultsChangelogTest extends BaseAssistantTest
 
 
 
+   /*******************************************************************************
+    ** Rolling the changelog back never deletes data: not the host's own rows
+    ** (changesets marked ran included), not the seeded ones.
+    *******************************************************************************/
+   @Test
+   void rollbackKeepsEveryRow() throws Exception
+   {
+      try(Connection connection = freshSchema(); Statement statement = connection.createStatement())
+      {
+         statement.execute("INSERT INTO assistant_policy (name, kill_switch) VALUES ('default', FALSE)");
+         statement.execute("INSERT INTO assistant_profile (name, display_name, is_default, enabled) VALUES ('default', 'Hal', TRUE, TRUE)");
+         statement.execute("INSERT INTO assistant_skill (name, instructions) VALUES ('find-and-count', 'The host wrote this.')");
+         runChangelog(connection);
+         Map<String, Integer> before = counts(connection);
+
+         Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
+         new Liquibase(CHANGELOG.substring(1), new ClassLoaderResourceAccessor(), database).rollback(10, (String) null);
+
+         assertThat(ranChangesets(connection)).isEmpty();
+         assertThat(counts(connection)).isEqualTo(before);
+         assertThat(scalar(connection, "SELECT display_name FROM assistant_profile WHERE name = 'default'")).isEqualTo("Hal");
+         assertThat(scalar(connection, "SELECT instructions FROM assistant_skill WHERE name = 'find-and-count'")).isEqualTo("The host wrote this.");
+      }
+   }
+
+
+
    /*******************************************************************************
     ** A host without one of the tables: that changeset is marked ran, the
     ** rest apply, nothing fails.
@@ -174,7 +201,7 @@ class AssistantDefaultsChangelogTest extends BaseAssistantTest
       for(String changeset : changesets)
       {
          assertThat(changeset).contains("-- preconditions onFail:MARK_RAN onError:MARK_RAN").contains("-- precondition-sql-check expectedResult:0 ")
-            .doesNotContain("precondition-table-exists").contains("-- rollback ").contains("NOT EXISTS");
+            .doesNotContain("precondition-table-exists").contains("-- rollback empty").contains("NOT EXISTS");
       }
       for(String table : SEEDED_TABLES)
       {