Sample design for a sync between iOS sqlite and Postgress

Client Database (sqlite).sql

CREATE TABLE Document (
  Id                integer PRIMARY KEY AUTOINCREMENT,
  Guid              varchar UNIQUE, /*This is the SERVER ID*/
  /* Foreign keys */
  FOREIGN KEY (Customer)
    REFERENCES Customer(Id) ON DELETE CASCADE,
  FOREIGN KEY (Location)
    REFERENCES Location(Id) ON DELETE CASCADE
);

CREATE TABLE DocumentDetail (
  Id            integer PRIMARY KEY AUTOINCREMENT,
  Guid          varchar UNIQUE, /*This is the SERVER ID*/
  Document      integer NOT NULL,
  Product       integer NOT NULL,
  /* Foreign keys */
  FOREIGN KEY (Document)
    REFERENCES Document(Id) ON DELETE CASCADE,
  FOREIGN KEY (Product)
    REFERENCES "Product"(Id) ON DELETE CASCADE
);

Server Database (PG).sql

-- For get the next version:
CREATE SEQUENCE document_version;
CREATE SEQUENCE documentdetail_version;
CREATE SEQUENCE payment_version;

-- Run on each insert/change
-- Using logical deletes only
CREATE OR REPLACE FUNCTION bach_update() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        NEW.ChangeDate = NOW();
        NEW.Version = nextval(lower(TG_TABLE_NAME || '_version'));
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        NEW.ChangeDate = NOW();
        NEW.Version = nextval(lower(TG_TABLE_NAME || '_version'));
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

-- Common fields to all the tables
CREATE TABLE baselog (
    Version INTEGER NOT NULL DEFAULT 0,
    ChangeDate TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    IsDeleted bool NOT NULL DEFAULT false
);

-- Our main tables
CREATE TABLE Document (
    Id      uuid PRIMARY KEY DEFAULT libs.uuid_generate_v4(),
-- More fields...
-- More fields...
) INHERITS (baselog);

CREATE TRIGGER document_version BEFORE INSERT OR UPDATE ON Document
    FOR EACH ROW EXECUTE PROCEDURE bach_update();

CREATE TABLE DocumentDetail (
    Id      uuid PRIMARY KEY DEFAULT libs.uuid_generate_v4(),
    Document uuid NOT NULL REFERENCES Document ON DELETE CASCADE,
-- More fields...
-- More fields...
) INHERITS (baselog);

CREATE TRIGGER documentdetail_version BEFORE INSERT OR UPDATE ON DocumentDetail
    FOR EACH ROW EXECUTE PROCEDURE bach_update();

CREATE TABLE Payment (
    Id      uuid PRIMARY KEY DEFAULT libs.uuid_generate_v4(),
    Document uuid NOT NULL REFERENCES Document ON DELETE RESTRICT,
-- More fields...
-- More fields...
) INHERITS (baselog);

CREATE TRIGGER payment_version BEFORE INSERT OR UPDATE ON Payment
    FOR EACH ROW EXECUTE PROCEDURE bach_update();

Sync code (push) iOS.m

- (NSMutableDictionary *) fixData:(DbObject *)record isDeleted:(BOOL)isDeleted;
{
    NSMutableDictionary *data = [NSMutableDictionary dictionary];
    /* Stuff */
    data[@"isdeleted"] = @(isDeleted);
    data[@"version"] = @([self versionForTable:[record tableName]]); //This get the local version
    posPush++;
    data[@"pos"] = @(posPush);
    return data;
}

-(void) logRecord:(DbObject *)record status:(RecordStatus)status
{
    NSString *sql = @"INSERT OR REPLACE INTO SyncLog (Id, Name, Op) VALUES (:id, :name, :op)";
    
    [[Db currentDb] execute:sql withDict:@{@"id":record.guid, @"name":[record tableName],@"op": @(status)}];
}

