Badges DB Design

Also see:

There are two separate databases:

  1. App Database which is PostGreSQL
  2. BadgeOS / WordPress database which is MySQL (version 5.7 which does not support CONNECT BY)

We're learning what will work to store in one DB versus the other, but generally:

  • Badge data goes in BadgeOS and leverages existing code that we expect to be "supported" with on-going and "backward compatible" releases.
  • Application-specific records are landing in the PostGreSQL DB.
  • Achievements and Badges are recorded within BadgeOS
  • Views can be used to pull records from the following interesting tables:
    • wp-posts
    • wp-postmeta

API Support

Categories below come from this page: Award Badges API



  • sub_achievements
  • badge_with_meta - Transposition of Badge Data
  • achievement_meta - Transposition of Sub Achievement data
  • achievement_type - Early and simple query.
  • step_meta - Transposition of Step meta data
  • badge_brief
  • sub_achievements_with_parent


Both Progress and Badge Brief depend on User's Badges

Badge Brief

Both Progress and Badge Brief depend on User's Badges

Send Events

Example Views

Finding achievement types

drop view achievement_type;
CREATE VIEW `achievement_type` AS 
  select p.`ID` AS `post_id`,
         p.`post_title` AS `post_title`,
         p.`post_name` AS `post_name`,
         p.`guid` as url,
         thumbp.`guid` as image_url
    from `wp_posts` as p
    join `wp_postmeta` as pm
      on pm.post_id =
     and pm.meta_key = '_thumbnail_id'
    join `wp_posts` as thumbp
      on pm.meta_value =
   where ((p.`post_type` = 'achievement-type') and (p.`post_status` = 'publish'))
  • Find the records in wp_posts for post_type = "achievement-type"
  • Join on id = post_id to the wp_postmeta table
  • Join the _thumbnail_id attribute's value back to the wp_posts table to find the guid

Finding Badge Summaries

    `p`.`ID` AS `post_id`,
    `p`.`post_title` AS `post_title`,
    `p`.`post_name` AS `post_name`,
    `p`.`guid` AS `url`,
    `thumbp`.`guid` AS `image_url`
            `wp_posts` `p`
        JOIN `wp_postmeta` `pm` ON
                    (`pm`.`post_id` = `p`.`ID`) AND(`pm`.`meta_key` = '_thumbnail_id')
    LEFT JOIN `wp_posts` `thumbp` ON
        ((`pm`.`meta_value` = `thumbp`.`ID`))
        (`p`.`post_type` = 'seekers') AND(`p`.`post_status` = 'publish')
  • post_title is what shows up as the achievement page's title.
  • post_name is the slug (or perma-link) which is editable under "quick edit". I'm using the aware, adept, advocate, angel labels for these and this affects joins later on.

Sub-Achievement with Parent

CREATE VIEW sub_achievement_with_parent AS
SELECT p.ID as sub_achievement_post_id,
       p.post_title as sub_achievement_name, as step_id,
       parent_step.post_title as step_name,
       parent.ID as parent_id,
       parent.post_title as parent_name
  FROM wp_posts p
  LEFT JOIN wp_p2p p2s
    ON p2s.p2p_from = p.ID
  LEFT JOIN wp_posts parent_step
    ON parent_step.ID = p2s.p2p_to
  LEFT JOIN wp_p2p p2p
    ON p2p.p2p_from = parent_step.ID
  LEFT JOIN wp_posts parent
    ON parent.ID = p2p.p2p_to    
 WHERE p.post_type = 'sub-achievement' 
   AND parent_step.post_type = 'step'

Meta per Badge

SELECT pm.* FROM `wp_postmeta` pm
  JOIN badge_brief b
    on b.post_id = pm.post_id

Steps per Badge

SELECT p.post_title as badge_name,
       child.post_title as step_name
  FROM wp_posts p
  LEFT JOIN wp_p2p p2p
    ON p2p.p2p_to = p.ID
  LEFT JOIN wp_posts child
    ON child.ID = p2p.p2p_from
 WHERE p.post_type = 'seekers'

Recursive Queries

select  id,
from    (select * from tablename
         order by parent, id) tablename,
        (select @pv := '1') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', id)

Dirty Data

Coming across some problem records when running this query:

SELECT p.ID as step_post_id,
       if (p.post_type = 'step', p.post_title, null) as step_name, 
       if (p.post_type = 'constituent-step', p.post_title, null) as constituend_step_name,
       parent.ID as parent_id,
       parent.post_title as parent_name
  FROM wp_posts p
  LEFT JOIN wp_p2p p2p
    ON p2p.p2p_from = p.ID
  LEFT JOIN wp_posts parent
    ON parent.ID = p2p.p2p_to
 WHERE (p.post_type = 'constituent-step' 
    OR p.post_type = 'step')
   AND > 3000
  • There are "Auto Draft" records which do not have a parent.
  • There are records with no title that list unused records as parents.

From 2017


  • Use PostGRESQL as platform since Clue Ride is a Geo-spatial application, but would be nice to have ability to support MySQL as well. Currently installed on Mensa
  • Familiar with Hibernate, but not a requirement
  • Credentials provided as part of an authenticated session; all requests will be specific to that User ID with exception of admin accounts which have raised credentials (separate API?)
  • Module supports REST API with JSON payload.
  • Java-based service layer
  • Want to explore DAO layers

Use Cases

UC diagram here

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License