Added goose for migrations, refactored SQL schema, server and client

are broken

14 files changed, 389 insertions, 351 deletions

diff --git a/internal/data/frequencies.sql.go b/internal/data/frequencies.sql.go
index 024c069..9ee8994 100644
--- a/internal/data/frequencies.sql.go
+++ b/internal/data/frequencies.sql.go
@@ -115,84 +115,6 @@ func (q *Queries) GetNetworkFrequencies(ctx context.Context, networkID snowflake
 	return items, nil
 }
 
-const setFrequencyColor = `-- name: SetFrequencyColor :one
-UPDATE frequencies SET
-  hex_color = ?
-WHERE id = ?
-RETURNING id, network_id, name, hex_color, perms, position
-`
-
-type SetFrequencyColorParams struct {
-	HexColor string
-	ID       snowflake.ID
-}
-
-func (q *Queries) SetFrequencyColor(ctx context.Context, arg SetFrequencyColorParams) (Frequency, error) {
-	row := q.db.QueryRowContext(ctx, setFrequencyColor, arg.HexColor, arg.ID)
-	var i Frequency
-	err := row.Scan(
-		&i.ID,
-		&i.NetworkID,
-		&i.Name,
-		&i.HexColor,
-		&i.Perms,
-		&i.Position,
-	)
-	return i, err
-}
-
-const setFrequencyName = `-- name: SetFrequencyName :one
-UPDATE frequencies SET
-  name = ?
-WHERE id = ?
-RETURNING id, network_id, name, hex_color, perms, position
-`
-
-type SetFrequencyNameParams struct {
-	Name string
-	ID   snowflake.ID
-}
-
-func (q *Queries) SetFrequencyName(ctx context.Context, arg SetFrequencyNameParams) (Frequency, error) {
-	row := q.db.QueryRowContext(ctx, setFrequencyName, arg.Name, arg.ID)
-	var i Frequency
-	err := row.Scan(
-		&i.ID,
-		&i.NetworkID,
-		&i.Name,
-		&i.HexColor,
-		&i.Perms,
-		&i.Position,
-	)
-	return i, err
-}
-
-const setFrequencyPerms = `-- name: SetFrequencyPerms :one
-UPDATE frequencies SET
-  perms = ?
-WHERE id = ?
-RETURNING id, network_id, name, hex_color, perms, position
-`
-
-type SetFrequencyPermsParams struct {
-	Perms int64
-	ID    snowflake.ID
-}
-
-func (q *Queries) SetFrequencyPerms(ctx context.Context, arg SetFrequencyPermsParams) (Frequency, error) {
-	row := q.db.QueryRowContext(ctx, setFrequencyPerms, arg.Perms, arg.ID)
-	var i Frequency
-	err := row.Scan(
-		&i.ID,
-		&i.NetworkID,
-		&i.Name,
-		&i.HexColor,
-		&i.Perms,
-		&i.Position,
-	)
-	return i, err
-}
-
 const swapFrequencies = `-- name: SwapFrequencies :exec
 UPDATE frequencies SET
   position = CASE
diff --git a/internal/data/members.sql.go b/internal/data/members.sql.go
new file mode 100644
index 0000000..c9932cc
--- /dev/null
+++ b/internal/data/members.sql.go
@@ -0,0 +1,224 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.27.0
+// source: members.sql
+
+package data
+
+import (
+	"context"
+	"strings"
+
+	"github.com/kyren223/eko/pkg/snowflake"
+)
+
+const filterUsersInNetwork = `-- name: FilterUsersInNetwork :many
+SELECT user_id FROM members
+WHERE network_id = ? AND user_id IN (/*SLICE:users*/?)
+`
+
+type FilterUsersInNetworkParams struct {
+	NetworkID snowflake.ID
+	Users     []snowflake.ID
+}
+
+func (q *Queries) FilterUsersInNetwork(ctx context.Context, arg FilterUsersInNetworkParams) ([]snowflake.ID, error) {
+	query := filterUsersInNetwork
+	var queryParams []interface{}
+	queryParams = append(queryParams, arg.NetworkID)
+	if len(arg.Users) > 0 {
+		for _, v := range arg.Users {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:users*/?", strings.Repeat(",?", len(arg.Users))[1:], 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:users*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []snowflake.ID
+	for rows.Next() {
+		var user_id snowflake.ID
+		if err := rows.Scan(&user_id); err != nil {
+			return nil, err
+		}
+		items = append(items, user_id)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const getMemberById = `-- name: GetMemberById :one
+SELECT user_id, network_id, joined_at, is_member, is_admin, is_muted, is_banned, ban_reason FROM members
+WHERE network_id = ? AND user_id = ?
+`
+
+type GetMemberByIdParams struct {
+	NetworkID snowflake.ID
+	UserID    snowflake.ID
+}
+
+func (q *Queries) GetMemberById(ctx context.Context, arg GetMemberByIdParams) (Member, error) {
+	row := q.db.QueryRowContext(ctx, getMemberById, arg.NetworkID, arg.UserID)
+	var i Member
+	err := row.Scan(
+		&i.UserID,
+		&i.NetworkID,
+		&i.JoinedAt,
+		&i.IsMember,
+		&i.IsAdmin,
+		&i.IsMuted,
+		&i.IsBanned,
+		&i.BanReason,
+	)
+	return i, err
+}
+
+const getMembers = `-- name: GetMembers :many
+SELECT
+  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted,
+  members.user_id, members.network_id, members.joined_at, members.is_member, members.is_admin, members.is_muted, members.is_banned, members.ban_reason
+FROM members
+JOIN users ON users.id = members.user_id
+WHERE network_id = ? AND is_member = true
+`
+
+type GetMembersRow struct {
+	User   User
+	Member Member
+}
+
+func (q *Queries) GetMembers(ctx context.Context, networkID snowflake.ID) ([]GetMembersRow, error) {
+	rows, err := q.db.QueryContext(ctx, getMembers, networkID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetMembersRow
+	for rows.Next() {
+		var i GetMembersRow
+		if err := rows.Scan(
+			&i.User.ID,
+			&i.User.Name,
+			&i.User.PublicKey,
+			&i.User.Description,
+			&i.User.IsPublicDM,
+			&i.User.IsDeleted,
+			&i.Member.UserID,
+			&i.Member.NetworkID,
+			&i.Member.JoinedAt,
+			&i.Member.IsMember,
+			&i.Member.IsAdmin,
+			&i.Member.IsMuted,
+			&i.Member.IsBanned,
+			&i.Member.BanReason,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const getNetworksOfUser = `-- name: GetNetworksOfUser :many
+SELECT networks.id, networks.owner_id, networks.name, networks.icon, networks.bg_hex_color, networks.fg_hex_color, networks.is_public FROM networks
+JOIN members ON networks.id = members.network_id
+WHERE members.user_id = ?
+`
+
+func (q *Queries) GetNetworksOfUser(ctx context.Context, userID snowflake.ID) ([]Network, error) {
+	rows, err := q.db.QueryContext(ctx, getNetworksOfUser, userID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []Network
+	for rows.Next() {
+		var i Network
+		if err := rows.Scan(
+			&i.ID,
+			&i.OwnerID,
+			&i.Name,
+			&i.Icon,
+			&i.BgHexColor,
+			&i.FgHexColor,
+			&i.IsPublic,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const setMember = `-- name: SetMember :one
+INSERT INTO members (
+  user_id, network_id,
+  is_member, is_admin, is_muted,
+  is_banned, ban_reason
+) VALUES (
+  ?1, ?2,
+  ?3, ?4, ?5,
+  ?6, ?7
+)
+ON CONFLICT DO
+UPDATE SET
+  is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted,
+  is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position
+WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id
+RETURNING user_id, network_id, joined_at, is_member, is_admin, is_muted, is_banned, ban_reason
+`
+
+type SetMemberParams struct {
+	UserID    snowflake.ID
+	NetworkID snowflake.ID
+	IsMember  bool
+	IsAdmin   bool
+	IsMuted   bool
+	IsBanned  bool
+	BanReason *string
+}
+
+func (q *Queries) SetMember(ctx context.Context, arg SetMemberParams) (Member, error) {
+	row := q.db.QueryRowContext(ctx, setMember,
+		arg.UserID,
+		arg.NetworkID,
+		arg.IsMember,
+		arg.IsAdmin,
+		arg.IsMuted,
+		arg.IsBanned,
+		arg.BanReason,
+	)
+	var i Member
+	err := row.Scan(
+		&i.UserID,
+		&i.NetworkID,
+		&i.JoinedAt,
+		&i.IsMember,
+		&i.IsAdmin,
+		&i.IsMuted,
+		&i.IsBanned,
+		&i.BanReason,
+	)
+	return i, err
+}
diff --git a/internal/data/models.go b/internal/data/models.go
index 3b5425f..bb0b271 100644
--- a/internal/data/models.go
+++ b/internal/data/models.go
@@ -9,6 +9,11 @@ import (
 	"github.com/kyren223/eko/pkg/snowflake"
 )
 
+type BlockedUser struct {
+	BlockingUserID snowflake.ID
+	BlockedUserID  snowflake.ID
+}
+
 type Frequency struct {
 	ID        snowflake.ID
 	NetworkID snowflake.ID
@@ -18,6 +23,17 @@ type Frequency struct {
 	Position  int64
 }
 
+type Member struct {
+	UserID    snowflake.ID
+	NetworkID snowflake.ID
+	JoinedAt  string
+	IsMember  bool
+	IsAdmin   bool
+	IsMuted   bool
+	IsBanned  bool
+	BanReason *string
+}
+
 type Message struct {
 	ID          snowflake.ID
 	SenderID    snowflake.ID
@@ -37,6 +53,12 @@ type Network struct {
 	IsPublic   bool
 }
 
+type TrustedUser struct {
+	TrustingUserID   snowflake.ID
+	TrustedUserID    snowflake.ID
+	TrustedPublicKey []byte
+}
+
 type User struct {
 	ID          snowflake.ID
 	Name        string
@@ -45,26 +67,3 @@ type User struct {
 	IsPublicDM  bool
 	IsDeleted   bool
 }
-
-type UserBlockedUser struct {
-	BlockerUserID snowflake.ID
-	BlockedUserID snowflake.ID
-}
-
-type UserNetwork struct {
-	UserID    snowflake.ID
-	NetworkID snowflake.ID
-	JoinedAt  string
-	IsMember  bool
-	IsAdmin   bool
-	IsMuted   bool
-	IsBanned  bool
-	BanReason *string
-	Position  *int64
-}
-
-type UserTrustedUser struct {
-	TrusterUserID    snowflake.ID
-	TrustedUserID    snowflake.ID
-	TrustedPublicKey []byte
-}
diff --git a/internal/data/networks.sql.go b/internal/data/networks.sql.go
index 9adeeb8..5091602 100644
--- a/internal/data/networks.sql.go
+++ b/internal/data/networks.sql.go
@@ -84,132 +84,6 @@ func (q *Queries) GetNetworkById(ctx context.Context, id snowflake.ID) (Network,
 	return i, err
 }
 
-const getPublicNetworks = `-- name: GetPublicNetworks :many
-SELECT id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public FROM networks
-WHERE is_public = true
-`
-
-func (q *Queries) GetPublicNetworks(ctx context.Context) ([]Network, error) {
-	rows, err := q.db.QueryContext(ctx, getPublicNetworks)
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-	var items []Network
-	for rows.Next() {
-		var i Network
-		if err := rows.Scan(
-			&i.ID,
-			&i.OwnerID,
-			&i.Name,
-			&i.Icon,
-			&i.BgHexColor,
-			&i.FgHexColor,
-			&i.IsPublic,
-		); err != nil {
-			return nil, err
-		}
-		items = append(items, i)
-	}
-	if err := rows.Close(); err != nil {
-		return nil, err
-	}
-	if err := rows.Err(); err != nil {
-		return nil, err
-	}
-	return items, nil
-}
-
-const setNetworkIcon = `-- name: SetNetworkIcon :one
-UPDATE networks SET
-  icon = ?,
-  bg_hex_color = ?,
-  fg_hex_color = ?
-WHERE id = ?
-RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public
-`
-
-type SetNetworkIconParams struct {
-	Icon       string
-	BgHexColor string
-	FgHexColor string
-	ID         snowflake.ID
-}
-
-func (q *Queries) SetNetworkIcon(ctx context.Context, arg SetNetworkIconParams) (Network, error) {
-	row := q.db.QueryRowContext(ctx, setNetworkIcon,
-		arg.Icon,
-		arg.BgHexColor,
-		arg.FgHexColor,
-		arg.ID,
-	)
-	var i Network
-	err := row.Scan(
-		&i.ID,
-		&i.OwnerID,
-		&i.Name,
-		&i.Icon,
-		&i.BgHexColor,
-		&i.FgHexColor,
-		&i.IsPublic,
-	)
-	return i, err
-}
-
-const setNetworkIsPublic = `-- name: SetNetworkIsPublic :one
-UPDATE networks SET
-  is_public = ?
-WHERE id = ?
-RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public
-`
-
-type SetNetworkIsPublicParams struct {
-	IsPublic bool
-	ID       snowflake.ID
-}
-
-func (q *Queries) SetNetworkIsPublic(ctx context.Context, arg SetNetworkIsPublicParams) (Network, error) {
-	row := q.db.QueryRowContext(ctx, setNetworkIsPublic, arg.IsPublic, arg.ID)
-	var i Network
-	err := row.Scan(
-		&i.ID,
-		&i.OwnerID,
-		&i.Name,
-		&i.Icon,
-		&i.BgHexColor,
-		&i.FgHexColor,
-		&i.IsPublic,
-	)
-	return i, err
-}
-
-const setNetworkName = `-- name: SetNetworkName :one
-UPDATE networks SET
-  name = ?
-WHERE id = ?
-RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public
-`
-
-type SetNetworkNameParams struct {
-	Name string
-	ID   snowflake.ID
-}
-
-func (q *Queries) SetNetworkName(ctx context.Context, arg SetNetworkNameParams) (Network, error) {
-	row := q.db.QueryRowContext(ctx, setNetworkName, arg.Name, arg.ID)
-	var i Network
-	err := row.Scan(
-		&i.ID,
-		&i.OwnerID,
-		&i.Name,
-		&i.Icon,
-		&i.BgHexColor,
-		&i.FgHexColor,
-		&i.IsPublic,
-	)
-	return i, err
-}
-
 const transferNetwork = `-- name: TransferNetwork :one
 UPDATE networks SET
   owner_id = ?
