Python PostgreSQL daily sales report
A daily sales desk shows yesterday's revenue, order count, weekly change, and leading products beside an archive of earlier reports. Tokay runs the generator every morning and keeps the Flask viewer live as a Web Service.
Last updated
The two Python entry points share one PostgreSQL database, which is a useful shape when scheduled work creates something people return to later. Ninety days of fictional orders make the first report meaningful without another account.
- Runtime
- Python
- Framework
- Flask
- Service types
- Web Service, Scheduled Service
- Resources
- PostgreSQL
- Required secrets
- None
What it does
The viewer opens with a clear prompt when no report has run yet.
After the generator finishes, the latest report shows revenue, orders, change from the prior week, and the three products that earned the most. Report history links back through earlier dates.
Run the same reporting date again and the existing report refreshes instead of producing a duplicate.
Deploy it
Bring this folder to Tokay any way you like. Paste or upload it in the dashboard, push it with git, or hand it to your AI agent along with our agent instructions. Tokay figures out the rest.
Working in Claude, ChatGPT, VS Code, or another MCP client? Connect the Tokay MCP server and ask your agent to deploy this example. It signs in through your browser, so there is no token to paste.
New to Tokay? The getting started guide walks you through your first deploy.
Try it
- Open the generator Service and choose Run Now.
- Refresh the viewer and open yesterday's report from Report history.
- Run the generator again. The same dated report gets a new generated time rather than a duplicate row.
How it works
You only need one schema owner. The viewer applies the Alembic migration and seeds fictional orders, while the generator reads those tables, calculates its reporting windows, saves one dated result, and exits.
Tokay starts that short process on schedule and keeps every run result. If the calculation fails, the error remains attached to the run instead of disappearing into a long lived server log.
Secrets
You do not need to add a secret. Tokay supplies the shared DATABASE_URL to both Services.
Grow from here
Try the uptime monitor when a scheduled job should keep lightweight history with the Service.
Source code
generator/generate_report.py Raw
import json
import os
import sys
from collections import defaultdict
from datetime import date, timedelta
from decimal import Decimal
import psycopg
from psycopg.rows import dict_row
def revenue_between(connection, start, end):
row = connection.execute(
"""
SELECT COALESCE(SUM(quantity * unit_price_cents), 0) AS revenue
FROM orders
WHERE ordered_on BETWEEN %s AND %s
""",
(start, end),
).fetchone()
return int(row["revenue"])
def main():
report_date = date.today() - timedelta(days=1)
with psycopg.connect(os.environ["DATABASE_URL"], row_factory=dict_row) as connection:
orders = connection.execute(
"SELECT item, quantity, unit_price_cents FROM orders WHERE ordered_on = %s ORDER BY item",
(report_date,),
).fetchall()
for order in orders:
if order["quantity"] <= 0 or order["unit_price_cents"] < 0:
raise ValueError(f"Invalid order data for {order['item']} on {report_date}")
item_revenue = defaultdict(int)
revenue = 0
for order in orders:
line_total = order["quantity"] * order["unit_price_cents"]
item_revenue[order["item"]] += line_total
revenue += line_total
top_items = [
{"name": name, "revenue_cents": amount}
for name, amount in sorted(item_revenue.items(), key=lambda item: (-item[1], item[0]))[:3]
]
week_start = report_date - timedelta(days=6)
previous_end = week_start - timedelta(days=1)
previous_start = previous_end - timedelta(days=6)
week_revenue = revenue_between(connection, week_start, report_date)
previous_revenue = revenue_between(connection, previous_start, previous_end)
change = None if previous_revenue == 0 else Decimal(week_revenue - previous_revenue) * 100 / Decimal(previous_revenue)
connection.execute(
"""
INSERT INTO daily_reports
(report_date, revenue_cents, order_count, top_items, week_revenue_cents,
previous_week_revenue_cents, week_change_percent, generated_at)
VALUES (%s, %s, %s, %s::jsonb, %s, %s, %s, now())
ON CONFLICT (report_date) DO UPDATE SET
revenue_cents = EXCLUDED.revenue_cents,
order_count = EXCLUDED.order_count,
top_items = EXCLUDED.top_items,
week_revenue_cents = EXCLUDED.week_revenue_cents,
previous_week_revenue_cents = EXCLUDED.previous_week_revenue_cents,
week_change_percent = EXCLUDED.week_change_percent,
generated_at = now()
""",
(report_date, revenue, len(orders), json.dumps(top_items), week_revenue, previous_revenue, change),
)
print(json.dumps({"event": "daily_sales_report", "date": str(report_date), "revenue_cents": revenue, "orders": len(orders)}))
print(f"Daily report ready for {report_date}. {len(orders)} orders and ${revenue / 100:.2f} revenue.")
if __name__ == "__main__":
try:
main()
except Exception as error:
print(f"Daily report failed. {error}", file=sys.stderr)
raise
generator/requirements.txt Raw
psycopg[binary]==3.3.4
viewer/migrations/versions/20260718_01_create_sales_reports.py Raw
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
revision = "20260718_01"
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
op.create_table(
"orders",
sa.Column("id", postgresql.UUID(as_uuid=False), primary_key=True),
sa.Column("ordered_on", sa.Date(), nullable=False),
sa.Column("item", sa.Text(), nullable=False),
sa.Column("quantity", sa.Integer(), nullable=False),
sa.Column("unit_price_cents", sa.Integer(), nullable=False),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
)
op.create_index("orders_ordered_on_idx", "orders", ["ordered_on"])
op.create_table(
"daily_reports",
sa.Column("report_date", sa.Date(), primary_key=True),
sa.Column("revenue_cents", sa.BigInteger(), nullable=False),
sa.Column("order_count", sa.Integer(), nullable=False),
sa.Column("top_items", postgresql.JSONB(), nullable=False),
sa.Column("week_revenue_cents", sa.BigInteger(), nullable=False),
sa.Column("previous_week_revenue_cents", sa.BigInteger(), nullable=False),
sa.Column("week_change_percent", sa.Numeric(8, 2), nullable=True),
sa.Column("generated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
)
def downgrade():
op.drop_table("daily_reports")
op.drop_index("orders_ordered_on_idx", table_name="orders")
op.drop_table("orders")
viewer/migrations/env.py Raw
import os
from alembic import context
from sqlalchemy import engine_from_config, pool
config = context.config
database_url = os.environ["DATABASE_URL"].replace("postgresql://", "postgresql+psycopg://", 1)
config.set_main_option("sqlalchemy.url", database_url.replace("%", "%%"))
def run_migrations_online():
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(connection=connection, target_metadata=None)
with context.begin_transaction():
context.run_migrations()
run_migrations_online()
viewer/static/site.css Raw
:root { color-scheme: light; font-family: Inter, ui-sans-serif, system-ui, sans-serif; background: #f1eee7; color: #242823; }
* { box-sizing: border-box; }
body { margin: 0; }
a { color: inherit; }
header { width: min(1120px, calc(100% - 36px)); margin: 0 auto; padding: 22px 0; display: flex; justify-content: space-between; border-bottom: 1px solid #c8c2b6; font-weight: 800; }
header a { text-decoration: none; }
header span { color: #79766d; font-weight: 500; }
main { width: min(1120px, calc(100% - 36px)); margin: 58px auto 90px; display: grid; grid-template-columns: 1.25fr 0.75fr; gap: 28px; }
.intro { grid-column: 1 / -1; display: grid; grid-template-columns: 1.3fr 0.7fr; gap: 22px 60px; align-items: end; margin-bottom: 28px; }
.kicker { grid-column: 1 / -1; color: #9c4f36; font-size: 0.75rem; font-weight: 800; letter-spacing: 0.12em; text-transform: uppercase; }
h1 { margin: 0; font: 500 clamp(3.6rem, 9vw, 8rem)/0.86 Georgia, serif; letter-spacing: -0.06em; }
.intro > p:last-child { margin: 0; color: #68685f; font: 1.08rem/1.7 Georgia, serif; }
.report, .empty { padding: 28px; border-radius: 18px; background: #fbf8f1; box-shadow: 0 18px 50px #47413815; }
.report-head { display: flex; justify-content: space-between; align-items: start; }
.report-head p { margin: 0 0 6px; color: #847e72; }
h2 { margin: 0; font: 500 2rem Georgia, serif; }
.metrics { display: grid; grid-template-columns: repeat(3, 1fr); gap: 12px; margin: 30px 0; }
.metrics article { padding: 18px; border: 1px solid #d5cec1; border-radius: 12px; }
.metrics span, .metrics strong { display: block; }
.metrics span { color: #80796f; font-size: 0.78rem; }
.metrics strong { margin-top: 8px; font: 500 1.8rem Georgia, serif; }
.top { border-top: 1px solid #d5cec1; }
.top h3 { font: 500 1.45rem Georgia, serif; }
.top div { display: flex; justify-content: space-between; padding: 11px 0; border-top: 1px solid #e2ddd3; }
.generated { margin: 24px 0 0; color: #898277; font-size: 0.78rem; }
.history { padding: 24px; border-radius: 18px; background: #2f5147; color: #f7f1e4; }
.history h2 { margin-bottom: 20px; }
.history a { display: grid; grid-template-columns: 1fr auto; gap: 4px 15px; padding: 13px 0; border-top: 1px solid #ffffff30; text-decoration: none; }
.history small { grid-column: 1 / -1; color: #cbd5ce; }
.empty { min-height: 310px; display: grid; align-content: center; }
.empty p { color: #6b685f; line-height: 1.6; }
@media (max-width: 760px) { main, .intro { grid-template-columns: 1fr; } .intro > * { grid-column: 1; } .metrics { grid-template-columns: 1fr; } }
viewer/templates/reports.html Raw
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Juniper Goods daily sales</title>
<link rel="stylesheet" href="/static/site.css">
</head>
<body>
<header><a href="/">Juniper Goods</a><span>Daily sales desk</span></header>
<main>
<section class="intro">
<p class="kicker">A clear morning number</p>
<h1>How did yesterday go?</h1>
<p>Each report comes from the orders in PostgreSQL. Open one before the day gets noisy.</p>
</section>
{% if report %}
<section class="report">
<div class="report-head"><div><p>Report for</p><h2>{{ report.report_date.strftime('%A, %B %-d') }}</h2></div><a href="/">All reports</a></div>
<div class="metrics">
<article><span>Revenue</span><strong>${{ '%.2f'|format(report.revenue_cents / 100) }}</strong></article>
<article><span>Orders</span><strong>{{ report.order_count }}</strong></article>
<article><span>Week change</span><strong>{% if report.week_change_percent is none %}New{% else %}{{ '%+.1f'|format(report.week_change_percent) }}%{% endif %}</strong></article>
</div>
<div class="top"><h3>What sold</h3>{% for item in report.top_items %}<div><span>{{ loop.index }}. {{ item.name }}</span><strong>${{ '%.2f'|format(item.revenue_cents / 100) }}</strong></div>{% endfor %}</div>
<p class="generated">Generated {{ report.generated_at.strftime('%b %-d at %-I:%M %p') }}</p>
</section>
{% else %}
<section class="empty"><h2>Your first report is one run away.</h2><p>Open the Daily Sales Generator Service and choose <strong>Run Now</strong>.</p></section>
{% endif %}
<section class="history">
<h2>Report history</h2>
{% for item in reports %}<a href="/reports/{{ item.report_date }}"><span>{{ item.report_date.strftime('%b %-d') }}</span><strong>${{ '%.2f'|format(item.revenue_cents / 100) }}</strong><small>{{ item.order_count }} orders</small></a>{% else %}<p>No reports yet.</p>{% endfor %}
</section>
</main>
</body>
</html>
viewer/alembic.ini Raw
[alembic]
script_location = %(here)s/migrations
prepend_sys_path = .
[loggers]
keys = root,sqlalchemy,alembic
[handlers]
keys = console
[formatters]
keys = generic
[logger_root]
level = WARN
handlers = console
qualname =
[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine
[logger_alembic]
level = INFO
handlers =
qualname = alembic
[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic
[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
viewer/app.py Raw
import os
import time
import uuid
from datetime import date, timedelta
import psycopg
from flask import Flask, abort, render_template
from psycopg.rows import dict_row
app = Flask(__name__)
ITEMS = [
("Stoneware mug", 2800),
("Linen tea towel", 1900),
("Olive wood spoon", 1600),
("Pour over set", 5200),
("Pantry labels", 1200),
("Market tote", 3400),
]
def connect():
return psycopg.connect(os.environ["DATABASE_URL"], row_factory=dict_row)
def seed_orders():
today = date.today()
rows = []
for days_ago in range(1, 91):
day = today - timedelta(days=days_ago)
order_total = 2 + ((day.toordinal() * 7) % 5)
if day.weekday() in (4, 5):
order_total += 2
for index in range(order_total):
item, price = ITEMS[(day.toordinal() + index * 3) % len(ITEMS)]
quantity = 1 + ((day.toordinal() + index) % 3 == 0)
rows.append((str(uuid.uuid5(uuid.NAMESPACE_URL, f"sales:{day}:{index}")), day, item, quantity, price))
with connect() as connection:
with connection.cursor() as cursor:
cursor.executemany(
"""
INSERT INTO orders (id, ordered_on, item, quantity, unit_price_cents)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT (id) DO NOTHING
""",
rows,
)
def seed_with_retry():
for attempt in range(8):
try:
seed_orders()
return
except psycopg.Error:
if attempt == 7:
raise
time.sleep(min(2 ** attempt, 8))
@app.get("/")
def index():
with connect() as connection:
reports = connection.execute(
"SELECT * FROM daily_reports ORDER BY report_date DESC LIMIT 45"
).fetchall()
return render_template("reports.html", reports=reports, report=None)
@app.get("/reports/<report_date>")
def report_detail(report_date):
with connect() as connection:
report = connection.execute(
"SELECT * FROM daily_reports WHERE report_date = %s", (report_date,)
).fetchone()
reports = connection.execute(
"SELECT * FROM daily_reports ORDER BY report_date DESC LIMIT 45"
).fetchall()
if not report:
abort(404)
return render_template("reports.html", reports=reports, report=report)
@app.get("/health")
def health():
return {"ok": True}
seed_with_retry()
if __name__ == "__main__":
app.run(host="0.0.0.0", port=int(os.getenv("PORT", "5000")))
viewer/requirements.txt Raw
Flask==3.1.3
gunicorn==26.0.0
psycopg[binary]==3.3.4
alembic==1.18.5
Built from revision 52c7b7f