Skip to content

Commit 949fc56

Browse files
huumnhuumn
authored
No Trust November (#2675)
* most but not all of the boost related changes * generated ranking columns * trust only lasts a year and require lower confidence * denormalize downzaps and add to item details * rewards only go to zaps * add compound indices for territories and main sorts * no meme bounty * add generated column for top boost * squash migrations * remove boost position cuz it's nasty anyway * adapt jobs ranking * update faq for notrust * ease editing of half-life and make it 12h * allow rankhot generated column to be migrated more easily * add generated outlawed column * remove defunct call to auctionPosition * fix link rel rules * fix ancestor downzap denormalization in migration * increase half life and fix old boosts
1 parent 59d9919 commit 949fc56

File tree

26 files changed

+684
-470
lines changed

26 files changed

+684
-470
lines changed

‎api/payIn/types/boost.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -63,7 +63,7 @@ export async function onPaid (tx, payInId) {
6363
await tx.$executeRaw`
6464
INSERT INTO pgboss.job (name, data, retrylimit, retrybackoff, startafter, keepuntil)
6565
VALUES ('expireBoost', jsonb_build_object('id', ${payIn.itemPayIn.itemId}::INTEGER), 21, true,
66-
now() + interval '30 days', now() + interval '40 days')`
66+
now() + interval '7 days', now() + interval '10 days')`
6767
}
6868

6969
export async function describe (models, payInId) {

‎api/payIn/types/downZap.js‎

Lines changed: 16 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -80,12 +80,24 @@ export async function onPaid (tx, payInId) {
8080
ON CONFLICT ("itemId", "userId") DO UPDATE
8181
SET "downZapSats" = "ItemUserAgg"."downZapSats" + ${sats}::INTEGER, updated_at = now()
8282
RETURNING LOG("downZapSats" / GREATEST("downZapSats" - ${sats}::INTEGER, 1)::FLOAT) AS log_sats
83+
), item_downzapped AS (
84+
UPDATE "Item"
85+
SET "weightedDownVotes" = "weightedDownVotes" + zapper."zapTrust" * zap.log_sats,
86+
"subWeightedDownVotes" = "subWeightedDownVotes" + zapper."subZapTrust" * zap.log_sats,
87+
"downMsats" = "downMsats" + ${msats}::BIGINT
88+
FROM zap, zapper
89+
WHERE "Item".id = ${item.id}::INTEGER
90+
RETURNING "Item".*
8391
)
8492
UPDATE "Item"
85-
SET "weightedDownVotes" = "weightedDownVotes" + zapper."zapTrust" * zap.log_sats,
86-
"subWeightedDownVotes" = "subWeightedDownVotes" + zapper."subZapTrust" * zap.log_sats
87-
FROM zap, zapper
88-
WHERE "Item".id = ${item.id}::INTEGER`
93+
SET "commentDownMsats" = "commentDownMsats" + ${msats}::BIGINT
94+
FROM (
95+
SELECT "Item".id
96+
FROM "Item", item_downzapped
97+
WHERE "Item".path @> item_downzapped.path AND "Item".id <> item_downzapped.id
98+
ORDER BY "Item".id
99+
) AS ancestors
100+
WHERE "Item".id = ancestors.id`
89101
}
90102

91103
export async function describe (models, payInId) {

‎api/resolvers/item.js‎

Lines changed: 32 additions & 109 deletions
Original file line numberDiff line numberDiff line change
@@ -4,11 +4,10 @@ import { getMetadata, metadataRuleSets } from 'page-metadata-parser'
44
import { ruleSet as publicationDateRuleSet } from '@/lib/timedate-scraper'
55
import domino from 'domino'
66
import {
7-
ITEM_SPAM_INTERVAL, ITEM_FILTER_THRESHOLD,
7+
ITEM_SPAM_INTERVAL,
88
COMMENT_DEPTH_LIMIT, COMMENT_TYPE_QUERY,
99
USER_ID, POLL_COST, ADMIN_ITEMS, GLOBAL_SEED,
1010
NOFOLLOW_LIMIT, UNKNOWN_LINK_REL, SN_ADMIN_IDS,
11-
BOOST_MULT,
1211
ITEM_EDIT_SECONDS,
1312
COMMENTS_LIMIT,
1413
COMMENTS_OF_COMMENT_LIMIT,
@@ -35,22 +34,19 @@ function commentsOrderByClause (me, models, sort) {
3534
const sharedSortsArray = []
3635
sharedSortsArray.push('("Item"."pinId" IS NOT NULL) DESC')
3736
sharedSortsArray.push('("Item"."deletedAt" IS NULL) DESC')
38-
// outlawed items should be at the bottom
39-
sharedSortsArray.push(`NOT ("Item"."weightedVotes" - "Item"."weightedDownVotes" <= -${ITEM_FILTER_THRESHOLD} OR "Item".outlawed) DESC`)
4037
const sharedSorts = sharedSortsArray.join(', ')
4138

4239
if (sort === 'recent') {
4340
return `ORDER BY ${sharedSorts},
44-
("Item".cost > 0 OR "Item"."weightedVotes" - "Item"."weightedDownVotes" > 0) DESC,
41+
("Item".genoutlawed = FALSE) DESC,
4542
"Item".created_at DESC, "Item".id DESC`
4643
}
4744

4845
if (sort === 'hot') {
4946
return `ORDER BY ${sharedSorts},
50-
"hotScore" DESC NULLS LAST,
51-
"Item".msats DESC, "Item".id DESC`
47+
"rankhot" DESC, "Item".id DESC`
5248
} else {
53-
return `ORDER BY ${sharedSorts}, "Item"."weightedVotes" - "Item"."weightedDownVotes" DESC NULLS LAST, "Item".msats DESC, "Item".id DESC`
49+
return `ORDER BY ${sharedSorts}, "ranktop" DESC, "Item".id DESC`
5450
}
5551
}
5652

