feat: add localized ledger explorer dashboard

infrastructure/postgres/migration_test.go

@@ -25,3 +25,16 @@ func TestInitialMigrationContainsLedgerSafetyGuards(t *testing.T) {
 		}
 	}
 }
+
+func TestExplorerFilterMigrationAddsSupportingIndexes(t *testing.T) {
+	contents, err := migrationFiles.ReadFile("migrations/000002_explorer_filters.up.sql")
+	if err != nil {
+		t.Fatal(err)
+	}
+	schema := string(contents)
+	for _, required := range []string{"journals_effect_recorded_idx", "lower(effect_kind)", "ledger_accounts_user_owner_idx", "USER_AVAILABLE", "USER_FROZEN"} {
+		if !strings.Contains(schema, required) {
+			t.Fatalf("explorer filter migration is missing %q", required)
+		}
+	}
+}

infrastructure/postgres/migrations/000002_explorer_filters.down.sql

@@ -0,0 +1,2 @@
+DROP INDEX IF EXISTS ledger_accounts_user_owner_idx;
+DROP INDEX IF EXISTS journals_effect_recorded_idx;

infrastructure/postgres/migrations/000002_explorer_filters.up.sql

@@ -0,0 +1,7 @@
+CREATE INDEX journals_effect_recorded_idx
+    ON journals (lower(effect_kind), recorded_at DESC, id DESC)
+    WHERE sealed_at IS NOT NULL;
+
+CREATE INDEX ledger_accounts_user_owner_idx
+    ON ledger_accounts (owner_id, id)
+    WHERE class IN ('USER_AVAILABLE', 'USER_FROZEN');

infrastructure/postgres/query_repository.go

