Guide

Build a Postgres signup dashboard

Turn a table of accounts into two dashboard panels: the total number of accounts, and signups per UTC day for the last two weeks. You create a small sample table, a user that can only read it, and the two queries — then swap in your own table when it works.

Before you start: where the database has to be

lytics runs your queries from its own server, not from your browser. So the database has to be one that server can reach:

• A public hostname or address. lytics refuses any host that is or resolves to a private, loopback or link-local address — localhost, 127.0.0.1, 10.x, 192.168.x, a Docker container name — for every account. A database that only your laptop, your VPN or your private network can see will not work.
• Accepting connections from outside. If your provider puts a firewall or an allowed-address list in front of the database, it has to let lytics through.
• TLS. Connections require it unless you switch it off. Hosted Postgres services generally provide it.
• A user that can create a role and a schema, for steps 1 and 2 — the database owner, or an admin.

1. Create the sample table and data

A table of accounts with an id and the time each was created — the two things a signup chart needs. It goes in a schema of its own, lytics_tutorial, so it cannot collide with your tables and is removed with one statement. Run this as the database owner, in psql or any SQL client:

-- SAMPLE SCHEMA for the lytics tutorial. A schema of its own, so that
-- DROP SCHEMA lytics_tutorial CASCADE removes every trace of it.
CREATE SCHEMA lytics_tutorial;

CREATE TABLE lytics_tutorial.accounts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX accounts_created_at ON lytics_tutorial.accounts (created_at);

Then the sample data: 53 fictional accounts, 44 of them in the last fourteen days. The rows are dated relative to today in UTC, so the panels show the same counts whenever you run it. Run it once — running it again adds another 53.

-- SAMPLE DATA: 53 fictional accounts and nothing else — no names, no
-- email addresses. Dated relative to today (UTC), so the charts have
-- something to show whenever you run it. Run it once.
INSERT INTO lytics_tutorial.accounts (created_at)
SELECT least(
         now(),
         (date_trunc('day', now() AT TIME ZONE 'UTC')
            - make_interval(days => s.days_ago)
            + make_interval(mins => 37 + n * 151)) AT TIME ZONE 'UTC'
       )
  FROM (VALUES
         (40, 3), (31, 2), (22, 4), (13, 2), (12, 3), (11, 1),
         (10, 4), (8, 2), (7, 5), (6, 3), (5, 2), (4, 6),
         (3, 4), (2, 3), (1, 7), (0, 2)
       ) AS s(days_ago, signups)
 CROSS JOIN LATERAL generate_series(1, s.signups) AS n;

2. Create a user that can only read that table

lytics connects as whatever user you give it, so give it one that can read the table and nothing else. lytics runs every query read-only as well, but the database's own permissions are the part you control:

-- Run as the database owner or an admin, in the database that has the table.
-- Replace your_database, and the password with a long random one.
CREATE ROLE lytics_reader LOGIN PASSWORD 'REPLACE_WITH_A_LONG_RANDOM_PASSWORD';
ALTER ROLE lytics_reader SET default_transaction_read_only = on;

GRANT CONNECT ON DATABASE your_database TO lytics_reader;
GRANT USAGE ON SCHEMA lytics_tutorial TO lytics_reader;
GRANT SELECT ON lytics_tutorial.accounts TO lytics_reader;   -- this table, nothing else

Replace your_database with the database's name and REPLACE_WITH_A_LONG_RANDOM_PASSWORD with a long random password — you type it into lytics once, in step 4. The ALTER ROLE line makes every session of this user read-only, whatever it connects with. To confirm what it can do:

-- Still as the admin: what can lytics_reader do?
SELECT has_table_privilege('lytics_reader', 'lytics_tutorial.accounts', 'SELECT') AS can_read,
       has_table_privilege('lytics_reader', 'lytics_tutorial.accounts', 'INSERT') AS can_insert;
-- can_read = true, can_insert = false

3. Create a lytics account

Create an account, or sign in. You arrive on Databases, where the rest of the tutorial happens. Connecting a database needs no app: an app only matters if you want the panels on its dashboard as well (step 7).

Create an account and open Databases

4. Connect the database

Under Connect a database, fill in the form from your provider's connection details. Each part of a connection string has its own field — do not paste a postgres:// URL into Host.

Press Connect. lytics looks the host up first, and refuses it there if it is private. The password is encrypted before it is stored and is never shown again; to change it, remove the connection and add it again.

Then press Test on the new connection. It should say Connected as lytics_reader — PostgreSQL …. If it says something else, see Troubleshooting.

5. Add the total-accounts panel

Open Add a panel and fill it in:

• Title: Accounts
• Database: the connection from step 4
• Show as: Number — it shows the first column of the first row, large.

SELECT count(*) AS accounts
  FROM lytics_tutorial.accounts

Press Try it: it runs the query without saving and shows 53. Then Save panel, which runs it once more and stores the result.

6. Add the daily-signups panel

• Title: Signups per day
• Show as: Columns — one column per day, so a day with no signups is a visible gap. Line suits a longer range, where the trend matters more than any one day.

