Skip to contents

Data Access Patterns

Source: vignettes/data-access-patterns.Rmd
data-access-patterns.Rmd

This vignette demonstrates real-world data access workflows using artutils. Rather than documenting individual functions, it shows how they combine to solve actual problems in the platform.

Understanding the Data Model

Before diving into patterns, it helps to understand how data is structured:

Artist (artist_index)
├── Collections (collection_index)
│   └── Artworks (artwork_index)
│       ├── Stats (artwork_stats)
│       ├── Meta (artwork_meta)
│       ├── Profile (artwork_profiles)
│       ├── Colors (artwork_colors)
│       ├── Frames (artwork_frame_analytics)
│       └── Benchmark (artwork_benchmark)
├── Stats (artist_stats)
├── OpenSea (artist_opensea)
├── Style Map (artist_style_map)
└── Preferences (settings.artist_preferences)

Most queries start from an artist UUID and navigate down this hierarchy.

Pattern 1: URL Routing to Artist Profile

Web applications typically receive a URL slug and need to render a full profile page. This pattern shows the complete workflow from slug to rendered data.

library(artutils)

render_artist_profile <- function(slug) {
  # Reuse connection for all queries - reduces latency
  cn <- artcore::..dbc()
  on.exit(artcore::..dbd(cn))

  # Step 1: Resolve slug to artist record
  # Returns NULL if artist doesn't exist - handle 404 at app layer

  artist <- get_artist_by_slug(slug, cn = cn)
  if (is.null(artist)) {
    return(list(error = "not_found", slug = slug))
  }

  artist_uuid <- artist$artist_uuid

  # Step 2: Fetch parallel data for profile header
  # These queries are independent - could parallelize in production
  stats <- get_artist_stats(artist_uuid, cn = cn)
  collections <- get_artist_collections_summary(artist_uuid, cn = cn)
  recent_works <- get_artist_recent_works(artist_uuid, limit = 9, cn = cn)
  style_map <- getArtistStyleMap(artist_uuid, cn = cn)

  # Step 3: Enrich recent works with thumbnail paths
  # Path functions are pure - no database calls
  if (nrow(recent_works) > 0) {
    recent_works[, thumb_url := pathArtworkThumb(artist_uuid, art_uuid), by = art_uuid]
    recent_works[, main_url := pathArtMainImage(artist_uuid, art_uuid), by = art_uuid]
  }

  # Step 4: Build style distribution for profile
  style_counts <- style_map[, .N, by = category]
  data.table::setorder(style_counts, -N)

  list(
    artist = list(
      uuid = artist_uuid,
      name = artist$artist_name,
      slug = artist$slug,
      bio = artist$bio,
      location = paste(artist$city, artist$state, sep = ", "),
      avatar = pathArtistThumb(artist_uuid),
      member_since = stats$member_since,
      social = list(
        instagram = artist$url_ig,
        site = artist$url_site,
        tiktok = artist$url_tiktok
      )
    ),
    stats = list(
      artworks = stats$artworks,
      collections = stats$collections,
      primary_style = style_counts$category[1]
    ),
    collections = collections,
    recent_works = recent_works,
    styles = style_counts
  )
}

# Usage
profile <- render_artist_profile("jane-doe")
if (is.null(profile$error)) {
  cat("Artist:", profile$artist$name, "\n")
  cat("Primary style:", profile$stats$primary_style, "\n")
  cat("Recent works:", nrow(profile$recent_works), "\n")
}

Key Points

  1. Connection reuse: Opening a connection has ~50ms overhead. Reuse across queries.
  2. Early exit on 404: Check slug resolution before making additional queries.
  3. Path functions are pure: They construct URLs without database access.
  4. data.table by-reference: The [, := ...] syntax modifies in place.

Pattern 2: Artwork Detail Page with Full Context

The artwork detail view requires comprehensive data aggregation. The getAppdata() function handles this, but understanding what it returns enables better UI integration.

