Add unique login stats to `/stats` page by jimchamp · Pull Request #11697 · internetarchive/openlibrary

jimchamp · 2026-01-15T23:59:46Z

Adds a new statistic to the /stats page: Number of patrons who have logged in at least once during the past 30 days.

This data comes from querying the store_index table. The query takes several minutes to run, so this value is cached for 12 hours. To prevent this from blocking the (already slow) stats page from loading, this data is fetched asynchronously once the page has loaded.

Technical

Testing

Screenshot

The new stat, rendered in a local instance of Open Library.

Stakeholders

Copilot

Pull request overview

This pull request adds a new statistic to the /stats admin page showing the number of unique patrons who have logged in within the past 30 days. The data is fetched from the store_index table via a database query that takes several minutes to run, so the result is cached for 12 hours and loaded asynchronously on the client side to prevent blocking the page load.

Changes:

Added database query function to count unique logins within a configurable time period
Implemented server-side caching with 12-hour timeout to avoid expensive repeated queries
Created asynchronous client-side loading to prevent blocking the stats page

Reviewed changes

Copilot reviewed 6 out of 6 changed files in this pull request and generated 10 comments.

Show a summary per file

File	Description
openlibrary/core/admin.py	Added `get_unique_logins_since()` to query login counts and `get_cached_unique_logins_since()` wrapper with memcache memoization
openlibrary/plugins/openlibrary/partials.py	Added `UniqueLoginCountPartial` handler to expose login count data via partials API endpoint
openlibrary/templates/admin/index.html	Added "Unique Logins" section with loading indicator and i18n strings for displaying the count
openlibrary/plugins/openlibrary/js/stats/index.js	Created new module with async fetch logic to retrieve and display login counts
openlibrary/plugins/openlibrary/js/index.js	Added initialization code to load stats module when the monthly-login-counts element is present

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

Copilot · 2026-01-16T00:05:39Z

openlibrary/plugins/openlibrary/js/stats/index.js

+    const counts = await fetchCounts()
+        .then((resp) => {
+            if (resp.status !== 200) {
+                throw new Error(`Failed to fetch partials. Status code: ${resp.status}`)
+            }
+            return resp.json()
+        })
+
+    const countDiv = document.createElement('DIV')
+    countDiv.innerHTML = i18nStrings.uniqueLoginsCopy
+    const countSpan = countDiv.querySelector('.login-counts')
+    countSpan.textContent = counts.loginCount
+    loadingIndicator.replaceWith(countDiv)


Missing error handling for the fetch operation. If the network request fails or the promise rejects, the loading indicator will remain on the page indefinitely. Consider adding a .catch() block to handle errors and display an appropriate error message to the user.

Suggested change

const counts = await fetchCounts()

.then((resp) => {

if (resp.status !== 200) {

throw new Error(`Failed to fetch partials. Status code: ${resp.status}`)

}

return resp.json()

})

const countDiv = document.createElement('DIV')

countDiv.innerHTML = i18nStrings.uniqueLoginsCopy

const countSpan = countDiv.querySelector('.login-counts')

countSpan.textContent = counts.loginCount

loadingIndicator.replaceWith(countDiv)

try {

const counts = await fetchCounts()

.then((resp) => {

if (resp.status !== 200) {

throw new Error(`Failed to fetch partials. Status code: ${resp.status}`)

}

return resp.json()

})

const countDiv = document.createElement('DIV')

countDiv.innerHTML = i18nStrings.uniqueLoginsCopy

const countSpan = countDiv.querySelector('.login-counts')

countSpan.textContent = counts.loginCount

loadingIndicator.replaceWith(countDiv)

} catch (error) {

// Ensure the loading indicator is removed and an error is shown to the user

// while also logging the underlying issue for debugging purposes.

// eslint-disable-next-line no-console

console.error('Error fetching unique login counts:', error)

const errorDiv = document.createElement('DIV')

errorDiv.textContent = (i18nStrings && i18nStrings.uniqueLoginsError) || 'Unable to load unique login counts.'

loadingIndicator.replaceWith(errorDiv)

}

openlibrary/core/admin.py

Copilot · 2026-01-16T00:05:40Z

openlibrary/core/admin.py

+def get_unique_logins_since(since_days=30):
+    since_date = datetime.datetime.now() - datetime.timedelta(days=since_days)
+    date_str = since_date.strftime("%Y-%m-%d")
+
+    query = """
+        SELECT COUNT(id) FROM store_index
+            WHERE type = 'account'
+            AND name = 'last_login'
+            AND value > $date
+    """
+
+    oldb = db.get_db()
+    results = list(oldb.query(query, vars={"date": date_str}))
+
+    if not results:
+        return 0
+    return results[0].get('count', 0)
+
+
+def get_cached_unique_logins_since(since_days=30):
+    from openlibrary.plugins.openlibrary.home import caching_prethread
+
+    twelve_hours = 60 * 60 * 12
+    key_prefix = 'logins_since'
+    mc = cache.memcache_memoize(
+        get_unique_logins_since,
+        key_prefix=key_prefix,
+        timeout=twelve_hours,
+        prethread=caching_prethread(),
+    )
+    return mc(since_days=since_days)


The new get_unique_logins_since and get_cached_unique_logins_since functions lack test coverage. Given that the repository has comprehensive test infrastructure in openlibrary/tests/core/ for database functions and admin functionality, tests should be added to verify the correctness of the SQL query, caching behavior, and edge cases (e.g., no results, different date ranges).

Copilot · 2026-01-16T00:05:40Z

openlibrary/plugins/openlibrary/js/stats/index.js

