Repository: juanfont/headscale

Issue:
[Bug] v0.26.1 to v.27.0 sqlite failure with 'namespaces' table
### Is this a support request?

- [x] This is not a support request

### Is there an existing issue for this?

- [x] I have searched the existing issues

### Current Behavior

Continuation of my instance mentioned in #2837 after adjusting the ACL's.

Instance has had a ruggedy upgrade path with schema changes and possibly skipping a version in the past and reverting, however reglardless my working v0.26.1 upgrade fails to v0.27.0 with:

```
>> Remove index "idx_namespaces_deleted_at"

>> Remove table "kvs"

>> Remove table "namespaces"

2025-11-07T23:11:50Z FTL home/runner/work/headscale/headscale/cmd/headscale/cli/serve.go:32 > Error initializing error="creating new headscale: init state: init database: validating schema: invalid schema (-got, +want):\n\n>> Remove index \"idx_namespaces_deleted_at\"\n\n>> Remove table \"kvs\"\n\n>> Remove table \"namespaces\"\n"
```

### Expected Behavior

Schema updates between versions should work.



### Steps To Reproduce

With this v0.26.1 schema I get this error in v0.27.0:

```
$ cat schema.sql 
CREATE TABLE `migrations` (`id` text,PRIMARY KEY (`id`));
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE users(
  id integer PRIMARY KEY AUTOINCREMENT,
  name text,
  display_name text,
  email text,
  provider_identifier text,
  provider text,
  profile_pic_url text,
  created_at datetime,
  updated_at datetime,
  deleted_at datetime
);
CREATE TABLE pre_auth_keys(
  id integer PRIMARY KEY AUTOINCREMENT,
  key text,
  user_id integer,
  reusable numeric,
  ephemeral numeric DEFAULT false,
  used numeric DEFAULT false,
  tags text,
  expiration datetime,
  created_at datetime,
  CONSTRAINT fk_pre_auth_keys_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE api_keys(
  id integer PRIMARY KEY AUTOINCREMENT,
  prefix text,
  hash blob,
  expiration datetime,
  last_seen datetime,
  created_at datetime
);
CREATE TABLE nodes(
  id integer PRIMARY KEY AUTOINCREMENT,
  machine_key text,
  node_key text,
  disco_key text,
  endpoints text,
  host_info text,
  ipv4 text,
  ipv6 text,
  hostname text,
  given_name varchar(63),
  user_id integer,
  register_method text,
  forced_tags text,
  auth_key_id integer,
  last_seen datetime,
  expiry datetime,
  approved_routes text,
  created_at datetime,
  updated_at datetime,
  deleted_at datetime,
  CONSTRAINT fk_nodes_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_nodes_auth_key FOREIGN KEY(auth_key_id) REFERENCES pre_auth_keys(id)
);
CREATE TABLE policies(
  id integer PRIMARY KEY AUTOINCREMENT,
  data text,
  created_at datetime,
  updated_at datetime,
  deleted_at datetime
);
CREATE INDEX idx_users_deleted_at ON users(deleted_at);
CREATE UNIQUE INDEX idx_provider_identifier ON users(
  provider_identifier
) WHERE provider_identifier IS NOT NULL;
CREATE UNIQUE INDEX idx_name_provider_identifier ON users(
  name,
  provider_identifier
);
CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users(
  name
) WHERE provider_identifier IS NULL;
CREATE UNIQUE INDEX idx_api_keys_prefix ON api_keys(prefix);
CREATE INDEX idx_policies_deleted_at ON policies(deleted_at);

```

### Environment

```markdown
- OS: Ubuntu 24.04
- Headscale version: v0.27.0
- Tailscale version: 1.86.5
```

### Runtime environment

- [x] Headscale is behind a (reverse) proxy
- [x] Headscale runs in a container

### Debug information

n/a

Hints from issue comments:
@nblock: This should be already fixed in main and will be part of 0.27.1.

