DIY Ticker
At work someone mentioned it would be cool to have a live ticker for new customers that sign up to our program.
In theory this is in my wheel house. I'm great at building shoddy next.js apps, I have a data source, and I can generally brute force my way through any hardware headaches— so let's make it happen.
To start I needed to pick out what this would run on. All I should really need was a screen, a single board computer, and a handful of right angled cables to keep things tidy.
For the panel itself I wanted something that resembled one of those LED stock tickers. Unfortunately LED panels are surprisingly expensive, so I had to settle for a LCD screen. I found a 12.3" panel for $60 on Amazon that fit the bill (and was way cheaper than anything I could find on Ali Express).
As for the brains of the operation, I grabbed a raspberry pi 5. Was this overkill for what would ultimately be an internet browser? Totally, but I plan to repurpose it and swap in cheaper hardware sooner or later.
Now, it's web app time.
Data-wise, there were a few constraints. The vendor we use to manage the program doesn’t have an API that supports any reporting requests to get live customer counts. The other contingency being that I wanted to avoid using any microservices, because I am only going to be pulling in a small amount of data.
In the name of keeping it "simple", it was easiest for me to create a Dagster job that runs a query on a local database to grab customer counts and commits the output in a json file directly to the repository every morning. I get more of those little green vanity contribution squares on my Github and I don’t have to worry about my Supabase project being paused abruptly, it’s a win-win.
import json
import base64
from datetime import datetime
import requests
from dagster import asset, EnvVar
@asset(required_resource_keys={"redshift"})
def store_enrollment_json(context):
query = "SELECT" + """
'{' || '"metadata": ' || metadata_json || ', ' || '"stores": ' || stores_json || '}'
as json_output
FROM (
-- Get metadata as a JSON object. Need a max date for web component.
SELECT '{"last_updated": "' || MAX(date) || '"}' as metadata_json
FROM (
-- Example data
VALUES
('2024-01-01', 'Store A', 100),
('2024-01-02', 'Store A', 150),
('2024-01-01', 'Store B', 200),
('2024-01-02', 'Store B', 250)
) as sample_data(date, store, value)
) metadata,
(
-- Convert store data into nested JSON structure
SELECT '{' || LISTAGG('"' || store || '": ' || dates_json, ', ')
WITHIN GROUP (ORDER BY store) || '}'
as stores_json
FROM (
-- For each store, create JSON object of dates and values
SELECT
store,
'{' || LISTAGG('"' || date || '": ' || value, ', ')
WITHIN GROUP (ORDER BY date) || '}' as dates_json
FROM (
-- Example data
VALUES
('2024-01-01', 'Store A', 100),
('2024-01-02', 'Store A', 150),
('2024-01-01', 'Store B', 200),
('2024-01-02', 'Store B', 250)
) as sample_data(date, store, value)
GROUP BY store
) store_json
) stores_combined;
""".strip()
# Execute query and get results
results = context.resources.redshift(query)
json_data = results[0][0] # Get the JSON string from the first row, first column
# GitHub API configuration
github_token = EnvVar("GITHUB_TOKEN").get_value()
github_repo = EnvVar("GITHUB_REPO").get_value()
github_api_url = f"https://api.github.com/repos/{github_repo}/contents/data.json"
headers = {
"Authorization": f"token {github_token}",
"Accept": "application/vnd.github.v3+json"
}
# First, try to get the file if it exists to get its SHA
response = requests.get(github_api_url, headers=headers)
# Prepare the file content
file_content = json.loads(json_data)
content_encoded = base64.b64encode(json.dumps(file_content, indent=2).encode()).decode()
# Prepare the commit message
commit_message = f"Update store enrollment data - {datetime.now().strftime('%Y-%m-%d')}"
# Prepare the payload
payload = {
"message": commit_message,
"content": content_encoded,
}
# If file exists, include its SHA in the payload
if response.status_code == 200:
payload["sha"] = response.json()["sha"]
# Push to GitHub
response = requests.put(github_api_url, headers=headers, json=payload)
if response.status_code in [200, 201]:
context.log.info("Successfully updated store enrollment data on GitHub")
else:
raise Exception(f"Failed to update file on GitHub: {response.status_code} - {response.text}")
# Update the assets list
website_assets = [
store_enrollment_json
]
Now with the asset sorted, and a few other small configs not worth mentioning here, we can expect a json file with the example output below to be pushed to our repository every morning.
{
"metadata": {"last_updated": "2024-01-02"},
"stores": {
"Store A": {
"2024-01-01": 100,
"2024-01-02": 150
},
"Store B": {
"2024-01-01": 200,
"2024-01-02": 250
}
}
}
A very important part of this project will be "seeing number go up". This is built with a lagged feed, so we will need to simulate customer growth based on the change from the previous day. Daily enrollment growth is pretty consistent, so there's no real need to go overboard by bringing in more data for better forecasting.
All that really matters for our component:
- It always uses the most recent date in the data feed.
- The count can only go up between 7AM - 10PM to emulate business hours.
- The count goes up in time-dependent increments, so incase the Raspberry Pi is reset, the count will be consistent.
// Constants for business hours
const dayStartHour = 7; // 7 AM
const dayEndHour = 22; // 10 PM
// Calculate the current count based on time of day and historical data
const calculateCurrentCount = () => {
const now = new Date();
const currentHour = now.getHours();
const currentMinute = now.getMinutes();
// Get today's and yesterday's totals (either for all stores or selected store)
let todayTotal, yesterdayTotal;
if (selectedStore === 'All Stores') {
todayTotal = Object.values(data.stores).reduce((sum, store) => sum + (store as StoreData)[mostRecentDate], 0);
yesterdayTotal = Object.values(data.stores).reduce((sum, store) => sum + (store as StoreData)[previousDate], 0);
} else {
todayTotal = (data.stores[selectedStore] as StoreData)[mostRecentDate];
yesterdayTotal = (data.stores[selectedStore] as StoreData)[previousDate];
}
// Calculate how many new customers we expect today
const totalDailyIncrease = todayTotal - yesterdayTotal;
// Calculate how far through the business day we are
const totalMinutesInDay = (dayEndHour - dayStartHour) * 60;
let minutesSinceStart = 0;
if (currentHour >= dayStartHour && currentHour < dayEndHour) {
minutesSinceStart = (currentHour - dayStartHour) * 60 + currentMinute;
} else if (currentHour >= dayEndHour) {
minutesSinceStart = totalMinutesInDay;
}
// Calculate progress through the day and expected increase
const progress = Math.min(minutesSinceStart / totalMinutesInDay, 1);
const expectedIncrease = Math.floor(totalDailyIncrease * progress);
// Return the total expected count
return todayTotal + expectedIncrease;
};
All we need to do now is write a function that checks if the count has gone up, and if so, update the count.
// Calculate seconds until the next minute
const now = new Date();
const secondsUntilNextMinute = 60 - now.getSeconds();
// Initial timeout to sync with the minute mark
const initialTimeout = setTimeout(() => {
// First update when we hit the next minute
const newCount = calculateCurrentCount();
setCount(newCount);
setLastUpdate(new Date());
// Then set up an interval for every minute after that
const interval = setInterval(() => {
const newCount = calculateCurrentCount();
setCount(newCount);
setLastUpdate(new Date());
}, 60000); // Check every 60 seconds
return () => clearInterval(interval);
}, secondsUntilNextMinute * 1000);
// Cleanup
return () => clearTimeout(initialTimeout);
}, [selectedStore]);
Core functionality aside, seeing a number go up feels good, but do you know what feels even better?
Confetti cannons.
const triggerConfetti = () => {
// Left corner confetti burst
confetti({
particleCount: 80,
spread: 40,
origin: { x: 0, y: 0.9 },
angle: 45,
startVelocity: 100,
scalar: 1.4,
gravity: 0.8,
ticks: 400,
colors: ['#cbdb09', '#1a6b1a', '#39ff14', '#FFFF00']
});
// Right corner confetti
confetti({
particleCount: 80,
spread: 40,
origin: { x: 1, y: 0.9 },
angle: 135,
startVelocity: 100,
scalar: 1.4,
gravity: 0.8,
ticks: 400,
colors: ['#cbdb09', '#1a6b1a', '#39ff14', '#FFFF00']
});
};
// Calculate seconds until the next minute
const now = new Date();
const secondsUntilNextMinute = 60 - now.getSeconds();
// Initial timeout to sync with the minute mark
const initialTimeout = setTimeout(() => {
// First update when we hit the next minute
const newCount = calculateCurrentCount();
setCount(prevCount => {
if (Math.floor(newCount) !== Math.floor(prevCount)) {
setIsAnimating(true);
triggerConfetti();
setTimeout(() => setIsAnimating(false), 300);
}
return newCount;
});
setLastUpdate(new Date());
// Then set up an interval for every minute after that
const interval = setInterval(() => {
const newCount = calculateCurrentCount();
setCount(prevCount => {
if (Math.floor(newCount) !== Math.floor(prevCount)) {
setIsAnimating(true);
triggerConfetti();
setTimeout(() => setIsAnimating(false), 300);
}
return newCount;
});
setLastUpdate(new Date());
}, 60000); // Check every 60 seconds
return () => clearInterval(interval);
}, secondsUntilNextMinute * 1000);
// Cleanup
return () => clearTimeout(initialTimeout);
}, [selectedStore]);
Now we are TALKING.
With the web app wrapped up, all we need to do is configure the pi to boot up directly to this page.
To do this we'll need to create a new script that will launch Chromium in kiosk mode:
sudo nano /usr/bin/kiosk.sh
Then add these fields to the existing file:
#!/bin/bash
xset s off
xset s noblank
xset -dpms
unclutter -idle 0 -root &
sed -i 's/"exited_cleanly":false/"exited_cleanly":true/' ~/.config/chromium/Default/Preferences
sed -i 's/"exit_type":"Crashed"/"exit_type":"Normal"/' ~/.config/chromium/Default/Preferences
/usr/bin/chromium-browser --noerrdialogs --disable-infobars --kiosk https://neighbor-rewards.vercel.app/
Make it executable:
sudo chmod +x /usr/bin/kiosk.sh
Create an autostart directory:
mkdir -p ~/.config/autostart
Create a .desktop file to run the script on startup:
nano ~/.config/autostart/kiosk.desktop
And then add this to the desktop file:
[Desktop Entry]
Type=Application
Name=Kiosk
Exec=/usr/bin/kiosk.sh
X-GNOME-Autostart-enabled=true
Now on boot, the pi should go directly to the web app!
