UVF IT: Basic Social Media Scheduler Clone with TypeScript & SQLite

Welcome to another official UVF IT guide! In this tutorial we’ll build a tiny clone of Postiz – a social media scheduler – using TypeScript, Express, and SQLite. The real Postiz talks to dozens of platforms; our version stores scheduled posts locally and pretends to publish them on demand.

You’ll get a working REST API and a tiny HTML UI in under an hour, all on free‑tier tools. No paid APIs, no cloud accounts – just Node.js and a local database.

Before you start

Node.js >=18 and npm are required; older versions may break ES‑module imports.
We replace real social‑network APIs with a mock “publish” endpoint – no actual posting will occur.
SQLite is used for simplicity; it lacks the scaling features of Postiz’s cloud DB.

Clone Architecture

Phase 1: Scaffold Project

We start by creating a fresh npm workspace that uses TypeScript and Express. This gives us a clean folder structure, type‑checking, and a lightweight web server to expose our API.

The commands below initialize the repo, add the necessary runtime and dev dependencies, and generate a minimal tsconfig.json so TypeScript knows how to compile our code.

mkdir postiz-clone && cd postiz-clone
npm init -y
npm install express
npm install -D typescript @types/node @types/express ts-node-dev
npx tsc --init --rootDir src --outDir dist --esModuleInterop true --module commonjs --target es2022
mkdir src && touch src/index.ts

Phase 2: Data Model & SQLite Setup

Next we add a tiny SQLite wrapper that creates a posts table and provides helper functions to insert and fetch scheduled posts. SQLite runs in‑process, so there’s no external service to start – perfect for a quick prototype.

The file src/db.ts will be imported by our Express routes later, keeping all database logic in one place and making future extensions straightforward.

src/db.ts

import Database from 'better-sqlite3';

// Open (or create) a local SQLite file
const db = new Database('scheduler.db');

// Ensure the posts table exists
const init = `
CREATE TABLE IF NOT EXISTS posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL,
  scheduled_at TEXT NOT NULL,
  posted INTEGER DEFAULT 0
);
`;
db.exec(init);

export interface Post {
  id?: number;
  content: string;
  scheduled_at: string; // ISO string
  posted?: number;
}

export function addPost(post: Post): number {
  const stmt = db.prepare('INSERT INTO posts (content, scheduled_at, posted) VALUES (?, ?, ?)');
  const info = stmt.run(post.content, post.scheduled_at, post.posted ?? 0);
  return info.lastInsertRowid as number;
}

export function getPendingPosts(): Post[] {
  const stmt = db.prepare('SELECT * FROM posts WHERE posted = 0 ORDER BY scheduled_at ASC');
  return stmt.all() as Post[];
}

export function markAsPosted(id: number): void {
  const stmt = db.prepare('UPDATE posts SET posted = 1 WHERE id = ?');
  stmt.run(id);
}

Phase 3: API Endpoints & Mock Publish

In this step we expose a tiny REST API that the UI can talk to. The /posts endpoint lets us read all scheduled items and add new ones, while /publish/:id pretends to push a post to a social network by just logging a message. We store the schedule in an in‑memory SQLite database so the data survives for the life of the process, keeping the clone simple yet realistic.

We use Express for routing and the sqlite3 package for a lightweight SQL store. All routes return JSON so the front‑end can update itself without a page reload. The mock publish simply updates the published flag and prints a console line, which you’ll see when you run the app.

src/server.ts

import express from 'express';
import bodyParser from 'body-parser';
import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

const app = express();
app.use(bodyParser.json());
app.use(express.static('public'));

// Initialise an in‑memory SQLite DB with a simple table
async function initDb() {
  const db = await open({ filename: ':memory:', driver: sqlite3.Database });
  await db.exec(`CREATE TABLE posts (id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT NOT NULL, schedule TEXT NOT NULL, published INTEGER NOT NULL DEFAULT 0);`);
  return db;
}

