Workspace Pool

workspace.{table}

SELECT id, name, connector_slug, connection_status, has_ever_synced
FROM workspace.providers
WHERE disabled_at IS NULL
  AND connection_status IS DISTINCT FROM 'disabled'
ORDER BY connector_slug, name

SELECT
  provider.connector_slug,
  provider.name,
  filter.parent_tap,
  filter.field,
  filter.value
FROM workspace.providers AS provider
JOIN workspace.parent_data_filters AS filter
  ON filter.tenant_connector_id = provider.id
WHERE provider.disabled_at IS NULL
  AND provider.connection_status IS DISTINCT FROM 'disabled'
ORDER BY provider.connector_slug, filter.parent_tap, filter.value

SELECT p.connector_slug, s.name AS stream, s.kind, s.enabled, s.enabled_since
FROM workspace.provider_connections AS s
JOIN workspace.providers AS p ON p.id = s.provider_id
WHERE p.disabled_at IS NULL
ORDER BY p.connector_slug, s.name

SELECT
  child.name,
  parent.name AS parent_name
FROM workspace.roles AS child
LEFT JOIN workspace.roles AS parent
  ON parent.id = child.parent_role_id
ORDER BY child.name

SELECT
  r.name AS role,
  rp.permission
FROM workspace.roles AS r
JOIN workspace.role_permissions AS rp
  ON rp.role_id = r.id
WHERE rp.permission = 'tenant'
  OR starts_with(rp.permission, 'tenant.')
ORDER BY r.name, rp.permission

SELECT DISTINCT r.name AS role
FROM workspace.roles AS r
JOIN workspace.role_permissions AS rp
  ON rp.role_id = r.id
WHERE rp.permission = 'tenant.users.manage'
  OR starts_with('tenant.users.manage', rp.permission || '.')
ORDER BY r.name

SELECT
  u.email,
  u.status,
  r.name AS role,
  ur.assigned_at
FROM workspace.user_roles AS ur
JOIN workspace.users AS u
  ON u.id = ur.user_id
JOIN workspace.roles AS r
  ON r.id = ur.role_id
ORDER BY u.email, r.name

SELECT
  r.name AS role,
  count(*) AS members
FROM workspace.roles AS r
JOIN workspace.user_roles AS ur
  ON ur.role_id = r.id
GROUP BY r.name
ORDER BY r.name

SELECT pane_key, actor_kind, value, revision
FROM workspace.preferences
WHERE handle = 'dateRange'

WITH items AS (
  SELECT unnest(json_get_array(value, 'items')) AS item
  FROM workspace.preferences
  WHERE handle = 'meetings' AND actor_kind = 'workspace'
),
nodes AS (
  SELECT json_get_str(item, 'id') AS id, json_get_str(item, 'parent') AS parent,
         json_get_str(item, 'name') AS name, json_get_str(item, 'definition') AS definition
  FROM items
),
leaves AS (
  SELECT id, coalesce(name || ': ' || definition, name) AS meaning
  FROM nodes
  WHERE id NOT IN (SELECT parent FROM nodes WHERE parent IS NOT NULL)
),
criteria AS (
  SELECT map(array_agg(id ORDER BY id), array_agg(meaning ORDER BY id)) AS criteria
  FROM leaves
),
prompt AS (
  SELECT value ->> 'text' AS text
  FROM workspace.preferences
  WHERE handle = 'prompt' AND actor_kind = 'workspace'
)
SELECT m.meeting_key,
       choice(m.context, instructions => p.text, criteria => c.criteria) AS answer
FROM parables."teamtimespend".meeting_context m
CROSS JOIN criteria c CROSS JOIN prompt p

SELECT
  member.email,
  member.name,
  directory.orgunitpath,
  directory.isadmin,
  directory.suspended
FROM workspace.users AS member
LEFT JOIN providers.google.users AS directory
  ON directory.primaryemail = member.email