-- Signups per UTC day, the last 14 days including today.
-- A day with no signups is a 0, not a missing column.
SELECT to_char(d.day, 'YYYY-MM-DD') AS day,
       count(a.id) AS signups
  FROM generate_series(
         date_trunc('day', now() AT TIME ZONE 'UTC') - interval '13 days',
         date_trunc('day', now() AT TIME ZONE 'UTC'),
         interval '1 day'
       ) AS d(day)
  LEFT JOIN lytics_tutorial.accounts a
         ON a.created_at >= d.day AT TIME ZONE 'UTC'
        AND a.created_at <  (d.day + interval '1 day') AT TIME ZONE 'UTC'
 GROUP BY d.day
 ORDER BY d.day

Three things about its shape matter to lytics:

• The label first, the number second. For columns and lines, the first column names each point and the first column after it whose values are all numbers is the value. Swap them and the panel says Nothing to chart.
• UTC days. Postgres works out created_at::date or current_date in the session's time zone, which comes from the server or the role, and lytics does not change it. This query builds each day from now() AT TIME ZONE 'UTC' and compares the timestamps against UTC midnights, so an account created at 23:30 UTC lands on the same day whatever the server is set to. Web analytics days in lytics are UTC too, so the two line up.
• The day as text. to_char(…, 'YYYY-MM-DD') is shown exactly as written. A date or timestamp column comes back as a full timestamp such as 2026-10-20T00:00:00.000Z, and a plain date can even arrive shifted into the day before.

The generate_series makes fourteen days whether or not anyone signed up on them; the LEFT JOIN and count(a.id) turn an empty day into a 0. Try it should show fourteen columns adding up to 44; then Save panel.

7. Check the stored results

With the sample data, the two panels show this. The dates below are worked out in UTC; your columns carry the dates of the fourteen days ending today in UTC, and today's column keeps filling until midnight UTC.

The other 9 accounts are older than two weeks: they count towards the total, not the chart.

Under each panel on Databases, Updated … in … ms says when its stored result was made. That stored result is what every screen shows — Databases, an app's dashboard and its embeds; none of them runs the query. It is replaced when:

• you save the panel, change its query with Save and run, or press Run now;
• the panel is older than its Refresh every setting — 15 minutes, an hour (the default), 6 hours or a day. lytics looks for panels that are due every five minutes and runs them one after another, so a refresh lands up to about five minutes after the interval. A run that fails also waits for the next interval, and the last good result stays on screen with failed: … next to the time.

To put the panels on an app's dashboard, choose the app under On on each panel, or under On the dashboard of when you add it. Pinned panels also appear in that app's embeds, which is one more reason to keep them to counts.

Limits

Use your own table

Once the sample works, point the same two queries at your own signups. You need the table, and the column that records when each row was created:

SELECT to_char(d.day, 'YYYY-MM-DD') AS day,
       count(t.your_created_column) AS signups
  FROM generate_series(
         date_trunc('day', now() AT TIME ZONE 'UTC') - interval '13 days',
         date_trunc('day', now() AT TIME ZONE 'UTC'),
         interval '1 day'
       ) AS d(day)
  LEFT JOIN your_schema.your_table t
         ON t.your_created_column >= d.day AT TIME ZONE 'UTC'
        AND t.your_created_column <  (d.day + interval '1 day') AT TIME ZONE 'UTC'
       -- AND t.deleted_at IS NULL     -- any filter goes here, in the ON
 GROUP BY d.day
 ORDER BY d.day

• Replace your_schema.your_table and your_created_column. Count a column that is never null on a real row, as here, so empty days still come out as 0.
• Put any filter — test accounts, deleted rows — in the ON clause, not in a WHERE: a WHERE on the joined table removes the empty days.
• If the column is a timestamp without a time zone that already holds UTC, compare it with the day directly:

  LEFT JOIN your_schema.your_table t
         ON t.your_created_column >= d.day
        AND t.your_created_column <  d.day + interval '1 day'

• For 30 days, change '13 days' to '29 days'. On a large table, an index on the timestamp column keeps the query well inside the fifteen seconds — the range in the join can use it.
• The total is SELECT count(*) AS accounts FROM your_schema.your_table, with the same filters in a WHERE.
• Grant the reader USAGE on your schema and SELECT on that one table, as in step 2 — not on the whole schema.

Built on lp-data.cloud? Its apps share the same user tables, and Add the lp-data.cloud panels writes these queries for you — see the docs.

Troubleshooting

Connection errors appear under the connection after Connect or Test; query errors under the form after Try it, or under the panel after a run.

Clean up

When you have moved the panels to your own table, remove the sample. In lytics, delete the two sample panels. In the database:

-- When you are done with the sample, as the admin:
DROP SCHEMA lytics_tutorial CASCADE;
-- and, if the reader is not reading anything else:
REVOKE CONNECT ON DATABASE your_database FROM lytics_reader;
DROP ROLE lytics_reader;

Next

• Create an account and connect your database.
• Not ready yet? Explore a sample dashboard with illustrative data, a database panel included.
• Embed the dashboard in another application, panels and all.