Privacy-first password manager with built-in email aliasing. Fully encrypted and self-hostable.
// <auto-generated />
#nullable disable
namespace AliasClientDb.Migrations
{
using System;
using Microsoft.EntityFrameworkCore.Migrations;
/// <inheritdoc />
public partial class _170FieldBasedDataModelUpdate : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
// Disable foreign keys to prevent cascade deletes when dropping old tables
// Must be executed outside of transaction to take effect in SQLite
migrationBuilder.Sql("PRAGMA foreign_keys = OFF;", suppressTransaction: true);
migrationBuilder.DropForeignKey(
name: "FK_Attachments_Credentials_CredentialId",
table: "Attachments");
migrationBuilder.DropForeignKey(
name: "FK_Passkeys_Credentials_CredentialId",
table: "Passkeys");
migrationBuilder.DropForeignKey(
name: "FK_TotpCodes_Credentials_CredentialId",
table: "TotpCodes");
migrationBuilder.RenameColumn(
name: "CredentialId",
table: "TotpCodes",
newName: "ItemId");
// Use raw SQL for safer index dropping with IF EXISTS
migrationBuilder.Sql("DROP INDEX IF EXISTS \"IX_TotpCodes_CredentialId\";");
migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS \"IX_TotpCodes_ItemId\" ON \"TotpCodes\" (\"ItemId\");");
migrationBuilder.RenameColumn(
name: "CredentialId",
table: "Passkeys",
newName: "ItemId");
// Use raw SQL for safer index dropping with IF EXISTS
migrationBuilder.Sql("DROP INDEX IF EXISTS \"IX_Passkeys_CredentialId\";");
migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS \"IX_Passkeys_ItemId\" ON \"Passkeys\" (\"ItemId\");");
migrationBuilder.RenameColumn(
name: "CredentialId",
table: "Attachments",
newName: "ItemId");
// Use raw SQL for safer index dropping with IF EXISTS
migrationBuilder.Sql("DROP INDEX IF EXISTS \"IX_Attachments_CredentialId\";");
migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS \"IX_Attachments_ItemId\" ON \"Attachments\" (\"ItemId\");");
migrationBuilder.CreateTable(
name: "FieldDefinitions",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
FieldType = table.Column<string>(type: "TEXT", maxLength: 50, nullable: false),
Label = table.Column<string>(type: "TEXT", maxLength: 255, nullable: false),
IsMultiValue = table.Column<bool>(type: "INTEGER", nullable: false),
IsHidden = table.Column<bool>(type: "INTEGER", nullable: false),
EnableHistory = table.Column<bool>(type: "INTEGER", nullable: false),
Weight = table.Column<int>(type: "INTEGER", nullable: false),
ApplicableToTypes = table.Column<string>(type: "TEXT", nullable: true),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_FieldDefinitions", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Folders",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
Name = table.Column<string>(type: "TEXT", maxLength: 255, nullable: false),
ParentFolderId = table.Column<Guid>(type: "TEXT", nullable: true),
Weight = table.Column<int>(type: "INTEGER", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_Folders", x => x.Id);
table.ForeignKey(
name: "FK_Folders_Folders_ParentFolderId",
column: x => x.ParentFolderId,
principalTable: "Folders",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "Logos",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
Source = table.Column<string>(type: "TEXT", maxLength: 255, nullable: false),
FileData = table.Column<byte[]>(type: "BLOB", nullable: true),
MimeType = table.Column<string>(type: "TEXT", maxLength: 100, nullable: true),
FetchedAt = table.Column<DateTime>(type: "TEXT", nullable: true),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_Logos", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Tags",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
Name = table.Column<string>(type: "TEXT", maxLength: 255, nullable: false),
Color = table.Column<string>(type: "TEXT", maxLength: 50, nullable: true),
DisplayOrder = table.Column<int>(type: "INTEGER", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_Tags", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Items",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
Name = table.Column<string>(type: "TEXT", maxLength: 255, nullable: true),
ItemType = table.Column<string>(type: "TEXT", maxLength: 50, nullable: false),
LogoId = table.Column<Guid>(type: "TEXT", nullable: true),
DeletedAt = table.Column<DateTime>(type: "TEXT", nullable: true),
FolderId = table.Column<Guid>(type: "TEXT", nullable: true),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_Items", x => x.Id);
table.ForeignKey(
name: "FK_Items_Folders_FolderId",
column: x => x.FolderId,
principalTable: "Folders",
principalColumn: "Id",
onDelete: ReferentialAction.SetNull);
table.ForeignKey(
name: "FK_Items_Logos_LogoId",
column: x => x.LogoId,
principalTable: "Logos",
principalColumn: "Id",
onDelete: ReferentialAction.SetNull);
});
migrationBuilder.CreateTable(
name: "FieldHistories",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
ItemId = table.Column<Guid>(type: "TEXT", nullable: false),
FieldDefinitionId = table.Column<Guid>(type: "TEXT", nullable: true),
FieldKey = table.Column<string>(type: "TEXT", maxLength: 100, nullable: true),
ValueSnapshot = table.Column<string>(type: "TEXT", nullable: false),
ChangedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_FieldHistories", x => x.Id);
table.ForeignKey(
name: "FK_FieldHistories_FieldDefinitions_FieldDefinitionId",
column: x => x.FieldDefinitionId,
principalTable: "FieldDefinitions",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_FieldHistories_Items_ItemId",
column: x => x.ItemId,
principalTable: "Items",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "FieldValues",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
ItemId = table.Column<Guid>(type: "TEXT", nullable: false),
FieldDefinitionId = table.Column<Guid>(type: "TEXT", nullable: true),
FieldKey = table.Column<string>(type: "TEXT", maxLength: 100, nullable: true),
Value = table.Column<string>(type: "TEXT", nullable: true),
Weight = table.Column<int>(type: "INTEGER", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_FieldValues", x => x.Id);
table.ForeignKey(
name: "FK_FieldValues_FieldDefinitions_FieldDefinitionId",
column: x => x.FieldDefinitionId,
principalTable: "FieldDefinitions",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_FieldValues_Items_ItemId",
column: x => x.ItemId,
principalTable: "Items",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "ItemTags",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
ItemId = table.Column<Guid>(type: "TEXT", nullable: false),
TagId = table.Column<Guid>(type: "TEXT", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_ItemTags", x => x.Id);
table.ForeignKey(
name: "FK_ItemTags_Items_ItemId",
column: x => x.ItemId,
principalTable: "Items",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_ItemTags_Tags_TagId",
column: x => x.TagId,
principalTable: "Tags",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_FieldHistories_FieldDefinitionId",
table: "FieldHistories",
column: "FieldDefinitionId");
migrationBuilder.CreateIndex(
name: "IX_FieldHistories_ItemId",
table: "FieldHistories",
column: "ItemId");
migrationBuilder.CreateIndex(
name: "IX_FieldValues_FieldDefinitionId",
table: "FieldValues",
column: "FieldDefinitionId");
migrationBuilder.CreateIndex(
name: "IX_FieldValues_FieldKey",
table: "FieldValues",
column: "FieldKey");
migrationBuilder.CreateIndex(
name: "IX_FieldValues_ItemId",
table: "FieldValues",
column: "ItemId");
migrationBuilder.CreateIndex(
name: "IX_FieldValues_ItemId_FieldDefinitionId_Weight",
table: "FieldValues",
columns: new[] { "ItemId", "FieldDefinitionId", "Weight" });
migrationBuilder.CreateIndex(
name: "IX_FieldValues_ItemId_FieldKey",
table: "FieldValues",
columns: new[] { "ItemId", "FieldKey" });
migrationBuilder.CreateIndex(
name: "IX_Folders_ParentFolderId",
table: "Folders",
column: "ParentFolderId");
migrationBuilder.CreateIndex(
name: "IX_Items_FolderId",
table: "Items",
column: "FolderId");
migrationBuilder.CreateIndex(
name: "IX_Items_LogoId",
table: "Items",
column: "LogoId");
migrationBuilder.CreateIndex(
name: "IX_ItemTags_ItemId",
table: "ItemTags",
column: "ItemId");
migrationBuilder.CreateIndex(
name: "IX_ItemTags_ItemId_TagId",
table: "ItemTags",
columns: new[] { "ItemId", "TagId" },
unique: true);
migrationBuilder.CreateIndex(
name: "IX_ItemTags_TagId",
table: "ItemTags",
column: "TagId");
migrationBuilder.CreateIndex(
name: "IX_Logos_Source",
table: "Logos",
column: "Source",
unique: true);
migrationBuilder.CreateIndex(
name: "IX_Tags_Name",
table: "Tags",
column: "Name");
migrationBuilder.AddForeignKey(
name: "FK_Attachments_Items_ItemId",
table: "Attachments",
column: "ItemId",
principalTable: "Items",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
migrationBuilder.AddForeignKey(
name: "FK_Passkeys_Items_ItemId",
table: "Passkeys",
column: "ItemId",
principalTable: "Items",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
migrationBuilder.AddForeignKey(
name: "FK_TotpCodes_Items_ItemId",
table: "TotpCodes",
column: "ItemId",
principalTable: "Items",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
// Data migration: Migrate from old Credentials/Services/Aliases structure to new Items/FieldValues structure
// System fields now use FieldKey directly in FieldValues (no FieldDefinitions needed)
// Migrate Items from Credentials
// ItemType is 'Alias' if the credential has identity fields populated (first_name, last_name, gender, birthdate)
// Email alone does not make an item an Alias - it's now a login field
// Note: BirthDate values starting with '0001-' are DateTime.MinValue (empty/unset)
migrationBuilder.Sql(@"
INSERT INTO Items (Id, Name, ItemType, LogoId, FolderId, CreatedAt, UpdatedAt, IsDeleted)
SELECT
c.Id,
s.Name AS Name,
CASE
WHEN a.Id IS NOT NULL AND (
(a.FirstName IS NOT NULL AND a.FirstName != '') OR
(a.LastName IS NOT NULL AND a.LastName != '') OR
(a.Gender IS NOT NULL AND a.Gender != '') OR
(a.BirthDate IS NOT NULL AND a.BirthDate != '' AND a.BirthDate NOT LIKE '0001-%')
) THEN 'Alias'
ELSE 'Login'
END AS ItemType,
NULL AS LogoId,
NULL AS FolderId,
c.CreatedAt,
c.UpdatedAt,
c.IsDeleted
FROM Credentials c
LEFT JOIN Services s ON s.Id = c.ServiceId
LEFT JOIN Aliases a ON a.Id = c.AliasId;
");
// Migrate Logos from Services
// Extract normalized domain from URL (e.g., 'https://www.github.com/path' -> 'github.com')
// This matches the browser extension's FaviconService.extractSourceFromUrl() logic
// Uses LOWER() for case-insensitive www. removal
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO Logos (Id, Source, FileData, MimeType, FetchedAt, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
-- Extract and normalize hostname: remove protocol, path, lowercase, and www. prefix
REPLACE(
LOWER(
SUBSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END,
1,
CASE
WHEN INSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END, '/') > 0
THEN INSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END, '/') - 1
ELSE LENGTH(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END)
END
)
),
'www.', ''
) AS Source,
s.Logo AS FileData,
'image/png' AS MimeType,
NULL AS FetchedAt,
MIN(s.CreatedAt) AS CreatedAt,
MAX(s.UpdatedAt) AS UpdatedAt,
0 AS IsDeleted
FROM Services s
WHERE s.Logo IS NOT NULL AND s.Url IS NOT NULL AND s.Url != ''
GROUP BY REPLACE(
LOWER(
SUBSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END,
1,
CASE
WHEN INSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END, '/') > 0
THEN INSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END, '/') - 1
ELSE LENGTH(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END)
END
)
),
'www.', ''
);
");
// Update Items with LogoId
// Match Items to Logos using the same normalized domain extraction logic
migrationBuilder.Sql(@"
UPDATE Items
SET LogoId = (
SELECT l.Id FROM Logos l
INNER JOIN Services s ON REPLACE(
LOWER(
SUBSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END,
1,
CASE
WHEN INSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END, '/') > 0
THEN INSTR(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END, '/') - 1
ELSE LENGTH(
CASE
WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
ELSE s.Url
END)
END
)
),
'www.', ''
) = l.Source
INNER JOIN Credentials c ON c.ServiceId = s.Id
WHERE c.Id = Items.Id
LIMIT 1
)
WHERE EXISTS (
SELECT 1 FROM Credentials c
INNER JOIN Services s ON s.Id = c.ServiceId
WHERE c.Id = Items.Id AND s.Logo IS NOT NULL
);
");
// Migrate login.url field (system field using FieldKey)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'login.url' AS FieldKey,
s.Url AS Value,
0 AS Weight,
s.UpdatedAt AS CreatedAt,
s.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
INNER JOIN Services s ON s.Id = c.ServiceId
WHERE s.Url IS NOT NULL AND s.Url != '';
");
// Migrate login.username field (system field using FieldKey)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'login.username' AS FieldKey,
c.Username AS Value,
0 AS Weight,
c.UpdatedAt AS CreatedAt,
c.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
WHERE c.Username IS NOT NULL AND c.Username != '';
");
// Migrate notes.content field (system field using FieldKey)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'notes.content' AS FieldKey,
c.Notes AS Value,
0 AS Weight,
c.UpdatedAt AS CreatedAt,
c.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
WHERE c.Notes IS NOT NULL AND c.Notes != '';
");
// Migrate login.password field (system field using FieldKey) - only most recent password
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
p.CredentialId AS ItemId,
NULL AS FieldDefinitionId,
'login.password' AS FieldKey,
p.Value AS Value,
0 AS Weight,
p.UpdatedAt AS CreatedAt,
p.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Passwords p
INNER JOIN (
SELECT CredentialId, MAX(UpdatedAt) AS MaxUpdated, MAX(Id) AS MaxId
FROM Passwords
WHERE IsDeleted = 0
GROUP BY CredentialId
) pm ON p.CredentialId = pm.CredentialId AND p.UpdatedAt = pm.MaxUpdated AND p.Id = pm.MaxId
WHERE p.IsDeleted = 0;
");
// Migrate login.email field (system field using FieldKey)
// Email is now a login field, applicable to both Login and Alias types
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'login.email' AS FieldKey,
a.Email AS Value,
0 AS Weight,
a.UpdatedAt AS CreatedAt,
a.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
INNER JOIN Aliases a ON a.Id = c.AliasId
WHERE a.Email IS NOT NULL AND a.Email != '';
");
// Migrate alias.first_name field (system field using FieldKey)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'alias.first_name' AS FieldKey,
a.FirstName AS Value,
0 AS Weight,
a.UpdatedAt AS CreatedAt,
a.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
INNER JOIN Aliases a ON a.Id = c.AliasId
WHERE a.FirstName IS NOT NULL AND a.FirstName != '';
");
// Migrate alias.last_name field (system field using FieldKey)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'alias.last_name' AS FieldKey,
a.LastName AS Value,
0 AS Weight,
a.UpdatedAt AS CreatedAt,
a.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
INNER JOIN Aliases a ON a.Id = c.AliasId
WHERE a.LastName IS NOT NULL AND a.LastName != '';
");
// Migrate alias.gender field (system field using FieldKey)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'alias.gender' AS FieldKey,
a.Gender AS Value,
0 AS Weight,
a.UpdatedAt AS CreatedAt,
a.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
INNER JOIN Aliases a ON a.Id = c.AliasId
WHERE a.Gender IS NOT NULL AND a.Gender != '';
");
// Migrate alias.birthdate field (system field using FieldKey)
// Exclude DateTime.MinValue (year 0001) which represents empty/unset dates
// Strip time portion from old format (yyyy-MM-dd HH:mm:ss or yyyy-MM-ddTHH:mm:ss) to new format (yyyy-MM-dd)
// Note: GUID format must be uppercase with dashes (8-4-4-4-12) to match C# Guid.Parse expectations
migrationBuilder.Sql(@"
INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
SELECT
UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
c.Id AS ItemId,
NULL AS FieldDefinitionId,
'alias.birthdate' AS FieldKey,
SUBSTR(a.BirthDate, 1, 10) AS Value,
0 AS Weight,
a.UpdatedAt AS CreatedAt,
a.UpdatedAt AS UpdatedAt,
0 AS IsDeleted
FROM Credentials c
INNER JOIN Aliases a ON a.Id = c.AliasId
WHERE a.BirthDate IS NOT NULL AND a.BirthDate != '' AND a.BirthDate NOT LIKE '0001-%';
");
// Drop old tables AFTER data migration (order matters for foreign keys)
migrationBuilder.DropTable(
name: "Passwords");
migrationBuilder.DropTable(
name: "Credentials");
migrationBuilder.DropTable(
name: "Aliases");
migrationBuilder.DropTable(
name: "Services");
// Re-enable foreign keys after migration is complete
// Must be executed outside of transaction to take effect in SQLite
migrationBuilder.Sql("PRAGMA foreign_keys = ON;", suppressTransaction: true);
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropForeignKey(
name: "FK_Attachments_Items_ItemId",
table: "Attachments");
migrationBuilder.DropForeignKey(
name: "FK_Passkeys_Items_ItemId",
table: "Passkeys");
migrationBuilder.DropForeignKey(
name: "FK_TotpCodes_Items_ItemId",
table: "TotpCodes");
migrationBuilder.DropTable(
name: "FieldHistories");
migrationBuilder.DropTable(
name: "FieldValues");
migrationBuilder.DropTable(
name: "ItemTags");
migrationBuilder.DropTable(
name: "FieldDefinitions");
migrationBuilder.DropTable(
name: "Items");
migrationBuilder.DropTable(
name: "Tags");
migrationBuilder.DropTable(
name: "Folders");
migrationBuilder.DropTable(
name: "Logos");
migrationBuilder.RenameColumn(
name: "ItemId",
table: "TotpCodes",
newName: "CredentialId");
migrationBuilder.RenameIndex(
name: "IX_TotpCodes_ItemId",
table: "TotpCodes",
newName: "IX_TotpCodes_CredentialId");
migrationBuilder.RenameColumn(
name: "ItemId",
table: "Passkeys",
newName: "CredentialId");
migrationBuilder.RenameIndex(
name: "IX_Passkeys_ItemId",
table: "Passkeys",
newName: "IX_Passkeys_CredentialId");
migrationBuilder.RenameColumn(
name: "ItemId",
table: "Attachments",
newName: "CredentialId");
migrationBuilder.RenameIndex(
name: "IX_Attachments_ItemId",
table: "Attachments",
newName: "IX_Attachments_CredentialId");
migrationBuilder.CreateTable(
name: "Aliases",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
BirthDate = table.Column<string>(type: "TEXT", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
Email = table.Column<string>(type: "TEXT", maxLength: 255, nullable: true),
FirstName = table.Column<string>(type: "VARCHAR", maxLength: 255, nullable: true),
Gender = table.Column<string>(type: "VARCHAR", maxLength: 255, nullable: true),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
LastName = table.Column<string>(type: "VARCHAR", maxLength: 255, nullable: true),
NickName = table.Column<string>(type: "VARCHAR", maxLength: 255, nullable: true),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Aliases", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Services",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
Logo = table.Column<byte[]>(type: "BLOB", nullable: true),
Name = table.Column<string>(type: "TEXT", maxLength: 255, nullable: true),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
Url = table.Column<string>(type: "TEXT", maxLength: 255, nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Services", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Credentials",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
AliasId = table.Column<Guid>(type: "TEXT", nullable: false),
ServiceId = table.Column<Guid>(type: "TEXT", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
Notes = table.Column<string>(type: "TEXT", nullable: true),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
Username = table.Column<string>(type: "TEXT", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Credentials", x => x.Id);
table.ForeignKey(
name: "FK_Credentials_Aliases_AliasId",
column: x => x.AliasId,
principalTable: "Aliases",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Credentials_Services_ServiceId",
column: x => x.ServiceId,
principalTable: "Services",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "Passwords",
columns: table => new
{
Id = table.Column<Guid>(type: "TEXT", nullable: false),
CredentialId = table.Column<Guid>(type: "TEXT", nullable: false),
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
IsDeleted = table.Column<bool>(type: "INTEGER", nullable: false),
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
Value = table.Column<string>(type: "TEXT", maxLength: 255, nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Passwords", x => x.Id);
table.ForeignKey(
name: "FK_Passwords_Credentials_CredentialId",
column: x => x.CredentialId,
principalTable: "Credentials",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Credentials_AliasId",
table: "Credentials",
column: "AliasId");
migrationBuilder.CreateIndex(
name: "IX_Credentials_ServiceId",
table: "Credentials",
column: "ServiceId");
migrationBuilder.CreateIndex(
name: "IX_Passwords_CredentialId",
table: "Passwords",
column: "CredentialId");
migrationBuilder.AddForeignKey(
name: "FK_Attachments_Credentials_CredentialId",
table: "Attachments",
column: "CredentialId",
principalTable: "Credentials",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
migrationBuilder.AddForeignKey(
name: "FK_Passkeys_Credentials_CredentialId",
table: "Passkeys",
column: "CredentialId",
principalTable: "Credentials",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
migrationBuilder.AddForeignKey(
name: "FK_TotpCodes_Credentials_CredentialId",
table: "TotpCodes",
column: "CredentialId",
principalTable: "Credentials",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
}
}
}