2 files changed, 49 insertions, 0 deletions
diff --git a/golang/pkg/database/sql/001_trading.sql b/golang/pkg/database/sql/001_trading.sql
new file mode 100644
index 0000000..fa8cbfe
--- /dev/null
+++ b/golang/pkg/database/sql/001_trading.sql
@@ -0,0 +1,30 @@
+CREATE TABLE markets (
+  id INTEGER PRIMARY KEY,
+  systemSymbol TEXT NOT NULL,
+  data JSON NOT NULL,
+  updated DATE NOT NULL
+);
+CREATE INDEX markets_systemSymbol on markets (systemSymbol);
+CREATE UNIQUE INDEX markets_data_symbol on markets(json_extract(data, '$.symbol'));
+
+CREATE TABLE systems (
+  id INTEGER PRIMARY KEY,
+  data JSON NOT NULL
+);
+CREATE UNIQUE INDEX systems_data_symbol on systems (json_extract(data, '$.symbol'));
+
+CREATE TABLE transactions (
+  id INTEGER PRIMARY KEY,
+  data JSON NOT NULL
+);
+CREATE UNIQUE INDEX transactions_data_symbol on transactions (json_extract(data, '$.symbol'));
+CREATE INDEX transactions_data_type on transactions (json_extract(data, '$.type'));
+CREATE INDEX transactions_data_shipSymbol on transactions (json_extract(data, '$.shipSymbol'));
+CREATE INDEX transactions_data_waypointSymbol on transactions (json_extract(data, '$.waypointSymbol'));
+
+CREATE TABLE waypoints (
+  id INTEGER PRIMARY KEY,
+  data JSON NOT NULL,
+  updated DATE NOT NULL
+);
+CREATE UNIQUE INDEX waypoints_data_symbol on waypoints(json_extract(data, '$.symbol'));
diff --git a/golang/pkg/database/transactions.go b/golang/pkg/database/transactions.go
new file mode 100644
index 0000000..71719c7
--- /dev/null
+++ b/golang/pkg/database/transactions.go
@@ -0,0 +1,19 @@
+package database
+
+import (
+	"encoding/json"
+	"fmt"
+
+	"git.adyxax.org/adyxax/spacetraders/golang/pkg/model"
+)
+
+func (db *DB) AppendTransaction(transaction *model.Transaction) error {
+	data, err := json.Marshal(transaction)
+	if err != nil {
+		return fmt.Errorf("failed to marshal transaction: %w", err)
+	}
+	if _, err := db.Exec(`INSERT INTO transactions(data) VALUES (json(?));`, data); err != nil {
+		return fmt.Errorf("failed to append transaction: %w", err)
+	}
+	return nil
+}