render_artwork_detail <- function(artist_uuid, artwork_uuid) {
  # getAppdata() opens its own connection and aggregates everything
  appdata <- getAppdata(artist_uuid, artwork_uuid)

  # The returned structure has three main sections:

  # 1. ARTIST CONTEXT - for header/sidebar
  artist_section <- list(
    name = appdata$artist$info$artist_name,
    avatar = appdata$artist$thumb,
    total_artworks = appdata$artist$stats$tot_artworks,
    total_hours = appdata$artist$stats$tot_hours,
    social = appdata$artist$urls,
    # Collections for navigation dropdown
    collections = appdata$artist$collections
  )

  # 2. ARTWORK DATA - the main content
  artwork_section <- list(
    title = appdata$artwork$info$basic$art_title,
    name = appdata$artwork$info$basic$art_name,
    category = appdata$artwork$info$basic$category,
    description = appdata$artwork$info$profile$description,
    # Performance metrics
    brush_strokes = appdata$artwork$stats$brush_strokes,
    drawing_hours = appdata$artwork$stats$drawing_hours,
    unique_colors = appdata$artwork$stats$n_unique_colors,
    average_bpm = appdata$artwork$stats$ave_bpm,
    # Image dimensions
    width = appdata$artwork$info$meta$image_width,
    height = appdata$artwork$info$meta$image_height,
    # Benchmark scores (percentile vs artist's other work)
    benchmarks = list(
      time_effort = appdata$artwork$benchmarks$time_effort,
      skill_artistry = appdata$artwork$benchmarks$skill_artistry,
      complex_detail = appdata$artwork$benchmarks$complex_detail
    )
  )

  # 3. MEDIA PATHS - for replay player, gallery, certificate
  media_section <- list(
    # Main artwork image
    main_image = appdata$paths$main_img,
    # Frame sequence for replay player
    frame_prefix = appdata$paths$frames,
    frame_count = appdata$config$n_frames,
    # Gallery variants (different crops/styles)
    variant_count = appdata$config$n_variants,
    carousel_count = appdata$config$n_carousel,
    # Analytics graphs
    graph_prefix = appdata$paths$graphs,
    # Signature detection result
    signature = appdata$paths$signature
  )

  # 4. CERTIFICATE DATA - if issued
  certificate_section <- if (!is.null(appdata$certificate$cert_id)) {
    list(
      cert_id = appdata$certificate$cert_id,
      pdf_path = appdata$certificate$path_pdf,
      jpeg_path = appdata$certificate$path_jpeg
    )
  } else {
    NULL
  }

  list(
    artist = artist_section,
    artwork = artwork_section,
    media = media_section,
    certificate = certificate_section
  )
}

Accessing Frame-Level Analytics

For advanced visualizations (progress charts, technique analysis), access frame data directly:

analyze_creative_process <- function(artist_uuid, artwork_uuid) {
  frames <- getFrameAnalytics(artist_uuid, artwork_uuid)

  if (nrow(frames) == 0) {
    return(list(error = "no_frame_data"))
  }


  # Calculate per-frame deltas from cumulative values
  frames[, hours_delta := c(elapsed_hours[1], diff(elapsed_hours))]
  frames[, strokes_delta := c(cumulative_strokes[1], diff(cumulative_strokes))]

  # Technique phase distribution
  # Phases: "sketch", "base", "detail", "refine", "final"
  phase_time <- frames[, .(
    hours = sum(hours_delta, na.rm = TRUE),
    strokes = sum(strokes_delta, na.rm = TRUE)
  ), by = technique_phase]

  # Color evolution over time
  color_evolution <- frames[, .(
    frame,
    elapsed_hours,
    unique_colors,
    color_diversity,
    cumulative_strokes
  )]

  # Work intensity patterns
  # High BPM = fast, confident strokes

  # Low BPM = careful, deliberate work
  intensity_pattern <- frames[, .(
    avg_bpm = mean(estimated_bpm, na.rm = TRUE),
    max_bpm = max(estimated_bpm, na.rm = TRUE),
    min_bpm = min(estimated_bpm, na.rm = TRUE)
  ), by = technique_phase]

  # Detect "flow states" - sustained high productivity
  # Flow = BPM above artist's average for 10+ consecutive frames
  frames[, bpm_above_avg := estimated_bpm > mean(estimated_bpm, na.rm = TRUE)]
  frames[, flow_run := data.table::rleid(bpm_above_avg)]
  flow_states <- frames[bpm_above_avg == TRUE, .(
    duration_frames = .N,
    start_frame = min(frame),
    end_frame = max(frame)
  ), by = flow_run][duration_frames >= 10]

  list(
    phase_distribution = phase_time,
    color_evolution = color_evolution,
    intensity = intensity_pattern,
    flow_states = flow_states,
    summary = list(
      total_frames = nrow(frames),
      total_hours = max(frames$elapsed_hours, na.rm = TRUE),
      dominant_phase = phase_time[which.max(hours)]$technique_phase,
      flow_percentage = nrow(frames[bpm_above_avg == TRUE]) / nrow(frames) * 100
    )
  )
}

Collections may have visibility settings (visible, hidden, or inherited from artist preferences). This pattern shows how to build a gallery that respects these settings.

