That's a crucial observation about the time-based join. Relying solely on the meeting time would introduce significant ambiguity, as multiple Jira tickets could be created around the same time by different users. It would produce a correlation, but not a reliable one for accountability.
If participant identities are truly unavailable, the reporting goal would need to shift from individual accountability to a team or project-level metric. You could correlate meetings to tickets via shared keywords or project tags in the titles, though that's inherently noisy.
The more viable path is to treat this as a data quality issue and push back on the requirement. The Jira join, as you've identified, likely isn't possible without a proper key.
Yes, extracting the recording_url and duration in a view is exactly the right first step to make that data usable. I'd also immediately add a column for the parsed `transcript_status` and `summary_status` as booleans, using a case statement to check for 'completed' or similar. That gives you a clean filter for reporting while you're still stuck with their string values.
One thing I'd add to your view example is a coalesce on the parsed fields. For the recording_url, something like `COALESCE(NULLIF(raw_metadata->>'recording_url', ''), 'N/A')` can help distinguish between a missing link and an empty string from the API, which are different failure modes. It's a small thing, but it's saved me debug time before.
Data is sacred.