Sharkey

https://docs.joinsharkey.org/
Admin
Sharkey is a Misskey fork following upstream changes when possible, with added features. Like any...
Tokens:26,136
Snippets:245
Trust Score:7.8
Update:3 months ago
Show doc for...
Tokens:
Raw
### Meilisearch Docker Quickstart

Source: https://docs.joinsharkey.org/docs/customisation/search/meilisearch

Quickly start a Meilisearch instance in production mode using Docker. Ensure to replace the master key with a secure one. This command mounts a local directory for data persistence.

```bash
mkdir ./meili_data
docker run -it --rm \
  -p 7700:7700 \
  -e MEILI_ENV="production" \
  -e MEILI_MASTER_KEY="forTheLoveOfYourDeityCHangeMe" \
  -v $(pwd)/meili_data:/meili_data \
  getmeili/meilisearch:v1.8
```

--------------------------------

### Clone Repository and Install Dependencies

Source: https://docs.joinsharkey.org/docs/install/fresh

Clones the Sharkey repository, installs dependencies using pnpm, and copies the example configuration.

```bash
git clone --recurse-submodules -b stable https://activitypub.software/TransFem-org/Sharkey.git
cd Sharkey
pnpm install --frozen-lockfile
cp .config/example.yml .config/default.yml
```

--------------------------------

### Start Sharkey Application

Source: https://docs.joinsharkey.org/docs/install/fresh

Starts the Sharkey application. It will listen on port 3000 by default.

```bash
pnpm start
```

--------------------------------

### Example: Minimal Theme with Font

Source: https://docs.joinsharkey.org/docs/customisation/themes

A minimal theme example that sets a custom font face using `fontFaceSrc`. A `props` value must be provided, even if empty.

```javascript
{
  id: \"0fb6f73a-02ea-4722-8394-4b02188b7cfa\",
  name: \"Prank\",
  author: \"dakkar\",
  desc: \"Sets a funny font\",
  base: \"light\",
  props:{
    fontFaceSrc: \"url(https://fonts.gstatic.com/s/rubikbubbles/v3/JIA1UVdwbHFJtwA7Us1BPFbRNTE.ttf) format('truetype')\",
  }
}
```

--------------------------------

### Fetch Docker Compose and Configuration Files

Source: https://docs.joinsharkey.org/docs/install/fresh

Download the Docker Compose file and example configuration files for Sharkey.

```bash
wget -O Sharkey/docker-compose.yml https://activitypub.software/TransFem-org/Sharkey/-/raw/stable/compose_example.yml
wget -O Sharkey/.config/default.yml https://activitypub.software/TransFem-org/Sharkey/-/raw/stable/.config/docker_example.yml
wget -O Sharkey/.config/docker.env https://activitypub.software/TransFem-org/Sharkey/-/raw/stable/.config/docker_example.env
cd Sharkey
```

--------------------------------

### Start DeepLX Server

Source: https://docs.joinsharkey.org/docs/customisation/translate

Start the DeepLX server using pnpm. The port can be customized using the DEEPLXPORT environment variable.

```bash
pnpm start
# or
DEEPLXPORT=(number) pnpm start
```

--------------------------------

### Start Docker Containers

Source: https://docs.joinsharkey.org/docs/install/fresh

Start the Sharkey Docker containers in detached mode.

```bash
docker compose up -d
```

--------------------------------

### Install DeepLX Server

Source: https://docs.joinsharkey.org/docs/customisation/translate

Clone the DeepLX server repository and install its dependencies using pnpm.

```bash
git clone https://activitypub.software/Marie/deeplx-server
cd deeplx-server
pnpm i
```

--------------------------------

### Start Database and Connect to PostgreSQL Shell

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-1.0.5

After updating Docker Compose and volume mount paths, start only the database service and then connect to a psql shell to perform database migrations.

```bash
docker compose up -d db # (instead of `db`, you may need to use whatever name is set for the service in your docker compose config)

```

```bash
docker exec -it db psql -U firefish -d firefish # (replace `db` as before, and `-U firefish -d firefish` with the database user and database name, respectively, if they’re different from `firefish`)

```

--------------------------------

### Manage OpenRC Service

Source: https://docs.joinsharkey.org/docs/install/fresh

Commands to add and start the Sharkey service using OpenRC.

```bash
sudo rc-update add sharkey
sudo rc-service sharkey start
```

--------------------------------

### Create Directories for Docker Installation

Source: https://docs.joinsharkey.org/docs/install/fresh

Create the necessary directories for Sharkey's configuration and data when using Docker.

```bash
mkdir Sharkey && mkdir Sharkey/.config
```

