Fedora Badges Developer Database Environment is now available!

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.

  1. Open the aforementioned URI on a browser to log in with the credentials provided.

  2. Use the sidebar to navigate to the exploration menu as shown with the Explore link.

  3. Select the Fedora Badges PostgreSQL database as the data source from the dropdown menu.

  4. Use the query builder functionality to craft SQL queries using the user interface[4].

  5. Use the code query functionality to craft SQL queries using the textbox interface[5].

Feel free to reach out to me if you have any questions regarding this and I will be glad to be of assistance.


  1. https://www.zerotier.com/ ↩︎

  2. ↩︎

  3. ↩︎

  4. Preferably, with your mouse ↩︎

  5. Preferably, with your keyboard ↩︎

3 Likes

@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.

Again thank you.

1 Like

@thunderbirdtr, glad to be of help.

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.

Thanks for posting some interesting queries.

1 Like

But only for people in at least one non-default FAS group (and maybe also FPCA?). Otherwise, spammers…

There’s a few folks who have what appears to be an email address and then it appends “@fedoraproject.org”.

An example seems to be similar to this:

rwright@emaildomain.com@fedoraproject.com

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. :broom:

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.

1 Like

The application can be connected to using the following credentials

  • URI
    https://explorer.apexaltruism.net/

Some days ago works fine but Cloudflare report an error for now

Error 1033

The host (explorer.apexaltruism.net) is configured as an Argo Tunnel, and Cloudflare is currently unable to resolve it.

Hey @icesvz,

The local ISP has been giving me trouble but now it should be fixed. Can you please check again and see if it is working?

1 Like

it works fine, thanks