카페 고객 관리(CRM) 시스템 구축 가이드

CREATE TABLE customers (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 customer_code VARCHAR(20) UNIQUE NOT NULL,
 name VARCHAR(100) NOT NULL,
 phone VARCHAR(20) UNIQUE,
 email VARCHAR(100),
 birth_date DATE,
 gender CHAR(1) CHECK (gender IN ('M', 'F')),
 join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 tier_id INTEGER REFERENCES customer_tiers(id),
 total_visits INTEGER DEFAULT 0,
 total_spent DECIMAL(12,2) DEFAULT 0,
 last_visit TIMESTAMP,
 marketing_consent BOOLEAN DEFAULT FALSE,
 sms_consent BOOLEAN DEFAULT FALSE,
 email_consent BOOLEAN DEFAULT FALSE,
 status VARCHAR(20) DEFAULT 'ACTIVE',
 notes TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 인덱스 생성
CREATE INDEX idx_customers_phone ON customers(phone);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_tier ON customers(tier_id);
CREATE INDEX idx_customers_last_visit ON customers(last_visit);

POST /api/v1/customers
Content-Type: application/json

{
 "name": "홍길동",
 "phone": "010-1234-5678",
 "email": "hong@example.com",
 "birth_date": "1990-01-01",
 "gender": "M",
 "marketing_consent": true
}

Response 201:
{
 "success": true,
 "data": {
 "id": "uuid",
 "customer_code": "CUST0101001",
 "tier": "BRONZE",
 "welcome_points": 1000
 }
}

GET /api/v1/customers/{id}

Response 200:
{
 "success": true,
 "data": {
 "id": "uuid",
 "name": "홍길동",
 "tier": "SILVER",
 "total_visits": 15,
 "total_spent": 450000,
 "points_balance": 2500,
 "last_visit": "-01-20T10:30:00Z"
 }
}

// server.js
const express = require('express');
const { Pool } = require('pg');
const cors = require('cors');

const app = express();
app.use(cors());
app.use(express.json());

// 데이터베이스 연결
const pool = new Pool({
 user: process.env.DB_USER,
 host: process.env.DB_HOST,
 database: process.env.DB_NAME,
 password: process.env.DB_PASSWORD,
 port: process.env.DB_PORT,
});

// 고객 등록 API
app.post('/api/v1/customers', async (req, res) => {
 const { name, phone, email, birth_date, gender, marketing_consent } = req.body;
 
 try {
 const client = await pool.connect();
 
 // 중복 확인
 const existingCustomer = await client.query(
 'SELECT id FROM customers WHERE phone = $1',
 [phone]
 );
 
 if (existingCustomer.rows.length > 0) {
 return res.status(400).json({
 success: false,
 error: '이미 등록된 전화번호입니다.'
 });
 }
 
 // 고객 코드 생성
 const today = new Date().toISOString().slice(0, 10).replace(/-/g, '');
 const customerCode = `CUST${today}${String(Math.floor(Math.random() * 1000)).padStart(3, '0')}`;
 
 // 고객 등록
 const result = await client.query(
 `INSERT INTO customers (customer_code, name, phone, email, birth_date, gender, marketing_consent)
 VALUES ($1, $2, $3, $4, $5, $6, $7)
 RETURNING id, customer_code`,
 [customerCode, name, phone, email, birth_date, gender, marketing_consent]
 );
 
 // 웰컴 포인트 지급
 const welcomePoints = 1000;
 await client.query(
 `INSERT INTO point_history (customer_id, points, type, description)
 VALUES ($1, $2, 'EARNED', '신규 가입 웰컴 포인트')`,
 [result.rows[0].id, welcomePoints]
 );
 
 client.release();
 
 res.status(201).json({
 success: true,
 data: {
 id: result.rows[0].id,
 customer_code: result.rows[0].customer_code,
 tier: 'BRONZE',
 welcome_points: welcomePoints
 }
 });
 
 } catch (error) {
 console.error('고객 등록 오류:', error);
 res.status(500).json({
 success: false,
 error: '서버 오류가 발생했습니다.'
 });
 }
});

// CustomerRegistration.jsx
import React, { useState } from 'react';
import axios from 'axios';

const CustomerRegistration = => {
 const [formData, setFormData] = useState({
 name: '',
 phone: '',
 email: '',
 birth_date: '',
 gender: '',
 marketing_consent: false
 });
 
 const [loading, setLoading] = useState(false);
 const [message, setMessage] = useState('');
 
 const handleSubmit = async (e) => {
 e.preventDefault();
 setLoading(true);
 
 try {
 const response = await axios.post('/api/v1/customers', formData);
 
 if (response.data.success) {
 setMessage(`고객 등록 완료! 고객번호: ${response.data.data.customer_code}`);
 setFormData({
 name: '',
 phone: '',
 email: '',
 birth_date: '',
 gender: '',
 marketing_consent: false
 });
 }
 } catch (error) {
 setMessage(error.response?.data?.error || '등록 중 오류가 발생했습니다.');
 } finally {
 setLoading(false);
 }
 };
 
 return (
 <div className="customer-registration">
 <h2>고객 등록</h2>
 <form onSubmit={handleSubmit}>
 <!-- 폼 필드들 -->
 </form>
 </div>
 );
};