Deploying Framadate

Introduction

Framadate is a free and open-source online service for planning appointments or making decisions quickly and easily. Created by Framasoft, a French non-profit organization promoting free software, Framadate is a privacy-respecting alternative to proprietary tools like Doodle. Built with PHP and designed with simplicity in mind, Framadate enables groups to collaboratively find the best time for meetings, events, or activities without requiring participants to create accounts or share personal information.

Framadate is known for:

Privacy-First Design: No tracking, no ads, no data collection - participants remain anonymous
Two Poll Types: Date polls for scheduling meetings and classic polls for making group decisions
No Account Required: Create and participate in polls without registration
Multi-Language Support: Available in 20+ languages including English, French, German, Spanish, Italian
Deadline Management: Set expiration dates for polls to maintain relevance
Vote Modification: Participants can update their responses until the poll closes
Comment System: Built-in discussion threads for each poll
Export Capabilities: Export results to CSV or iCal formats
Email Notifications: Optional email alerts for poll creators and participants
Mobile Responsive: Works seamlessly on smartphones and tablets
Lightweight: Minimal server requirements with efficient PHP architecture
Open Source: Fully auditable AGPLv3 licensed codebase
Self-Hosted: Complete control over your data and infrastructure
GDPR Compliant: Designed with European privacy standards in mind

Common use cases include team meeting scheduling, event planning, workshop coordination, volunteer shift scheduling, group decision-making, conference room booking, community organizing, project milestone selection, and any scenario requiring collaborative scheduling or voting without creating accounts.

This comprehensive guide walks you through deploying Framadate on Klutch.sh using Docker, including MySQL/MariaDB database setup, persistent storage configuration, email integration, environment variables, and production-ready best practices.

Why Deploy Framadate on Klutch.sh?

Simplified Deployment: Launch Framadate without managing servers or infrastructure
Persistent Storage: Reliable volume storage for database data and uploaded files
Custom Domains: Use your own domain for your Framadate instance
Environment Variables: Secure configuration management through Klutch.sh
Automatic HTTPS: Built-in SSL certificates for secure access
Easy Scaling: Adjust resources as your polling needs grow
Zero Downtime Updates: Deploy changes without service interruption
Container Benefits: Isolated, reproducible environment with version control
Backup Support: Easy database backup and disaster recovery
Privacy Control: Host your own polling service with complete data sovereignty

Prerequisites

Before you begin, ensure you have:

A Klutch.sh account
A GitHub account with a repository for your Framadate project
Basic understanding of PHP applications and Docker
(Optional) MySQL/MariaDB database deployed on Klutch.sh - see our MySQL deployment guide
(Optional) SMTP server credentials for email notifications

Preparing Your Repository

Step 1: Create Project Directory

Create a new directory for your Framadate deployment:

mkdir framadate-klutch
cd framadate-klutch

Step 2: Create the Dockerfile

Create a Dockerfile in your project root. Framadate doesn’t provide official Docker images, so we’ll build a custom one:

FROM php:8.1-apache

# Install system dependencies and PHP extensions
RUN apt-get update && apt-get install -y \
    git \
    curl \
    libpng-dev \
    libonig-dev \
    libxml2-dev \
    libzip-dev \
    zip \
    unzip \
    gettext \
    && docker-php-ext-install pdo_mysql mysqli mbstring exif pcntl bcmath gd gettext zip \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

# Enable Apache modules
RUN a2enmod rewrite headers

# Set working directory
WORKDIR /var/www/html

# Clone Framadate from official repository
RUN git clone https://framagit.org/framasoft/framadate/framadate.git . \
    && git checkout 1.2.6

# Create necessary directories
RUN mkdir -p tpl_c \
    && mkdir -p app/inc/data \
    && chown -R www-data:www-data /var/www/html \
    && chmod -R 755 /var/www/html

# Copy configuration script
COPY configure.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/configure.sh

# Expose port 80
EXPOSE 80

# Health check
HEALTHCHECK --interval=30s --timeout=10s --start-period=40s --retries=3 \
    CMD curl -f http://localhost/ || exit 1

# Run configuration script and start Apache
CMD ["/usr/local/bin/configure.sh"]

Step 3: Create Configuration Script

Create configure.sh to handle database setup and configuration:

#!/bin/bash
set -e

CONFIG_FILE="/var/www/html/app/inc/config.php"

echo "Configuring Framadate..."

# Wait for database to be ready
if [ ! -z "$DB_HOST" ]; then
    echo "Waiting for database to be ready..."
    for i in {1..30}; do
        if mysql -h"$DB_HOST" -P"${DB_PORT:-3306}" -u"$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1" >/dev/null 2>&1; then
            echo "Database is ready!"
            break
        fi
        echo "Waiting for database... attempt $i"
        sleep 2
    done
