Documentation for standard Jira tables

Question

We are running Jira on prem and are looking into reporting off of the Jira DB. We are still in the discovery phase of this project. Does documentation currently exist on what standard tables exist, fields in the tables, field descriptions, primary keys, foreign keys, etc.

Nic Brough -Adaptavist- · Answer

Oh, you could be, that sounds about right for an off-the-shelf Jira.  It's not the tables that are the problem though, it's the logic you'll need to extract what you need.

Random example - imagine an issue with a summary, assignee and three components set.  A simple read of just that data and nothing else needs 8 joins if you want the data as its displayed (or one REST call)

Collin Marsden · Answer

Perhaps we are not using Jira to it's full potential then, as only 170 tables have any data in them at all. The majority of the data rests in about 30 tables.

Nic Brough -Adaptavist- · Answer

This is a spectacularly bad idea.  Jira databases are not a database, they're just a data store, and they simply are not built for reporting, and you should be looking at Apps that improve/add reporting and understand the data for you.  Or even, just using the REST API.  (I've managed to build a query that needed 200 joins to get the same data I could get from a 200 character REST call)

If you insist, then three pieces of advice:

* Prepare to spend a lot of time building and then maintaining partially incomplete data dictionaries.  A lot.
* Do not report off the production database.  Replicate it and report off a slave.  Then it won't matter to production users when you run a horrendous query that cripples the performance
* Start digging at https://developer.atlassian.com/server/jira/platform/database-schema/