[nextjs]SNS Server-28(myapp38) 

👉🏻 회원가입 및 로그인과 관련된 기능입니다.
These are features related to sign-up and login.

👉🏻 myapp38에서는 알림 기능을 구현합니다.
myapp38 implements a notification feature.

👉🏻 알림 기능은 pinafore.social을 기준으로 합니다.
The notification feature is based on pinafore.social.

👉🏻 전체 코드는 깃허브에서 확인 할 수 있습니다.
You can find the full code on GitHub.

https://github.com/gideonslife01/flm-nextjs

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

myapp project/  
├── app/  (Next.js App Router)
│   ├── .well-known/webfinger/route.ts  -> webfinger
│   ├── api/follow/route.ts    -> Follow API(temporary)
│   ├── api/announce/route.ts  -> Boost(Announcement)
│   ├── api/like/route.ts.     -> Like API
│   ├── api/posts/route.ts     -> Writing API
│   ├── api/timeline/route.ts  -> Timeline API
│   ├── api/v1/instance/route.ts -> auth
│   ├── api/v1/apps/route.ts.    -> auth
│   ├── api/v1/accounts/verify_credentials/route.ts -> auth
│   ├── api/v1/statuses/route.ts -> Write Post
│   ├── api/v1/timelines/home/route.ts -> timeline
│   ├── oauth/authorize/route.ts -> auth
│   ├── oauth/token/route.ts -> auth
│   ├── api/v1/search/home/route.ts -> search
│   ├── api/v2/search/home/route.ts -> search
│   ├── api/v1/accounts/[id]/followers/route.ts -> followers
│   ├── api/v1/accounts/[id]/following/route.ts -> following
│   ├── 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
│   │   └── _components/themes/
│   │       ├── themeex/ThemeexTheme.tsx   -> Example Theme
│   │       ├── pinafore/PinaforeTheme.tsx -> Theme 1
│   │       ├── mastodon/MastodonTheme.tsx -> Theme 2
│   │       └── minimal/MinimaltTheme.tsx  -> Theme 3
│   ├── layout.tsx, page.tsx, globals.css
│   └── favicon.ico
├── lib/
│   ├── theme.tsx              -> Theme Provider
│   ├── watchThemes.ts         -> Check real-time theme changes
│   ├── auth.ts                -> Authentication, User Management
│   ├── ap.ts                  -> Follow,Undo,Create,Likes,Announce
│   └── db.ts                  -> DB connection
├── data/
│   ├── keys/userIDs/          -> private.pem, public.pem(New)
│   └── keys/                  -> private.pem, public.pem(legacy)
├── data.sqlite                -> Database(1/3)
├── data.sqlite-wal            -> Database(2/3)
├── data.sqlite-shm            -> Database(3/3)
├── Caddyfile                  -> https 
├── instrumentation.ts         -> Background Server
└── package.json

📁 프로젝트 시작 / Project Start

[nextjs]프로젝트요약/project summary(myapp30+)

---

📁 FOLLOWERS,FOLLOWING 테이블 구조 변경
Modification of FOLLOWERS and FOLLOWING table structures

✔️ 테이블 / Table

— 테이블은 알림 확인시 날짜가 저장되지 않아 무조건 now로 표시되어 followers테이블 필드에 날짜입력 추가
The table always displays ‘now’ because the date is not saved when a notification is checked, so date input was added to the followers table field.

— 테이블 구조를 수정하면 필드가 기존 필드마지막에 추가되므로 관리와 일관성문제로 테이블 새로 생성
Since modifying the table structure adds fields to the end of existing fields, a new table is created due to management and consistency issues.

— 한줄씩 실행을 추천합니다. 한번에 실행하면 데이터 삭제 될 수 있습니다.
It is recommended to execute the commands one line at a time. Executing them all at once could result in data loss.

-- 1. 백업 / backup 
CREATE TABLE followers_backup AS SELECT * FROM followers;
CREATE TABLE following_backup AS SELECT * FROM following;

SELECT * FROM followers_backup LIMIT 2;
SELECT * FROM following_backup LIMIT 2;

-- 2. 기존 테이블 삭제 / Delete existing table
DROP TABLE followers;
DROP TABLE following;

