[nextjs]SNS Server-11(myapp21)

👉🏻 @user1@aloy-horizon.duckdns.org의 follows에 같은 사용자가 두번 입력되는 문제를 수정합니다.
Fixes an issue where the same user is entered twice in the ‘follows’ list for @user1@aloy-horizon.duckdns.org.

👉🏻 follow요청시 following과 followers테이블에 기존 사용자 있으면 요청을 보내지 않습니다.
When a follow request is made, the request is not sent if the user already exists in the ‘following’ or ‘followers’ tables.

📁 전체 프로젝트 구조 / Overall Project Structure

myapp21/  
├── app/  (Next.js App Router)
│   ├── .well-known/webfinger/route.ts  -> webfinger
│   ├── api/posts/route.ts     -> Writing API
│   ├── api/follow/route.ts    -> Follow API(temporary)
│   ├── api/timeline/route.ts  -> Timeline API
│   ├── users/[username]/
│   │   ├── statuses/[id]/route.ts -> Indivisual Post
│   │   ├── route.ts               -> Acotr Information
│   │   ├── followers/route.ts     -> Followers List
│   │   ├── following/route.ts     -> Following List
│   │   ├── inbox/route.ts         -> Inbox
│   │   └── outbox/route.ts        -> outbox
│   ├── usersui/[username]/page.tsx  -> Timeline UI
│   ├── layout.tsx, page.tsx, globals.css
│   └── favicon.ico
├── lib/
│   ├── ap.ts                  -> Follow Accept
│   └── db.ts                  -> DB connection
├── data/
│   └── keys/                  -> private.pem, public.pem
├── data.sqlite                -> Database
├── Caddyfile                  -> https 
└── package.json

📁 프로젝트 시작(myapp21)
Project Start (myapp21)

npx create-next-app@latest

📁 SQLite설치 / Installing SQLite

cd ~/myapp20
npm install better-sqlite3
npm install -D @types/better-sqlite3

📁 DDNS,https설정 / DDNS,https settings

[nextjs]nextjs+domain(ddns)+https(caddy)

📁 도메인허용, @userid 사용하기
Allowing domains, using @userid

— next.config.ts

import type { NextConfig } from "next";

const nextConfig: NextConfig = {
  /* allow domain*/
  allowedDevOrigins: ['aloy-horizon.duckdns.org', '*.duckdns.org'],

  /* host.domain.org/@userid */
  async rewrites() {
    return [
      {
        source: '/@:username',
        destination: '/usersui/:username',
      },
    ]
  }
};


export default nextConfig;

---

📁 Following 처리 부분 수정 / Modification to the ‘Following’ processing logic

✔️ @user1@aloy-horizon.duckdns.org 계정에서 followers가 중복 요청 하지 않도록합니다.
Prevent duplicate requests from followers for the @user1@aloy-horizon.duckdns.org account.

— 유니크 인덱스 추가(sqlite) / Add Unique Index (SQLite)

CREATE UNIQUE INDEX IF NOT EXISTS idx_following_actor_username ON following(actor, username);

— myapp21/app/api/follow/route.ts

... ...

export async function POST(req: Request) {

 ... ...

    // myapp21 ✅
    // 이미 팔로잉 중이면 네트워크 요청을 보내지 않음
    // Do not send a network request if already following
    const already = db.prepare('SELECT id FROM following WHERE actor = ? AND username = ?').get(target, username) as any;
    if (already) {
      console.log(`ℹ️ [${username}] 이미 팔로잉 중 / Already following - 요청 스킵 / Skip request: ${target}`);
      return Response.json({ 
        ok: true, 
        alreadyFollowing: true, 
        target, 
        username,
        message: '이미 팔로잉 중 / Already following'
      });

... ...

}  
  
... ...

  // myapp21 ✅ 
  // 데이터베이스에 중복 입력 방지 / Preventing duplicate entries in the database
    if (result.ok) {
      try {
        db.prepare('INSERT INTO following (id, actor, username) VALUES (?, ?, ?)')
          .run(result.followDoc.id, target, username);
        console.log(`✅ DB 저장 / DB Save: ${username} -> ${target}`);
      } catch (e: any) {
        if (e.code === 'SQLITE_CONSTRAINT_UNIQUE') {
          console.log(`ℹ️ 이미 저장됨  / Already saved: ${target}`);
        } else {
          console.error('DB 저장 실패 / DB save failed:', e);
        }
      }
    }

... ...

}

... ...