build_collection_gallery <- function(artist_uuid, include_hidden = FALSE) {
  cn <- artcore::..dbc()
  on.exit(artcore::..dbd(cn))

  # Get all collections for artist
  collections <- get_artist_collections_summary(artist_uuid, cn = cn)

  if (nrow(collections) == 0) {
    return(list(collections = list(), total = 0))
  }

  # Get visibility status for all collections in one query
  # This is more efficient than checking each collection individually
  visibility <- get_collect_visibility(
    artists = artist_uuid,
    collects = collections$collection_uuid,
    cn = cn
  )

  # Merge visibility into collections
  collections <- merge(
    collections,
    visibility,
    by = "collection_uuid",
    all.x = TRUE
  )

  # Default to visible if no visibility record (shouldn't happen with addCollection fix)
  collections[is.na(is_visible), is_visible := TRUE]

  # Filter based on caller's permissions
  if (!include_hidden) {
    collections <- collections[is_visible == TRUE]
  }

  # Enrich with thumbnail paths
  # Use first artwork as collection thumbnail
  collections[, thumb_url := {
    if (artwork_count > 0) {
      first_art <- get_artist_recent_works(artist_uuid, limit = 1, cn = cn)
      if (nrow(first_art) > 0) {
        pathArtworkThumb(artist_uuid, first_art$art_uuid[1])
      } else {
        NA_character_
      }
    } else {
      NA_character_
    }
  }, by = collection_uuid]

  list(
    collections = collections,
    total = nrow(collections),
    visible_count = sum(collections$is_visible),
    hidden_count = sum(!collections$is_visible)
  )
}

Single Collection Visibility Check

For routing decisions (should this URL 404 or render?), use the single-item check:

can_view_collection <- function(collection_uuid, artist_uuid, viewer_is_owner = FALSE) {
  # Owner can always view their own collections

  if (viewer_is_owner) return(TRUE)

  cn <- artcore::..dbc()
  on.exit(artcore::..dbd(cn))

  is_collection_visible(collection_uuid, artist_uuid, cn = cn)
}

# In router middleware
route_collection <- function(artist_slug, collection_uuid, session) {
  artist <- get_artist_by_slug(artist_slug)
  if (is.null(artist)) return(render_404())

  is_owner <- identical(session$user_id, artist$artist_uuid)

  if (!can_view_collection(collection_uuid, artist$artist_uuid, is_owner)) {
    return(render_404())  # or render_403() depending on UX preference
  }

  render_collection_page(artist$artist_uuid, collection_uuid)
}

Pattern 4: Building Search Results

Search results need lightweight data for grid display, with efficient pagination.

search_artworks <- function(artist_uuid = NULL, category = NULL,
                            style_tag = NULL, limit = 20, offset = 0) {
  cn <- artcore::..dbc()
  on.exit(artcore::..dbd(cn))

  # Build dynamic WHERE clause
  conditions <- c("1=1")  # Always true base

  if (!is.null(artist_uuid)) {
    artcore::validate_uuid(artist_uuid, "artist UUID")
    conditions <- c(conditions,
      paste0("ai.artist_uuid = ", DBI::dbQuoteLiteral(cn, artist_uuid)))
  }

  if (!is.null(category)) {
    conditions <- c(conditions,
      paste0("ap.category = ", DBI::dbQuoteLiteral(cn, category)))
  }

  if (!is.null(style_tag)) {
    conditions <- c(conditions,
      paste0("ast.tag = ", DBI::dbQuoteLiteral(cn, style_tag)))
  }

  where_clause <- paste(conditions, collapse = " AND ")

  # Query with JOINs for filtering
  qry <- paste0("
    SELECT DISTINCT
      ai.art_uuid, ai.artist_uuid, ai.art_name, ai.art_title,
      ai.collection_uuid, ai.created_utc,
      idx.artist_name,
      s.brush_strokes, s.drawing_hours
    FROM app.artwork_index ai
    JOIN app.artist_index idx ON ai.artist_uuid = idx.artist_uuid
    LEFT JOIN app.artwork_profiles ap ON ai.art_uuid = ap.art_uuid
    LEFT JOIN app.artwork_styles ast ON ai.art_uuid = ast.art_uuid
    LEFT JOIN app.artwork_stats s ON ai.art_uuid = s.art_uuid
    WHERE ", where_clause, "
    ORDER BY ai.created_utc DESC
    LIMIT ", limit, " OFFSET ", offset
  )

  results <- dbArtGet(qry, cn)

  if (nrow(results) == 0) {
    return(list(items = list(), total = 0, has_more = FALSE))
  }

  # Enrich with paths (no additional queries needed)
  results[, thumb_url := pathArtworkThumb(artist_uuid, art_uuid), by = art_uuid]

  # Check if there are more results
  count_qry <- paste0("
    SELECT COUNT(DISTINCT ai.art_uuid) as total
    FROM app.artwork_index ai
    LEFT JOIN app.artwork_profiles ap ON ai.art_uuid = ap.art_uuid
    LEFT JOIN app.artwork_styles ast ON ai.art_uuid = ast.art_uuid
    WHERE ", where_clause
  )
  total <- dbArtGet(count_qry, cn)$total[1]

  list(
    items = results,
    total = total,
    has_more = (offset + nrow(results)) < total,
    page = floor(offset / limit) + 1,
    per_page = limit
  )
}