-(void) pushDocument:(DbObject *)record isDelete:(BOOL)isDelete handler:(handleError)handler
{
    RecordStatus st;
    
    if (isDelete) {
        st = RECORD_DELETED;
    } else {
        st = RECORD_UPDATED;
    }
    
    [self logRecord:record status:st];

    if (![self isReachable]) {
        DDLogWarn(@"Can't send %@, server is not reachable", [record tableName]);
        return;
    }

    Document *doc;
    DocumentDetail *det;
    
    NSMutableDictionary *enc;
    NSMutableArray *details = [NSMutableArray array];
    NSMutableArray *pays = [NSMutableArray array];

    DDLogInfo(@"Send %@ for sync", [record tableName]);

    if ([record isKindOfClass:[DocumentDetail class]]) {
        det = (DocumentDetail *)record;
        doc = det.document;
        
        enc = [self fixData:doc isDeleted:NO];
        
        [details addObject:[self fixData:det isDeleted:isDelete]];
    } else {
        enc = [self fixData:record isDeleted:isDelete];

        for (DocumentDetail *d in doc.details) {
            [details addObject:[self fixData:d isDeleted:isDelete]];
        }
        
        for (Payment *p in [doc loadPayments]) {
            [pays addObject:[self fixData:p isDeleted:isDelete]];
        }
    }
    
    enc[@"details"] = details;
    enc[@"payments"] = pays;
    
    NSDictionary *params = @{@"document":enc};
    
    [self sendRecords:@"sync/document/" data:params handler:handler];
}

Sync code(pull).sql

--The iOS get a copy of the data from the server as a sqlite DB, and for each table do this:
INSERT OR REPLACE INTO document (guid, version,customer,user,docreference,consecutive,documentdate,deliverydate,paymentdue,notes,rute,shippingcost,change,location,address,zipcode,phone,phone2,cellphone,doctype,state,info,currencycode)
SELECT id, version,(SELECT customer.Id FROM customer WHERE customer.guid=customer),user,docreference,consecutive,replace(datetime(documentdate, 'localtime'),' ', 'T'),replace(datetime(deliverydate, 'localtime'),' ', 'T'),replace(datetime(paymentdue, 'localtime'),' ', 'T'),notes,rute,shippingcost,change,(SELECT location.Id FROM location WHERE location.guid=location),address,zipcode,phone,phone2,cellphone,doctype,state,info,currencycode
FROM SERVER.document
WHERE not(SERVER.document.isdeleted);
--UP--
DELETE FROM document WHERE guid IN (
SELECT Id FROM SERVER.document WHERE isdeleted);

Sync on the server.py

# This use sqlalchemy
class dbSync(object):

    def getTable(self, name):
        return sq.Table(
            name,
            self.meta,
            autoload=True,
            autoload_with=self.con,
            listeners=[
                ('column_reflect', listen)
            ]
        )
    #This get all the records > client version
    def copyTables(self, path, tables, deviceId):
        src = self.con

        dest = self.getDbSqlite(path)
        meta = sq.MetaData()
        meta.bind = dest

        haveData = False

        with dest.begin():
            for table, version in tables:
                table = self.getTable(table.lower())

                if table.name == 'company':
                    sql = table.select().where(table.c.username >= self.schema)
                else:
                    sql = table.select().where(table.c.version >= version)

                data = src.execute(
                    sql
                ).fetchall()

                if len(data):
                    print "Creating ", table
                    haveData = True

                    desTable = self.buildSqliteTable(meta, table)
                    desTable.connection = dest
                    desTable.create()

                    print "Importing ", len(data), ' from ', desTable

                    dest.execute(
                        desTable.insert(),
                        data
                    )


        dest.dispose()
        return haveData
    # This save the record in the DB
    def saveRecord(self, table, data):
        theGuid = data['guid']
        data["id"] = theGuid

        if table.name == 'company':
# A special case
        else:
            exist = self.con.execute(
                select([1]).where(table.c.id == theGuid)
            ).scalar()

        print "Exist ", exist, "V:", data['version'], "D:", data['isdeleted']
        if exist:
            version = int(data['version'])
            #print "Version ", version
            self.con.execute(
                table.update().where((table.c.id == theGuid) &
                                     (table.c.isdeleted == False)),
                data
            )

        else:
            del data['version']
            self.con.execute(
                table.insert(),
                data
            )

        version = self.con.execute(
            select([table.c.version]).where(table.c.id == theGuid)
        ).scalar()

        return dict(
            table=table.name,
            version=version,
            guid=theGuid
        )

    def syncRecord(self, tableName, data):
        table = self.getTable(tableName.lower())

        with self.con.begin():
            return self.saveRecord(table, data)
    #Save the invoice. When a detail/payment is made, it bring the header but no all
    #the details for performance reasons
    def syncDocument(self, document):
        doc = self.getTable('document')
        det = self.getTable('documentdetail')
        pay = self.getTable('payment')

        details = document['details']
        payments = document['payments']

        result = []

        with self.con.begin():
            result.append(self.saveRecord(doc, document))

            for d in details:
                result.append(self.saveRecord(det, d))

            for d in payments:
                result.append(self.saveRecord(pay, d))

            return result