L. Mansfield

Data catalog

Every table behind this site.

What each table is built from, who owns it, how fresh it is and what every column means, plus the metrics defined on them. It’s generated from the same definitions the nightly run tests against, so it can’t say something the tests don’t.

Tables

16 tables, each queryable in any SQL source
projectsOne row per project card on /projects; Home's featured cards are the featured rows.
Owner
Logan Mansfield
Lives in
data/console/site.sql
Freshness
Rebuilt every run; expected within 36 hours of the site pages
Shown on
Home: the featured projects; Projects: the featured projects; Projects: the more builds
  • slugVARCHARThe card's anchor id on /projects (#fraud, #owl-park...).
  • titleVARCHARProject name as the card shows it.
  • badgeVARCHARStatus badge on the card (Live, Running, Retired...), or null.
  • eyebrowVARCHARThe small line above the title: kind of project.
  • yearINTEGERYear shown on the card.
  • featuredBOOLEANTrue for the three featured projects shown on Home too.
  • statusVARCHARactive, or archived for retired cards.
  • summaryVARCHARThe card's synopsis.
  • tagsVARCHAR[]The card's tags, in order.
  • linksSTRUCT("label" VARCHAR, url VARCHAR)[]The card's links as (label, url) pairs.
  • sort_orderINTEGERPosition on /projects.

Tested for: at least 3 rows; no blanks in slug, title, eyebrow, year, summary; slug is unique; title is unique; status is one of active, archived; exactly three featured projects; every column is described. Latest results.

experienceOne row per role on the Home experience timeline.
Owner
Logan Mansfield
Lives in
data/console/site.sql
Freshness
Rebuilt every run; expected within 36 hours of the site pages
Shown on
Home: the experience timeline
  • roleVARCHARJob title.
  • noteVARCHARA short aside after the title (where a role started), or null.
  • orgVARCHAREmployer.
  • locationVARCHARWhere the role was based.
  • startedDATEFirst month in the role.
  • endedDATEMonth the role ended; null while it's current.
  • is_currentBOOLEANTrue for the role held now.
  • summaryVARCHAROne-line description of the work.
  • sort_orderINTEGERPosition on the timeline.

Tested for: at least 1 row; no blanks in role, org, started, summary; a role ends after it starts; exactly one current role; every column is described. Latest results.

skillsThe Home toolkit, one row per skill.
Owner
Logan Mansfield
Lives in
data/console/site.sql
Freshness
Rebuilt every run; expected within 36 hours of the site pages
Shown on
Home: the toolkit
  • skillVARCHARThe skill as its chip reads.
  • categoryVARCHARThe toolkit group it sits in.
  • sort_orderINTEGERPosition in the toolkit.

Tested for: at least 3 rows; no blanks in skill, category; skill is unique; category is one of Data stack, Forecasting & ML, Platforms I run at ZooTampa; every column is described. Latest results.

credentialsCertifications and workshops listed on Home.
Owner
Logan Mansfield
Lives in
data/console/site.sql
Freshness
Rebuilt every run; expected within 36 hours of the site pages
Shown on
Home: the credentials
  • credentialVARCHARName of the certification or workshop.
  • detailVARCHARExam code, issuer or kind.
  • sort_orderINTEGERPosition in the list.

Tested for: at least 1 row; no blanks in credential; credential is unique; every column is described. Latest results.

resumeOne row: who I am, the Home "now" strip, and where to reach me.
Owner
Logan Mansfield
Lives in
data/console/site.sql
Freshness
Rebuilt every run; expected within 36 hours of the site pages
Shown on
Home: the now strip
  • nameVARCHARFull name.
  • headlineVARCHARThe hero's first clause.
  • nowVARCHARThe "Now" row of the strip.
  • buildingVARCHARThe "Building" row of the strip.
  • latestVARCHARThe "Latest" row of the strip.
  • locationVARCHARHome base, from the footer.
  • emailVARCHARContact email from the Email button.
  • linkedinVARCHARLinkedIn profile URL.
  • githubVARCHARGitHub profile URL.
  • resume_pdfVARCHARRésumé PDF URL.
  • siteVARCHARThis site's URL.

Tested for: exactly 1 row; no blanks in name, headline, now, building, latest, email; every column is described. Latest results.

