Something went wrong. Try again.
(READ ONLY) Margin is an open annotation layer for the internet. Powered by the AT Protocol.
margin.at
extension web atproto comments
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142-- name: UpsertPublication :execINSERT INTO publications (uri, author_did, url, name, description, show_in_discover, indexed_at)VALUES ($1, $2, $3, $4, $5, $6, $7)ON CONFLICT(uri) DO UPDATE SET name = EXCLUDED.name, description = EXCLUDED.description, show_in_discover = EXCLUDED.show_in_discover, indexed_at = EXCLUDED.indexed_at;
-- name: DeletePublication :execDELETE FROM publications WHERE uri = $1;
-- name: GetPublicationByURL :oneSELECT uri, author_did, url, name, description, show_in_discover, indexed_atFROM publications WHERE url = $1;
-- name: UpsertDocument :execINSERT INTO documents (uri, author_did, site, path, title, description, text_content, tags_json, canonical_url, published_at, indexed_at)VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)ON CONFLICT(uri) DO UPDATE SET title = EXCLUDED.title, description = EXCLUDED.description, text_content = EXCLUDED.text_content, tags_json = EXCLUDED.tags_json, canonical_url = EXCLUDED.canonical_url, indexed_at = EXCLUDED.indexed_at;
-- name: DeleteDocument :execDELETE FROM documents WHERE uri = $1;
-- name: GetDocumentByCanonicalURL :oneSELECT uri, author_did, site, path, title, description, text_content, tags_json, canonical_url, published_at, indexed_atFROM documents WHERE canonical_url = $1;
-- name: GetDocumentByURI :oneSELECT uri, author_did, site, path, title, description, text_content, tags_json, canonical_url, published_at, indexed_atFROM documents WHERE uri = $1;
-- name: GetDocumentsWithoutEmbeddings :manySELECT d.uri, d.author_did, d.site, d.path, d.title, d.description, d.text_content, d.tags_json, d.canonical_url, d.published_at, d.indexed_atFROM documents dLEFT JOIN document_embeddings de ON d.uri = de.document_uriWHERE de.document_uri IS NULLORDER BY d.indexed_at DESCLIMIT $1;
-- name: GetAnnotationsWithoutEmbeddings :manySELECT a.uri, a.author_did, a.motivation, a.body_value, a.body_format, a.body_uri, a.target_source, a.target_hash, a.target_title, a.selector_json, a.tags_json, a.created_at, a.indexed_at, a.cidFROM annotations aLEFT JOIN annotation_embeddings ae ON a.uri = ae.annotation_uriWHERE ae.annotation_uri IS NULL AND a.motivation IN ('commenting', 'highlighting')ORDER BY a.created_at DESCLIMIT $1;
-- name: GetHighlightsWithoutEmbeddings :manySELECT h.uri, h.author_did, h.target_source, h.target_title, h.selector_json, h.tags_jsonFROM highlights hLEFT JOIN annotation_embeddings ae ON h.uri = ae.annotation_uriWHERE ae.annotation_uri IS NULLORDER BY h.created_at DESCLIMIT $1;
-- name: GetDistinctAnnotationAuthors :manySELECT DISTINCT author_did FROM annotation_embeddings;
-- name: GetRecentDocuments :manySELECT uri, author_did, site, path, title, description, text_content, tags_json, canonical_url, published_at, indexed_atFROM documentsORDER BY published_at DESCLIMIT $1 OFFSET $2;
-- name: GetPopularDocuments :manySELECT d.uri, d.author_did, d.site, d.path, d.title, d.description, d.text_content, d.tags_json, d.canonical_url, d.published_at, d.indexed_atFROM documents dLEFT JOIN annotations a ON a.target_source = d.canonical_urlGROUP BY d.uriORDER BY COUNT(a.uri) DESC, d.published_at DESCLIMIT $1 OFFSET $2;
-- name: GetDocumentCount :oneSELECT COUNT(*) FROM documents;
-- name: UpsertDocumentEmbedding :execINSERT INTO document_embeddings (document_uri, embedding, updated_at) VALUES ($1, $2, $3)ON CONFLICT(document_uri) DO UPDATE SET embedding = EXCLUDED.embedding, updated_at = EXCLUDED.updated_at;
-- name: UpsertAnnotationEmbedding :execINSERT INTO annotation_embeddings (annotation_uri, author_did, document_uri, embedding, updated_at) VALUES ($1, $2, $3, $4, $5)ON CONFLICT(annotation_uri) DO UPDATE SET embedding = EXCLUDED.embedding, document_uri = EXCLUDED.document_uri, updated_at = EXCLUDED.updated_at;
-- name: DeleteAnnotationEmbedding :execDELETE FROM annotation_embeddings WHERE annotation_uri = $1;
-- name: UpsertUserProfile :execINSERT INTO user_profiles (author_did, embedding, tag_affinities, annotation_count, updated_at) VALUES ($1, $2, $3, $4, $5)ON CONFLICT(author_did) DO UPDATE SET embedding = EXCLUDED.embedding, tag_affinities = EXCLUDED.tag_affinities, annotation_count = EXCLUDED.annotation_count, updated_at = EXCLUDED.updated_at;
-- name: GetUserProfile :oneSELECT author_did, embedding, tag_affinities, annotation_count, updated_at FROM user_profiles WHERE author_did = $1;
-- name: GetAnnotationEmbeddingsByAuthor :manySELECT annotation_uri, author_did, document_uri, embedding, updated_at FROM annotation_embeddings WHERE author_did = $1;
-- name: GetRecentAnnotationEmbeddingsByAuthor :manySELECT annotation_uri, author_did, document_uri, embedding, updated_at FROM annotation_embeddings WHERE author_did = $1 ORDER BY updated_at DESC LIMIT $2;
-- name: GetCandidateDocuments :manySELECT d.uri, d.author_did, d.site, d.path, d.title, d.description, d.tags_json, d.canonical_url, d.published_at, de.embedding, COALESCE(eng.cnt, 0) AS engagementFROM documents dJOIN document_embeddings de ON d.uri = de.document_uriLEFT JOIN ( SELECT document_uri, COUNT(DISTINCT author_did) AS cnt FROM annotation_embeddings WHERE document_uri IS NOT NULL GROUP BY document_uri) eng ON eng.document_uri = d.uriLEFT JOIN publications p ON d.site = p.uri OR d.site = p.urlWHERE d.author_did != $1 AND (p.show_in_discover IS NULL OR p.show_in_discover = true) AND LENGTH(d.title) > 15 AND (LENGTH(COALESCE(d.description, '')) >= 30 OR LENGTH(COALESCE(d.text_content, '')) >= 100) AND LOWER(d.title) NOT LIKE '%test%' AND LOWER(d.title) NOT LIKE '%testing%' AND LOWER(d.title) NOT LIKE '%hello world%' AND LOWER(d.title) NOT LIKE '%untitled%' AND LOWER(d.title) NOT LIKE '%draft%' AND LOWER(d.title) NOT LIKE '%asdf%' AND LOWER(d.title) NOT LIKE '%lorem%' AND LOWER(d.title) NOT LIKE '%placeholder%' AND d.uri NOT IN ( SELECT DISTINCT ae2.document_uri FROM annotation_embeddings ae2 WHERE ae2.author_did = $2 AND ae2.document_uri IS NOT NULL )ORDER BY d.published_at DESCLIMIT $3;
-- name: MatchAnnotationToDocument :oneSELECT uri FROM documents WHERE canonical_url = $1;