How to access the database for your Fisikal instance

How to make queries on the MySQL database behind your Fisikal instance.

If your company owns their own Fisikal instance then, since you own all the data, you may request direct read-only MySQL access to the database.

Querying the database allows your team to:

• Generate your own lists of data in forms that are not available in the Fisikal UI
• Allow Fisikal data to be extracted into a Business Intelligence (BI) tool for analysis
• Automate the retrieval of certain data for use in a workflow

Considerations

• Access to the database is read-only. We will not allow write access to the data.
• Running a complex or large report can have a performance impact on the Fisikal instance for other users. Consider performance when writing and running queries, and consider the timing of the query on the business.
• Consider whether the data you require is already available in the built-in reports in Fisikal. That is usually a significantly simpler method.
• Your private client information is available through this route in bulk. Consider where the data will go when it is extracted from Fisikal. It is secure in the database, but once it is in a CSV file and downloaded to a personal computer then the data is outside the security perimeter. 
• Do not share the credentials widely.

Requirements

IP address whitelisting

Access to the Fisikal database is granted using an IP address whitelist. You must supply the static IP address of the location from where queries will be made.

Make this request to your Fisikal account manager.

In return you will receive confirmation that the IP address has been whitelisted for your instance, and the credentials required for access.

Database querying software

For ad hoc queries you will need  a SQL querying tool.

For example:

• MySQL Workbench
  • https://www.mysql.com/products/workbench/
• JetBrains DataGrip
  • https://www.jetbrains.com/datagrip/
• dbForge Studio for MySQL
  • https://www.devart.com/dbforge/mysql/studio/download.html
• Retool
  • https://retool.com/

Use the credentials provided in your selected tool to connect to the MySQL database.

Database documentation

The database for Fisikal involves 100s of tables and joins.

There is no practical database diagram available (we've tried but it is too big to be useful).

There is no ongoing database documentation to complement the database itself.

Instead the database has been designed with industry standard practices to be recognisable and explorable by a mildly wise database technician.

Please do ask for assistance from your account manager if you have questions.

Useful queries

Details of a client:

select u.id,
       u.name,
       u.email,
       if(u.disabled, 'Inactive', 'Active') as 'status',
       u.alt_id,
       l.name                               as 'home_location_name',
       u.created_at,
       u.updated_at,
       u.last_login_at
from users u
         left join location_users lu on u.id = lu.user_id and lu.`default` = true
         left join locations l on l.id = lu.location_id
where u.id = 1234

Details of an event in the schedule:

select o.id,
       st.name                     as 'service_title',
       ac.name                     as 'activity',
       sc.name                     as 'category',
       o.occurs_at,
       l.name                      as 'location',
       u.name                      as 'trainer_name',
       count(oc.id)                as 'clients_booked',
       if(s.use_activity_schedule_colors, ac.schedule_color_header,
          s.schedule_color_header) as 'header_colour'
from appointments a
         join occurrences o on a.id = o.appointment_id
         join trainer_activities ta on a.trainer_activity_id = ta.id
         join services s on s.id = ta.service_id
         join service_titles st on s.service_title_id = st.id
         join activity_categories ac on s.activity_category_id = ac.id
         join service_categories sc on s.service_category_id = sc.id
         join locations l on ta.location_id = l.id
         join users u on u.id = ta.trainer_id
         join occurrence_clients oc on o.id = oc.occurrence_id and oc.is_cancelled = false
where o.id = 123

List of Packages assigned to a client:

select u.id                                   as 'client_id',
       u.name,
       csp.id                                 as 'csp_id',
       sp.name                                as 'service_package_name',
       csp.created_at,
       csp.starts_at,
       csp.updated_at,
       if(csp.disabled, 'Inactive', 'Active') as 'csp_status',
       if(csp.is_expired, 'Expired', '')      as 'is_expired',
       csp.expires_at,
       if(csp.refunded, 'Refunded', '')       as 'is_refunded',
       if(csp.archived, 'Archived', '')       as 'is_archived',
       csp.terminated_at,
       csp.terminated_at_note,
       csp.cost,
       csp.cost_discount,
       c.total_amount                         as 'total_credits_remaining'
from client_service_packages csp
         join service_packages sp on csp.service_package_id = sp.id
         join users u on u.id = csp.client_id
         left join (select c.client_service_package_id, sum(amount) as 'total_amount'
                    from credits c
                    group by c.client_service_package_id) c on c.client_service_package_id = csp.id
where u.id = 12345