Price Averaging

/ah average shows players the weighted unit price of an item based on real recent sales. The service that powers it runs continuously in the background so the menu lookup is a fast cache hit, not a SQL round-trip.

Pipeline

transactions table  ──►  SQL aggregate (every 30 minutes)
                         │
                         ▼
                     Redis hash "auctionhouse:average"   ──►  Caffeine (1-hour refresh)
                                                              │
                                                              ▼
                                                          AverageService.average(key)
                                                              │
                                                              ▼
                                                          /ah average,
                                                          %item_average%,
                                                          %item_averageWeighted%

The SQL

SELECT item_key,
       ROUND(SUM(price) * 1.0 / SUM(amount)) AS weighted_avg_unit_price
FROM transactions
WHERE item_key IS NOT NULL
  AND amount > 0
  AND created_at >= NOW() - INTERVAL 90 DAY
GROUP BY item_key
HAVING COUNT(*) >= 3

Filter	Why
`item_key IS NOT NULL`	Items without a stable key (e.g. vanilla materials with unique NBT) aren't averaged.
`amount > 0`	Defensive — every legitimate transaction stamps a positive amount.
`created_at >= NOW() - INTERVAL 90 DAY`	Rolling 90-day window so prices reflect current market.
`COUNT(*) >= 3`	Avoids publishing a "market price" off a single rogue transaction.

The query is run by TransactionService.fetchAllAverages() and is the authoritative source. Both the periodic refresh (/ah refresh) and the startup warmup go through it.

Item keys

The aggregation groups by the item_key column. Keys are produced by AHUtils.itemKey(stack) at listing time:

Stack	Key
Vanilla material with no NBT	`"diamond_sword"`
Mob spawner	entity type — `"zombie"` (read from the `BlockStateMeta`)
Enchanted book	`"eb_" + sorted enchantment codes` — e.g. `"eb_sh1_pr2"` for Sharpness I + Protection II
AstralItems / AstralPets / AstralScrolls custom item	The supplier's key (which already encodes rarity, etc.)
Anything else with PersistentData / damage	`null` — not averagable

Enchantment codes come from a fixed table in AHUtils (e.g. sharpness → sh, protection → pr, unbreaking → un). The list is stable, sorted, and joined by _ — so a book with Sharpness II + Looting III always ends up as the same key regardless of inventory order.

Redis hash

auctionhouse:average is a single Redis hash:

HSET auctionhouse:average diamond_sword 1850
HSET auctionhouse:average eb_sh1_pr2     2240
HSET auctionhouse:average zombie         11000

Field	Value
`<item key>`	weighted unit price as a string

The hash is written from AverageService.refetch() and read by the Caffeine cache when an entry is missed locally. Storing as a single hash keeps the network footprint low and lets you inspect or hand- patch averages from a Redis CLI without touching Postgres.

Caffeine cache

AverageService keeps an AsyncLoadingCache<String, Integer>:

Refresh after write: 1 hour. Reads return the stale value; the loader runs in the background.
Loader timeout: 5 seconds. If Redis is down, the call resolves to "no data".
Warm-up: the cache is fully populated from Redis on onEnable.

That gives players instant /ah average responses while the background pipeline keeps the cache fresh without ever spiking the database.

Manual refresh

/ah refresh

Forces AverageService.refetch() to run synchronously: the SQL aggregate executes, the Redis hash is overwritten, and the Caffeine cache is invalidated. Use it after a manual DB edit or any time you suspect a stale value (e.g. the 30-minute scheduler missed a tick due to a restart).

Placeholders

For items rendered in menus the same data is available without any extra wiring:

Placeholder	Value
`%item_average%`	Weighted unit price, or `null` if missing.
`%item_averageWeighted%`	`average × amount` for the menu item.

See Developer API for the rest of the placeholder catalogue.

Last modified: 25 July 2026