Your posted `schema.sql` does not contain the tables `kvs`, `namespaces`. Are you sure its actual schema dump of your 0.26.1 instance? Can you please try to drop the offending tables and try to upgrade to 0.27.0?

Solution patch:
diff --git a/hscontrol/db/db.go b/hscontrol/db/db.go
index 2035ec41..4eefee91 100644
--- a/hscontrol/db/db.go
+++ b/hscontrol/db/db.go
@@ -952,6 +952,34 @@ AND auth_key_id NOT IN (
 					return nil
 				},
 			},
+			{
+				// Drop all indices that are no longer in use and has existed.
+				// They potentially still present from broken migrations in the past.
+				// They should all be cleaned up by the db engine, but we are a bit
+				// conservative to ensure all our previous mess is cleaned up.
+				ID: "202511101554-drop-old-idx",
+				Migrate: func(tx *gorm.DB) error {
+					for _, oldIdx := range []struct{ name, table string }{
+						{"idx_namespaces_deleted_at", "namespaces"},
+						{"idx_routes_deleted_at", "routes"},
+						{"idx_shared_machines_deleted_at", "shared_machines"},
+					} {
+						err := tx.Migrator().DropIndex(oldIdx.table, oldIdx.name)
+						if err != nil {
+							log.Trace().
+								Str("index", oldIdx.name).
+								Str("table", oldIdx.table).
+								Err(err).
+								Msg("Error dropping old index, continuing...")
+						}
+					}
+
+					return nil
+				},
+				Rollback: func(tx *gorm.DB) error {
+					return nil
+				},
+			},
 
 			// Migrations **above** this points will be REMOVED in version **0.29.0**
 			// This is to clean up a lot of old migrations that is seldom used



Test patch:
diff --git a/hscontrol/db/testdata/sqlite/headscale_0.26.1_dump_schema-to-0.27.0-old-table-cleanup.sql b/hscontrol/db/testdata/sqlite/headscale_0.26.1_dump_schema-to-0.27.0-old-table-cleanup.sql
index 388fefbc..d911e960 100644
--- a/hscontrol/db/testdata/sqlite/headscale_0.26.1_dump_schema-to-0.27.0-old-table-cleanup.sql
+++ b/hscontrol/db/testdata/sqlite/headscale_0.26.1_dump_schema-to-0.27.0-old-table-cleanup.sql
@@ -31,10 +31,15 @@ CREATE UNIQUE INDEX idx_name_provider_identifier ON users (name,provider_identif
 CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users (name) WHERE provider_identifier IS NULL;
 
 -- Create all the old tables we have had and ensure they are clean up.
-CREATE TABLE `namespaces` (`id` text,PRIMARY KEY (`id`));
+CREATE TABLE `namespaces` (`id` text,`deleted_at` datetime,PRIMARY KEY (`id`));
 CREATE TABLE `machines` (`id` text,PRIMARY KEY (`id`));
 CREATE TABLE `kvs` (`id` text,PRIMARY KEY (`id`));
-CREATE TABLE `shared_machines` (`id` text,PRIMARY KEY (`id`));
+CREATE TABLE `shared_machines` (`id` text,`deleted_at` datetime,PRIMARY KEY (`id`));
 CREATE TABLE `pre_auth_key_acl_tags` (`id` text,PRIMARY KEY (`id`));
-CREATE TABLE `routes` (`id` text,PRIMARY KEY (`id`));
+CREATE TABLE `routes` (`id` text,`deleted_at` datetime,PRIMARY KEY (`id`));
+
+CREATE INDEX `idx_routes_deleted_at` ON `routes`(`deleted_at`);
+CREATE INDEX `idx_namespaces_deleted_at` ON `namespaces`(`deleted_at`);
+CREATE INDEX `idx_shared_machines_deleted_at` ON `shared_machines`(`deleted_at`);
+
 COMMIT;