--------------------------------

### Start Shell as Sharkey User

Source: https://docs.joinsharkey.org/docs/install/fresh

Switches to a bash shell as the newly created 'sharkey' user.

```bash
sudo -u sharkey bash
```

--------------------------------

### Manage Systemd Service

Source: https://docs.joinsharkey.org/docs/install/fresh

Commands to reload systemd, enable, and start the Sharkey service.

```bash
sudo systemctl daemon-reload
sudo systemctl enable sharkey
sudo systemctl start sharkey
```

--------------------------------

### Example: Mi Botanical Dark Theme

Source: https://docs.joinsharkey.org/docs/customisation/themes

An example of a built-in Misskey theme, showcasing various properties like accent colors, background, foreground, and more.

```javascript
{
  id: \"504debaf-4912-6a4c-5059-1db08a76b737\",

  name: \"Mi Botanical Dark\",
  author: \"syuilo\",

  base: \"dark\",

  props: {
    accent: \"rgb(148, 179, 0)\",
    bg: \"rgb(37, 38, 36)\",
    fg: \"rgb(216, 212, 199)\",
    fgHighlighted: \"#fff\",
    fgOnWhite: \"@accent\",
    divider: \"rgba(255, 255, 255, 0.14)\",
    panel: \"rgb(47, 47, 44)\",
    panelHeaderDivider: \"rgba(0, 0, 0, 0)\",
    header: \":alpha<0.7<@panel\",
    navBg: \"#363636\",
    renote: \"@accent\",
    mention: \"rgb(212, 153, 76)\",
    mentionMe: \"rgb(212, 210, 76)\",
    hashtag: \"#5bcbb0\",
    link: \"@accent\",
  },
}
```

--------------------------------

### Example: Minimal Theme with Code Highlighter

Source: https://docs.joinsharkey.org/docs/customisation/themes

A minimal theme that sets a code highlighter theme using `catppuccin-mocha`. A `props` value, even if empty, is required.

```javascript
{
  id: \"c690ae5f-3523-4a02-9206-2cf0c433f2d5\",
  name: \"Mocha code\",
  author: \"dakkar\",
  desc: \"Highlight with catppuccin-mocha\",
  base: \"light\",
  props: {},
  codeHighlighter: { base: \"catppuccin-mocha\" }
}
```

--------------------------------

### Manual Update Steps

Source: https://docs.joinsharkey.org/docs/install/fresh

Steps for manually updating Sharkey, including stopping the service, pulling changes, installing dependencies, building, and migrating.

```bash
sudo systemctl stop sharkey
```

```bash
rc-service sharkey stop
```

```bash
sudo -u sharkey bash
```

```bash
cd Sharkey
```

```bash
git checkout stable
```

```bash
git pull --recurse-submodules
```

```bash
pnpm install --frozen-lockfile
```

```bash
pnpm run build
```

```bash
pnpm run migrate
```

```bash
sudo systemctl restart sharkey
```

```bash
rc-service sharkey restart
```

```bash
pnpm run clean
```

```bash
pnpm run clean-all
```

--------------------------------

### Font Face Source Examples

Source: https://docs.joinsharkey.org/docs/customisation/themes

Demonstrates different ways to specify the source for custom fonts using the `fontFaceSrc` property, including local fonts, URLs, and formatted URLs.

```css
local(Arial)
```

```css
url(https://example.com/somefont.ttf)
```

```css
url(https://example.com/somefont.ttf) format('truetype')
```

```css
url(https://example.com/somefont.woff) format('woff'), url(https://example.com/somefont.ttf) format('truetype'), local(Arial)
```

--------------------------------

### Sharkey Configuration for Private Networks

Source: https://docs.joinsharkey.org/docs/customisation/translate

Example configuration for Sharkey to allow connections to private networks for DeepLX or LibreTranslate.

```yaml
allowedPrivateNetworks:
 - network: '127.0.0.1'
   ports: [ (the deeplx port) ]

# or for LibreTranslate:
allowedPrivateNetworks:
 - network: '(serverip)'
   ports: [ (port) ]
```

--------------------------------

### Color Specification Examples

Source: https://docs.joinsharkey.org/docs/customisation/themes

Illustrates various ways to specify colors within theme properties, including hex codes, RGB/RGBA tuples, references to other colors or constants, and function calls for manipulation.

```css
#112233
```

```css
rgb(0.5, 0.7, 1.0, 0.5)
```

```css
rgba(0.5, 0.7, 1.0, 0.5)
```

```css
@fg
```

```css
$something
```

```css
:darken<0.5<@fg
```