(async () => {
  const db = await initDb();

  // List all scheduled posts
  app.get('/posts', async (req, res) => {
    const rows = await db.all('SELECT * FROM posts ORDER BY id DESC');
    res.json(rows);
  });

  // Create a new schedule
  app.post('/posts', async (req, res) => {
    const { content, schedule } = req.body;
    if (!content || !schedule) return res.status(400).json({error:'Missing fields'});
    const result = await db.run('INSERT INTO posts (content, schedule) VALUES (?, ?)', content, schedule);
    const newPost = await db.get('SELECT * FROM posts WHERE id = ?', result.lastID);
    res.status(201).json(newPost);
  });

  // Mock publish a post
  app.post('/publish/:id', async (req, res) => {
    const { id } = req.params;
    const post = await db.get('SELECT * FROM posts WHERE id = ?', id);
    if (!post) return res.status(404).json({error:'Not found'});
    await db.run('UPDATE posts SET published = 1 WHERE id = ?', id);
    console.log(`🔔 Mock publish: "${post.content}" scheduled for ${post.schedule}`);
    res.json({message:'Published', post: {...post, published:1}});
  });

  const PORT = 3000;
  app.listen(PORT, () => console.log(`Server running on http://localhost:${PORT}`));
})();

Phase 4: Tiny UI to Schedule Posts

Now we build a single HTML page that talks to the API we just created. The page contains a small form for the post content and a datetime picker, plus a list that shows all scheduled items. When you click “Schedule” the form data is POSTed to /posts; each item in the list gets a “Publish” button that triggers the mock publish endpoint.

All interactions use the native fetch API, keeping the front‑end framework‑free. After any change we refresh the list so you can see the updated state immediately, which is perfect for a quick proof‑of‑concept.

public/index.html

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8" />
  <title>UVF IT Scheduler Clone</title>
  <style>
    body{font-family:sans-serif;margin:2rem;}
    .post{border:1px solid #ccc;padding:0.5rem;margin:0.5rem 0;}
    button{margin-left:0.5rem;}
  </style>
</head>
<body>
  <h1>Schedule a Post</h1>
  <form id="scheduleForm">
    <textarea id="content" rows="3" placeholder="Post content" required></textarea><br/>
    <input type="datetime-local" id="schedule" required />
    <button type="submit">Schedule</button>
  </form>
  <h2>Scheduled Items</h2>
  <div id="posts"></div>

  <script>
    async function loadPosts(){
      const res = await fetch('/posts');
      const posts = await res.json();
      const container = document.getElementById('posts');
      container.innerHTML = '';
      posts.forEach(p=>{
        const div = document.createElement('div');
        div.className='post';
        div.innerHTML = `<strong>${p.content}</strong><br/>When: ${p.schedule}<br/>Status: ${p.published?"✅ Published":"⏳ Pending`}
          + `<button data-id="${p.id}" ${p.published?'disabled':''}>Publish</button>`;
        container.appendChild(div);
        div.querySelector('button').addEventListener('click', async()=>{
          await fetch(`/publish/${p.id}`,{method:'POST'});
          loadPosts();
        });
      });
    }

    document.getElementById('scheduleForm').addEventListener('submit', async e=>{
      e.preventDefault();
      const content = document.getElementById('content').value;
      const schedule = document.getElementById('schedule').value;
      await fetch('/posts',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({content,schedule})});
      e.target.reset();
      loadPosts();
    });

    loadPosts();
  </script>
</body>
</html>

Phase 5: Run & Verify Locally

With the server and UI in place, it’s time to launch the clone. We use ts-node so you don’t need a separate compile step—just run the TypeScript file directly. Once the server is listening, open a browser to http://localhost:3000 and you’ll see the scheduling form.

Create a post, hit “Schedule”, and the list should update. Then click the “Publish” button next to the entry; the server will log a mock‑publish line and the UI will mark the item as published. This confirms the full round‑trip from front‑end to back‑end works.

npm install express body-parser sqlite3 sqlite ts-node @types/express @types/node
npm install -D typescript @types/body-parser
npx ts-node src/server.ts

Verifying the Stack

Check	Command / Why it matters
Server starts without errors	Run `npx ts-node src/server.ts` and see "Server running on http://localhost:3000" in the console.
UI loads in browser	Open http://localhost:3000 in Chrome; the page shows the form and an empty list.
Can schedule and publish a post	Enter content & datetime, click Schedule, then click Publish; the list updates and console shows mock publish message.

What’s Next?

You now have a functional scheduler core. Extend it by adding user auth, persisting SQLite to a file, or integrating real social‑network APIs using OAuth tokens.