Mastodon のDBのテーブル構成

Mastodon（v1.5.1）で使われているPostgreSQL（v.9.2.18）のテーブルとフィールド（カラム）名をリストアップしました。

自分のインスタンス内の登録ユーザーやトゥートを、同サーバー内に設置されている WordPress などの PHP アプリと直接連携させるのに調べたのですが、他に需要があるかわかりませんが、共有いたします。（※SSH 経由で接続して確認しました）

DBの接続

• DB User: postgres
• DB Password: なし（マストドンのインストールユーザーでのアクセスの場合）
• DB名: mastodon

account_domain_blocks

COLUMN NAME	TYPE
id	integer
account_id	integer
domain	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone

accounts

COLUMN NAME	TYPE
id	integer
username	character varying
domain	character varying
secret	character varying
private_key	text
public_key	text
remote_url	character varying
salmon_url	character varying
hub_url	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone
note	text
display_name	character varying
uri	character varying
url	character varying
avatar_file_name	character varying
avatar_content_type	character varying
avatar_file_size	integer
avatar_updated_at	timestamp without time zone
avatar_remote_url	character varying
header_file_name	character varying
header_content_type	character varying
header_file_size	integer
header_updated_at	timestamp without time zone
header_remote_url	character varying
subscription_expires_at	timestamp without time zone
silenced	boolean
suspended	boolean
locked	boolean
statuses_count	integer
followers_count	integer
following_count	integer
last_webfingered_at	timestamp without time zone
inbox_url	character varying
outbox_url	character varying
shared_inbox_url	character varying
followers_url	character varying
protocol	integer

ar_internal_metadata

COLUMN NAME	TYPE
key	character varying
value	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone

blocks

COLUMN NAME	TYPE
id	integer
account_id	integer
target_account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone

conversation_mutes

COLUMN NAME	TYPE
id	integer
account_id	integer
conversation_id	bigint

conversations

COLUMN NAME	TYPE
id	integer
uri	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone

domain_blocks

COLUMN NAME	TYPE
id	integer
domain	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone
severity	integer
reject_media	boolean

favourites

COLUMN NAME	TYPE
id	integer
account_id	integer
status_id	bigint
created_at	timestamp without time zone
updated_at	timestamp without time zone

follow_requests

COLUMN NAME	TYPE
id	integer
account_id	integer
target_account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone

follows

COLUMN NAME	TYPE
id	integer
account_id	integer
target_account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone

imports

COLUMN NAME	TYPE
id	integer
account_id	integer
type	integer
approved	boolean
created_at	timestamp without time zone
updated_at	timestamp without time zone
data_file_name	character varying
data_file_size	integer
data_content_type	character varying
data_updated_at	timestamp without time zone

media_attachments

COLUMN NAME	TYPE
id	integer
status_id	bigint
file_file_name	character varying
file_content_type	character varying
file_file_size	integer
file_updated_at	timestamp without time zone
remote_url	character varying
account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone
shortcode	character varying
type	integer
file_meta	json

mentions

COLUMN NAME	TYPE
id	integer
account_id	integer
status_id	bigint
created_at	timestamp without time zone
updated_at	timestamp without time zone

mutes

COLUMN NAME	TYPE
id	integer
account_id	integer
target_account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone

notifications

COLUMN NAME	TYPE
id	integer
account_id	integer
activity_id	bigint
activity_type	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone
from_account_id	integer

oauth_access_grants

COLUMN NAME	TYPE
id	integer
resource_owner_id	integer
application_id	integer
token	character varying
expires_in	integer
redirect_uri	character varying
created_at	timestamp without time zone
revoked_at	timestamp without time zone
scopes	character varying

oauth_access_tokens

COLUMN NAME	TYPE
id	integer
resource_owner_id	integer
application_id	integer
token	character varying
refresh_token	character varying
expires_in	integer
revoked_at	timestamp without time zone
created_at	timestamp without time zone
scopes	character varying

oauth_appications

COLUMN NAME	TYPE
id	integer
name	character varying
uid	character varying
secret	character varying
redirect_uri	text
scopes	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone
superapp	boolean
website	character varying

preview_cards

COLUMN NAME	TYPE
id	integer
status_id	bigint
url	character varying
title	character varying
description	character varying
image_file_name	character varying
image_content_type	character varying
image_file_size	integer
image_updated_at	timestamp without time zone
created_at	timestamp without time zone
updated_at	timestamp without time zone
type	integer
html	text
author_name	character varying
author_url	character varying
provider_name	character varying
provider_url	character varying
width	integer
height	integer

reports

COLUMN NAME	TYPE
id	integer
account_id	integer
target_account_id	integer
status_ids	bigint[]
comment	text
action_taken	boolean
created_at	timestamp without time zone
updated_at	timestamp without time zone
action_taken_by_account_id	integer

schema_migrations

COLUMN NAME	TYPE
version	character varying

session_activations

COLUMN NAME	TYPE
id	bigint
user_id	integer
session_id	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone
user_agent	character varying
ip	inet
access_token_id	integer
web_push_subscription_id	integer

settings

COLUMN NAME	TYPE
id	integer
var	character varying
value	text
thing_type	character varying
thing_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone

statuses

COLUMN NAME	TYPE
id	integer
uri	character varying
account_id	integer
text	text
created_at	timestamp without time zone
updated_at	timestamp without time zone
in_reply_to_id	bigint
reblog_of_id	bigint
url	character varying
sensitive	boolean
visibility	integer
in_reply_to_account_id	integer
application_id	integer
spoiler_text	text
reply	boolean
favourites_count	integer
reblogs_count	integer
language	character varying
conversation_id	integer

statuses_tags

COLUMN NAME	TYPE
status_id	bigint
tag_id	integer

stream_entries

COLUMN NAME	TYPE
id	integer
account_id	integer
activity_id	bigint
activity_type	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone
hidden	boolean

subscriptions

COLUMN NAME	TYPE
id	integer
callback_url	character varying
secret	character varying
expires_at	timestamp without time zone
confirmed	boolean
account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone
last_successful_delivery_at	timestamp without time zone
domain	character varying

tags

COLUMN NAME	TYPE
id	integer
name	character varying
created_at	timestamp without time zone
updated_at	timestamp without time zone

users

COLUMN NAME	TYPE
id	integer
email	character varying
account_id	integer
created_at	timestamp without time zone
updated_at	timestamp without time zone
encrypted_password	character varying
reset_password_token	character varying
reset_password_sent_at	timestamp without time zone
remember_created_at	timestamp without time zone
sign_in_count	integer
current_sign_in_at	timestamp without time zone
last_sign_in_at	timestamp without time zone
urrent_sign_in_ip	inet
last_sign_in_ip	inet
admin	boolean
comfirmation_token	character varying
confirmed_at	timestamp without time zone
confirmation_sent_at	timestamp without time zone
unconfirmed_email	character varying
locale	character varying
encrypted_otp_secret	character varying
encrypted_otp_secret_iv	character varying
enctypted_otp_secret_salt	character varying
consumed_timestep	integer
otp_required_for_login	boolean
last_emailed_at	timestamp without time zone
otp_backup_codes	character varying[]
filtered_languages	character varying[]

web_push_subscriptions

COLUMN NAME	TYPE
id	integer
endpoint	character varying
key_p256dh	character varying
key_auth	character varying
data	json
created_at	timestamp without time zone
updated_at	timestamp without time zone

web_settings

COLUMN NAME	TYPE
id	integer
user_id	integer
data	json
created_at	timestamp without time zone
updated_at	timestamp without time zone