@@ -138,7 +134,7 @@ export async function getAd (parent, { sub, subArr = [], showNsfw = false }, { m
138134
activeOrMine(),
139135
subClause(sub, 1, 'Item', me, showNsfw),
140136
muteClause(me))}
141-
ORDER BY boost desc, "Item".created_at ASC
137+
ORDER BY rankboost DESC, "Item".created_at ASC
142138
LIMIT 1`
143139
}, ...subArr))?.[0] || null
144140
}
@@ -150,20 +146,16 @@ const orderByClause = (by, me, models, type, sub) => {
150146
case 'sats':
151147
return 'ORDER BY "Item".msats DESC'
152148
case 'zaprank':
153-
return topOrderByWeightedSats(me, models, sub)
149+
return 'ORDER BY "Item".ranktop DESC, "Item".id DESC'
154150
case 'boost':
155-
return 'ORDER BY "Item".boost DESC'
151+
return 'ORDER BY "Item".boost + "Item"."oldBoost" DESC'
156152
case 'random':
157153
return 'ORDER BY RANDOM()'
158154
default:
159155
return `ORDER BY ${type === 'bookmarks' ? '"bookmarkCreatedAt"' : '"Item".created_at'} DESC`
160156
}
161157
}
162158

163-
export function joinHotScoreView (me, models) {
164-
return ' JOIN hot_score_view g ON g.id = "Item".id '
165-
}
166-
167159
// this grabs all the stuff we need to display the item list and only
168160
// hits the db once ... orderBy needs to be duplicated on the outer query because
169161
// joining does not preserve the order of the inner query
@@ -190,7 +182,8 @@ export async function itemQueryWithMeta ({ me, models, query, orderBy = '' }, ..
190182
SELECT "Item".*, to_jsonb(users.*) || jsonb_build_object('meMute', "Mute"."mutedId" IS NOT NULL) as user,
191183
COALESCE("MeItemPayIn"."meMsats", 0) as "meMsats", COALESCE("MeItemPayIn"."mePendingMsats", 0) as "mePendingMsats",
192184
COALESCE("MeItemPayIn"."meMcredits", 0) as "meMcredits", COALESCE("MeItemPayIn"."mePendingMcredits", 0) as "mePendingMcredits",
193-
COALESCE("MeItemPayIn"."meDontLikeMsats", 0) as "meDontLikeMsats", COALESCE("MeItemPayIn"."mePendingBoostMsats", 0) as "mePendingBoostMsats",
185+
COALESCE("MeItemPayIn"."meDontLikeMsats", 0) as "meDontLikeMsats", COALESCE("MeItemPayIn"."mePendingDontLikeMsats", 0) as "mePendingDontLikeMsats",
186+
COALESCE("MeItemPayIn"."mePendingBoostMsats", 0) as "mePendingBoostMsats",
194187
b."itemId" IS NOT NULL AS "meBookmark", "ThreadSubscription"."itemId" IS NOT NULL AS "meSubscription",
195188
"ItemForward"."itemId" IS NOT NULL AS "meForward", to_jsonb("Sub".*) || jsonb_build_object('meMuteSub', "MuteSub"."userId" IS NOT NULL)
196189
|| jsonb_build_object('meSubscription', "SubSubscription"."userId" IS NOT NULL) as sub,
@@ -215,6 +208,7 @@ export async function itemQueryWithMeta ({ me, models, query, orderBy = '' }, ..
215208
sum("PayIn".mcost) FILTER (WHERE "PayIn"."payInState" <> 'PAID' AND "PayOutBolt11".id IS NOT NULL AND "PayIn"."payInType" = 'ZAP') AS "mePendingMsats",
216209
sum("PayIn".mcost) FILTER (WHERE "PayIn"."payInState" <> 'PAID' AND "PayOutBolt11".id IS NULL AND "PayIn"."payInType" = 'ZAP') AS "mePendingMcredits",
217210
sum("PayIn".mcost) FILTER (WHERE "PayIn"."payInType" = 'DOWN_ZAP') AS "meDontLikeMsats",
211+
sum("PayIn".mcost) FILTER (WHERE "PayIn"."payInType" = 'DOWN_ZAP' AND "PayIn"."payInState" <> 'PAID') AS "mePendingDontLikeMsats",
218212
sum("PayIn".mcost) FILTER (WHERE "PayIn"."payInState" <> 'PAID' AND "PayIn"."payInType" = 'BOOST') AS "mePendingBoostMsats"
219213
FROM "ItemPayIn"
220214
JOIN "PayIn" ON "PayIn".id = "ItemPayIn"."payInId"
@@ -362,7 +356,7 @@ export async function filterClause (me, models, type) {
362356

363357
// handle outlawed
364358
// if the item is above the threshold or is mine
365-
const outlawClauses = [`"Item"."weightedVotes" - "Item"."weightedDownVotes" > -${ITEM_FILTER_THRESHOLD} AND NOT "Item".outlawed`]
359+
const outlawClauses = ['"Item".genoutlawed = FALSE']
366360
if (me) {
367361
outlawClauses.push(`"Item"."userId" = ${me.id}`)
368362
}
@@ -390,7 +384,7 @@ function typeClause (type) {
390384
case 'freebies':
391385
return '"Item".cost = 0'
392386
case 'outlawed':
393-
return `"Item"."weightedVotes" - "Item"."weightedDownVotes" <= -${ITEM_FILTER_THRESHOLD} OR "Item".outlawed`
387+
return '"Item".genoutlawed = TRUE'
394388
case 'borderland':
395389
return '"Item"."weightedVotes" - "Item"."weightedDownVotes" < 0'
396390
case 'all':
@@ -561,7 +555,7 @@ export default {
561555
${SELECT},
562556
(boost IS NOT NULL AND boost > 0)::INT AS group_rank,
563557
CASE WHEN boost IS NOT NULL AND boost > 0
564-
THEN rank() OVER (ORDER BY boost DESC, "Item".created_at ASC)
558+
THEN rank() OVER (ORDER BY rankboost DESC, "Item".created_at ASC)
565559
ELSE rank() OVER (ORDER BY "Item".created_at DESC) END AS rank
566560
FROM "Item"
567561
${payInJoinFilter(me)}
@@ -581,7 +575,7 @@ export default {
581575
break
582576
default:
583577
if (decodedCursor.offset === 0) {
584-
// get pins for the page and return those separately
578+
// get pins for the page and return those separately
585579
pins = await itemQueryWithMeta({
586580
me,
587581
models,
@@ -613,10 +607,9 @@ export default {
613607
me,
614608
models,
615609
query: `
616-
${SELECT}, g.hot_score AS "hotScore", g.sub_hot_score AS "subHotScore"
610+
${SELECT}
617611
FROM "Item"
618612
LEFT JOIN "Sub" ON "Sub"."name" = "Item"."subName"
619-
${joinHotScoreView(me, models)}
620613
${payInJoinFilter(me)}
621614
${whereClause(
622615
// in home (sub undefined), filter out global pinned items since we inject them later
@@ -630,10 +623,10 @@ export default {
630623
await filterClause(me, models, type),
631624
subClause(sub, 3, 'Item', me, showNsfw),
632625
muteClause(me))}
633-
ORDER BY ${sub ? '"subHotScore"' : '"hotScore"'} DESC, "Item".msats DESC, "Item".id DESC
626+
ORDER BY rankhot DESC, "Item".id DESC
634627
OFFSET $1
635628
LIMIT $2`,
636-
orderBy: `ORDER BY ${sub ? '"subHotScore"' : '"hotScore"'} DESC, "Item".msats DESC, "Item".id DESC`
629+
orderBy: 'ORDER BY rankhot DESC, "Item".id DESC'
637630
}, decodedCursor.offset, limit, ...subArr)
638631
break
639632
}
@@ -722,79 +715,6 @@ export default {
722715
LIMIT 3`
723716
}, similar)
724717
},
725-
auctionPosition: async (parent, { id, sub, boost }, { models, me }) => {
726-
const createdAt = id ? (await getItem(parent, { id }, { models, me })).createdAt : new Date()
727-
let where
728-
if (boost > 0) {
729-
// if there's boost
730-
// has a larger boost than ours, or has an equal boost and is older
731-
// count items: (boost > ours.boost OR (boost = ours.boost AND create_at < ours.created_at))
732-
where = {
733-
OR: [
734-
{ boost: { gt: boost } },
735-
{ boost, createdAt: { lt: createdAt } }
736-
]
737-
}
738-
} else {
739-
// else
740-
// it's an active with a bid gt ours, or its newer than ours and not STOPPED
741-
// count items: ((bid > ours.bid AND status = 'ACTIVE') OR (created_at > ours.created_at AND status <> 'STOPPED'))
742-
where = {
743-
OR: [
744-
{ boost: { gt: 0 } },
745-
{ createdAt: { gt: createdAt } }
746-
]
747-
}
748-
}
749-
750-
where.AND = {
751-
subName: sub,
752-
status: 'ACTIVE',
753-
deletedAt: null
754-
}
755-
if (id) {
756-
where.AND.id = { not: Number(id) }
757-
}
758-
759-
return await models.item.count({ where }) + 1
760-
},
761-
boostPosition: async (parent, { id, sub, boost = 0 }, { models, me }) => {
762-
const where = {
763-
boost: { gte: boost },
764-
status: 'ACTIVE',
765-
deletedAt: null,
766-
outlawed: false,
767-
parentId: null
768-
}
769-
if (id) {
770-
where.id = { not: Number(id) }
771-
}
772-
773-
const homeAgg = await models.item.aggregate({
774-
_count: { id: true },
775-
_max: { boost: true },
776-
where
777-
})
778-
779-
let subAgg
780-
if (sub) {
781-
subAgg = await models.item.aggregate({
782-
_count: { id: true },
783-
_max: { boost: true },
784-
where: {
785-
...where,
786-
subName: sub
787-
}
788-
})
789-
}
790-
791-
return {
792-
home: homeAgg._count.id === 0 && boost >= BOOST_MULT,
793-
sub: subAgg?._count.id === 0 && boost >= BOOST_MULT,
794-
homeMaxBoost: homeAgg._max.boost || 0,
795-
subMaxBoost: subAgg?._max.boost || 0
796-
}
797-
},
798718
newComments: async (parent, { itemId, after }, { models, me }) => {
799719
const comments = await itemQueryWithMeta({
800720
me,
@@ -1179,11 +1099,20 @@ export default {
11791099
}
11801100
return msatsToSats(BigInt(item.msats) + BigInt(item.mePendingMsats || 0) + BigInt(item.mePendingMcredits || 0))
11811101
},
1102+
downSats: async (item, args, { models, me }) => {
1103+
if (me?.id === item.userId) {
1104+
return msatsToSats(BigInt(item.downMsats))
1105+
}
1106+
return msatsToSats(BigInt(item.downMsats) + BigInt(item.mePendingDontLikeMsats || 0))
1107+
},
1108+
commentDownSats: async (item, args, { models }) => {
1109+
return msatsToSats(item.commentDownMsats)
1110+
},
11821111
boost: async (item, args, { models, me }) => {
11831112
if (me?.id !== item.userId) {
1184-
return item.boost
1113+
return item.boost + item.oldBoost
11851114
}
1186-
return item.boost + msatsToSats(BigInt(item.mePendingBoostMsats || 0))
1115+
return (item.boost + item.oldBoost) + msatsToSats(BigInt(item.mePendingBoostMsats || 0))
11871116
},
11881117
credits: async (item, args, { models, me }) => {
11891118
if (me?.id === item.userId) {
@@ -1436,12 +1365,13 @@ export default {
14361365
if (me && Number(item.userId) === Number(me.id)) {
14371366
return false
14381367
}
1439-
return item.outlawed || item.weightedVotes - item.weightedDownVotes <= -ITEM_FILTER_THRESHOLD
1368+
return item.genoutlawed
14401369
},
14411370
rel: async (item, args, { me, models }) => {
14421371
const sats = item.msats ? msatsToSats(item.msats) : 0
1443-
const boost = item.boost ?? 0
1444-
return (sats + boost < NOFOLLOW_LIMIT) ? UNKNOWN_LINK_REL : 'noopener noreferrer'
1372+
const boost = (item.boost ?? 0) + (item.oldBoost ?? 0)
1373+
const cost = (item.cost ?? 0)
1374+
return (sats + boost + cost < NOFOLLOW_LIMIT || item.genoutlawed) ? UNKNOWN_LINK_REL : 'noopener noreferrer'
14451375
},
14461376
mine: async (item, args, { me, models }) => {
14471377
return me?.id === item.userId
@@ -1667,10 +1597,3 @@ export const getForwardUsers = async (models, forward) => {
16671597
export const SELECT =
16681598
`SELECT "Item".*, "Item".created_at as "createdAt", "Item".updated_at as "updatedAt",
16691599
ltree2text("Item"."path") AS "path"`
1670-
1671-
function topOrderByWeightedSats (me, models, sub) {
1672-
if (sub) {
1673-
return 'ORDER BY "Item"."subWeightedVotes" - "Item"."subWeightedDownVotes" DESC, "Item".msats DESC, "Item".id DESC'
1674-
}
1675-
return 'ORDER BY "Item"."weightedVotes" - "Item"."weightedDownVotes" DESC, "Item".msats DESC, "Item".id DESC'
1676-
}

‎api/typeDefs/item.js‎

Lines changed: 2 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -9,19 +9,10 @@ export default gql`
99
dupes(url: String!): [Item!]
1010
related(cursor: String, title: String, id: ID, minMatch: String, limit: Limit! = ${LIMIT}): Items
1111
search(q: String, sub: String, cursor: String, what: String, sort: String, when: String, from: String, to: String): Items
12-
auctionPosition(sub: String, id: ID, boost: Int): Int!
13-
boostPosition(sub: String, id: ID, boost: Int): BoostPositions!
1412
itemRepetition(parentId: ID): Int!
1513
newComments(itemId: ID, after: Date): Comments!
1614
}
1715
18-
type BoostPositions {
19-
home: Boolean!
20-
sub: Boolean!
21-
homeMaxBoost: Int!
22-
subMaxBoost: Int!
23-
}
24-
2516
type TitleUnshorted {
2617
title: String
2718
unshorted: String
@@ -119,9 +110,11 @@ export default gql`
119110
bountyPaidTo: [Int]
120111
noteId: String
121112
sats: Int!
113+
downSats: Int!
122114
credits: Int!
123115
commentSats: Int!
124116
commentCredits: Int!
117+
commentDownSats: Int!
125118
lastCommentAt: Date
126119
upvotes: Int!
127120
meSats: Int!

0 commit comments

Comments
 (0)