fi

# Create configuration file from template
cat > "$CONFIG_FILE" << 'EOF'
<?php
// Database configuration
const DB_CONNECTION_STRING = 'mysql:host=DB_HOST;port=DB_PORT;dbname=DB_NAME;charset=utf8';
const DB_USER = 'DB_USER_PLACEHOLDER';
const DB_PASSWORD = 'DB_PASSWORD_PLACEHOLDER';

// General configuration
const APP_URL = 'APP_URL_PLACEHOLDER';
const IMAGE_TITRE = 'images/logo.png';
const NOMAPPLICATION = 'SITE_NAME_PLACEHOLDER';
const DEFAULT_LANGUAGE = 'DEFAULT_LANG';
const DEFAULT_MAIL_DOMAIN = 'MAIL_DOMAIN';

// Security
const USE_REMOTE_USER = true;
const LOG_FILE = 'admin/stdout.log';

// Features
const ALLOW_COMMENTS = true;
const ALLOW_MAILSCHANGE = true;
const EDITABLE_FIRSTNAME = true;
const EDITABLE_MAIL = true;

// Limits
const MAX_NAME_LENGTH = 64;
const MAX_EMAIL_LENGTH = 128;
const MAX_SLOTS_PER_POLL = 366;

// Purge settings (days before poll expiration)
const PURGE_DELAY = 60;

// Time zone
date_default_timezone_set('TIMEZONE_PLACEHOLDER');
EOF

# Replace placeholders with environment variables
sed -i "s|DB_HOST|${DB_HOST:-localhost}|g" "$CONFIG_FILE"
sed -i "s|DB_PORT|${DB_PORT:-3306}|g" "$CONFIG_FILE"
sed -i "s|DB_NAME|${DB_NAME:-framadate}|g" "$CONFIG_FILE"
sed -i "s|DB_USER_PLACEHOLDER|${DB_USER:-framadate}|g" "$CONFIG_FILE"
sed -i "s|DB_PASSWORD_PLACEHOLDER|${DB_PASSWORD:-changeme}|g" "$CONFIG_FILE"
sed -i "s|APP_URL_PLACEHOLDER|${APP_URL:-http://localhost}|g" "$CONFIG_FILE"
sed -i "s|SITE_NAME_PLACEHOLDER|${SITE_NAME:-Framadate}|g" "$CONFIG_FILE"
sed -i "s|DEFAULT_LANG|${DEFAULT_LANGUAGE:-en}|g" "$CONFIG_FILE"
sed -i "s|MAIL_DOMAIN|${MAIL_DOMAIN:-example.com}|g" "$CONFIG_FILE"
sed -i "s|TIMEZONE_PLACEHOLDER|${TIMEZONE:-UTC}|g" "$CONFIG_FILE"

echo "Configuration file created successfully!"

# Initialize database if needed
if [ "$AUTO_INIT_DB" = "true" ]; then
    echo "Initializing database..."
    mysql -h"$DB_HOST" -P"${DB_PORT:-3306}" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < install/sql/mysql.sql || echo "Database already initialized"
fi

# Set proper permissions
chown -R www-data:www-data /var/www/html
chmod -R 755 /var/www/html

echo "Starting Apache..."
exec apache2-foreground

Step 4: Create Database Schema

Create a schema.sql file for manual database initialization:

-- Framadate Database Schema
-- Run this manually in your MySQL database or set AUTO_INIT_DB=true