-- 3. 동일한 스키마로 새로 생성 (필드순서 동일하게 맞춤)
-- Create new with the same schema (matching field order)
CREATE TABLE followers (
    id TEXT PRIMARY KEY,
    actor TEXT NOT NULL,
    inbox TEXT NOT NULL,
    username TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE following (
    id TEXT PRIMARY KEY,
    actor TEXT NOT NULL,
    inbox TEXT NOT NULL,
    username TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 4. 인덱스는 기존과 동일함 / The index is the same as before.
CREATE INDEX idx_followers_username ON followers(username);
CREATE INDEX idx_followers_actor ON followers(actor);
CREATE INDEX idx_followers_created ON followers(created_at DESC);
CREATE UNIQUE INDEX idx_followers_actor_username ON followers(actor, username);

CREATE INDEX idx_following_username ON following(username);
CREATE INDEX idx_following_actor ON following(actor);
CREATE INDEX idx_following_inbox ON following(inbox);
CREATE INDEX idx_following_created ON following(created_at DESC);
CREATE UNIQUE INDEX idx_following_actor_username ON following(actor, username);

-- 5. 데이터 복구 (inbox 없으면! actor + /inbox로! 추정!)
-- Data recovery! (If there's no 'inbox', assume it's 'actor' + '/inbox'!)
INSERT INTO followers (id, actor, inbox, username, created_at)
SELECT 
  id,
  actor,
  COALESCE(inbox, actor || '/inbox'),
  username,
  COALESCE(created_at, CURRENT_TIMESTAMP)
FROM followers_backup;

INSERT INTO following (id, actor, inbox, username, created_at)
SELECT 
  id,
  actor,
  COALESCE(inbox, actor || '/inbox'),
  username,
  COALESCE(created_at, CURRENT_TIMESTAMP)
FROM following_backup;

-- 6. 확인 / check
SELECT * FROM followers;
SELECT * FROM following;
.schema followers
.schema following

-- 7. 백업 테이블 삭제(확인 후)
-- Delete backup table (after verification)
DROP TABLE followers_backup;
DROP TABLE following_backup;

✔️ 인덱스 추가 / Add Index

— 테이블 구조변경으로 인덱스도 동일하게 맞추기
Adjusting indexes to match table structure changes

CREATE INDEX IF NOT EXISTS idx_followers_inbox ON followers(inbox);

📁 코드수정 / Code Modification

✔️ 테이블수정으로 코드 수정한 부분
Parts of the code modified due to table changes

app/api/v1/accounts/[id]/following/route.ts
app/api/v1/accounts/[id]/followers/route.ts
app/users/[username]/inbox/route.ts
lib/db.ts

✔️ 알림기능 / Notification feature

— app/api/v1/notifications/route.ts

// ✅ myapp38 - app/api/v1/notifications/route.ts 

import { NextResponse } from 'next/server';
import db from '@/lib/db';

const DOMAIN = process.env.DOMAIN || 'aloy-horizon.duckdns.org';

function parseActor(actor: string, idx: number) { ... }
function makeAccount(parsed: ReturnType<typeof parseActor>, idx: number, createdAt?: string) { ... }
export async function GET(req: Request) { ...
// 1. Follow 알림 / Follow Notification
try { ... } catch(e){ console.error('follow', e); }

// 2. Like 알림 / Like Notification
try { ... } catch(e){ console.error('likes notif', e); }

// 3. Reblog 알림 / Reblog(boost) Notification
try { ... }catch(e){}
}
... ...

📁 테스트 / Test

✔️ 알림 라우트 터미널 테스트 / Notification Route Terminal Test

curl "https://aloy-horizon.duckdns.org/api/v1/notifications?limit=20"

# 터미널 / Terminal
% curl "https://aloy-horizon.duckdns.org/api/v1/notifications?limit=20"

# 응답 / Response

[{"id":"follow_0_https://mastodon.social/b7cc4c41-b22a-43b6-9df8-5bb46b2222fd","type":"follow","created_at":"2026-09-10T23:25:07.353Z","account":{"id":"notif_acct_mastodon_social_117193449750714993_0","username":"117193449750714993","acct":"117193449750714993@mastodon.social","display_name":"117193449750714993","avatar":"https://aloy-horizon.duckdns.org/icon.png", ... ... }]

✔️ 팔로워 알림 / Followers Notification

✔️ 부스트 알림 / Boost Notification

✔️ 좋아요 알림 / Likes Notification

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

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

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