fraud_totalsOne row: the fraud pipeline's last 30 days in total.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → totals
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the last 30 days totals
  • checkoutsINTEGERCheckouts scored.
  • flaggedINTEGERCheckouts sent to review or declined.
  • declinedINTEGERCheckouts declined.
  • reviewedINTEGERCheckouts sent to manual review.
  • fraudINTEGERCheckouts the answer key marks as fraud.
  • fraud_caughtINTEGERFraudulent checkouts that were flagged.
  • false_declinesINTEGERLegitimate checkouts that were declined.
  • legitINTEGERCheckouts the answer key marks as legitimate.
  • fraud_dollars_stoppedDOUBLEDollars of fraud that were flagged.
  • fraud_dollars_approvedDOUBLEDollars of fraud that got through.
  • p50_scoring_msDOUBLEMedian time to score a checkout, in milliseconds.
  • p95_scoring_msDOUBLE95th-percentile scoring time, in milliseconds.
  • p95_checkout_msDOUBLE95th-percentile time for the whole checkout, in milliseconds.
  • scoring_timeoutsINTEGERCheckouts approved because scoring timed out (fail open).
  • active_daysINTEGERDays in the window with at least one checkout.

Tested for: exactly 1 row; no blanks in checkouts, fraud, fraud_caught, legit; fraud plus legit equals checkouts; flagged equals reviewed plus declined; caught fraud is no more than all fraud; updated within 36 hours; every column is described. Latest results.

fraud_dailyOne row per America/New_York day with checkouts in the last 30 days.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → daily
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the fraud per day chart
  • dayDATECalendar day, America/New_York.
  • checkoutsINTEGERCheckouts scored that day.
  • approvedINTEGERCheckouts approved.
  • reviewedINTEGERCheckouts sent to manual review.
  • declinedINTEGERCheckouts declined.
  • fraudINTEGERCheckouts the answer key marks as fraud.
  • fraud_caughtINTEGERFraudulent checkouts that were flagged.
  • false_declinesINTEGERLegitimate checkouts that were declined.
  • fraud_dollars_stoppedDOUBLEDollars of fraud that were flagged.
  • fraud_dollars_approvedDOUBLEDollars of fraud that got through.
  • p95_scoring_msDOUBLE95th-percentile scoring time that day, in milliseconds.

Tested for: at least 1 row; no blanks in day, checkouts, fraud, fraud_caught; day is unique; approved, reviewed and declined add up to checkouts; the days add up to the 30-day totals; updated within 36 hours; every column is described. Latest results.

fraud_score_binsThe model's score histogram: checkouts per 0.05-wide score bin, split by the answer key.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → score_bins
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the score distribution chart
  • score_fromDOUBLEBottom of the bin (inclusive).
  • score_toDOUBLETop of the bin.
  • legitINTEGERLegitimate checkouts scored in the bin.
  • fraudINTEGERFraudulent checkouts scored in the bin.

Tested for: 1 to 20 rows; score_from is unique; bins sit between 0 and 1; the bins add up to the 30-day totals; updated within 36 hours; every column is described. Latest results.

fraud_patternsFraud and the share caught, by the generator's fraud pattern.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → patterns
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the catch rate by fraud pattern table
  • patternVARCHARThe kind of fraud the generator played out.
  • fraudINTEGERFraudulent checkouts of that pattern.
  • caughtINTEGEROf those, how many were flagged.

Tested for: at least 1 row; pattern is unique; pattern is one of card_testing, stolen_card_resale, gift_card_cashout, account_takeover, fraud_ring; the patterns add up to all fraud; updated within 36 hours; every column is described. Latest results.

fraud_compareThe rules and the new model side by side on the same checkouts: one row per scorer.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → compare
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the new model vs rules table
  • scorerVARCHARrules or model.
  • checkoutsINTEGERCheckouts both scorers saw.
  • fraudINTEGEROf those, how many the answer key marks as fraud.
  • legitINTEGEROf those, how many are legitimate.
  • flaggedINTEGERCheckouts this scorer flagged (sent to review or declined).
  • caughtINTEGERFraudulent checkouts this scorer flagged.
  • cutoffDOUBLEThe lowest score this scorer flagged at: the rules' risk score, or the model's fraud probability.
  • p50_msDOUBLEMedian scoring time, in milliseconds.
  • p95_msDOUBLE95th-percentile scoring time, in milliseconds.

Tested for: exactly 2 rows; scorer is unique; scorer is one of rules, model; no blanks in scorer, checkouts, fraud, legit, flagged, caught; fraud plus legit equals checkouts; caught fraud is no more than flagged or all fraud; both scorers saw the same checkouts; updated within 36 hours; every column is described. Latest results.

fraud_compare_patternsFraud caught by each scorer, by the generator's fraud pattern, on the checkouts both scorers saw.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → compare_patterns
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the new model vs rules by fraud pattern chart
  • patternVARCHARThe kind of fraud the generator played out.
  • scorerVARCHARrules or model.
  • fraudINTEGERFraudulent checkouts of that pattern.
  • caughtINTEGEROf those, how many this scorer flagged.