CREATE TABLE IF NOT EXISTS `fd_comment` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `poll_id` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `comment` text,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fd_poll` (
  `id` varchar(64) NOT NULL,
  `admin_id` varchar(64) NOT NULL,
  `title` text,
  `description` text,
  `admin_name` varchar(64) DEFAULT NULL,
  `admin_mail` varchar(128) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` timestamp NULL DEFAULT NULL,
  `format` varchar(2) DEFAULT NULL,
  `editable` tinyint(1) DEFAULT '1',
  `receiveNewVotes` tinyint(1) DEFAULT '0',
  `receiveNewComments` tinyint(1) DEFAULT '0',
  `hidden` tinyint(1) DEFAULT '0',
  `password_hash` varchar(255) DEFAULT NULL,
  `results_publicly_visible` tinyint(1) DEFAULT '1',
  `ValueMax` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fd_slot` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `poll_id` varchar(64) NOT NULL,
  `title` text,
  `moments` text,
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fd_vote` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `poll_id` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  `choices` text NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqId` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`),
  KEY `uniqId` (`uniqId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add foreign key constraints
ALTER TABLE `fd_comment`
  ADD CONSTRAINT `fd_comment_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `fd_poll` (`id`) ON DELETE CASCADE;

ALTER TABLE `fd_slot`
  ADD CONSTRAINT `fd_slot_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `fd_poll` (`id`) ON DELETE CASCADE;

ALTER TABLE `fd_vote`
  ADD CONSTRAINT `fd_vote_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `fd_poll` (`id`) ON DELETE CASCADE;

Step 5: Create Environment Configuration

Create a .env.example file for environment variable reference:

# Database Configuration
DB_HOST=mysql-db.klutch.sh
DB_PORT=8000
DB_NAME=framadate
DB_USER=framadate
DB_PASSWORD=your_secure_password_here

# Application Configuration
APP_URL=https://example-app.klutch.sh
SITE_NAME=Framadate
DEFAULT_LANGUAGE=en
MAIL_DOMAIN=example.com
TIMEZONE=UTC

# Database Initialization
AUTO_INIT_DB=false

# Feature Flags (optional)
# ALLOW_COMMENTS=true
# ALLOW_MAILSCHANGE=true
# PURGE_DELAY=60

Step 6: Create .gitignore

Create a .gitignore file to exclude sensitive files:

# Environment files
.env
.env.local
.env.production

# Database files
*.sql
!schema.sql

# Framadate data (local testing)
tpl_c/
app/inc/data/

# Logs
*.log

# OS files
.DS_Store
Thumbs.db

Step 7: Create README

Create a README.md file with deployment instructions:

# Framadate on Klutch.sh

This repository contains the Docker configuration for deploying Framadate on Klutch.sh.

## Features

- Privacy-respecting poll and scheduling platform
- No account required for participants
- Date polls and classic polls
- Email notifications
- Mobile responsive
- Multi-language support

## Prerequisites

1. MySQL/MariaDB database deployed on Klutch.sh
2. Database initialized with schema.sql

## Deployment

1. Deploy MySQL database first (see Klutch.sh MySQL guide)
2. Initialize database with schema.sql
3. Push this repository to GitHub
4. Create a new app on Klutch.sh
5. Connect your GitHub repository
6. Configure environment variables
7. Deploy!

## Environment Variables

See `.env.example` for all available configuration options.

## Database Setup

Connect to your MySQL database and run:

```sql
CREATE DATABASE framadate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'framadate'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON framadate.* TO 'framadate'@'%';
FLUSH PRIVILEGES;

Then import the schema:

mysql -h your-db.klutch.sh -P 8000 -u framadate -p framadate < schema.sql

Access

After deployment, access Framadate at: https://your-app.klutch.sh

Languages

Framadate supports: English, French, German, Spanish, Italian, and 15+ more languages. Set DEFAULT_LANGUAGE to: en, fr, de, es, it, etc.

### Step 8: Initialize Git Repository

```bash
git init
git add .
git commit -m "Initial Framadate deployment configuration"

Push to GitHub:

git remote add origin https://github.com/yourusername/framadate-klutch.git
git branch -M main
git push -u origin main

Deploying on Klutch.sh

Part 1: Deploy MySQL Database

Deploy MySQL First
Follow our MySQL deployment guide to set up your database. Make note of:
- Database host (e.g., mysql-db.klutch.sh)
- Database port (usually 8000)
- Database credentials

Create Database and User
Connect to your MySQL database and run:

CREATE DATABASE framadate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'framadate'@'%' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON framadate.* TO 'framadate'@'%';
FLUSH PRIVILEGES;

Initialize Database Schema
Import the Framadate schema:

mysql -h your-mysql-db.klutch.sh -P 8000 -u framadate -p framadate < schema.sql

Part 2: Deploy Framadate Application

Log in to Klutch.sh
Navigate to klutch.sh/app and sign in to your account.
Create a New Project
Click New Project, give it a name (e.g., "Framadate Polling"), and create the project.
Create a New App
Inside your project, click New App and select your GitHub repository containing the Framadate Dockerfile.
Configure Traffic Type
In the app settings, select HTTP traffic since Framadate runs a web server on port 80.

Set Environment Variables
Add the following environment variables in the Klutch.sh dashboard:

# Database Configuration (REQUIRED)
DB_HOST=your-mysql-db.klutch.sh
DB_PORT=8000
DB_NAME=framadate
DB_USER=framadate
DB_PASSWORD=your_database_password

# Application Configuration (REQUIRED)
APP_URL=https://your-framadate-app.klutch.sh
SITE_NAME=Framadate
DEFAULT_LANGUAGE=en
MAIL_DOMAIN=your-domain.com
TIMEZONE=America/New_York

# Database Initialization (Optional - only for first deployment)
AUTO_INIT_DB=false

Security Note: Never commit database credentials to your repository. Always set them as environment variables in Klutch.sh.

Deploy the App
Click Deploy. Klutch.sh will automatically detect the Dockerfile and build your Framadate container.
Access Your Framadate Instance
Once deployed, access Framadate at https://your-app-name.klutch.sh. You can now create polls and share them with participants!

Configuration

Application Settings

Configure Framadate behavior through environment variables:

Core Configuration:

APP_URL: Public URL for your Framadate instance (used in emails and links)
SITE_NAME: Display name shown in the interface (default: “Framadate”)
DEFAULT_LANGUAGE: Interface language - en, fr, de, es, it, etc.
MAIL_DOMAIN: Domain used for generating poll notification emails
TIMEZONE: Server timezone (e.g., UTC, America/New_York, Europe/Paris)

Database Configuration:

DB_HOST: MySQL database hostname
DB_PORT: MySQL port (typically 8000 on Klutch.sh)
DB_NAME: Database name (default: framadate)
DB_USER: Database username
DB_PASSWORD: Database password

Optional Features:

AUTO_INIT_DB: Set to true to automatically initialize database schema on first run

Language Support

Framadate supports 20+ languages. Set DEFAULT_LANGUAGE to:

en - English
fr - French
de - German
es - Spanish
it - Italian
pt - Portuguese
nl - Dutch
br - Brazilian Portuguese
ca - Catalan
oc - Occitan

Timezone Configuration

Common timezone values:

# Americas
TIMEZONE=America/New_York
TIMEZONE=America/Los_Angeles
TIMEZONE=America/Chicago
TIMEZONE=America/Toronto

# Europe
TIMEZONE=Europe/London
TIMEZONE=Europe/Paris
TIMEZONE=Europe/Berlin
TIMEZONE=Europe/Amsterdam

# Asia
TIMEZONE=Asia/Tokyo
TIMEZONE=Asia/Singapore
TIMEZONE=Asia/Hong_Kong

# Australia
TIMEZONE=Australia/Sydney
TIMEZONE=Australia/Melbourne

Email Configuration

For email notifications, you’ll need to configure SMTP settings. Create a custom configuration file or use environment variables:

# Add to configure.sh for SMTP support
SMTP_HOST=smtp.example.com
SMTP_PORT=587
SMTP_USER=noreply@example.com
SMTP_PASSWORD=your_smtp_password

Security Settings

Enhance security with these configurations:

Database Security: Use strong passwords and restrict database access
HTTPS Only: Always use HTTPS for production deployments
Poll Password Protection: Enable password-protected polls for sensitive scheduling
Rate Limiting: Consider adding rate limiting to prevent abuse

Sample Usage

Creating a Date Poll

Use Framadate’s web interface to create scheduling polls:

Visit your Framadate instance at https://your-app.klutch.sh
Click Create a date poll
Enter poll details:
- Poll title (e.g., "Team Meeting - Project Kickoff")
- Your name and email (optional)
- Description of the meeting or event
Select date and time options:
- Add multiple date options
- For each date, add time slots
- Set poll expiration date
Configure poll settings:
- Allow comments
- Allow participants to modify their votes
- Email notifications for new votes
Create the poll and share the link with participants

Creating a Classic Poll

For non-date decisions (e.g., choosing a venue or option):

Click Create a classic poll
Enter poll information
Add multiple choice options
Configure voting settings (Yes/No, Yes/Maybe/No)
Share the poll link

Using PHP to Create Polls Programmatically

While Framadate is primarily web-based, you can interact with the database directly:

<?php
// Connect to Framadate database
$db = new PDO(
    'mysql:host=your-db.klutch.sh;port=8000;dbname=framadate',
    'framadate',
    'your_password'
);

// Create a new poll
function createPoll($db, $title, $adminName, $adminEmail) {
    $pollId = generatePollId();
    $adminId = generateAdminId();

    $stmt = $db->prepare("
        INSERT INTO fd_poll (id, admin_id, title, admin_name, admin_mail, format, creation_date)
        VALUES (?, ?, ?, ?, ?, 'D', NOW())
    ");

    $stmt->execute([$pollId, $adminId, $title, $adminName, $adminEmail]);

    return [
        'poll_id' => $pollId,
        'admin_id' => $adminId,
        'admin_url' => "https://your-app.klutch.sh/admin/{$pollId}/{$adminId}",
        'public_url' => "https://your-app.klutch.sh/{$pollId}"
    ];
}

// Add time slots to poll
function addSlots($db, $pollId, $dates) {
    $stmt = $db->prepare("
        INSERT INTO fd_slot (poll_id, title, moments)
        VALUES (?, ?, ?)
    ");

    foreach ($dates as $date => $times) {
        $moments = implode(',', $times);
        $stmt->execute([$pollId, $date, $moments]);
    }
}

// Generate unique IDs
function generatePollId() {
    return bin2hex(random_bytes(8));
}

function generateAdminId() {
    return bin2hex(random_bytes(12));
}

// Usage example
$poll = createPoll(
    $db,
    "Team Meeting - Q1 Planning",
    "John Doe",
    "john@example.com"
);

addSlots($db, $poll['poll_id'], [
    '2025-01-15' => ['09:00', '10:00', '14:00', '15:00'],
    '2025-01-16' => ['09:00', '10:00', '14:00', '15:00'],
    '2025-01-17' => ['09:00', '10:00', '14:00']
]);

echo "Poll created!\n";
echo "Public URL: {$poll['public_url']}\n";
echo "Admin URL: {$poll['admin_url']}\n";

Using Python to Fetch Poll Results

import mysql.connector
from datetime import datetime

class FramadateAPI:
    def __init__(self, host, port, database, user, password):
        self.conn = mysql.connector.connect(
            host=host,
            port=port,
            database=database,
            user=user,
            password=password
        )
        self.cursor = self.conn.cursor(dictionary=True)

    def get_poll(self, poll_id):
        """Fetch poll details"""
        self.cursor.execute(
            "SELECT * FROM fd_poll WHERE id = %s",
            (poll_id,)
        )
        return self.cursor.fetchone()

    def get_slots(self, poll_id):
        """Fetch poll time slots"""
        self.cursor.execute(
            "SELECT * FROM fd_slot WHERE poll_id = %s ORDER BY id",
            (poll_id,)
        )
        return self.cursor.fetchall()

    def get_votes(self, poll_id):
        """Fetch all votes for a poll"""
        self.cursor.execute(
            "SELECT * FROM fd_vote WHERE poll_id = %s ORDER BY date",
            (poll_id,)
        )
        return self.cursor.fetchall()

    def get_comments(self, poll_id):
        """Fetch poll comments"""
        self.cursor.execute(
            "SELECT * FROM fd_comment WHERE poll_id = %s ORDER BY date",
            (poll_id,)
        )
        return self.cursor.fetchall()

    def get_poll_summary(self, poll_id):
        """Get complete poll summary with votes"""
        poll = self.get_poll(poll_id)
        slots = self.get_slots(poll_id)
        votes = self.get_votes(poll_id)
        comments = self.get_comments(poll_id)

        return {
            'poll': poll,
            'slots': slots,
            'votes': votes,
            'comments': comments,
            'vote_count': len(votes),
            'comment_count': len(comments)
        }

    def close(self):
        self.cursor.close()
        self.conn.close()

# Usage
api = FramadateAPI(
    host='your-db.klutch.sh',
    port=8000,
    database='framadate',
    user='framadate',
    password='your_password'
)

# Get poll summary
summary = api.get_poll_summary('abc123def456')

print(f"Poll: {summary['poll']['title']}")
print(f"Created: {summary['poll']['creation_date']}")
print(f"Total votes: {summary['vote_count']}")
print(f"Comments: {summary['comment_count']}")

print("\nTime slots:")
for slot in summary['slots']:
    print(f"  {slot['title']}: {slot['moments']}")

print("\nVotes:")
for vote in summary['votes']:
    print(f"  {vote['name']}: {vote['choices']}")

api.close()

Using Node.js to Monitor Polls

const mysql = require('mysql2/promise');

class FramadateMonitor {
    constructor(config) {
        this.config = config;
    }

    async connect() {
        this.connection = await mysql.createConnection({
            host: this.config.host,
            port: this.config.port,
            database: this.config.database,
            user: this.config.user,
            password: this.config.password
        });
    }

    async getActivePolls() {
        const [rows] = await this.connection.execute(`
            SELECT id, title, admin_name, creation_date, end_date,
                   (SELECT COUNT(*) FROM fd_vote WHERE poll_id = fd_poll.id) as vote_count,
                   (SELECT COUNT(*) FROM fd_comment WHERE poll_id = fd_poll.id) as comment_count
            FROM fd_poll
            WHERE end_date > NOW() OR end_date IS NULL
            ORDER BY creation_date DESC
        `);
        return rows;
    }

    async getRecentVotes(hours = 24) {
        const [rows] = await this.connection.execute(`
            SELECT v.*, p.title as poll_title
            FROM fd_vote v
            JOIN fd_poll p ON v.poll_id = p.id
            WHERE v.date > DATE_SUB(NOW(), INTERVAL ? HOUR)
            ORDER BY v.date DESC
        `, [hours]);
        return rows;
    }

    async getPollStatistics(pollId) {
        const [poll] = await this.connection.execute(
            'SELECT * FROM fd_poll WHERE id = ?',
            [pollId]
        );

        const [slots] = await this.connection.execute(
            'SELECT * FROM fd_slot WHERE poll_id = ?',
            [pollId]
        );

        const [votes] = await this.connection.execute(
            'SELECT * FROM fd_vote WHERE poll_id = ?',
            [pollId]
        );

        // Calculate vote distribution
        const voteMatrix = {};
        votes.forEach(vote => {
            const choices = vote.choices.split('');
            choices.forEach((choice, index) => {
                if (!voteMatrix[index]) {
                    voteMatrix[index] = { yes: 0, no: 0, maybe: 0 };
                }
                if (choice === '2') voteMatrix[index].yes++;
                else if (choice === '1') voteMatrix[index].maybe++;
                else if (choice === '0') voteMatrix[index].no++;
            });
        });

        return {
            poll: poll[0],
            slots: slots,
            totalVotes: votes.length,
            voteDistribution: voteMatrix
        };
    }

    async close() {
        if (this.connection) {
            await this.connection.end();
        }
    }
}

// Usage
(async () => {
    const monitor = new FramadateMonitor({
        host: 'your-db.klutch.sh',
        port: 8000,
        database: 'framadate',
        user: 'framadate',
        password: 'your_password'
    });

    await monitor.connect();

    // Get active polls
    const activePolls = await monitor.getActivePolls();
    console.log('Active Polls:');
    activePolls.forEach(poll => {
        console.log(`  ${poll.title}: ${poll.vote_count} votes, ${poll.comment_count} comments`);
    });

    // Get recent votes
    const recentVotes = await monitor.getRecentVotes(24);
    console.log(`\nVotes in last 24 hours: ${recentVotes.length}`);

    // Get poll statistics
    if (activePolls.length > 0) {
        const stats = await monitor.getPollStatistics(activePolls[0].id);
        console.log(`\nStatistics for "${stats.poll.title}":`);
        console.log(`  Total participants: ${stats.totalVotes}`);
        console.log(`  Time slots: ${stats.slots.length}`);
    }

    await monitor.close();
})();

Using Go to Export Poll Data

package main

import (
    "database/sql"
    "encoding/csv"
    "fmt"
    "log"
    "os"
    "strings"

    _ "github.com/go-sql-driver/mysql"
)

type Poll struct {
    ID          string
    Title       string
    AdminName   string
    Format      string
    CreatedAt   string
}

type Slot struct {
    ID       int
    PollID   string
    Title    string
    Moments  string
}

type Vote struct {
    ID      int
    PollID  string
    Name    string
    Choices string
    Date    string
}

type FramadateExporter struct {
    db *sql.DB
}

func NewFramadateExporter(dsn string) (*FramadateExporter, error) {
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

    if err := db.Ping(); err != nil {
        return nil, err
    }

    return &FramadateExporter{db: db}, nil
}

func (fe *FramadateExporter) GetPoll(pollID string) (*Poll, error) {
    var poll Poll
    err := fe.db.QueryRow(`
        SELECT id, title, admin_name, format, creation_date
        FROM fd_poll WHERE id = ?
    `, pollID).Scan(&poll.ID, &poll.Title, &poll.AdminName, &poll.Format, &poll.CreatedAt)

    return &poll, err
}

func (fe *FramadateExporter) GetSlots(pollID string) ([]Slot, error) {
    rows, err := fe.db.Query(`
        SELECT id, poll_id, title, moments
        FROM fd_slot WHERE poll_id = ? ORDER BY id
    `, pollID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var slots []Slot
    for rows.Next() {
        var slot Slot
        if err := rows.Scan(&slot.ID, &slot.PollID, &slot.Title, &slot.Moments); err != nil {
            return nil, err
        }
        slots = append(slots, slot)
    }

    return slots, nil
}

func (fe *FramadateExporter) GetVotes(pollID string) ([]Vote, error) {
    rows, err := fe.db.Query(`
        SELECT id, poll_id, name, choices, date
        FROM fd_vote WHERE poll_id = ? ORDER BY date
    `, pollID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var votes []Vote
    for rows.Next() {
        var vote Vote
        if err := rows.Scan(&vote.ID, &vote.PollID, &vote.Name, &vote.Choices, &vote.Date); err != nil {
            return nil, err
        }
        votes = append(votes, vote)
    }

    return votes, nil
}

func (fe *FramadateExporter) ExportToCSV(pollID, filename string) error {
    poll, err := fe.GetPoll(pollID)
    if err != nil {
        return err
    }

    slots, err := fe.GetSlots(pollID)
    if err != nil {
        return err
    }

    votes, err := fe.GetVotes(pollID)
    if err != nil {
        return err
    }

    file, err := os.Create(filename)
    if err != nil {
        return err
    }
    defer file.Close()

    writer := csv.NewWriter(file)
    defer writer.Flush()

    // Write header
    header := []string{"Participant"}
    for _, slot := range slots {
        moments := strings.Split(slot.Moments, ",")
        for _, moment := range moments {
            header = append(header, fmt.Sprintf("%s %s", slot.Title, moment))
        }
    }
    writer.Write(header)

    // Write votes
    for _, vote := range votes {
        row := []string{vote.Name}
        choices := strings.Split(vote.Choices, "")

        for _, choice := range choices {
            switch choice {
            case "2":
                row = append(row, "Yes")
            case "1":
                row = append(row, "Maybe")
            case "0":
                row = append(row, "No")
            default:
                row = append(row, "")
            }
        }
        writer.Write(row)
    }

    return nil
}

func (fe *FramadateExporter) Close() error {
    return fe.db.Close()
}

func main() {
    dsn := "framadate:your_password@tcp(your-db.klutch.sh:8000)/framadate"
    exporter, err := NewFramadateExporter(dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer exporter.Close()

    pollID := "abc123def456"

    // Export to CSV
    err = exporter.ExportToCSV(pollID, "poll_results.csv")
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Poll exported successfully to poll_results.csv")
}

Production Best Practices

Security Hardening

Database Security

# Use strong, unique passwords
DB_PASSWORD=$(openssl rand -base64 32)

# Restrict database access to app IP only
# Configure in MySQL: GRANT ALL ON framadate.* TO 'framadate'@'app-ip-address'

HTTPS Only
- Use Klutch.sh’s automatic SSL certificates
- Set APP_URL to use https://
- Consider HTTP Strict Transport Security (HSTS)
Rate Limiting
- Add Nginx rate limiting to prevent poll spam
- Implement CAPTCHA for poll creation

Poll Expiration

# Set reasonable purge delay
PURGE_DELAY=30  # Delete polls 30 days after expiration

Input Validation
- Framadate sanitizes inputs by default
- Keep software updated for security patches

Performance Optimization

Database Indexing

-- Add indexes for common queries
CREATE INDEX idx_poll_end_date ON fd_poll(end_date);
CREATE INDEX idx_vote_date ON fd_vote(date);
CREATE INDEX idx_comment_date ON fd_comment(date);

Connection Pooling
- Configure PHP-FPM for optimal connections
- Adjust MySQL max_connections based on load

Caching

# Enable opcache in PHP
opcache.enable=1
opcache.memory_consumption=128
opcache.max_accelerated_files=10000

Resource Limits
- Allocate 512MB-1GB memory for typical usage
- Scale based on concurrent poll count

Backup Strategy

Database Backups

# Automated daily backup
mysqldump -h your-db.klutch.sh -P 8000 -u framadate -p framadate \
  --single-transaction --quick --lock-tables=false \
  > framadate_backup_$(date +%Y%m%d).sql

# Compress backups
gzip framadate_backup_*.sql

# Keep last 30 days
find . -name "framadate_backup_*.sql.gz" -mtime +30 -delete

Full System Backup
- Use Klutch.sh volume snapshots
- Schedule weekly snapshots

Off-site Backups

# Upload to S3 or backup service
aws s3 cp framadate_backup.sql.gz s3://your-bucket/backups/

Test Restores

# Regularly test backup restoration
mysql -h test-db.klutch.sh -P 8000 -u framadate -p test_framadate < backup.sql

Monitoring and Logging

Health Checks
- Klutch.sh monitors the built-in health check endpoint
- Add custom health checks for database connectivity

Application Logs

# View logs through Klutch.sh dashboard
# Or access Apache logs in container
tail -f /var/log/apache2/access.log
tail -f /var/log/apache2/error.log

Database Monitoring

-- Monitor poll activity
SELECT
    DATE(creation_date) as date,
    COUNT(*) as polls_created
FROM fd_poll
WHERE creation_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(creation_date);

-- Monitor vote activity
SELECT
    COUNT(*) as total_votes,
    COUNT(DISTINCT poll_id) as active_polls
FROM fd_vote
WHERE date > DATE_SUB(NOW(), INTERVAL 7 DAY);

Alert Configuration
- Set up Klutch.sh alerts for downtime
- Monitor disk usage on database volume

Scaling Considerations

Vertical Scaling
- Increase memory for high-traffic periods
- Upgrade database tier for large poll volumes

Database Optimization

-- Optimize tables monthly
OPTIMIZE TABLE fd_poll, fd_slot, fd_vote, fd_comment;

-- Analyze for query optimization
ANALYZE TABLE fd_poll, fd_slot, fd_vote, fd_comment;

Cleanup Strategy

# Automatic cleanup of expired polls
# Add to cron job or scheduled task
PURGE_DELAY=60  # Days after expiration

Load Testing

# Test concurrent poll access
ab -n 1000 -c 50 https://your-app.klutch.sh/

Update Strategy

Test Updates Locally

# Update to latest Framadate version
git fetch --tags
git checkout 1.2.7  # Latest version

# Test locally
docker build -t framadate-test .
docker run -p 8080:80 framadate-test

Staged Rollout
- Deploy to staging environment first
- Test all functionality before production

Version Pinning

# Pin specific version in Dockerfile
RUN git clone https://framagit.org/framasoft/framadate/framadate.git . \
    && git checkout 1.2.6

Rollback Plan
- Keep previous container image
- Maintain database backups before updates

Troubleshooting

Database Connection Fails

Problem: Framadate cannot connect to MySQL database.

Solution:

Verify database credentials in environment variables

Check database is running and accessible:

mysql -h your-db.klutch.sh -P 8000 -u framadate -p

Confirm database and user exist:

SHOW DATABASES;
SELECT User, Host FROM mysql.user WHERE User='framadate';

Check network connectivity from app to database
Review logs for specific error messages

Polls Not Saving

Problem: Created polls are not saved or disappear.

Solution:

Check database schema is properly initialized:
```
SHOW TABLES;
DESCRIBE fd_poll;
```
Verify database user has write permissions:
```
SHOW GRANTS FOR 'framadate'@'%';
```
Check disk space on database volume
Review application logs for errors
Test database write operations manually

Email Notifications Not Working

Problem: Poll creators and participants don’t receive email notifications.

Solution:

Verify SMTP configuration in config.php
Check SMTP credentials are correct
Test SMTP connectivity:
Terminal window
```
telnet smtp.example.com 587
```
Review mail logs for delivery errors
Check spam folders
Ensure MAIL_DOMAIN matches your domain

Timezone Issues

Problem: Poll times display incorrectly for participants.

Solution:

Set correct timezone in environment:
Terminal window
```
TIMEZONE=America/New_York
```
Verify PHP timezone setting:
```
echo date_default_timezone_get();
```

Check database timezone:

SELECT @@global.time_zone, @@session.time_zone;

Ensure consistent timezone across all components

Performance Degradation

Problem: Slow loading times or timeouts.

Solution:

Check database indexes exist:

SHOW INDEX FROM fd_poll;
SHOW INDEX FROM fd_vote;

Optimize database tables:

OPTIMIZE TABLE fd_poll, fd_vote, fd_slot, fd_comment;

Review slow query log
Increase PHP memory limit and execution time
Scale up database tier if needed
Clean up expired polls

Language Not Displaying Correctly

Problem: Interface shows in wrong language or characters display incorrectly.

Solution:

Verify DEFAULT_LANGUAGE is set correctly
Check database charset:
```
SHOW VARIABLES LIKE 'character_set%';
```

Ensure database uses utf8mb4:

ALTER DATABASE framadate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Clear browser cache
Verify locale files are present in container

Cannot Delete Expired Polls

Problem: Old polls accumulate and consume storage.

Solution:

Manually delete expired polls:

DELETE FROM fd_poll WHERE end_date < DATE_SUB(NOW(), INTERVAL 60 DAY);

Set up automated cleanup cron job
Adjust PURGE_DELAY to remove polls sooner

Check for orphaned records:

SELECT COUNT(*) FROM fd_vote WHERE poll_id NOT IN (SELECT id FROM fd_poll);
DELETE FROM fd_vote WHERE poll_id NOT IN (SELECT id FROM fd_poll);

Additional Resources

You now have a complete Framadate deployment on Klutch.sh! Framadate provides a privacy-respecting, open-source alternative to proprietary scheduling tools, giving you full control over your data while enabling seamless collaboration for scheduling meetings, events, and making group decisions without requiring participants to create accounts.