```css
:darken<2<:alpha<0.4<@fg
```

--------------------------------

### Configure MeiliSearch in Docker Compose

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-1.0.5

Replace your existing Sonic or ElasticSearch configuration with this MeiliSearch setup when migrating to Sharkey, as Sharkey currently only supports MeiliSearch.

```yaml
meilisearch:
    restart: always
    image: getmeili/meilisearch:v1.3.4
    environment:
      - MEILI_NO_ANALYTICS=true
      - MEILI_ENV=production
    env_file:
      - .config/meilisearch.env
    networks:
      - calcnet # <-- Use whatever network name is used in the docker compose here
    volumes:
      - ./meili_data:/meili_data # <--- make sure to replace the volume with one that fits your existing docker compose

```

--------------------------------

### Create PostgreSQL Database and User

Source: https://docs.joinsharkey.org/docs/install/fresh

Sets up the PostgreSQL database and user required by Sharkey. Ensure you replace '{YOUR_PASSWORD}' with a secure password.

```sql
CREATE DATABASE sharkey WITH ENCODING = 'UTF8';
CREATE USER sharkey WITH ENCRYPTED PASSWORD '{YOUR_PASSWORD}';
GRANT ALL PRIVILEGES ON DATABASE sharkey TO sharkey;
ALTER DATABASE sharkey OWNER TO sharkey;
\q
```

--------------------------------

### Initialize Sharkey Schema

Source: https://docs.joinsharkey.org/docs/install/fresh

Creates the necessary database schema for Sharkey using pnpm.

```bash
pnpm run init
```

--------------------------------

### Create Instance Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

SQL command to create the __chart__instance table. This comprehensive table tracks various instance metrics, including requests, notes, users, and drive statistics.

```sql
CREATE TABLE public.__chart__instance (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___requests_failed SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___requests_succeeded SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___requests_received SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___notes_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_inc INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_dec INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_diffs_normal INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_diffs_reply INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_diffs_renote INTEGER DEFAULT '0'::BIGINT NOT NULL, ___users_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___users_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___users_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___following_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___following_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___following_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___followers_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___followers_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___followers_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___drive_totalFiles" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_incFiles" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_incUsage" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_decFiles" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_decUsage" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___notes_diffs_withFile" INTEGER DEFAULT 0 NOT NULL);
```

--------------------------------

### Create PGroonga Index for Note Search

Source: https://docs.joinsharkey.org/docs/customisation/search

Install the pgroonga extension and create a GIN index on the 'text', 'cw', and 'name' columns of the 'note' table for advanced full-text search capabilities. Ensure PGroonga is installed and compatible with your PostgreSQL version.

```sql
CREATE EXTENSION IF NOT EXISTS pgroonga;
CREATE INDEX CONCURRENTLY IF NOT EXISTS "CUSTOM_IDX_note_text_pg"
    ON "note"
 USING pgroonga ("text","cw","name")
 WITH (query_allow_column=true);
ANALYZE "note";
```

--------------------------------

### Troubleshooting Build and Migration Issues

Source: https://docs.joinsharkey.org/docs/install/migrate/misskey

If the initial build or migration fails, try cleaning all artifacts and rebuilding from scratch. This can resolve issues related to corrupted builds or dependencies.

```bash
pnpm run clean-all
pnpm install --frozen-lockfile
pnpm run build
pnpm run migrate
```

--------------------------------

### Define Custom Color Constants

Source: https://docs.joinsharkey.org/docs/customisation/themes

Add extra values for color specifications using keys starting with '$'. These are not set as CSS variables and are not escaped.

```json
props: {
  \"$base\": \"#aa0044\",
  \"fg\": \":darken<3<$base\",
  \"bg\": \":lighten<4<$base\",
  \"accent\": \"$base\"
}
```

--------------------------------

### CREATE TABLE for __chart_day__instance

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily instance-wide statistics, such as requests, notes, and user counts.

```sql
CREATE TABLE public.__chart_day__instance (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___requests_failed SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___requests_succeeded SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___requests_received SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___notes_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_inc INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_dec INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_diffs_normal INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_diffs_reply INTEGER DEFAULT '0'::BIGINT NOT NULL, ___notes_diffs_renote INTEGER DEFAULT '0'::BIGINT NOT NULL, ___users_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___users_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___users_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___following_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___following_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___following_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___followers_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___followers_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___followers_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___drive_totalFiles" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_incFiles" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_incUsage" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_decFiles" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___drive_decUsage" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___notes_diffs_withFile" INTEGER DEFAULT 0 NOT NULL);
```

--------------------------------