export async function DELETE(req: Request) {

  ... ...

    // ✅ myapp21 - 언팔로우 중복 체크 / 
    const exists = db.prepare('SELECT id FROM following WHERE actor = ? AND username = ?').get(target, username) as any;
    if (!exists) {
      console.log(`ℹ️ [${username}] 팔로잉 중 아님 / Not Following - 언팔 스킵 /  Skip Unfollowing: ${target}`);
      return Response.json({ ok: true, alreadyNotFollowing: true, message: '이미 언팔 상태 / Already unfollowed.' });
    }

... ...

}

— myapp21/lib/db.ts

db.exec(`
  CREATE INDEX IF NOT EXISTS idx_followers_username ON followers(username);
  CREATE INDEX IF NOT EXISTS idx_followers_actor ON followers(actor);
  CREATE INDEX IF NOT EXISTS idx_following_username ON following(username);
  CREATE INDEX IF NOT EXISTS idx_following_actor ON following(actor);
  CREATE INDEX IF NOT EXISTS idx_posts_username_created ON posts(username, created_at DESC);
  CREATE INDEX IF NOT EXISTS idx_inbox_username_created ON inbox_posts(username, created_at DESC);
  CREATE INDEX IF NOT EXISTS idx_inbox_actor ON inbox_posts(actor);
  CREATE UNIQUE INDEX IF NOT EXISTS idx_following_actor_username ON following(actor, username);
`);

📁 Follower 처리 부분 수정 / Modified the follower handling logic.

— 유니크 인덱스 추가 / Add unique index (sqlite)

CREATE UNIQUE INDEX idx_followers_actor_username ON followers(actor, username);

— myapp21/app/users/[username]/inbox/route.ts

      
... ...

// ✅ myapp21 
// 데이터베이스에 중복 입력 방지 / Preventing duplicate entries in the database      

const exists = db.prepare('SELECT 1 FROM followers WHERE actor = ? AND username = ?').get(actorId, username) as any;
      
      if (exists) {
        console.log(`ℹ️ [${username}] 이미 팔로워 / Already follower: ${actorId} - 중복 무시/Ignore duplicates`);
      } else {
        db.prepare('INSERT INTO followers (id, actor, inbox, username) VALUES (?,?,?,?)')
        .run(body.id, actorId, inboxUrl, username);
        console.log(`✅ [${username}] 팔로우 저장 / follow save : ${actorId}`);
      }

— myapp21/lib/db.ts

CREATE UNIQUE INDEX IF NOT EXISTS idx_following_actor_username ON following(actor, username);
CREATE UNIQUE INDEX idx_followers_actor_username ON followers(actor, username);

📁 gotosocial DB(docker compose)

✔️아래의 과정으로 기존 DB를 초기화 할 수 있습니다.
You can initialize the existing database by following the steps below.

# DB 들어가기 / Accessing the DB
sqlite3 sqlite.db

SELECT username, domain, followers_count, following_count FROM accounts WHERE domain='aloy-horizon.duckdns.org';

# 삭제 / delete
DELETE FROM accounts WHERE domain='aloy-horizon.duckdns.org' AND username='user1';

.quit

# 서버 재시작 / Server restart
docker compose restart gotosocial
docker compose logs -f gotosocial

✔️ sqlite에서 오류가 있는 데이터 삭제

— 아래처럼 followers,following,toots를 모두 0으로 초기화 시킵니다.

sqlite> SELECT id, username, domain, uri FROM accounts WHERE domain='aloy-horizon.duckdns.org';
01KZWNMF7BFD5N6MAYV3068C81|user1|aloy-horizon.duckdns.org|https://aloy-horizon.duckdns.org/users/user1

sqlite> SELECT * FROM account_stats WHERE account_id='01KZWNMF7BFD5N6MAYV3068C81';
01KZWNMF7BFD5N6MAYV3068C81|2026-06-10 01:20:08.931+00:00|0|1|0|2|0|

-- follows 테이블 확인 / Check the 'follows' table.
sqlite>SELECT * FROM follows WHERE account_id='01KZWNMF7BFD5N6MAYV3068C81' OR target_account_id='01KZWNMF7BFD5N6MAYV3068C81';

sqlite>DELETE FROM account_stats WHERE account_id='01KZWNMF7BFD5N6MAYV3068C81';
sqlite>DELETE FROM accounts WHERE id='01KZWNMF7BFD5N6MAYV3068C81';