Pattern 5: OpenSea Integration Check

For NFT-related features, check OpenSea account and artwork status:

check_nft_status <- function(artist_uuid, artwork_uuid = NULL) {
  cn <- artcore::..dbc()
  on.exit(artcore::..dbd(cn))

  # Get artist OpenSea account info
  opensea <- getArtistOpensea(artist_uuid, cn = cn)

  artist_has_account <- !is.null(opensea$wallet_address) &&
                        nchar(opensea$wallet_address) > 0

  result <- list(
    artist_has_opensea = artist_has_account,
    wallet_address = opensea$wallet_address,
    opensea_url = opensea$url_os
  )

  # If checking specific artwork
  if (!is.null(artwork_uuid)) {
    is_nft <- artHasNFT(artist_uuid, artwork_uuid, cn = cn)
    result$artwork_is_nft <- is_nft

    if (is_nft) {
      # Get NFT-specific metadata if minted
      nft_qry <- stringr::str_glue(
        "SELECT url_os, contract_address, token_id
         FROM app.artwork_opensea
         WHERE artist_uuid = '{artist_uuid}' AND art_uuid = '{artwork_uuid}'"
      )
      nft_data <- dbArtGet(nft_qry, cn)
      if (nrow(nft_data) > 0) {
        result$nft_url <- nft_data$url_os[1]
        result$contract <- nft_data$contract_address[1]
        result$token_id <- nft_data$token_id[1]
      }
    }
  }

  result
}

Database Connection Best Practices

Pattern: Auto-managed (Simple Queries)

For single queries, let functions manage their own connections:

# Function opens and closes connection internally
stats <- get_artist_stats(artist_uuid)
# No cleanup needed

Pattern: Shared Connection (Batch Operations)

For multiple queries, share a connection to reduce overhead:

cn <- artcore::..dbc()
on.exit(artcore::..dbd(cn))  # Ensures cleanup even on error

# Multiple queries reuse the same connection
info <- get_artist_by_slug("jane-doe", cn = cn)
stats <- get_artist_stats(info$artist_uuid, cn = cn)
collections <- get_artist_collections_summary(info$artist_uuid, cn = cn)
recent <- get_artist_recent_works(info$artist_uuid, limit = 6, cn = cn)
# Connection closed when function exits

Pattern: Custom Queries

For queries not covered by helper functions, use dbArtGet() directly:

# Simple SELECT
top_artists <- dbArtGet("
  SELECT artist_uuid, artist_name, total_artworks
  FROM app.artist_stats s
  JOIN app.artist_index i USING (artist_uuid)
  ORDER BY total_artworks DESC
  LIMIT 10
")

# Single row as list (for config lookups)
slug <- "jane-doe"
artist <- dbArtGet(
  stringr::str_glue("SELECT * FROM app.artist_index WHERE slug = '{slug}'"),
  unlist = TRUE  # Returns list instead of data.table
)
artist$artist_name  # Direct field access

Error Handling Philosophy

artutils functions follow a fail-fast philosophy. Missing data indicates either:

  1. Invalid input (bad UUID) - caller should validate
  2. Pipeline failure - data should exist but doesn’t
  3. Race condition - record deleted between queries

Handle errors at the application layer:

safe_get_artwork <- function(artist_uuid, artwork_uuid) {
  tryCatch({
    appdata <- getAppdata(artist_uuid, artwork_uuid)
    list(success = TRUE, data = appdata)
  }, error = function(e) {
    # Log for debugging
    stringr::str_c(
      "getAppdata failed", artist_uuid, artwork_uuid, e$message, 
      collapse = "|"
    ) |> rdstools::log_err()
    # Return structured error for UI
    list(success = FALSE, error = "artwork_not_found", message = e$message)
  })
}

# In Shiny app
observeEvent(input$artwork_selected, {
  result <- safe_get_artwork(rv$artist_uuid, input$artwork_selected)
  if (result$success) {
    rv$appdata <- result$data
    showTab("artwork_detail")
  } else {
    showNotification("Artwork not found", type = "error")
  }
})

Performance Considerations

  1. Connection pooling: In production Shiny apps, use pool package instead of direct connections.

  2. Caching: getAppdata() results are stable - cache at session level.

  3. Lazy loading: Don’t fetch frame analytics until user views replay player.

  4. Path functions are free: pathArtworkThumb() etc. are pure string operations.

  5. Batch visibility checks: Use get_collect_visibility() for multiple collections instead of looping is_collection_visible().