### Manual Migration Steps for Sharkey

Source: https://docs.joinsharkey.org/docs/install/migrate/misskey

Use these commands to migrate a Misskey instance to Sharkey manually. Ensure you are running these commands as the correct user and in the correct directory.

```bash
sudo -u misskey -i
cd misskey
git remote rename origin misskey
git remote add origin https://activitypub.software/TransFem-org/Sharkey.git
git remote update -p
git checkout -b stable --track origin/stable
git pull --recurse-submodules
pnpm install --frozen-lockfile
pnpm run build
pnpm run migrate
```

--------------------------------

### Build Sharkey

Source: https://docs.joinsharkey.org/docs/install/fresh

Compiles the Sharkey application using pnpm.

```bash
pnpm run build
```

--------------------------------

### Docker Migration Steps for Sharkey

Source: https://docs.joinsharkey.org/docs/install/migrate/misskey

Follow these steps if you are using Misskey's `docker-compose.yml` file to migrate to Sharkey. This involves updating Git remotes, pulling changes, and rebuilding Docker images.

```bash
git remote rename origin misskey
git remote add origin https://activitypub.software/TransFem-org/Sharkey.git
git remote update -p
git checkout -b stable --track origin/stable
git pull --recurse-submodules
pnpm install --frozen-lockfile
sudo docker compose build
```

--------------------------------

### Caddyfile Configuration for Sharkey

Source: https://docs.joinsharkey.org/docs/install/fresh

Add this to your Caddyfile to proxy requests to Sharkey. Replace sharkey.example with your domain and adjust the port if necessary.

```caddy
sharkey.example {
reverse_proxy localhost:3000
}
```

--------------------------------

### Set Custom Image for Animation

Source: https://docs.joinsharkey.org/docs/customisation/themes

Replace the default animation frames by uploading an image and setting the `oneko-image` property to its drive URL. Ensure the URL value starts with a double quote and does not end with one.

```json
"oneko-image": \"url(https://yourinstance.example.com/files/ee17b385-a084-4e2a-b531-225dfb96cc3c)\",
```

--------------------------------

### Create Trigram Index for Simple LIKE Search

Source: https://docs.joinsharkey.org/docs/customisation/search

Use this SQL command to create a trigram index for the 'text' column in the 'note' table to improve the performance of simple LIKE searches. Ensure the pg_trgm extension is installed.

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY IF NOT EXISTS "CUSTOM_IDX_note_text_trgm"
    ON "note"
 USING gin ("text" gin_trgm_ops);
ANALYZE "note";
```

--------------------------------

### Configure Sharkey for Simple LIKE Search

Source: https://docs.joinsharkey.org/docs/customisation/search

Set the 'provider' to 'sqlLike' in your Sharkey configuration file to use the default simple LIKE search method.

```yaml
fulltextSearch:
  provider: sqlLike
```

--------------------------------

### SQL Queries for Firefish to Sharkey Schema Migration

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Execute these SQL queries within a psql shell connected to your Firefish database to rename enum types, adapting the schema for Sharkey compatibility. Start a transaction before running these commands.

```sql
-- start a transaction, so we won't leave the db in a halfway state if
-- things go wrong
BEGIN;

-- undo Firefish removing "_enum" from enum names
ALTER TYPE "antenna_src" RENAME TO "antenna_src_enum";
ALTER TYPE "drive_file_usage_hint" RENAME TO "drive_file_usage_hint_enum";
ALTER TYPE "muted_note_reason" RENAME TO "muted_note_reason_enum";
ALTER TYPE "note_visibility" RENAME TO "note_visibility_enum";
ALTER TYPE "notification_type" RENAME TO "notification_type_enum";
ALTER TYPE "page_visibility" RENAME TO "page_visibility_enum";
ALTER TYPE "poll_note_visibility" RENAME TO "poll_notevisibility_enum";
ALTER TYPE "relay_status" RENAME TO "relay_status_enum";
ALTER TYPE "user_emoji_mod_perm" RENAME TO "user_emojimodperm_enum";
ALTER TYPE "user_profile_ffvisibility" RENAME TO "user_profile_ffvisibility_enum";
ALTER TYPE "user_profile_muting_notification_types" RENAME TO "user_profile_mutingnotificationtypes_enum";

```

--------------------------------

### Update Sharkey with Docker

Source: https://docs.joinsharkey.org/docs/install/fresh

Fetch the latest Docker image and restart the services.

```bash
docker compose pull && docker compose up -d
```

--------------------------------

### Systemd Service Configuration for Sharkey

Source: https://docs.joinsharkey.org/docs/install/fresh

Configuration file for setting up Sharkey as a systemd service. Adjust paths and environment variables as needed.

```ini
[Unit]
Description=Sharkey daemon