@@ -7,6 +7,7 @@ import (
 	"fmt"
 	"time"
 
+	"gl/application/explorer"
 	"gl/domain/ledger"
 
 	"github.com/jackc/pgx/v5"
@@ -54,22 +55,77 @@ func (r *JournalRepository) List(ctx context.Context, filter JournalFilter) ([]l
 		ownerType = filter.Account.OwnerType
 		ownerID = filter.Account.OwnerID
 	}
-	rows, err := r.database.Query(ctx, listJournalsSQL,
+	return r.queryJournals(ctx, listJournalsSQL,
 		filter.AssetID,
 		accountClass,
 		ownerType,
 		ownerID,
+		filter.OwnerID,
+		filter.EffectKind,
 		filter.RecordedFrom,
 		filter.RecordedTo,
 		limit,
 		filter.Offset,
 	)
+}
+
+func (r *JournalRepository) GetByTransactionHash(ctx context.Context, hash string) ([]ledger.Journal, error) {
+	return r.queryJournals(ctx, getJournalsByTransactionHashSQL, hash)
+}
+
+func (r *JournalRepository) Stats(ctx context.Context) (explorer.Stats, error) {
+	var stats explorer.Stats
+	if err := r.database.QueryRow(ctx, getExplorerStatsSQL).Scan(
+		&stats.JournalCount,
+		&stats.EntryCount,
+		&stats.AccountCount,
+		&stats.LastRecordedAt,
+	); err != nil {
+		return explorer.Stats{}, fmt.Errorf("read explorer stats: %w", err)
+	}
+	return stats, nil
+}
+
+func (r *JournalRepository) TopHolders(ctx context.Context, assetLimit, holderLimit int) ([]explorer.Holder, error) {
+	if assetLimit <= 0 || assetLimit > 20 {
+		assetLimit = 5
+	}
+	if holderLimit <= 0 || holderLimit > 20 {
+		holderLimit = 5
+	}
+	rows, err := r.database.Query(ctx, topHoldersSQL, assetLimit, holderLimit)
+	if err != nil {
+		return nil, fmt.Errorf("list top holders: %w", err)
+	}
+	defer rows.Close()
+
+	holders := make([]explorer.Holder, 0, assetLimit*holderLimit)
+	for rows.Next() {
+		var holder explorer.Holder
+		var balance string
+		if err := rows.Scan(&holder.Rank, &holder.OwnerType, &holder.OwnerID, &holder.AssetID, &balance); err != nil {
+			return nil, fmt.Errorf("scan top holder: %w", err)
+		}
+		holder.Balance, err = ledger.ParseAmount(balance)
+		if err != nil {
+			return nil, fmt.Errorf("decode top holder balance: %w", err)
+		}
+		holders = append(holders, holder)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate top holders: %w", err)
+	}
+	return holders, nil
+}
+
+func (r *JournalRepository) queryJournals(ctx context.Context, query string, args ...any) ([]ledger.Journal, error) {
+	rows, err := r.database.Query(ctx, query, args...)
 	if err != nil {
 		return nil, fmt.Errorf("list journals: %w", err)
 	}
 	defer rows.Close()
 
-	journals := make([]ledger.Journal, 0, limit)
+	journals := make([]ledger.Journal, 0)
 	for rows.Next() {
 		journal, scanErr := scanJournal(rows)
 		if scanErr != nil {
@@ -206,6 +262,42 @@ FROM journals j WHERE j.id = $1 AND j.sealed_at IS NOT NULL`
 const getJournalByIdempotencySQL = `SELECT ` + journalColumns + `
 FROM journals j WHERE j.idempotency_key = $1 AND j.sealed_at IS NOT NULL`
 
+const getJournalsByTransactionHashSQL = `SELECT ` + journalColumns + `
+FROM journals j
+WHERE j.blockchain_transaction_hash = $1 AND j.sealed_at IS NOT NULL
+ORDER BY j.recorded_at DESC, j.id DESC`
+
+const topHoldersSQL = `WITH recent_assets AS (
+    SELECT e.asset_id, MAX(j.recorded_at) AS last_activity
+    FROM journal_entries e
+    JOIN journals j ON j.id = e.journal_id
+    WHERE j.sealed_at IS NOT NULL
+    GROUP BY e.asset_id
+    ORDER BY last_activity DESC, e.asset_id
+    LIMIT $1
+), holder_balances AS (
+    SELECT a.owner_type, a.owner_id, e.asset_id, SUM(e.amount) AS balance
+    FROM journal_entries e
+    JOIN journals j ON j.id = e.journal_id AND j.sealed_at IS NOT NULL
+    JOIN ledger_accounts a ON a.id = e.account_id
+    JOIN recent_assets ra ON ra.asset_id = e.asset_id
+    WHERE a.class IN ('USER_AVAILABLE', 'USER_FROZEN')
+    GROUP BY a.owner_type, a.owner_id, e.asset_id
+    HAVING SUM(e.amount) > 0
+), ranked_holders AS (
+    SELECT owner_type, owner_id, asset_id, balance,
+           ROW_NUMBER() OVER (
+               PARTITION BY asset_id
+               ORDER BY balance DESC, owner_type, owner_id
+           ) AS holder_rank
+    FROM holder_balances
+)
+SELECT rh.holder_rank, rh.owner_type, rh.owner_id, rh.asset_id, rh.balance::text
+FROM ranked_holders rh
+JOIN recent_assets ra ON ra.asset_id = rh.asset_id
+WHERE rh.holder_rank <= $2
+ORDER BY ra.last_activity DESC, rh.asset_id, rh.holder_rank`
+
 const listJournalsSQL = `SELECT DISTINCT ` + journalColumns + `
 FROM journals j
 JOIN journal_entries e ON e.journal_id = j.id
@@ -215,10 +307,14 @@ WHERE j.sealed_at IS NOT NULL
   AND ($2::text IS NULL OR (
       a.class = $2 AND a.owner_type = $3 AND a.owner_id = $4
   ))
-  AND ($5::timestamptz IS NULL OR j.recorded_at >= $5)
-  AND ($6::timestamptz IS NULL OR j.recorded_at <= $6)
+  AND ($5::text IS NULL OR (
+      a.class IN ('USER_AVAILABLE', 'USER_FROZEN') AND a.owner_id = $5
+  ))
+  AND ($6::text IS NULL OR lower(j.effect_kind) = lower($6))
+  AND ($7::timestamptz IS NULL OR j.recorded_at >= $7)
+  AND ($8::timestamptz IS NULL OR j.recorded_at <= $8)
 ORDER BY j.recorded_at DESC, j.id DESC
-LIMIT $7 OFFSET $8`
+LIMIT $9 OFFSET $10`
 
 const getEntriesSQL = `
 SELECT e.line_number, a.class, a.owner_type, a.owner_id, e.asset_id,
@@ -237,3 +333,10 @@ WHERE j.sealed_at IS NOT NULL
   AND a.class = $1 AND a.owner_type = $2 AND a.owner_id = $3
   AND a.asset_id = $4
   AND ($5::timestamptz IS NULL OR j.recorded_at <= $5)`
+
+const getExplorerStatsSQL = `
+SELECT
+    (SELECT count(*) FROM journals WHERE sealed_at IS NOT NULL),
+    (SELECT count(*) FROM journal_entries e JOIN journals j ON j.id = e.journal_id WHERE j.sealed_at IS NOT NULL),
+    (SELECT count(*) FROM ledger_accounts),
+    COALESCE((SELECT max(recorded_at) FROM journals WHERE sealed_at IS NOT NULL), 'epoch'::timestamptz)`