+    const countDiv = document.createElement('DIV')
+    countDiv.innerHTML = i18nStrings.uniqueLoginsCopy
+    const countSpan = countDiv.querySelector('.login-counts')
+    countSpan.textContent = counts.loginCount
+    loadingIndicator.replaceWith(countDiv)


Using innerHTML with user-controlled or externally fetched data could introduce XSS vulnerabilities. While the i18n string comes from the server-side template, it's safer to use textContent or createElement/appendChild for better security. Consider creating the elements programmatically rather than using innerHTML to set the entire content.

Suggested change

const countDiv = document.createElement('DIV')

countDiv.innerHTML = i18nStrings.uniqueLoginsCopy

const countSpan = countDiv.querySelector('.login-counts')

countSpan.textContent = counts.loginCount

loadingIndicator.replaceWith(countDiv)

const fragment = document.createRange().createContextualFragment(i18nStrings.uniqueLoginsCopy)

const countSpan = fragment.querySelector('.login-counts')

if (countSpan) {

countSpan.textContent = counts.loginCount

}

loadingIndicator.replaceWith(fragment)

Data is fetched internally. Data is not user-provided.

Copilot · 2026-01-16T00:05:40Z

openlibrary/plugins/openlibrary/js/stats/index.js

+    const loadingIndicator = containerElem.querySelector('.loadingIndicator')
+    const i18nStrings = JSON.parse(containerElem.dataset.i18n)
+
+    const counts = await fetchCounts()
+        .then((resp) => {
+            if (resp.status !== 200) {
+                throw new Error(`Failed to fetch partials. Status code: ${resp.status}`)
+            }
+            return resp.json()
+        })
+
+    const countDiv = document.createElement('DIV')
+    countDiv.innerHTML = i18nStrings.uniqueLoginsCopy
+    const countSpan = countDiv.querySelector('.login-counts')
+    countSpan.textContent = counts.loginCount
+    loadingIndicator.replaceWith(countDiv)


Missing null check for loadingIndicator and countSpan elements. If the DOM structure is different than expected (e.g., due to template changes), querySelector could return null, causing a runtime error. Add checks to ensure these elements exist before calling methods on them.

Copilot · 2026-01-16T00:05:41Z

openlibrary/core/admin.py

+def get_cached_unique_logins_since(since_days=30):
+    from openlibrary.plugins.openlibrary.home import caching_prethread
+
+    twelve_hours = 60 * 60 * 12
+    key_prefix = 'logins_since'
+    mc = cache.memcache_memoize(
+        get_unique_logins_since,
+        key_prefix=key_prefix,
+        timeout=twelve_hours,
+        prethread=caching_prethread(),
+    )
+    return mc(since_days=since_days)


The function documentation should specify the expected return type and describe the caching behavior. Consider adding a docstring that explains the purpose, parameters, cache timeout, and return value.

openlibrary/core/admin.py

openlibrary/plugins/openlibrary/partials.py

openlibrary/core/admin.py

Copilot · 2026-01-16T00:05:42Z

openlibrary/core/admin.py

+def get_unique_logins_since(since_days=30):
+    since_date = datetime.datetime.now() - datetime.timedelta(days=since_days)
+    date_str = since_date.strftime("%Y-%m-%d")
+
+    query = """
+        SELECT COUNT(id) FROM store_index
+            WHERE type = 'account'
+            AND name = 'last_login'
+            AND value > $date
+    """
+
+    oldb = db.get_db()
+    results = list(oldb.query(query, vars={"date": date_str}))
+
+    if not results:
+        return 0
+    return results[0].get('count', 0)


The function documentation should specify the expected return type and describe what happens when there are no results. Consider adding a docstring similar to other functions in this module that explains the purpose, parameters, and return value.

for more information, see https://pre-commit.ci

Add functions for fetching unique login counts

0a879b1

Copilot AI review requested due to automatic review settings January 15, 2026 23:59

Copilot started reviewing on behalf of jimchamp January 16, 2026 00:00 View session

Copilot AI reviewed Jan 16, 2026

View reviewed changes

jimchamp marked this pull request as draft January 16, 2026 05:36

Update /stats page with login counts

b172b9d

jimchamp force-pushed the login-stats branch from 24915f0 to b172b9d Compare January 16, 2026 06:00

[pre-commit.ci] auto fixes from pre-commit.com hooks

b1d8f5f

for more information, see https://pre-commit.ci

jimchamp marked this pull request as ready for review January 16, 2026 06:07

mekarpeles merged commit 9f0cc79 into internetarchive:master Jan 20, 2026
5 checks passed

jimchamp deleted the login-stats branch February 4, 2026 00:45

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Add unique login stats to `/stats` page#11697

Add unique login stats to `/stats` page#11697
mekarpeles merged 3 commits intointernetarchive:masterfrom
jimchamp:login-stats

jimchamp commented Jan 15, 2026 •

edited

Loading

Uh oh!

Copilot AI left a comment

Uh oh!

Copilot AI Jan 16, 2026

Uh oh!

Uh oh!

Copilot AI Jan 16, 2026

Uh oh!

Copilot AI Jan 16, 2026

Uh oh!

jimchamp Jan 16, 2026

Uh oh!

Copilot AI Jan 16, 2026

Uh oh!

Copilot AI Jan 16, 2026

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI Jan 16, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Uh oh!

Conversation

jimchamp commented Jan 15, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Technical

Testing

Screenshot

Stakeholders

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Copilot AI Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Copilot AI Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

jimchamp Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI Jan 16, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

jimchamp commented Jan 15, 2026 •

edited

Loading