[Service]
Type=simple
User=sharkey
ExecStart=/usr/bin/pnpm start
WorkingDirectory=/home/sharkey/Sharkey
Environment="NODE_OPTIONS=--max-old-space-size=8192"
Environment="NODE_ENV=production"
TimeoutSec=60
StandardOutput=journal
StandardError=journal
SyslogIdentifier=sharkey
Restart=always

[Install]
WantedBy=multi-user.target
```

--------------------------------

### OpenRC Service Configuration for Sharkey

Source: https://docs.joinsharkey.org/docs/install/fresh

Configuration file for setting up Sharkey as an OpenRC service. Adjust paths and environment variables as needed.

```sh
#!/sbin/openrc-run

name=sharkey
description="Sharkey daemon"

command="/usr/bin/pnpm"
command_args="start"
command_user="sharkey"

supervisor="supervise-daemon"
supervise_daemon_args=" -d /home/sharkey/Sharkey -e NODE_ENV=production -e \"NODE_OPTIONS=--max-old-space-size=8192\""

pidfile="/run/${RC_SVCNAME}.pid"

depend() {
  need net
  use logger nginx
}
```

--------------------------------

### Create Reversi Game and Matching Tables

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Creates the 'reversi_game' and 'reversi_matching' tables. These tables were present in older Misskey versions and are now reintroduced.

```sql
CREATE TABLE "reversi_game" ("id" character varying(32) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "startedAt" TIMESTAMP WITH TIME ZONE, "user1Id" character varying(32) NOT NULL, "user2Id" character varying(32) NOT NULL, "user1Accepted" boolean NOT NULL DEFAULT false, "user2Accepted" boolean NOT NULL DEFAULT false, "black" integer, "isStarted" boolean NOT NULL DEFAULT false, "isEnded" boolean NOT NULL DEFAULT false, "winnerId" character varying(32), "surrendered" character varying(32), "logs" jsonb NOT NULL DEFAULT '[]', "map" character varying(64) array NOT NULL, "bw" character varying(32) NOT NULL, "isLlotheo" boolean NOT NULL DEFAULT false, "canPutEverywhere" boolean NOT NULL DEFAULT false, "loopedBoard" boolean NOT NULL DEFAULT false, "form1" jsonb DEFAULT null, "form2" jsonb DEFAULT null, "crc32" character varying(32), CONSTRAINT "PK_76b30eeba71b1193ad7c5311c3f" PRIMARY KEY ("id"));
CREATE INDEX "IDX_b46ec40746efceac604142be1c" ON "reversi_game" ("createdAt");
CREATE TABLE "reversi_matching" ("id" character varying(32) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "parentId" character varying(32) NOT NULL, "childId" character varying(32) NOT NULL, CONSTRAINT "PK_880bd0afbab232f21c8b9d146cf" PRIMARY KEY ("id"));
CREATE INDEX "IDX_b604d92d6c7aec38627f6eaf16" ON "reversi_matching" ("createdAt");
CREATE INDEX "IDX_3b25402709dd9882048c2bbade" ON "reversi_matching" ("parentId");
CREATE INDEX "IDX_e247b23a3c9b45f89ec1299d06" ON "reversi_matching" ("childId");
```

--------------------------------

### Create Firefish Database Tables

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

SQL commands to create the necessary tables for Firefish data tracking.

```sql
CREATE TABLE public.__chart_day__per_user_reaction (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___local_count SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_count SMALLINT DEFAULT '0'::BIGINT NOT NULL);
CREATE TABLE public.__chart_day__users (id SERIAL NOT NULL, DATE INTEGER NOT NULL, ___local_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Create Antenna Note Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Creates an empty 'antenna_note' table. This table is needed for Sharkey's functionality.

```sql
CREATE TABLE antenna_note();
```

--------------------------------

### CREATE TABLE for __chart_day__per_user_drive

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily per-user drive statistics, including file counts and usage.

```sql
CREATE TABLE public.__chart_day__per_user_drive (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, "___totalCount" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___totalSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___incCount" SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___incSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___decCount" SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___decSize" INTEGER DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### CREATE TABLE for __chart_day__per_user_following

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily per-user following and follower counts, differentiating between local and remote relationships.

```sql
CREATE TABLE public.__chart_day__per_user_following (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___local_followings_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_followings_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_followings_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_followers_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_followers_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_followers_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followings_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_followings_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followings_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followers_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_followers_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followers_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### CREATE TABLE for __chart_day__per_user_notes

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily per-user note statistics, including total counts and different note types.

```sql
CREATE TABLE public.__chart_day__per_user_notes (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___DEC SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___diffs_normal SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___diffs_reply SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___diffs_renote SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___diffs_withFile" SMALLINT DEFAULT '0'::SMALLINT NOT NULL);
```

--------------------------------

### Create Drive Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

SQL command to create the __chart__drive table. This table tracks local and remote drive activity, including increment and decrement counts and sizes.

```sql
CREATE TABLE public.__chart__drive (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "___local_incCount" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___local_incSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___local_decCount" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___local_decSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___remote_incCount" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___remote_incSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___remote_decCount" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___remote_decSize" INTEGER DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Configure Code Highlighter Theme

Source: https://docs.joinsharkey.org/docs/customisation/themes

Set the `codeHighlighter` top-level key to specify a Shiki theme for MFM code blocks. The `base` value should be a theme name bundled with Shiki.

```javascript
codeHighlighter: { \"base\": \"catppuccin-mocha\" }
```

--------------------------------

### Create Daily Federation Chart Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily federation statistics, including delivered and inbox instances, stalled instances, and subscription/publication counts.

```sql
CREATE TABLE public.__chart_day__federation (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "unique_temp___deliveredInstances" CHARACTER VARYING[] DEFAULT '{}'::CHARACTER VARYING[] NOT NULL, "___deliveredInstances" SMALLINT DEFAULT '0'::SMALLINT NOT NULL, "unique_temp___inboxInstances" CHARACTER VARYING[] DEFAULT '{}'::CHARACTER VARYING[] NOT NULL, "___inboxInstances" SMALLINT DEFAULT '0'::SMALLINT NOT NULL, unique_temp___stalled CHARACTER VARYING[] DEFAULT '{}'::CHARACTER VARYING[] NOT NULL, ___stalled SMALLINT DEFAULT '0'::SMALLINT NOT NULL, ___sub SMALLINT DEFAULT '0'::SMALLINT NOT NULL, ___pub SMALLINT DEFAULT '0'::SMALLINT NOT NULL, ___pubsub SMALLINT DEFAULT '0'::SMALLINT NOT NULL, "___subActive" SMALLINT DEFAULT '0'::SMALLINT NOT NULL, "___pubActive" SMALLINT DEFAULT '0'::SMALLINT NOT NULL);
```

--------------------------------

### Configure Docker Compose Web Service

Source: https://docs.joinsharkey.org/docs/install/fresh

Modify the docker-compose.yml file to use the latest Sharkey image and comment out the build context.

```yaml
services:
  web:
    image: registry.activitypub.software/transfem-org/sharkey:latest
    # build: .
    restart: always
    …
```

--------------------------------

### CREATE TABLE for __chart_day__notes

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily note statistics, differentiating between local and remote notes and their types.

```sql
CREATE TABLE public.__chart_day__notes (id SERIAL NOT NULL, DATE INTEGER NOT NULL, ___local_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_inc INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_dec INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_diffs_normal INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_diffs_reply INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_diffs_renote INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_inc INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_dec INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_diffs_normal INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_diffs_reply INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_diffs_renote INTEGER DEFAULT '0'::BIGINT NOT NULL, "___local_diffs_withFile" INTEGER DEFAULT 0 NOT NULL, "___remote_diffs_withFile" INTEGER DEFAULT 0 NOT NULL);
```

--------------------------------

### Apply User and Profile Data Migrations

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

This SQL script performs essential data migrations for existing users, including setting approval status, restoring moved data, and updating various user profile and meta settings. It also drops old Firefish-specific columns.

```sql
BEGIN;

-- all existing users are approved, because Firefish doesn't have a
-- concept of approvals
UPDATE "user" SET approved = true;

-- now we put back the data we moved aside
UPDATE "user" SET "movedToUri" = "ff_movedToUri" WHERE "ff_movedToUri" IS NOT NULL;
UPDATE "user" SET "alsoKnownAs" = "ff_alsoKnownAs" WHERE "ff_alsoKnownAs" IS NOT NULL;
UPDATE "user" SET "noindex" = NOT (COALESCE("ff_isIndexable", true));
UPDATE "user" SET "speakAsCat" = COALESCE("ff_speakAsCat", false);
UPDATE "user_profile" SET "preventAiLearning" = COALESCE("ff_preventAiLearning", true);
UPDATE "meta" SET "silencedHosts" = COALESCE("ff_silencedHosts",'{}');

ALTER TABLE "user" DROP COLUMN "ff_movedToUri";
ALTER TABLE "user" DROP COLUMN "ff_alsoKnownAs";
ALTER TABLE "user" DROP COLUMN "ff_isIndexable";
ALTER TABLE "user" DROP COLUMN "ff_speakAsCat";
ALTER TABLE "user_profile" DROP COLUMN "ff_preventAiLearning";
ALTER TABLE "meta" DROP COLUMN "ff_silencedHosts";
```

--------------------------------

### Generate Meilisearch API Key for Sharkey

Source: https://docs.joinsharkey.org/docs/customisation/search/meilisearch

Create a Meilisearch API key with specific permissions for Sharkey. This requires Meilisearch to be running and accessible, and the master API key to be set in an environment variable. The index name must end with '---notes'.

```bash
curl -s -X POST 'http://localhost:7700/keys' -H 'Content-Type: application/json'   -H "Authorization: Bearer $MASTER_KEY"   --data-binary '{ 
    "description": "Sharkey Meilisearch key",
    "actions": ["*"],
    "indexes": ["shonk_social---notes"],
    "expiresAt": "2027-01-01T00:00:00Z"
  }' | jq '.key'
```

--------------------------------

### Create Network Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

SQL command to create the __chart__network table. This table is used for monitoring network traffic, including incoming/outgoing requests and bytes, and total time.

```sql
CREATE TABLE public.__chart__network (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "___incomingRequests" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___outgoingRequests" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___totalTime" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___incomingBytes" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___outgoingBytes" INTEGER DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Apache VirtualHost Configuration for Sharkey

Source: https://docs.joinsharkey.org/docs/install/fresh

A sample Apache 2.4.47+ configuration for proxying to Sharkey, including WebSocket support and SSL. Adjust domain and ports as needed.

```apache
<VirtualHost *>
 DocumentRoot /var/empty
 ServerName sharkey.example

 SSLEngine On
 RequestHeader set X-Forwarded-Proto "https"

 AllowEncodedSlashes NoDecode

 ProxyPreserveHost On

 ProxyPass "/" "http://127.0.0.1:3000/" upgrade=websocket
 ProxyPassReverse "/" "https://sharkey.example/"
 ProxyPassReverse "/" "http://127.0.0.1:3000/"

 # or, if your Sharkey listens on a UNIX socket
 # ProxyPass "/" "unix:/opt/sharkey/sharkey.sock|http://127.0.0.1/" upgrade=websocket
 # ProxyPassReverse "/" "https://sharkey.example/"
 # ProxyPassReverse "/" "unix:/opt/sharkey/sharkey.sock|http://127.0.0.1/"

</VirtualHost>
```

--------------------------------

### HAProxy Frontend Configuration

Source: https://docs.joinsharkey.org/docs/install/fresh

Add these lines to your HAProxy frontend configuration to route traffic for sharkey.example.

```haproxy
  acl sharkey hdr(Host) -i sharkey.example
  use_backend sharkey-be if sharkey
```

--------------------------------

### Configure Code Highlighter with Overrides

Source: https://docs.joinsharkey.org/docs/customisation/themes

Provide custom styles for code syntax highlighting by setting `codeHighlighter` with a `base` of `_none_` and providing a TextMate theme definition in `overrides`.

```javascript
codeHighlighter: {
  \"base\": \"_none_\",
  \"overrides\": {
    // put the whole TextMate theme definition here
  }
}
```

--------------------------------

### CREATE TABLE for __chart_day__network

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking daily network traffic statistics, including requests and data transfer.

```sql
CREATE TABLE public.__chart_day__network (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "___incomingRequests" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___outgoingRequests" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___totalTime" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___incomingBytes" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___outgoingBytes" INTEGER DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Create Per-User Reaction Chart Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking per-user reactions, distinguishing between local and remote reaction counts.

```sql
CREATE TABLE public.__chart__per_user_reaction (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___local_count SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_count SMALLINT DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Font Face Source from Drive

Source: https://docs.joinsharkey.org/docs/customisation/themes

Illustrates how to use a font file uploaded to the drive by providing its URL in the `fontFaceSrc` property.

```css
url(https://instance.example.com/files/8b0cc94f-0307-4aef-867b-b2e9ee6fd79f)
```

--------------------------------

### Add NSFW Detection Settings

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

This SQL script adds new columns and enum types required for NSFW media detection in Sharkey. These settings are expected by Sharkey even if not actively used by the current version.

```sql
-- next, add NSFW detection settings that Firefish removed
-- Sharkey doesn't use these right now, but it expects them to be here
CREATE TYPE "public"."meta_sensitivemediadetectionsensitivity_enum" AS ENUM('medium', 'low', 'high', 'veryLow', 'veryHigh');
CREATE TYPE "public"."meta_sensitivemediadetection_enum" AS ENUM('none', 'all', 'local', 'remote');

ALTER TABLE "user_profile" ADD "autoSensitive" BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE "meta" ADD "enableSensitiveMediaDetectionForVideos" BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE "meta" ADD "setSensitiveFlagAutomatically" BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE "meta" ADD "sensitiveMediaDetectionSensitivity" "public"."meta_sensitivemediadetectionsensitivity_enum" NOT NULL DEFAULT 'medium';
ALTER TABLE "meta" ADD "sensitiveMediaDetection" "public"."meta_sensitivemediadetection_enum" NOT NULL DEFAULT 'none';
ALTER TABLE "drive_file" ADD "maybePorn" BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE "drive_file" ADD "maybeSensitive" BOOLEAN NOT NULL DEFAULT FALSE;

COMMENT ON COLUMN "drive_file"."maybeSensitive" IS 'Whether the DriveFile is NSFW. (predict)';
```

--------------------------------

### Create AP Request Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

SQL command to create the __chart__ap_request table. Use this to set up the database schema for tracking API requests.

```sql
CREATE TABLE public.__chart__ap_request (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "___deliverFailed" INTEGER DEFAULT 0 NOT NULL, "___deliverSucceeded" INTEGER DEFAULT 0 NOT NULL, "___inboxReceived" INTEGER DEFAULT 0 NOT NULL);
```

--------------------------------

### Recreate User Profile Columns

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

This SQL script recreates the 'room' and 'clientData' JSONB columns in the user_profile table, which were removed by Firefish. Default empty JSON objects are provided.

```sql
-- recreate user profile columns that Firefish removed
ALTER TABLE "user_profile" ADD "room" JSONB NOT NULL DEFAULT '{}';
ALTER TABLE "user_profile" ADD "clientData" JSONB NOT NULL DEFAULT '{}';

COMMENT ON COLUMN "user_profile"."room" IS 'The room data of the User.';
COMMENT ON COLUMN "user_profile"."clientData" IS 'The client-specific data of the User.';
```

--------------------------------

### Create Per-User Following Chart Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking per-user following and follower counts, differentiating between local and remote relationships and their changes.

```sql
CREATE TABLE public.__chart__per_user_following (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, ___local_followings_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_followings_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_followings_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_followers_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_followers_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___local_followers_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followings_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_followings_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followings_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followers_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_followers_inc SMALLINT DEFAULT '0'::BIGINT NOT NULL, ___remote_followers_dec SMALLINT DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Create Per-User Drive Chart Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

Defines the schema for tracking per-user drive usage, including counts and sizes for incremental and decremental changes.

```sql
CREATE TABLE public.__chart__per_user_drive (id SERIAL NOT NULL, DATE INTEGER NOT NULL, "group" CHARACTER VARYING(128) NOT NULL, "___totalCount" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___totalSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___incCount" SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___incSize" INTEGER DEFAULT '0'::BIGINT NOT NULL, "___decCount" SMALLINT DEFAULT '0'::BIGINT NOT NULL, "___decSize" INTEGER DEFAULT '0'::BIGINT NOT NULL);
```

--------------------------------

### Create Notes Table

Source: https://docs.joinsharkey.org/docs/install/migrate/firefish-20241205

SQL command to create the __chart__notes table. This schema tracks local and remote note statistics, including total counts, increments, decrements, and differences.

```sql
CREATE TABLE public.__chart__notes (id SERIAL NOT NULL, DATE INTEGER NOT NULL, ___local_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_inc INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_dec INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_diffs_normal INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_diffs_reply INTEGER DEFAULT '0'::BIGINT NOT NULL, ___local_diffs_renote INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_total INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_inc INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_dec INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_diffs_normal INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_diffs_reply INTEGER DEFAULT '0'::BIGINT NOT NULL, ___remote_diffs_renote INTEGER DEFAULT '0'::BIGINT NOT NULL, "___local_diffs_withFile" INTEGER DEFAULT 0 NOT NULL, "___remote_diffs_withFile" INTEGER DEFAULT 0 NOT NULL);
```

--------------------------------

### Configure Sharkey for PGroonga Search

Source: https://docs.joinsharkey.org/docs/customisation/search

Set the 'provider' to 'sqlPgroonga' in your Sharkey configuration file to enable search using the PGroonga extension. Restart Sharkey after applying the configuration.

```yaml
fulltextSearch:
  provider: sqlPgroonga
```
Tokens:
Raw