diff --git a/internal/data/users.sql.go b/internal/data/users.sql.go
index 33eab3f..f7a7f9a 100644
--- a/internal/data/users.sql.go
+++ b/internal/data/users.sql.go
@@ -52,25 +52,6 @@ func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) error {
 	return err
 }
 
-const getDeletedUserById = `-- name: GetDeletedUserById :one
-SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users
-WHERE id = ? AND is_deleted = true
-`
-
-func (q *Queries) GetDeletedUserById(ctx context.Context, id snowflake.ID) (User, error) {
-	row := q.db.QueryRowContext(ctx, getDeletedUserById, id)
-	var i User
-	err := row.Scan(
-		&i.ID,
-		&i.Name,
-		&i.PublicKey,
-		&i.Description,
-		&i.IsPublicDM,
-		&i.IsDeleted,
-	)
-	return i, err
-}
-
 const getUserById = `-- name: GetUserById :one
 SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users
 WHERE id = ? AND is_deleted = false
@@ -108,107 +89,3 @@ func (q *Queries) GetUserByPublicKey(ctx context.Context, publicKey ed25519.Publ
 	)
 	return i, err
 }
-
-const setUserDescription = `-- name: SetUserDescription :one
-UPDATE users SET
-  description = ?
-WHERE id = ? AND is_deleted = false
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
-`
-
-type SetUserDescriptionParams struct {
-	Description string
-	ID          snowflake.ID
-}
-
-func (q *Queries) SetUserDescription(ctx context.Context, arg SetUserDescriptionParams) (User, error) {
-	row := q.db.QueryRowContext(ctx, setUserDescription, arg.Description, arg.ID)
-	var i User
-	err := row.Scan(
-		&i.ID,
-		&i.Name,
-		&i.PublicKey,
-		&i.Description,
-		&i.IsPublicDM,
-		&i.IsDeleted,
-	)
-	return i, err
-}
-
-const setUserName = `-- name: SetUserName :one
-UPDATE users SET
-  name = ?
-WHERE id = ? AND is_deleted = false
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
-`
-
-type SetUserNameParams struct {
-	Name string
-	ID   snowflake.ID
-}
-
-func (q *Queries) SetUserName(ctx context.Context, arg SetUserNameParams) (User, error) {
-	row := q.db.QueryRowContext(ctx, setUserName, arg.Name, arg.ID)
-	var i User
-	err := row.Scan(
-		&i.ID,
-		&i.Name,
-		&i.PublicKey,
-		&i.Description,
-		&i.IsPublicDM,
-		&i.IsDeleted,
-	)
-	return i, err
-}
-
-const setUserPublicDMs = `-- name: SetUserPublicDMs :one
-UPDATE users SET
-  is_public_dm = ?
-WHERE id = ? AND is_deleted = false
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
-`
-
-type SetUserPublicDMsParams struct {
-	IsPublicDM bool
-	ID         snowflake.ID
-}
-
-func (q *Queries) SetUserPublicDMs(ctx context.Context, arg SetUserPublicDMsParams) (User, error) {
-	row := q.db.QueryRowContext(ctx, setUserPublicDMs, arg.IsPublicDM, arg.ID)
-	var i User
-	err := row.Scan(
-		&i.ID,
-		&i.Name,
-		&i.PublicKey,
-		&i.Description,
-		&i.IsPublicDM,
-		&i.IsDeleted,
-	)
-	return i, err
-}
-
-const setUserPublicKey = `-- name: SetUserPublicKey :one
-UPDATE users SET
-  public_key = ?
-WHERE id = ? AND is_deleted = false
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
-`
-
-type SetUserPublicKeyParams struct {
-	PublicKey ed25519.PublicKey
-	ID        snowflake.ID
-}
-
-func (q *Queries) SetUserPublicKey(ctx context.Context, arg SetUserPublicKeyParams) (User, error) {
-	row := q.db.QueryRowContext(ctx, setUserPublicKey, arg.PublicKey, arg.ID)
-	var i User
-	err := row.Scan(
-		&i.ID,
-		&i.Name,
-		&i.PublicKey,
-		&i.Description,
-		&i.IsPublicDM,
-		&i.IsDeleted,
-	)
-	return i, err
-}
diff --git a/internal/server/api/database.go b/internal/server/api/database.go
index 0617515..00e8d4d 100644
--- a/internal/server/api/database.go
+++ b/internal/server/api/database.go
@@ -2,19 +2,26 @@ package api
 
 import (
 	"database/sql"
+	"embed"
 	"log"
 
 	_ "github.com/mattn/go-sqlite3"
+	"github.com/pressly/goose/v3"
 
 	"github.com/kyren223/eko/pkg/assert"
 )
 
+//go:embed migrations/*.sql
+var embedMigrations embed.FS
+
 var db *sql.DB
 
 func ConnectToDatabase() {
 	var err error
 	db, err = sql.Open("sqlite3", "file:server.db?cache=shared")
-	assert.NoError(err, "DB should always be accessible")
+	if err != nil {
+		log.Fatalln("unable to open db:", err)
+	}
 	assert.AddFlush(db)
 	log.Println("established connection with the database")
 
@@ -29,6 +36,18 @@ func ConnectToDatabase() {
 		assert.NoError(err, "DB pragmas should always execute with no errors")
 	}
 
+	log.Println("opened database, running up migrations...")
+
+	goose.SetBaseFS(embedMigrations)
+	if err := goose.SetDialect("sqlite3"); err != nil {
+		assert.NoError(err, "sqlite3 is a valid dialect that shouldn't error")
+	}
+	if err := goose.Up(db, "migrations"); err != nil {
+		db.Close()
+		log.Fatalln("error running up migrations:", err)
+	}
+	log.Println("database up migrations applied successfully")
+
 	log.Println("database connection ready to be used")
 }
 
diff --git a/internal/server/api/migrations/20250109134844_users.sql b/internal/server/api/migrations/20250109134844_users.sql
new file mode 100644
index 0000000..8ebb9eb
--- /dev/null
+++ b/internal/server/api/migrations/20250109134844_users.sql
@@ -0,0 +1,12 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS users (
+  id INTEGER PRIMARY KEY,
+  name TEXT NOT NULL,
+  public_key BLOB NOT NULL UNIQUE,
+  description TEXT NOT NULL DEFAULT '',
+  is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (false, true)) DEFAULT true,
+  is_deleted BOOLEAN NOT NULL CHECK (is_deleted IN (false, true)) DEFAULT false
+);
+
+-- +goose Down
+DROP TABLE IF EXISTS users;
diff --git a/internal/server/api/migrations/20250109135310_networks.sql b/internal/server/api/migrations/20250109135310_networks.sql
new file mode 100644
index 0000000..c3332c8
--- /dev/null
+++ b/internal/server/api/migrations/20250109135310_networks.sql
@@ -0,0 +1,13 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS networks (
+  id INTEGER PRIMARY KEY,
+  owner_id INT NOT NULL REFERENCES users (id),
+  name TEXT NOT NULL,
+  icon TEXT NOT NULL,
+  bg_hex_color TEXT NOT NULL,
+  fg_hex_color TEXT NOT NULL,
+  is_public BOOLEAN NOT NULL CHECK (is_public IN (false, true))
+);
+
+-- +goose Down
+DROP TABLE IF EXISTS networks;
diff --git a/internal/server/api/migrations/20250109135557_frequencies.sql b/internal/server/api/migrations/20250109135557_frequencies.sql
new file mode 100644
index 0000000..a444af1
--- /dev/null
+++ b/internal/server/api/migrations/20250109135557_frequencies.sql
@@ -0,0 +1,24 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS frequencies (
+  id INTEGER PRIMARY KEY,
+  network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE,
+  name TEXT NOT NULL,
+  hex_color TEXT NOT NULL,
+  perms INT NOT NULL CHECK (perms IN (0, 1, 2)),
+  -- 0 no access | 1 read | 2 read & write
+  position INT NOT NULL
+);
+
+-- +goose StatementBegin
+CREATE TRIGGER IF NOT EXISTS on_frequency_delete
+AFTER DELETE ON frequencies
+BEGIN
+  UPDATE frequencies SET
+    position = position - 1
+  WHERE network_id = OLD.network_id AND position > OLD.position;
+END
+-- +goose StatementEnd
+
+-- +goose Down
+DROP TABLE IF EXISTS frequencies;
+DROP TRIGGER IF EXISTS on_frequency_delete;
diff --git a/internal/server/api/migrations/20250109140401_messages.sql b/internal/server/api/migrations/20250109140401_messages.sql
new file mode 100644
index 0000000..92f75f4
--- /dev/null
+++ b/internal/server/api/migrations/20250109140401_messages.sql
@@ -0,0 +1,18 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS messages (
+  id INTEGER PRIMARY KEY,
+  sender_id INT NOT NULL REFERENCES users (id),
+  content TEXT NOT NULL,
+  edited BOOLEAN NOT NULL CHECK (edited IN (false, true)) DEFAULT false,
+
+  frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE,
+  receiver_id INT REFERENCES users (id),
+
+  CHECK (
+    (frequency_id IS NOT NULL AND receiver_id IS NULL) OR
+    (frequency_id IS NULL AND receiver_id IS NOT NULL)
+  )
+);
+
+-- +goose Down
+DROP TABLE IF EXISTS messages;
diff --git a/internal/server/api/migrations/20250109141122_members.sql b/internal/server/api/migrations/20250109141122_members.sql
new file mode 100644
index 0000000..61fba61
--- /dev/null
+++ b/internal/server/api/migrations/20250109141122_members.sql
@@ -0,0 +1,15 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS members (
+  user_id INT NOT NULL REFERENCES users (id),
+  network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE,
+  joined_at TEXT NOT NULL DEFAULT current_timestamp,
+  is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true,
+  is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false,
+  is_muted BOOLEAN NOT NULL CHECK (is_muted IN (false, true)) DEFAULT false,
+  is_banned BOOLEAN NOT NULL CHECK (is_banned IN (false, true)) DEFAULT false,
+  ban_reason TEXT, -- null ONLY if is_banned is false
+  PRIMARY KEY (user_id, network_id)
+);
+
+-- +goose Down
+DROP TABLE IF EXISTS members;
diff --git a/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql
new file mode 100644
index 0000000..8916378
--- /dev/null
+++ b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql
@@ -0,0 +1,17 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS trusted_users (
+  trusting_user_id INT NOT NULL REFERENCES users (id),
+  trusted_user_id INT NOT NULL REFERENCES users (id),
+  trusted_public_key BLOB NOT NULL,
+  PRIMARY KEY (trusting_user_id, trusted_user_id)
+);
+
+CREATE TABLE IF NOT EXISTS blocked_users (
+  blocking_user_id INT NOT NULL REFERENCES users (id),
+  blocked_user_id INT NOT NULL REFERENCES users (id),
+  PRIMARY KEY (blocking_user_id, blocked_user_id)
+);
+
+-- +goose Down
+DROP TABLE IF EXISTS trusted_users;
+DROP TABLE IF EXISTS blocked_users;
diff --git a/internal/server/api/migrations/20250109143707_on_user_delete.sql b/internal/server/api/migrations/20250109143707_on_user_delete.sql
new file mode 100644
index 0000000..940bec4
--- /dev/null
+++ b/internal/server/api/migrations/20250109143707_on_user_delete.sql
@@ -0,0 +1,15 @@
+-- +goose Up
+-- +goose StatementBegin
+CREATE TRIGGER on_user_delete
+AFTER UPDATE OF is_deleted ON users
+WHEN NEW.is_deleted = true
+BEGIN
+  DELETE FROM networks WHERE owner_id = NEW.id;
+  DELETE FROM members WHERE user_id = NEW.id;
+  DELETE FROM trusted_users WHERE trusting_user_id = NEW.id OR trusted_user_id = NEW.id;
+  DELETE FROM blocked_users WHERE blocking_user_id = NEW.id OR blocked_user_id = NEW.id;
+END
+-- +goose StatementEnd
+
+-- +goose Down
+DROP TRIGGER IF EXISTS on_user_delete;
diff --git a/internal/server/api/migrations/20250109144057_indices.sql b/internal/server/api/migrations/20250109144057_indices.sql
new file mode 100644
index 0000000..1732fb2
--- /dev/null
+++ b/internal/server/api/migrations/20250109144057_indices.sql
@@ -0,0 +1,9 @@
+-- +goose Up
+CREATE INDEX IF NOT EXISTS idx_frequency_network ON frequencies (network_id);
+CREATE INDEX IF NOT EXISTS idx_frequency_messages ON messages (frequency_id);
+CREATE INDEX IF NOT EXISTS idx_direct_messages ON messages (sender_id, receiver_id);
+
+-- +goose Down
+DROP INDEX IF EXISTS idx_frequency_network;
+DROP INDEX IF EXISTS idx_frequency_messages;
+DROP INDEX IF EXISTS idx_direct_messages;