In which table are the user story entries stored?

[Andreas Pluto]

I need this information to summarize the development of the project for the final control by the auditors.

I hope, someone cans solve this little problem:-)

P.S.: Explanatory snapshots I have - just can't get them uploaded

Regards,

Andreas Pluto

[Nic Brough -Adaptavist- · Accepted solution]

I would strongly recommend that you do not bother reading the database.

Issue (story) data is smeared across 18 tables, and that's for an issue that has no custom fields, and is just an issue (no service desk request, and not being used in a Software project).  This increases exponentially as you add fields or other functions, and you're going to find that when you look at an issue in the UI, there are things in it that do not appear to be in the database at all.

I would take a step back and give us the actual question the auditors are asking, rather than just guess that the database is the place to look (because it isn't).

[Andreas Pluto]

Hi,

ok - 18 tables. That sounds like fun :-)
How about the names :-) ?

.

[Nic Brough -Adaptavist-]

To form useful queries in SQL from a Jira Software database, you need to understand the logical (done in code, not the database) relationships between these tables:

AO_0201F0_KB_HELPFUL_AGGR
AO_0201F0_KB_VIEW_AGGR
AO_0201F0_STATS_EVENT
AO_0201F0_STATS_EVENT_PARAM
AO_0A5972_NOTIFICATION_SETTING
AO_0A5972_PUSH_REGISTRATION
AO_0AC321_RECOMMENDATION_AO
AO_21D670_WHITELIST_RULES
AO_21F425_MESSAGE_AO
AO_21F425_MESSAGE_MAPPING_AO
AO_21F425_USER_PROPERTY_AO
AO_2C4E5C_MAILCHANNEL
AO_2C4E5C_MAILCONNECTION
AO_2C4E5C_MAILGLOBALHANDLER
AO_2C4E5C_MAILHANDLER
AO_2C4E5C_MAILITEM
AO_2C4E5C_MAILITEMAUDIT
AO_2C4E5C_MAILITEMCHUNK
AO_2C4E5C_MAILRUNAUDIT
AO_2F1435_HEALTH_CHECK_STATUS
AO_2F1435_PROPERTIES
AO_2F1435_READ_NOTIFICATIONS
AO_38321B_CUSTOM_CONTENT_LINK
AO_3B1893_LOOP_DETECTION
AO_4789DD_HEALTH_CHECK_STATUS
AO_4789DD_PROPERTIES
AO_4789DD_READ_NOTIFICATIONS
AO_4789DD_TASK_MONITOR
AO_4AEACD_WEBHOOK_DAO
AO_54307E_AGENTSIGNATURES
AO_54307E_ASYNCUPGRADERECORD
AO_54307E_CAPABILITY
AO_54307E_CONFLUENCEKB
AO_54307E_CONFLUENCEKBENABLED
AO_54307E_CONFLUENCEKBLABELS
AO_54307E_CSATENTRIES
AO_54307E_CUSTOMGLOBALTHEME
AO_54307E_CUSTOMTHEME
AO_54307E_EMAILCHANNELSETTING
AO_54307E_EMAILSETTINGS
AO_54307E_GOAL
AO_54307E_GROUP
AO_54307E_GROUPTOREQUESTTYPE
AO_54307E_IMAGES
AO_54307E_METRICCONDITION
AO_54307E_PARTICIPANTSETTINGS
AO_54307E_QUEUE
AO_54307E_QUEUECOLUMN
AO_54307E_REPORT
AO_54307E_SERIES
AO_54307E_SERVICEDESK
AO_54307E_STATUSMAPPING
AO_54307E_THRESHOLD
AO_54307E_TIMEMETRIC
AO_54307E_VIEWPORT
AO_54307E_VIEWPORTFIELD
AO_54307E_VIEWPORTFIELDVALUE
AO_54307E_VIEWPORTFORM
AO_550953_SHORTCUT
AO_563AEE_ACTIVITY_ENTITY
AO_563AEE_ACTOR_ENTITY
AO_563AEE_MEDIA_LINK_ENTITY
AO_563AEE_OBJECT_ENTITY
AO_563AEE_TARGET_ENTITY
AO_575BF5_DEV_SUMMARY
AO_575BF5_PROCESSED_COMMITS
AO_575BF5_PROVIDER_ISSUE
AO_575BF5_PROVIDER_SEQ_NO
AO_587B34_GLANCE_CONFIG
AO_587B34_PROJECT_CONFIG
AO_5FB9D7_AOHIP_CHAT_LINK
AO_5FB9D7_AOHIP_CHAT_USER
AO_60DB71_AUDITENTRY
AO_60DB71_BOARDADMINS
AO_60DB71_CARDCOLOR
AO_60DB71_CARDLAYOUT
AO_60DB71_COLUMN
AO_60DB71_COLUMNSTATUS
AO_60DB71_DETAILVIEWFIELD
AO_60DB71_ESTIMATESTATISTIC
AO_60DB71_ISSUERANKING
AO_60DB71_ISSUERANKINGLOG
AO_60DB71_LEXORANK
AO_60DB71_LEXORANKBALANCER
AO_60DB71_NONWORKINGDAY
AO_60DB71_QUICKFILTER
AO_60DB71_RANKABLEOBJECT
AO_60DB71_RAPIDVIEW
AO_60DB71_SPRINT
AO_60DB71_STATSFIELD
AO_60DB71_SUBQUERY
AO_60DB71_SWIMLANE
AO_60DB71_TRACKINGSTATISTIC
AO_60DB71_VERSION
AO_60DB71_WORKINGDAYS
AO_733371_EVENT
AO_733371_EVENT_PARAMETER
AO_733371_EVENT_RECIPIENT
AO_7A2604_CALENDAR
AO_7A2604_HOLIDAY
AO_7A2604_WORKINGTIME
AO_82B313_ABILITY
AO_82B313_ABSENCE
AO_82B313_AVAILABILITY
AO_82B313_INIT
AO_82B313_PERSON
AO_82B313_RESOURCE
AO_82B313_SKILL
AO_82B313_TEAM
AO_88263F_HEALTH_CHECK_STATUS
AO_88263F_PROPERTIES
AO_88263F_READ_NOTIFICATIONS
AO_97EDAB_USERINVITATION
AO_9B2E3B_EXEC_RULE_MSG_ITEM
AO_9B2E3B_IF_COND_CONF_DATA
AO_9B2E3B_IF_COND_EXECUTION
AO_9B2E3B_IF_CONDITION_CONFIG
AO_9B2E3B_IF_EXECUTION
AO_9B2E3B_IF_THEN
AO_9B2E3B_IF_THEN_EXECUTION
AO_9B2E3B_PROJECT_USER_CONTEXT
AO_9B2E3B_RSETREV_PROJ_CONTEXT
AO_9B2E3B_RSETREV_USER_CONTEXT
AO_9B2E3B_RULE
AO_9B2E3B_RULE_EXECUTION
AO_9B2E3B_RULESET
AO_9B2E3B_RULESET_REVISION
AO_9B2E3B_THEN_ACT_CONF_DATA
AO_9B2E3B_THEN_ACT_EXECUTION
AO_9B2E3B_THEN_ACTION_CONFIG
AO_9B2E3B_THEN_EXECUTION
AO_9B2E3B_WHEN_HAND_CONF_DATA
AO_9B2E3B_WHEN_HANDLER_CONFIG
AO_A0B856_WEB_HOOK_LISTENER_AO
AO_A415DF_AOABILITY
AO_A415DF_AOABSENCE
AO_A415DF_AOAVAILABILITY
AO_A415DF_AOCONFIGURATION
AO_A415DF_AOCUSTOM_WORDING
AO_A415DF_AODEPENDENCY
AO_A415DF_AODOOR_STOP
AO_A415DF_AOESTIMATE
AO_A415DF_AOEXTENSION_LINK
AO_A415DF_AONON_WORKING_DAYS
AO_A415DF_AOPERMISSION
AO_A415DF_AOPERSON
AO_A415DF_AOPLAN
AO_A415DF_AOPLAN_CONFIGURATION
AO_A415DF_AOPRESENCE
AO_A415DF_AORELEASE
AO_A415DF_AOREPLANNING
AO_A415DF_AORESOURCE
AO_A415DF_AOSKILL
AO_A415DF_AOSOLUTION_STORE
AO_A415DF_AOSPRINT
AO_A415DF_AOSTAGE
AO_A415DF_AOSTREAM
AO_A415DF_AOSTREAM_TO_TEAM
AO_A415DF_AOTEAM
AO_A415DF_AOTHEME
AO_A415DF_AOWORK_ITEM
AO_A415DF_AOWORK_ITEM_TO_RES
AO_A44657_HEALTH_CHECK_ENTITY
AO_B9A0F0_APPLIED_TEMPLATE
AO_C16815_ALERT_AO
AO_CFF990_AOTRANSITION_FAILURE
AO_D9132D_ASSIGNMENT
AO_D9132D_ASSIGNMENT_EXT
AO_D9132D_CONFIGURATION
AO_D9132D_DEP_ISSUE_LINK_TYPES
AO_D9132D_DISTRIBUTION
AO_D9132D_EXCLUDED_VERSIONS
AO_D9132D_GENERICREPORT
AO_D9132D_HIERARCHY_CONFIG
AO_D9132D_INIT
AO_D9132D_ISSUE_SOURCE
AO_D9132D_NONWORKINGDAYS
AO_D9132D_PERMISSIONS
AO_D9132D_PLAN
AO_D9132D_PLAN_CUSTOM_FIELD
AO_D9132D_PLANSKILL
AO_D9132D_PLANTEAM
AO_D9132D_PLANTHEME
AO_D9132D_PLANVERSION
AO_D9132D_PROGRAM
AO_D9132D_PROGRAM_CUSTOM_FIELD
AO_D9132D_RANK_ITEM
AO_D9132D_SCEN_CUSTOM_FIELD
AO_D9132D_SCEN_TEAM_EX_SPRINT
AO_D9132D_SCENARIO
AO_D9132D_SCENARIO_ABILITY
AO_D9132D_SCENARIO_AVLBLTY
AO_D9132D_SCENARIO_CHANGES
AO_D9132D_SCENARIO_ISSUE_CMPNT
AO_D9132D_SCENARIO_ISSUE_LABEL
AO_D9132D_SCENARIO_ISSUE_LINKS
AO_D9132D_SCENARIO_ISSUE_RES
AO_D9132D_SCENARIO_ISSUES
AO_D9132D_SCENARIO_PERSON
AO_D9132D_SCENARIO_RESOURCE
AO_D9132D_SCENARIO_SKILL
AO_D9132D_SCENARIO_STAGE
AO_D9132D_SCENARIO_TEAM
AO_D9132D_SCENARIO_THEME
AO_D9132D_SCENARIO_VERSION
AO_D9132D_SCENARIO_XPVERSION
AO_D9132D_SHARED_REPORT
AO_D9132D_SOLUTION
AO_D9132D_STAGE
AO_D9132D_TEAM_EX_SPRINT
AO_D9132D_THEME
AO_D9132D_VERSION_ENRICHMENT
AO_D9132D_X_PROJECT_VERSION
AO_E8B6CC_BRANCH
AO_E8B6CC_BRANCH_HEAD_MAPPING
AO_E8B6CC_CHANGESET_MAPPING
AO_E8B6CC_COMMIT
AO_E8B6CC_GIT_HUB_EVENT
AO_E8B6CC_ISSUE_MAPPING
AO_E8B6CC_ISSUE_MAPPING_V2
AO_E8B6CC_ISSUE_TO_BRANCH
AO_E8B6CC_ISSUE_TO_CHANGESET
AO_E8B6CC_MESSAGE
AO_E8B6CC_MESSAGE_QUEUE_ITEM
AO_E8B6CC_MESSAGE_TAG
AO_E8B6CC_ORG_TO_PROJECT
AO_E8B6CC_ORGANIZATION_MAPPING
AO_E8B6CC_PR_ISSUE_KEY
AO_E8B6CC_PR_PARTICIPANT
AO_E8B6CC_PR_TO_COMMIT
AO_E8B6CC_PROJECT_MAPPING
AO_E8B6CC_PROJECT_MAPPING_V2
AO_E8B6CC_PULL_REQUEST
AO_E8B6CC_REPO_TO_CHANGESET
AO_E8B6CC_REPO_TO_PROJECT
AO_E8B6CC_REPOSITORY_MAPPING
AO_E8B6CC_SYNC_AUDIT_LOG
AO_E8B6CC_SYNC_EVENT
AO_ED669C_SEEN_ASSERTIONS
AO_F1B27B_HISTORY_RECORD
AO_F1B27B_KEY_COMP_HISTORY
AO_F1B27B_KEY_COMPONENT
AO_F1B27B_PROMISE
AO_F1B27B_PROMISE_HISTORY
app_user
audit_changed_value
audit_item
audit_log
avatar
board
boardproject
changegroup
changeitem
clusteredjob
clusterlockstatus
clustermessage
clusternode
clusternodeheartbeat
clusterupgradestate
columnlayout
columnlayoutitem
component
configurationcontext
customfield
customfieldoption
customfieldvalue
cwd_application
cwd_application_address
cwd_directory
cwd_directory_attribute
cwd_directory_operation
cwd_group
cwd_group_attributes
cwd_membership
cwd_user
cwd_user_attributes
deadletter
draftworkflowscheme
draftworkflowschemeentity
entity_property
entity_property_index_document
entity_translation
external_entities
externalgadget
favouriteassociations
feature
fieldconfigscheme
fieldconfigschemeissuetype
fieldconfiguration
fieldlayout
fieldlayoutitem
fieldlayoutscheme
fieldlayoutschemeassociation
fieldlayoutschemeentity
fieldscreen
fieldscreenlayoutitem
fieldscreenscheme
fieldscreenschemeitem
fieldscreentab
fileattachment
filtersubscription
gadgetuserpreference
genericconfiguration
globalpermissionentry
groupbase
issue_field_option
issue_field_option_scope
issuelink
issuelinktype
issuesecurityscheme
issuestatus
issuetype
issuetypescreenscheme
issuetypescreenschemeentity
jiraaction
jiradraftworkflows
jiraeventtype
jiraissue
jiraperms
jiraworkflows
jiraworkflowstatuses
jquartz_blob_triggers
jquartz_calendars
jquartz_cron_triggers
jquartz_fired_triggers
jquartz_job_details
jquartz_job_listeners
jquartz_locks
jquartz_paused_trigger_grps
jquartz_scheduler_state
jquartz_simple_triggers
jquartz_simprop_triggers
jquartz_trigger_listeners
jquartz_triggers
label
licenserolesdefault
licenserolesgroup
listenerconfig
mailserver
managedconfigurationitem
membershipbase
moved_issue_key
nodeassociation
nodeindexcounter
notification
notificationinstance
notificationscheme
oauthconsumer
oauthconsumertoken
oauthspconsumer
oauthsptoken
optionconfiguration
os_currentstep
os_currentstep_prev
os_historystep
os_historystep_prev
os_wfentry
permissionscheme
permissionschemeattribute
pluginstate
pluginversion
portalpage
portletconfiguration
priority
productlicense
project
project_key
projectcategory
projectchangedtime
projectrole
projectroleactor
projectversion
propertydata
propertydate
propertydecimal
propertyentry
propertynumber
propertystring
propertytext
qrtz_calendars
qrtz_cron_triggers
qrtz_fired_triggers
qrtz_job_details
qrtz_job_listeners
qrtz_simple_triggers
qrtz_trigger_listeners
qrtz_triggers
reindex_component
reindex_request
remembermetoken
remotelink
replicatedindexoperation
resolution
rundetails
schemeissuesecurities
schemeissuesecuritylevels
schemepermissions
searchrequest
sequence_value_item
serviceconfig
sharepermissions
tempattachmentsmonitor
trackback_ping
trustedapp
upgradehistory
upgradetaskhistory
upgradetaskhistoryauditlog
upgradeversionhistory
userassociation
userbase
userhistoryitem
userpickerfilter
userpickerfiltergroup
userpickerfilterrole
versioncontrol
votehistory
workflowscheme
workflowschemeentity
worklog

[Andreas Pluto]

Okay,
it seems to be a little more than 18 tables.
Actually it looks like the whole database - that reduces the fun :-(

[Nic Brough -Adaptavist-]

Exactly why I recommend not doing it.  You need to understand everything before you can be sure your SQL is going to give you a good answer.

With a REST call to the API, you can get any part of, or all, of an issue in a single question, so I'd recommend using that.

[Radek Dostál]

Do you mean data mining from the database?

Issues are stored in 'jiraissue' table which contains system field data. Custom field values are stored in 'customfieldvalue', custom fields in 'customfield'.

In any case you should not be needing to involve the database for issue-related data as all of that can be picked up from the tool in a better and safer way.

DB Schema can be found here: https://developer.atlassian.com/server/jira/platform/database-schema/

[Andreas Pluto]

In principle, that is correct.
The problem with my task is the attached documents.
Collecting them and attaching them to the project is not the problem. Since documents are often named without meaning, it is difficult to judge their content without reading them. A more detailed description from the user stories allows more conclusions than the short entries in the jiraissue.
I know the scheme - but since not all of them are directly related (and I can't seem to find the description of the tables anywhere) it is wtas tedious to explore.