feat: Add SQLITE database entry logging

21 files changed, 797 insertions, 326 deletions

diff --git a/src/main/kotlin/moe/nea/ledger/ItemIdProvider.kt b/src/main/kotlin/moe/nea/ledger/ItemIdProvider.kt
index 65580fa..43f2cb7 100644
--- a/src/main/kotlin/moe/nea/ledger/ItemIdProvider.kt
+++ b/src/main/kotlin/moe/nea/ledger/ItemIdProvider.kt
@@ -22,7 +22,7 @@ class ItemIdProvider {
 		MinecraftForge.EVENT_BUS.post(BeforeGuiAction(event.gui))
 	}
 
-	private val knownNames = mutableMapOf<String, String>()
+	private val knownNames = mutableMapOf<String, ItemId>()
 
 	@SubscribeEvent
 	fun saveInventoryIds(event: BeforeGuiAction) {
@@ -45,7 +45,7 @@ class ItemIdProvider {
 		}
 	}
 
-	fun findForName(name: String): String? {
+	fun findForName(name: String): ItemId? {
 		return knownNames[name]
 	}
 
diff --git a/src/main/kotlin/moe/nea/ledger/ItemUtil.kt b/src/main/kotlin/moe/nea/ledger/ItemUtil.kt
index 892127d..b82c97f 100644
--- a/src/main/kotlin/moe/nea/ledger/ItemUtil.kt
+++ b/src/main/kotlin/moe/nea/ledger/ItemUtil.kt
@@ -4,11 +4,11 @@ import net.minecraft.item.ItemStack
 import net.minecraft.nbt.NBTTagCompound
 
 
-fun ItemStack.getInternalId(): String? {
+fun ItemStack.getInternalId(): ItemId? {
     val nbt = this.tagCompound ?: NBTTagCompound()
     val extraAttributes = nbt.getCompoundTag("ExtraAttributes")
     val id = extraAttributes.getString("id")
-    return id.takeIf { it.isNotBlank() }
+    return id.takeIf { it.isNotBlank() }?.let(::ItemId)
 }
 
 
diff --git a/src/main/kotlin/moe/nea/ledger/Ledger.kt b/src/main/kotlin/moe/nea/ledger/Ledger.kt
index 54ad6e7..f85a6b0 100644
--- a/src/main/kotlin/moe/nea/ledger/Ledger.kt
+++ b/src/main/kotlin/moe/nea/ledger/Ledger.kt
@@ -79,7 +79,6 @@ class Ledger {
 	@Mod.EventHandler
 	fun init(event: FMLInitializationEvent) {
 		logger.info("Initializing ledger")
-		Database.init()
 
 		val di = DI()
 		di.registerSingleton(this)
@@ -97,11 +96,14 @@ class Ledger {
 			NpcDetection::class.java,
 			LogChatCommand::class.java,
 			ConfigCommand::class.java,
+			Database::class.java
 		)
 		di.instantiateAll()
 		di.getAllInstances().forEach(MinecraftForge.EVENT_BUS::register)
 		di.getAllInstances().filterIsInstance<ICommand>()
 			.forEach { ClientCommandHandler.instance.registerCommand(it) }
+
+		di.provide<Database>().loadAndUpgrade()
 	}
 
 	var lastJoin = -1L
diff --git a/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt b/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt
index c35d203..045c6b1 100644
--- a/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt
+++ b/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt
@@ -3,7 +3,11 @@ package moe.nea.ledger
 import com.google.gson.Gson
 import com.google.gson.JsonArray
 import com.google.gson.JsonObject
+import moe.nea.ledger.database.DBItemEntry
+import moe.nea.ledger.database.DBLogEntry
+import moe.nea.ledger.database.Database
 import moe.nea.ledger.events.ChatReceived
+import moe.nea.ledger.utils.Inject
 import net.minecraft.client.Minecraft
 import net.minecraft.util.ChatComponentText
 import net.minecraftforge.fml.common.eventhandler.SubscribeEvent
@@ -11,7 +15,8 @@ import net.minecraftforge.fml.common.gameevent.TickEvent.ClientTickEvent
 import java.io.File
 import java.text.SimpleDateFormat
 import java.time.Instant
-import java.util.*
+import java.util.Date
+import java.util.UUID
 
 class LedgerLogger {
 	fun printOut(text: String) {
@@ -21,30 +26,32 @@ class LedgerLogger {
 	val profileIdPattern =
 		"Profile ID: (?<profile>[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})".toPattern()
 
-	var currentProfile: String? = null
+	var currentProfile: UUID? = null
 
 	var shouldLog by Ledger.managedConfig.instance.debug::logEntries
 
+	@Inject
+	lateinit var database: Database
+
 	@SubscribeEvent
 	fun onProfileSwitch(event: ChatReceived) {
 		profileIdPattern.useMatcher(event.message) {
-			currentProfile = group("profile")
+			currentProfile = UUID.fromString(group("profile"))
 		}
 	}
 
 
 	fun printToChat(entry: LedgerEntry) {
+		val items = entry.items.joinToString("\n§e") { " - ${it.direction} ${it.count}x ${it.itemId}" }
 		printOut(
 			"""
             §e================= TRANSACTION START
             §eTYPE: §a${entry.transactionType}
             §eTIMESTAMP: §a${entry.timestamp}
-            §eTOTAL VALUE: §a${entry.totalTransactionCoins}
-            §eITEM ID: §a${entry.itemId}
-            §eITEM AMOUNT: §a${entry.itemAmount}
+            §e%s
             §ePROFILE: §a${currentProfile}
             §e================= TRANSACTION END
-            """.trimIndent()
+            """.trimIndent().replace("%s", items)
 		)
 	}
 
@@ -79,6 +86,21 @@ class LedgerLogger {
 		if (shouldLog)
 			printToChat(entry)
 		Ledger.logger.info("Logging entry of type ${entry.transactionType}")
+		val transactionId = UUIDUtil.createULIDAt(entry.timestamp)
+		DBLogEntry.insert(database.connection) {
+			it[DBLogEntry.profileId] = currentProfile ?: UUIDUtil.NIL_UUID
+			it[DBLogEntry.playerId] = UUIDUtil.getPlayerUUID()
+			it[DBLogEntry.type] = entry.transactionType
+			it[DBLogEntry.transactionId] = transactionId
+		}
+		entry.items.forEach { change ->
+			DBItemEntry.insert(database.connection) {
+				it[DBItemEntry.transactionId] = transactionId
+				it[DBItemEntry.mode] = change.direction
+				it[DBItemEntry.size] = change.count
+				it[DBItemEntry.itemId] = change.itemId
+			}
+		}
 		entries.add(entry.intoJson(currentProfile))
 		commit()
 	}
@@ -110,28 +132,92 @@ class LedgerLogger {
 	}
 }
 
+enum class TransactionType {
+	AUCTION_BOUGHT,
+	AUCTION_SOLD,
+	AUTOMERCHANT_PROFIT_COLLECT,
+	BANK_DEPOSIT,
+	BANK_WITHDRAW,
+	BAZAAR_BUY_INSTANT,
+	BAZAAR_BUY_ORDER,
+	BAZAAR_SELL_INSTANT,
+	BAZAAR_SELL_ORDER,
+	BITS_PURSE_STATUS,
+	BOOSTER_COOKIE_ATE,
+	COMMUNITY_SHOP_BUY,
+	DUNGEON_CHEST_OPEN,
+	KISMET_REROLL,
+	NPC_BUY,
+	NPC_SELL,
+}
+
+@JvmInline
+value class ItemId(
+	val string: String
+) {
+	companion object {
+		val COINS = ItemId("SKYBLOCK_COIN")
+		val BITS = ItemId("SKYBLOCK_BIT")
+		val NIL = ItemId("SKYBLOCK_NIL")
+		val DUNGEON_CHEST_KEY = ItemId("DUNGEON_CHEST_KEY")
+		val BOOSTER_COOKIE = ItemId("BOOSTER_COOKIE")
+		val KISMET_FEATHER = ItemId("KISMET_FEATHER")
+	}
+}
+
+
+data class ItemChange(
+	val itemId: ItemId,
+	val count: Double,
+	val direction: ChangeDirection,
+) {
+	enum class ChangeDirection {
+		GAINED,
+		TRANSFORM,
+		SYNC,
+		CATALYST,
+		LOST;
+	}
+
+	companion object {
+		fun gainCoins(number: Double): ItemChange {
+			return gain(ItemId.COINS, number)
+		}
+
+		fun gain(itemId: ItemId, amount: Number): ItemChange {
+			return ItemChange(itemId, amount.toDouble(), ChangeDirection.GAINED)
+		}
+
+		fun lose(itemId: ItemId, amount: Number): ItemChange {
+			return ItemChange(itemId, amount.toDouble(), ChangeDirection.LOST)
+		}
+
+		fun loseCoins(number: Double): ItemChange {
+			return lose(ItemId.COINS, number)
+		}
+	}
+}
 
 data class LedgerEntry(
-	val transactionType: String,
+	val transactionType: TransactionType,
 	val timestamp: Instant,
-	val totalTransactionCoins: Double,
-	val itemId: String? = null,
-	val itemAmount: Int? = null,
+	val items: List<ItemChange>,
 ) {
-	fun intoJson(profileId: String?): JsonObject {
+	fun intoJson(profileId: UUID?): JsonObject {
+		val coinAmount = items.find { it.itemId == ItemId.COINS || it.itemId == ItemId.BITS }?.count
+		val nonCoins = items.find { it.itemId != ItemId.COINS && it.itemId != ItemId.BITS }
 		return JsonObject().apply {
-			addProperty("transactionType", transactionType)
+			addProperty("transactionType", transactionType.name)
 			addProperty("timestamp", timestamp.toEpochMilli().toString())
-			addProperty("totalTransactionValue", totalTransactionCoins)
-			addProperty("itemId", itemId ?: "")
-			addProperty("itemAmount", itemAmount ?: 0)
-			addProperty("profileId", profileId)
+			addProperty("totalTransactionValue", coinAmount)
+			addProperty("itemId", nonCoins?.itemId?.string ?: "")
+			addProperty("itemAmount", nonCoins?.count ?: 0.0)
+			addProperty("profileId", profileId.toString())
 			addProperty(
 				"playerId",
-				(Minecraft.getMinecraft().thePlayer?.uniqueID?.toString() ?: lastKnownUUID)
-					.also { lastKnownUUID = it })
+				UUIDUtil.getPlayerUUID().toString()
+			)
 		}
 	}
 }
 
-var lastKnownUUID = "null"
diff --git a/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt b/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt
new file mode 100644
index 0000000..ddfb8c2
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt
@@ -0,0 +1,39 @@
+package moe.nea.ledger
+
+import com.mojang.util.UUIDTypeAdapter
+import io.azam.ulidj.ULID
+import net.minecraft.client.Minecraft
+import java.time.Instant
+import java.util.UUID
+import kotlin.random.Random
+
+object UUIDUtil {
+	@JvmInline
+	value class ULIDWrapper(
+		val wrapped: String
+	) {
+		init {
+			require(ULID.isValid(wrapped))
+		}
+	}
+
+	fun parseDashlessUuid(string: String) = UUIDTypeAdapter.fromString(string)
+	val NIL_UUID = UUID(0L, 0L)
+	fun getPlayerUUID(): UUID {
+		val currentUUID = Minecraft.getMinecraft().thePlayer?.uniqueID
+			?: Minecraft.getMinecraft().session?.playerID?.let(::parseDashlessUuid)
+			?: lastKnownUUID
+		lastKnownUUID = currentUUID
+		return currentUUID
+	}
+
+	fun createULIDAt(timestamp: Instant): ULIDWrapper {
+		return ULIDWrapper(ULID.generate(
+			timestamp.toEpochMilli(),
+			Random.nextBytes(10)
+		))
+	}
+
+	private var lastKnownUUID: UUID = NIL_UUID
+
+}
\ No newline at end of file
diff --git a/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt b/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt
new file mode 100644
index 0000000..77ac215
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt
@@ -0,0 +1,19 @@
+package moe.nea.ledger.database
+
+import moe.nea.ledger.ItemChange
+import moe.nea.ledger.ItemId
+import moe.nea.ledger.TransactionType
+
+object DBLogEntry : Table("LogEntry") {
+	val transactionId = column("transactionId", DBUlid)
+	val type = column("type", DBEnum<TransactionType>())
+	val profileId = column("profileId", DBUuid)
+	val playerId = column("playerId", DBUuid)
+}
+
+object DBItemEntry : Table("ItemEntry") {
+	val transactionId = column("transactionId", DBUlid) // TODO: add foreign keys
+	val mode = column("mode", DBEnum<ItemChange.ChangeDirection>())
+	val itemId = column("item", DBString.mapped(ItemId::string, ::ItemId))
+	val size = column("size", DBDouble)
+}
diff --git a/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt b/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt
index 5c9099c..dee99e4 100644
--- a/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt
+++ b/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt
@@ -1,10 +1,11 @@
 package moe.nea.ledger.database
 
+import moe.nea.ledger.UUIDUtil
 import java.sql.Connection
 import java.sql.PreparedStatement
 import java.sql.ResultSet
-import java.sql.Timestamp
 import java.time.Instant
+import java.util.UUID
 
 interface DBSchema {
 	val tables: List<Table>
@@ -16,6 +17,54 @@ interface DBType<T> {
 	fun get(result: ResultSet, index: Int): T
 	fun set(stmt: PreparedStatement, index: Int, value: T)
 	fun getName(): String = javaClass.simpleName
+	fun <R> mapped(
+		from: (R) -> T,
+		to: (T) -> R,
+	): DBType<R> {
+		return object : DBType<R> {
+			override fun getName(): String {
+				return "Mapped(${this@DBType.getName()})"
+			}
+
+			override val dbType: String
+				get() = this@DBType.dbType
+
+			override fun get(result: ResultSet, index: Int): R {
+				return to(this@DBType.get(result, index))
+			}
+
+			override fun set(stmt: PreparedStatement, index: Int, value: R) {
+				this@DBType.set(stmt, index, from(value))
+			}
+		}
+	}
+}
+
+object DBUuid : DBType<UUID> {
+	override val dbType: String
+		get() = "TEXT"
+
+	override fun get(result: ResultSet, index: Int): UUID {
+		return UUIDUtil.parseDashlessUuid(result.getString(index))
+	}
+
+	override fun set(stmt: PreparedStatement, index: Int, value: UUID) {
+		stmt.setString(index, value.toString())
+	}
+}
+
+object DBUlid : DBType<UUIDUtil.ULIDWrapper> {
+	override val dbType: String
+		get() = "TEXT"
+
+	override fun get(result: ResultSet, index: Int): UUIDUtil.ULIDWrapper {
+		val text = result.getString(index)
+		return UUIDUtil.ULIDWrapper(text)
+	}
+
+	override fun set(stmt: PreparedStatement, index: Int, value: UUIDUtil.ULIDWrapper) {
+		stmt.setString(index, value.wrapped)
+	}
 }
 
 object DBString : DBType<String> {
@@ -31,6 +80,45 @@ object DBString : DBType<String> {
 	}
 }
 
+class DBEnum<T : Enum<T>>(
+	val type: Class<T>,
+) : DBType<T> {
+	companion object {
+		inline operator fun <reified T : Enum<T>> invoke(): DBEnum<T> {
+			return DBEnum(T::class.java)
+		}
+	}
+
+	override val dbType: String
+		get() = "TEXT"
+
+	override fun getName(): String {
+		return "DBEnum(${type.simpleName})"
+	}
+
+	override fun set(stmt: PreparedStatement, index: Int, value: T) {
+		stmt.setString(index, value.name)
+	}
+
+	override fun get(result: ResultSet, index: Int): T {
+		val name = result.getString(index)
+		return java.lang.Enum.valueOf(type, name)
+	}
+}
+
+object DBDouble : DBType<Double> {
+	override val dbType: String
+		get() = "DOUBLE"
+
+	override fun get(result: ResultSet, index: Int): Double {
+		return result.getDouble(index)
+	}
+
+	override fun set(stmt: PreparedStatement, index: Int, value: Double) {
+		stmt.setDouble(index, value)
+	}
+}
+
 object DBInt : DBType<Long> {
 	override val dbType: String
 		get() = "INTEGER"
@@ -57,12 +145,12 @@ object DBInstant : DBType<Instant> {
 	}
 }
 
-// TODO: add table
 class Column<T> @Deprecated("Use Table.column instead") constructor(val name: String, val type: DBType<T>) {
 	val sqlName get() = "`$name`"
 }
 
 interface Constraint {
+	val affectedColumns: Collection<Column<*>>
 	fun asSQL(): String
 }
 
@@ -71,6 +159,9 @@ class UniqueConstraint(val columns: List<Column<*>>) : Constraint {
 		require(columns.isNotEmpty())
 	}
 
+	override val affectedColumns: Collection<Column<*>>
+		get() = columns
+
 	override fun asSQL(): String {
 		return "UNIQUE (${columns.joinToString() { it.sqlName }})"
 	}
@@ -111,18 +202,37 @@ abstract class Table(val name: String) {
 		println(string)
 	}
 
-	fun createIfNotExists(connection: Connection) {
+	fun createIfNotExists(
+		connection: Connection,
+		filteredColumns: List<Column<*>> = columns
+	) {
 		val properties = mutableListOf<String>()
-		for (column in columns) {
+		for (column in filteredColumns) {
 			properties.add("${column.sqlName} ${column.type.dbType}")
 		}
+		val columnSet = filteredColumns.toSet()
 		for (constraint in constraints) {
-			properties.add(constraint.asSQL())
+			if (columnSet.containsAll(constraint.affectedColumns)) {
+				properties.add(constraint.asSQL())
+			}
 		}
-		connection.prepareAndLog("CREATE TABLE IF NOT EXISTS `$name` (" + properties.joinToString() + ")")
+		connection.prepareAndLog("CREATE TABLE IF NOT EXISTS $sqlName (" + properties.joinToString() + ")")
 			.execute()
 	}
 
+	fun alterTableAddColumns(
+		connection: Connection,
+		newColumns: List<Column<*>>
+	) {
+		for (column in newColumns) {
+			connection.prepareAndLog("ALTER TABLE $sqlName ADD ${column.sqlName} ${column.type.dbType}")
+				.execute()
+		}
+		for (constraint in constraints) {
+			// TODO: automatically add constraints, maybe (or maybe move constraints into the upgrade schema)
+		}
+	}
+
 	enum class OnConflict {
 		FAIL,
 		IGNORE,
diff --git a/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt b/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt
new file mode 100644
index 0000000..7d1782a
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt
@@ -0,0 +1,68 @@
+package moe.nea.ledger.database
+
+import java.sql.Connection
+
+interface DBUpgrade {
+	val toVersion: Long
+	val fromVersion get() = toVersion - 1
+	fun performUpgrade(connection: Connection)
+
+	companion object {
+
+		fun performUpgrades(
+			connection: Connection,
+			upgrades: Iterable<DBUpgrade>,
+		) {
+			for (upgrade in upgrades) {
+				upgrade.performUpgrade(connection)
+			}
+		}
+
+		fun performUpgradeChain(
+			connection: Connection,
+			from: Long, to: Long,
+			upgrades: Iterable<DBUpgrade>,
+			afterEach: (newVersion: Long) -> Unit,
+		) {
+			val table = buildLookup(upgrades)
+			for (version in (from + 1)..(to)) {
+				val currentUpgrades = table[version] ?: listOf()
+				println("Scheduled ${currentUpgrades.size} upgrades to reach DB version $version")
+				performUpgrades(connection, currentUpgrades)
+				afterEach(version)
+			}
+		}
+
+		fun buildLookup(upgrades: Iterable<DBUpgrade>): Map<Long, List<DBUpgrade>> {
+			return upgrades.groupBy { it.toVersion }
+		}
+
+		fun createTable(to: Long, table: Table, vararg columns: Column<*>): DBUpgrade {
+			require(columns.all { it in table.columns })
+			return of("Create table ${table}", to) {
+				table.createIfNotExists(it, columns.toList())
+			}
+		}
+
+		fun addColumns(to: Long, table: Table, vararg columns: Column<*>): DBUpgrade {
+			return of("Add columns to table $table", to) {
+				table.alterTableAddColumns(it, columns.toList())
+			}
+		}
+
+		fun of(name: String, to: Long, block: (Connection) -> Unit): DBUpgrade {
+			return object : DBUpgrade {
+				override val toVersion: Long
+					get() = to
+
+				override fun performUpgrade(connection: Connection) {
+					block(connection)
+				}
+
+				override fun toString(): String {
+					return name
+				}
+			}
+		}
+	}
+}
\ No newline at end of file
diff --git a/src/main/kotlin/moe/nea/ledger/database/Database.kt b/src/main/kotlin/moe/nea/ledger/database/Database.kt
index 9dc4e00..a77ea30 100644
--- a/src/main/kotlin/moe/nea/ledger/database/Database.kt
+++ b/src/main/kotlin/moe/nea/ledger/database/Database.kt
@@ -1,10 +1,11 @@
 package moe.nea.ledger.database
 
 import moe.nea.ledger.Ledger
+import java.sql.Connection
 import java.sql.DriverManager
 
-object Database {
-	val connection = DriverManager.getConnection("jdbc:sqlite:${Ledger.dataFolder.resolve("database.db")}")
+class Database {
+	lateinit var connection: Connection
 
 	object MetaTable : Table("LedgerMeta") {
 		val key = column("key", DBString)
@@ -15,14 +16,38 @@ object Database {
 		}
 	}
 
-	fun init() {
+	data class MetaKey(val name: String) {
+		companion object {
+			val DATABASE_VERSION = MetaKey("databaseVersion")
+			val LAST_LAUNCH = MetaKey("lastLaunch")
+		}
+	}
+
+	fun setMetaKey(key: MetaKey, value: String) {
+		MetaTable.insert(connection, Table.OnConflict.REPLACE) {
+			it[MetaTable.key] = key.name
+			it[MetaTable.value] = value
+		}
+	}
+
+	val databaseVersion: Long = 1
+
+	fun loadAndUpgrade() {
+		connection = DriverManager.getConnection("jdbc:sqlite:${Ledger.dataFolder.resolve("database.db")}")
 		MetaTable.createIfNotExists(connection)
-		val meta = MetaTable.selectAll(connection).associate { it[MetaTable.key] to it[MetaTable.value] }
-		val lastLaunch = meta["lastLaunch"]?.toLong() ?: 0L
+		val meta = MetaTable.selectAll(connection).associate { MetaKey(it[MetaTable.key]) to it[MetaTable.value] }
+		val lastLaunch = meta[MetaKey.LAST_LAUNCH]?.toLong() ?: 0L
 		println("Last launch $lastLaunch")
-		MetaTable.insert(connection, Table.OnConflict.REPLACE) {
-			it[MetaTable.key] = "lastLaunch"
-			it[MetaTable.value] = System.currentTimeMillis().toString()
+		setMetaKey(MetaKey.LAST_LAUNCH, System.currentTimeMillis().toString())
+
+		val oldVersion = meta[MetaKey.DATABASE_VERSION]?.toLong() ?: -1
+		println("Old Database Version: $oldVersion; Current version: $databaseVersion")
+		// TODO: create a backup if there is a db version upgrade happening
+		DBUpgrade.performUpgradeChain(
+			connection, oldVersion, databaseVersion,
+			Upgrades().upgrades
+		) { version ->
+			setMetaKey(MetaKey.DATABASE_VERSION, version.toString())
 		}
 	}
 
diff --git a/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt b/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt
new file mode 100644
index 0000000..e83abe7
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt
@@ -0,0 +1,20 @@
+package moe.nea.ledger.database
+
+class Upgrades {
+	val upgrades = mutableListOf<DBUpgrade>()
+
+	fun add(upgrade: DBUpgrade) = upgrades.add(upgrade)
+
+	init {
+		add(DBUpgrade.createTable(
+			0, DBLogEntry,
+			DBLogEntry.type, DBLogEntry.playerId, DBLogEntry.profileId,
+			DBLogEntry.transactionId))
+		add(DBUpgrade.createTable(
+			0, DBItemEntry,
+			DBItemEntry.itemId, DBItemEntry.size, DBItemEntry.mode, DBItemEntry.transactionId
+		))
+	}
+
+
+}
\ No newline at end of file
diff --git a/src/main/kotlin/moe/nea/ledger/database/schema.dot b/src/main/kotlin/moe/nea/ledger/database/schema.dot
new file mode 100644
index 0000000..d932f6a
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/database/schema.dot
@@ -0,0 +1,23 @@
+digraph {
+    node [shape=plain];
+    rankdir=LR;
+    entry [label=<
+        <table border="0" cellborder="1" cellspacing="0">
+            <tr><td>Log Entry</td></tr>
+            <tr><td port="player">playerId</td></tr>
+            <tr><td port="profile">profileId</td></tr>
+            <tr><td port="date">timestamp</td></tr>
+            <tr><td port="type">Type</td></tr>
+        </table>
+    >];
+    item [label=<
+        <table border="0" cellborder="1" cellspacing="0">
+            <tr><td>Item Stack</td><tr>
+            <tr><td port="transaction">Transaction</td></tr>
+            <tr><td port="id">Item ID</td></tr>
+            <tr><td port="count">Count</td></tr>
+            <tr><td port="direction">Transfer Direction</td></tr>
+        </table>
+    >];
+//    item:transaction -> entry;
+}
\ No newline at end of file
diff --git a/src/main/kotlin/moe/nea/ledger/modules/AuctionHouseDetection.kt b/src/main/kotlin/moe/nea/ledger/modules/AuctionHouseDetection.kt
index cbbff12..0d9b0cb 100644
--- a/src/main/kotlin/moe/nea/ledger/modules/AuctionHouseDetection.kt
+++ b/src/main/kotlin/moe/nea/ledger/modules/AuctionHouseDetection.kt
@@ -1,11 +1,14 @@
 package moe.nea.ledger.modules
 
-import moe.nea.ledger.events.BeforeGuiAction
-import moe.nea.ledger.events.ChatReceived
+import moe.nea.ledger.ItemChange
+import moe.nea.ledger.ItemId
 import moe.nea.ledger.ItemIdProvider
 import moe.nea.ledger.LedgerEntry
 import moe.nea.ledger.LedgerLogger
 import moe.nea.ledger.SHORT_NUMBER_PATTERN
+import moe.nea.ledger.TransactionType
+import moe.nea.ledger.events.BeforeGuiAction
+import moe.nea.ledger.events.ChatReceived
 import moe.nea.ledger.getInternalId
 import moe.nea.ledger.getLore
 import moe.nea.ledger.parseShortNumber
@@ -18,83 +21,88 @@ import net.minecraftforge.fml.common.eventhandler.SubscribeEvent
 import java.util.regex.Pattern
 
 class AuctionHouseDetection @Inject constructor(val ledger: LedgerLogger, val ids: ItemIdProvider) {
-    data class LastViewedItem(
-        val count: Int,
-        val id: String,
-    )
-    /*
-        You collected 8,712,000 coins from selling Ultimate Carrot Candy Upgrade to [VIP] kodokush in an auction!
-        You collected 60,000 coins from selling Walnut to [MVP++] Alea1337 in an auction!
-        You purchased 2x Walnut for 69 coins!
-        You purchased ◆ Ice Rune I for 4,000 coins!
-     */
+	data class LastViewedItem(
+		val count: Int,
+		val id: ItemId,
+	)
+	/*
+		You collected 8,712,000 coins from selling Ultimate Carrot Candy Upgrade to [VIP] kodokush in an auction!
+		You collected 60,000 coins from selling Walnut to [MVP++] Alea1337 in an auction!
+		You purchased 2x Walnut for 69 coins!
+		You purchased ◆ Ice Rune I for 4,000 coins!
+	 */
 
-    val collectSold =
-        Pattern.compile("You collected (?<coins>$SHORT_NUMBER_PATTERN) coins? from selling (?<what>.*) to (?<buyer>.*) in an auction!")
-    val purchased =
-        Pattern.compile("You purchased (?:(?<amount>[0-9]+)x )?(?<what>.*) for (?<coins>$SHORT_NUMBER_PATTERN) coins!")
-    var lastViewedItems: MutableList<LastViewedItem> = mutableListOf()
+	val collectSold =
+		Pattern.compile("You collected (?<coins>$SHORT_NUMBER_PATTERN) coins? from selling (?<what>.*) to (?<buyer>.*) in an auction!")
+	val purchased =
+		Pattern.compile("You purchased (?:(?<amount>[0-9]+)x )?(?<what>.*) for (?<coins>$SHORT_NUMBER_PATTERN) coins!")
+	var lastViewedItems: MutableList<LastViewedItem> = mutableListOf()
 
-    @SubscribeEvent
-    fun onEvent(event: ChatReceived) {
-        collectSold.useMatcher(event.message) {
-            val lastViewedItem = lastViewedItems.removeLastOrNull()
-            ledger.logEntry(
-	            LedgerEntry(
-		            "AUCTION_SOLD",
-		            event.timestamp,
-		            parseShortNumber(group("coins")),
-		            lastViewedItem?.id,
-		            lastViewedItem?.count
-	            )
-            )
-        }
-        purchased.useMatcher(event.message) {
-            ledger.logEntry(
-	            LedgerEntry(
-		            "AUCTION_BOUGHT",
-		            event.timestamp,
-		            parseShortNumber(group("coins")),
-		            ids.findForName(group("what")),
-		            group("amount")?.toInt() ?: 1
-	            )
-            )
-        }
-    }
+	@SubscribeEvent
+	fun onEvent(event: ChatReceived) {
+		collectSold.useMatcher(event.message) {
+			val lastViewedItem = lastViewedItems.removeLastOrNull()
+			ledger.logEntry(
+				LedgerEntry(
+					TransactionType.AUCTION_SOLD,
+					event.timestamp,
+					listOfNotNull(
+						ItemChange.gainCoins(parseShortNumber(group("coins"))),
+						lastViewedItem?.let { ItemChange.lose(it.id, it.count) }
+					),
+				)
+			)
+		}
+		purchased.useMatcher(event.message) {
+			ledger.logEntry(
+				LedgerEntry(
+					TransactionType.AUCTION_BOUGHT,
+					event.timestamp,
+					listOf(
+						ItemChange.loseCoins(parseShortNumber(group("coins"))),
+						ItemChange.gain(
+							ids.findForName(group("what")) ?: ItemId.NIL,
+							group("amount")?.toInt() ?: 1
+						)
+					)
+				)
+			)
+		}
+	}
 
-    @SubscribeEvent
-    fun onBeforeAuctionCollected(event: BeforeGuiAction) {
-        val chest = (event.gui as? GuiChest) ?: return
-        val slots = chest.inventorySlots as ContainerChest
-        val name = slots.lowerChestInventory.displayName.unformattedText.unformattedString()
+	@SubscribeEvent
+	fun onBeforeAuctionCollected(event: BeforeGuiAction) {
+		val chest = (event.gui as? GuiChest) ?: return
+		val slots = chest.inventorySlots as ContainerChest
+		val name = slots.lowerChestInventory.displayName.unformattedText.unformattedString()
 
-        if (name == "BIN Auction View" || name == "Auction View") {
-            handleCollectSingleAuctionView(slots)
-        }
-        if (name == "Manage Auctions") {
-            handleCollectMultipleAuctionsView(slots)
-        }
-    }
+		if (name == "BIN Auction View" || name == "Auction View") {
+			handleCollectSingleAuctionView(slots)
+		}
+		if (name == "Manage Auctions") {
+			handleCollectMultipleAuctionsView(slots)
+		}
+	}
 
-    private fun handleCollectMultipleAuctionsView(slots: ContainerChest) {
-        lastViewedItems =
-