Tested for: at least 2 rows; pattern and scorer are unique together; scorer is one of rules, model; pattern is one of card_testing, stolen_card_resale, gift_card_cashout, account_takeover, fraud_ring; caught fraud is no more than all fraud; the patterns add up to each scorer's catches; updated within 36 hours; every column is described. Latest results.

fraud_compare_sweepWhat each scorer would flag at a few other cutoffs, on the checkouts both scorers saw.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → compare_sweep
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
Shown on
Live data: the other cutoffs table
  • scorerVARCHARrules or model.
  • cutoffDOUBLEFlag every checkout scored at or above this.
  • flaggedINTEGERCheckouts that cutoff would flag.
  • caughtINTEGERFraudulent checkouts that cutoff would flag.

Tested for: at least 2 rows; scorer and cutoff are unique together; scorer is one of rules, model; cutoffs sit between 0 and 1; caught fraud is no more than flagged; updated within 36 hours; every column is described. Latest results.

fraud_compare_day_oneThe rules and the new model over the model's first 24 hours live, a fixed window (orders placed from Nov 14, 22:27:41 UTC, to 24 hours later), counting only rows loaded and model scores recorded before Nov 15, 22:57:41 UTC (the window end plus 30 minutes). The site's copy quotes these counts, so both the order window and the load/score cutoff are fixed; claims in metrics.json check the copy against them.
Owner
Logan Mansfield
Lives in
data/fraud-snapshot.json → compare_day_one
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
  • scorerVARCHARrules or model.
  • window_startVARCHARStart of the window (UTC, inclusive).
  • window_endVARCHAREnd of the window (UTC, exclusive).
  • checkoutsINTEGERCheckouts in the window both scorers saw.
  • fraudINTEGEROf those, how many the answer key marks as fraud.
  • legitINTEGEROf those, how many are legitimate.
  • flaggedINTEGERCheckouts this scorer flagged.
  • caughtINTEGERFraudulent checkouts this scorer flagged.

Tested for: exactly 2 rows; scorer is unique; scorer is one of rules, model; no blanks in scorer, window_start, window_end, checkouts, fraud, legit, flagged, caught; fraud plus legit equals checkouts; caught fraud is no more than flagged or all fraud; the window is the fixed first 24 hours; updated within 36 hours; every column is described. Latest results.

fraud_metricsOne row per site metric over fraud_totals: the definitions every page reads, with today's value. Each metric is also a macro, like catch_rate(fraud_caught, fraud).
Owner
Logan Mansfield
Lives in
data/pipeline/metrics.sql, from data/pipeline/metrics.json
Freshness
Rebuilt every run; expected within 36 hours of the fraud snapshot
  • metricVARCHARThe metric's name, as metrics.json and its macro call it.
  • labelVARCHARWhat the site calls it.
  • definitionVARCHARNumerator over denominator, in fraud_totals columns.
  • numeratorDOUBLEThe numerator's value.
  • denominatorDOUBLEThe denominator's value.
  • valueDOUBLENumerator divided by denominator (blank when there's nothing to divide by).

Tested for: metric is unique; no blanks in metric, label, definition, value; every metric is a share between 0 and 1; catch rate and fraud approved add up to 1; updated within 36 hours; every column is described. Latest results.

github_activityPublic GitHub activity per day and repository over the last 30 days, as far back as the 300 most recent public events the API serves.
Owner
Logan Mansfield
Lives in
data/pipeline/github.json → activity
Freshness
Rebuilt every run; expected within 30 hours of the GitHub pull
  • dayDATECalendar day, America/New_York.
  • repoVARCHARPublic repository (owner/name).
  • eventsINTEGERPublic events that day (pushes, pull requests, reviews...).
  • pushesINTEGEROf those, pushes.

Tested for: any number of rows; no blanks in day, repo, events; day and repo are unique together; pushes are no more than events; updated within 30 hours; every column is described. Latest results.

github_reposPublic repositories on github.com/lmansf (forks left out).
Owner
Logan Mansfield
Lives in
data/pipeline/github.json → repos
Freshness
Rebuilt every run; expected within 30 hours of the GitHub pull
  • repoVARCHARRepository (owner/name).
  • languageVARCHARGitHub's main language for it, or null.
  • starsINTEGERStargazers.
  • pushed_onDATEDay of the last push, UTC.
  • archivedBOOLEANTrue if the repository is archived.

Tested for: at least 1 row; no blanks in repo, pushed_on; repo is unique; updated within 30 hours; every column is described. Latest results.

Metrics

each is also a SQL macro

Sources and staging