-- follows는 이미 비어있음 / 'follows' is already empty.

-- toots 오류 있어서 모두 삭제 / Deleted everything due to errors with Toots.

sqlite> SELECT id FROM accounts WHERE domain='aloy-horizon.duckdns.org';
01M0RKD5BF8JEGA7HN2A66Z54C

sqlite> SELECT COUNT(*) FROM statuses WHERE account_id='01M0RKD5BF8JEGA7HN2A66Z54C';
0

sqlite> SELECT COUNT(*) FROM statuses WHERE account_id='01KZWNMF7BFD5N6MAYV3068C81';
3

sqlite> DELETE FROM statuses WHERE account_id='01KZWNMF7BFD5N6MAYV3068C81';
sqlite> DELETE FROM account_stats WHERE account_id='01KZWNMF7BFD5N6MAYV3068C81';
sqlite> DELETE FROM accounts WHERE id='01KZWNMF7BFD5N6MAYV3068C81';

sqlite> SELECT id, username, domain FROM accounts WHERE domain='aloy-horizon.duckdns.org';
01M0RKD5BF8JEGA7HN2A66Z54C|user1|aloy-horizon.duckdns.org

sqlite> DELETE FROM statuses WHERE account_id='01M0RKD5BF8JEGA7HN2A66Z54C';
sqlite> DELETE FROM account_stats WHERE account_id='01M0RKD5BF8JEGA7HN2A66Z54C';
sqlite> DELETE FROM accounts WHERE id='01M0RKD5BF8JEGA7HN2A66Z54C';
sqlite> DELETE FROM statuses WHERE id IN (SELECT id FROM statuses WHERE account_id NOT IN (SELECT id FROM accounts));
sqlite> .quit

-- 도커 컴포즈를 재시작합니다.
docker compose down
docker compose up -d

📁 테스트 / Test

✔️ 터미널에서 아래의 라우트로 테스트 할 수 있습니다.
You can test using the route below in the terminal.

# 팔로우 (following 1로 증가)
# Follow (following increased to 1)

curl -X POST https://aloy-horizon.duckdns.org/api/follow \
  -H "Content-Type: application/json" \
  -d '{"username":"user1","target":"https://freelifemakers.com/users/user1"}'

# 언팔로우 (following 0으로 감소)
# Unfollow (following count reduced to 0)

curl -X DELETE https://aloy-horizon.duckdns.org/api/follow \
  -H "Content-Type: application/json" \
  -d '{"username":"user1","target":"https://freelifemakers.com/users/user1"}'

# 글 쓰기 (outbox 7로 증가 + 팔로워들에게 배달)
# Writing a post (added to outbox 7 + delivered to followers)

curl -X POST https://aloy-horizon.duckdns.org/api/posts \
  -H "Content-Type: application/json" \
  -d '{"username":"user1","content":"Hello Follow! #test"}'

✔️ 팔로우 테스트 / Follow Test

–팔로우 요청을 두번하면 요청을 보내지 않습니다.
If you send a follow request twice, the request will not be sent.

 ~ % curl -X POST https://aloy-horizon.duckdns.org/api/follow \
  -H "Content-Type: application/json" \
  -d '{"username":"user1","target":"https://freelifemakers.com/users/user1"}'
{"ok":true,"inbox":"https://freelifemakers.com/users/user1/inbox","target":"https://freelifemakers.com/users/user1","username":"user1","result":"{\"status\":\"Accepted\"}","follow":{"@context":"https://www.w3.org/ns/activitystreams","id":"https://aloy-horizon.duckdns.org/users/user1/follows/1787534744543","type":"Follow","actor":"https://aloy-horizon.duckdns.org/users/user1","object":"https://freelifemakers.com/users/user1"}}%                                                                                                                       ~ % curl -X POST https://aloy-horizon.duckdns.org/api/follow \
  -H "Content-Type: application/json" \
  -d '{"username":"user1","target":"https://freelifemakers.com/users/user1"}'
{"ok":true,"alreadyFollowing":true,"target":"https://freelifemakers.com/users/user1","username":"user1","message":"이미 팔로잉 중 / Already following"}%                                                                                                                             
~ % 

✔️ 글 배달 확인 / Confirming post delivery

https://aloy-horizon.duckdns.org/@user1

요한복음 8장 32절 / John 8:32

“그리고 너희는 진리를 알게 될 것이며, 진리가 너희를 자유롭게 할 것이다.”

“Then you will know the truth ,and the truth will set you free”