Publications DB

We have 3 individual tables to record Posts, Comments and Mirrors.

Posts

Table: posts

name	type	notes
id	Int	unique
profile_id	String
created_at	DateTime
is_related_to_post	String?
is_related_to_comment	String?
language	String? (2)	ISO 639-1 (Alpha-2)
region	String? (2)	ISO 3166-1 alpha-2
content_warning	PublicationContentWarning?
primary_content_type	PublicationContentType
publication_context	PublicationContext?
content_uri	String?	Decentralised storage location of post content metadata
content_s3_uri	String?	Centralised storage location of post content metadata
metadata_uri	String?	Decentralised storage location of post's NFT collection's metadata
metadata_s3_uri	String?	Centralised storage location of post's NFT collection's metadaata
contract_publication_id	String? (66)
post_id	String?	Concatenation of profile_id and contract_publication_id. This field is unique across all 3 tables
contract_address	String? (48)
collect_nft_address	String? (48)
is_dispatched	Boolean
reference_implementation	String? (48)
reference_return_data	String? (1000)
is_metadata_processed	Boolean
has_error	Boolean
metadata_error_reason	String?
created_block_hash	DateTime	Transaction hash of Post NFT item mint
block_timestamp	DateTime	Time of Post NFT item mint
metadata_version	String
content	String?

Comments

Table: comments

name	type	notes
id	Int	unique
profile_id	String
created_at	DateTime
is_related_to_post	String?	foreign key: post_id of Post commented on
language	String? (2)	ISO 639-1 (Alpha-2)
region	String? (2)	ISO 3166-1 alpha-2
content_warning	PublicationContentWarning?
primary_content_type	PublicationContentType
publication_context	PublicationContext?
content_uri	String?	Decentralised storage location of post content metadata
content_s3_uri	String?	Centralised storage location of post content metadata
metadata_uri	String?	Decentralised storage location of post's NFT collection's metadata
metadata_s3_uri	String?	Centralised storage location of post's NFT collection's metadaata
contract_publication_id	String? (66)
comment_id	String?	Concatenation of profile_id and contract_publication_id. This field is unique across all 3 tables
contract_address	String? (48)
collect_nft_address	String? (48)
is_dispatched	Boolean
reference_implementation	String? (48)
reference_return_data	String? (1000)
is_metadata_processed	Boolean
has_error	Boolean
metadata_error_reason	String?
created_block_hash	DateTime	Transaction hash of Post NFT item mint
block_timestamp	DateTime	Time of Post NFT item mint
metadata_version	String
content	String?

Mirrors

Table: mirrors

name	type	notes
id	Int	unique
profile_id	String
created_at	DateTime
is_related_to_post	String?	foreign key: post_id of Post mirrored
language	String? (2)	ISO 639-1 (Alpha-2)
region	String? (2)	ISO 3166-1 alpha-2
content_warning	PublicationContentWarning?
primary_content_type	PublicationContentType
publication_context	PublicationContext?
content_uri	String?	Decentralised storage location of post content metadata
content_s3_uri	String?	Centralised storage location of post content metadata
metadata_uri	String?	Decentralised storage location of post's NFT collection's metadata
metadata_s3_uri	String?	Centralised storage location of post's NFT collection's metadaata
contract_publication_id	String? (66)
mirror_id	String?	Concatenation of profile_id and contract_publication_id. This field is unique across all 3 tables
contract_address	String? (48)
collect_nft_address	String? (48)
is_dispatched	Boolean
reference_implementation	String? (48)
reference_return_data	String? (1000)
is_metadata_processed	Boolean
has_error	Boolean
metadata_error_reason	String?
created_block_hash	DateTime	Transaction hash of Post NFT item mint
block_timestamp	DateTime	Time of Post NFT item mint
metadata_version	String
content	String?

Enum Reference

enum PublicationContentWarning {
  NSFW
  SENSITIVE
  SPOILER
}

enum PublicationContentType {
  VIDEO
  IMAGE
  ARTICLE
  TEXT_ONLY
  AUDIO
  LINK
  EMBED
}

enum PublicationContext {
  ACTIVITY_AND_ACHIEVEMENT
  STATUS_UPDATE
}