Fedora Badges Developer Database Environment is now available for the team members belonging to Fedora Badges Engineering to connect to, understand how the existing system database works and begin designing an API around it. This database has been deployed on my self-hosted homelab infrastructure and is a copy of a snapshot of the original PostgreSQL database taken on 9th June 2023. I originally planned on bringing this up that day, but I had to rework my networking setup when I moved from ZeroTier[1] to Wireguard[2], which ended up causing delays.
The database can be connected to using the following credentials
Database tahrir
Username tahrir-readonly
Password tahrir-readonly
URI badgesdb-main.apexaltruism.net:5432
A role having write access to the database is also available and is important for the purposes of developing an API around this. Please feel free to comment under this discussion post and I should be able to provide you with those. I would like to emphasize again the fact that this is a copy of the original PostgreSQL database so experimenting with it is encouraged as it will help the team members to better understand the database architecture. The learnings taken from investigating the existing database will be considered while re-designing the schema.
For folks who are not adept with PostgreSQL, here’s a link to a Grafana[3] application, the graphical user interface of which can be used to easily interact with the database. Unlike the URI to connect to the actual database, this unfortunately does not have a role with write access and it can only be used to study the existing relations available in the database. This generally helps lower the barrier of entry by a lot and can be a great jumping point into helping the Fedora Badges Engineering team with designing and developing the refreshed database schema.
The application can be connected to using the following credentials
Username badgesdb-readonly
Password l0v3f3d0r4b4d8e5
URI https://explorer.apexaltruism.net/
Please use the following steps to navigate to the exploration menu on the application.
Open the aforementioned URI on a browser to log in with the credentials provided.
@t0xic0der first of all it is great work to see that people wants play with current DB is awesome. I hope that it will also help other folks who wants to help out in new fedora badge system.
I am also willing to help you to create podman/pod container for testing locally and write and change values based on testing purposes on new system. I think it would be worth to say that "this is DB is NOT THE PRODUCTION one so in case If something happening to this it is all safe.
I have stated twice before and the URIs also state it clearly that this is not the database that is deployed on the production but rather a copy of a snapshot that was taken on 9th June 2023. I do not think that there would be much difference if we were to state this again on the application itself.
I am probably mistaken here but I think that this database does not have the information about who has what badges and when it was awarded to them. I am looking into making a copy of a snapshot of that database available as well - as soon as possible for folks to take a look at and play around with.
This is awesome! Thank you @t0xic0der!
Just some notes from my own look around.
-- milestone (empty)
select * from milestone;
-- series (empty)
select * from series;
-- team (1 record, infra)
select * from team;
-- issuers (1 record, Fedora Project)
select * from issuers;
-- invitations (312 rows, looks like it allows you with a cupon code to collect a badge?)
select * from invitations;
-- assertions (387118 rows, badge assignment table - people to badge)
select * from assertions;
-- badges (587 records, the badge meta data)
-- id for badge is a string - the slug for a badge
select * from badges;
-- authorizations (1038 records, people who are allowed to assertion a badge or grant it manually)
select * from authorizations;
-- persons (59636 records, people from FAS - email, nickname, website, bio, created_on, last_login)
-- email address seems to be "nickname"||@fedoraproject.org
-- some emails seem to be email||@fedoraproject.org - maybe fas account is a email for them?
select * from persons;
--- scratch notes
-- find accounts with probable errors by email address
select * from persons where substring(email FROM '@(.*)$') in (
select substring(email FROM '@(.*)$') AS domain_name from persons group by substring(email FROM '@(.*)$') having count(*) < 1000
);
-- find accounts with no badges (128 rows)
select * from persons where id not in (select person_id from assertions);
-- find some badges that have not been issued recently or a lot
with dataset as (
select b.id, b.name, count, min, max, 'Not Issued Often' as category from badges b
join (
select badge_id, count(*), min(issued_on), max(issued_on) from assertions group by badge_id
) a on a.badge_id = b.id
)
select *, 'Not Issued Often' as result_type from dataset where count < 100 union
select *, 'Not Issued for 3 Years' as result_type from dataset where max < now()::DATE-'3 year'::interval;
A few comments from the top of my head regarding your observations.
That’s because the system was designed as a “global” badges aggregator allowing different organizations (e.g. other distros) to issue badges. That never materialized. So all badges are issued by Fedora Project, hence only one issuer.
That’s correct. These are mostly event badges, where either a link or a QR code is given out to participants to claim their badge.
Correct. Authorizations allow authorized people to manually award a badge to other people. Authorization is also needed for creating invitation links for event badges (see above).
It’s FAS username@fedoraproject.org. The part before the @ corresponds to the FAS username. I’m not entirely sure, but I believe fedoraproject.org is the Kerberos domain. It also is an e-mail alias which will be forwarded to the address set in FAS.
I hope that helps to shed some light. If not for you then for others exploring the database.
Ouch. That’s probably a bug. My memory here is dim, but I think there was an issue where the account system briefly let people log in with their email address instead of username, and then that confused a bunch of applications. I think those should be rewritten in the database — but possibly the email address username is different from the corresponding FAS username, so … carefully.
Yeah. That’s definitely not correct. Looks like we have some cleaning up to do in the process of migrating the data.
Although, I could also imagine that these accounts are now stale and, possibly, need to be merged with the correct account. I hope to be able to find the time either today or tomorrow to peek around the database myself.
